Monday, December 1, 2008

CDC in SQL Server 2008

CDC - Change Data Capture, it will capture the changes on both data and schema in a server. This is a new and powerful facility provided by Microsoft in SQL Server 2008. This feature is very useful when the developer is working with many servers.
Say for example a developer DevA is working with the server Srv1 and Srv2. He wants to pull the records from the Srv1 to Srv2. There is a table called tab1 is available in Srv1 and he wants to pull the records from the tab1 to Srv2 table tab2 for reporting purposes.
In SQL 2005:
Consider both servers (Srv1 & Srv2 ) in SQL 2005. The developer written a job from Srv2 to pull the full records from tab1 in Srv1. Every time he is truncating the table tab2 in Srv2 and pushing all the records from the tab1 in Srv1.
Drawbacks:
  • It takes more time to pull all the records from tab1.
  • If something happens while pulling there is no records are available in tab2 (Srv2) because of truncation.
In SQL 2008:
Consider both the server (Srv1 & Srv2) are in the SQL Server 2008. In SQL Server 2008 we have a facility to track a table what are the changes done for the data and schema. There is a tracking table is maintained on each table which keep tracks of the changes in the table. If you enabling the CDC for the tab1 in the Srv1 which will keep track the changes on the data in a seperate table.
From the tracking table the developer can easily find the which records is inserted or updated or deleted newly in the table tab1. With the help of the tracking table, the developers can easily pull the countable records from the table tab1(Srv1) to the table tab2(Srv2).

I hope it will helpful for you guys... If you not clear Please give your feedback...








No comments: