Using Sql Server 2008. Pretend I have a publisher and 2 subscribers A and B. The replicated table has a guid on it and also a primary key that is not the guid. Frequently, updates happen at both subscribers near simultaneously generating 2 different guids but the same primary key. Then when they go to syncronize I get primary key violation errors.

Thinking of ways to resolve this - having the guid be generated based on the primary key or having replication use the primary key instead of guid (that seems hard/silly) or as a last resort have replication just continue on and ignore those violations.

Which route should I take?


I'd change the IDENTITY so one is odd, one even. Or negative/positive.

Probably the simplest way to do it...

Edit: I assumed identity PK columns.

I'd suggest you update the question with the relevant info contained in the comment. It's incomplete otherwise.

You said "The desired behaviour is that after the merge all rows are identical. The first one to the publisher wins..."

This sounds like conflict resolution, which is how you'd deal with PK collisions.

  • This isn't an replication/identity problem. The primary key is a composite of two varchar columns - there is no way to change this. There can be N subscribers. Frequently the subscribers will all add an identical row simultaneously. Identical in terms of the PK not the guid since that's randomly generated. The desired behaviour is that after the merge all rows are identical. The first one to the publisher wins and all other subscription clients then delete their row with the same PK and update to the row from the subscriber that won. I don't want dupped PKs distinguised by IDENTITY col – Gary Dec 17 '09 at 22:55
  • @gbn would you suggest this approach over msdn.microsoft.com/en-us/library/ms152543%28v=SQL.100%29.aspx and why? – Seph Jan 16 '12 at 6:16
  • @Seph: I'd do both. They are different concepts. My answer is about generating unique keys per server. Your link is about having the behaviour (trigger, IDENTITY, whatever) not run on the subscriber. You can't have 2 servers with the same IDENTITY step and start. – gbn Jan 16 '12 at 7:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy