I ran across an interesting problem the other day. I was setting up replication for a couple of VERY large tables. These two tables between there are about 553 Gigs in size. There isn’t enough room on the distributor, and there is a slow link between the distribution server and the subscriber. After 6 days of the snapshot trying to be pushed to the subscriber I killed it because it just wasn’t getting anywhere fast.
Now normally I would just copy the snapshot to the subscriber and run the distribution agent on the subscriber while the snapshot loads (I thought I had a blog post about this but apparently I don’t, so I’ll blog about that later). The problem with doing that in this case is that the distributor doesn’t have enough drive space to create the snapshot and being that the servers are hosted with RackSpace adding 600 Gigs of SAN drive space to this cluster would cost about about $12,000 a month with a one year contract on the new storage. Fortunately there’s another cluster that has enough DAS space available so I can simply point the replication agent to use a network share on this drive instead by using the sp_changepublication procedure as shown below.
[sql]exec sp_changepublication @publication=’PublicationName’, @property=’alt_snapshot_folder’, @value=’\ServerNetworkShare'[/sql]
However when I get to the subscriber I don’t want to read from the network share. I want to copy the files to the local disk and read from there. There’s no switch when running the distribution agent manually so before starting the distribution agent on the subscriber you have to manually edit the dbo.syspublications table on the subscriber, specifically editing the alt_snapshot_folder column so that it points to the local path that the files will be stored in on the subscriber. You can then run the distribution agent on the subscriber and the snapshot will load. Once the snapshot is loaded kill the snapshot on the subscriber and start it on the distributor as normal and all will once again be well with the world.
A question that I get when I talk about doing this is why copy the files over the network instead of just letting the distribution agent load the snapshot remotely. I’ve found (and I’ve done this several times to deal with slow networks) that the time spent compressing the snapshot files (and they compress really well as they are just text) and copying them over the network is almost way less time then it would take to load the snapshot remotely from the distributor.
In the case of this project the publisher and distributor were in Dallas and the subscriber was in New York City. But I’ve used this same technique from Orange County, CA to Dallas Texas; from Dallas, Texas to Los Angeles, CA; and from Los Angeles, CA to Beijing, China. In every case it’s a little slow, it’s a little annoying, but it works (in the other cases the distributor did have enough space to hold the snapshot so I didn’t have to modify the dbo.syspublications table, but everything else I did was the same).
Kendal Van Dyke (blog | @SQLDBA) has another great technique that he posted a couple of years ago.
Denny
