Overlapping data on a merge replication between three SQL Servers

Tags:
Merge replication
Primary keys
We have a merge replication between our three SQL servers at three different locations. One is the publisher and two are subscribers, and we have a primary key on a table (a unique field with is auto-incremental by one). On the publisher’s server, if the user creates a new order, the table assigns it a number (e.g. 1000 in the unique field). At the same time, a user enters a new order in the subscriber server, which is also assigned a number (e.g. 1000 in its unique field). When the merge replication takes place, though, the unique number has already been assigned on the publisher server and details for order no. 1000 will not be updated on the publisher (due to primary key constraints). How can we resolve this situation and allow both the subscriber and the publisher order number to be replicated without creating any conflicts?

Answer Wiki

Thanks. We'll let you know when a new response is added.

This is one of the potential issues when using merge replication. Within the Publication properties you should be able to set the identity range size. When merge replication fires up it assigns a range of IDs to each server. These IDs are the ranges which should be used by each server when new records are added to the table. If you run out of IDs in these ranges the SQL Server will fall back to using the normal IDs to use.

How often do you run a merge between the machines?

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following