December 11, 2007 at 4:12 pm
Hi everyone,
Not sure anyone can help me with this, but I wanted to give it a try and see if someone else might've had to do the same thing.....
We will be moving our Production Colo Data Ceter to the new out-of-state data center soon.
I need to come up with the plan on how to move our 25 databases over to the new data center, which range from 70GB to 500MB. We are 24/7 shop and are using SQL Server 2005. The downtime on the actual cut-over date is less than 6 hours....:((
I estimated that it would take at least 3 days to do initial full backup on the databases, copy to tapes, ship the tapes, restore full backups at the new datacenter with Norecovery.
I can do Diff backups every day after the full backup and untill the cutover date. But given a large transaction volume some of our Diff backups can get pretty large on a third day, almost a size of the Full Backup. There's no way I'll be able to take the diff Backups, then copy them to the new datacenter over the wire, then restore with Recovery, all in 6 hours.
Should I consider Database Mirroring or Log Shipping instead. Not sure how that can be setup when the mirrored database is 3 days older than the its source database.
Any creative ideas would be greatly appreciated.
Thanks
Narine
December 11, 2007 at 4:49 pm
How far is the new center? I'd really look at snail mailing or driving the full over, maybe then a diff from day 2 and go with logs to catch up quickly over the wire. You'd have to do some testing and work out the transfers. Potentially FedEx could help as well.
If you're moving the center, any chance of getting enough disk storage to detach, copy them over over the drive inside the 6 hours and then reattach?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 11, 2007 at 4:57 pm
Thanks for the reply, Steve.
We're talking moving from California to Tennessee.
Fedex or UPS will definitely be required.
December 11, 2007 at 5:37 pm
Will the old data center be alive for some time, meaning will both be up?
If so, I'd really look at a Fed Ex schedule over a couple days to get the full and diff's moved. Need people on both ends to get things set up. I'd try to stagger out some of the dbs if possible. Then you can start moving logs across the wire as the fulls and diffs are restoring.
With some practice, you could probably get downtime to minutes.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 15, 2007 at 2:38 pm
I recently lead the movement of the databases for our 10TB application from one datacenter to another, and we used the same process mentioned above:
- Ship backup tapes to new colo
- Restore databases
- Copy diffs over the wire and restore
- log-ship until the cutover
A couple things that helped: ("Source" = DB in original colo, "Target" = DB in new colo)
* Add short-term additional bandwidth between the datacenters
- This allows the freedom of not having to move every database in one step.
With the added bandwidth, our infrastructure folks established a VPN between the datacenters, allowing application servers in either location (we moved over a hundred servers in this move) to access databases in either location. We are a 24/7 ASP with a high-transaction app (over 100M inserts per day), and we had it all working over three 100Mbit lines, including the log-shipping.
* Use backup compression, and script your own log shipping
- We use SQL LiteSpeed, but any of the similar products will do the trick.
We generally get better than 6:1 compression on the log backups, and we take them every 15 minutes. Another DBA wrote a nice script that runs every 15 minutes on each target server, grabbing the new logs from the source server, copying them over the wire to the new server, and restoring them.
To complete the move of a database:
1. Prevent further modification of the source database: ALTER DATABASE [DB Name Goes Here] SET READ_ONLY
EDIT: I forgot a step
2a. Back up the log on the source one last time (aka 'take the tail log backup'). You can back up read-only databases.
2b. On the target server, run the Log Copy & Restore job one last time and disable it. All transactions from the source are now applied to the target.
3. Complete the transfer by recovering the database on the target: RESTORE DATABASE [DB Name Goes Here] WITH RECOVERY
4. Update application connections strings to use the new database
5. Detach source database so incorrect connections will fail.
Steps 1-3 can be completely scripted (copy the T-SQL from the job in II and paste it in an SSMS/QA window) and ready to run when the actual time comes. Using the technique, we were able to effectively move a 5TB database in 5 seconds, from an application point of view.
* Practice a few times (!!!)
- You can start and stop your own manual log shipping without affecting the source database. Pick a few smaller databases you can copy over the wire, and run through the process (without taking the source DB offline, of course): Restore DB, run log-shipping for a while, recover DB.
-Eddie
Eddie Wuerch
MCM: SQL
December 16, 2007 at 6:47 am
Eddie has exactly what you need in the prior post. if you are a truly 24x7 shop and using transaction logs, cutting over in a few minutes during lunch will be a breeze. Practice, practice, practice... and you will be fine.
The only caveat would be the size of the circuit and the number and size of your tran logs.
Good Luck.
December 17, 2007 at 9:33 am
don't forget you are going to need server wide stuff already setup and ready to go. i.e logins, server roles, ssis , sqlagent jobs.
This can all be done up front as long as you apply a change freeze on these items at some point.
---------------------------------------------------------------------
December 18, 2007 at 7:08 am
if you have EMC SAN's just use SRDF. we mostly go R/O when we have several hours of downtime and R/W when there is a few days, like when we moved to another datacenter a few years back.
you need 2 EMC SAN's. 1 at each location and servers.
for R/O we just use SQL replication and point a few critical apps at the replicated tables while we do our maintenance and than go back to R/W. for a few apps we allow R/W and just merge the changes back into the production tables since it's usually only a few rows.
in your case i would have a talk with PHB and tell him the deal and if you need to buy some hardware to do this. easiest way is to buy new hardware, set up at new location and migrate one database at a time
December 18, 2007 at 8:32 am
Based on your DB sizes, you can probably get it done over the wire.
Use Winrar to rar them up and send the full backups over the wire. Since 70GB is your biggest DB, you shoud be able to get it down to under 20GB with good compression using winrar. You can also rar into small chunks as well, so that you can save time and transfer as the files are being created from the backup.
Once you have the fulls restored, follow up with differential and trans logs
December 18, 2007 at 10:59 am
Thanks everybody so much for your helpful suggestions.
I was glad to see that I'm on the right track with using Full+Diff+Logs Method.
I'll need to setup Log shipping and try it out.
And I started using Winrar which does have a super great compression.
Many thanks again.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply