End of Line for SQL Server 2008 R2 and Windows Server 2008 R2

In the next year, both SQL Server 2008/2008 R2 (July 8, 2019) and Windows Server 2008/2008 R2 (Jan 14, 2020) are hitting their end-of-life dates. No more security patches*, hotfixes, or support from Microsoft. In this day and age, and with your regulatory environment, this is a certain deadline. Soon, if not already, your security and infrastructure audits should flag these versions of SQL Server as a vulnerability.

What This Means for your Old SQL Servers

While both the end of life for these Windows and SQL versions present a clear and present danger to your compliance and stability, I’ll be talking mostly about the path forward for your SQL Server environment.

Those long-needed server upgrade projects need to get rolling. Keep in mind that both products are already outside “mainstream support” and are in “extended support”. In fact, these 2008/2008 R2 versions have been in “extended support” for years, meaning that Microsoft assistance isn’t free, no features are added, and only bug fixes and patches are released.

Your SQL Server probably contains many databases, and probably serves one or more applications. At Sparkhound we have a thorough migration checklist for the planning, preparation, migration and testing phases. The safest way to conduct SQL Server migrations is to proceed application by application. This method allows individual applications and their connection strings to be tested with all the component pieces inside of the new SQL Server instance, while also ensuring safe rollback. If your SQL Server hosts databases for multiple applications, each set of databases will be migrated with the application in testing (as opposed to migrating all databases at once).

This is the safest way to migrate, because it contains changes when it comes to application testing, and ensures safe rollback.

Think Long-Term with Your Upgrade Project

First off, we do not recommend an upgrade in place solution, where the potential rollback includes uninstalling programs in Windows Control Panel or reverting a VM image. You probably wouldn't do an in-place upgrade for your home PC operating system, so don't try it with your mission-critical SQL Server.

Since SQL Server 2016 and above require at least Windows Server 2012 operating system, now is the time to also upgrade the operating system and server virtualization/hardware. Take this opportunity—along with the scrutiny, outage, maintenance windows, and testing resources that comes with it—to upgrade the server for the long-haul.

Upgrading the server allows for many advantages before and after the migration outage. Prior to the upgrade, we can take our time configuring, updating settings, and performance testing the new server while normal operation continues. We can restore the old databases up to the new server many times, testing them and rehearsing our runbook. By leaving the old server untouched, we retain it for quick and easy reference throughout our preparation phase, and also for failback at the time of migration. By using DNS aliases or Availability Group listeners, we can redirect application connection strings to the new SQL Server easily for a short, controlled outage. If there's an issue, we redirect application connection strings back to the untouched old SQL Server.

SQL Server is Built with Backwards Compatibility In Mind

SQL Server has released 4 major versions since the 2010 release of SQL Server 2008 R2, and each one has aggressively added features while maintaining a loyalty to reverse compatibility. There are no changes to SQL Server since 2005 that break syntax or stop existing code from working. There are major performance improvements in new versions which are very welcomed and certainly beneficial.

In some edge cases, clients reported that only most of their code worked faster after upgrading to SQL 2014. (Technical aside—I have had 2 clients since 2014 experience some query performance regression which was identified and addressed quickly. Given adequate preparation and knowledge, these issues can be identified and addressed before migration, and are not significant hurdles. I mention the SQL 2014 performance issues only as an honest but rare caveat; but otherwise, the enhanced cardinality estimator that was released in that version was a major improvement.)

The point of mentioning the incremental feature improvements and Microsoft's stubborn loyalty to prior compatibility levels is to assure you that upgrading from SQL 2008/2008 R2 to SQL Server 2017 is safe and recommended. There is no need to incrementally upgrade to 2012, 2014 or 2016. Upgrading straight to SQL Server 2017 – released over a year ago and patched monthly – can be successful with no code changes.

While SQL 2005 compatibility mode hasn't been supported since 2012, SQL 2008 compatibility mode has been supported and will be supported through SQL 2019.

We Know Cost Savings are a Project Driver

The biggest reason that many environments stayed at SQL 2008/2008 R2 is for the old server-based licensing model. Starting with SQL Server 2012, Microsoft moved to charging core licensing, meaning that extremely high CPU-core machines became prohibitively expensive while 4-core boxes likely did not see an increase in licensing cost.

