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 2016 if…
- You’re working with slow-moving enterprises that haven’t updated their build test lists in years. SQL Server 2016 is well-known and accepted at every enterprise shop out there.
- 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’re willing to build a new server in a few years – because even extended support for this product ends in 2026. Even in slow-moving enterprises, they still want their SQL Servers to be under support, and there’s already a time bomb here. If the migration process will take 6-12 months to plan, budget, get approval for, test, and execute, then you would need to start that migration in 2025, which isn’t that far away.
- 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 late 2023, SQL Server 2016 is still the #2 most popular version, which is pretty amazing given that it’s ~7 years old.
You should consider SQL Server 2017 if…
- 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, 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’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 or 2019.
You should consider SQL Server 2019 if…
- You want years of support – 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 only want to apply patches every 60 days, or less – because after a couple dozen Cumulative Updates, this version is stable enough that most of the big bugs feel like they’ve been found.
- 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.
You should consider SQL Server 2022 if…
First off, I don’t think you should. SQL Server 2022’s flagship feature still isn’t ready yet. But if you still want 2022, consider it only 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 compat level 160 basically breaks 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 in late 2023, 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.
150 Comments. Leave new
Excellent… Its very best information, in SQL Server Paradigm Shift. Thanks for writing for this, will adhere the knowledge. Thanks very much. Jay
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.
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?
“Should be doable” = check your SLA. 😀
I’ve just tried restoring the database. 5 GB took 71 minutes on the S2 level. Will test with production data soon. Thank you for the warning.
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:
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.
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.
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 🙂
Thanks, glad you liked it!
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.
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!
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.
I’d be looking for a new ERP if I’m forced to run a version of SQL that exits extended support soon.
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.
As a starter for 10 you could look at using DEA (https://docs.microsoft.com/en-us/sql/dea/database-experimentation-assistant-overview?view=sql-server-2017)(ignore the 2017 part – it applies for 2016 as well) and before Brent jumps on me- as I said it is a starter for 10. It generates all the reports and allows you to focus on where needs to be improved. There are scripts out there as well for building the platforms in Azure if you have access and credit to run it up there.
DiscoBob – oh no I totally agree, it’s a good fit for exactly this purpose, and you were smart to suggest it here. Cheers!
Thanks Brent – Made my day 😀
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.
Matt – yeah, generally I prefer virtualization for that scenario. So much easier to patch guests.
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.
You’re welcome, glad you enjoyed it.
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.
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?
Thanks!
Sure, check this out – it’s really cool! https://powerbi.microsoft.com/
PowerPivot for Excel still exists, it’s now called “the Excel data model” since Excel 2013. In terms of functionality and new features though, Power BI (Desktop) is lightyears ahead.
This a very well thought out post! Well done Brent!
Thanks sir!
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?
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.
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 🙂
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!
I don’t know about the tradeoff, but for 2017: http://www.nikoport.com/2018/01/05/columnstore-indexes-part-118-sql-server-2017-editions-limitations/
Yep, Niko’s 2017 post sums it up well. About the tradeoff – doh, that’s left from an earlier version of the post. Thanks!
So it’s safe to say that 2017 was only released for compatibility with Linux. ?
If that’s the case then why?
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 ?
hi Alvin,
http://lmgtfy.com/?q=Can+i+run+SQL+2019+on+Window+Server+2012+R2+%3F
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.
That’s a little beyond what I can do quickly in a blog post comment.
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.
Clay – have any versions of SQL Server been released since the post was written? If not, why would my opinion change?
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.
That’s not a new version, so no, no changes to the post. Thanks!
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
Luis – for unrelated questions, hit a Q&A site like https://Dba.stackexchange.com.
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.
Alrighty then.
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.
I’m not disagreeing – I’m just thinking maybe – just maybe – the problem isn’t SQL Server.
+1
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?
For personalized advice on your server, that’s where consulting comes in. You can click Consulting at the top of this page for that kind of help.
I sent you a contact form. Definitely interested in consulting.
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.
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?
I update the post every release – I’ve already updated it since it was originally posted. Enjoy!
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.”
Wow, I love that last line!
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.
Kallol – sorry, I don’t do SSIS or SSRS.
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
Yes.
Has Microsoft published a Roadmap for SQL Server beyond SQL Server 2019?
No, they generally don’t publish forward-looking roadmaps for SQL Server.
So no idea when the next major release will be either I suppose.
Nope. There’s not a public preview yet, and even when they have public previews available, they don’t announce the release date right away, so we’re quite a ways off.
Great Thanks!
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.
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?
TIA
No changes at this time.
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.
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?
Darwin – for general unrelated questions, head to a Q&A site like https://dba.stackexchange.com or https://sqlservercentral.com.
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?
Has something changed?
To be honest I don’t know, you mean we still shouldn’t use SQL server 2019? because …
Hello,
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.
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,. 😀
Agreed with Jeff there, and “hope” isn’t a strategy: we gotta test before we go live.
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.
Hey brent as we are already in 2021, is it better now to install SQL 2019?
Read the post carefully. Cheers!
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.
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)?
Mark – go through the list of concerns on 2019, and think about which ones happen regardless of compatibility level.
With latest CU 16 for SQL 2019 where a lot of bugs seems to be fixed, do we consider this version stable? I agree there were a lot of issues, especially with the new features and improvements, but I think most of the problems were stabilized. What is your opinion?
Next year the only really supported version will be SQL 2019 (extended support is only for Security fixes)
Hi Team,
I have one question. We have SSAS tabular – 2016 version. all Power BI Reports are live connected to SSAS 2016 tabular cube. We have upgraded from 2016 to 2019 version. I have seen that single query is a lot faster into SSAS 2019 compare to SSAS 2016 in DAX studio as well as in Power BI but we we perform load testing like many of users are accessing at same time then performance is really worst in SSAS 2019 with compare to SSAS 2016 since CPU usage is continually in 100%. It is not case for SSAS 2016 with same amount of load and Avg. CPU utilization is 50%. Is it something DAX query in SSAS 2019 takes more CPU then 2016 ? which I have not observed in DAX studio with single query execution.
For personalized assistance with performance tuning, click Consulting at the top of the page.
Hey Brent as we are already well into 2022, anything changed on your stand SQL 2019?
I keep the post updated. Cheers!
The conclusion at the end still recommends SQL Server 2017, but the features of each version make it sound like SQL Server 2019 is a better choice, looking ahead. Does the recommendation of 2017 stand?
Do newer SQL versions have more bugs for Microsoft to patch, or is it just that they don’t bother fixing the bugs in the older products, particularly in “extended support”? You mentioned that new features have had less real-world bug finding (the bugs being mostly rare conditions anyway), but what if I don’t use new features? I suppose there are new features that may affect how any query is run, when that query wasn’t a problem before.
I don’t enjoy installing patches, and it may be hard work persuading management to approve the work, but it’s necessary.
While I’m on, what was that about nonclustered columnstore indexes being not updatable previously? Does that mean that you can update table data, but the columnstore index returns the old data? That’s a “Whoa” moment.
The article stands. For more in-depth Q&A about your particular architecture’s needs, feel free to click Consulting at the top of the screen. Hope that’s fair. Cheers!
Hi Brent
These last few months I’ve been planning our migration from in house SQL servers running on 2014, over to an Azure Managed Instance as our servers are approaching EOL. Now I’m aware that they’re running on the lastest SQL core (whether I want to or not) so always up to date, which at time of planning and design I thought was a positive, but this post has now made me reconsider. Features which work now, could change during a ‘silent’ update when MS decides to upgrade. I guess this means I should also be testing against SQL 2022 when released before its features are introduced to Azure SQL and hope theres nothing breaking in there?! How do others plan for something unknown?
Hi! This is really beyond the scope of this blog post, unfortunately. You might try a Q&A web site like dba.stackexchange.com or sqlservercentral.com.
SQL Server 2019 CU17 is out,
Is it a time to upgrade to SQL 2019?
Thank you
No… wait for at least the 3rd CU of 2022. 😀
That will be around the same time as support end date for 2019? 🙂
So here’s a legit question… (not that others aren’t 😉
At what point should someone ever consider moving on from 2017 – only when some new feature is added that you MUST have? When 2017 at MS’s end-of-support?
The reason I ask is that should no new must-have-feature be added, is it reasonable to make 10-year leaps of the product, as one version becomes obsolete (support-wise) you love to the latest/newest version and buy yourself another 8-10 years of blissful non-upgrade time?
This makes some sense to me, assuming the version you are using is still working for you, and the upgrade path to the next jump isn’t too onerous.
Change equals risk.
So ask, why change the server? There needs to be a reward in exchange for the risk. What’s the reward that you need in the newer versions? That’s how you make the decision.
For those still wondering if there’s a reason to move off of 2017, I discovered the version of SQL Reporting Services (SSRS) that comes with 2017 doesn’t support HTTP Strict Transport Security (HSTS) (a simple standard to protect visitors by ensuring that their browsers always connect to a website over HTTPS). If you want/need to have your SSRS reports displayed in the client browser support HSTS, you have to upgrade to at least 2019. If you are working with several instances of SQL, maybe you could have the one for SSRS upgraded to 2019, while leaving your “data” instances at 2017? Haven’t tried this, as we have shared instances (supports both data and reporting).
Brent any comments on SQL 2022 CU9 does it fix the issues
Rather than me commenting on every single CU, what it fixes, and what it doesn’t, I’d rather teach you to fish. Read what I’ve said in the post, and then look at the release notes for CU9 to see if it fixes the issues.
Thank you very much Mr. Ozar, I love the quality of information and education you provide. Thanks.
Thanks for reminding me that SQL 2017 exists, I’d completely forgotten!
Now to forget it again…
HA!