Introduction To CDC (Change Data Capture) Of SQL Server - Part One

In this article, we will look into a SQL Server feature named CDC.

In this article, we will look intoa  SQL Server feature, called CDC, used for tracking/auditing database changes at table level. This feature will help us to track database changes like INSERT, UPDATE and DELETE on tables.

It even tracks old and new values for an update operation. CDC uses SQL Server transaction logs for capturing all inserts, updates, and deletes on a table. This feature is available on 2008 or higher versions and part of enterprise editions. Let’s open management studio and enable CDC on EmployeeDB to track the changes by following the below steps:

Enable CDC on a database by running the following command, it needs sysadmin privileges.

Enable CDC
Create a role to which we will give access to CDC tables (which will hold all data changes) using the following command:

  1. CREATEROLEcdc_role  
We need to select tables on which tracking should be enabled by running the following command:
  1. EXECsys.sp_cdc_enable_table  
  2. @source_schema='dbo',-- Schema name  
  3. @source_name='employees',-- Table Name  
  4. @role_name=N'cdc_role'-- Role having access on CDC tables [having data audit details]  
This will start SQL jobs to track changes done to employees table. If you expand Tables node and go to System Tables in Object Explorer, there will be a table employees_CT with exact schema of employees to hold data changes:

CDC

Select query

Let’s test CDC by doing some changes to employees table:

employees table

Let’s query our tracking table [dbo_employees_CT]:

result

If column _$operation is 1 it means it’s a DELETE operation; 2 means INSERT; 3 means Value before UPDATE; and 4 means Values after UPDATE. We will write the following query to get results more meaningfully:

DELETE operation

Apart from dbo_employees_CT table, we have other tables created by CDC under System Tables to store metadata for its tracking purpose. Let’s understand purpose of each:

Captured_columns: It has all column’s details on which CDC is enabled:

details

Change_tables: It contains capture details like table name, role name etc along start and end lsn. Any change on a table is uniquely identified by LSN (log sequence number).

contains capture details

ddl_history: It contains information on any schema changes on the tracking table [employees] like adding\removing a column. Here, I added a new column location.

contains information

index_columns: It contains index details of tables on which tracking is enabled.

contains index details

lsn_time_mapping: It contains mapping details of table change’s LSN and its time of occurrence:

contains mapping details

I am ending things here. In next article, we will drill down more on CDC. I hope this article will be helpful for all.
 
Read more articles on SQL Server: