SQL Server Replication Overrides Data at Subscriber

Question

We need to replicate all the data from ProjectA and ProjectB databases to the Central database (All being hosted by different SQL Server instances). I have adopted the Central subscriber topology by creating transnational publications at PojectA and Project B; Moreover creating a subscription at the instance hosting the Central database. I made sure to chose in Article Properties "Keep existing object unchanged"; However the data of ProjectA are being overridden by the data from ProjectB at destination. What am I doing wrong?

Answer 1

The solution to the Central Subscriber Model is horizontal partitioning, using static row filters, and setting the Action if name is in use article property to Delete data. If article has a row filter, delete only data that matches the filter.

Horizontal partitioning

Ideally, published tables in a Central Subscriber topology will be horizontally partitioned. In order to horizontally partition the tables to be published, a location-specific column should be added and included as a part of a composite primary key.

Static row filters

For each article to be published in a Central Subscriber topology, a static row filter should be defined to leverage the Action if name is in use article property appropriately. A static row filter uses a WHERE clause to select the data to be published. To publish rows from Publisher 1, specify LocationID = 1 for the filter clause. Likewise, to publish rows from Publisher 2 and Publisher 3, specify LocationID = 2 and LocationID = 3 for the filter clause, respectively.

Action if name is in use

When creating the publications and adding articles, the article property Action if name is in use needs to be set to Delete data. If article has a row filter, delete only data that matches the filter. This can be set using the New Publication Wizard Article Properties dialog or by using replication stored procedures sp_addarticle and specifying a value of delete for the @pre_creation_cmd argument. This way, when the central subscriber is initialized or reinitialized from multiple publication snapshots, previously applied snapshot data will be preserved since only data matching the filter clause will be deleted.

I cover this in more detail in Central Subscriber Model Explained.