Monday 22 October 2012

Implementing Change Data Capture Part 1 - Configure Database

I've finally gotten round to investigating Change Data Capture or CDC in SQL Server 2008. For ages it's seemed like this big unknown, but while studying for the SQL Server 2008 Developer MCITP certification I decided it would be a good time to dive in.

Surprisingly, Books Online (BOL), which I've always struggled using, has not only a good explanation of CDC but a great tutorial on setting it up. Plus the kind Microsoft people even take you through the implementation of an SSIS based incremental load. This was a great find, as one of the ways in which CDC really shines is when used as part of an ETL solution. There are other very useful applications, such as auditing data changes (not to be confused with SQL Server 2008's Audit feature), but I'll focus on the ETL application here.

For more in-depth information about what Change Data Capture is and how it works see the BOL entry 'Basics of Change Data Capture'.

What I'd like to do over the next few posts is share a few scripts I've come up with to streamline the implementation of CDC. While the basics are very easy to implement, it's the SSIS package(s) that could get rather tedious to build without some generic scripts.