I'm part of a company that was acquired by another company. We're moving it to their SAN. We are now part of the new company and on the same network, we're in a different domain that will be going away sometime in the future. Please Help me with the pros & cons of the various methods of moving this large db to new server and storage. Source: SQL 2008 database (on Windows Server 2003) and I need to move to new SAN Storage, and to a new Server (Windows Server 2008 R2). Here are the db details: Database: 3589175.50 MB Recovery=SIMPLE, Version=655.
Yes, it's almost 3.5 TB; spread across 6 data files. Data1 655360000 KB
Data2 551936000 KB
Data3 552448000 KB
Data4 747877504 KB
Data5 548104448 KB
Data6 586821760 KB
Log1 32768000 KB
1) Detach database; copy mdf and ndf files to new SAN, reattach them onto the new SQL Server
2) Backup database (currently being backed up using TSM) and restore to new server "with move" option.
3) Using copy database wizard in SQL Server Management Studio.
4) Set up replication to the new server & SAN
Because of the size of the db, I think 3 is definitely out. I'm in favor of at least TRYING the backup/restore. Additionally, couldn't I get some sort of portable disk pack to back the database onto (as opposed to network back, which I presume is a lot slower), then attach the disk pack to the new server and restore it? Regarding the copy data files method: Using Robocopy on a test file that's about 280 GB in size: Pull Speed : 1081.357 MegaBytes/min. Push Speed : 727.859 MegaBytes/min So we're talking a couple of days to copy the db. Not too good of a thing. So... let's open a conversation about this if you please. Thanks, Bob McC
If the two servers can talk to one another then why not just setup log shipping? I have done this hundreds of times for migrations to new servers. If the new server can see TSM then there is no reason why you couldn't restore the last full and diff from TSM to the new server, then setup log shipping. When the time comes to fail over it is a very simple process. Your question didn't mention allowable downtime. Anything you can do to stage this ahead of time the better, you don't want to wait until go live weekend to attempt this restore only to find you have a flawed process and or invalid backup.
Any chance of a direct SAN transfer? Connect one to the other & copy the files across. Personally, if you can do that, I'd feel more comfortable, for once, with detach, copy, attach. Yeah, the database is down during the process, but it's a single step, clean and pretty safe. Otherwise, if you want real safety, get ready for some work and prep a bunch of disk space. You can backup the database. Then restore it to it's new home. You can do this without taking the database offline. Then backup the logs and restore them to the new location. You can more or less keep doing this until some arbitrary point where you take the database offline, backup the tail of the log, restore that to the new server, then bring it online through recovery. This is the most work of all the viable choices, but it'll be the safest AND it gives you maximum uptime (not something you referred to). It's basically log shipping. I wouldn't even consider #3. I'm pretty sure I wouldn't pick #4 either, mainly because setting up replication is a bear and you can do the log-shipping approach with less persnickety work around the distribution server, etc.
I go along with what @Grant Fritchey said, but would prioritise option 2 (the backup/restore) option over the SAN copy to minimise downtime. Yes, transferring the backup via locally-connected portable storage is an option - I've done that before because it was quicker. However, you need to bear in mind your transaction logs - make sure you don't delete any T-Logs (or DIFFs) taken after the FULL backup that you have transferred before you get them restored on the new server, in order to maintain the log chain and the ability to successfully restore. This shouldn't be a problem if the two locations are close (are they close?), but if you're going intercontinental, then you may have more problems. options 3 & 4 - definite no-nos.
I guess we are overlooking a key feature now a days available so easily i.e. backup compression ;) Seems like the biggest hurdle is the time to copy whether the files OR the full backup (full backup would be almost the same size as are the files). Not to mention the storage size (the most expensive resource) for the backup that would be restored which would mean that you would need atleast double the size of the database. Which to me might not be feasable and in most cases very well be the main reasons to migrate to the other bigger servers. So go for the backup compression tools which would save you time and storage. Hence Log shipping setup would be benefitted as well. Considering Log shipping is the best idea, the first third party tool comes to mind is Red Gate's SQL Backup Pro. Upto to 95% smaller and hence faster backups, Self healing Log Shipping (GUI environement is very helful), Multiple threads to increase backup performance, Network resilience for fault-tolerant backup transfer, including automatic resuming of interrupted transfers, DBCC CHECKDB etc. are some of the key features of this wonderful tool. I can left rest of the features to our own @Grant Fritchey :) Just my two cents.
I would not use the copy option from SSMS in offline / online copy mode it's only automating something that can be done with more control manually and it usually makes a mess of the copy. If you carry out daily backup consider a tool like Hyperbac which will intercept the backups and compress them. Some of our databases reduce down to 4% of the MDF+LDF size so the network copy is far easier. You can get a trial version and mark the backups as zip compatible then unzip on new server. Restore on new server in recovery mode then do a differential backup on source and restore the new database into operation mode.