Sql-server – Merge replication Identity columns for already existing databases

merge-replicationsql server

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

Best Answer

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

Related Question