Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am about to embark on migrating database files to a new SAN (from an old SAN) abd I have a couple of options to implement this. (1) It was suggested that I look into the level of effort of restoring a full backup to a new database on the server. However, (2) my original plan was to copy the files from the old SAN to the new SAN by detaching and then reattaching the database.

My gut tells me that I'd rather detach, copy, and attach since it seems more fail-safe, but that may just be my naïvety. I don't want to miss a transaction or somehow "break something" in the process of renaming databases.

I guess my question is whether or not I am justified in my skepticism of the BACKUP-RESTORE-Replay option and what are other merits or risks of that option?

share|improve this question
5  
I would not use detach/attach. Why? If something goes wrong during detach, you now have zero copies of your database. I'd say take a backup, then a log backup, then take the primary offline. In other words, path one - not for speed reasons but for safety reasons. I don't understand why SQL Compare / Data Compare would be involved? What schema/data will be different after the restore? –  Aaron Bertrand May 30 '12 at 19:08
3  
I have to use detach/attach because of some internal policies and I highly recommend avoiding it if possible. –  JNK May 30 '12 at 19:47

3 Answers 3

up vote 13 down vote accepted

Personally, I would avoid the detach/attach mechanisms. Especially in SQL Server 2000, I just don't trust that you will always bring the server back up and be able to attach those files. I've heard plenty of stories where this didn't happen cleanly - just because you have a Plan B doesn't automatically make Plan A sensible.

With backup / restore, you don't risk having to go to Plan B. If the backup fails, your database is still up. If restore fails, your old database is still up. In both cases you can restore the operation of the original database and revisit the plan later. In addition to the extra security here over stopping SQL Server and/or detaching, this also means you can test the hoo-has out of the backup/restore methodology (assuming you currently have the space to perform the backups and another instance to test the restore). You can't really test the detach approach without detaching the databases or stopping SQL Server, and that's tough to do outside of a proper maintenance window. And finally, with the other approaches you can't even start copying the files until you've detached or brought SQL Server down. With backup/restore you can have the .bak file waiting on the new storage long before you take the last log backup and start your maintenance window.

One other benefit over the pull-the-drive-out-from-under-SQL-Server method: with backup/restore you can move various files to different drive letters than they were before. For example when we migrated to a new SAN, we were able to have more volumes, so we could move tempdb to T:\ (which didn't exist before), some of the data and log files to new drive letters, etc. to better utilize all the new I/O capacity we had. If you simply shut down SQL Server and then swap out the disks, you need to have the same drive letters and the same number of volumes.

share|improve this answer

I used to move databases almost constantly, due to SAN reconfiguration and migrations.

Assuming that you are moving a whole server at a time, I would go with something like your path #2. (If you are moving one database at a time, and eventually doing every database on a server, that would be more problematic since you would have to be changing paths to the files.)

Note that "single_user" doesn't necessarily mean YOU. You could go to DBCC CHECKDB a database and not be able to get in because someone is already in there. Prepare a script that you can run to boot "everyone but you" out of a database and keep it in a handy place. Note that SQL 2000 doesn't have the same "keep everyone out" features as the more modern versions.

One old trick is to pause the SQL Server service. This will prevent new logins, but anyone who is already connected can continue as usual. So: connect via an SSMS window so you can do work, then pause the service, then kick out the undesirable connections, do your thing via SSMS command window (not the GUI, it makes and breaks lots of connections) and then un-pause the service. Warning: I'm not sure how that would play out on a cluster. It might want to failover.

It is handy to have a way to keep all app users out of a server until you are done your work. Otherwise, connections can start popping up while you are trying to do things, that can lead to resource contention and/or slowness. I have used the following ways in the past, depending on the exact situation: Turning off the app server(s) Use of ALTER DATABASE .. SET RESTRICTED_USER (If app accounts are members of db_owner, sysadmin or dbcreator roles, that's a problem.) Telling the users that the system will be offline at some specific time, like a Sunday morning. (This won't work in a "for real" 24x7 environment.) Unplugging the NIC that faces the app servers or users. (In this case, I could get in via another NIC connected to an admin-only network or through ILO.)

Detaching a large number of databases and reattaching them can be a lot of work. If you do that, make sure you have your "attach" script written ahead of time.

I've had plenty of success stopping the SQL Server, copying everything, changing the drive letters and starting SQL Server. No detach/attach. As long as SQL Server is off and you are copying (not MOVING) files, you can't get into too much trouble, even if you are moving the system databases. Since the paths are the same, SQL Server won't realize anything has changed while the service was off. Just make sure that you get the drive letters pointed back to the correct volumes or things will go badly for you.

My most frequent problem was not getting the ACLs on the file directories correct. More modern versions of SQL Server are better at setting just the permissions that the service account needs while older versions seem less fussy. If you forget to set the ACLs, and the service account isn't a local administrator (not that I'd recommend that), one or more databases may not open when the instance starts. Don't panic, just change the ACLs and attach the database.

I generally use ROBOCOPY to do this sort of work. There is a command line switch to preserve ACLs.

Using a CRC calculation/verification isn't a bad idea, but I've never done that. When the databases come back up, I do run CHECKDB() on all of them. I will usually prepare a script for this ahead of time, rather than relying on manually kicking off a maintenance job. That way, I can check a couple of smaller databases first before checking a large database which could take many minutes or hours to run. I doubt that a CRC check (or a Redgate Data Compare tool) would find something that CHECKDB() would miss, and if it did SQL Server wouldn't be able to fix it.

After I copy the files, but before I restart the instance, I will go and slightly change the filepath of the OLD folders by renaming one of the folders. This is an extra check against the "oops, the server is still pointing to the old files" problem.

Don't be in a hurry to drop the old files and recover space on the old storage and make double sure that your full backups have run successfully. Test restore a couple of those backups to somewhere else. Once you have good checkdb() runs and good full backups, then you can think about dropping that old storage and shutting down the Lefthand.

The worst problems I've had with these migrations have happened after I thought I was done. That would be the SAN admin telling me that something had happened and my file systems were scrambled. (Repartioned, reformatted, copied again.)

Another fun problem is the SAN being slow for no apparent reason. If you think that it will take 10 hours to copy your data and you are 30% copied at hour number 9, you have a problem. Watch the transfer times (robocopy shows % copied and gives time estimates, or you can use Perfmon) and have a fallback plan if something goes wierd.

Also, I am not sure if your volumes will be partitioned for you, but you might want to be sure that they are using a 1 MB offset. On Windows Server 2008 and better, this should not be a problem. On older OS, it is. There is a ton of googlable stuff on this, and your storage guys should know about it, but I'd ask.

share|improve this answer

First I would look at the option of simply using the feature of the storage array to handle the data moving. If those aren't available because the vendor doesn't support it, you didn't purchase it, or the SAN admin doesn't know how to do it...

Then I'd simply use the following steps.

  1. Present the new storage to the SQL Server.
  2. Stop the SQL Services
  3. COPY all the data from the old drive to the new drive (don't forget to include the permissions if using robocopy)
  4. Remove the drive letters from the old drives.
  5. Change the drive letters on the new drives to match the old drive letters
  6. Start the SQL Services

That's it.

There's no detach / attach needed as, as far as SQL Server knows nothing has changed. And if something does go horribly wrong, you've got the old copy of the databases sitting on the old LUN on the old storage array. Failing back is just a matter of changing the drive letters around.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.