Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
xalpha
Starting Member
13 Posts |
 Posted - 2012-06-11 : 07:44:42
|
| Hello,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.Thank youMatthias |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-06-11 : 08:52:28
|
| Replication is perfect for your scenario. You only have to publish the one table you care about. More details here: http://msdn.microsoft.com/en-us/library/ms151176.aspx |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-11 : 15:41:01
|
| 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
xalpha
Starting Member
13 Posts |
Posted - 2012-07-02 : 12:29:41
|
| 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.Thank you for the support!Matthias |
|
|
|
|
|
|
Sync only 1 Table to an other server