21:03:53.260 Delivered snapshot from the ‘unc\CHRISSK1$SQL2K8_ADVENTUREWORKSLT_TRANUPDATE\20100307144149\’ sub-folder in 1014 milliseconds Specify Publication Alternate Snapshot folder on SubscriberĪnother option is to modify the Publication properties and specify the Publisher’s Alternate Snapshot Location and “Compressed Backups” 21:03:53.222 Bulk copied data into table ‘Customer’ (440 rows) 21:03:52.944 Bulk copying data into table ‘Customer’ 21:03:52.250 Snapshot will be applied from the alternate folder ‘D:\LocalSubscriber\unc\CHRISSK1$SQL2K8_ADVENTUREWORKSLT_TRANUPDATE\20100307144149\’ You can then edit the Job properties and add back the –Continuous properties and if desired, remove the alternate Snapshot location.ħ) To verify, you can examine the Distribution Agent History. Note you don’t specify the “\UNC” folder in the Agent Properties, however the snapshot files must reside under a \UNC folder, for example:ĥ) Save the Agent Job Properties then START the JOB or START Synchronization.Ħ) When the Snapshot files have been applied the Agent will show “Success. The Subscriber folder much have “UNC” folder in the path such as:ĭ:\LocalSubscriber\unc\CHRISSK1$SQL2K8_ADVENTUREWORKSLT_TRANUPDATEĢ) Create the Subscription and specify to Initialize “At first synchronization”ģ) Once the subscription is created, right-click and edit the Distribution Agent JOB properties.Ĥ) In the Job Steps, edit the CMD line and remove the -Continuous (add it back later) and add the -AltSnapshotFolder
You can use any method to zip and/or move the files across the network. This section walks through these steps.ġ) Create Publication then copy the Snapshot folder contents to the local Subscriber. One option is to manually copy the entire Snapshot file to the local server, then specify the local Snapshot directory as the source for the Distribution Agent. Manually Copy Snapshot folder to Subscriber If you see high waiting on ASYNC_NETWORK_IO in SUSPENDED state try these steps for improving Snapshot performance. 02:19:18.323 TCP Provider: An existing connection was forcibly closed by the remote host. You may also see errors like these below in your distribution.msrepl_errors table or being reported in Replication Monitor
This command is used to load the Subscriber tables.
To see if the Network is the bottleneck examine the WAITs for “INSERT BULK” command. When applying Snapshot across the network for Transactional Replication, network delays can have significant performance impact and increase time it requires to apply a Snapshot. Please note this does not apply when Snapshot created using default “concurrent” option. I found we can add in the below point to increase the perf of the snapshot application by the distribution agent by use of MaxBCPThreads parameter as mentioned in the article below. 1) MaxBCPThreads (contributed by Sanjaya Padhi, Microsoft SQL Server Support)