The Distribution Agent for a Transactional Publication Will Ignore the MaxBCPThreads Setting by Default

The company I work for hosts a SaaS solution for multiple customers, and we utilize transactional replication to push a copy of the customer's data from our SQL servers to a SQL server on-site at the customer's location.  This allows much greater reporting flexibility without impacting the performance of the production SQL Server.

We are currently migrating our largest customer to a new SQL server (which requires us to set up replication from scratch and generate/push a new snapshot to initialize the new transactional publications).  By default, SQL Server ignores our setting for MaxBCPThreads in the Distribution Agent profile and will only push the BCP files serially.  With this customer we have a large pipe between our data center and the customer's location (on the opposite coast), but serial snapshot delivery only allows us to consume about 10% of the available bandwidth (even with the maximum packet size option of -PacketSize 32767 in the command line of the Distribution Agent job).

In short, to address the issue we needed to recreate the publication and specify the sp_addpublication argument @sync_method = 'native'.  However, the reason why is a bit obscure.

According to SQL Server 2005, 2008 and 2008 R2 Books Online, the default @sync_method is character for snapshot publications (non-concurrent character mode BCP output) and concurrent_c (concurrent character mode BCP output) for all other publication types. However, in my experience, the actual defaults are native (non-concurrent native mode BCP output) for snapshot publications and concurrent (concurrent native mode BCP output) for all other publication types.  This is actually a good thing as the native SQL BCP snapshot format is faster/more efficient to deliver to the subscriber. The character formats are only necessary for publications with non-SQL Server publishers.

The default in SQL Server 2000 was native and according to SQL Server 2012 Books Online, the default has been changed back to native for all SQL Server publications.

So what is the difference between native and concurrent?  Books Online indicates that utilizing the concurrent option "Produces native-mode bulk copy program output of all tables but does not lock tables during the snapshot."  Whereas, the native option locks the tables for the duration of the snapshot generation.  However, this is not the whole story. When using the concurrent option the tables are not locked for the entire duration of the snapshot generation, but the very last step of the Snapshot generation process is to lock the tables to capture the delta. This may still cause blocking (it does in our environment), but the impact should greatly reduced as the tables are locked for a much shorter period of time.

So what does this have to do with the snapshot delivery? In addition to affecting the snapshot generation, the concurrency setting also affects snapshot delivery at the subscriber. When using the concurrent sync_method, the Distribution Agent will ignore the MaxBCPThreads setting (in the Agent Profile) and deliver the BCP files in the snapshot serially. With smaller databases this is not a major issue. However, in my case, I'm trying to push snapshots that are around 100GB and serial delivery of the BCP does not take advantage of the amount of bandwidth we have between our site and our customer's site.  From SQL Server Books Online: When applying a snapshot that was generated at the Publisher using the concurrent snapshot option, one thread is used, regardless of the number you specify for MaxBcpThreads.

By utilizing the native snapshot process (and increasing the MaxBCPThreads value from the default of 1 to 16), I can now push 16 BCP files simultaneously to the subscriber, thus taking full advantage of the large pipe between our site and our customer's site.  There is no upper limit to what MaxBCPThreads can be set to, but you don't want to set it too high and overwhelm the CPUs on the distributor or subscriber.  I've successfully tested 32 simultaneous threads on a system with only 16 cores (no hyper-threading).

Note: You cannot change the concurrency setting of a publication on the fly.  You will need to drop and recreate the publication via a T-SQL script.  You can use one of the following methods to script the publication creation:

• Right-click the existing publication in SQL Server Management Studio and choose Generate Scripts... (I usually output to a new Query Editor window and then save the script once I've made the appropriate changes)
• Go through the GUI to set up the publication and choose Generate a script file with steps to create the publication (save the file to your hard drive and then open with SSMS once script generation is complete)

Once you have the script, change @sync_method = 'concurrent' to @sync_method = 'native'.  You will also want to verify that any SQL Server authentication passwords are correct (for security purposes, SQL Server will not extract the passwords from the existing publication).


Resources used in my research to address this issue: 

BOL SQL 2005: http://technet.microsoft.com/en-us/library/ms188738(v=sql.90).aspx

BOL SQL 2008: http://technet.microsoft.com/en-us/library/ms188738(v=sql.100).aspx

BOL SQL 2008 R2: http://technet.microsoft.com/en-us/library/ms188738(v=sql.105).aspx

BOL SQL 2012: http://technet.microsoft.com/en-us/library/ms188738.aspx

Greg Robidoux's very helpful post: http://www.mssqltips.com/sqlservertip/1270/transactional-replication-snapshot-issues-in-sql-server/ 

SQL Server Central post on the topic:http://www.sqlservercentral.com/Forums/Topic1129095-291-1.aspx

I'd specifically like to thank Hilary Cotter for providing a very swift answer to my dilemma.  His contribution to the SQL Server community is greatly appreciated!