We have three Servers running SQL Server 2008 which all contain a Product database. These are currently kept in sync once per day by sending a copy of the MDF to the two Subscribing servers and detaching the existing DB and replacing it with the latest copy.

The two subscribers contain a Customer database which is not present on the publisher. The Customer database contains customer purchase information, so it needs to reference ProductIDs in the Product table.

We have implemented Merge Replication on a similar set up in our testing environment and are preparing to implement these changes on our live system.

The end result should look something like:

enter image description here

The Problem

In order to implement these changes, we will need to generate a snapshot and synchronise the data between the Publisher and the Subscribers. While this is occurring, any client application which relies on the data in the Product database will fail, and any Stored Procedures on the Customer database which make cross database joins will also fail.

We'd like to keep this downtime to a minimum or eliminate it completely.

We've had some crazy ideas thrown around which may or may not work (using synonyms to point a subscriber to another server's Product table while snapshot generation/synchronisation takes place) but they don't seem very elegant, if they'll work at all. I'm hoping someone else has had a similar issue and might have some insight :)

1 Answer 1


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:


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.

  • Thanks Brandon - we'll be attempting this when we do eventually go live in the next few weeks :) Commented Mar 20, 2013 at 7:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.