SQL Server–Change Data Capture [CDC] for “Watching” database tables for DML operations

In scenarios when we want to watch tables for any inserts, updates and deletes, we implement triggers. Triggers – not only needs database development effort and needs to be correctly written, It places locks on tables and slows things down.

In SQL Server 2008, Microsoft introduced a new capability called “Change Data Capture” [CDC] to watch and track changes on tables for any inserts, updates and deletes. This requires almost no database development effort and is more efficient than triggers. A very nice thing about CDC is that It makes use of transaction log which has all the data about any changes made to the database alreadySo why reinvent the wheel?

Basically, first you enable CDC on the database. Then enable CDC on the table (e.g. Account) you want to watch which will automatically create the a change tracking table (“Account_CT”) for the watched table. Any changes in your watched table (e.g. Account) will get recorded in the change tracking table (e.g Account_CT) and you can use the tracking table for all your queries.

“Talk is cheap. Show me the code.” – Linus Torvalds

1. Preparing sample data

/*Create sample database*/
USE [master]
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N’ChangeDataCaptureTest’)
DROP DATABASE [ChangeDataCaptureTest]
GO
/****** Object:  Database [ChangeDataCaptureTest]    Script Date: 10/23/2013 17:25:04 ******/
CREATE DATABASE [ChangeDataCaptureTest]

/*Create the sample table – This is the table we will be watching for inserts, updates and deletes*/
USE ChangeDataCaptureTest
GO
CREATE TABLE [Account]
(
    Id INT Primary Key IDENTITY(1,1),
    [Description] VARCHAR(500),
    [Active] BIT
)
GO

2. Enable CDC on the database

This looks scary, but all it is doing it is checking an already existing flag in sys.databases table in the master database. If you execute the following, you can see in which database CDC is currently enabled. In the below example, It is not enabled in any of the database:-
USE master
GO
SELECT [name], database_id, is_cdc_enabled 
FROM sys.databases      
GO    

image

Now we can enable CDC on our sample database.
USE ChangeDataCaptureTest
GO
EXEC sys.sp_cdc_enable_db
GO

If you execute the following, you can see the “is_cdc_enabled” column for our sample database is enabled:-
USE master
GO
SELECT [name], database_id, is_cdc_enabled
FROM sys.databases
GO

image

3. Enable CDC on the table you want to watch for Insert/Update/Delete

Now we need to enable the CDC on the table we want to watch.

USE ChangeDataCaptureTest
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N’dbo’,
@source_name   = N’Account‘,
@role_name     = NULL
GO

When we execute the above, we see two Sql jobs created and started automatically.

image

image

cdc.ChangeDataCaptureTest_capture
This job watches the table “Accounts” and put changes in the tracking table Account_CT
cdc.ChangeDataCaptureTest_cleanup –
This job cleans up the tracking table Account_CT and can be scheduled as per the requirement.

At this point If we query the sys.tables :-
USE ChangeDataCaptureTest
GO
SELECT [name], is_tracked_by_cdc
FROM sys.tables
GO

image

4. Testing the results

Let us insert/update/delete data in the watched table [Account] and see the tracked changes in the [Account_CT] table.

Insert Operation
USE ChangeDataCaptureTest
GO
INSERT INTO Account
VALUES (‘Test’, 1)

Select to verify results
USE ChangeDataCaptureTest
GO
SELECT * FROM Account
GO
SELECT * FROM cdc.dbo_Account_CT
GO

image

Value for “__$operation” column is 2 indicates “Insert”. We can see the values in the columns “Description” and “Active”.

Update operation
USE ChangeDataCaptureTest
GO
UPDATE Account
SET Active= 0
WHERE Id = 1
Select to verify the results
USE ChangeDataCaptureTest
GO
SELECT * FROM Account
GO
SELECT * FROM cdc.dbo_Account_CT
GO

image
Value for “__$operation” column :-
3 = update (captured column values are those before the update operation).
4 = update (captured column values are those after the update operation)

DELETE Operations
USE ChangeDataCaptureTest
GO
DELETE Account
WHERE id = 1
Select to verify results
USE ChangeDataCaptureTest
GO
SELECT * FROM Account
GO
SELECT * FROM cdc.dbo_Account_CT
GO

image

Value for “__$operation” column is 2 indicates “Delete”.

Time based search on table changes
Need to see changes in a table based on given timestamp? No problem. When we enabled the table for change tracking, It also added a system table named “cdc.lsn_time_mapping” which has all the transaction with the timestamp.
Just join the change tracking table (Account_CT) with the system table “cdc.lsn_time_mapping” table on transaction id (start_lsn) and have the transaction filter criteria on the same.

image

USE ChangeDataCaptureTest
GO
SELECT B.*, A.tran_begin_time, A.tran_end_time 
FROM cdc.lsn_time_mapping A
INNER JOIN cdc.dbo_Account_CT B
ON A.start_lsn = B.__$start_lsn

image

Note :- In CDC, there’s no way to trace the user who causes each transaction.

Advertisements

, , ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Random Thoughts

The World as I see it

Simple Programmer

Making The Complex Simple

Ionic Solutions

Random thoughts on software construction, design patterns and optimization.

Long (Way) Off

A tragic's view from the cricket hinterlands

%d bloggers like this: