SQL Server 2008 Change Data Capture

SQL Server 2008 has a new Change Data Capture feature that allows you to track Inserts, Updates, and Deletes on a table. A lot of the features I’ve used in the past to detect changes are generally very expensive operations so I’m always looking for alternatives.  There are several caveats you need to know about before attempting to use CDC.

1.  You must be using either Enterprise, Developer, or Evaluation editions of SQL Server 2008

2.  You must have a sysadmin fixed server role

3.  You must have a db_owner fixed database role

4.  CDC can only be applied to user databases

5.  SQL Server Agent must be running

All of the stored procedures used for CDC can be found on msdn http://msdn.microsoft.com/en-us/library/bb500244.aspx.

Step One

CDC must be enabled on each database you decide to check for changes.  To enable CDC on a database using the following script:

Use AdventureWorks2008
exec sys.sp_cdc_enable_db
select name, is_cdc_enabled from sys.databases

The select statement shows which databases have Change Data Capture enabled.

Step Two

Create a table to test CDC:

Create Table dbo.CDC_Test
(ID int,
Description varchar (50))

Step Three

CDC must also be enable on each individual table:

exec sys.sp_cdc_enable_table 
    @source_schema = 'dbo', 
    @source_name = 'CDC_Test',
    @role_name = 'CDCRole'
select name, is_tracked_by_cdc from sys.tables

Here I’ve created a new role called “CDCrole” that didn’t previously exist.  This stored procedure has many parameters but I’ve only used the required ones here.  View all the other available parameters here http://msdn.microsoft.com/en-us/library/bb522475.aspx.  The select statement displays all the tables that have Change Data Capture enabled.  You may have also noticed that two SQL Agent Jobs were created (cdc.AdventureWorks2008.capture – watches for changes in transaction logs, cdc.AdventureWorks2008.cleanup – automates cleanup).

Step Four

Insert a new record into the test table:

INSERT INTO AdventureWorks2008.dbo.CDC_Test
    Values(1, 'CDC Test' )

Step Five

Update the record in the table:

UPDATE AdventureWorks2008.dbo.CDC_Test
   SET ID = 3
      ,Description = 'NewTest'

Step Six

Use the function created by CDC to return which columns are Inserted, Deleted, or Updated

DECLARE @from_lsn binary(10), @to_lsn binary(10)
SET @from_lsn =
SET @to_lsn   = sys.fn_cdc_get_max_lsn()
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_CDC_Test
  (@from_lsn, @to_lsn, N'all')

How is this useful?  The _$operation column is used to indicate the change (1 = Delete, 2 = Insert, 3 = Update with row filter option set to “all update old”, 4 = More typical Update).  So my example shows one insert and one update.

Step Seven

Use the following procedure to view all tables with CDC enabled.

exec sys.sp_cdc_help_change_data_capture

Step Eight

Disable Change Data Capture on table and database.  To disable just the individual table run the following:

exec sys.sp_cdc_disable_table 
  @source_schema = 'dbo', 
  @source_name = 'CDC_Test',

Run the following to disable CDC for the database:

exec sys.sp_cdc_disable_db

You don’t need to do both if you want to disable the table and database just run the disable database stored procedure.  You will also notice the SQL Agent Jobs are removed when the last procedure is ran.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s