Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

migrated from stackoverflow.com Jan 16 '12 at 11:28

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 2 down vote accepted

I'd change the IDENTITY so one is odd, one even. Or -ve/+ve.

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.