Sql-server – Can a replication subscription also be used as a publication

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!

The biggest time saving option is to generate the snapshot and compress it with your favorite program, like 7-Zip, copy the compressed snapshot to the subscriber using file-copy or FTP, and then apply it locally using the -AltSnapshotFolder Merge Agent parameter. The existing Product database can be left in place while the snapshot is being copied over and client applications can operate normally.

The way this works is open the SQL Agent job for the Subscriber you wish to deploy the snapshot to and get the Run Agent. command. It looks something like this:

-Publisher [PACIFIC] -PublisherDB [TestDB2] -Publication [TestMergePub1] -Subscriber [PACIFIC] -SubscriberDB [TestSubDB2] -Distributor [PACIFIC] -DistributorSecurityMode 1

Then, grab the snapshot files from the publisher and compress it. For example, on my server I compress the snapshot folder found in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\repldata\unc. I then copy the snapshot folder to the subscriber using file-copy or FTP and decompress it in a folder called C:\TEMP\unc.

So looking at this on the Subscriber I would see this for the snapshot folder:

C:\TEMP\unc\PACIFIC_TESTDB2_TESTMERGEPUB1\20130207200123

Then from the command line on the Subscriber, run this:

c:\Program Files\Microsoft sql server\100\com\replmerg.exe -Publisher [PACIFIC] -PublisherDB [TestDB2] -Publication [TestMergePub1] -Subscriber [PACIFIC] -SubscriberDB [TestSubDB2] -Distributor [PACIFIC] -DistributorSecurityMode 1 -AltSnapshotFolder C:\TEMP

This will apply the snapshot locally and will be significantly faster then applying it over the wire. Downtime at the Subscriber will be minimal.