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.  View this blog with screenshots at my regular blog http://blogs.pragmaticworks.com/devin_knight/.

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

           (ID
           ,Description)
    Values(1, 'CDC Test' )

Step Five

Update the record in the table:

UPDATE AdventureWorks2008.dbo.CDC_Test

   SET ID = 3
      ,Description = 'NewTest'
 WHERE ID = 1

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 =
   sys.fn_cdc_get_min_lsn('dbo_CDC_Test')
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',
  @capture_instance='dbo_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.