February 1, 2012 at 5:27 am
I setup Peer to Peer Replication on two SQL2k8R2 m/c. To overcome identity conflict problem i thought to resolve it by setting even identity increment on server 1 (say emp table with id 1,3,5,..) and odd identity increment on server 2 (say emp table with 2,4,6,...). I then executed a sp to insert bulk data into 'emp' table at server 1 and server 2.
The result was :
On Server 1 (emp table) the count was - 1,2,3,4,5,... (Expected Result)
On Server 2 (emp table) the count was - 2,4,6....
Point of concern is data did not replicated from Server 1 to Server 2 and when i had a look at eventvwr it said the error was :
'A conflict of type 'Update-Update' was detected at peer 2 between peer 1 (incoming), transaction id 0x0000000000002b47 and peer 2 (on disk), transaction id 0x000000000000280f'
Any assistance will be highly appreciated.
Thanks much in advance.
February 1, 2012 at 5:38 am
A bit more..
Firstly, I did this POC with few inserts and was working fine.
Secondly, I have already set the 'Allow peer to peer conflict detection' and 'Continue replication after conflict detection' to True
Thanks much
PM
February 17, 2012 at 12:11 am
Positive..!! Just got over my problem that I describe in my earlier mail. I was just wondering the reason behind conflicts in replication when triggers and identity both comes into picture at same time.
Let me try to enlighten the set-up.
Terminology Used:
Publisher Table with suffix - P and Subscriber Table with suffix – S
Server 1 Server 2
Sales-P ID (1, 1) Sales-S ID (1, 1)
Trigger-P Trigger-S
Customer-P ID (1, 1) Customer-S ID (1, 1)
Requirement: When Sales-P is populated with data it should insert into Customer-P using Trigger-P and the same data should replicate across Sales-S and should fire the trigger-S which should in-turn insert data in Customer-S. Basically we are avoiding replication of Customer tables.
Problem: When we replicate both the tables with triggers disabled at subscriber, replication works fine. Now if we replicate only Sales-P table to subscriber with triggers enabled at subscriber, we land up with Identity error.
Fix: After some workaround, the way out to the thread appears to be…
•Mark ‘NOT FOR REPLICATION’ for Trigger-P as YES
•Mark ‘NOT FOR REPLICATION’ for Sales-S Identity property as YES
•Mark ‘NOT FOR REPLICATION’ for Trigger-S as NO
•Mark ‘NOT FOR REPLICATION’ for Customer-S Identity Specification as NO
Story Behind: Internally the story seems to be related to 'insert into select' statement in the trigger that expect to provide explicit values for the [id] column that is itself marked ‘NOT FOR REPLICATION’. So in the solution we are just marking publisher trigger ‘NOT FOR REPLICATION’, reverse for the triggers at subscriber and marking Identity for Customer-S for replication.
I would say that it is simply unfortunate that the delicate interactions between all these NFR settings are causing trouble. Finally the solution you get is effective….
Thanks Much,
NirvanASQL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply