The majority of SQL Server Replication topologies are based on the Central Publisher model, which is comprised of a single publisher replicating to one or more subscribers. Another replication model, which is sometimes overlooked, is the Central Subscriber model, which is comprised of multiple publishers replicating to one subscriber using Transactional Replication.
The Central Subscriber model is useful for rolling up or consolidating data from multiple sources. Some examples include:
Rolling up inventory from several warehouses into a central server at corporate headquarters.
Sending data from remote offices within a company to a central office for business continuity.
Consolidating order information to one location for centralized ordering.
By default, subscriptions are initialized from a snapshot generated by the Snapshot Agent and then applied by the Distribution Agent. When the snapshot is applied, by default the article property Action if name is in use is set to Drop existing object and create a new one, which instructs that the destination table be dropped if it already exists at a subscriber. This behavior can be problematic in the Central Subscriber model when snapshots are applied since snapshots must be applied from multiple publications. The first snapshot is applied as expected, however, subsequent snapshot applications result in the previous snapshot data being wiped out.
The solution to this problem is horizontal partitioning, 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.
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. Consider a table that looks like this:
To horizontally partition TestTable and prepare it for a Central Subscriber configuration at Publisher 1, drop primary key constraint PK_TestTable_ID, add a location-specific column named LocationID with a default value of 1, and add the new composite primary key including the LocationID column.
Next, to horizontally partition TestTable and prepare it for a Central Subscriber configuration at Publisher 2, the same preparation can be done with a default value of 2 for LocationID.
Finally, to horizontally partition TestTable and prepare it for a Central Subscriber configuration at Publisher 3, the same preparation can be done with a default value of 3 for LocationID.
Once the tables are horizontally partitioned, they can be properly published in a Central Subscriber topology by using static row filters, filtering on the LocationID column and setting the article property Action if name is in use to Delete data. If article has a row filter, delete only data that matches the filter.
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 usearticle 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.
