Relational Database Systems

Change Data Capture

It is new feature in SQL Server 2008 that provides feature to track all data changes without writing triggers. CDC process works using transaction log. When table is enabled for CDC immediately after any changes made to table is available for tracking. Meta data change is also written along with the actual change.

Before we start looking into implementation, lets look at pre-requisition:

  •     SQL Server 2008 (Enterprise, Developer, and Evaluation Edition)

  •     Sysadmin permission required at database level to enable CDC

  •     dbo permission required to enable CDC at table level

  •     Primary Key required in table only when net change is required

Now, lets start how to implement it:

I have not used any particular database name or table name while writing step by step implementation guide. Hence please follow syntax pattern with actual name for value indicated within <> brackets

Step 1: Enable CDC at database Level

               

Step 2: Enable CDC at Table Level

           

           

You are all set to start using changed data tracked.

You may visit  http://technet.microsoft.com/en-us/library/bb510744.aspx for list of functions available for CDC

Implementation Overview

Conclusion

CDC is an excellent feature in SQL Server 2008 but it is very important to use it with proper planning. A lot many number of records will be added to the tracked table so ensure you have enough space to store changed data.

Leave a Reply

Your email address will not be published. Required fields are marked *

To Top