How SQL Server 2005/2008 Replication resumes interrupted snapshot delivery

Anurag Sharma
Microsoft SQL Server Escalation Services

Background

In many cases customer often have large databases published and creating a snapshot of such large database is always a pain. And, even when published databases are of manageable size, a slow or unreliable network link can further complicate the issue.

From SQL Server 2005 onwards a snapshot delivery is now capable of resuming later on from a previous interruption. This is a huge benefit in aforementioned scenarios.

SQL Server 2005/2008 a new system table MSsnapshotdeliveryprogress has been introduced to track the snapshot delivery progress. This table resides in subscription database. The table contains necessary details on what snapshot files have been applied to subscriber. For every snapshot file successfully delivered to subscriber a row is added to this table by either merge or distribution agent.

How it works

When an interrupted snapshot delivery process is restarted, the Replication (distribution/merge) agent iterates through the entire collection of snapshot files that need to be applied to the subscriber as it normally would; but with the new resumability support, the distribution/merge agent will check the MSsnapshotdeliveryprogress table to see if a file has already been applied to the subscriber by a previously interrupted snapshot delivery session prior to applying the snapshot file. If the MSsnapshotdeliveryprogress table indicates that a file has already been applied by an interrupted snapshot delivery session, the distribution/merge agent will simply skip processing of the file.

Table Structure (per BOL)

Column name	Data type	Description
session_token	nvarchar(260)	Identifies the path to snapshot folder from which the file was successfully delivered. For publications that use parameterized filters, the string dynsnap will be appended to the value.
progress_token_hash	int	A hash value generated based on the value of progress_token that is used improve lookup efficiency for a given progress_token value.
progress_token	nvarchar(500)	Identifies a file that has been successfully delivered, where the value is a combination of the file name and path.
progress_timestamp	datetime	The date time value that indicates when a snapshot file was successfully delivered

What is not resumable

This list is not comprehensive, but here are a few of the reason snapshot file would not be resumable.

Index creation (.idx) files
Declarative Referential Integrity (.dri) files (in case of merge replication only)
Applying a .bcp file to a DTS only subscriber
Any user written custom script

How to reset the MSsnapshotdeliveryprogress table

The need may arise when you need to restart the entire snapshot processes. You can execute the procedure sp_resetsnapshotdeliveryprogress on subscriber database to reset snapshot delivery process by removing all rows from MSsnapshotdeliveryprogress table. This applies to pull subscribers only.