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:

Resources:

Enabling Change Data Capture
cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL)
sys.fn_cdc_map_time_to_lsn (Transact-SQL)