Reasons to Upgrade to SQL Server 2017

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 3^rd 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

• Update to support partition elimination in query plans that have spatial indexes in SQL Server 2016 and 2017
• Improvement: Enable "forced flush" mechanism in SQL Server 2017 on Linux

SQL Server 2017 CU5

• Better intra-query parallelism deadlocks troubleshooting in SQL Server 2017
• Improves the query performance when an optimized bitmap filter is applied to a query plan in SQL Server 2016 and 2017

SQL Server 2017 CU4

• Improvement: Improves the performance of SQL Server 2017 on smaller systems

SQL Server 2017 CU3

• Update adds support for MAXDOP option for CREATE STATISTICS and UPDATE STATISTICS statements in SQL Server 2017
• Improve tempdb spill diagnostics in DMV and Extended Events in SQL Server 2017
• Update enables XML Showplans to provide a list of statistics used during query optimization in SQL Server 2017
• Update adds execution statistics of a scalar-valued, user-defined function to the Showplan XML file in SQL Server 2017
• Update adds optimizer row goal information in query execution plans in SQL Server 2017
• Update enables PolyBase technology in SQL Server 2016 and 2017
• Update adds CPU timeout setting to Resource Governor workgroup REQUEST_MAX_CPU_TIME_SEC in SQL Server 2017

SQL Server 2017 CU2

• Update for manual change tracking cleanup procedure in SQL Server 2017
• Improvement: General improvements to the change tracking cleanup process in SQL Server 2017
• Update adds a new extended event "marked_transaction_latch_trace" in SQL Server 2017 on Linux and Windows
• Performance improvement for Spatial Intermediate Filter in SQL Server 2017

SQL Server 2017 CU1

• Update to improve the performance for columnstore dynamic management views "column_store_row_groups" and "dm_db_column_store_row_group_physical_stats" in SQL Server 2016 or 2017
• Add the ability to disable or enable a few new DMVs and DMFs introduced in SQL Server 2017
• Enhancement: New keyword is added to CREATE and UPDATE STATISTICS statements to persist sampling rate for future statistics updates in SQL Server

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.