Optimizing Synchronization

Optimizing synchronization during merge replication allows you to minimize network traffic when determining if recent changes have caused a row to move into or out of a partition for a Subscriber. In merge replication, an option is provided that stores more information at the Publisher instead of transferring that information over the network to the Subscriber. While this option may result in a larger database at the Publisher, it can improve synchronization performance over a slow link. However, more information will be stored at the Publisher and additional storage space will be necessary.

If the optimize synchronization setting is not used, changes in one partition will cause the merge process to verify the partition content of data sent to all Subscribers again, even if the change affects only one or a few Subscribers.

For example, if sales data is filtered based on the state where a customer resides, and a customer moves from Washington to California, that row needs to be removed from the data partition sent to Washington Subscribers and added to the data partition sent to California Subscribers.

If optimize synchronization is not used, the merge process will also check partitions sent to other Subscribers (those in Idaho, Oregon, and so on) for the state value that changed. The merge process cannot know what the California value was before it was changed. Enabling synchronization optimization will allow the merge process to accurately know what partitions were affected and what Subscribers need to be cleaned up.

By storing additional information at the Publisher, Microsoft® SQL Server™ 2000 can more quickly determine the filtered data that should be sent to a particular Subscriber. When synchronization is optimized, SQL Server 2000 creates before image tables at the Publisher that contain additional information about changes to columns used in horizontal or join filters. These before images from an UPDATE or DELETE to such a column permit the Merge Agent to determine quickly and accurately which Subscriber may need to have rows added to or removed from a specific data partition.

For example, if a sales organization partitions and distributes data based on sales territories, and the publication is enabled to optimize synchronization, the information about how data is partitioned would be stored in before image tables at the Publisher. If sales territories shift and data needs to be repartitioned to multiple Subscribers, it will be a faster process to update and redistribute the data because the information about how data is currently partitioned is already at the Publisher.

This optimization may be useful if your application allows for the values used in row filters to change frequently for a given row. For example, if you frequently shift or realign sales territory assignments, you may gain a significant performance improvement during synchronization through this optimization.

The amount of information stored at Publisher is based on columns used to define the partition. For example, if the columns in a partition total 20 bytes and there are 10 million rows, approximately an extra 200 MB will be stored at the Publisher. If there are only 10,000 rows, 200 KB will be stored at the Publisher.

Caution  Choosing to maintain this additional information at the Publisher results in an increase in the storage requirements for the merge replication tracking system tables in the publication database; however, if UPDATES to columns included in partitions are not atypical, the performance gains are usually worth maintaining the additional information.

To minimize the amount of data sent over the network