There are many ways to save money on SQL Server licensing when moving up from SQL 2008/2008 R2:

• Server workload upgrade and consolidation, taking advantage of many performance improvements to run more data on less hardware

• Move and upgrade to Azure VM’s with built-in SQL Server licensing costs

• Move workloads to Azure Managed Instance, a distinct new cloud offering available only since Q4’18

• Migrate to Azure VM’s running SQL Server 2008 and 2008 R2 – which will receive free additional years of Extended Security Updates

*That’s right – Extended Security Updates will be made available, but at a new and additional cost. If you have Software Assurance or an Enterprise Agreement, you can stay on SQL Server and Windows Server 2008 and 2008 R2 for 3 more years by paying 75% of the cost of the latest version with the core licensing model. This is not exactly an affordable option, as you’d be paying near-new price for 9-year old software.

Highlights of New Features in Each Version Since SQL 2008 R2

Be sure to ask your DBA and/or the Sparkhound SQL DBA team about the following features:

SQL 2012 (11.0)

• Introduction of Availability Groups: High Availability, Disaster Recovery, and Online readable secondary replicas

• Generational improvements to how SSIS packages are designed and deployed with the new Project deployment model

• Introduction of Columnstore indexes, a crucial and easy-to-implement performance boost for reporting large data sets

SQL 2014 (12.0)

• Enhancements further stabilize and improve performance of Availability Groups

• In-memory table engine introduced for ultra-fast writes

• Columnstore indexes are further enhanced, now writeable when the clustered index

• Native backup encryption, native backup to Azure blob store

• Many performance improvements including the new cardinality estimator

SQL 2016 (13.0)

• With 2016 SP1, many Enterprise features were moved down to Standard edition, lowering the cost for some key deployments, potentially reducing the need to buy Enterprise edition

• Columnstore indexes are further enhanced, now writeable in all forms

• Built-in AT TIME ZONE syntax for easy datetime time zone conversion

• The Query Store feature allows developers to have easy, detailed reporting on the worst-performing and most resource-consuming queries

• New security features for regulated environments including Dynamic Data Masking, Always Encrypted, and Row Level Security

• Native JSON processing in the SQL engine

• Better tooling and separate development cycles for Data Tools and Management Studio

• Huge improvements for Reporting Services and incremental integration with PowerBI

• Polybase query engine to allow querying of Hadoop and Azure Blob files

• Introduction of built-in Machine Learning services, the only commercial DB platform with this feature

SQL 2017 (14.0)

• Huge improvements and new features for built-in Maintenance Plans, Availability Groups, index maintenance, performance of Columnstore indexes

• SQL Server on Linux

• Availability Groups get a clusterless option, full support for Distributed Transactions

• New syntax additions prevent reinvention of the wheel for common string manipulation and more

• Automatic plan correction feature for no-admin performance tuning pulled down from Azure SQL

• New native graph database capabilities with SQL Graph

• Much more info in Microsoft Press publication SQL Server 2017 Administration Inside Out

SQL 2019 (15.0)

Due out in 2019 (Summer?)

• Create a live alternative to ETL for heterogenous data systems with Polybase external tables, to natively query data in Cosmos, SAP HANA, Teradata, Apache Spark, Mongo DB, Oracle, HDFS, DB2, or any ODBC connection.

• SQL Server in Docker or Kubernetes, including Availability Groups across containers

• Always Encrypted enhanced with addition of secure enclaves to restrict data in SQL Server to application use only for regulated data environments

• SQL Configuration Manager has been enhanced with a new certificate management feature

• A lot more…

• Much more info coming in Microsoft Press publication SQL Server 2019 Administration Inside Out, due in 2019

What Is the Next Step?

Sparkhound’s SQL DBA team has extensive experience with upgrades and migrations, and a detailed template checklist runbook for planning, prep, execution, and testing. We’d be happy to share and review your upgrade/migration plan, or help you develop your upgrade/migration plan from scratch. Reach out to us for a quick planning meeting, a review of our migration planning documentation, and an explainer on any new features.

Information and material in our blog posts are provided "as is" with no warranties either expressed or implied. Each post is an individual expression of our Sparkies. Should you identify any such content that is harmful, malicious, sensitive or unnecessary, please contact  marketing@sparkhound.com