none
Identical Rows causing conflicts

    Question

  • I have a merge replication job that has a record on the publisher and a record on the subscriber. I am getting a conflict:

    A row insert at 'Publisher.CustomersMasters' could not be propagated to 'Subscriber.CustomersMasters'. This failure can be caused by a constraint violation.  Violation of PRIMARY KEY constraint 'PK_Customers'. Cannot insert duplicate key in object 'dbo.Customers'.

    I have done a union all on the publisher's and the subscriber's record and they are identical in every column.  I have no idea how to fix this.  There are over 1,000 conflicts so far and most of them are just like this one.  I have recently dropped all subscriptions and rebuilt them hoping to get rid of all conflicts.

    We have spent lots of money with outside vendors to help with this but, so far, they are no help at all.


    Steve

    Monday, March 31, 2014 6:12 PM

All replies

  • The publisher and subscriber records involved with the PK violations are identical with the exception of the rowguid column.  The rowguid columns should be different.

    Primary key conflicts/violations will not be resolved automatically. You will need to manually delete one of the offending rows to get the primary key conflicts from reoccurring on every synchronization.

    If you expect primary key conflicts to occur regularly then you may want to consider doing one of the following:

    1. Horizontally partition your data by adding a location specific identifier column and extend the primary key(s) to include this column. Then have subscriber applications only insert rows belonging to their partition.

    2. Set the Merge article property @compensate_for_errors to true which sends a compensating change to the source replica to undo the failed change when a primary key conflict occurs.


    Brandon Williams (blog | linkedin)

    Monday, March 31, 2014 6:21 PM
    Moderator
  • The rowguid is also identical. Both are running SQL Serve 2005


    Steve

    Monday, March 31, 2014 6:24 PM
  • Is your data partitioned?  Is it possible that your application is inserting the same primary keys at the Publisher and Subscribers?

    To find the root cause you may need to perform some auditing.  I have an example here:  www.sqlrepl.com/sql-server/auditing-changes-in-merge-replication/


    Brandon Williams (blog | linkedin)

    Monday, March 31, 2014 6:30 PM
    Moderator
  • In this particular job, we are not using a partition. All customers get merged with all subscribers. If you are a customer in Memphis and go to San Antonio, your data is there, too.  The "app" only updates the subscriber but, occasionally we update the Publisher.  I won't go into the disaster we just went thru with replication but, lets just say that I had to do a lot of work to synchronize the publisher and subscribers.  In the process, I would pull data from the subscriber that did not exist on the publisher.  I'd put it into a temporary table and then move records into the publisher and then delete them from the subscriber.  Then I rebuilt the subscription.  It is certainly possible that these 1,000 records were pulled up and not deleted from the subscriber.  My concern is, if I delete the record from the subscriber, won't that delete it from the publisher, too?  isn't that what merge replication is supposed to do?


    Steve

    Monday, March 31, 2014 6:54 PM
  • My concern is, if I delete the record from the subscriber, won't that delete it from the publisher, too?  isn't that what merge replication is supposed to do?

    Yes, you need to be concerned with that, especially if the rowguid column values are exactly the same for the conflicting rows in question.

    Can you post a screenshot of the Conflict Viewer for a given primary key conflict please?  Also, please expand the rowguid column so that it is completely visible.  You may want to blur out any confidential information.


    Brandon Williams (blog | linkedin)

    Monday, March 31, 2014 8:22 PM
    Moderator

  • Steve

    Monday, March 31, 2014 10:07 PM