I have 1 Table that should be replicated between 2 different 2008 servers. Changes are only made in server A but I need to hold a copy in server B. What is the best practice?
I have 3 solutions in mind:
1) Use a trigger on server A. Problem: I have no DTC on Server A and I would not be easy to get it because the server is controlled by other an other administrator. In the moment I have only read permission.
2) Replication between the Servers. Problem: For only one table it could be a bit oversized.
3) Scheduled Task on server B (maybe every hour) that uses a UNION-Statement to sync the tables. Problem: Not perfectly proper and maybe bad performace. And there will be a quite big delay.
Does anyone have an other smart idea? I'm curious about a clever solution for such a problem.
whats the frequency of changes happening in ServerA table? is it a transactional or analytical system? how quickly you want changes to be reflected in other table? whats average amount of DML operations happening in table on a day?
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
Ok, my laptop was broken the last time but now I have so report my final solution. The table on Server A is a logging table and gets 4-6 new rows per minute. The changes should be reflected in the other table at least in 1-2 minutes.
First I tried a replication. It was perfect but the administrator of the other server had a problem. He makes some dirty things (I don't know exactly but I think a truncate or rename job every few weeks). Finally I had to disable the replication. Now I have a little time scheduled script at the Server A. Every minute it makes a select over all items with a newer ID as the last and commit the rows to the new server. Not the best solution but it works proper since a few days.
Sync only 1 Table to an other server
Posted - 06/11/2012 : 07:44:42
