SQL Server 2008 Capture DML Changes Using Change Data Capture

SQL Server 2008 introduces a new feature called Change Data Capture (CDC). CDC Captures DDL & DML activities on a SQL Server Table, and places the changes in a separate SQL Server relational table. In this article we will see how CDC can help a DBA to track DML changes for SQL Server Tables. The CDC feature is by default disabled at the database level. A member of the sysadmin server role must enable a database for Change Cata Capture. Once the database is enabled for CDC any member of dbo fixed database role can enable the tables within the database for Change Cata Capture.

Overview of Change Data Capture
Once CDC is enabled at the database level, the next step will be to enable CDC for a specific table for which the change needs to be captured. The CDC feature gathers the changed data from the database transaction log file and inserts the change information in an associated change table which is created during the setup and configuration process of CDC. There is a one to one relationship between the source table and the change capture table. You can have a maximum of two change tables for a single source table. As the CDC feature needs to continuously read the transaction log, for the CDC to work SQL Server Agent should always be running.

How to Enable CDC for a SQL Server 2008 Database
1. Connect to the SQL Server 2008 Instance using SQL Server Management Studio
2. In the query window, type the following TSQL Query to create the ChangeDataCapture Database:

Use Master
Go

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'ChangeDataCapture')
DROP DATABASE ChangeDataCapture
GO

USE [master]
GO
Create Database ChangeDataCapture
Go

3. Once the database is successfully created you need to enable the CDC feature for the database, this can be done by executing the following TSQL Query:

Use ChangeDataCapture
Go
EXEC sys.sp_cdc_enable_db
GO



4. Execute the following query to check whether the database is enabled for CDC:

Select [name] as DBName, is_cdc_enabled from sys.databases

The value of 1 for the is_cdc_enabled column means that the database is enabled for CDC, and a value of 0 means that the database is not enabled for CDC.



5. Once the database is enabled for CDC, you could see the new cdc schema, cdc user, and a few metadata tables and other system objects created in the ChangeDataCapture database. The most important things which a DBA needs to keep in mind when enabling CDC for a database is to make sure that there are no cdc schema or cdc user in the database before configuring CDC. If there were a cdc schema or cdc user in the databases then the configuration of CDC will fail, so a DBA needs to remov or rename previously existing cdc schema or user from the database before configuring CDC.


How to Configure CDC for a SQL Server 2008 Database Table
1. Now let’s create the Currency Table in ChangeDataCapture Database by executing the following TSQL Query:

Use ChangeDataCapture
Go
Create table Currency
(
CurrencyKey Int Identity(1,1) Primary Key NOT NULL,
CurrencyAlternateKey varchar(5),
CurrencyName varchar(25)
)



2. Once the Currency table is successfully created, a DBA needs to make sure that the SQL Server Agent Service is running. In order for the CDC to be successful the SQL Server Agent should be running.

3. Enable CDC for the table Currency by executing the following TSQL Query:

Use ChangeDataCapture
Go
EXEC sp_cdc_enable_table 'dbo', 'Currency', @role_name = NULL, @supports_net_changes =1
Go



4. Execute the following query to check whether the table is enabled for CDC:

Use ChangeDataCapture
Go
Select [name], is_tracked_by_cdc from sys.tables
GO

The value of 1 for the is_tracked_by_cdc column means that CDC is enabled for the table and the value of 0 means that CDC is not enabled.



5. Once you have enabled CDC for the Currency table, another table is created for keeping changed data and the information about the changes in the source table. The new table created will have the cdc.dbo_Currency_CT name and the same is highlighted in the above image.

6. Let's insert a few rows to the Currency table:

Use ChangeDataCapture
Go
Insert into Currency Values ('AFA','Afghani')
Insert into Currency Values ('INR','Indian Rupee')
Insert into Currency Values ('JPY','Yen')
Insert into Currency Values ('USD','US Dollar')
Go
7. CDC captures any DML changes that happen to the Currency Table by logging these changes into the cdc.dbo_Currency_CT table.

Use ChangeDataCapture
Go
Select * from cdc.dbo_Currency_CT
Go



8. Now let’s update and delete some rows from the Currency table by executing the following TSQL Query:

Use ChangeDataCapture
Go

Update Currency
Set CurrencyName = 'AFGHANI'
Where CurrencyKey = 1
Go

Delete From Currency
Where CurrencyKey = 3
Go

9. Now if you run the select statement against the cdc.dbo_Currency_CT table you should see that all the changes to the Currency table are captured. You could also see the values between 1...4 being assigned against _$operations column. The translation for each of these values is outlined below:

_$operations = 1 Means row was Deleted
_$operations = 2 Means row was Inserted
_$operations = 3 Means value of row before update
_$operations = 4 Means value of row after update



Conclusion
The Change Data Capture feature helps DBA’s to keep track of all the Data Manuplation Language changes on a specific user table.