Fire up your merge replication performance
The best way to improve merge replication performance is to run merge agents as frequently as possible. However, if your Subscribers are offline, running merge agents will not always be possible, and you should urge users to synchronize frequently instead. The following tips will help you improve merge replication performance.
1. Use the appropriate profile
There are several profiles specific to merge replication performance you should use to address your particular requirements. To see these profiles, right click on your merge agent, select Agent Profiles and then select the appropriate profile from the following list:
- Default agent profile
- High volume server-to-server profile
- Rowcount and checksum validation profile
- Rowcount validation profile
- Slow link agent profile
- Verbose history agent profile
- Windows Synchronization Manager profile
2. Simplify your filters
Filters limit the amount of data that will be synchronized with the Publisher each time the merge agent runs. If you use filtering, make sure you
To simplify your filters, you may need to redesign your tables. If you are using the Host_name parameter in your filter, you can override the host name value on your Subscriber by using the HostName property of your merge agent. Make sure each column that comprises your filter condition is indexed and the index is updated frequently.
3. Increase batch sizes
Increase your batch sizes as much as possible. This frequently improves overall performance by preventing merge agent errors when Declarative Referential Integrity (DRI) violations arise (the errors can be a normal part of a merge synchronization and clear the next time the synchronization agent is run).
Set these parameters in your merge agent's properties.
4. Re-index merge tables frequently
Re-index the MSmerge_contents, MSmerge_tombstone, MSmerge_genhistory and MSmerge_replinfo tables frequently to improve overall replication performance.
5. Limit retention settings
Limit your merge publication retention settings. To do this, right click on your merge publication and select properties. In the Subscriptions expire section, select the minimum amount of time you expect your Subscribers to be offline before they resynchronize. Often, you will find that it takes longer to resynchronize with Subscribers that have been offline for an appreciable amount of time than it takes to resend a new snapshot and start over. (Starting over is called a re-initialization.) If you choose to re-initialize, you can select "Upload the changes at the Subscriber before re-initializing" so that work is not lost.
6. Use Alternate Sync Partners
The Alternate Sync Partners feature allows your Publisher to go offline (for service, for example) and your Subscribers to connect with an alternate Publisher until your Publisher comes back online. There are many restrictions when using Alternate Sync Partners; for example, it doesn't allow automatic identity range partitioning adjustments, and you can't automatically failover your Subscribers to use the Alternate Sync Partner when the Publisher becomes unavailable. Consult Microsoft support for more information on how to set up Alternate Sync Partners. Note: This feature will not be available in SQL Server 2005.
7. Republish for high availability
With the limitations on using Alternate Sync Partners, many replication topology designers struggle to provide highly available merge replication solutions. The answer is to use republishing -- making a Subscriber a Publisher to other Subscribers. That way you could have a main Publisher at your head office and Subscribers in each region (North, South, East and West), and then have each state office connect to and synchronize with these regional servers. To do so, you will have to carefully select global priorities to make such a topology work.
About the author: Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and subsequently studied economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.
This was first published in September 2005