Monday, March 12, 2012

P2P replication

Hi,
I understand that P2P replication does not do automatic conflict detection.
I do not have conflict scenarios in my application except for the following
single requirement:
For a test plan i will add cases and will give numbering like case 1, 2, 3,
4 etc.
Any new case added to a plan will have the number as max(number) for the
plan + 1.
There are 4 servers with P2P replication setup. Consider the below scenario
1) Assume that max number for the plan at second t1 was 4.
Server1 adds new case. Data gets committed in DB server1 as 5. User gets
success message.
In the same instant server2 adds new case, data gets committed in DB server2
as 5. User gets success message.
Due to replication lag, it will take couple of section for data in db
server1 to reach dbserver2.
When data synchronization happens there are two 5's and this will cause
conflict.
How will this kind of conflict during replication be handled in
1) Merge Repln
2) P2P repln
I want to go with P2P here as there is only one less probable conflict
scenario to get the better performance that P2P offers. Any suggestion how
this will behave or should be handled in P2P.
Thanks,
Lalitha
Lalitha,
in merge the selected conflict resolver will run and you'll get a message
like this: "The row was inserted at 'computer1.Sub1' but could not be
inserted at 'computer2.Northwind'. Violation of PRIMARY KEY constraint
'PK_PublicationName'. Cannot insert duplicate key in object 'tyourtable'."
In P2P you'll have to modify the stored procedures to avoid an error which
will stop the distribution agent from running, or use the SkipErrors
parameter to avoid it.
However, if the PK is PlanID and a geographic identifier, you'll have the
data partitioned and no resulting PK errors.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

No comments:

Post a Comment