Horizontally Partitioned Merge Publications

[Sunite]

Horizontally Partitioned Merge Publications

 

Q

Why does data disappear from the subscriber when you run a horizontally partitioned merge publication?



A


Data that seems to "disappear" has been a problem since the release of merge replication in SQL Server 7.0. Merge replication gives you the ability to process a transaction on a subscriber database and have the data propagate back to the publisher database. Such reverse propagation presents a unique problem when you deal with a horizontally partitioned publication.

When you apply a horizontal partition, you essentially add a WHERE clause to a query. This WHERE clause determines which table rows to send to a particular subscriber. Suppose you split a contact database for your mobile salespeople so that each mobile salesperson's database corresponds to the regions for which that salesperson is responsible. For example, if Joe Smith were responsible for the Eastern region, his database would contain only Eastern region contacts. The horizontal partition enforces this restriction.

Now, suppose that the regional responsibilities change and Joe becomes responsible for the Southeastern region. Joe's database contains data for the Eastern region, which conflicts with the horizontal-partitioning rules. At the next merge, Joe will receive all the Southeastern region contacts so that his database complies with the partitioning rules. However, now Joe has data from both the Eastern and Southeastern regions, creating a conflict.

Merge replication prevents you from having data that violates your partitioning rules. Therefore, when the merge job sends the Southeastern region data to Joe, it also removes the Eastern region data from Joe's database. This resolution causes an apparent loss of data, and resolving the conflict creates a significant amount of network traffic whenever the partitioning rules change.

When data conflicts with the partitioning rules, SQL Server removes that data to comply with the partition. However, SQL Server does not restrict the data that enters the database. Joe can add a new contact outside his region. SQL Server saves this data in Joe's database, but the data does not remain there after the next merge. Thus, users can appear to lose data from their databases, but SQL Server has not actually lost the data. SQL Server stores that data in the publisher database and eliminates any data from the subscriber database that does not match the horizontal filter.

In SQL Server 7.0, if you make any changes at the subscriber database, SQL Server simply dumps the changes and overwrites them. Therefore, you have to ensure that everything is synchronized before changing the partitions, or you can lose data. SQL Server 2000 includes a flag that forces the upload of all changes before realigning the data with the partition. You can accomplish this pre-alignment upload by marking the subscription for reinitialization before you realign the data.