Meet the new Google AI Assistant Gemini (2024-0 6-08)

An Upgrade Slog

I saw a blog post from Randolph West recently that asked How do you restore a SQL Server 2000 database in the year 2024? It's a bit of a process, involving an intermediate version and two restores. He also points out the need to run DBCC after the first restore, which is a good idea. I wonder how many people would take the time to do this, or even think about it as an upgrade step?

This was interesting to read as I had a customer ask me about doing this a few months back. They were trying to clean up their database estate and modernize some of their older systems. This was becoming a big project for them, as they had several pre-2017 systems, none of which were in support. Auditors, regulatory authorities, and even business partners see this as a large security risk and get concerned if you're running older software.

I've felt that in most cases, I ought to be able to run a database server for close to a decade. I certainly need to patch it with CUs in that time, but the support lifecycle says that you get mainstream support for 5 years and then extended support (paid) for 5 more. That extended cycle also includes security patches, so ten years seems reasonable.

As a side note, the final support lifecycle for 2014 ends on 9 Jul 2024. That's a decade if you upgraded in the first year of release.

However, many of us have multiple instances, and upgrading those can be a chore. Perhaps you trust that nothing breaks, but I would say for many larger organizations, upgrades are a constant fact of life, and it is important to probably start testing upgrades at five years, knowing it might take 1-2 years to upgrade all instances of a given version. That's if you don't find issues in testing. If you test a 2017->2022 upgrade now and find issues, you might spend time mitigating these, or maybe wait for SQL Server 2025 (my guess) and hope you don't have the same issues. There are also the challenges of in-place vs. side-by-side upgrades, and you might choose one in testing, but decide to change for various reasons.

I still find myself a little nervous about the "evergreen" versions of SQL Server, where Microsoft patches them as needed. I know they try hard not to break any backward compatibility, but if they do, then you're stuck. I prefer to schedule my upgrades and make them a normal part of the DBA job. That being said, don't drag them out for years and years. If you still have SQL Server 2012 or older versions, you're doing something wrong.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums