Glenn Berry

Reasons to Upgrade to SQL Server 2017

Free eBook : Query Optimization
SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Register to Download

Featured Author

Paul White is an independent SQL Server consultant specializing in performance tuning, execution plans, and the query optimizer.

Paul’s Posts

Since SQL Server 2012 fell out of Mainstream Support on July 11, 2017, and SQL Server 2014 will fall out of Mainstream Support on July 9, 2019, and as both SQL Server 2008 and SQL Server 2008 R2 will be falling out of Extended Support on July 9, 2019, I have had an increasing number of clients who are planning to upgrade from these legacy versions of SQL Server to a more modern version (either 2016 or 2017).

If you are considering an upgrade to a modern version of SQL Server, should you move to SQL Server 2016 or to SQL Server 2017? My general guidance would be to strongly prefer SQL Server 2017 in most cases, unless you have a valid, tangible reason not to. For example, if you have 3rd party software that is explicitly not supported on SQL Server 2017, that could be a blocker (although I would try talking to your software vendor about that).

So why should you prefer SQL Server 2017? Here are a number of reasons in several different categories.

New Engine Features in SQL Server 2017

Even though much of the development work in SQL Server 2017 was dedicated to making a version that runs on several different Linux distributions, there are still a number of useful new Engine features in SQL Server 2017.

One useful new feature is Automatic Plan Correction (which relies on the Query Store feature that was introduced in SQL Server 2016). This feature helps detect and automatically correct many query plan stability issues. My colleague, Erin Stellato, has written about Automatic Plan Correction and how it works in SQL Server 2017 Enterprise Edition and Azure SQL Database in much more detail.

Another new feature is Adaptive Query Processing (AQP) (which is enabled with compatibility mode 140), which is very helpful for batch mode operations used with Columnstore indexes. AQP has three components, which include Batch Mode Adaptive Memory Grant Feedback, Batch Mode Adaptive Joins, and Interleaved Execution for Multi-Statement Table Valued Functions.

There are also a number of diagnostic and troubleshooting improvements that make the life of a DBA much easier! These include multiple Showplan enhancements that are very useful for query tuning, along with several new DMVs that are useful for diagnostic troubleshooting. Query Store is even more efficient in SQL Server 2017 compared to SQL Server 2016.

There were also several “community-driven” enhancements in SQL Server 2017, such as smart differential backup, smart transaction log backup, improved backup performance for small databases on high-end servers, and improved tempdb diagnostics and monitoring.

Replication Enhancements

Microsoft has made it possible to protect a remote distribution database in an Availability Group with SQL Server 2017 CU6. They have also added the ability to dynamically reload transaction Agent profile parameters in SQL Server 2017 CU3. This makes it much easier to tune and maintain your replication topology than it was in the past.

Microsoft Support

Once a major version of SQL Server falls out of Mainstream Support, there are no more Service Packs or Cumulative Updates. Only security updates are released while the major version is in Extended Support. After Extended Support ends, there are no more updates at all unless you purchase Microsoft Premium Assurance (which is only available for SQL Server 2008 and newer).

Recent evidence of this policy is the fact that Microsoft will not be releasing a Spectre/Meltdown security patch for SQL Server 2005. Once the General Data Protection Regulation (GDPR) goes into effect on May 25, 2018, older versions of SQL Server will be more vulnerable over time as they fall out of Microsoft Support.

SQL Server 2017 will be fully supported for about 15 months longer than SQL Server 2016. Microsoft has a page with many resources about the end of support for SQL Server 2008 and 2008 R2.

The Mainstream and Extended Support end dates for all recent versions of SQL Server are shown in Table 1.

SQL Server Version End of Mainstream Support End of Extended Support
SQL Server 2005 April 12, 2011 April 12, 2016
SQL Server 2008
SQL Server 2008 R2
July 8, 2014 July 9, 2019
SQL Server 2012 July 11, 2017 July 12, 2022
SQL Server 2014 July 9, 2019 July 9, 2024
SQL Server 2016 July 13, 2021 July 14, 2026
SQL Server 2017 October 11, 2022 October 12, 2027

Microsoft Servicing Policy

Microsoft SQL Server 2017 was released on October 2, 2017, and so far, there have been six SQL Server 2017 Cumulative Updates that have been released. Microsoft has adopted what they call the “Modern Servicing Model” for SQL Server 2017, which means that there will be no Service Packs for SQL Server 2017 or future versions.

Instead, there will be monthly Cumulative Updates for the first year, and then quarterly Cumulative Updates for the next four years after that. This means that defects will be fixed much more quickly during the first year after release. SQL Server 2017 has been released long enough that I consider it quite stable.

Not only does Microsoft correct specific defects in Cumulative Updates, they also release new functionality and other product improvements, which are quite often focused on improving performance. Here is the list of the updates and improvements for SQL Server 2017, as of CU6.

SQL Server 2017 CU6

SQL Server 2017 CU5

SQL Server 2017 CU4

SQL Server 2017 CU3

SQL Server 2017 CU2

SQL Server 2017 CU1

Even though you might think SQL Server 2017 was “just a Linux port,” there are actually many real-world improvements in the core Database Engine that will benefit all platforms. Microsoft continues to add extra functionality to the product in SQL Server 2017 Cumulative Updates, and SQL Server 2017 will be fully supported by Microsoft for a longer period of time than SQL Server 2016.