Playing with CDC in Katmai

Change Data Capture (CDC) is something DBAs around me have been screaming about for ages. For auditing and other reasons, we have needed to rely on 3rd party tools from companies like Lumigent, whose motto for their auditing product previously named Entegra went something like, "Who did what to which data when?" It's a slogan I don't think I'll ever forget, and not just because I have it plastered on a T-Shirt I grabbed at PASS or SQL Connections that year…

Much to the delight of SQL Server DBAs everywhere (and surely to the chagrine of 3rd party vendors like Lumigent), there is now CDC functionality coming in Katmai (sorry, SQL Server 2008), and you can play with it in the June CTP. (If you haven't read in one of the hundreds of blog posts on this site and elsewhere, the June CTP is available here.)

EXEC sp_cdc_enable_table_change_data_capture 'dbo', 'Test', @role_name = NULL;— there are many more options to this procedure; this is the default— behavior, including capturing all columns in the table.GO

Now, in two out of three tries from scratch, I've found that I needed to restart SQL Server Agent for data changes to start being captured (I filed a suggestion on Connect). So, to be sure, before we put any data into the table, let's go to SQL Server Agent in Object Explorer, expand jobs, right-click "cdc.CDCTest_capture" and choose "Start Job at Step…" and get the job running once. Now:

In order to capture the data changes, there is a new table created in a schema called 'cdc', named <schema>_<table>_CT. In this case, it contains all of the columns from the table, as well as other information including pointers to the log and the type of operation that was captured.

For the __$operation column, 1 is a delete, 2 is an insert, 3 is the before image of an update, and 4 is the after image of an update. (SELECTs are not captured.) So, we can see my initial two inserts there in the first two rows, and then the last two rows show the before and after image of the update (this is just like the deleted and inserted columns available within a DML trigger).

[An important item to note here is that if you make schema changes to the table, you need to disable CDC at the table level and then re-enable it in order to capture changes to the new columns. This "feature" is documented but I don't think it will be very intuitive.]

This gives you a bit of information, but for me, it wasn't quite enough. What if I want to track (easily!) what user performed that update, changing 'bar' to 'splunge', and when it happened? Well, I created my own logging table, with a representation of the three columns from the unique index on dbo_test_CT, and two additional columns (username and eventDate):

[Note that change data is only kept around for 72 hours by default. This retention time is stored in minutes (4320) in the msdb.dbo.cdc_jobs.retention column. I've had reasonable (but not perfect) success in changing this value and having the cleanup job obey.]

Now, you can make the trigger get more information, e.g. host_name from sys.dm_exec_sessions or client_net_address from sys.dm_exec_connections. I just wanted to open the door.

Note that I did not stress test this solution… not that a stress test on an early CTP would be very realistic, anyway. I'm just playing around here, so far at least. 🙂

About the Author

9 comments on this post

Krishna - April 14, 2009, 12:53 AM

This is not working for me as SUSER_SNAME() inside the cdc trigger is sa not the user making the data change. Is there any config setting I am missing?

Krishna - April 14, 2009, 12:53 AM

This is not working for me as SUSER_SNAME() inside the cdc trigger is sa not the user making the data change. Is there any config setting I am missing?

Krishna - April 14, 2009, 12:53 AM

This is not working for me as SUSER_SNAME() inside the cdc trigger is sa not the user making the data change. Is there any config setting I am missing?

Krishna - April 14, 2009, 12:53 AM

This is not working for me as SUSER_SNAME() inside the cdc trigger is sa not the user making the data change. Is there any config setting I am missing?

Krishna - April 14, 2009, 12:53 AM

This is not working for me as SUSER_SNAME() inside the cdc trigger is sa not the user making the data change. Is there any config setting I am missing?

ShanDBA - September 30, 2009, 4:45 PM

Krishna,
Use System_User instead of SUSER_SNAME().

davetiyeci - November 18, 2009, 5:16 PM

düğün davetiyesi ve davetiye sözleri

davetiyeci - November 19, 2009, 12:20 PM

düğün davetiyesi ve davetiye sözleri

vamsi - May 20, 2014, 10:05 PM

when i try to enable cdc on a table i get the below error. i tried making db_owner as sa, granted view server state permission but still i get the below error.
EXECUTE sys.sp_cdc_enable_table
@source_schema = N'dbo'
, @source_name = N'CDC_test1'
, @role_name = Null
–@supports_net_changes = 1
GO
Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 607
Could not update the metadata that indicates table [dbo].[CDC_test1] is enabled for Change Data Capture. The failure occurred when executing the command 'sp_cdc_create_change_table'. The error returned was 297: 'The user does not have permission to perform this action.'. Use the action and error to determine the cause of the failure and resubmit the request.
Msg 266, Level 16, State 2, Procedure sp_cdc_enable_table_internal, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 3930, Level 16, State 1, Procedure sp_cdc_enable_table, Line 61
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 266, Level 16, State 2, Procedure sp_cdc_enable_table, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.