SQL Server Performance Forum – Threads Archive
Merge Replication Issue
hello everyone,i am not too familiar with Replication and i wonder if i could get some advice. i established merge replication between 2 servers A and B.
The publication and distributor were set on server A (using a push subscription). Each of these servers have people that add/update and delete records. And the updates made to one server is replicated to the other,so both database have the same data. i tried to simulate what happens if merge replication is down.. so, replication was "paused" and people continued to add/update records to both server A and B databases..
when replication was "enabled" only the updates made on server A were replicated across, so all the updates made to server B Database while replication was down, were "overwitten". All new records created by users on server B dissapeard.. what i hoped to see is the replication agent on both servers would "remember" who did what and merge all these updates (with precedence on who did what first) once replication was back on. Is this possilbe?
If yes how can i do this?
DO i need to make any database table changes (for example on all tables my Primary keys are intergers with identity set to "yes" to auto increment)? (i hope i explained this right..) thanks in advance
Are you getting any error after this event, if so what was that? Set of KBAs for reference about Merge Replication:
KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;307356 about Merge article processing order.
http://support.microsoft.com/default.aspx?scid=kb;en-us;308266
3
HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
I too have the same problem and I notice it has been a month or so since your post and was wondering if you have had any luck. Basically what I have discovered is that for merge replication you simply cannot use integers and identity increments as they do not resolve. I honeslty would have thought that using the GUIDs the system would be intellegent enough to apply a new primary keyu and cascade update the foreign keys intelligently. Even setting one DB to start autoincrementing from 1 and the other from say 10000000 and ensure that until the DBs have 10 million records you won’t have an issue does not work. Merge replication wants both DBs to have the same identity seed. Seems the only issue is to use GUIDs as primary keys or simply do not let SQL do it for you and manually set one DB to increment in one way and the other in another. Merge replication should be smart enough to solve this problem using the conflict resolver and customising the triggers but I have not figured that one out yet. Any further advice would be greatly appreciated.
Can you check for duplicates in the rowguid column – another poster had this issue when he copied a row back into the same table. If there are any duplicates, then they’ll need to be removed – either you can delete the row or drop then readd the rowguid column. Also refer the errors during the process for assessment. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
hello gents, sorry for not replying back, and i thank you for your help i did find a solution to this problem but not sure it is the best way of doing it i changed the primary key from an int to a Uniquidentifier (isrowgrid=yes). Up to this point the data in the table on both servers had the same data. then i changed the sa account on one server to create an replication error, and added/updated different data on both machines and when i corrected the error and re-established replication, all updates made by users on both servers where applied and synchronised by the conflict resolver! all records that i added on each server did come through and where merged. However using uniqueidentifiers has a negative impact on performance, unless you have a powerfull server. try it out cheers
Bill13
]]>