SQL Server Migration Steps

Every few years, I help several of my clients migrate to new SQL Server instances.

While the specific steps vary from client to client and migration to migration, the high-level steps tend to stay the same.  The outline below is the framework I use.

This outline is tailored to simple SQL Server scenarios.  More complex situations–such as those that involve replication, load balancing, high transaction volume, 24/7 operations with no off-peak hours, etc.–will require additional planning.

1. Install SQL Server on new server
2. Transfer SQL Server logins from old to new server
3. Create and test database transfer scripts
4. Set databases to read-only on new server (to prevent accidental edits)
5. Set up backup and maintenance tasks on new server
6. Pre-deploy applications with code to auto-relink tables (based on read-only status of db on old server)
7. Set old database to Read-Only
8. Back up the database on the old server
9. Restore on the new server
10. Remove read-only status on the new server
11. Take database off-line on old server
12. Repeat steps 7 - 11 for each database
13. Update applications to point only to the new server
14. Decommission old server

My Philosophy

I prioritize two things:

• Minimize production downtime
• Avoid disasters

Minimizing Production Downtime

Prep the New Server

I do as much as possible to set up the new server ahead of the data migration itself.  That includes:

• Transferring SQL Server logins
• Installing the Ola Hallengren scripts
• Performing dry runs of the db backup/restore scripts
• Setting up SQL Agent backup/maintenance jobs
• Opening appropriate port(s) in the firewall

Script as Much as Possible

I use Ola Hallengren's SQL Server scripts to automate as much as I can.  

In particular, I perform steps 7-10 of the outline above in a single batch file.  For most databases, this means that there is less than sixty seconds of downtime where there is no editable production database available.  That small–and, most importantly, predictable–amount of downtime is easy to squeeze into off-peak hours.

Update Applications to Auto-Relink Tables

At application startup, I check the read-only status of the database on the old server.  If the old database has been set to Read-Only, then I relink the tables to point to the database on the new server.

There's a small chance that a user could open an application between the time I set the old database to read-only and it gets restored on the new server.  I don't worry about that because (A) I normally perform those steps during off-peak hours and (B) by the time the user acknowledges the error message and restarts the application, the process will likely have finished.

The huge advantage of doing it this way, though, is that you don't need to synchronize deployment of a new version of your application with the migration of the data.

Accomodate Readers

It's not uncommon for a few stragglers to be late in receiving an application update that points to the new database server.  

To support those users, I keep the old database on-line, but read-only, for a certain amount of time.  In most cases, I'd prefer users be able to see slightly stale data rather than nothing at all.  If that's not acceptable in your situation, you can add the step to take the old database off-line as part of your backup/restore script.

Avoiding Disasters

Preventing Data Loss

There are two ways to lose data during a SQL Server migration:

1. Users edit data on the new server that gets overwritten by the final backup/restore process
2. Users edit data on the old server following the final backup/restore process

I avoid the first situation by setting databases to read-only on the new server while I am configuring backups and maintenance tasks.  Users may still be able to see data on the new server (which isn't ideal as it will be stale prior to the final cutover), but at least they won't be able to make any changes to it.

I avoid the  second situation by setting databases to read-only on the old server BEFORE I create the backups that will be restored on the new server.  

Be Wary of Writers

At no point during the migration is the same database writeable in more than one SQL Server instance.  

Phased Decommissioning

I don't immediately delete the database on the old server following the migration.

I've found it can be very handy to keep the old database around, especially for troubleshooting purposes.  When you take a database off-line, any read-only users that had been looking at the old copy of the database without realizing it will immediately begin squawking that their app is broken.  At that point, you can easily bring the old copy of the database back on-line if need be.

Once the old database has been off-line for a reasonable amount of time, I feel better about deleting it from the old server.

Notes

Role-Based Access Simplifies Permission Migration

If you are assigning table-level permissions directly to Active Directory user accounts, your SQL Server migration will be a lot harder than it needs to be.

To make things run as smooth as possible, I strongly recommend you implement SQL Server Role-Based Access.

Referenced articles

Image by Sammy-Sander from Pixabay

UPDATE [2022-06-09]: Added link to "Scripted SQL Server Migrations" article.