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.

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)
)



2. Once the Currency table is successfully created, you need 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 below mentioned TSQL Query

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



4. Execute the query below 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 for is_tracked_by_cdc column means that CDC is disabled.



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 cdc.dbo_Currency_CT name as shown.

6. Next step will be to alter the Currency table to add the CurrencyName column:

Use ChangeDataCapture
Go
Alter table Currency add CurrencyName varchar(25)
Go

7. The DDL changes which have occurred to the Currency table can be viewed by executing the following TSQL Query:

Use ChangeDataCapture
Go
Select OBJECT_NAME(Source_Object_ID) As [Table Name],
OBJECT_NAME(Object_ID) As [CDC Table Name],
DDL_Command As [DDL Command],
DDL_LSN As [Log Sequence Number],
DDL_Time As [DateModified]
From CDC.ddl_history
Go



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