2

could you please suggest me the way I could automatically resolve primary key conflicts during a merge between Publisher and Subscriber. It seems Sql Server doesn't do it out of the box :(.

Conflict viewer shows me next message:

A row insert at '_publisher_server_' could not be propagated to '_subscriber_server_'. This failure can be caused by a constraint violation. Violation of PRIMARY KEY constraint 'PK_PartPlan_FD9D7F927172C0B5'. Cannot insert duplicate key in object '_table_name_'.

Thank you.

spkenny
  • 448
  • 5
  • 15

3 Answers3

1

This isn't an easy solution (since you've presumably already designed your database with auto-incrementing int keys), but using GUID ("uniqueidentifier") for your primary keys will solve your PK collision problem.

MusiGenesis
  • 74,184
  • 40
  • 190
  • 334
  • Or, better yet, choose a primary key that's unique by definition. – Vladislav Zorov May 11 '11 at 12:24
  • can't do this because of historical reasons :( – spkenny May 11 '11 at 12:27
  • @Vladislav: "natural" primary keys are nice when they exist, but in my experience they're pretty rare (and often things that seem like ideal natural keys end up having duplicates anyway, like Social Security number, e.g.). – MusiGenesis May 11 '11 at 13:31
  • @user68923: if you can't use GUIDs, then you need to set up identity ranges on the subscribers. Identity ranges ensure that each subscriber adds new records with PK values unique to the overall system. See: http://msdn.microsoft.com/en-us/library/ms146907.aspx – MusiGenesis May 11 '11 at 13:37
  • Your only real alternative to GUIDs and identity ranges is to programmatically work through the conflict viewer messages, reassign PK values for records that conflicted, and re-synchronize. But trust me, you *really* don't want to do that sort of thing. – MusiGenesis May 11 '11 at 13:39
  • Merge replication automatically creates GUID columns in each row. It can be dangerous to use this as the PK without more planning and additional code changes. One notable reason is because of the `INSERT` slowdown caused by using non-sequential GUIDs as a clustered index. – Matthew May 11 '11 at 17:35
1

Have you tried WHEN MATCHED THEN and WHEN NOT MATCHED BY TARGET THEN to do an UPSERT (conditional UPDATE or INSERT)?

Documentation can be found here.

I'm assuming the primary key represents the same item in both DBs.

Vladislav Zorov
  • 2,998
  • 19
  • 32
  • Hmm, I'm don't have good exp. with replication. Could I somehow run the code above when PK conflict arise? If I could write some kind of automatic resolver that could handle this types of conflicts it would be great (T-SQL or C#). I know about custom resolvers, but could they handle this conflict? Maybe someone did this before? – spkenny May 11 '11 at 12:47
  • Maybe http://msdn.microsoft.com/en-us/library/ms147911.aspx ? You can then implement the `InsertErrorHandler`. – Vladislav Zorov May 11 '11 at 13:13
0

The easiest way I have solved this problem using autonumer PK's is to change the autonumber increment from 1 to 10 (or 100, or 1000, whatever is required) then set the seed differently on all the participants.

So, I may start seeds:
DB1 at 1
DB2 at 2
DB3 at 3
...
DBn at n (n < increment)

For example: An increment of 100 will yield PK's for DBs:
DB1: ** 101, 201, 301...
DB2: ** 102, 202, 302...
DB3: ** 103, 203, 303...
No matter how many rows are INSERTed they will always have unique PKs because the final digits reflect the particular database.

This method can be adapted as needed for your number of subscribers, they will never collide, and you have the added benefit of knowing the point-of-origin just given your surrogate key.

For existing tables, just reset the PK seeds and intervals by script. It should be very easy to do.

You could use a GUIDE PK but using a GUID can be quite problematic as a primary key, particularly if you don't also remove it from the clustered index. They are larger as well and you may already have code depending on integers.

When you create merge replication, SQL Server automatically creates GUIDs that it uses to track changes, but that doesn't mean they need to be PK's

Matthew
  • 10,244
  • 5
  • 49
  • 104