The end of support for SQL Server 2008: What should you consider before upgrade

There are multiple scenarios to consider as we face the end of support for SQL Server 2008. Depending on your specific infrastructure, application architecture, and business needs, your data platform could migrate to something completely different.

But first, a story about SQL Server 2008

Almost 10 years ago, in the end of 2009 I was working as a consultant for a top 5 banking institution. My team had a goal of migrating the application back-end to SQL Server 2008 SP1 released one year before. I was the only database developer to support the back-end side of the application and made most decisions on how to develop new functionality and support code running on outdated SQL Server 2000.

So, when the migration project was announced and the manager asked who wanted to volunteer to become the team lead, I raised my hand. What can be more exciting than upgrading to the newer version? But what seemed like a simple “SQL version upgrade” from the outside was, in reality, an intense, collaborative effort of the entire team on the inside. The initial assessment consisted of more than 50 questions and some strategic decisions that needed to be made. We had to ensure the new database servers could comply with high security standards for banking, strict SLA requirements, COB/DR strategy and so on.

With SQL Server 2008 Microsoft released a long-awaited set of functionalities including change data capture, native backup compression, policy-based management, transparent data encryption and more. Even though we didn’t plan to use all new capabilities right away, we did take advantage of backup compression and new datetime data types. While reviewing the compatibility issues and potential code changes recommended by SQL Server Upgrade Advisor, I came up with the additional list of enhancements that couldn’t be recommended by automated tools – remove database objects and even entire databases that hadn’t been used for a long time or have been deprecated, standardize role-based security, improve account naming conventions etc.

In the end, the project was a success. We not only released a new set of SQL servers fully compliant with the latest security standards, but also saved space by removing deprecated objects, refactored outdated code (some custom-coded functions were longer needed because the functionality became available as a part of SQL Server engine), and ensured we planned capacity for next several years.

And now, the end of SQL Server 2008

Now the era of SQL Server 2008 is almost over. The extended support for both SQL Server 2008 and SQL Server 2008 R2 will end in July 2019. The truth is, there are still companies that rely on SQL Server 2008 to run their production databases. But will it be possible for them to perform upgrades or migration to the newer versions easier than it was several years ago? Let’s think about it.

Between 2000 to 2010 Microsoft released three versions of SQL Server: SQL Server 2000, 2005 and 2008. Thus, most migrations were to the nearest version, i.e. 2000 to 2005 or 2005 to 2008, with the maximum span of two versions (like the one I was part of). Usually the more often the upgrade is made, the smaller the checklist and the number of compatibility issues to address.

In the next decade, the pace of development increased and from 2010 to 2019 there were four versions of SQL Server engine released: SQL Server 2012, 2014, 2016, 2017, and the fifth currently in community preview (CTP) and coming out before the end of 2019.

Therefore, the very first decisions to be made are (1) which version to go with and (2) how it should be done – through in-place upgrade, migration, or rolling upgrade.

Choosing your version

The choice of the version is not as complicated as it may look. Strategically thinking, it doesn’t make sense to migrate to either SQL Server 2012 or SQL Server 2014. End of extended support for SQL Server 2012 is in about 2.5 years, and SQL Server 2014 is also a 5-year-old technology. That means it’s about halfway toward being retired.

The more attractive options would be either upgrading or migrating to SQL Server 2016 and SQL Server 2017. Both versions have minimal differences in features and already reached certain level of maturity in terms of security updates and service packs (it’s worth mentioning the concept of service pack is no longer relevant for 2017 version because it was switched to cumulative updates). Moreover, SQL Server 2017 can run both on Windows and Linux, and that choice makes a difference. It opens the door for additional options to consider.

The preference for the type of SQL Server upgrade is driven by the affordable downtime and risk. It also depends on the features used in the source installation and the features needed to be kept and added in the target installation. In-place upgrade where the existing hardware and OS are used to run upgraded version may not be the right choice long-term.

Many installations of SQL Server 2008/2008 R2 were placed on Windows Server 2008/2008 R2, which is less than one year away from its end of extended support and must be upgraded as well. By contrast, Windows Server 2012 is still supported until October 2023 and provides the opportunity for in-place upgrade of SQL Server instances running on this OS.

Image source: Microsoft blog

Rolling upgrade vs. new installation

The rolling upgrade is the solution designed for servers that are part of the same configuration and need to be upgraded one-by-one to reduce downtime. Based on the suggested above scenario to move three (SQL 2016) to four (SQL 2017) releases ahead, the upgraded and not-upgraded nodes may experience compatibility issues due to differences in SQL Server engine and SQL Server Native Client. Therefore, the migration to new installation, instead of rolling upgrade, might be the better choice in some cases.

Another advantage of the migration to completely new installation is the modernization toward high availability. SQL Server 2008 doesn’t support Always On technology, which became the standard for building reliable and fault tolerant RDBMS environments since the release of version 2012. However, SQL Server 2016 and 2017 not only offer HA on Standard edition, but also allow building hybrid environments where initially one primary node runs on-premises and secondary DR node(s) are placed to the cloud.

The fully-managed cloud deployment for SQL Server provides several advantages such as reducing time spent on operational support and cost savings by changing resource utilization in response to business demand. In addition to that, cloud migration can be a good starting point to make the installation consistently compliant with the latest security standards and best practices.

Final considerations

As we explored just the tip of the iceberg, it is already clear there are multiple scenarios to be considered depending on each specific infrastructure and application architecture and business needs. The upgrades didn’t become easier in preparation, but more flexible with multiple choices in each situation. Microsoft made great progress in reducing the range of editions for SQL Server 2017, while increasing the variety of assessment and testing tools to simplify the migration decisions and mitigate the risks of incompatibility. With the increased frequency of releases and advanced capabilities like AI built inside SQL Server engine, the future upgrades have the promise to be more agile and customer specific than ever before.