Large DB, low capacity line, resync log-shipping options.

Microsoft SQL Server 2000
SQL Database
SQL Server 2005
SQL Server 2005 Database
SQL Server database
Hello, We've a large SQL2000 DB which is 335GBs in size and a majority of tables are geographically replicated to a subscriber using transactional replication across a 10Mbit line. We're going through the process of converting the DBs' environment to SQL 2005. Unforetunately I think this means we'll have to re-synch replication (either using snapshot, or via backup) We need as little an outage as possible. Pushing a snapshot across the 10Mbit line will take too long. Backing up the database with compression software (backup compresses to 68Gb) and copying it will also take too long. I'm considering dropping all the non-clustered indexes first and then backing up the DB this will reduce the backup size by 50%, although I still think this will take too long to copy. We may have to get this onto an external disk and courier it - but this presents its own problems around security. Any other ideas appreciated :) Regards, SCM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Yes, the upgrade will require that you resend the snap.

Setup the replication using a local path for the snapshot (C:\something\). Make sure that its a folder that each subscriber has available with enough space for the snap.

After the snap is created, zip it up (its just text at this point and should compress nicly).

Upload it to a webserver that has a lot of bandwidth available (get a hosting account at GoDaddy or something if needed). Then at each site download the file and decompress it to the snapshot folder specified. Run the distribution agent on the remote machine to load the snap. (If you are using a pull subscription this will already be the default, if you are running a push subscription then stop the job on the distributor, and run the command manually on the subscriber).

I’ve used this technique to setup replication from the US to China over a T1 (1.5 Meg line). SQL Replication would have taken 44 days to get setup, this technique took less than 2. This should reduce your outage time to a few hours at most. You can test this by taking the snapshot, zipping it up and seeing how long it takes to upload then download. After it is zipped up it may be small enough that you can transfer it over the leased line.

Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • admin
    0 pointsBadges:
  • SuperCoolMoss
    Thanks for the answer Mr D. There's one publisher and one subscriber (both clustered, SQL2000 32bit, Windows 2003 with SAN storage). Were migrating both environments to new 64bit clusters on new SANs, upgrading to SQL 2005 64 bit. The new 64 bit environments are currently being built. Would it be possible to stop access to the application, wait for replication latency to reach zero, backup the SQL2000 database on both subscriber and publisher, copy the backups to the corresponding new environments over faster local network, restore both databases effectively upgrading to SQL2005, and setup replication afresh, resynching without having to send anything down the slower line? Regards, SCM
    140 pointsBadges:
  • Denny Cherry
    Honestly I don't know. I'm not sure how that would work. Assuming that the new publisher is setup as a valid publisher on the distributor, in theory it should work, you will need to edit some values in the replication tables on the publisher, distributor, and subscriber as well as edit the distribution agent jobs manually to ensure that they are pointing to the new system first. I would definitely test this in a lab first.
    69,115 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: