Sql-server – Minimising downtime implementing merge replication on live system

merge-replicationreplicationsql server

Background

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 🙂

Best Answer

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.

Related Question