@alt_snapshot_folder for PULL Subscribers

–Chris Skorlinski, SQL Server Escalation Services

To improve SQL Distribution agent performance when applying Snapshot, first ZIP then copy the snapshot files locally to the subscriber. This is a great solution for subscribers on WAN international networks as ZIP can be smaller and faster to copy then Replication can move uncompressed Snapshot files.  Testing shows a 1gb file can take > 10 minutes to PUSH via Replication but only 1 minute to ZIP into 25mb file, 1 minute to copy, and 1 minute to unzip resulting in Snapshot being applied in 1/2 time compared Replication to move the data.

Setup the Distribution Agent as a PULL subscriber via new Subscription Wizard or TSQL scripts specifying the @alt_snapshot_folder in the configuration settings. This instructs Distribution Agent running on the Subscriber (PULL) to retrieve local copy of the snapshot files.

— Executed on the Publisher
— Adding the transactional subscriptions
— Flags Snapshot Agent to create run creating Snapshot files.

exec sp_addsubscription @publication = …

— Start the Snapshot Agent, when complete, copy the files to local subscriber. Copy from "\unc\…" including the "unc" folder.
— Target Directory Example: X\ReplData\unc\mypublication\<snapshotdate>

— Executed on the Subscriber
exec sp_addpullsubscription @publisher =…
exec sp_addpullsubscription_agent @publisher = … @alt_snapshot_folder = N’X:\ReplData\’, …

The Subscriber Pull Distribution Agent Job History will show:

2015-11-03 20:00:40.370 Initializing
2015-11-03 20:00:40.417 Snapshot will be applied from the alternate folder ‘C:\Temp\unc\CUSTOMER\20151103145645\’

If you’ve already created a PUSH and want to use a PULL with a local snapshot folder, you can run the Distribution Agent on the subscriber specifying the AltSnapshot folder path as a run-time parameter to the Distribution Agent (distrib.exe) instead of a configuration setting during setup.


You can also change the PULL Distribution Agent Subscription Properties:


Comments (0)

Skip to main content