Configuring Change Data Capture

In this article Im going to configure change data capture. Im going to create a new database and a table for this purpose. Configuring CDC is very simple its just a two step process as below

• Enable CDC for the database
• Enable CDC for the required tables

Use the below script to create new database and a table for testing purpose.

USE master
GO
IF EXISTS(SELECT 1 FROM sys.databases WHERE name = ‘ChangeDataCapture’)
DROP DATABASE ChangeDataCapture
GO
CREATE DATABASE ChangeDataCapture
GO
USE ChangeDataCapture
GO
CREATE TABLE Employee
(
empno INT NOT NULL,
empname VARCHAR(200) NOT NULL
)
INSERT Employee VALUES(134563,’Sankar’)
INSERT Employee VALUES(983678,’Gopi’)
INSERT Employee VALUES(237832,’James’)

Enabling CDC for the database

Use the below script to enable CDC for the database. Once CDC is enabled 5 system tables related CDC are created as shown in the image.

USE ChangeDataCapture
GO
EXEC sys.sp_cdc_enable_db
GO

Enabling CDC for the required tables

Use the below script to enable CDC for a table, Im using required parameters only for the procedure. Once you enable this for a table, a new tracking table and two SQL jobs are created as shown in the image.

USE ChangeDataCapture
GO
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'Employee',
@role_name = 'CDC_Role'
GO

Where,

@source_schema – Name of the schema in which the source table belongs

@source_name – Name of the table to which CDC need to turn on

@role_name – Role used for gate access to change data. If the role doesn’t exists then it will be created.

Thats all CDC is turned on for the table and the table is ready for tracking DML & DDL changes.

Testing DML changes tracking

Lets do some DML operation in the Employee table and check for tracked data.

USE ChangeDataCapture
GO
--Inserting Data
INSERT Employee VALUES('223987', 'Mahesh')
--Updating Data
UPDATE Employee
SET empname='Gopinath' WHERE empno=983678
--Deleting Data
DELETE Employee WHERE empno=237832
SELECT * FROM Employee

Lets check the tracking table

SELECT CASE __$operation
    WHEN 2 THEN 'INSERT'
    WHEN 3 THEN 'Before UPDATE'
    WHEN 4 THEN 'After UPDATE'
    ELSE 'DELETE' END '$Operation'
    ,empno,empname FROM cdc.dbo_Employee_CT

From the output you see that all the DML operations are captured. Operation field is expanded in case statement itself.

Testing DDL changes tracking

Now lets do some DDL changes and check for tracked changes.

ALTER TABLE Employee ADD Sex CHAR(1)

Lets check the tracking table

USE ChangeDataCapture
GO
SELECT * FROM cdc.ddl_history

Limitations

• TRUNCATE command doesnt work on tables that is enabled for CDC.

• DROP Command works fine with CDC enabled tables, so careful while using drop command on CDC enabled tables.

• No Auditing on users doing DML or DDL operations in the tracked table. Even change table or CDC related system tables doesnt any information on user performing DDL or DML operations on the tracked tables. To accomplish this you need to use Auditing feature in SQL Server 2008.