Microsoft SQL Server 2000 Administrators Companion

3 4

In this section, you will learn how to configure and tune a snapshot replication system for optimal performance. The snapshot replication system is much simpler to configure and tune than are transactional and merge replication systems. With transactional and merge replication, a snapshot is initially propagated to the subscribers and then smaller amounts of replicated data are constantly applied to these systems. By contrast, a snapshot replication operation fully refreshes the database and is therefore much more straightforward. This section begins with a review of the attributes of snapshot replication, and then it presents configuration, monitoring, and tuning guidelines.

Attributes of Snapshot Replication

Because snapshot replication copies an existing database to the distributor and then to the subscriber, the performance of snapshot replication depends on the ability of certain system components to move large amounts of data. Factors that can affect the performance of snapshot replication are listed here. By properly sizing and configuring your snapshot replication system, you can reduce the effects these factors have on performance.

• I/O performance on the publisher Because the entire database (or parts of it) is copied from the publisher, the performance of the I/O subsystem on the publisher can be a limiting factor. The snapshot creation task is more I/O intensive than CPU intensive; thus, CPU power is not usually a factor.
• I/O performance on the distributor The distributor receives large amounts of data at one time, and at some later time, it distributes that data. A slow I/O subsystem on the distributor will bog down the snapshot creation process.
• I/O performance on the subscriber The distributor attempts to distribute a database or subset of a database to the subscriber all at once. If the I/O subsystem on the subscriber is inadequate, replication performance will suffer.
• The bandwidth of the network between the publisher, distributor, and subscriber Because large amounts of data are being transferred, a bottleneck can easily occur on the network. Make sure that your network does not limit replication performance.

Configuring Snapshot Replication

This section provides some guidelines for configuring snapshot replication. Because snapshot replication uses the distribution database for storing only state information about the replication process, and not the replication data itself (which is done in a file), it is not typically necessary to tune SQL Server on the distributor. These guidelines should help you set up a snapshot replication system that performs optimally. The guidelines are listed here:

• Configure sufficient I/O capacity on the publisher, distributor, and subscriber
• Configure the distributor to keep the snapshot on the publisher system
• Configure the distributor and publisher on the same system
• Increase the number of BCP threads

Let's look at each of these configuration guidelines in more detail.

Configure Sufficient I/O Capacity

As mentioned in the preceding section, in snapshot replication a large amount of data is copied at one time, so a slow disk subsystem will slow down the entire process. By increasing the performance of certain I/O subsystems, you will enhance the performance of the entire replication process. On a system involved in replication, as on any SQL Server system, the transaction log 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. The RAID level that you use depends on whether you are configuring the publisher, the distributor, or the subscriber.

Configuring the I/O Subsystem on the Publisher Whether you use a RAID 5 or RAID 10 volume for the data files on the publisher depends on the read/write ratio of the access to that data volume. As mentioned in Chapter 5, any disk volume that is experiencing more than 10 percent writes is not a good candidate for RAID 5 and should use RAID 10. This is because of the excessive overhead associated with RAID 5 writes. So, in determining whether you can use RAID 5, monitor the system and track the number of writes versus the number of reads. Regardless of the RAID level you choose, you should properly size the I/O subsystem, as outlined in Chapter 5 and Chapter 6.

Configuring the I/O Subsystem on the Distributor Because snapshot replication stores a file on the distributor rather than using the SQL Server database, you must make sure that the snapshot location has sufficient I/O capacity to absorb a large number of writes. Thus, the snapshot location is more suited for RAID 10 than for RAID 5. As you will see later in this section, in some cases, storing the snapshot on the publisher is more efficient.

Configuring the I/O Subsystem on the Subscriber You can enhance the performance of snapshot replication by using a RAID 10 volume rather than a RAID 5 volume for the data files on the subscriber. This is because of the large number of writes that the subscriber experiences during snapshot replication.

NOTE

Select the Snapshot Location

Because snapshot replication copies the snapshot to the distributor and then later copies it to the subscriber, you can remove this extra step from the replication process. The distributor is still used, but it can be configured to store the snapshot on the publisher. This will eliminate an extra network copy operation. To configure the distributor to store the snapshot on the publisher, follow these steps:

