“No, we’re not upgrading. What are we missing out on?”

SQL Server 2016

Vale, RIP, good riddance – take your pick, although that last seems a bit harsh.  SQL Server 2016 is exiting extended support on 14^th July – Bastille Day, no comment – 2026.  This follows Microsoft’s Fixed Lifecycle Policy:

• release,
• roughly five years of mainstream support during which you get patches, security fixes, performance and functionality enhancements, etc
• roughly five further years of extended support, during which you get security fixes and not much else

After that date, it’s incredibly rare for Microsoft to release any updates outside of the (paid-for) Extended Security Update (ESU) program, and thus continued usage of an EOL product should be considered an extreme action for short-term usage only.

Since SQL Server 2016 was released in June 2016, there have been several newer versions released – 2017, 2019, 2022, and just a few months ago, SQL Server 2025. Given that some of these are already deep into the extended support period, let’s just help you focus your minds on the need to upgrade by showing the lifecycle dates for those other versions:

Yes, I use ISO 8601 formatted dates.  They’re unambiguous, deal with it.  The number of times I’ve worked with and been bitten by systems that have unexpected date formats by default due to weird configuration settings is too many.

Each of these new versions has brought improvements in performance and functionality. Rather than look at reasons why you should no longer be running SQL Server 2016 from the perspective of compliance or lack of support, this post is looking at some of the new (well, new-ish – some of this is nearly ten years old now) fun stuff that you’re missing out on.

My sources for this include the various “What’s new in SQL Server <version>” posts, and Bob Ward’s books.  Links below:

SQL Server 2017 – What’s New – (Past Bob Ward and/or Amazon has let me down here – no book available on Amazon)

SQL Server 2019 – What’s New – SQL Server 2019 Revealed

SQL Server 2022 – What’s New – SQL Server 2022 Revealed

SQL Server 2025 – What’s New – SQL Server 2025 Unveiled

SQL Server 2017

It’s obviously been a while since this came out, and the memory of what was good is fading.  However, looking at the “What’s New” page, I can see a few things that have proved useful for our clients even in the last few weeks:

• Intelligent query processing / adaptive query processing – this was the first pass and provided the underlying framework for so many more improvements in the query processor. The number of features isn’t high in the first pass, and we did hit some issues with interleaved execution with one client, but it was a starting point for greater things.
• New DMVs, including dm_db_log_info (a replacement for DBCC LOGINFO which makes some potential transaction log performance issues much easier to predict and deal with before they become a real problem.

SQL Server 2019

Bob’s book’s cover has as an apparent subtitle “Including Big Data Clusters and Machine Learning”

Some of the features we’ve been using from SQL Server 2019 and later are:

• Memory-optimised TempDB Metadata – to improve the performance of tempdb-heavy workloads. This seriously reduces contention in tempdb, including various control structures within tempdb.  Arvinder, another member of Coeo’s Managed Services team, has written a blog post about this in more detail, showing the symptoms that were resolved by implementing this feature. Read that here – The Ghost in the Machine: How a Simple SELECT Paralyzed a 1TB SQL Server
• Resumable index rebuilds – allowing indexing operations to be suspended and resumed means that even large indexes can now receive maintenance, potentially freeing up significant disk space and improving performance.
• Data Classification and auditing – this is a set of tools to help with GDPR-related security and privacy issues. There are weaknesses to the classification tool – it only looks at the column names; however, there is tooling built into SQL Server Management Studio to support this level of data classification activity.

SQL Server 2022

Bob’s book’s cover has “A Hybrid Data Platform Powered by Security, Performance, and Availability”

For me, most of the good stuff in SQL Server 2022 has been improvements, rather than shiny new features.  Improvements such as:

• Intelligent Query Processing (IQP), Query Intelligence, and other Query Optimiser feedback improvements – including SQL Server being more aware of its own performance, and making changes to query plans based on changes in performance
• TempDB gets more performance improvements, building on work done in SQL Server 2019.
• XML compression – finally!

The main new feature is Contained Availability Groups – finally removing the overhead of running processes to synchronise SQL Agent scheduled tasks and logins between all replicas hosting availability groups.  However, there are some “gotchas”, chief of which is that an existing AG cannot be upgraded to be Contained.  There is some overhead as you’ll need to be connected to the listener rather than instance, and that even applies for administration work.  This has been a long time coming – I’ve been waiting for this functionality since AGs were first announced in 2012, and Microsoft themselves announced it for SQL Server 2019, but had to pull it as it wasn’t ready.  Unfortunately, it hasn’t gone entirely smoothly for one of our clients, and we’re still not quite sure why that’s been problematic, although removing the database from the AG and putting it back in again appears to have resolved the issue.

SQL Server 2025

This time, Bob’s book’s cover has “The AI-Ready Enterprise Database with Microsoft Fabric Integration”, and he devotes several chapters to the new functionality in SQL Server that supports and leverages AI.

Given that SQL Server 2025 is relatively new, this section is going to be more about features I’m looking forward to implementing at client sites, rather than features we have been using.

• JSON data finally having its own data type, with in-built compression and indexing, to add to the JSON-related functions that have been introduced in this and previous releases.
• SQL Server backup improvements.
  • We can now take full (not copy-only) backups on secondaries, therefore fully offloading all backup jobs from the primary.
  • There’s a new compression algorithm which is reckoned to be faster and better – obviously, with all compression algorithms, your data will determine how good it is. And some of us remember being bitten by some of Microsoft’s early efforts with bugs in its DoubleSpace disk compression utility back in the last millennium (gods, I feel old writing that), so will be leaving it to others to make sure all is good before going in with it.
  • Backup to immutable Azure storage – if backing up to a URL, the backup routine will only perform one write commit, meaning that backups can go directly into this type of storage. It would be good if they could also port this for some other backup vaults that work in similar ways.
• Fabric – specifically, mirroring databases to Fabric. We have several clients who go through various replication processes and other ETL routines to get their data into Fabric, Databricks, whatever.  Rather than going through all that pain, we can now just mirror the data straight into Fabric and use it there in near real-time.

SQL Server Management Studio

SQL Server Management Studio (SSMS) has also received a lot of attention over the years.  However, as you upgrade your SQL Server instances, you should also be upgrading SSMS to enable you to access that new functionality in the GUI that so many of us use.  SSMS gets updated regularly – currently on version 22.something, with point updates being released every month – like everything else.  Since the decoupling of SSMS from the main SQL Server distribution from SQL Server 2016, patching SSMS is a separate activity – one that quite often gets missed.

I am not about to go into a history of versions and feature updates – see Microsoft’s Release Notes for SSMS if you’re interested.

So what are you waiting for?

The examples above are only a small fraction of the new functionality available.  What’s missing?  What would you like to see available in SQL Server?  What do you have to have before you can migrate?  Have you mentioned it to Microsoft?  There are many ways to make suggestions for SQL Server functionality, report bugs, etc.  Similarly, there are feedback pages for SSMS.

If it’s just a lack of time or in-house expertise, get in touch with our team by emailing info@coeo.com – they can sort you out with a combination of consultancy and managed services support to get your migration project started, unstuck, or see it all the way through to completion.