SQL 2008 Enabling Change Data Capture Made Easy

Ok I was looking all over the web, and I can into “older” pre-release version of these steps, then ran into problems that just didn’t really work if I copied the steps I read about.

So quick and dirty this is my step-by-step of setting up Change Data Capture or CDC for SQL 2008. A New feature by the way in SQL 2008.

Assumptions:

• Database is named DuFrainCashOut
• Table I plan to audit is called CashOut, owned by dbo. So dbo.CashOut
• All these commands are run against the database

Steps:

1. Enable your Database. (IF YOU GET ERROR, step 1.2.1)
1. Command:

exec sys.sp_cdc_enable_db

2. If you get an error: Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.
1. Run this command:

sp_changedbowner 'dbo'   

2. (Of course swap out dbo with whoever the owner is)
2. Turn on CDC for a specific table (You have to do each table)
1. Command:

EXEC sys.sp_cdc_enable_table 'dbo', 'CashOut', null, null, 1

Done. Now it’s up and running for that table.

Now you have to get the data out. When you ran the last command it created to tabular functions, fn_cdc_get_all_changes_dbo_CashOut and fn_cdc_get_net_changes_dbo_CashOut

You can select against these to get the data you need. I had a problem here because you have to use LSN numbers to get the data and I had problems getting accurate numbers. Anyway, this is what I came up with..

DECLARE @begin_time datetime 
DECLARE @end_time datetime 
DECLARE @begin_lsn binary(10)
DECLARE @end_lsn binary(10)

SET @begin_time = '2009-10-05 12:00:00.000'

SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @begin_time);
SELECT @end_lsn = sys.fn_cdc_get_max_lsn()
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_CashOut(@begin_lsn, @end_lsn, 'all update old');

I tried the same method with @begin_lsn for @end_lsn but I kept getting null’s and gave up. This will create you two sql jobs by the way for each table you CDC for.

This is what is returned:

Column name	Data type	Description
__$start_lsn	binary(10)	Commit LSN associated with the change that preserves the commit order of the change. Changes committed in the same transaction share the same commit LSN value.
__$seqval	binary(10)	Sequence value used to order changes to a row within a transaction.
__$operation	int	Identifies the data manipulation language (DML) operation needed to apply the row of change data to the target data source. Can be one of the following: 1 = delete 2 = insert 3 = update (captured column values are those before the update operation). This value applies only when the row filter option 'all update old' is specified. 4 = update (captured column values are those after the update operation)
__$update_mask	varbinary(128)	A bit mask with a bit corresponding to each captured column identified for the capture instance. This value has all defined bits set to 1 when __$operation = 1 or 2. When __$operation = 3 or 4, only those bits corresponding to columns that changed are set to 1.
<captured source table columns>	varies	The remaining columns returned by the function are the captured columns identified when the capture instance was created. If no columns were specified in the captured column list, all columns in the source table are returned.

Resources:

Enabling Change Data Capture

cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL)

sys.fn_cdc_map_time_to_lsn (Transact-SQL)