SQL 2005 Replication - How do I get the DA Profile MaxBCP option to work with a Transactional Publication?

Question

I have two publications on a database. One is a transactional publication, the other is a snapshot publication. We have a stand-alone Distributor (separate from the publisher) and both publications have push subscriptions (to the same database on the Subscribers). There are two subscribers (our client is transitioning from SQL Server 2005 to SQL Server 2008 R2, so we're pushing the data to both machines currently). The Publisher and Distributor are both running SQL Server 2005.

I'm using the same Distribution Agent Profile for both publications. When the snapshot is applied at the subscriber, the snapshot publication will honor the MaxBCP setting and push multiple BCP files simultaneously. Unfortunately, the Transactional publication (which is configured to use the same profile for the Distribution Agent) will only push the BCP files serially.

We have a pretty fast link between our site and the subscriber (on the other side of the country), but the single streams only utilize about 2-2.5 Mbit. Utilizing multiple BCP would allow us to take full advantage of the bandwidth. We tested setting MaxBCP to 16 with the snapshot publication and that actually allowed us to burst up to 35 Mbit with an average rate of about 29Mbit.

We're planning on migrating the database to SQL Server 2008 R2 on our side as well and we'd like to take full advantage of the available bandwidth when we reinitialize it on SQL 2008 R2 as the DB is very large (~250 GB).

Answer 1

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

To address the issue we needed to recreate the publication and specify the sp_addpublication argument @sync_method = 'native'.

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.

Also 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.

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.

Now that I've recreated the publication using the argument @sync_method = 'native', I can now push multiple BCP files simultaneously to the subscriber, thus taking full advantage of the large pipe between our site and our customer's site.

I just finished blogging on this topic (with more detail) here: http://mattslocumsql.blogspot.com/2012/06/distribution-agent-for-transactional.html

Hilary Cotter's swift response was invaluable to solving my issue. Thank you again, sir!