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.
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:
Resources used in my research to address this issue:
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!