Large DB, low capacity line, resync log-shipping options.
Home > IT Answers > SQL Server > Large DB, low capacity line, resync log-shipp...
SuperCoolMoss
140 pts.
0
Q:
Large DB, low capacity line, resync log-shipping options.
SQL Server 2000, SQL Server database, SQL Server 2005, SQL Server 2005 Database, SQL database replication
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
ASKED: Mar 18 2009  1:53 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Mrdenny
46795 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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.
Last Answered: Mar 18 2009  9:08 PM GMT by Mrdenny   46795 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

admin   0 pts.  |   Mar 18 2009  8:48PM GMT

hello

 

SuperCoolMoss   140 pts.  |   Mar 18 2009  10:03PM GMT

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

 

mrdenny   46795 pts.  |   Mar 22 2009  7:29PM GMT

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.

 
0