Moving Database Servers between DataCenters

Question

Moving Database Servers between DataCenters

hammer88

SSC Journeyman

Points: 82
More actions
March 29, 2005 at 11:22 am

#110357

Hello everyone, this is my first post on this forum.
I will be needing to migrate a production server from one datacenter (Atlanta) to a different datacenter (Burbank).
My questions is what the most pain free method is of making this migration. The datacenters are on different backbones. The servers have different drive letters as the logs will now be on its own dedicated RAID1 and the data on RAID10 instead of RAID5. Whoo hooo...
The data files are roughly 45GB and backups are roughly 6GB(using litespeed).
My downtime window is Friday afternoon through Sunday noon (possibly Monday morning).
My options I believe are to either transfer all mdf and ldf files and reattach them at the new server. Will the new drive letters and folder structure affect this? I may not be able to transfer 45 GB in the time frame I have.
My other option is to probably take a complete backup the week before the cutover of the actual production databases and simply do a restore with standby and apply the transactions logs during the downtime. Then manually sync the logins/roles, schedule all jobs, and manually copy the dts packages then modify the source objects for the new server.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 1

We recently transferred a > TB database from a data center in Canada to the UK using a variation on the second method you describe above.

1 week before Full Backup

1 day before Differential Backup (don't need to transfer so many Tlog backups)

during the day Hourly Transaction Log backups

It took a fair amount of planning and logistics, have you ever tried getting external hard drives and tapes through customs , but we had the entire database up and available just 4 hours after we switched off access to the one in Canada, mainly because it took us that long to transfer the final backup (we still took the rest of the weekend to repopulate the full text indexes and run DBCC checks and reindexing but to be honest it all went like a dream).

Martin Mojzisek SSCrazy Points: 2207 More actions · Answer 2

check this article and the additional links :

http://vyaskn.tripod.com/moving_sql_server.htm

athurgar SSCrazy Points: 2816 More actions · Answer 3

Moved 100GB database from one datacenter to another using the backup, transaction log method.

Just need to know how long it will take your backup to copy over the network and restore - couple of days should be plenty and hourly transaction log backups.

Good luck.

hammer88 SSC Journeyman Points: 82 More actions · Answer 4

Thanks for the replies fellas.

When you guys made that move, did you backup and restore the master, msdb, tempdb as well? and if you do that, will that transfer the dts packages, scheduled jobs? I'm assuming it will transfer the logins/roles just fine.

Aaron Templeton SSCarpal Tunnel Points: 4655 More actions · Answer 5

I don't think you are allowed to backup the tempdb. If you made some customizations to it I think you have to do those manually. msdb will port your jobs. I expect that porting the master database will make your life easier. Those who know more than I can give you more concrete answers, but there are some quick ones for you.

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 6

We couldn't backup and restore the system databases because we were moving from 32-bit to 64-bit servers (although I don't think I would have done it that way anyhow)

We have all of the logins and roles documented and recreated them up front (giving them new passwords on the way). I scripted up all of the scheduled tasks, custom alerts and the like and re-applied them to the MSDB database at the new site. Transferring the DTS packages was as simple as opening them and using save as to move them to the new server.

The only thing that may need a little extra massaging is orphaned logins, we didn't take all of the logins over (lots weren't needed so we didn't bother), this left several orphans which had to be dealt with via the sp_change_users_login stored procedure.

Feel free to ask anything else you want, if I've got the time then I don't mind helping out by answering a couple of questions

Edited to say: why would you want to backup the TempDB database? it's recreated from scratch each time you restart SQL so there wouldn't be any point.

Vik Nat Hall of Fame Points: 3930 More actions · Answer 7

I have moved large DB's in the past from one physical location to another and the way - I have achieved this is by taking a full backup using WINRAR to break the BAK file into smaller chunks like 100MB ( easier to manage transfer failers) and then FTP them over to the new location - UNRAR the files, restore the BAK and put it in the standby mode. I then ship tran logs on schedule and apply them to the standby DB. So when I am ready to make the switch - I apply the last trans log and bring up the DB from the standby.

I did not have any DTS packages - but I had a lot of SQL Jobs. I did not move any of the system DB's, but scripted out all the SQL jobs and ran the script on the DB server at the new location.

You will need to fix the logins if you do not copy over the master table. You can use the sp_change_users_logins to accomlish that.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 8

Some more thoughts ... just because the diferent data centers are on different backbones does not prohibit data transfer. You need to chat with the network fo0lks to find out 2 things.

1) can they be 'bridged' (can the 2 separate backbones talk to one another ?

2) if thay can 'talk', the size of the 'pipe' between them. If your 'pipe' is OC3 or larger (with bandwidth to spare, a copy of a 45 Gb backup should be pretty quick ... less than 3-4 hours).

At a bank I worked at within the last 2 years, they did quarterly disaster recovery tests to a remote site. The re-sync time (time to recopy all the current production data and database backups via volume copy after the test) took between 8-10 hours on a dedicated OC3. The data volume many times greater than 45 Gb (I forget the actual numbers). So if your new server is already built and configured, the Friday night to Sunday window should be more than sufficient.

My guess is that if you started at 6:00 PM Friday and did:

- backup on the old server

- copy/ftp the backup to the new server

- loaded all users/roles/etc (if they have not been already)

l- oaded the database

- performed DBCC's just to be a good DBA

You'd probably be home prior to midnight.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

hammer88 SSC Journeyman Points: 82 More actions · Answer 9

Sounds very encouraging. Thanks for all the pointers. I think what I will test out is the transfer time between these two datacenters. If all is well.

I think this will be my plan on Friday afternoon:

- Put the database into single user mode

- Do a complete backup using Litespeed to get my production databases to a 6-10GB file size

- Do a straight copy of the master and msdb mdf/ldf (150MB). I believe this should alleviate needing to recreate the jobs and needing to resolve logins, correct?

- Then resave all the dts packages.

- Run the checks as suggested by everyone. What would be the basic dbcc check that are run after a database move to verify all is well?

You guys are AWESOME!!!

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 10

The standard DBCC triage is:

CHECKDB()

CHECKALLOC()

CHECKCATALOG()

You may also want to execute UPDATE STATISTICS and sp_recompile for each table since you'll have all of that extra time.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 11

Sound like you've got yourself a plan

Seeing as you've got all weekend you'll probably have loads of time to do the normal reindexing and other intensive jobs (dbcc checkdb, etc) that we often struggle to find time for too.

Good Luck and let us know how you get on

Martin Mojzisek SSCrazy Points: 2207 More actions · Answer 12

Beware one thing. If you want to copy master and msdb datafiles and you have different drive letters on the new server, pointers to individual dbs datafiles in master will be invalid and the server won't even go up. (missing model)

You will have to perform procedure to change the pointers to the new locations which is not trivial - detach model, attach model, alter the location of tempdb, then detach and attach msdb - all with special trace flags and startup options. It's perfectly doable but requieres testing beforehand.

Vik Nat Hall of Fame Points: 3930 More actions · Answer 13

NP - thats why we are here - We are all here to help each other. I would run the DBCC CHECKDB after you move the DB. Check BOL for more information on it. Keep an eye on the temp DB when you run the CHECKDB.

Also would be a good idea to rebuild the indexes using DBCC DBREINDEX or at least run the DBCC indexdefrag. Though the DBCC DBREINDEX is more effective, but again it does create a lot of overhead. Keep an eye on the trans log when if you decide to run DBCC CHECKDB. Might need to back up the trans log several times.