Learn Change data capture – CDC in SQL Server with Example

1
1137

Aim :-  In this post, we will discuss What, Why, How of Change Data Capture (CDC) in SQL Server.

Description :-  SSIS is a very good ETL tool provided by Microsoft and generally used to Load data into Database. In some scenarios, Business user comes up with a requirement to save all the history of the changes to the data. This type of requirement is usually applied for data security purpose. This can be implemented using numerous methods such as by writing some complex queries in a stored procedure or with the help of triggers (as data is captured after DML operations) and some other ways.

With the release of SQL Server 2005, they tried to tackle the problem of tracking or capturing data changes in the table. It introduced some new features such as “After update”, “After Insert”, “After Delete” triggers that solved this data capture problem to a very large extent. With recent versions of SQL Server like 2008 and others, a better solution was introduced known as Change Data Capture. It made the developer’s life so easy by providing an option to capture all data changes without writing any code for it.

Change Data Capture

Change Data Capture also known as (aka) CDC helps to track operations like INSERT, UPDATE and DELETE on the data present in a SQL Server table. CDC needs to be enabled first and you can execute the below query to check whether CDC is enabled or not for the databases.

This query on execution will return the list of database names, database ids and will show whether CDC is enabled (1) or not (0).

Is CDC enabled on Databases in SQL Server
Is CDC enabled on Databases in SQL Server

We can enable Change Data Capture (CDC) in two levels.

  1. Database level
  2. Table level

NOTE :-  When we enable the CDC, it will create CDC schema, CDC User, Meta data tables and other objects (system stored procedure and jobs).

STEP 1 :  Steps to enable Change Data Capture (CDC) on Database level

  • Open SQL Server Management Studio (SSMS).
  • Now, Choose database where you want to enable CDC. In our case, I am applying CDC on my “Test” database.
  • Click on New Query to open the SQL query editor.
  • For enabling the CDC on Database level we are using below system stored procedure

Enabling Change Data Capture on Test Database
Enabling Change Data Capture on Test Database

  • Once it gets executed successfully. You will get the message like command completed successfully.
Change Data Capture enabled on Test database Successfully
Change Data Capture enabled on Test database Successfully
  • Now, it means that CDC is enabled on my “Test” database. Also, you can find a schema with name cdc has now been created. Moreover, once CDC is enabled on Database it will create some System tables.
Some System tables being created as a part of CDC schema
Some System tables being created as a part of CDC schema
  • Cdc.captured_column returns list of captured columns
  • Cdc.change_tables returns list of all enabled tables for capture
  • Cdc_ddl_history contains history of all the DDL changes since CDC is enabled
  • Cdc.index_columns contains indexes related with change table
  • Cdc.Isn_time_mapping maps time and LSN number.

And, Change Data capture (CDC) also creates some system Stored Procedures.

Some Stored Procedures are being created as a part of CDC schema
Some Stored Procedures are being created as a part of CDC schema

STEP 2 :  Steps to enable Change Data Capture (CDC) on Table

  • For enabling Change Data Capture (CDC) on table level, we will use the below Stored Procedure.
  • We will enable CDC on emp table in Test database.
  • Now, Execute the above Stored Procedure.
  • It will create a table under System table as shown below in the image.
Change Data Capture in SQL Server is enabled for Emp table
Change Data Capture in SQL Server is enabled for Emp table

It also creates SQL server Agent job and these jobs are automatically enabled when they are created.

  1. cdc.Test_capture
  2. cdc.test_cleanup
SQL Server Jobs are automatically enabled
SQL Server Jobs are automatically enabled

Now, we have enabled CDC on both levels i.e. Database level as well as Table level. Let’s test the functionality of Change Data Capture (CDC) in SQL Server.

STEP 3 :  Test the Change Data Capture (CDC) functionality with INSERT, DELETE, UPDATE operations

Currently, we have only 2 records in the Emp table as shown in the screenshot below.

Number of rows in Emp table
Number of rows in Emp table

INSERT Operation

  • Let’s insert 1 record into this Emp table.

  • Now, we can find that inserted record in the cdc.dbo_emp_CT.
Insert command of CDC in SQL Server
Insert command of CDC in SQL Server

UPDATE Operation

  • Let’s update 1 record of this Emp table. We will update the address of Kumar from Pune to Mumbai.
  • Now, we can find 2 records in the cdc.dbo_emp_CT. One is having old value and second row is having updated records.
Update command of CDC in SQL Server
Update command of CDC in SQL Server

DELETE Operation

  • Let’s delete 1 record from the Emp table.
  • Now, see the 4th row which is the deleted record.
Delete command of CDC in SQL Server
Delete command of CDC in SQL Server

NOTE :-  Did you notice the different values of  _$operation column. Different value shows different DML operation which were performed on the table. Find below the relation between these values and DML operations.

CDC Operations and Operation Numbers in SQL Server
CDC Operations and Operation Numbers in SQL Server

cdc.captured_columns Table

  • It stores the information about the columns of the table on which we enabled CDC in SQL Server.

CDC Captured columns in SQL Server
CDC Captured columns in SQL Server

cdc. change_tables

  • It stores the name of table (list of table name) on which we enabled the Change Data Capture(CDC) in SQL Server.

CDC Change tables in SQL Server
CDC Change tables in SQL Server

For ages, programmers put in hard efforts and spent sleepless nights thinking of creating a solution to track all the changes happened on the data. With the release of SQL Server 2008, Change Data Capture (CDC) tool came into market and resolved the problem of capturing data changes. Your Ideas, Suggestions, Comments or Queries are highly encouraged and will be appreciated by us.

Editor – Chander Sharma

View articles written by Chander Sharma

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.8/10 (6 votes cast)
VN:F [1.9.22_1171]
Rating: +6 (from 6 votes)
Learn Change data capture – CDC in SQL Server with Example, 9.8 out of 10 based on 6 ratings

1 COMMENT

Comments are closed.