Database Migrations Made Easy (Or At Least Easier)

I do quite a few database migrations where I work.  How many, you ask?  Just this  year I've performed around 18 customer database migrations (and I set up almost half that number of new SQL Servers to support the migrations).  Several of these migrations included high profile customers with compressed time-frames.  

Along the way, I've learned several tricks that I hope will help.

We have a couple core databases for our application that are critical to the total operation of our software.  The recovery model for these databases are set to Full and we do transaction log backups every 15 minutes.  We just restore these databases to the new server using NORECOVERY so that we can continue to apply the transaction log backups.

The rest (and bulk) of our customer databases are generated monthly through bulk loading processes.  Once the monthly cycle has closed out, the databases are cleaned and set to read-only for reporting purposes.  Obviously the databases that have aged out and are currently read-only can just be backed up and restored to the new server.

To reduce the outage window during the migration, we schedule a suspension of the bulk loading process so that all the open and future databases can be moved without taking down the entire system.  Obviously your mileage may very here depending on the database model you are working with.

The Full Recovery Model and Transaction log backups are your friends in a tight time-frame as you can get the vast bulk of the data restored on the destination server before the outage window begins.

SQL logins can be easily migrated to the new server using sp_HelpRevLogin.  You don't even need to know the password, it will extract the password hash so that the password on the new server will match the old server.  The script for this SPROC can be found here: http://support.microsoft.com/kb/918992/en-us

Once the database is online, you can use sp_abFixUserLogins to sync database users to the logins on the new server (thus preserving the exact same rights).  The code for this SPROC can be found here: http://www.sqlservercentral.com/Forums/Topic232234-257-1.aspx#bm238983

Overall Tips:

1. Prepare a Project Plan
• Well before the migration, write out a project plan so that you know what you need to do when and you don't miss any critical steps along the way
2. Script EVERYTHING!  
• Clicking through the GUI is OK, but it takes time and it's easy to miss that one little option that will cause you huge headaches down the road when you miss it.  If you're not familiar with how to script the backup or restore, don't worry!  Just go through the GUI and set the options once, but don't click OK, instead click the Generate Script button at the top of the GUI.  As the name implies, this will open a new query window with the script to perform the actions you just specified in the GUI.  Find/Replace is much faster than going through the GUI many times and you can be sure you didn't miss the options if you made sure to specify them the first time through.
3. Test your project plan and your scripts
• Do a dry run of your project plan and make sure that it is solid.  It's easier (and far less stressful) to find and correct mistakes when you don't have an impatient customer breathing down your neck (or worse yet, your boss' neck).
• Be sure to test all of your scripts as you work through the dry run.  Just like your project plan, it's much easier and less stressful to test and correct everything ahead of time.
4. Save your scripts and your project plan in a safe and well known place
• You need to know exactly where your scripts and project plan are (yes, I've walked into a migration and totally blanked on where I put those pesky scripts that I worked so hard on).
• Save your scripts and project plan to a location that is secure (i.e. redundant storage) and backed up.  You should NOT save them only to your desktop.  If your hard drive crashes, all your efforts are in vain (yes, I've also walked into a migration and had my PC crap out on me, but my scripts were in a safe place, so I could just use another PC and move on).
• Having a common location for your scripts and project plan is also handy if you have a peer/code review and others need to access them.
5.  Keep your scripts and project plan after the migration is complete
• You may be questioned after the migration to ask if you completed a specific task.  By keeping your plan and scripts, you can easily reference your documentation to confirm exactly what actions you did (or did not) perform.
• You never know when you may need to do another migration and Find/Replace makes quick work of adapting the existing scripts and plan to a new project.  I never delete any of my scripts (unless they are total rubbish and I've already rewritten them).

Happy database migrating!