Joey on SQL Server

So You Need To Move Off SQL Server 2016

As of July 13, SQL Server 2016 no longer has mainstream support. Your next steps as a DBA include understanding Microsoft's support model, knowing the risks of upgrading and then biting the upgrade bullet.

• By Joey D'Antoni
• 07/20/2021

SQL Server 2016 lost mainstream support this month, on July 13. While you can still run SQL Server 2016 and open support tickets, there will be very few performance updates. In an unusual step, however, Microsoft announced it will release a Service Pack 3 for SQL Server 2016 in the third quarter of this year. It will remain in a semi-supported state until July 2026. After that, you won't see any fixes for performance bugs, only security updates.

While it seems like just yesterday that SQL Server 2016 came out, there have been two new releases of SQL Server since then. Meanwhile, SQL Server 2012 is also going off of support completely next year. And, while you are thinking about your servers, Windows Server 2012 R2 is in extended support, and you should look to move to Windows Server 2019 or the forthcoming 2022 edition.

Understand the SQL Server Support Model
Each version of SQL Server is backed by Microsoft's support policy, which includes a full five years in mainstream support and five years in extended support. Microsoft defines these as follows:

• Mainstream support includes functional, performance, scalability and security updates.
• Extended support includes only security updates

Beyond extended support, there are some other support options, including purchasing extended security updates or moving workloads to Azure Virtual Machines (which would include those security updates at no cost).

Being stuck on an outdated version of SQL Server is not very much fun for a DBA, and it also likely means you are on an older version of Windows. Frequently, the decision to stay on an older version is not made by the IT organization; it is dictated by third-party software vendors that have not tested their code against newer versions of SQL Server. 

You can get support for versions of SQL Server that are out of support, but the options are not easy. The first possibility is to simply pay for it. For example, if you have an application that is stuck on SQL Server 2012, you can pay for extended support -- but be prepared for sticker shock. According to Microsoft:

On-premises: Customers with active Software Assurance or subscription licenses can purchase Extended Security Updates annually for 75 percent of the license cost of the latest version of SQL Server or Windows Server for the first year, 100 percent of the license cost for the second year, and 125 percent of the license cost for the third year. Customers pay for only the servers they need to cover, so they can reduce costs each year as they upgrade parts of their environment.

The normal cost of software assurance for SQL Server is 23 percent of your licensing cost, so extended support is a three- to five-fold increase.

The other choice you have for both Windows Server 2012 R2 and SQL Server 2012 is to move your workloads onto Azure Virtual Machines. This is an easier option for companies that are already in the cloud, but if you are exclusively on-premises, moving to Azure is a project onto itself. Staying on older software is a classic example of technical debt, which, in this case, has a clearly defined cost.

What's the Right SQL Server Version?
This is an easy answer. If you are building your own software, use the latest release of SQL Server and the latest supported version of Windows or Linux. The operating system that SQL Server runs on doesn't really matter, so just use the latest supported version. There are some exceptions to this related to failover clustering, but that code has been static since Windows Server 2016.

Each release of SQL Server typically includes new features, added metadata for troubleshooting and other enhancements. For example, SQL Server 2019 has a feature that allows you to capture the last execution statistics for a given query, something that previously required a very expensive capture process. 

Knowing the Risks
As a consultant, I talk with a lot of customers about SQL Server (and Azure) and the topic of upgrading SQL Server versions comes up often. Many organizations are nervous about upgrading their database software. Frequently, this concern is driven by a lack of understanding in both independent software vendors (ISVs) and IT organizations. While support from ISVs is important, if your vendor isn't keeping up-to-date with releases of Windows and SQL Server, you may need to make the difficult decision to move on without the vendor's blessing.

Understanding the risks of a SQL Server upgrade can be helpful. The first risk is that in upgrading the version of SQL Server, you run into an error during the setup process. This is exceedingly rare; I asked my colleagues and we only remember three instances of this happening in our collective 120 years of experience. Beyond the rarity of these failures, they can easily be mitigated by not doing an in-place upgrade, and building a new server and restoring your databases to it.

Besides the pure risk of the upgrade, the larger risk is that changes to the database engine between versions can cause your queries to degrade in performance. While this risk is bigger than the upgrade risk (a change to the cardinality estimator in the SQL Server 2014 optimizer impacted a lot of workloads), there are easy ways to mitigate against that. SQL Server and Azure SQL Database have a property called compatibility level. Microsoft supports compatibility levels back to SQL Server 2008 and has committed to keep those in newer releases of the engine.

This means your SQL queries will perform the same in a newer version of the database engine as they did in the past. This is both good and bad; you may miss some performance improvements, but this does reduce the risk of any unexpected performance anomalies. You can also use the query store feature in SQL Server 2016 and above. This is helpful if you want to upgrade the compatibility level of the database to take advantage of newer performance features.

Running your business on unsupported software is a major risk, in terms of both security and management headaches. Microsoft has provided several options for support and for managing the upgrade process with precise control. Beyond just upgrading your database, you should push your application vendors to support new versions of SQL Server and Azure SQL to ensure you stay current and supported.