SQL Server merge replication performance fired up

If your Subscribers are frequently offline, it may not be possible to run merge agents regularly to keep replication running smooth. To help you solve performance dilemmas, contributor Hilary Cotter offers his tuning tips in the final part of this three-part series on merge replication.

This is the third tip of a three-part series on merge replication. In part one, contributor Hilary Cotter explained how merge replication works. In part two, he outlined 10 merge replication tips and tricks. Below he'll explain how to improve merge replication performance.

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

Get more on replication in SQL Server:
  • How merge replication works
  • Ten merge replication tips and tricks
  • 15 SQL Server replication tips in 15 minutes
  • have indexes on the filters, as they can be performance intensive.

    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).

    • UploadGenerationsPerBatch
    • DownloadGenerationsPerBatch
    • UploadReadChangesPerBatch
    • DownloadReadChangesPerBatch
    • UploadWriteChangesPerBatch
    • DownloadWriteChangesPerBatch

    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.

    For more information on merge replication, consult ReplicationAnswers.com or post your questions on the Microsoft.Public.SQLServer.Replication newsgroup.

    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.

    Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning