4

We have a largish database (350GB) which is replicated to a subscriber which is georaphically in a different location. Unfortunateley we have a slow connection between the two sites.

Today we're getting the following error at the distributor:

The subscription(s) have been marked inactive and must be reinitialized.

Is there anyway we can kick this back into life without rebuilding the subscription from a snapshot?

Alex Angas
  • 2,017
  • 2
  • 26
  • 37
SuperCoolMoss
  • 1,262
  • 11
  • 20
  • We resynced the replication by couriering a usb drive containg a backup of the primary database. We used the stored procedure sp_scriptpublicationcustomprocs to create the necessary replication store procedures on the subscriber. Thanks for the good replies - I've chosen Mr. Dennys' answer as we may use this method in the future. – SuperCoolMoss May 30 '09 at 08:48
  • We've also increase the subscriber timeout on the distributor. – SuperCoolMoss May 30 '09 at 09:04

4 Answers4

5

I've used this trick before to kickstart replication. Assuming that the sites have faster internet access then the link between them.

Setup your replication, but use a local path that exists on both servers for the snapshot location. Run the snapshot. Compress the text files that are created (they will compress a lot). FTP the files to an outside FTP server that you can access from both sites. When they are done uploading, download them at the other site. Decompress them and put them in the correct folder. Startup the distribution agent at the REMOTE site so that it is reading the local files and inserting into the local database. Once it has loaded up the snapshot you can run the distribution agent at which ever site you prefer.

I used this technique to setup replication between the US and China and it cut the time from 43 days to 17 hours which is well within the subscriber timeout window.

mrdenny
  • 27,174
  • 4
  • 41
  • 69
1

I have also used Denny's tricks, but you may also need to optimize the traffic flow to get optimal speed. There are two different areas you have to tweak, SQL Replication and Network TCPIP tweaks. I have TCPIP written up on a blog http://www.sqlwebpedia.com/content/tcp-optimization-windows and one now for Replication: http://www.sqlwebpedia.com/content/transactional-replication-over-wan-tips HTH

SQLGuyChuck
  • 114
  • 5
0

I do not believe there is a way to re-initialize without a new snapshot, at least I've always had to and I can't find anything now either.

I thought I had seen some kb article describing a way to adjust the time out for slow connections, but that seems to have disappeared, or may have been a dream.

I'm going to keep watching this space to see if some guru knows of way.

jhayes
  • 486
  • 2
  • 6
  • 12
  • You can increase the timeout by selecting Configure Publishing, subscribers and Distribution and click properties on the distribution agent. – SuperCoolMoss May 30 '09 at 09:09
0

There's a new initialize-from-LSN method but it was only introduced in 2008. There's no way to do the equivalent in earlier versions, including 2000. Sorry.

Paul Randal
  • 7,194
  • 1
  • 36
  • 45