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 ended 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’re dealing with an application whose newest supported version is only SQL Server 2012, but not 2014 or newer.
  • 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 2 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’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 30-60 days – because on newer releases like this, the patches are coming fast and furious, and they fix some pretty significant issues. (Remember, there are no more Service Packs, just Cumulative Updates.)
  • You don’t mind undocumented patchesMicrosoft 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 (December 2019) 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.

Code smells
Is it supposed to smell this bad?

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.

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

129 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

  • 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?

    • 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.

      • 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?

      • Hey Brent I know you posted this a while ago but what’s your problem with Azure SQL PITR? I thought ot worked quite well. Has anything changed since your post?

        • Peter – read this post: https://www.brentozar.com/archive/2017/06/builder-day-point-time-restore-azure-sql-db/

          As of this writing (mid-2021), there’s still no progress report or guaranteed restore time for Azure SQL DB.

          • Sorry Brent I’m not sure what you mean by progress report. Do other cloud providers have a guaranteed restore time and what kind of guarantee would you say is reasonable?

            I get the problems you point out in your post but I think the PITR works pretty well.

            I don’t think you can restore a DB to a different server via the Portal btw; if you can I’d like to know how.

          • Peter – sure, no worries, start by reading this: https://www.brentozar.com/archive/2011/12/letters-that-get-dbas-fired/

            And then ask a few questions:

            • What’s the RTO for my Azure SQL DB under load? (When it’s generating a lot of transaction log activity, how long will it take to restore?)
            • Does that meet the business’s RTO goals?
            • If not, what options do I have to make it go faster?
            • When I’m waiting for that restore to finish, and the business wants to give the customers a status update, what will I say?

            Answers to those questions have stopped some of my clients from adopting Azure SQL DB. Hope that helps.

          • I’m not a big fan of the cloud and even less of a fan of Azure but I understand why they can’t make a guarantee… it’s for the exact same reason no one can guarantee the restore time of anything on-prem. Bad things happen. The best that someone can do on prem is state how long the last restore took and provide an estimate that it would take that long again with no guarantee that it won’t take longer because of something unexpected happening.

            It’s quite reasonable to expect no guarantees from any cloud provider and providing estimates would be a bozo-no-no because most people would incorrectly assume that an estimate was some sort of guarantee. Same goes with progress reports. Best laid plans of mice and men and all that.

          • My thoughts exactly Jeff. I’m not banging the drum for the cloud at all but don’t see how you can provide any real guarantees regardless of the platform. Grateful for your thoughts Brent.

          • Peter – it’s not a guarantee, it’s just an objective.

            When I give you a related reading link, I need you to actually read it, not just assume you know the contents.

            Take a deep breath, walk away, come back later, and read it with an open mind. It feels like you’re here to hammer me into a specific answer, not to learn. I’ve done my best here to help you along the path, but you’re the one who has to walk it. Learning isn’t about standing in place and insisting: it’s about taking new steps.

            And enjoy the journey!

          • Er, not sure what makes you think I didn’t read it but hey.

          • Because you’re talking about guarantees. The post doesn’t.

            Let’s take a time out, okay? Thanks for understanding.

    • 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.

      • 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”!

  • 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.

  • Great article. I feel thoroughly vindicated 🙂

  • SQL 2017 – You want adaptive query processing…

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

  • 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)

  • 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.

    • Koen Verbeeck
      January 25, 2019 6:59 am

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

      • 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!

  • 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!

  • 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.

  • 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.

    • 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.

  • 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).

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • Excellent summary Brent. Keep up the great work.

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

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

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

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

    This is a…. Unlike my above comment.

  • 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?

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

      • 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?

        • 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?

          • 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.

    • 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 🙂

  • 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!

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

    • 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.

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

  • Henrik Staun Poulsen
    May 15, 2019 4:07 am
  • 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.

  • 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.

  • 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.

  • Luis Agustin Azario
    November 21, 2019 9:33 pm

    Regarding “You want easier future upgrades – because starting with 2017, you can have a Distributed Availability Group….” I have 2 clusters i managed to install with Sql-Server 2017, each cluster in diffrent DC, only a pair of servers each, All Standard edition. I was able to configure and test almost without issues the windows Cluster, Quorum for it, AG, including failing over from Primary to secondary. Also created Listener and tested it.
    I am in the process of creating DAG for Disaster recovery, I know that I have only on Database per AG but among this I am not sure (can’t find specific and clear info) if I can fully configure DAG with Standard Edition.
    Can anybody confirm or tell me where to look ? Thank you

  • Kannan Chandrasekaran
    December 4, 2019 2:24 am

    Good Post, But my opinion is please be using SQL server 2008 and it is consider as most stable database engine. Recent SQL server versions are not stable, that’s why Microsoft keep releasing multiple SQL server version every year. All of their latest versions are just a fancy wordings. To my 10 years of experience in SQL server Database administrator SQL server is marketing 2016 with clustered column store,Always on load balancing, OLTP workload optimization with new cardinality estimators. But none of them are working as per the expectations. We recently faced a count query issue on our largest table after creating non clustered column store index. The table actual row count was 1 billion but after index creation it returned with 40 billion as a count. all of our query results are running with incorrect result and Microsoft confirmed this as a bug and provided their fix in CU. We will not accept mistakes in basic things like select count with incorrect results, this will impact the business. Still SQL server have no improvement in table partitioning, still always on supports with full recovery model, enabling legacy estimator in database scoped configuration for queries running well in older database version. challenges in memory optimized tables implemented in always on. durable table works similar to normal table but the table creation syntax is the only different and requires additional file group to ho;d such tables. Running durable memory optimized count query result duration is similar to normal table count duration. When comes to large volume those fancy will not work as per the expectations.

  • Kannan Chandrasekaran
    December 4, 2019 3:23 am

    We are using SQL server 2016 sp1 enterprise edition. The problems we are facing are our realtime issues, those are not received by surfing any websites.When come to performance majority of the stored procedures are running behind 2008 and 2012 in 2016.

  • Kannan Chandrasekaran
    December 4, 2019 3:49 am

    Thanks for agreeing. When we are planning to go with latest version the features projected by product vendors will not produce incorrect results. Cardinality estimation is one of the major problem. We have 1500 objects works well up to 2012 after 2016 execution durations increased and tempdb and db logs are running out of storage, enabling legacy estimation on or change db compatibility level to 2012 resolving our problem. Now SQL server released 2017 and also preparing for 2019. In that case we all prefer to go with 2019, think about companies migrated to 2017 will pay additional cost for 2019. Microsoft should consider their customers when releasing latest versions. Releasing cu is different than version release. If possible kindly refer niko post and search my name I was describing my problem and niko also agreed..

    • I’m not agreeing. I’m not disagreeing either. I just haven’t seen your server. If you’d like to hire me to find out whether I agree, feel free to click Consulting at the top of the screen. Thanks!

  • Here’s one for’ya! In 2003, we set up a database in SQL 7 (I think that’s what it was) – THEN, later, was able to upgrade it to SQL 2000 installed on a Server 2003 virtual server.

    Now – 2019 – We have have SQL 2012 installed – Come to realize, you can’t just go from SQL 2000 to 2012 – ONLY via SQL 2008. So – I made that happen.

    ONLY to realize – my custom app uses RAISERROR and TSQUAL which aren’t compatible in SQL 2012 – So, I had to change all my SPR’s…

    Now – ready to flip the switch – finding out SQL 2012 ends support in 2022 – and NOW bringing me to this page.

    I was going to consider 2019 and just go for it.

    Your thoughts?

  • Kannan Chandrasekaran
    December 19, 2019 10:52 pm

    Hi Timothy King, No need to fear about end of support. As a Microsoft SQL Server DBA , we raised a support ticket to Microsoft support team for a major bug in non clustered column store index in 2016 version SP2 due to our internal security policies restrictions we are unable to bring the support team to diagnose our server. Because the team will install some diagnostic software and collect logs from our server, as per the policy we have so many restrictions and unable to proceed further, in that case we are unable to utilize the support. Better to use a stable version of SQL server, I believe 2008 or 2012 consider as a stable versions, to my experience new versions of SQL server are concentrated in cross platform technologies for analytics workload, most of the existing queries running well in 2012 are running with degraded performance due to the latest cardinality estimation and optimizer enhancements, Even Microsoft accepted this as a bug and provide workaround like this, enable legacy cardinality estimation on, use query hint for the specific query blocks, change sql server compatibility to 2012 something like this. But one thing we need to consider in future if there is very limited scope to bring other data source data for processing in your environment means we can run with older version of SQL server. Existing features requires lot of improvements but Microsoft is not looking such things and releasing versions like a movie. some of them are table partitions enhancements as of now no composite partition supported in Microsoft but we can achieve in other ways but this is not a solution, varchar(max) columns are not supported in index, Requires improvement in fibre mode execution (enable light pooling)instead of thread pool to avoid CPU context switching problems, fibre mode will not support external dll execution and other things, non durable memory optimized tables not reflected in always on. If i am explains multiple items then people may thing i am surfing from internet and write those but not like that these are all our real time issues we faced. Please stick with your stable SQL server version for your continuous application support without any escalations. Don’t spend your dollars for new version if you are going to run only simple or complex stored procedures.

    • Kannan – you may want to check with your company’s legal team and insurance companies. Some folks aren’t legally allowed (or prohibited by their insurance companies) from running software that is no longer supported by the vendor.

  • Brandon Forest
    January 3, 2020 3:31 pm

    Brent, I’m making the case to our CIO for upgrading our SQL2012 servers . A year later, is the your advise still to stay with SQL2017?

  • With all the supposed “improvements” they made to 2016 (they were designed to support people that apparently don’t know much about SQL Server and were crushing for me) and seeing similar “improvements” that cannot be “disabled” in 2019, the impending “upgrade” to 2019 scares me to death.

    For example, how many people actually know what the permanent changes to TempDB in the form of making TF1117 functionality no longer optional for TempDB are? I’ll bet very few. It made it impossible for me to copy a small 25GB table that required SET INDENTITY_INSERT ON because of yet another “improvement” that causes the table to be sorted in TempDB even though the Clustered Indexes are identical because we “right sized” our TempDB to use 8 files on a 100GB disk allocation. All 8 files automatically tried to grow to 25GB. 8*25GB > 100GB and BOOM!

    How many people know about the automatic FAST INSERT functionality that MS imparted on 2016 as a default and how many people actually know how badly it screws your databases if you don’t actually need that functionality? This is the thing that automatically creates new extents of inserted data that arrives as “bulk” without checking to see if already allocated extents with free space on them already exist for the sake of performance. The trouble is, if only one row is inserted using “insert bulk” (not to be confused with BULK INSERT, etc), it allocates an entire extent. For programs that use that method (and there are a whole lot more than most would think), you’ll find a whole lot of “allocated but unused” space being created. The only way to recover that space is to rebuild the related heap or index. And that’s why people don’t usually see the effect because they’re constantly undoing the damage by using index maintenance…. which they’re also doing wrong especially if they’re using REORGANIZE. The only way to overcome the problem without changing code is to use TF 692.

    Of course, we won’t get into things like how 2012 had a problem that would frequently corrupt Clustered Indexes if you rebuilt them with ONLINE = ON or how the original release of 2014 SP1 destroyed a lot of SSIS servers (which I very thankfully advocated not using at work and fortunately jumped from 2012 to 2016 skipping right over 2014).

    The “hits” just keep on coming and I fear for the servers and the data.

    “Change is inevitable… change for the better is not.”

  • Hey Brent,
    We are planning to upgrade our SQL server from 2104 to SQL Server 2016.
    Our SQL Server is in a clustered setup, and Infrastructure is planning to do a side-by-side upgrade(where they will spin up new windows server machines and move resources from old machines to new machines)
    We have every 99% SSIS packages stored in File System, and 2% in SSISDB(Integration Services Catalog).
    We have SSRS reports too.

    What should be our approach towards SSIS packages and SSRS reports , while SQL server is getting upgraded.

  • Rudy Panigas
    July 6, 2020 1:14 pm

    Now that we are in July 2020, do you still feel the same about using SQL 2017 over SQL 2019? Also, do you recommend using compatibility mode? We has some SQL 2014 servers and are thinking of running SQL 2019 and change the compatibility mode to SQL 2014. No much to gain but can upgrade by changing the compat mode. Love to hear your opinion on this

  • Has Microsoft published a Roadmap for SQL Server beyond SQL Server 2019?

  • Now that we are in October 2020, do you still feel the same about using SQL 2017 over SQL 2019? There are no new features we wish to take advantage of (at this time), just want to push out the time to the next upgrade (2030, hot diggity!). We are currently happily on 2012, just don’t want to get too antiquated. I am the DBA so would like to go 2019, but dev feels we should go to 2017. Thank you.

    • I don’t recommend that folks go to SQL Server 2019 due to the quality problems – unless there’s something they desperately, desperately need that’s only available in 2019.

      • Thanks Brent. Your response time rivals even the strictest of SLA’s. 🙂

      • 2019 has always scared me to death with all of the supposed “improvements” they’ve made for reasons of performance. You’ve justified my reasoning there. It reminds me of the RTM for 2017, which was just awful. Thanks for your post, Brent.

    • How about upgrade to 2016 from where you are. Consider it base camp for the next upgrade. You will be in striking distance of the next upgrade and can hang with 2016 for years if you want.

  • After reading the post and all comments, I am getting the impression that “upgrading just to be up-to-date” isn’t viewed favorably in the DB community? Looking for ammunition to push back against management who hears we are running on 2014 (while the calendar will soon say 2021). We aren’t using big DBs, clustering, hyper-anything, etc., and I don’t look forward to upgrading our servers every 2-3 years because MS has come out with a new version.

    • Typically, change equals risk. If something is working, then what’s the best case scenario for an upgrade? It continues to work, only more efficiently.

      • That’s definitely a “Best Case” scenario that I’ve frequently not seen materialize with such changes. Normally, the reverse has been true every time a new version comes out. 2017 RTM was a great example of “Change is inevitable… change for the better is not”. In fact, I’ve not seen an RTM yet where something works “more efficiently”. I used to wait for SP1 but 2012, 2014, and now 2017 changed all that. If I can afford to do so, I try to quietly lag behind by at lease 1 version. If you remember all the horror in 2012 until they finally fixed most of their regression mistakes in SP3, you know why I take such a position.

        Yeah… they’ve complicated the matter by not marking anything as an SP anymore, which is another reason I try to avoid whatever the current version is so long as the version I’m using is still supported.

  • Luis Agustin Azario
    November 6, 2020 10:45 am

    I share both postions Todd’s and Brent’s, anyway I can share with you that I spent las two years migrating about 20 to 50 Sql-Servers from 2000, 2005 and 2012 to newer versions, 2016 or even to 2019, when posible and no critical proyects.
    I had a very good experience with the hole thing, for example, Always-on, for example is great, very powerfull tech, I am also involved in RDBMS radical migration, only a few, from Oracle to Sql-Server, due to Management decisions for lowering license costs and this also were a success.

  • Hi, seeing as nobody appears to have asked for a few months, now that we’re in 2021 I’m curious as to whether you still feel the same about preferring SQL Server 2017 over SQL Server 2019 in most use cases, or has 2019 finally matured enough?

    And if someone is only using Web Edition features, how does that affect your recommendation?

  • A noticeable change between 2017 and 2019 is the capabilities of graph databases. You can directed graphs in 2019 using edge constraints and it protects against deleting nodes with edges, things not in 2017. I know that most people aren’t getting SQL Server to use a graph database (Neo4j is probably what comes to mind first), but that you can leverage graph databases *with* “standard” relational tables *and* not needing to migrate to another DBMS is something quite a few people I work with find a lot of use of.

  • Alexandre Quinas
    March 20, 2021 11:24 pm

    Great Article!
    We have some Databases in 2012 and 2014, and were in the final phase of testing with SS2019, and in one particular database we use a lot of UDF and TVF, the performance in these database is in average 1.5 slower than in the current production environment. Already tried every configuration possible in the server, disabling inling in some functions helped, but most of the functions are lot inlineable! Probably will Go to SS2017!

  • Is possible to run swing migration from SQL SERVER 2008 R2 to 2019 with Log Shipping?

  • Support for UTF8 is important for data warehouse running data vault.

    The way Unicode characters are hashed in sql until SQL Server 2019 was not consistent with hash made in Python or other languages.

    So if you hashed your data vault keys with sql server and you want to integrate that with data stored outside of sql say in a datalake, and your hashing values had Danish letters for instance, then the same key will have two different hash values.

    Btw, does the recommendations to wait with 2019 still stands in April 2021?

  • Hi, You still preferring SQL Server 2017 over SQL Server 2019?

  • To be honest I don’t know, you mean we still shouldn’t use SQL server 2019? because …

  • Miroslav Georgiev
    June 16, 2021 3:31 pm

    We have now 11 CUs for 2019 and almost 2 years sice its release. What is the big blocker with SQL 2019 to go to production? Is there something specific that is dangerous at this moment? Please consider that 2016 is almost out of mainstream support and only 2017 and 2019 will have full support.
    Thank you.

    • Read the 2019 section again, really slowly this time, and click on the links.

      • Miroslav Georgiev
        June 17, 2021 8:12 pm

        Hello, I had the feeling that you do not recommend it at all, but it seems I am not entirely right after I read carefully:)
        In our case we have all the issues that SQL 2019 suppose to fix. We have dramatic use of UDFs, temp tables, table variables and a lot of contention on tempdb (doesn’t matter what we do). Even we are facing last-page contention on some tables. I hope to have more benefits than negatives.
        We aim to go to Prod Q4 2021

        • I absolutely understand and appreciate the “hope” there. I have similar problems but I’m scared to death of all the nasty things I’ve heard of in 2019. If we do the migration from 2016 this year, I’ll post here to let folks know what we found. If anyone else does the migration, it would sure be nice if you good folks would reply on this thread with the same vigor and detail to let the rest of us know how things worked out. Heh… I can’t put my finger on it but something tells me that migration to a newer version is a bit difficult to ultimately avoid,. 😀

  • I do hate supporting multiple SQL Server versions. Its difficult to implement new features, then do a separate cut for older versions.
    It would be nice if a patch to older versions would allow ignoring syntax specific to new versions when possible. Say we have a new OPTION syntax. A patched 2017 build would recognize this as a valid 2019 syntax, and then ignore it.

  • in the days of FORTRAN, and Cray then super computers having special parallel processing features, (yes, I am that old), there would be a special comment syntax, that other compilers would treat as comments, but the Cray compiler would recognize as special directives.

  • Surya Balusu
    June 21, 2021 1:20 pm

    Hey brent as we are already in 2021, is it better now to install SQL 2019?

  • Just installed CU 11 on fresh installed FCI.
    guess what…
    had to uninstall the CU since the failover did not happen.
    2019? I still doubt

    • In the last year, I have been having more and more problems with antivirus/antimalware programs interfering with SQL servers, and especially SQL servers that have something in them that use failover clustering in both 2016 and 2017. Cylance especially has been particularly problematic, but have had issues with cisco, defender, mcafee and to a lesser degree fire eye. Exclusions lists that used to work, have needed to be added to, in order stop what appears to be heuristics engines from scanning activities they have seen on a particular server literally hundreds of thousands of times. Have had something like installing a CU cause a failover cluster or availability group to fall apart, sometimes after OS reboot come back and then not be an issue again, but also sometimes having to uninstall CU, turn off the AV and reinstall CU, to make it work again.

  • Mark Rodriguez
    August 31, 2021 5:54 pm

    We have a SaaS vendor who is updating the version of SQL Server from 2016 to 2019. We receive SQL backups from them and restore to a SQL Server 2016 in our data center, which would mean we need to upgrade our servers to 2019 as well.

    Generally speaking, do the same concerns with SQL Server 2019 exist if you keep databases in a lower compatibility mode (say 2016 or 2017)?


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.