Configuring, Monitoring, and Tuning the Distributor for Transactional Replication | Microsoft SQL Server 2000 Administrators Companion

3 4

In this section, you'll learn how to configure, monitor, and tune the distributor for transactional replication. As mentioned in the previous chapter, the distributor is a server containing the SQL Server database, called the distribution database, that is used as a repository of replication data. This data is held in a SQL Server database to provide several benefits, including the following:

Excellent performance SQL Server provides the performance the distributor needs to acquire, hold, and then distribute data.
Reliability Because SQL Server supports a high level of recoverability, a SQL Server database is ideal for replication data. Using the transaction log, SQL Server is able to recover from system failures without losing any data.
Ease of use Because SQL Server replication communicates directly with the distributor via SQL Server communications protocols, setting up and configuring the distributor is easy.

Configuring the Distributor

Depending on the frequency of modifications to your database, the amount of activity in the distributor can be quite high. Because the distributor uses a SQL Server database, all modifications to the distributor must be logged in the transaction log. You should configure the distribution database and the log to be large enough to perform the work required and fast enough to perform it efficiently. While the default distribution configuration can work for smaller replication systems, it is inadequate for many systems because the SQL Server replication wizards do not place the SQL Server transaction log and data files optimally. They are usually placed on the default SQL Server volume and both transaction log and database files are placed on the same volume.

By configuring the distribution database appropriately, you can avoid costly performance problems. Here are a few guidelines for configuring the distribution database:

Use a RAID controller on the distribution database system. Using a hardware RAID controller is more efficient than using software RAID.
Configure the distribution database's transaction log on a RAID 1 volume. The transaction log should be isolated to allow for the higher performance that is achieved with sequential I/O operations.
Configure the transaction log to be large enough so that it is not necessary to constantly back up the transaction log. Depending on your needs, you might be able to back up the transaction log only once a day (preferably at night).
Configure the distribution database on a RAID 1 or RAID 10 volume. RAID 5 is not appropriate because of the high number of writes to the distribution database.
Configure the distribution database to be large enough to hold extra replication data. If a subscriber fails, the database might have to hold several days' worth of replication data.
Tune the distribution database as you would any other SQL Server database.

Configuring the Distributor by Using Enterprise Manager

To properly configure the distribution database as outlined in the preceding list, you need to specify where this database resides. To do this by using Enterprise Manager, first invoke the Configure Publishing And Distribution Wizard. Use this wizard to set up publishing and distribution, specifying in the Customize The Configuration screen that you want to customize the distribution settings (by clicking Yes). This option allows you to set the distribution database location manually. (It also allows you to pick a name for the database, enable publishing, and create publications as well as subscribers.)

Unfortunately, when you use the wizard, you can't set the size of the distribution database. You can increase the size of the distribution database by selecting that database's properties in Enterprise Manager and changing the size of the database or transaction log files. If you prefer specifying the location and the size of the database at one time, you can use the sp_adddistributiondb stored procedure.

Configuring the Distributor by Using sp_adddistributiondb

The system stored procedure sp_adddistributiondb allows you to script the creation of the distribution database. This is useful when you want to specify the size and location of the database and its transaction log. And once you have written a script that creates the distribution database, you can use it on different systems, or you can use it to re-create the distribution database in the event of a system reconfiguration.

NOTE
Before you can create the distribution database, you must enable the distributor. You accomplish this by using the system stored procedure sp_adddistributor, followed by the distributor's system name.

The syntax for the sp_adddistributiondb stored procedure is shown in SQL Server Books Online. An example of using that stored procedure is shown here:

 sp_adddistributor Dash

The following SQL statement initializes the system named Dash as a distributor.

 sp_adddistributiondb @database=dist, @data_folder='C:\mssql2000\data', @data_file='dist.mdf', @data_file_size=10, @log_folder='C:\mssql2000\data', @log_file='dist.ldf', @log_file_size=2, @min_distretention=0, @max_distretention=72, @history_retention=96, @security_mode=0, @login='sa', @password='', @createmode=0

Monitoring the Distributor

You monitor the distributor by using Windows 2000 Performance Monitor (perfmon). Within perfmon are a number of objects that are added when SQL Server replication is used. These objects are as follows:

SQLServer:Replication Agents Provides the number of each type of agent that is running
SQLServer:Replication Dist. Provides information about distribution latency
SQLServer:Replication Logreader Provides data about Log Reader Agent activity and latency
SQLServer:Replication Merge Provides data about merge rates
SQLServer:Replication Snapshot Provides information about snapshot replication performance

By using Performance Monitor to monitor these values, you can sometimes determine whether the distributor is experiencing a performance problem. This perfmon data provides a lot of valuable information, but it does not always identify problems. For monitoring the distributor, we are most concerned with the SQLServer:Replication Dist. object. This object provides the following counters:

Dist:Delivered Cmds/sec Monitors the number of commands per second delivered to the subscriber. This counter gives you a good idea of how much activity is occurring on the subscriber.
Dist:Delivered Trans/sec Monitors the number of transactions per second delivered to the subscriber. This counter also gives you a good idea of how much activity is occurring on the subscriber.
Dist:Delivery Latency Monitors the amount of time it takes for transactions to be applied to the subscriber after they are delivered to the distributor. This counter can give you some insight into how backed up the distributor is.

While these counters give you some general indication of how the distribution process is running, they are of limited usefulness when you are determining whether you need to tune the distributor because the most important aspect of tuning the distributor is tuning the SQL Server database. Thus, you should look mainly for the following problems:

High CPU usage Are one or more CPUs running at high rates (greater than 75 percent of their capacity) for long periods?
I/O bottlenecks Are I/O rates too high? Monitor I/O operations per second and seconds per I/O operation.
Response times Are SQL Server response times too high?

Tuning the Distributor

As mentioned earlier, the distributor is the server containing the distribution database, and this database must be tuned in the same manner as any other SQL Server database. You can enhance the performance of the distributor by properly sizing it, although, as you learned in Chapter 6, sizing is not always an easy task. The distributor should have enough capacity to handle extra work. The distributor is the link between the publishers and the subscribers and should be configured so that it is not a bottleneck. Some tips for configuring and tuning the distributor are provided here:

Tune the I/O subsystem Ensure that the distributor, like any other SQL Server system, has sufficient I/O capacity.
Use a multiprocessor system CPU power is not usually a problem because in most cases, operations that take place on the distributor are not extremely CPU intensive. However, you should use at least two CPUs to allow concurrent operations to take place.
Tune the operating system Configure the Server service to maximize throughput for network applications. This will configure the memory system to favor applications over file services. You make this setting through the Network icon in Control Panel. Also, remove any services that won't be used, such as IIS and FTP services.
Monitor the distributor during snapshot replication When snapshot replication is running, including when the initial snapshot is being copied in transactional and merge replication, a large number of I/O operations will occur at one time. Because so much data is being written to the distributor, the distributor's I/O subsystem might become overloaded. If this happens, the time it takes to apply the snapshot will increase. Therefore, you should monitor the distributor during the transmission of the snapshot.
Tune SQL Server Using the techniques and guidelines you've learned from this book, tune the SQL Server system.