Adding the Start Distributors Step
Now, let's look at how to add the start distributors step to a Snapshot Agent job. A typical and straightforward way to customize an agent job is to use Enterprise Manager. First, to customize a snapshot replication, open the Table Article Properties dialog box for each article, then click the Snapshot tab. Select the Delete all data in the existing table option, then clear the referential integrity and indexes options. (Note that choosing the DROP the existing table and re-create it default option instead of Delete all data in the existing table would work as well if you have no problems dropping tables on a production server.) The .sch file that the Snapshot Agent generates for each article will then contain a TRUNCATE TABLE statement for the corresponding target table, and the .idx file will be empty. You can find these .idx and .sch files in the snapshot folder, which by default is in the ReplData subdirectory under the SQL Server home directory (e.g., D:\Microsoft SQL Server\mssql\repldata). To view your snapshot folder's path, select the name of your publisher server from the server tree in Enterprise Manager, pull down the Tools menu, select Replication, then Configure Publishing, Subscribers, and Distribution. The Publisher and Distributor Properties dialog box will appear. On the Publishers tab, click the (...) button in the third column of the selected publisher row to bring up the Publisher Properties dialog box. In that dialog box, the Snapshot folder text box shows the path of the snapshot folder.
Next, to navigate to the agent jobs that your snapshot replication has created, again in Enterprise Manager, expand the server that functions as the publication's distributor. (Note that you can choose the same server for both distributor and publisher, but if this server has to run both agents, it will expend more resources than two separate servers.) Expand Replication Monitor, Publishers, and a specific publisher's name, then select your publication. Because at this time Enterprise Manager's right pane lists one Snapshot Agent job and one or more Distribution Agent jobs for that publication, this window, which I call the agent jobs window, is the best place to customize the agent jobs. Right-click any agent job and select Agent Profiles. The Agent Profiles dialog box contains read-only, system-type profiles that you can't modify. However, you can create a modifiable user-type profile by copying from an existing system profile. To issue the maximum number of concurrent bulk copies, the snapshot.exe and distrib.exe commands will use the profile's parameters (e.g., MaxBcpThreads) at the Run agent steps.
To customize the Snapshot Agent job, perform the following steps. Right-click the job named Snapshot in the agent jobs window, then select Agent Properties. In the Properties dialog box, click the Schedules tab, then set the starting time for the Snapshot Agent job. Click the Steps tab, select Detect nonlogged agent shutdown, then click Insert. In the New Job Step dialog box, which Figure 2 shows, enter the name of the step (e.g., Start Distribution Agent Jobs), then accept TSQL as the default command type. Next, select msdb as the database, then enter in the Command window an sp_start_job stored procedure call for each Distribution Agent job.
The two calls to sp_start_job start two Distribution Agent jobs, one for each subscriber. To find the name of a Distribution Agent job for the sp_start_job stored procedure's @job_name parameter, right-click a Distribution Agent job in the agent jobs window, then select Agent Properties. In the Properties dialog box, copy the agent name. This custom step starts each Distribution Agent job right after the Snapshot Agent job's Run agent step. To modify the flow of each step upon success or failure, double-click a step. In the Edit Job Step dialog box that appears, click the Advanced tab, then select your On success action or On failure action. Typically, you want to proceed to the next step on success so that the job runs serially through the steps until it finishes.
Adding the Drop Step
After you customize the Snapshot Agent job, you customize the Distribution Agent jobs by adding steps to drop and recreate all indexes and key constraints. To find the Distribution Agent jobs you want to modify, follow the path in Enterprise Manager that you took to find the Snapshot Agent job (in the agent jobs window). Right-click each Distribution Agent job, then select Agent Properties. In the Properties dialog box, click the Schedules tab, double-click each enabled schedule, select any schedule-type option except Start automatically when SQL Server Agent starts, then disable the schedule. Disabling the schedule causes the job to start only when the start distributors step calls it in the Snapshot Agent job. Switch to the Steps tab. Insert the drop step before the Run agent step, enter the step name, then accept the default command type and database, as Figure 3 shows. In the Command window, enter a stored procedure that drops all indexes and key constraints on all target tables in the remote subscription database. This stored procedure (hub_remote_drop_save_subscribed_article_indexes) accesses system tables in the publication database and therefore must be stored in the publisher. However, the Distribution Agent job runs Figure 3's EXEC statement at the distributor. This statement assumes that the publisher and the distributor are the same server. Otherwise, the statement would have included the qualified name of the HUB1DB linked publisher, as follows:
EXEC HUB1DB.master.dbo.hub_remote_drop_save_
subscribed_article_indexes 'hub2_publish_db',
'CGIS1', 'hub4_publish_db', 'addressing_truncate'
Figure 3's EXEC statement drops all the indexes and key constraints of target tables at the subscriber by calling the stored procedure from the distributor.
Now that you know how to create the drop step in the Distribution Agent job, you can implement the hub_remote_drop_save_subscribed_article_indexes stored procedure. Implementation occurs in four steps.
Prev. page
1
[2]
3
4
next page 
