locked
Duplicate records causing issues RRS feed

  • Question

  • Quick overview of the three databases involved.  I have one central database which holds all of the data from the two downstream database.  The two databases should have a unique subset of the data.  I have one of the downstream databases complaining that 233 records from the publisher (downstream database) already exist on the central machine.  My two questions are will this block all updates from the downstream DB to the central until this is cleared.  And two, what is the best way to fix the issue?
    Tuesday, May 1, 2012 6:04 PM

Answers

  • I guess the best way to handle this would be to remove the records from the subscriber and let the publisher insert them again? 
    Yes.  Another option would have been to do a subscription sync_type of replication support only.  This option assumes that the subscriber already has the schema and initial data.

    Brandon Williams (blog | linkedin)

    • Marked as answer by Iric Wen Wednesday, May 9, 2012 1:48 AM
    Wednesday, May 2, 2012 4:22 PM
  • So replication is smart enough to know that the records don't exist on the subscriber and resend them or are we saying that since the publisher is trying to send those records anyway, let's make it so the inserts are successful (by removing the records causing the issue.  If I removed a few extra from the subscriber, those won't be resent correct?

    He is stating the latter, "that since the publisher is trying to send those records anyway, let's make it so the inserts are successful (by removing the records causing the issue)". That is the safest way to do that. 

    With that, you only want to remove the records that replication is attempting to send so that you don't end up in an out of sync condition. 


    David

    • Marked as answer by Iric Wen Wednesday, May 9, 2012 1:48 AM
    Friday, May 4, 2012 8:16 PM

All replies

  • What kind of replication are you using?

    This shouldn't be blocked but typically primary key conflicts cannot be resolved automatically.

    You will need to manually remove these 233 records on either the Publisher or Subscriber.


    Brandon Williams (blog | linkedin)

    Tuesday, May 1, 2012 10:20 PM
  • Thanks Brandon, I didn't set up the replication job but I instructed the DBA to user transactional replication.  I want the 233 records to exist on both the publisher and subscriber.  I guess the best way to handle this would be to remove the records from the subscriber and let the publisher insert them again? 
    Wednesday, May 2, 2012 3:43 PM
  • I guess the best way to handle this would be to remove the records from the subscriber and let the publisher insert them again? 
    Yes.  Another option would have been to do a subscription sync_type of replication support only.  This option assumes that the subscriber already has the schema and initial data.

    Brandon Williams (blog | linkedin)

    • Marked as answer by Iric Wen Wednesday, May 9, 2012 1:48 AM
    Wednesday, May 2, 2012 4:22 PM
  • So replication is smart enough to know that the records don't exist on the subscriber and resend them or are we saying that since the publisher is trying to send those records anyway, let's make it so the inserts are successful (by removing the records causing the issue.  If I removed a few extra from the subscriber, those won't be resent correct?
    Wednesday, May 2, 2012 6:47 PM
  • So replication is smart enough to know that the records don't exist on the subscriber and resend them or are we saying that since the publisher is trying to send those records anyway, let's make it so the inserts are successful (by removing the records causing the issue.  If I removed a few extra from the subscriber, those won't be resent correct?

    He is stating the latter, "that since the publisher is trying to send those records anyway, let's make it so the inserts are successful (by removing the records causing the issue)". That is the safest way to do that. 

    With that, you only want to remove the records that replication is attempting to send so that you don't end up in an out of sync condition. 


    David

    • Marked as answer by Iric Wen Wednesday, May 9, 2012 1:48 AM
    Friday, May 4, 2012 8:16 PM