6 Common Pitfalls of SQL Server Migrations and Upgrades

If you’ve ever tried migrating a SQL Server database from one server to another, you know it can be a white-knuckle ride from start to finish. You need to wait forever while you backup and restore, change all your connection strings to point to the new server, and forget to change the data sources for your SSRS reports because no one remembered you have SSRS reports.

I come bearing hope. It doesn’t have to be this way.

In this post, I’m going to cover some of the pitfalls of migrating or upgrading your SQL Server instance, gently suggesting you DON’T DO THESE THINGS. I mean, you can do them, but that would only increase the likelihood of a long night of work. Maybe even a resume-generating event.

If you aren’t the kind of person that wants to read an entire post, then just focus on this great point Mike Walsh made over a decade ago. If you want to succeed, then plan to fail. Every one of these items noted below is a pitfall because they don’t allow for the prospect of failure. There, you can go back to eating your sandwich.

For those sticking around, let’s get to the good stuff. Or rather, the bad stuff you shouldn’t do.

1. In-place Upgrades

I admit it, I’ve done a few of these. And SQL Server has become dependable enough that they go great most of the time. But they still leave gross remnants of the older version, and the times they don’t succeed are a massive headache and long weekends.

There’s really no way to quickly roll back from an in-place upgrade unless (1) you are working with a virtual machine, (2) you have stopped all your SQL Server services, and (3) your Systems Administrators have made a dependable backup of the virtual machine after you stopped the services. However, if that backup doesn’t restore correctly…what then?

If you have the resources to start by setting up another instance, in-place upgrades are unnecessary. I know backup and restore can be time-consuming, which is why you should familiarize yourself with log shipping, or storage-level snapshots, or some other technology that will help you migrate faster than an in-place upgrade.

2. Inadequate planning

Everybody makes a plan to migrate or upgrade, but very few make a plan to fail. If possible, your plan should start as a collaborative effort with any team members involved with the migration. Other DBAs, Developers or QA testers who will perform smoke tests, systems administrators who set up the new servers, or anyone else. Make a checklist of each step, and then share that checklist with all members.

And here’s the important question too many folks forget: ask the group what can go wrong at each step. Remember, to succeed, you must prepare to fail. Did you discuss ALL the possible failures?

For each possible failure, determine if it requires a rollback.

Oh yeah, make a rollback plan. And as part of that rollback plan, make sure two things are carved in stone: (1) who makes the rollback decision, and (2) what is the absolute drop-dead time that a rollback decision must be made. Omitting these critical items from your plan will lead to confusion, heartache, and maybe even a stream of four-letter words by some colleagues.

And make sure your plan includes everything that needs to go along with the databases. What about the jobs? And logins? And encryption certificates? And those treacherous Linked Servers? And…what else? Which brings me to the next point.

3. Not using the right tools

Learning PowerShell might seem like a reach for a recommendation, but the folks at dbatools.io have made it ridiculously easy to manage SQL Server migrations using PowerShell. If you so desire, you can migrate databases, logins, jobs, audits, credentials, policy management objects, and more with one command (Start-DbaMigration). Nearly everything you need can be migrated in that one command, and you don’t have to worry about what you might have forgotten to migrate. It might take you all of an hour to figure out how to use PowerShell with this command, but I assure you it will save you plenty more hours in planning and execution of any migration.

Another tool that seems to get used all too infrequently is the Data Migration Assistant (DMA). This free tool from Microsoft looks at the versions of your old and new instances and lets you know all the possible incompatibilities. Give the results to your development team, they’ll love you. Well, maybe they won’t love the extra work you give them, but at least you’ll all know what will break before you migrate. And this is only part of what the DMA can do, so when you are still in the planning phase of your migration you should absolutely utilize this tool.

4. Not using CNAMEs and Aliases

Do your connection strings point to the name of your instance or listener? I don’t mean to offend, but if they do, then you’re doing it wrong. Or at least you’re making migrations way more difficult than they need to be.

Instead of pointing all connections at your server, utilize CNAMEs in Active Directory to point all your database connections to an alias instead of the actual instance name. This way, when you migrate within your domain, you can just repoint the CNAME to the new server with a few simple clicks (or even a PowerShell script). And if you work with the kind of organization that upgrades with each new version of SQL Server, then you’re going to be migrating every few years. No one wants to change all those connection strings every few years, right?

The key here is to take the time before you migrate to make sure all connections are pointed at the CNAME instead of the instance name. Having this all set up once before any migration makes every subsequent migration that much easier. No more updating all those app server connections, or user spreadsheets, or those SSRS data sources you forgot about. And if you want to be really savvy, you should consider creating CNAMEs for each database in case you don’t migrate all your databases to the same instance.

Oh, and the other great thing about using CNAMEs? Your rollback plan could be as simple as just pointing the CNAME back to the old instance. It couldn’t be any easier.

5. Lack of testing

Adequate planning is one thing, but even if you feel you and your colleagues have planned adequately, you still need to test the migration. For real, I’ve seen migration plans like:

1. Stop services on old SQL Server
2. BACKUP database on old SQL Server
3. RESTORE database on new SQL Server
4. Start services on new SQL Server instances

I bet if you test this, you’ll see it doesn’t work. Give yourself adequate time to test your migration and to test it more than once. Practice migrations from start to finish as best you can, and when you practice, think about failing. If you fail, then great – you learned something! Take that information and think about how to correct it.

Incidentally, if I haven’t mentioned PowerShell too much, then here’s another advantage: most of the commands in dbatools have “What if” options to simulate actions. Using those in your testing is a great way to find out if you have issues like inadequate permissions well before the actual migration.

6. Not checking the old server for connections

The last thing I want to mention is the invisible killer after you migrate and you think all is well. You may have missed changing some connection strings, like the one the VP of Finance uses in a spreadsheet he has. He’s going to get a bit perturbed when you migrate, call everything good, and then turn off that old server.

Avoid this problem by creating an extended event on the old server to monitor activity, and leave it up for a few days, weeks, or whatever time frame suits you best. If you don’t like extended events, use a Profiler trace. Just use something.

Conclusion

A lot can go wrong with SQL Server database migrations, but that shouldn’t stop you. Prepare as best you can for every failure you and your colleagues can think of, and plan accordingly. Make a thorough checklist. Test, fail, and repeat, until you get it right. Use the tools and options available to you to make yourself look like an all-knowing and all-powerful wizard. And don’t be so hasty to turn off the old server. Follow these steps, and who knows – maybe you’ll actually get excited the next time you have to migrate or upgrade.