Assigning Snapshot and Distribution agents. After you've defined and configured the publication and subscriptions, Enterprise Manager's Create Publication Wizard assigns a Snapshot Agent to each publication. Further, by default, Enterprise Manager's Create Push Subscription Wizard assigns only one Distribution Agent to distribute all publications in a database that a subscriber subscribes to. For example, a subscriber might subscribe to both publication A and publication B from the same publisher and publication database. By default, only one Distribution Agent serves these two publications to the subscriber. If the Snapshot agents of these publications run at the same time, you can't distribute the two snapshots in different schedules. To make the distribution schedule more flexible for each publication in a large-scale replication environment, I prefer to assign one Distribution Agent to publication A and one to publication B. To assign a separate Distribution Agent to each publication in a subscription, go to the publication's Properties window, click the Subscription Options tab, then select the Use a Distribution Agent that is independent of other publications from this database option, as Figure 1 shows. For each publication, you can view the result of this configuration in Enterprise Manager by following the path that the left pane in Figure 2 shows. The right pane shows one Snapshot Agent and two Distribution agents (one for each publication in each subscription) for the pubs1 publication in the Pubs database highlighted in the left pane.
Assigning Snapshot and Distribution Agent jobs. Enterprise Manager automatically defines a SQL Server job for each Snapshot Agent and each Distribution Agent. The agents carry out 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. Figure 3 shows these three steps on the Steps tab in the CGIS1-EGH-address-28 Properties-CGIS1 window. Figure 4 shows the command that runs during the Run agent step—which executes snapshot.exe. The command's full syntax is
snapshot -Publisher [CGIS1] -PublisherDB [EGH]
-Distributor [CGIS1] -Publication [address]
-DistributorSecurityMode 1
Snapshot.exe copies the schemas, indexes, and records of published articles from the publisher to the distributor. Then, in the distributor's Snapshot folder, snapshot.exe saves the article schemas in .sch T-SQL script files (as the code snippet in Figure 5, page 48, shows) and saves the indexes in .idx files (as Figure 6, page 48, shows). Finally, the records are stored in.bcp files. Each article has one set of .sch, .idx, and .bcp files.
Also by default, a Distribution Agent's job consists of the same three steps as that of a Snapshot Agent. However, its Run agent step executes distrib.exe, which the code in Figure 7, page 48, shows. Distrib.exe's full syntax is
distrib -Subscriber [HERTSCHEN3] -SubscriberDB [sde]
-Publisher [CGIS1] -Distributor CGIS1
-DistributorSecurityMode 1 -PublisherDB [EGH]
First, distrib.exe applies the schemas in all the .sch files to a subscription database (for convenience, I call it the sch step). Next, it uses bcp to copy the records from all the .bcp files to the target tables that the sch step creates (the bcp step). Finally, it applies the indexes in all the .idx files to the copied target tables (the idx step).
Snapshot.exe generates each set of .sch and .idx files according to the article properties you set in the Snapshot tab of the respective article's Properties dialog box, as Figure 8, page 49, shows. For example, selecting the Delete data in the existing table that matches the row filter statement option in Figure 8's ADDRESS Properties dialog box adds the DELETE FROM [ADDRESS] statement to the .sch file that Figure 5 shows for the ADDRESS article. However, if you select the Delete all data in the existing table (using TRUNCATE) option in Figure 8, a TRUNCATE TABLE [ADDRESS] statement results instead. In addition, selecting the Copy...indexes options in Figure 8 adds the CREATE INDEX statements to the .idx file that Figure 6 shows for the ADDRESS article. Conversely, clearing all Copy...indexes options empties the .idx file. Note that in SQL Server 2000, indexes are always saved to the .idx file even if you clear them in Enterprise Manager. To force an empty .idx file, use the sp_addarticle or sp_changearticle store procedures with schema_option = '0x01', as the following example shows:
sp_changearticle 'pubs', 'authors', 'schema_option', '0x01', 1, 1
sp_addarticle @publication = N'pubs', @article = N'authors', @schema_option =
0x0000000000000001, ...
Prev. page
1
[2]
3
next page 
