@alt_snapshot_folder for PULL Subscribers
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.
Tips to improve performance when applying Snapshot in Transactional Replication
You can also change the PULL Distribution Agent Subscription Properties:
