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.