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.
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.
Create a table to test CDC:
Create Table dbo.CDC_Test (ID int, Description varchar (50))
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).
Insert a new record into the test table:
INSERT INTO AdventureWorks2008.dbo.CDC_Test (ID ,Description) Values(1, 'CDC Test' )
Update the record in the table:
UPDATE AdventureWorks2008.dbo.CDC_Test SET ID = 3 ,Description = 'NewTest' WHERE ID = 1
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.
Use the following procedure to view all tables with CDC enabled.
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:
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.