G’day,
This months T-SQL Tuesday (January 2022) invite is brought to you by Steve Jones – @way0utwest
Steve’s asked us to write about “Planning for upgrades“
In the not too recent past, I’ve done quite a lot of migrations to Azure SQL Managed Instance, so, I thought about writing about that. But that seems more of an ‘uplift’ than an upgrade. I guess that’s an arguable point – depending on your perspective.
Anyway, just recently, I upgrades a SQL 2008 instance to SQL 2019. Quite straight forward I thought. And it was. But I ran into some unexpected events along the way that I thought might make an interesting post.
One of the first things that I like to do is talk to the person who wants the job doing – the client 
I like to find out their motivations and reasons for doing an upgrade, which would seems obvious in this case as SQL 2008 is out of extended support. This was a fully on-premises upgrade, but I also like to check facts.
There were a number of servers to upgrade, as you might expect – DEV, TEST , UAT and PROD.
Interestingly enough the server in the TEST environment had previously been upgraded to SQL 2016 with the user databases left in 2008 compatibility mode. Circumstances like this are always intriguing, and a few questions spring to mind such as why only one environment was tackled, why it was left and how this affects any code that makes it way though the various environments and eventually lands in the PROD environment.
Anyway, that aside, one of things I like to reference is Compatibility Certification – in recent years Microsoft has started to improve guidance and make certain guarantees around SQL Server upgrades, helping organisations to better manage risk.
It’s not a short article, but also not the longest one that you’ll come across. If you’re doing upgrades regularly (or even if you are not) then I’d bookmark it as the word certification and compliance will most likely lead search engines down other routes.
After investigating the environments, I’d normal put a plan together of how the environments will each be upgraded and the testing that will be done, both by you and by the client. I’d be keen to know their Change Advisory Board (CAB) processes and a rough indication of how long this would take.
Oh, and is this an in-place upgrade or a side-by-side upgrade (which some might refer to as a migration). In this case it was an in-place upgrade. There’s pros and cons to both and it depends on many factors such as risk, downtime and complexity – to name just a few.
I always favour downloading and running Database Migration Assistant (DMA).
DMA works fine for on-premises migrations / upgrades, it’s not just for the cloud. DMA produces both JSON and XLXS files that list out issues. I’ve seen a few open source solutions that pull the results into a data warehouse and then use a Power BI solution to visual represent any issues. Microsoft used to have one, but it seems to have disappeared at the moment (please let me know if you see it)
Then there’s the issues of upgrading instances that are out-of-support. You might require a ‘double hop” – basically where there are multiple upgrades involved, for instance upgrade to 2016 then upgrade to 2019.
It’s all fun and games till somebody loses an eye – so plan well.
Sometimes simply upgrades turn out to be way more time consuming and problematic than you imagine.
Take care out there.
Have a great day.
Cheers
Martin.