MS-SQL Server 2005: Initializing a merge subscription with alternate snapshot location

Question

We started some overseas merge replication 1 year ago and everything is going fine till now. My problem is that we have now so much data in our system that any crash on one of the subscriber's servers will be a disaster: reinitialising a subscription the standard way will take days (our connexions are definitely slow, but already very very expensive)! Among the ideas I have been following up are the following:

1. make a copy of the original database, freeze it, send the files by plane to the subscriber, and initiate replication without snapshot: this is something that was done traditionnaly with older versions of SQL, but it sounds a little bit messy to me: I would have to put my publisher's data in read-only mode and stop all replications untill the operation is completed.
2. make a snapshot of the data, send the snapshot files abroad, install them on the subscriber, and indicate the new snapshot location as an alternate location in the replication properties. This one sounds fair to me (no necessity to suspend ongoing replications, no data freeze), but, on this point, Microsoft help does not ... help.

I am sure some of you have already experienced such a situation. What was your choice?

EDIT: of course, one could say "Why don't you just give a try to your ideas", but it will take hours (multiple instances of sql-servers, virtual machines, and all that stuff...), and I was thinking that the guy who did it will need only 2 minutes to explain his idea. And I'd be the most happy man if someone accepts to loose 2 minutes of his time to spare me hours of hard work ...

Answer 1

We just went through something just like this, and it is not pretty. Even though all of the servers involved were local, it still took a long time.

Just to make things more difficult, at least with SQL 2000, the snapshot will fail if the compressed cab would exceed 4 Gig.

The best advice I could offer is to make sure that each site has good backups available. With that, at least the data would not have to be hand-carried to the subscriber.

Answer 2

I had to do something similar to this when replicating data from Los Angeles, CA to China. The snap would have taken 44 days to load using normal methods.

What I did was configure the SQL Replication to use a local path to the snapshot. I then disabled the transactional job (in your case the merge job). I then ran the snap. I zipped up the snap and FTPed the files from California to China. When they got to China I unzipped them and put them in the same folder path that I used in California.

I then ran the distrib.exe from the command line on the server in China. This loaded the data into the table in china. Once the snap was loaded I shutdown the distributor on the server in China and started up the normal distributor on the server in California.

This method only took about 28 hours instead of over a month.

If your data will take more then a couple of days to get to its destination then you will need to edit the publication and increase the amount of data that can be queued up or the subscriber will be timed out and a new snapshot will need to be taken.