Normally I get these monthly posts done early on T-SQL Tuesday (the second of the month), but that didn’t manage to happen this month. Maybe it’s me who needs an upgrade…
Upgrades are the topic du jour, hosted by Steve Jones (@way0utwest) over at his blog. He asks about our strategies for upgrades, how we test, how long it takes, all that. So I figured I’d wax lyrical about upgrades for a bit.
Azure SQL DB is a whole nother matter when compared to SQL Server, of course. With Azure SQL DB, the environment is frequently upgrade behind the scenes, hopefully without our noticing (“us noticing” sounds better there, but it’s just not correct… hmm…). We can set the compatibility level, which is when we choose to adopt an updated feature set, such as moving from SQL 2017 to SQL 2019, but the basic hot-fixes and the like are done for us behind scenes.
And yet we don’t tend to take this approach with on-premises installations.
Largely because we’ve been burned before. And (to mix metaphors) once bitten…
Annoyingly there are times when a cumulative update gets released a little prematurely. They’re rare, but they do happen from time to time. That’s slightly terrifying for someone who wants to protect their server from vulnerabilities. Particularly the one that that cumulative update is designed to fix.
This creates a dilemma. Should the update be applied the moment it becomes available, or should you wait for a couple of days, in a potentially vulnerable state? There’s arguments on both sides. If it was Azure SQL DB, the decision wouldn’t be yours. And if they found a problem with the update, Microsoft would be getting that fixed for you as soon as possible too.
A significant upgrade, such as between major versions of on-premises SQL Server, is definitely more effort. I like to think that backwards compatibility is a well-understood thing these days, and that SQL 2022 won’t break anything that was being done in SQL 2016. But with no absolute guarantee of that, there’s always a bunch of work to test things. Less if you have automated testing. But a lot of systems (I’m a consultant – I see plenty of bad as well as good) just aren’t set up like that.
And then there are the vendors who don’t want to support a later version. And who end up writing code that is only compatible with SQL Server v.Old, because they haven’t been able to persuade all their customers to move onto a newer version yet. And that means that other applications don’t get upgraded… it’s like a recursive CTE of old versions of applications and platforms, and you wonder when you’ll ever get to upgrade.
The key to all of this is DevOps. Automated testing so that you know that what’s being deployed is going to work. Even if the thing that’s being deployed includes a newer version of SQL Server.
I don’t envy application vendors who don’t have a strong DevOps story. They need to get it in place so that they can scale, but it doesn’t happen overnight. The road to good deployment practice is long and is threatened by all kinds of things. Code coverage is rarely complete, and problems seem to find those places that don’t have good testing in place yet (typically because problems are avoided in the areas that do have good testing). All this is so much easier when a project is starting from scratch, and not the culmination of a decade or more of development needing to be compatible with the last four versions of SQL Server and Windows.
That backward compatibility I mentioned… for the most part, I find that code does survive major version upgrades as far as correctness goes. And even the most covering test environments may say things are okay. But performance changes, such as in the Query Optimizer, mean that upgrades will almost always present some amount of pain.
It’s worth it though. Running the latest version means you’re getting the most attention in the way of fixes. It means you can attract the best employees to work on it. And you can take advantage of the new features.
So get your DevOps story happening. Include tests around performance (or maybe execution plan shapes), and get used to the process.
Upgrades are worthwhile. Get them done.
