Which Version of SQL Server Should You Use?

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 probably install SQL Server 2008 if…

  • You’re dealing with an ancient application that’s no longer under support, like a third party app written by a vendor who has folded up and closed down
  • You’re only licensed for SQL Server 2008 because you didn’t buy Software Assurance, which gets you the ability to run newer versions for free
  • You only have Standard Edition, and you want support for unlimited memory (which goes away in 2008R2)
  • You don’t know that both SQL 2008 and SQL Server 2008 R2 (which came out about two years later) both have the same end-of-support date (and, uh, now you know that, so, that rules out that one)
  • You don’t know that Extended support ends July 9, 2019. Yes, if you host it in an Azure VM, Microsoft will give you extended support until 2022, but that’s it.

With that end-of-support date, I’m just not a fan of new 2008 installs today. So let’s move on.

You should consider SQL Server 2008 R2 if…

SQL Server 7.0

But I got a really good deal on this CD at a garage sale

You need these new features added since SQL 2008:

  • PowerPivot for Excel (which has been replaced)
  • Utility Control Point (which nobody uses)
  • Master Data Services
  • StreamInsight

Uh…yeah. No. Let’s skip this version and move on.

You should consider SQL Server 2012 if…

  • You want an extremely well-known, well-documented product
  • You want to write modern T-SQL (paging, better windowing functions, conversion and parsing)
  • You want a lot of diagnostic capabilities (because a lot of goodies got added in the DMVs & XE while it was being actively updated, although those days have come to an end)
  • You are absolutely averse to installing patches (because SP4 came out in Oct 2017, and there’s only been one security fix since, and that’s it)
  • You’re comfortable building another SQL Server in 3 years (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.

You should consider SQL Server 2014 if…

  • You want an extremely well-known, well-documented product – like 2012, it’s pretty easy to find material off the shelf and hire people who know how to use the tools in this version
  • 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 newer 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 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’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 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.

Yeah, I know, I didn’t put “you want an extremely well-known, well-documented product” here in 2016, but it’s not because the product is bad. It’s just relatively new compared to 2012/2014, and it’s way, way harder to find great off-the-shelf training on things like Always Encrypted or columnstore indexes, 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 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.

You should consider waiting for SQL Server 2019 if…

  • You’re on 2012 or newer – so you have support for a while
  • Your environment is stable – so you don’t have a pressing need to change things
  • Your environment is performing well enough to get by
  • You only want to do one upgrade or new build in the year 2019
  • You heavily rely on user-defined functions – because 2019 may dramatically speed those up
  • You rely heavily on table variables, and you can change the code – those are getting better too

Microsoft is bringing 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 holding off on a new SQL Server deployment today (Jan 2019) for those technologies. If your database environment requires (not desires) those features, then you should already be running the 2019 previews in your test labs – but you’re probably already running other versions of SQL Server in production for your other database needs.

Code smells

Is it supposed to smell this bad?

If you only desire (not require) those features, then don’t wait around for 2019. Continue your production deployments today. Don’t gamble on v1.0 features that might or might not ship this year, and may not be at the quality and completion level you really want. Don’t test in prod.

So what’s the right answer?

When I look at that list today, SQL Server 2016 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 2016 today, and then seeing how 2019’s release goes, and planning for my 2019 deployments in the year 2020.

I’m sure I’m going to hear back from y’all though – what are your compelling reasons to pick a particular version?

Previous Post
Wanna see Erik Darling as Freddie Mercury at #SQLbits?
Next Post
Free Downloads for Powerful SQL Server Management

61 Comments. Leave new

  • Venkata Jayaram Peri
    January 21, 2019 9:37 am

    Excellent… Its very best information, in SQL Server Paradigm Shift. Thanks for writing for this, will adhere the knowledge. Thanks very much. Jay

    Reply
  • Henrik Staun Poulsen
    January 21, 2019 10:48 am

    I’m currently moving an old SQL Server 2008 R2 to Azure SQL. What are your thoughts about this move?

    Reply
    • HSP – oh that’s a great question! I’m a huge fan of Azure SQL DB if the database is self-contained (no cross-database joins) and the RPO/RTO goals aren’t too ambitious (since point-in-time recovery is still a bit of a rough story for Azure SQL DB.) I haven’t found a case yet where folks could deal with the limitations and the lack of guarantees around restore time, but I would be totally okay with it if they could.

      Reply
      • Henrik Staun Poulsen
        January 22, 2019 12:45 am

        Before I joined the company, they showed an RTO = 24 h. So RPO+RTO around 1 hour should be ok, if it does not happen every month. That should be doable with Azure SQL DB?

        Reply
    • Kasper Brandenburg
      January 21, 2019 11:55 am

      Be aware of which tier you select. Performance can suck on the lower tiers. Be mindfull that time-functions will only return UTC time, so GETDATE() for Denmark which would be CEST timezone on-premise, is now UTC time on Azure. Look into Managed Instances if you have the money for it.

      Reply
      • Henrik Staun Poulsen
        January 22, 2019 12:41 am

        hi Kasper, I had not noticed the GetDate() timezone (yet). So now there is a small search&replace job to do, which is not a problem now, but would have been if we had not found it before going into production.
        So here is a big “Thank You”!

        Reply
  • Thanks for the pointers! After reading, I feel confident I made the right decision to wait to 2020 to upgrade to SQL 2019. Currently on SQL 2014 and can get business support to test every 3 years at the most. Apps are fairly stable and they make high use of UDF’s and table variables.

    Reply
  • Great article. I feel thoroughly vindicated 🙂

    Reply
  • SQL 2017 – You want adaptive query processing…

    Yeah I’m not sure I’m convinced that’s a good reason either.

    Reply
  • It’d be great to have an article on what you might miss if migrating from SQL2016 Enterprise to SQL2016 Standard.
    Seems to be heaps of info on how similar they are and how many features Standard has…but hard to find what isn’t there.
    (I’ve come into environments upgraded from SQL2008R2 Enterprise to SQL2016 Enterprise without consideration of Standard)

    Reply
  • Two things I’d like to point out:
    * The version of MDS in SQL Server 2008 is crap. They changed so much in 2012 (and again in 2016), that 2012 should be your minimum entry point for MDS.
    * Clustered columnstore indexes were updateable in SQL Server 2012. In 2016, updateable non-clustered indexes were introduced.

    Reply
    • Koen Verbeeck
      January 25, 2019 6:59 am

      Whoopsie, I meant “Clustered columnstore indexes were updateable in SQL Server 2014.”

      Reply
      • Koen – right, exactly – they were updatable in 2014, but you couldn’t use any other indexes on them, and nonclustered columnstore indexes still weren’t updatable, so I call 2016 the minimum. I’ll make that more clear in the post. Thanks!

        Reply
  • A couple more:
    * R services was introduced in SQL Server 2016. SQL Server 2017 has some improvements and introduced Python, so I’d prefer that version though.
    * in SQL Server 2017, what’s the trade-off for columnstore indexes? What a cliffhanger!

    Reply
  • Stefan Gabriel
    January 21, 2019 10:59 pm

    Really great! But my ERP vendor says: with that version of this ERP system you’re allowed to just use 2008 R2, 2012 or 2014. Don’t run it on any different version! Otherwise I will not support you if you got some problems!
    Therefore I’m stuck with 2014. Unfortunately.

    Reply
  • The biggest reason for us to drop 2008 and require 2008 R2 as a minimum was TVP – Table Value Parameters. Finally we could stop using ‘special character separated’ VARCHARs to pass lots of data to stored procedures.

    Reply
    • Great article by the way. It seems to me that we should require 2016 R1 as the next minimum. I’m eagerly waiting to make some tests with column store indexes. These could really help improve performance in some cases.

      I’m running 2017 on my dev environment and a few queries using dynamic SQL are way slower than before (like 20s rather than 3s) because of changes to the cardinality estimator. Setting the db compatibility to 2012 fixes that though.
      I have to find the time once to isolate the issue and report it somehow or rewrite these queries in another way.

      Reply
  • Great article. For us the automatic plan correction of SQL 2017 is a huge selling point — hoping for no more urgent production issues requiring manual connection, investigation, and forcing a plan (of course we’ll still have to monitor it and stabilize the code).

    Reply
  • This is maybe a bit tangential to the point, but there’s another consideration here too: the version of Windows each version of SQL Server supports. 2008-2017 can all coexist on a 2012 R2 Windows Server, but SQL 2019 will require at least Windows 2016, which means SQL 2008 and 2008 R2 have to drop off. This may seem like a bit of a strange thing to worry about, but 90% of the SQL Server dbs I support are the backends for COTS products, and, well, ISVs suck. For the sake of SQL Server licensing efficiency, I pile an instance with each version into one big physical server (768 GB RAM) where I’ve kept the core count as low as possible.

    Reply
  • Thank you for the information! This is a great way for me to teach the business on why to upgrade; also it provides me with details on which version to upgrade to and why.

    You are awesome Brent! Thanks again.

    Reply
  • I figure that SQL Server 2016 will soon be the 2nd version back and SQL Server 2017 has been out for a while (after all it’s 2019 now) and so I am pushing for 2017. If I need to, I figure I can use the compatibility level feature.
    We still have a lot of 2008 R2. I imagine a lot of people do.

    Reply
  • We have one 2008 R2 server left in the farm because there’s “no time” to upgrade the app left on the server to a newer version. Ever just give up and root for a server failure? Unfortunately it’s a VM.

    Reply
  • Great article as always.
    I think you missed Polybase (PDW) starting SSRV2016 out of the box (licence included, if I remember correctly). Our lifecycle was 2012, 2012SP1, 2012SP2, 2016, 2016SP1, 2017.
    2014 was skipped because we did not found strong reasons to update.
    2016 was highly expected to integrate with our hadoop hortonworks, first was a huge deception, then we started using it, now it’s ok. (It misses HDFS partition mapping, ability to handle different structured lines, and a decent row size.)
    We went massive on columnar with 2016 but at a cost, many queries went to index locks on parallel and it took a while to solve, around TRACEON (4199, -1) and QUERY_OPTIMIZER_HOTFIXES (if I’m looking at the right script).
    We always used a lot of R, even at 2012 we already had R in the same server with SPs running rscript on shell as SSRV extension.
    Currently 2017 CU8 an hoping to upgrade today to 2017 CU13. I came were while looking for SSRV roadmap.

    Reply
  • So, what does a SQL Server CD suppose to smell like? I suppose it is too much to ask that it smells like bacon.
    We’re happy with SQL Server 2016.

    Reply
  • Muhammad Aatif Fasihi
    January 25, 2019 1:04 pm

    Actually I believe that the way Microsoft is releasing SQL server’s every one or two years like service packs will cost them heavily to maintain the code base and the team developing them.
    Anyhow, I found SQL 2016 as a balanced product to run critical production application/s.
    It will take sometime to adopt the “dazzled by excessive light” features like graph databases, etc.

    Reply
  • The biggest feature that I absolutely hate, especially for the migration from 2k12 to 2K16 was the incredible negative impact that the new Cardinality Estimator had on our systems. In fact, that seems to be a problem with all versions of SQL Server. Every time we do an upgrade, there’s always some bloody code that worked great in the older version that no longer works so great on the new version. And for the upgrade from 2K5 to 2K12, well… let’s just say I’m glad I waited until SP3 came out on that one and that we skipped 2K14 entirely.

    Reply
  • Excellent summary Brent. Keep up the great work.

    Reply
  • PowerPivot for Excel has been replaced? Could you please explain that a little bit more?
    Thanks!

    Reply
  • Carl E Thompson
    March 5, 2019 11:34 am

    This a very well thought out post! Well done Brent!

    Reply
  • Carl E Thompson
    March 5, 2019 11:35 am

    This is a…. Unlike my above comment.

    Reply
  • Hi Brent
    I was asked by my supervisor if SQL Server 2017 is stable enough or to stick to 2016 SP1. We dont use the new data science technologies or anything fancy just standard features. Plus we run everything on windows so linux isnt an option right now maybe in the future. So do i push for 2017 or keep 2016?

    Reply
    • Yep, that’s what the post is all about. Give er a read.

      Reply
      • Yeah I read your post. Let me ask another question. For setting up a BI solution using power BI. Which version will benefit more? 2016 or 2017. The obvious answer is 2019 but that’s not out yet. Any comments?

        Reply
        • Koen Verbeeck
          March 10, 2019 11:54 pm

          How are you going to use Power BI? With Power BI Report Server? With the service? Live connection to Tabular, DirectQuery to SQL Server or Import mode?

          Reply
          • Hi Koen
            I was asked to give storage requirements for using SSIS with the DW and SSAS
            It’s safe to say I need 2017 here or will 2019 be the best bet?

  • We’re still in design phase but report server utilizing directquery and import mode.

    Reply
    • Koen Verbeeck
      March 11, 2019 12:02 am

      In that case, you only need to check what kind of features you need in SQL Server itself, since Power BI Report Server is updated independently. Unless you need a specific SQL Server 2017 feature (ML perhaps?), you’re good with 2016. Or you can wait for 2019 🙂

      Reply
  • Koen Verbeeck
    March 11, 2019 12:45 am

    I was wondering, the article mentions performance improvements for columnstore indexes in SQL Server 2017. I checked the grid which is mentioned in the paragraph over SQL Server 2016, but there’s only one feature mentioned for 2017 and it’s about “Columnstore index can have a non-persisted computed column”. Also, the 2017 paragraph ends with “There’s a tradeoff though:”. What is the tradeoff? The suspense is killing me!

    Reply
  • So it’s safe to say that 2017 was only released for compatibility with Linux. ?
    If that’s the case then why?

    Reply
    • Koen Verbeeck
      March 11, 2019 3:24 am

      Mainly Linux (and SSIS scale-out), but also a bit SSAS Tabular. If you were using SSAS Tabular a lot, I’d say to go for 2017 instead of 2016.

      Reply
  • Can i run SQL 2019 on Window Server 2012 R2 ?

    Reply
  • Henrik Staun Poulsen
    May 15, 2019 4:07 am
    Reply
  • hi
    We are planning to migrate the database from sql2008 to sql 2017.What will be the impact for us.and also what are features of 2008 are deprecated in sql 2017,Kindly share ur thoughts.

    Reply
  • I just came across this as I am investigating the upgrading of a couple of boxes. Thank you for your thoughtful and informative post. My question is do you have the same opinion now that it is almost a year later than when you wrote this.

    Reply
  • 2017 has had 4 CU released since then I believe. I didn’t know if that changed things or not. Actually I would prefer 2016 because that would make my versions consistent across multiple servers.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}