This tip is the second of a three-part series on merge replication. In part one, contributor and SQL Server MVP Hilary Cotter explained how merge replication works. Here he offers merge replication tips and tricks.
Merge replication tips and tricks
I have assembled this list of tips and tricks over the years, and I have ordered them according to frequency of use.
1. Consider using bi-directional transactional replication
Bi-directional transactional replication, also known as two-way transactional replication, allows a server to be both a Publisher and a Subscriber to the same data; servers will replicate changes to the other servers, but changes will not be propagated back to the originating server.
Consider using bi-directional replication if the following situations apply:
- You need bi-directional replication between two nodes only.
- Your schema will change infrequently.
- Your publisher and Subscriber are very well partitioned to reduce the probability of conflicts.
Bi-directional transactional replication offers much better performance than merge replication in most cases. Merge replication is a better option for a two-node topology when your data update patterns are such that rows are updated frequently and inserted or deleted rarely; and you only need the net changes at discreet intervals (i.e., hourly or daily). For instance, a stock price application that only requires daily updates is a better candidate for merge replication.
2. Make schema changes with sp_repladdcolumn and sp_repldropcolumn
If you attempt to change an article that is published for merge replication, you will get an error message warning:
Server: Msg 4932, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN failed because 'columnName' is currently replicated.
To make schema changes to existing merge ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
replication topologies, use the sp_repladdcolumn and sp_repldropcolumn stored procedures.
3. Execute commands on all Subscribers
You will often be asked to replicate a set of commands to all your Subscribers, such as add a new login. To do this, use sp_addscriptexec. This command will work on all Subscribers that you deployed via UNC subscriptions.
4. Use dynamic snapshots
If you are filtering subscriptions, you will notice that your subscription still contains all of the data, even data that is not part of its filter. For example, if you send a subscription to a sales agent for California, he will receive data for the entire country. To minimize the size of the snapshot, use dynamic snapshots, and your Subscriber will only contain the data that meets the filtering criteria.
5. Change the ExchangeType parameter
Applications often require the Subscriber database to be read-only, which means Subscriber database changes will not to be replicated to the publisher and vice versa. To make the change, use the ExchangeType parameter, which has three possible values:
- UploadOnly: Only changes originating at the Subscriber are merged with the Publisher. The Publisher's changes stay in the Publisher. Use a value of 1 in your agent properties.
- DownloadOnly: Only changes originating at the Publisher are merged with the Subscriber. Use a value of 2 in your agent properties.
- Bi-Directional (the default): Changes originating at the Publisher and Subscriber are merged. Use a value of 3 in your agent properties.
To make these changes to your ExchangeType, connect to your Publisher in Enterprise Manager, expand Replication Monitor, expand Replication Agents, expand Merge Agents and right click on your Merge Agent. Select Agent Properties, click on the Steps tab and double click Run Agent. At the end of the Command text box, enter – ExchangeType 2. (See Figure 1)
[IMAGE]
Figure 1: Changes in ExchangeType parameter.
The ExchangeType parameter is on a subscription basis. You can achieve the same on an article basis by specifying the accounts that have rights to issue update, insert or delete statements on your Publisher. To specify the accounts, right click on your Publication, select Properties, and in the Article tab, click on the browse button to the right of selected articles. Then, in the Merging Changes tab, select which statements you want verified. By selectively granting rights to accounts, your Merge Agent will connect to the Publisher where you can control whether all insert statements (for example) that originate on a Subscriber will not be merged with the Publisher.
6. Use pull Subscribers for larger numbers of Subscribers
In general, you will get better performance if you use pull Subscribers as your Subscriber numbers increase. You will need to run your agents on a scheduled basis, and you will want to stagger the schedules (i.e., every seven minutes or some other prime number).
7. Wrap your agents to restart on failure
A large number of merge agents will fail due to transitory reasons (such as locking, batch failures, network problems, etc.), and these transitory errors will clear the next time the agent runs. Set your merge agent to restart on failure by right clicking on your merge agent and selecting Agent Properties. Then select Steps and double click on step 3 ("Detect nonlogged agent shutdown"). Click the Advanced tab. Where you see "On success action," select "Goto Step 1." This is illustrated in Figure 2.
[IMAGE]
Figure 2: Setting merge agent to restart
This way if your agent fails, it will restart. If it fails to do an abnormal event, it will not restart. You should collect the contents of MSrepl_errors on your Subscribers and evaluate them to discover exactly why they are failing.
8. Limit concurrent merge agents
For large numbers of Subscribers, you should limit the number of merge agents that can run concurrently. If you select this option, you must also set the StartQueueTimeout property to establish how long a merge agent will remain in a wait queue until other merge agents have completed processing. You may need to set this value to 60s or more from its default of 0 (which means it waits indefinitely).
To limit the number of concurrent merge agents, right click on your Publisher and select Properties, click on the Subscriptions tab and check the "Limit the number of concurrent merge processes to the following" box. This option is illustrated in Figure 3
[IMAGE]
Figure 3: Publication Properties
9. Take advantage of Windows Synchronization Manager
Windows Synchronization Manager is one of the most neglected replication components, although it is extremely powerful. Ideally, you use Windows Synchronization Manager when you wish to control how subscriptions are deployed and synchronized on laptops.
To enable a Publication to be pulled by Windows Synchronization Manager, you must enable your Publication for pull subscriptions. When you create your pull subscription and get to the Set Merge Agent Schedule, select "On Demand only."
Then click on Start, Programs, Accessories and click on Synchronize to synchronize your subscription. Click on the Setup button to control how you want the Subscription deployed (on boot, logon, low processor utilization, etc.), and if you click on the Properties button you can configure aspects of your Subscription such as Subscriber login, Publisher login and so on.
10. Resolve conflicts interactively
One final tab on Windows Synchronization Manager is in the Properties option. It is the Other tab. This tab has an option called "Resolve conflicts interactively." If you configure your Publication to resolve conflicts interactively (right click on your Publication, select Publication properties, click on the Articles tab, click on the browse button to the right of your article, select the Resolver tab and check the "Allow Subscribers to resolve conflicts interactively during on-demand synchronizations."
With this setting, subscriptions that are synchronized with Windows Synchronization Manager that generate update or delete conflicts (primary key collisions are merely logged for later resolution) will launch an interactive resolution dialog allowing the user to resolve the conflict.
In the final part of this series, I'll explain how to fire up merge replication performance.
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 both 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.
