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.
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:
Post a Comment