T-SQL Tuesday #147: Upgrade strategies

This month, Steve Jones invites us to write about SQL upgrade strategies.

Upgrading isn’t a daily business, sometimes even cumulative updates don’t get installed unless it fixes a large issue. Why? The gist is “if it ain’t broken, don’t fix it”. There is some truth in these words. The latest and greatest might sound great, but sometimes it results in things breaking greatly. We’ve all seen the examples. If you’re running SQL 2016 and all your apps support this, the performance good enough and you haven’t got any issues, why upgrade.

Yes, at some point there’s no support from Microsoft. At some point applications will stop supporting the older databases. But if all you’re doing is SELECT * FROM [dbo].table, you won’t really suffer.

Things change when you’re working with a lot of data or when you’re trying to use SQL Server to the max. At some point you’ll see new features that will support your workload or add security for your data. Here are the steps I’d take to evaluate new versions.

First, install a Developer edition on an isolated server. Restore a database that looks like your regular data. It doesn’t matter if it’s Adventure works, Wide World Importers, Northwind or StackOverflow. Try out the new stuff, run query’s. Try to prove the new things work, try to prove things don’t work. This way I’ll get some idea of what the upgrade can do. But just from a technical point of view.

Next, build a testing environment. That’s a server with production-like data. This is the point where you’ll have to decide if your testing environment has the same specs as production or not. My preference is to keep the sizing identical, if things fail in your testing environment, they will fail in production. But the impact is different. Run your workloads against the testing environment and see what happens. Check out what happens if weird stuff happens, like a double workload, or a back-up that runs extremely slow. Run a tool like OStress to simulate intensive workloads, try to write awful query’s (let’s be honest, we don’t really have to try and do that ;)). This way I get some idea of what can happen when the new software goes live.

If this feels good enough, on to migration practice. Time to think about the migration itself. What do take with us, what needs review. Do we take all the SQL Agent jobs with us? Do we need all the logins, all the roles? Are there other things to review?
Then, what will we use for the physical migration. Do restore a back-up, or detach-attach? Or use the community-powered dbatools tooling.
After all the decisions have been made, time for a trial run. Not with live production of course but with another system that can handle a bit of downtime. Run through your script, check off the things that work, edit what doesn’t work and evaluate the results. Thoroughly. Because you don’t want to fail when migrating production. Remember the five P’s: Proper Preparation Prevents Poor Performance.

Now, I’ve cut a lot of corners in this blog. Upgrading is a serious task that takes preparation and thought. But it’s doable. And if you ask nicely there’s always someone from the community willing to help. You’re always welcome to shoot a message my way!

Thanks for reading!