Capture DDL Changes using Change Data Capture with SQL Server 2008

SQL Server 2008 introduces a new feature called Change Data Capture (CDC). CDC Captures DDL and DML activity 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 DBA's to track the 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 CDC. Once the database is enabled for CDC any member of the dbo fixed database role can enable tables within the database for Change Data Capture.

Overview of Change Data Capture
Once the CDC is enabled at the database level, the next step is to enable CDC for a specific table for which the changes need to be captured. The CDC feature gathers the changed data from the database transaction log file and insert 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 that exists between the source table and the change capture table. You can have maximum of two change tables for a single source table. As the CDC feature needs to continuously read the transaction log file it’s obvious that for the CDC to work SQL Server Agent should be always 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 a 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 Change Data Capture 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 query below 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 CDC is not enabled.



5. Once the database is enabled for CDC, you could see new cdc schema, cdc user, few metadata tables and other system objects getting 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 were no cdc schema or cdc users existing in the database before configuring CDC. If there were any cdc schema or cdc users in the databases then the configuration of CDC will fail, so a DBA needs to remove or rename the previously existing cdc schema or users from the database before configuring CDC.