Merge Replication Tips And Tricks
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:
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
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.
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. 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)
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.
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
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.
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
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.
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. 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)
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.
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
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.
merge,
agent,
click,
publisher,
replication,
changes,
subscriber,
properties,
tab,
select,
agents,
subscribers,
right