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




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