Customize your snapshot replication by hand
Copying a huge amount of data to several servers poses a formidable challenge. SQL Server's copy utilities—among them bulk copy program (bcp), Data Transformation Services (DTS), and snapshot replication—can take more time to copy the data than is available in most production environments. After experimenting with these utilities, I developed a way to optimize the performance of large snapshot replication to levels acceptable in a real-world scenario. In "Bottleneck Blues," May 2002, InstantDoc ID 24518, I analyzed the performance of snapshot replication, identified where the performance bottlenecks occur, and suggested a way to clear the bottlenecks by customizing the replication steps.
In this article, I detail the full implementation of these customized steps. You configure the steps manually through Enterprise Manager and use T-SQL, a remote stored procedure, and SQL Distributed Management Objects (SQL-DMO) in a custom COM object to customize the steps. You can apply this article's listings and executable scripts to push and pull snapshot replication subscriptions in SQL Server 2000 and 7.0 without modification. (To download the listings and executables, go to http://www.sqlmag.com, InstantDoc ID 25538, and click Download the code.) To confirm the mathematical analysis that I presented in the first article, I include test results confirming that you can improve snapshot replication performance significantly when you optimize the replication by customizing its steps.
Configuring the Agent Jobs
To configure snapshot replication, you typically create a publication first, add articles, then add subscriptions to the publication. The configuration process creates a Snapshot Agent job and one or more Distribution Agent jobs. (For details about the process, see "Bottleneck Blues" and review "Implementing Replication" in the Enterprise Manager section of SQL Server Books Online—BOL.) The agents perform the replication by executing steps in their jobs. By default, a Snapshot Agent's job consists of three steps—Log agent startup message, Run agent, and Detect nonlogged agent shutdown. This job's Run agent step executes the snapshot.exe command-line utility. Also by default, a Distribution Agent job consists of the same three steps as the Snapshot Agent job. However, its Run agent step executes the distrib.exe command-line utility, which consists of three substeps. The schema (sch) substep deletes the target records, the bulk copy (bcp) substep bulk-copies the snapshot to the target tables, and the index (idx) substep creates indexes, if any, on the target tables. Because indexes and foreign key constraints are often defined on the target tables, the sch and bcp substeps become the bottlenecks. But if you could drop the indexes and constraints before these two substeps and recreate them afterward, the much faster TRUNCATE operation (or the default of dropping, then recreating the table) could replace the DELETE operation in the sch substep, and you wouldn't have to maintain the indexes during the BULK INSERT operation in the bcp substep. Fortunately, you can modify the Distribution Agent's job to incorporate the additional steps. You do so by inserting the Drop Indexes and Keys (drop) step before the Run agent step and inserting the Re-create Indexes and Keys (recreate) step afterward.
Adding steps to the Distribution Agent job creates impediments to the job's smooth operation, so first you have to determine the best way to configure both agent jobs to run most effectively. In Enterprise Manager, you can set the customized Distribution Agent job to disable, to start running at a scheduled time, or to start when the SQLServerAgent service starts. (The SQLServerAgent service is a Windows 2000 or Windows NT system service.) When this service starts, it also starts all the jobs that you set to Start automatically when SQL Server Agent starts. Setting a Distribution Agent job to start automatically doesn't work for this customization project. Once a job starts automatically and reaches Run agent, by default the job executes distrib.exe with a Continuous argument to poll new snapshots at a certain polling interval until the SQLServerAgent service stops or until you stop the job manually. Consequently, the Distribution Agent job's recreate step never executes, and the drop step never executes for the next snapshot polled.
If you set a customized Distribution Agent job to start running at a scheduled time, the job proceeds through each step without hanging on Run agent and stops until the next schedule begins. However, scheduling a Distribution Agent job doesn't work well for the customization project either. Predefining such a schedule is difficult because the schedule must ensure that the Distribution Agent job doesn't start before the Snapshot Agent job is finished and must not overlap the schedules of other Distribution Agent jobs.
To start and stop each Distribution Agent job at the right time, you have to serialize the Snapshot Agent job and the Distribution Agent jobs. To accomplish the serialization, you first need to disable each Distribution Agent job schedule so that the job starts only manually or on call. Next, customize the Snapshot Agent job so that when its Run agent step is finished, the job starts one Distribution Agent job at a time. You customize the Snapshot Agent job by inserting the new Start Distribution Agent Jobs (start distributors) step immediately after the Snapshot Agent job's Run agent step, as Figure 1 shows. During the start distributors step, the sp_start_job stored procedure executes against each Distribution Agent job one at a time. Figure 2 shows two Distribution Agent jobs started, one for each remote server (subscriber).
Prev. page
[1]
2
3
4
next page 
