Change Data Capture In SQL Server

Overview

As a part of any organization, we always need a tracking mechanism to track the changes in the respective tables and changes can be an inserted, updated or deleted in the database. Yes, it can be done by coding methods also, by inserting log files and keeping the log files on the Server, and you can track the necessary changes taking place in the database.

When you see on the DB SQL Server side, there is a change in the data capture mechanism where you can specify the tables which you want to keep a track of. Due to which we are able to see and  cross check  which process is happening. So let’s start. 

Introduction

This mechanism was introduced in SQL Server 2005 and higher, but there were some drawbacks in CDC environments as these drawbacks are being taken care of; from SQL Server 2008 and so on.

Just consider, when you enable Change Data Capture (CDC) on the database, it creates tables under the system tables and with the same structure of the original table source.

Any DML (Data Manipulation Language ) existing on the database SQL Server, Change Data Capture reads a SQL Server transaction log and captures it in their tables i.e. CDC tables .

Let’s Start

• Open SSMS
  
  
  

On Master, type a query

• SELECT * FROM sys.sysdatabases
  
  
  

Here, you will get an output which includes DBname, dbit,Path, creation date and so on.

• Let’s see on which database CDC is enabled.
  
  
• Type command 
  
  SELECT name,database_id,is_cdc_enabled from sys.databases.
  
  
  

You will get an output and see the list of the databases in which you see the “is_cdc_enabled ” column and you will see LDAPAuth database value is 1, which means CDC is enabled .

So let’s take some other database for CDC testing

• We will see the command to enable CDC,
  
  
  
  
  

  USE BloodBankManagement
  GO
  EXEC sp_cdc_enable_db
  GO

• To disable CDC on the database, the following operation needs to be performed:
  
  
  

  USE BloodBankManagement
  GO
  EXEC sp_cdc_disable_db

• Let’s check in the database, whether CDC is enabled or not
  
  Go to BloodBankManagement -> Tables -> System Tables
  
  You will see the screenshot, shown below:
  
  
  

Tables are created and are listed below:

• Cdc.captured_columns- It captures any columns from the original source tables.
  
• Cdc.change_tables- To see, what change is happening, DML changes in the original source table.
  
• Cdc.ddl_history- It contains DDL changes
  
• Cdc.index_columns- It contains index changes.
  
• Cdc.lsn_time_mapping- It maps the LSN number LSN is nothing nut an LAST sequence number . SQL server generates ab unique LSN number- It is used for each transactional log .
• Now let’s see how to enable CDC on the table, in order to track the changes.

Create table

CREATE TABLE TESTCDC (ID INT IDENTITY(1,1) PRIMARY KEY,
NAME VARCHAR(10) NOT NULL,
ADDRESS VARCHAR(20) NULL,
CITY VARCHAR(20) NULL);

 

• Now, let’s insert some values.
  

  INSERT INTO TESTCDC(NAME,ADDRESS,CITY)
  VALUES('AK','xyz','MUM')

  
  
  
• Insert a minimum of 10 records.
  
  
  
  
• Let’s check the data.
  
  
  
  
• Let's enable CDC on the table.
  

  EXEC sp_cdc_enable_table
  @source_schema=N'dbo',
  @source_name=N'TESTCDC',
  @role_name=NULL

  
  

You will see the output

• Refresh the system table view.
  
  
  

You will see

• CDC on the table is enabled, as you can see in the highlighted section.
  
  
• Select the CDC table.
  
  
  

You will see no data. Now, let's update a row and see whether it captures or not.

• Update a Value
  
  
  
  
• Select CDC Table
  
  
• You will see
  
  
  
  
• Now, let’s differentiate between New Value and Old Value.

Refer to the screenshot shown below, as you can see _$operation field,

• 3 denoted that it is before the update and Name column contains Value zz.
• 4 denotes after the update and updated value is SS in Name column.
  
  
  

Conclusion

This was CDC capturing data in SQL Server and keeping track. I hope you found  this article helpful. If you have any doubts related to this article, feel free to ask.