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
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.