SQL Server merge replication performance fired up

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

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server Replication SQL Server Mailbag: Migrating down to Standard Edition Push vs. pull: Configuring SQL Server replication Top load balancing methods for SQL Server New replication features in SQL Server 2008 and what they mean to you SQL Server database replication tutorial Licensing a standby server for SQL Server replication Upgrade live applications to SQL Server 2005 for high availability Tool to synchronize two SQL Server databases Simplify SQL Server replication Replication techniques in SQL Server Microsoft SQL Server Performance Monitoring and Tuning Using traces in SQL Server Profiler SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Data restoration and DB property management Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Troubleshooting Distributed Transaction Coordinator errors in SQL Server Clearing the Windows page file and its effect on server performance Optimizing SQL Server indexes –- even when they're not your indexes Performance implications of transaction log autogrowth in SQL Server The short course on how SQL Server really works Database Management and Administration Password cracking tools for SQL Server Using traces in SQL Server Profiler Meet compliance requirements with improved database security practices Hardening the network and OS for SQL Server security Securing the server and database in SQL Server How SQL Server 2008 components impact SharePoint implementations Troubleshooting Distributed Transaction Coordinator errors in SQL Server Achieving high availability and disaster recovery with SharePoint databases Clearing the Windows page file and its effect on server performance Deploying a SQL Server virtual appliance for Microsoft Hyper-V

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary replication  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.