1. On the distributor, invoke the Configure Publishing And Distribution Wizard. When the Publisher And Distributor Properties window appears, click the Publishers tab, shown in Figure 26-62.
2. Click the […] button. This will invoke the Publisher Properties window, shown in Figure 26-63.

   Figure 26-62. The Publishers tab of the Publisher And Distributor Properties window.

   Figure 26-63. The Publisher Properties window.

3. In this window, you can configure the snapshot location to be on the system where the snapshot is generated. In doing so, you must make sure that this location has enough I/O capacity to handle the additional load that is generated from the snapshot.

NOTE

Configure the Distributor and Publisher on the Same System

If the only type of replication you are using is snapshot replication, you can easily configure the publisher and distributor to be the same system. This will reduce network traffic because the snapshot doesn't have to be copied over the network to the distributor. However, if performance on the publisher is an issue, you should leave the snapshot on the distributor and let the distributor handle the distribution overhead.

Increase BCP Threads

You can also enhance replication performance by increasing the BCP threads that are used for the snapshot process. To do this, follow these steps:

1. In Enterprise Manager, expand the Replication Monitor folder, expand the Agents folder, and then click the Snapshot Agents folder. In the right-hand pane, right-click the desired publication and choose Agent Profiles from the shortcut menu that appears. This will invoke the Snapshot Agent Profiles dialog box, shown in Figure 26-64.
   Figure 26-64. The Snapshot Agent Profiles dialog box.

2. Click New Profile. This will create a new profile and will invoke the Replication Agent Profile Details dialog box, in which you can modify the profile. This dialog box is shown in Figure 26-65. Here you can change the MaxBcpThreads parameter.

   Figure 26-65. The Replication Agent Profile Details dialog box.

3. Once you have made your changes, name the profile and click OK. This will save the profile. Then select that profile in the Snapshot Agent Profiles dialog box.

Monitoring the Snapshot System

You monitor the snapshot system by using Microsoft Windows 2000 Performance Monitor. Within Performance Monitor are a number of objects that are added when SQL Server replication is used. In addition, a number of standard Performance Monitor objects are useful for monitoring snapshot replication. These objects include the following:

• SQLServer:Replication Agents Provides the number of each type of agent that is running.
• SQLServer:Replication Dist Provides information about distribution latency.
• SQLServer:Replication Snapshot Provides information about snapshot replication performance.
• PhysicalDisk Provides information about I/O activity. This information is highly useful because many performance problems related to snapshot replication are I/O related.
• Processor Enables you to monitor the system processors.
• System Provides information about the entire system.

These counters will give you a fairly good idea of how smoothly the replication process is running, but snapshot replication can occur quite quickly, so don't blink. If your system is well configured and well tuned, the snapshot process will run fairly quickly. To ensure optimal replication performance, watch for these potential problems:

• I/O bottlenecks Are I/O rates too high? Look for I/O operations per second and seconds per I/O operation.
• Network bottlenecks It is difficult to find a network bottleneck, but you might be able to determine if you have one by calculating the network throughput and comparing that to the snapshot replication time.

Tuning the Snapshot System

Tuning the snapshot system usually involves simply properly configuring it. The most significant problems that affect replication performance are I/O and network performance problems. You should look at the performance of your network and then determine whether your network is sufficient for your replication needs. Let's look at an example.

Suppose you have a 5-gigabyte (GB) database. If you are using a 10BaseT network, your network will have a maximum bandwidth of 10 megabits per second (Mbps), which is approximately 1 megabyte per second (MBps). Thus, a 5-GB database will take 5120 seconds, or 1.4 hours to replicate on this network. Here is the calculation: (5 GB * 1024 (MB/GB)) / 1 (MBps) = 5120 seconds, or 1.4 hours. In contrast, a 100BaseT network can perform the same replication operation in 8.3 minutes. A Gigabit Ethernet network can do this same task in 51 seconds. The network comparison is summarized in Table 26-1.

Table 26-1. Network comparison

As you can see, the size of your network really does count. By performing calculations like this, you should get a good idea of how fast the replication should be performed. If replication is taking much longer, you probably are experiencing a bottleneck somewhere else, such as I/O, memory, disk, and so on.