After you've located these two agentjob objects, you're ready to begin customizing the jobs. First, you insert a new step into the Snapshot Agent job after the job's second step. This new step becomes the job's third step, and you assign it StepID 3 to indicate its position, as Listing 5 shows. Recall that the command at step 3 is to start one or more Distribution Agent jobs (I show only one job start here). In SQL-DMO, you insert a new step by creating a new job-step object, assigning the step's properties, then adding the new step to the job object's steps. You also have to modify the flow of the second step; in case of failure, its OnFailStep used to flow to the old step 3, but that step is now step 4, as Figure 2 shows. Listing 5 shows the process for creating the third step and modifying the second step.
Next, you customize the selected Distribution Agent jobs, as Listing 6, page 40, shows. Again, I use only one Distribution Agent job. Each job includes two new inserted steps. One new step calls a stored procedure before the original second step to drop indexes and keys on destination tables and save their scripts. After you insert this new step, it becomes step 2. You insert the other new step after the original second step (which is now the third step). The new step calls a remote procedure stored on the subscriber to run the scripts that the first new step (now step 2) saves. This new step becomes step 4.
Mechanically, the code in Listing 6 is similar to the code in Listing 5 (which customizes the Snapshot Agent job), so Listing 6 shows only important lines. You can find the complete listing for this automated SQL-DMO application in the DMO_job.zip file.
Fast Replication, Efficient Production
In a distributed enterprise that involves loosely connected remote locations (which use a T1 or wireless connection) or many servers, a huge synchronized snapshot can easily timeout or run out of off-peak hours. In my tests, the optimization this series describes cuts nearly an hour off the replication time for each server. If your environment can't detect changes and can't drop and recreate production tables easily, snapshot is your only replication choice. With this optimization, you can speed up your snapshot replication by truncating tables, a process that's faster and less destructive for production environments than dropping tables.