Merge replication has been a feature of SQL Server since SQL Server 7.0. It is designed for clients who are frequently offline and need to bi-directionally replicate with a publisher.
Merge replication works by logging changes that occur on the publisher or subscriber(s) in your merge replication topology between synchronizations. During synchronizations, the publisher and subscriber compare what has changed since the last synchronization. Then they use stored procedures to synchronize the subscriber with changes that occurred on the publisher, and the publisher with changes that occurred on the subscriber. If changes occur on the same row(s) between synchronizations, during synchronization these changes are logged as conflicts and either the publisher's or the subscriber's change will be persisted on either side, depending on how you choose to resolve these conflicts.
Microsoft has published an article on how to improve merge replication performance, which is a helpful reference.
Here are some additional tips to consider:
Is merge replication the best replication choice?
Merge replication is not the only bi-directional replication solution Microsoft offers. When considering which choice to make, consider the following factors:
The following table illustrates the choices and their limitations:
[TABLE]
Evaluate whether some of the other bi-directional replication options will work for you, despite the limitations.
Choose the appropriate profile
Most of the fine tuning of merge replication topologies is done through the agent properties. Microsoft has bundled collections of these properties together in groups called profiles, with each profile dedicated to a specific topology. For example, SQL 2005 has the following profiles:
Choose the appropriate profile for each subscriber. Only use the Rowcount and checksum validation profile, Rowcount validation profile, and Verbose history agent p
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
rofile while you are debugging or performance tuning. If you are doing server to server merge replication, use the High volume server-to-server profile, instead of the Default agent profile.
Similarly if you are replicating to clients over low bandwidth links -- like phone lines -- use the slow link agent profile for these subscribers.
Minimize conflicts
A conflict occurs during a synchronization when:
When doing a sync, the synchronization process attempts to merge all changes on both sides. Depending on the type of conflict, a retry attempt is made after each batch is processed. These re-tries and the subsequent conflict logging are somewhat expensive, and anything you can do to minimize conflicts will shorten synchronization times. In some cases the merge agent will fail after a conflict and only succeed during the next synchronization.
Partitioning your data to avoid conflicts will increase the efficiency of your synchronizations.
Retention period
Merge replication has to do extensive tracking to know what it has sent and received from each subscriber, and to know what it has to send to each new subscriber in addition to the snapshot. On high volume systems, this tracking data (or replication metadata) can become very large and can increase the amount of time required to synchronize. This is especially acute with mobile subscribers. Drop the retention period to the smallest value possible. The limit should be a time when you know all subscribers will synchronize within; otherwise you will need to reinitialize these subscribers.
Snapshot
When synchronization times become lengthy, they may exceed the time period required to send a new snapshot. In this case, consider sending a new snapshot to these subscribers. This is especially acute with SQL CE subscribers.
Hierarchies
When you have large numbers of subscribers, there will be considerable locking when the merge agents run. In some cases, it will make sense to create hierarchies where groups of subscribers will synchronize with one publisher. This will in turn synchronize with another publisher further upstream. The topology might consist of a top level publisher replicating to four down stream subscriber/publishers, and from each of those four downstream subscriber/publishers 20 or more subscribers replicate to.
Creating such hierarchies will improve the overall performance of all nodes in the topology.
Join filters
Join filters are a merge only feature that treats related data as a unit or partition. Consider a case where you have a salesman table, which has a client table related by a common SalesPersonID key. Further, you have an order table which is related to the client table by a clientID key. If the clients were assigned to a new Sales Person and you had a filter on SalesPersonID, when you did the update of the SalesPersonID from let's say one to two, the rows in the client table would move to the subscription that matched the filter value. But the order rows that correspond to that client table would not move. In order to extend that partition to the orders table you either add the SalesPersonID to each the order table and update the value of the SalesPersonID column here as well, or use join filters.
Join filters require a lot of processing during synchronization to figure out all rows that should be added or removed from a partition. In SQL 2005, partitions can be pre-computed so the partitions will not have to be dynamically built during the synchronization process. However, this does add even more latency to all DML originating at the Publisher or Subscriber, so the impact using pre-computed partition may be greater than if you do not use them.
If you can make your join filters as shallow as possible. In our case, our join filters were two levels deep. You will get dramatic performance improvements by moving from four to five levels to ywo to one level(s). This will require re-architecting your schema however, and considerable de-normalization of your tables.
Another point about join filters and filters in general is to ensure that all columns you are filtering on have an index on them.
Summary
In this article we had a look at some not-so-obvious methods of improving merge replication performance. It is important to evaluate whether merge replication is the best solution. I find it is frequently implemented for server to server replication as a DR solution, when bi-directional transactional replication is a better fit. Properly tuned merge replication is highly scalable and does perform exceptionally well.
[TABLE]
