Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Snapshots - Part 1

By Andy Warren,

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.

Total article views: 2569 | Views in the last 30 days: 0
Related Articles

Snapshot Replication - deletion of snapshot folders from OS drives

Snapshot Replication - deletion of snapshot folders from OS drives


Configuring Replication

Help in Configuring Replication


snapshot replication job starts immediately when created

snapshot replication job starts immediately when created


Transactional Replication without snapshot replication (SQL Server 2005)?

Can a transactional replication be created without snapshot replication?





Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones