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.
You should consider SQL Server 2012 if…
- You’re dealing with an application whose newest supported version is only SQL Server 2012, but not 2014 or newer.
- You’re comfortable being out of support (because support ends in July 2022.)
- You either don’t need robust encryption for your backups, or you’re willing to buy a third party tool to get it.
2012 introduced a few other features – Availability Groups, columnstore indexes, contained databases, Data Quality Services – but they were so limited that it’s hard to consider this a good starting point for those features today.
In all, I just can’t recommend 2012 new installs today. Moving on.
You should consider SQL Server 2014 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.
2014 also introduced a few other features that don’t sound like assets today: In-Memory OLTP, which wasn’t production-quality at the time, Buffer Pool Extensions, data files in Azure blobs, backing up to a URL, and Delayed Durability.
You should consider SQL Server 2016 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 want to stay here until 2025-2026 – this version has more years left in its support life than SQL Server 2012/2014, so you can install it once and stick around longer.
- 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 should consider SQL Server 2017 if…
- You’re willing to apply patches every 30-60 days – because on newer releases like this, the patches are coming fast and furious, and they fix some pretty significant issues, and it’s going to be a while before 2019 comes out and 2017’s patches slow down. (Remember, there are no more Service Packs, just Cumulative Updates.)
- 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.
Yeah, I know, I didn’t put “you want an extremely well-known, well-documented product” here in 2017, but it’s not because the product is bad. It’s just relatively new compared to 2012/2014/2016, and it’s way, way harder to find great off-the-shelf training on things like Distributed Availability Groups or SQL Server on Linux, or hire people who know how to use those.
They’re not bad features. They’re great features – they’re just earlier on the adoption curve right now, so getting great best practices is harder, that’s all. Not impossible, just harder.
You should consider SQL Server 2019 if…
- You want as much support lifespan as possible – because it’s supported until 2030. Hot diggety, wouldn’t it be nice to not reinstall SQL Server for an entire decade?
- You’re willing to apply patches every 60-90 days – because although this version is mature, they’re still finding some big ol’ bugs.
- You don’t mind undocumented patches – Microsoft stopped documenting the contents of their patches, then got even worse, and as of 2021, they’re even shipping updates with new undocumented features. That makes me pretty uncomfortable for mission-critical production environments.
- 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.
- You rely heavily on table variables, and you can change the code – those are getting better too.
Microsoft brought some new technology bets to the table: Big Data Clusters, high availability in containers, and Java support. However, I just can’t make an argument for doing a new SQL Server deployment today just to get those technologies. If your database environment requires (not desires) those features, then you should already be running 2019 in your test labs – but you’re probably already running other versions of SQL Server in production for your other database needs.
You should consider SQL Server 2022 if…
- You’re willing to wait several months – because the first public preview came out in May 2022, and there are usually 2-3 previews before the final release ships.
- 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 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 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 Microsoft hasn’t even been able to demo this publicly yet, and the CTP 2 release notes say that this feature will be delivered at some point in the future. It’s already <checks watch> June, and Microsoft still hasn’t even been able to show a proof of concept of the feature, let alone production-grade code, so I get really nervous about this.
- You don’t need query performance monitoring – because the parameter-sensitive plan optimization changes in compat level 160 basically break monitoring tools.
In summary, you can tell that I’m kinda 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 2017 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 2017 today, and then seeing how 2019’s release goes, and planning for my 2019 deployments in the year 2021.