Clota Blog

Category: SQL Server

  • Dive into Buffer pool extension

    The Buffer pool extension feature started from SQL server 2014. This is a nice addon to SQL server to extend the memory paging to SSD. This is not an enterprise only  feature, this can be enabled on standard edition. The database size has grown huge now the Db’s having size more than 1 TB is…

  • Let’s talk about In memory OLTP

    In-memory OLTP frequently referred to by its code name “Hekaton”, was introduced in SQL Server 2014 but it got more popular from SQL Server 2016. This feature was being developed by Microsoft to stay in the game for high speed OLTP transaction speed and recent hardware changes in terms of the cost and throughput. Many…

  • SQL Server Info (Inventory)

    — SQL Server Info (Inventory) SELECT      SERVERPROPERTY(‘ServerName’) AS ServerName,      SERVERPROPERTY(‘MachineName’) AS MachineName,     CASE         WHEN  SERVERPROPERTY(‘InstanceName’) IS NULL THEN @@SERVICENAME         ELSE SERVERPROPERTY(‘InstanceName’)     END AS InstanceName,     SUBSTRING ( (SELECT @@VERSION),1, CHARINDEX(‘-‘,(SELECT @@VERSION))-1 ) as ProductName,     SERVERPROPERTY(‘ProductVersion’) AS ProductVersion,      SERVERPROPERTY(‘ProductLevel’) AS ProductLevel,     SERVERPROPERTY(‘ProductMajorVersion’) AS ProductMajorVersion,     SERVERPROPERTY(‘ProductMinorVersion’) AS ProductMinorVersion,     SERVERPROPERTY(‘ProductBuild’) AS ProductBuild,     SERVERPROPERTY(‘Edition’) AS Edition,     CASE SERVERPROPERTY(‘EngineEdition’)         WHEN 1 THEN ‘PERSONAL’         WHEN 2 THEN ‘STANDARD’         WHEN 3 THEN ‘ENTERPRISE’         WHEN 4 THEN ‘EXPRESS’         WHEN 5 THEN ‘SQL DATABASE’         WHEN 6 THEN ‘SQL DATAWAREHOUSE’     END AS EngineEdition,      CASE SERVERPROPERTY(‘IsHadrEnabled’)         WHEN 0 THEN ‘The Always On Availability Groups feature is disabled’         WHEN 1 THEN ‘The Always On Availability Groups feature is enabled’         ELSE ‘Not applicable’     END AS HadrEnabled,     CASE SERVERPROPERTY(‘HadrManagerStatus’)         WHEN 0 THEN ‘Not started, pending communication’         WHEN 1 THEN ‘Started…

  • Auditing: Change Data Capture(CDC)

    Overview: Change Data Capture, also known as CDC, introduced the first time in SQL Server 2008 version. Change data capture records insert, update, and delete activity that applies to a SQL Server table. if you want to store the audit information about the UPDATE, INSERT, DELETE operations then enable the SQL CDC on that table. Note: …

  • Ghost Records

    When any record get deleted from DB, SQL Server does not delete the records physically from disk immediately. The deleted records which exists physically in storage called Ghost Records. SQL Server does not use synchronous method to delete the records physically for DML operation because if SQL Server delete the records synchronously, this will going to drastically…

  • Provisioning a First Azure SQL Database

    In this section, we’ll learn: Provisioning Azure SQL database:  Provisioning an Azure SQL database refer to creating a new and blank Azure SQL database. We’ll create new SQL database in Azure using the Azure portal: 1. Open a browser and log in to the Azure portal: https://portal.azure.com 2. On the left-hand navigation pane, select Create a resource: 3. On the New page, under Databases, select SQL…

  • Moving A Table Data Into New Location Within Database

    Problem I often getting low disk space alert from one of my data drive. Adding space on the disk was not possible because of hard drive size limitations and barriers at OS max disk space size. So I started looking what can I do for database end and I found there is only one table…

  • Cloud Witness in Azure

     Cloud Witness is a type of Fail-over Cluster quorum witness that uses Microsoft Azure to provide a vote on cluster quorum. There are significant benefits which this approach: Problem with traditional cluster setup Lets consider a typical cluster setup which is shown in below diagram. Here we have file share witness in primary DC and unfortunately…

  • Provisioning a SQL Managed Instance Using the Azure Portal

    Azure SQL Managed Instance is intelligent, scalable cloud database service. SQL Managed Instance allows existing SQL Server customers to lift and shift their on-premises applications to the cloud with minimal application and database changes. To create a SQL Managed Instance, follow these steps: 1. Log in to https://portal.azure.com using your Azure credentials.  2. In the top search box, type SQL Managed…