Snapshots are an integral part of replication and for the most part works

well with a default configuration. Over the next couple articles I'm going to

take you on a deeper tour of snapshots so that you'll understand how the default

configuration works and some ideas for when you need to tweak the configuration.

At the end it should look a lot less mysterious and hopefully that will

encourage you to try to replication when it's appropriate. To start at the beginning, what is a snapshot? A good but over simplified

definition might be:

It's the process of quickly copying the initial set of data from the

publisher to a subscriber so that we can proceed with the next steps in

replication

Snapshots are common across all the different publication types. In snapshot

replication - the simplest - we just create the snapshot, apply it to the

subscriber, and then we're done. For transactional and merge replication

snapshots are the first step to do the heavy work of creating the initial copy

of the data on the subscriber and then we maintain it using our other

replication agents.

So let's assume that you've previously created a publication called

SnapshotTest1 on Adventureworks that contains the Person.Address table. The most

interesting thing that happens as far as snapshot behavior is that a job is

created that will execute snapshot.exe. In the image below you can see that

steps one and three are just logging, it's step two that does all the work.

But what work? The next image shows the details of the job step, and the full

command text is:

-Publisher [EARLGREY] -PublisherDB [adventureworks] -Distributor [EARLGREY] -Publication [SnapshotTest1] -DistributorSecurityMode 1

We're sending all of that as command line parameters to Snapshot.exe telling it that my laptop is configured as the publisher and

distributor, that we're publishing from Adventureworks a publication called

SnapshotTest1, and that we're using Windows security. You can find the details

about all the switches in Books Online at

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/repref9/html/2028ba45-4436-47ed-bf79-7c957766ea04.htm.

I'll add a subscriber on the same server so we have a destination, then run

the job so we can see what happens. Replication will create the repldata, unc,

and database folder (SERVERNAME_DATABASENAME format), and then create a folder

for the snapshot that is about to be created (folder name is just the

date/time). This folder can be put anywhere, just make sure that the snapshot

and distribution agents have access to the folder and that there is enough room

on the drive.

Here are the files created by the job:

So what do they mean?

• The *.BCP files contain the data from the Address table. In SQL 2000

  there would only be one file created, the rules changed a little in SQL

  2005. See

  SQL Server 2005 BCP Partitioning by Paul Ibison for a great explanation

  of the change.

• The address_2.idx file contains a script to create the indexes on the

  subscriber

• The address_2.sch file contains the script to create the table and the

  replication specific stored procedures on the subscriber

• The address_2.pre file is a pre-snapshot script (though not the same as

  the one you can configure manually) that by default drops the table on the

  subscriber if it exists

At this point the work of the snapshot agent is done. In the next article

we'll look at what the distribution agent does with the files and how/when the

files get cleaned up.