Tips to improve performance when applying Snapshot in Transactional Replication

1) MaxBCPThreads (contributed by Sanjaya Padhi, Microsoft SQL Server Support)

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.  Please note this does not apply when Snapshot created using default “concurrent” option.

http://technet.microsoft.com/en-us/library/cc966539.aspx

2) Alternate Snapshot Folder

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.

To see if the Network is the bottleneck examine the WAITs for “INSERT BULK” command. This command is used to load the Subscriber tables.

SELECT * from SYS.SYSPROCESSES
WHERE lastwaittype = ‘ASYNC_NETWORK_IO’

You may also see errors like these below in your distribution..msrepl_errors table or being reported in Replication Monitor

2010-05-30 14:30:11.107 Batch send failed
2010-05-30 02:19:18.323 Communication link failure   10054  MSSQL_ENG
2010-05-30 02:19:18.323 TCP Provider: An existing connection was forcibly closed by the remote host.

If you see high waiting on ASYNC_NETWORK_IO in SUSPENDED state try these steps for improving Snapshot performance.

Manually Copy Snapshot folder to 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. This section walks through these steps.

1) Create Publication then copy the Snapshot folder contents to the local Subscriber.  You can use any method to zip and/or move the files across the network. The Subscriber folder much have “UNC” folder in the path such as:

D:\LocalSubscriber\unc\CHRISSK1$SQL2K8_ADVENTUREWORKSLT_TRANUPDATE

2) Create the Subscription and specify to Initialize “At first synchronization”

3) Once the subscription is created, right-click and edit the Distribution Agent JOB properties.

4) In the Job Steps, edit the CMD line and remove the -Continuous (add it back later) and add the -AltSnapshotFolder <local folder on the subscriber>

Note you don’t specify the “\UNC” folder in the Agent Properties, however the snapshot files must reside under a \UNC folder, for example:

D:\LocalSubscriber\unc\CHRISSK1$SQL2K8_ADVENTUREWORKSLT_TRANUPDATE

5) Save the Agent Job Properties then START the JOB or START Synchronization.

— OR —

6) When the Snapshot files have been applied the Agent will show “Success.  You can then edit the Job properties and add back the –Continuous properties and if desired, remove the alternate Snapshot location.

7) To verify, you can examine the Distribution Agent History.

2010-03-07 21:03:52.229 Initializing
2010-03-07 21:03:52.250 Snapshot will be applied from the alternate folder ‘D:\LocalSubscriber\unc\CHRISSK1$SQL2K8_ADVENTUREWORKSLT_TRANUPDATE\20100307144149\’
2010-03-07 21:03:52.348 Applied script ‘Customer_2.pre’
2010-03-07 21:03:52.718 Applied script ‘NameStyle_3.sch’
2010-03-07 21:03:52.727 Applied script ‘Name_4.sch’
2010-03-07 21:03:52.737 Applied script ‘Phone_5.sch’
2010-03-07 21:03:52.920 Applied script ‘Customer_2.sch’
2010-03-07 21:03:52.944 Bulk copying data into table ‘Customer’
2010-03-07 21:03:53.222 Bulk copied data into table ‘Customer’ (440 rows)
2010-03-07 21:03:53.244 Applied script ‘Customer_2.dri’
2010-03-07 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

Another option is to modify the Publication properties and specify the Publisher’s Alternate Snapshot Location and “Compressed Backups”

Snapshot agent creates local files, then generates a CAB file which is copied across the network to the shared folder on the Subscriber.  Since compressed CAB is smaller, copy should be faster then having the Distribution Agent do a BULK INSERT across the network.  You can then specify the Subscriber’s local CAB file as the Snapshot location.

Push or Pull Distribution Agent?

–Thanks to Kendal Van *** (KendalVanDyke.blogspot.com) for the tip on Push v. Pull Distribution Agent

The Snapshot files flow through the Distribution Agent.  Therefore you need be aware if the Distribution Agent is a PUSH (running on the distributor) or a PULL (running on the subscriber).  For a PUSH subscriber and you specify the subscriber’s local Alternate Snapshot folder, the data will flow back up to the Distributor then back down to the Subscriber.  Instead, execute the Distribution Agent on the local subscriber.  This transfers the Snapshot directly from local disk into the SQL tables.

C:\>”C:\Program Files\Microsoft SQL Server\100\COM\Distrib.exe”
-Subscriber [subscriberSQL\SQL2K8]
-SubscriberDB [AdventureWorksLT_TranSub2]
-AltSnapshotFolder d:\Localsnapshot

Using Manual Copy method when Adding Articles

Another slight variation comes into play when you ADD articles to an existing publication.  The Distribution Agent ignores the -AltSnapshotFolder setting for articles added to the publication after the initial snapshot has been applied.  If you want to use an alternate snapshot location when adding a new article, you must first specify the snapshot location in the Publication properties then add the article. When you run the Snapshot Agent the newly added article will be BCP’ed into the new snapshot location.

Here is an example of how you can make this work for you when adding large tables to an existing publication.

1. 1. Change Snapshot location to C:\Snapshot  (for example).
   2. Add the new article to the publication.
   3. Generate a new Snapshot.  The Snapshot Agent will generate snapshot files only for the new article.
   4. ZIp/Compress the new Snapshot folder then copy files to an identical path on the subscriber

If you’ve configured a PUSH Distribution Agent, stop that Distribution Agent and start the Distribution Agent on the Subscriber from a cmd line.  By default, the agent will look in the same folder set in the publication properties (c:\Snapshot  for example).  However, since you are running the Distribution Agent on the Subscriber it will look in the subscriber’s C:\Snapshot folder instead of the publishers.

You can get all of the parameters for the Distribution Agent from the Agent Job Properties.  The BLOG posting below shows how to find the Agent job properties to use on the CMD line.  You only need to specify the location for the Distrib.exe as shown above.

KB article 312292 How to enable replication agents for logging to output files in SQL Server