2

I'm attempting to setup merge replication with Pull subscriptions (SQL Server 2014 for Subscribers and 2016 for the Publisher). I have a number of databases at remote sites which operate independently, and have been operating independently for years, and we have never had a central server (Publisher) where all remote site data rolls up.

The database schema is essentially the same at all sites (it's the same application after all). What this also means is that, since all DBs have been operating independently, there is definitely going to be duplication of values for Identity columns between Subscriptions (though the actual row data itself will be site specific). Say, Subscriber A Table X has ID column 25 with row data (a,b,c,d) and Subscriber B Table X has ID column 25 with row data (e,f,g,h).

I've been reading up on automatic identity range management, etc., which is all fine if I'm setting up brand new subscriptions. However, here I am inheriting a bunch of Subscriber databases and I have to setup replication so that the data is aggregated at a central location. Which means I am inheriting duplicate identity column values across subscribers. Auto range management is fine for new rows, but I'm not sure what is the best way to manage the identity conflicts that come with the existing databases.

Any help will be greatly appreciated!

Regards, Vikram

Improve this question

1 Answer 1

Reset to default
1

Vikram,

If your remote sites are named unique, then you can utilize HOST_NAME() as a FILTER while creating a Merge Publication, we are doing the same at our place.

We have 2000 stores across USA, which we replicate (via Merge Replication) to our home office. All these stores have same schema across the board, so table ID is going to be duplicated across the board. To overcome the duplication issue, we utilized followings columns in our Replicated Articles:

  1. Table ID
  2. RowGUID (It will be unique per server at your remote location)
  3. SourceStore (this is filter for HOST_NAME(), which will make record unique at the home office (i.e. Central Location)

Example:

-Subscriber A Table X has ID column 25 with row data (a,b,c,d,RowGUID,Host_Name())
-Subscriber B Table X has ID column 25 with row data (e,f,g,h,RowGUID,Host_Name())

Hope this helps with your issue.

Thank you, HP

Improve this answer
2
  • Hi HP, Thanks for the inputs, appreciate the quick response. Quick question - Yes, the row would be unique at the central location with the column combination, but will there not be a problem inserting the record at the center, because the identity column value would still be the same with NOT FOR REPLICATION on? So I will be attempting to insert separate unique rows (with the combination of row GUID and host name), but with same ID column values. NOT FOR REPLICATION is needed because the ID column is referenced in many other tables for joins. Commented Jun 28, 2017 at 3:17
  • No, there should not be any issue at your central location as records from two different sites will become unique with the Host_Name in it. Ex: 1, John, RowGUID, Server1 1, Steve, RowGUID, Server2 As you can see, eventhough the ID column is same (i.e. 1) row at central location is still unique with RowGUID and HostName. Commented Jun 28, 2017 at 20:47

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.