The Snapshot Agent

As with all replication agents, the Snapshot Agent is a console mode application that uses ODBC to communicate with SQL Server. The executables for SQL Server's replication agents reside in the 80COM subfolder under the Microsoft SQL Server folder. When you set up a snapshot publication, SQL Server creates a SQL Server Agent job that runs the Snapshot Agent. You can view this job by opening the appropriate entry under the AgentsSnapshot Agents node in the Replication Monitor on the distributor (or via the Jobs node under ManagementSQL Server Agent) in Enterprise Manager. Each snapshot job consists of several steps, one of which is to run snapshot.exe, the executable for the Snapshot Agent. The job step that actually runs the Snapshot Agent is configured as being of type Replication Snapshot, which tells SQL Server Agent to run snapshot.exe when it executes the step.

You can explore the Replication Snapshot job step to see what parameters are passed to snapshot.exe by default. Depending on how you've set up your snapshot publication, the parameters you see should look something like the following:

-Publisher [TUKPHRIP] -PublisherDB [pubs] -Distributor
    [TUKPHRIP] -Publication [pubs_sales] -DistributorSecurityMode 1

Note that you can run snapshot.exe independently of SQL Server Agent. Exercise 21.1 takes you through doing just that.

Exercise 21.1 Running a Replication Agent from the Command Line

exec sp_addpublication @publication = N'pubs_titles',
    @restricted = N'false', @sync_method = N'native',
    @repl_freq = N'snapshot', @description = N'Snapshot
    publication of pubs database from Publisher TUKPHRIP.',
    @status = N'inactive', @allow_push = N'true', @allow_pull =
    N'true', @allow_anonymous = N'true', @enabled_for_internet =
    N'false', @independent_agent = N'true', @immediate_sync =
    N'true', @allow_sync_tran = N'false', @autogen_sync_procs =
    N'true', @retention = 336, @allow_queued_tran = N'false',
    @snapshot_in_defaultfolder = N'true', @compress_snapshot =
    N'false', @ftp_port = 21, @allow_dts = N'false',
    @allow_subscription_copy = N'false', @add_to_active_directory =
    N'false'
exec sp_addpublication_snapshot @publication = N'pubs_titles',
    @frequency_type = 8, @frequency_interval = 64,
    @frequency_relative_interval = 0, @frequency_recurrence_factor
    = 1, @frequency_subday = 1, @frequency_subday_interval = 0,
    @active_start_date = 0, @active_end_date = 99991231,
    @active_start_time_of_day = 10600, @active_end_time_of_day = 0

Microsoft SQL Server Snapshot Agent 8.00.194
Copyright (c) 2000 Microsoft Corporation

Generating schema script for article 'titles'
Bulk copying snapshot data for article 'titles'
Bulk copied snapshot data for article 'titles' (18 rows).
Inserted schema command for article 'titles' into the
distribution database
Inserted index creation command for article 'titles' into the
distribution database.
Inserted bcp command for article 'titles' into the distribution
database.
A snapshot of 1 article(s) was generated.

The process finished. Use CTRL+C to close this window.

The Snapshot Agent creates files containing the data and schema for a publication's articles under the snapshot folder. By default, this folder is located under the ReplData folder under the root folder of your distributor's SQL Server installation but can be changed via the Publication Properties dialog in Enterprise Manager. Each publication gets its own snapshot folder. This folder name is composed of the name of the server, instance, and database from which the publication is publishing data as well as the name of the publication itself. Under each publication snapshot folder, there are two subfolders: unc and FTP. The unc path is the container for UNC path-based publication snapshots. The FTP subfolder is the container for FTP-based publication snapshots. Unless you explicitly enable a publication to be distributed over FTP, you should find its snapshots in a subfolder under the unc path.

Each generated snapshot gets its own subfolder under either unc or FTP. The name of this subfolder consists of the current date and time when the snapshot was generated. This subfolder contains the actual snapshot files themselves.

If you configure a publication to generate its snapshot in an alternate location (via the Publication Properties dialog) and you do not disable the generation in the normal snapshot folder, the snapshot will actually be written to both folders. If you enable the publication to be distributed via FTP, you can specify the relative path that an FTP client will use to access the files.

The snapshot files themselves are a mix of BCP data and Transact-SQL scripts. The data is saved in BCP format and has a file extension of .bcp. If you configured the publication for distribution only to other SQL Servers and did not allow it to be transformed via DTS, this file is written in BCP's native format (because this is generally faster); otherwise, character format is used. The other files (e.g., .sch, .dri, .trg, .idx, and so on) contain the Transact-SQL necessary to create each article's object, its declarative referential integrity, its triggers, and its indexes, as appropriate.

Besides storing the data for a table, the BCP files created by the Snapshot Agent also store the data returned by view objects. Just as you can use the BCP utility to write the results of a query against a view to a file, the Snapshot Agent will create a snapshot of the data returned by a view as though the view contained the data itself.

When the Snapshot Agent runs, it determines whether any new subscriptions have been added since the last time it ran. If no new subscriptions have been created, the agent doesn't need to create new scripts or data files. However, if a publication was created with the option to generate its snapshot immediately, the Snapshot Agent will create a new snapshot for it each time the agent runs.

Note that the Snapshot Agent is used to generate snapshots not only for snapshot replication but also for transactional and merge replication. Regardless of the type of replication, an initial snapshot is needed in order to seed a subscriber with data.

Once a snapshot has been generated for a publication, either the Distribution Agent (for snapshot and transactional replication) or the Merge Agent (for merge replication) picks it up and distributes it to subscribers. You can also take the files from the snapshot folder and transfer them to the subscriber manually. This is often done when setting up a remote subscriber for the first time that has a low-bandwidth connection to the distributor. By putting the initial snapshot on, say, a CD, you allow the site to be set up more quickly than if it had to wait on the snapshot to be transferred over a slow WAN link.