Detailed Migration Steps for SQL Server Upgrades, Part III

Part I: Detailed Migration Steps for SQL Server Upgrades

Part II: Detailed Migration Steps for SQL Server Upgrades

Post Migration

Assuming that everything went without a hitch (i.e., all applications are able to connect to and work against the NEW server), then you're still not done. Now that you're over on the new server, you'll want to make sure it truly becomes, well, Spartacus. In other words, you'll need to ensure that it's now ready to handle workloads, process scheduled jobs, and (of course) survive disasters. To that end, here's a partial list of things you'll want to address right away.

1. Kick off a full backup. Once you demote the old server and promote the new server, if something ugly happens, you'll be MUCH better off restoring from the new server than trying to 'reach back' and spin up a database backup 'across servers.' As such, 'seed' your new disaster recovery needs on the new server by spinning up a FULL backup as soon as possible.

2. Likewise, make sure you're now running transaction log backups. HOWEVER, to keep things a bit less confusing and easier to manage, don't kick these off UNTIL you've fully migrated. (i.e., in some environments you might 'interleave' T-Logs from the OLD and NEW databases if they're both running T-Log backups and that might wreak havoc should you have to either rollback/no-go and/or run into a disaster later on; or, in other words, think in terms of "Highlander" even when it comes to T-Log backups as well—just to keep things more simple and clean).

3. Ensure all jobs (i.e., SQL Agent jobs) and alerts are configured and working as expected on the new server. (As with the previous step, don't do this until/unless you've successfully migrated).

4. Update your disaster recovery (DR) docs to point to account for the new server name, file paths, backup locations, credentials, and ANY/ALL other changes that may have occurred.

5. Test and verify your disaster recovery plan. A lot has changed—don't be dumb and assume everything will simply work as expected.

Obviously, those last two steps listed won't have to take place the night of your actual migration. But you'll want to make these steps a very high priority within the next day or so to make sure the universe doesn't decide it needs to throw you some curve-balls.

Create a Specialized Checklist For Every Migration

Finally, the information above is great in the abstract—but for any specific migration that you undertake, you'll want to not only customize the information above, but test and rehearse it as well. One of the HUGE benefits of NOT doing an in-place migration is that you can test and rehearse key components of your migration over and over again. (Granted, some things you can't test very well—like shutting down your old/production database, but you can pretty easily and pretty faithfully simulate the operations on your end that you'll need to address—such as running "poor man's log shipping," spinning up the new/promoted database, and such.) But all of those benefits are moot if you DON'T take some time out to test and practice this. In environments where downtime is a major concern, it's also possible to create timelines that can show exactly how long each operation or step of your migration plan can take—though you'll need to coordinate aspects of this with devs/operations in terms of application/config changes.