Detailed Migration Steps for SQL Server Upgrades, Part I

There's a saying that all warning labels in airplane cockpits are written in blood (meaning that warnings stem from an unfortunate accident or mistake someone else made in the past—which shouldn't be repeated). To that end, I've decided to post an overview of actual steps you'll want to use when demoting an older server and promoting a newer server as part of a SQL Server upgrade or migration.

As I covered in a previous post, there are a number of reasons to avoid in-place upgrades. Accordingly, this post is written from the perspective of what needs to take place as part of a swap out of side-by-side upgrade (where you migrate your databases to a new/bigger/better server instead of performing an in-place upgrade).

Guiding Principles: Highlander vs Spartacus

Ultimately, the steps involved in promoting/demoting servers are pretty straight-forward—as long as you can keep in mind that the single worst thing you could do during an upgrade/migration would be to have two instances of your database(s) somehow running on both the old server and the new server at the same time. Or, in other words, the last thing you want your applications to have to deal with is a server that crops up somewhere and says "I'm Spartacus!," while other applications are confused by another server somewhere else that says: "No, I'm Spartacus." Doing so will cause data updates (and decisions based upon that data) to be written to more than one location at a time—something that's not exactly impossible to correct or recover from, but which, frankly, should be assumed to be so painful that it might as well be considered impossible.

As such, rather than permitting a Spartacus model, you'll want to shoot (very aggressively) for a Highlander model—where "there can be only one!"

With those guiding principles in mind, here's a quick overview of what you'll need to address as part of a SQL Server upgrade or migration.

Acceptance Testing

Before you can even plan/schedule an actual migration, you'll need to ensure that everything is working as expected on your new hardware and/or against your new version of SQL Server. As I mentioned in my last post, the ONLY viable way to do this is to spin up the new SQL Server on its final/actual hardware, migrate copies (i.e., backups) of your databases (and logins/security info) over to the new host, and then spin-up COPIES of your applications/application-servers that can be used to test and validate that everything against the new server is working as expected. In more formalized environments, quality assurance (QA) personnel can run entire batteries of key tests against the new server to verify that operations work as expected. In less formalized environments, this will mean that you'll have to get key end-users to go through the screens and make sure everything is working for them as expected in this new/test environment. (Make sure they know they're working against stale data.)

Communication

Communication, without a doubt, will be the most import thing you'll have to address with any migration of SQL Server—simply because there are so many key things to address and cover.

First, you'll need to communicate issues with management. Most likely they'll have been involved in the decision to upgrade to a newer version of SQL Server and/or to upgrade hardware. Still, in terms of the actual migration (where you demote/promote), you'll need to make sure you provide a clear picture of when and how the migration is to transpire, how much down-time is expected, and what your fallback and contingency and/or escalation plans are if something goes wrong. In other words, management doesn't know or care about the complexities involved—they're simply interested in costs, downtime, and how contingencies are managed. Do yourself (and them) a favor by setting expectations accordingly.

Second, you'll need to communicate with IT/operations to coordinate the demotion/promotion operation. Connection strings and app settings will all need to be changed to make sure you point all applications and operations at the NEW server/database as part of the migration.

Third, you'll need to communicate with endusers—as a means of helping to set their expectations. Let them know not only how long and when their applications are going to be down, but why (i.e., they're going to be much happier to tolerate any kind of downtime IF there's any type of context around what's going on, and especially, when said context is that an upgrade is in the works).

Finally, and a bit redundantly, make sure you've communicated with operations folks what you'll do and how you'll rollback should unexpected problems occur. You'll want to make sure you cover exactly what kind of communication should occur, and (within some environments—depending upon politics) whose role it is to determine go vs no go. And, should a no go be called, you'll want to make sure everyone understands how to roll back connection string details, and such.

Detailed Migration Steps for SQL Server Upgrades, Part II

Detailed Migration Steps for SQL Server Upgrades, Part III