Which Version of SQL Server is Best for You?

Wait! Before you install that next SQL Server, hold up. Are you sure you’re using the right version?

I know, management wants you to stay on an older build, and the vendor says they’ll only support older versions, but now’s your chance to make your case for a newer version – and I’m gonna help you do it.

I’m going to go from the dark ages forward, making a sales pitch for each newer version.

SQL Server 2014 would be better for you if…

• You’re dealing with an application whose newest supported version is only SQL Server 2014, but not 2016 or newer.
• You want to use Always On Availability Groups – but I’m even hesitant to put that here, because they continue to get dramatically better in subsequent versions. I’d just consider this a minimum starting point for even considering AGs (forget 2012) because starting with 2014, the secondary is readable even when the primary is down.
• You need to encrypt your backups, and you’re not willing to buy a third party backup tool.
• You use log shipping as a reporting tool, and you have tricky permissions requirements (because they added new server-level roles that make this easier.)
• You need faster performance without changing the code, and you have lots of time to put into testing – 2014’s Cardinality Estimator (CE) changes made for different execution plans, but they’re not across-the-board better. You still have to put in time to find the queries that are gonna get slower, and figure out how to mitigate those.

In all, I just can’t recommend 2014 new installs today. Moving on.

SQL Server 2016 would be better for you if…

• You’re an independent software vendor (ISV) – because 2016 Service Pack 1 gave you a lot of Enterprise features in Standard Edition. This meant you could write one version of your application that worked at both your small clients on Standard, and your big clients on Enterprise.
• You want an extremely well-known, well-documented product – it’s pretty easy to find material off the shelf and hire people who know how to use the tools in this version.
• You use Standard Edition – because it supports 128GB RAM (and can even go beyond that for some internal stuff like query plans.)
• You’re willing to build a new server in a few years – because even extended support for this product ends in 2026.
• You have compliance needs for a new application – And I’m specifically calling out new apps here, but 2016 adds Always Encrypted, Dynamic Data Masking, Row Level Security, and temporal tables, features which make it easier for you to build things to protect and track your valuable data. It’s still not easy, it’s just easier.
• You want to use columnstore indexes – I’m going to call this the minimum version I’d start with because they were finally updatable and could have both columnstore and rowstore indexes on the same table. This grid has a great comparison of what changed with columnstore over the years.
• You need query plan monitoring, and you can’t afford a third party tool – because Query Store gives you some pretty cool capabilities. People aren’t using it as much as I’d like. If I took a full time DBA job again tomorrow, this (and PowerShell) would be the two skills I’d probably pick up.
• You hate applying patches – because SQL Server 2016 SP3 is basically the end of the line. Nothing’s getting fixed here, and there certainly aren’t new features coming out for it.

Even in early 2023, SQL Server 2016 is still the #2 most popular version.

SQL Server 2017 would be better for you if…

• You’re willing to apply patches every 60-90 days – because even though it’s years and years old, there are still regular CUs coming out.
• You have a zero-RPO goal and financial risks – because 2017 added a new minimum commit replica setting on AGs that will let you guarantee commits were received by multiple replicas
• You want easier future upgrades – because starting with 2017, you can have a Distributed Availability Group with different versions of SQL Server in it. DAGs aren’t too robust or well-documented today, but I like the idea of this as a down payment on easier upgrades when you upgrade down the road. (Prior to this, AG version upgrades are absolutely terrible, and you’re often better off building a new cluster and migrating over to it.)
• You need high performance columnstore queries – because we got a lot of cool stuff for batch mode execution plans.
• You’re dead-set on running SQL Server on Linux – but seriously, go through the release notes and click on every Cumulative Update to read the bugs that were fixed. Some of the clustering bugs have really made my eyebrows raise.
• You’re dead-set on doing machine learning & R in SQL Server – I know it’s trendy for data folks to do this, but remember, you’re spending $2,000 to $7,000 per core for SQL Server licensing to do this.

It’s tough for me to make a case for 2017 here. The features aren’t really amazing, so folks end up either on 2016 (conservative) or 2019.

SQL Server 2019 would be better for you if…

• You want as much support lifespan as possible – because it’s supported until 2030. I love new versions, but most of us have to stick on a version as long as possible, and 2019 gives you a lot of runway.
• You’re willing to apply patches every 30-60 days – because although this version is mature, they’re still finding some big ol’ bugs.
• You’re comfortable learning via experimentation, not documentation – because as you get to these cutting edge features below, your experimentation & learning time goes up, because there are WAY less established industry best practices around the below stuff.
• You’re good at load & performance testing – because 2019 adds a lot of cool performance features when you enable 2019 compatibility mode, but it also makes big changes in your existing execution plans. Just to pick a number, say 99% of your queries go faster, but 1% go slower. Do you know which 1% they are, and what you’re going to do to mitigate their performance reductions? You can’t just test your slow queries on 2019: you’ve also gotta test your currently-fast queries to make sure they don’t slow down unacceptably.
• You heavily rely on user-defined functions – because 2019 can dramatically speed those up, although you need to do a lot of testing there, and be aware that Microsoft has walked back a lot of the improvements.

As of 2023, SQL Server 2019 has the biggest installation base. It’s a really good bet for long term support.

SQL Server 2019 would be better for you if…

• You need the absolute longest support – because 2022 is supported until 2033, whereas 2019’s support ends in 2030.
• You’re willing to apply patches every 30 days – because on new releases like this, the patches are coming fast and furious, and they fix some pretty significant issues, especially with brand-new features. Odds are, if you’re going to a brand-spankin’-new version in the year it releases, it’s because you desperately need the new features. Well, those are the least-tested, and they’re the ones getting the most urgent fixes – thus the need for frequent patching.
• You’re okay with serious bugs in those patches – because 2022’s first several Cumulative Updates have been pretty buggy, and at the moment, the fix is to uninstall the CUs, which means you’re unprotected from other bugs.
• You have a good relationship with Microsoft – like if you’re an enterprise customer with your own account manager, and they can help rapidly escalate your Premier support tickets.
• Your DR plan is Azure Managed Instances – because at some point, 2022 will theoretically make it possible to fail over to MIs, and more importantly, fail back when the disaster is over. I say theoretically because this feature is still in limited public preview, and you have to contact Microsoft to get it.
• You don’t need query performance monitoring – because the parameter-sensitive plan optimization changes in compatible level 160 basically breaks monitoring tools.

In summary, you can tell that I’m kind of nervous about the state of SQL Server 2022 right now.

So what’s the right answer?

When I look at that list today, SQL Server 2019 makes a pretty compelling case for most folks. It’s a good balance of new features, stability, and long shelf life.

In most shops, where folks are overworked and can’t upgrade every server every year, I can see installing 2019 today, and then seeing how 2022’s patch release goes, and holding out for the next version after 2022.