Tuesday, November 25, 2008

Creating two databases in sync...

We had this unique requirement of having two different tables in two different databases on two different DB servers in sync. (Too many differences huh...)
We created the same in a span of 2 days starting from scratch. We started with creating local triggers for Insert, Update and Delete which would dump the INSERTED / DELETED data in a temporary physical table on same DB server. Then we created stored procedures which would pull data from the remote temporary table and dump the same in local table. (Not the local temporary table.).

We used the Managed Application feature in SQL Server 2005 to run the stored procedure after a fixed interval of time. We did this to avoid updating data on remote Database directly using triggers.

Last but not the least the databases remain out of sync for the time indicated in Managed Application.


india said...

Nice work.
But could you please explore more on 3 things:
1)Objective of this Scenario.
2) Uses of this scenario
3)Process i.e Steps

Anonymous said...

Coool dude...

Abhijit Ranaware said...

Nice article. And welcome to the world of blogging!