CDC in SQL Server

CDC stands for change data capture, is a new and much anticipated feature introduced in SQL Server 2008. In earlier versions DBAs used to code complex DML triggers to track the changes made to their tables. Triggers comes with known performance issues and many draw backs, so change tracking and CDC introduced together in SQL Server 2008 but CDC is available only with the enterprise edition.

CDC keeps track of all DML changes which takes place in the database tables for which we have enabled the same.

DOWNLOAD SAMPLE LAB SCRIPT

Step 01: (Enable CDC on database level)

Step 02: (Enable CDC on Table)

To know more about the parameter of sys.sp_cdc_enable_table 

CDC_01
System objects are created referencing to CDC

Step 03: (Tracking DML changes)

CDC_02

In the above query, we inserted a brand new record then updated the author name and then deleted the same record.

We have done 3 DML operations but got 4 records inserted into the table and are of different operations (_$operation)

1 = Delete statement
2 = Insert statement
3 = Value before update
4 = Value after update

While creating the CDC on table “eBooks”, we had defined a parameter to capture all and net changes for the table (@support_net_changes = 1), which created two table valued functions related to the table. One is to show All Changes and another one is to show Net Changes.

fn_cdc_get_all_changes_dbo_eBooks – To show all changes including net changes
fn_cdc_get_net_changes_dbo_eBooks – To show only net changes
sys.fn_cdc_get_min_lsn – To get min LSN
sys.fn_cdc_get_max_lsn – To get max LSN

ALL Changes: (fn_cdc_get_all_changes_dbo_eBooks)

If we have to update one record 5 times in a table, the ALL changes function would return all 5 updates made to that record.

CDC_03Net Changes: (fn_cdc_get_net_changes_dbo_eBooks)

If we have to update one record in a table 5 times, then the most recent change would be the NET change. When the query is using Net Change function, we will get only one instance of a changed record, that means the latest changed record, no matters how many times that has been changed.

CDC_04

Viewing specific CDC data:

So far we know how to get all the DML changes from CDC table, now we want to audit the DML changes happened on “eBooks” table during a specified time frame.

sys.fn_cdc_map_lsn_to_time – Maps LSN to Datetime
sys.fn_cdc_map_time_to_lsn – Maps Datetime to LSN

Let’s first find, at what time the DML operations were done on “eBooks” table. To get the same, we will be using function sys.fn_cdc_map_lsn_to_time to get the date time mapped to LSN.

CDC_05
DDLTime column maps the LSN to datetime

Now we have the DDLTime column which tells us about the DML operation time happened on eBooks table. Let’s get the CDC data between a specified time frame of [2014-11-26 11:38:33.737] to [2014-11-26 12:18:24.277]

CDC_06
First table = All changes | Second table = Net changes

Disabling CDC:

After disabling the CDC,  respected system objects that are created, will be also removed automatically.

 

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

One thought on “CDC in SQL Server

  • November 28, 2014 at 12:16 pm
    Permalink

    Nice one.. well explained

    Reply

Leave a Reply

%d bloggers like this: