Monitoring and Tuning the Merge Replication System

3 4

This section explains how to monitor and tune a merge replication system for optimal performance. Additionally, this section presents some configuration guidelines for merge replication systems. This section also includes a review of the attributes of merge replication.

Attributes of Merge Replication

Merge replication is quite different from the other replication methods. Unlike transactional replication and snapshot replication, merge replication is not a one-way replication method. Modifications can be made either on the publisher or on any number of subscribers. Additionally, with transactional replication, the transaction log is read to track changes, which is an operation external to the normal operations of SQL Server, whereas with merge replication, triggers are created on the replicated tables in order to track changes to them.

Merge replication begins with the application of a snapshot, but because this operation occurs only once, you don't have to tune the snapshot replication process. The merge system creates tables on both the publisher and the distributor in order to perform the replication. In addition, a new column that holds a unique row identifier is added to every replicated table. This is used to uniquely identify each row so that the replication agent can effectively track changes.

When a row is inserted or modified, the trigger marks that row for replication. At a later time, the Merge Agent is activated, collects all marked rows, and sends them to the distributor for replication. At the same time, the Merge Agent modifies any rows in the publisher's tables that have been modified in the tables of the subscriber system or systems. In this way, two-way replication is accomplished.

Configuring Merge Replication

When you use merge replication, properly configuring the participating systems' I/O subsystems and the network is extremely important in improving replication performance, just as it is when you use other replication methods. You can also improve merge replication performance by configuring the merge batch size. By increasing the batch size, you ensure that fewer, larger batches will be used, which is more efficient. In addition, you might want to tune snapshot replication; however, because the application of the snapshot occurs only once, you can skip this step. Finally you can modify the Merge Agent; this process was described earlier in this chapter. In this section, I/O configuration and merge batch size configuration are described.

Configure Sufficient I/O Capacity

By configuring sufficient I/O capacity, you can enhance the performance of the entire replication process. As on any SQL Server system, the transaction log on a system participating in replication should be located on its own RAID 1 volume for data protection. The data files should be located on one or more RAID 10 or RAID 5 volumes. Like transactional replication, merge replication requires only minor adjustments to standard I/O configurations.

Configuring the I/O Subsystem on the Publisher In general, you should follow the standard SQL Server configuration guidelines described throughout this book when you are configuring the publisher's I/O subsystem. Unlike transactional replication, merge replication puts no additional load on the transaction log, so follow standard tuning guidelines when you configure the I/O capacity for the log.

Configuring the I/O Subsystem on the Distributor The distributor should be configured such that the distribution database has its transaction log on a dedicated RAID 1 disk volume. This will allow the distribution database's log to achieve maximum performance, thus improving the performance of the distributor.

Configuring the I/O Subsystem on the Subscriber Because merge replication is multidirectional, the subscriber and publisher are similarly tuned. Simply follow the general sizing and configuration guidelines described throughout this book.

Configure the Merge Batch Size

In busy systems, you can improve merge replication performance by configuring the merge batch size. The merge batch size determines how many changed rows are copied to the distributor at a time. When the batch size is increased, fewer, larger batches are sent, which might be more efficient.

Monitoring the Merge Replication System

You monitor merge replication via Windows 2000 Performance Monitor (perfmon). Within perfmon are a number of objects that are added when SQL Server replication is used. These objects include the following:

• SQLServer:Replication Agents Provides the number of each type of agent that is running.
• SQLServer:Replication Merge Provides data about merge rates. This includes information about conflicts per second, uploads per second, and downloads per second. This information does not really help with tuning merge replication.

The SQL Server merge replication counters are not extremely helpful for determining performance problems. The best way to tune a merge replication system is to simply tune it as you would any SQL Server system and to pay special attention to how the network is performing. Look for bottlenecks in the places they normally occur on a network, and follow the guidelines presented in this chapter and the previous two chapters to determine whether the distributor is overloaded.

Tuning the Merge Replication System

The main steps in tuning the merge replication system are to properly configure and monitor it, as described in the preceding sections, paying particular attention to I/O and network performance. You can monitor the system via perfmon, but, as mentioned, perfmon will not provide highly useful merge replication data. Instead, you must rely on other SQL Server counters and Microsoft Windows 2000 counters to tune the system.

As mentioned in Chapter 24, you might want to modify the BCP batch size and the merge batch size if your system is doing a lot of updates. By increasing the BCP batch size, you will increase the performance of the application of the original snapshot. By increasing the merge batch size, you will ensure that more changes are copied at a time, which might be more efficient. When more changes are copied at a time, the system is affected less often, but it must handle more work during the replication operation.

In addition, you can change the polling interval. However, this is not recommended. The default polling interval is usually adequate. Before tuning the polling interval, try changing the batch sizes. If you feel that you need merge replication to run more frequently or less frequently, change the polling interval.

You also need to monitor the network and increase its capacity if necessary, just as you do if you use snapshot or transactional replication. If your system does not appear to be performing optimally—for example, if CPUs and I/O subsystems are reaching their capacity and if the replication process seems to be taking too long—you might have a network problem. Perfmon does not have a counter that will show you network problems. A network-monitoring product such as Microsoft Systems Management Server (SMS) should be used. Monitor the network card to see if it's reaching its capacity. If your network is reaching its capacity, either purchase faster network cards or add a private network for replication, backup and recovery, or both. Finally remember that the publisher, distributor, and subscribers are SQL Server systems. You should thus tune these systems just as you would tune any other SQL Server system. Tuning guidelines for SQL Server systems are given throughout this book.