Joey on SQL Server

An Easy Way To Modernize Your SQL Server Environment

There used to be few good options for a DBA with a bunch of systems stuck on SQL Server 2008. But thanks to one easy trick, businesses on aging infrastructure can take advantage of Microsoft's modern data platform.

• By Joey D'Antoni
• 11/07/2018

Typically in this column, I tell you a lot about new and exciting features that are arriving to SQL Server and the broader Microsoft data platform. To take advantage of these new features, nearly always, you need to be on the latest release of SQL Server or maybe one version back.

A very recent of example of this involves the infamous SQL Server message 8152 error:

Msg 8152, Level 16, State 30, Line 13
String or binary data would be truncated.
The statement has been terminated.

If you've worked with SQL Server for any period of time, you have likely seen this error. It is effectively useless at helping you troubleshoot what value caused the error. Thankfully, our friends on Microsoft's Tiger Team were aware of how painful this was to developers and implemented a fix in SQL Server 2017 cumulative update 12 (which is also coming soon to SQL Server 2016) that returns to you the table name, column and offending data in question. With this new fix, you would see:

Msg 2628, Level 16, State 1, Line 30
String or binary data would be truncated in table 'AdventureWorks2016CTP3.dbo.myTable', column 'myString'. Truncated value: Long String of boring text '.

Note that you will need to use trace flag 460 to enable this messaging (Microsoft didn't want to break existing applications that expected this message).

Here's the catch: If you are still running SQL Server 2014 or older, you will likely never see this fix back-ported into your database engine.

While I used this error as an example because it's been a problem for so long, there are numerous other features that have been recently introduced to SQL Server that are only on the latest releases of the platform. A few examples include the Query Store, automatic tuning and adaptive query processing.

You are probably reading this and saying, "We have a bunch of legacy apps from small software vendors who won't support SQL Server 2012, much less a preview release of SQL Server 2019. Am I ever going to get to play with these new toys?"

Well, this is where the one easy trick comes in. At this fall's Ignite conference, Microsoft announced full support for long-term use of the compatibility level option. Compatibility level is a database property that determines not only functional behavior, but also query optimizer behavior for a given database. Traditionally, this property has been used as a stop-gap measure while upgrading a database engine version, used for a short time, before moving to the latest level.

Moving to the latest compatibility level should always be a goal for your application. There are many performance enhancements in nearly every new release that are protected by the compatibility level. However, this may not be possible for all applications, particularly third-party applications where you do not own the source code.

The reason for this is that a major risk of upgrading your SQL Server is your query execution plans changing for the worse and degrading your application performance. Now, Microsoft has invested in technologies such as auto-tuning to mitigate such risks, but it also acknowledges that customers may need to stay on an older compatibility level for the life of their legacy application. This means if you see plan changes, you can file a support ticket with Microsoft Support to work with the customer to isolate the root cause and resolve it. Additionally, Microsoft will help vendors move their applications to newer releases of SQL Server.

Microsoft's new take on database compatibility level has led to changes in the product's documentation, namely on Compatibility Levels and SQL Server Upgrades, Using Compatibility Level for Backward Compatibility and Best Practices for upgrading Database Compatibility Level. Here, Microsoft deems compatibility level to be "a valuable tool to assist in database modernization, by allowing the SQL Server Database Engine to be upgraded, while keeping connecting applications [in] functional status by maintaining the same pre-upgrade database compatibility level. As long as the application does not need to leverage enhancements that are only available in a higher database compatibility level, it is a valid approach to upgrade the SQL Server Database Engine and maintain the previous database compatibility level."

It also adds, "For new development work, or when an existing application requires use of new features, as well as performance improvements done in the query optimizer space, plan to upgrade the database compatibility level to the latest available in SQL Server, and certify your application to work with that compatibility level."

It's a clear change from not so long ago, which means Microsoft realizes the unspoken hero feature of SQL Server is backward-compatibility, and has invested in supporting that stance to mitigate upgrade risks.

While this is a great change from Microsoft, you still have to deal with independent software vendors (ISVs), many of whom will support newer releases of SQL Server, but only if you upgrade the version of your application software. A good example of this is SharePoint Server 2013, which explicitly only supports up to SQL Server 2014. If you wanted to move to a more modern release of SQL Server and have support from Microsoft for SharePoint, you would need to upgrade to SharePoint Server 2016.

While RDBMS upgrades are typically handled by the infrastructure and DBA groups of an IT organization, application software upgrades are more frequently handled by the business unit that owns the application, which usually increases the amount of scrutiny. If the business has heavily customized the application, this can make the app-upgrade process more challenging.

If you're a DBA who has a bunch of systems stuck on SQL Server 2008, what are you to do? The first thing you should do is discuss this new Microsoft position with your management, as that will be the first hurdle to cross before going down the path of a modernization project. Second, you should contact your key software vendors about both their support for newer SQL Server releases, as well as their stance on using compatibility level for their applications.

There is also a certain level of risk tolerance you have to balance with your management. Is the risk higher to be running an unsupported and unpatched release of your RDBMS, or to be running in a semi-supported format with your application vendor? If you are on an unsupported version of SQL Server when another vulnerability like Spectre or Meltdown happens, your data will be at risk.

The legitimate risk of upgrading to a new SQL Server version is performance degradation due to optimizer changes. However, with the use of compatibility level and the position of Microsoft support, this risk is mitigated. For the most part, I lean toward having a supported RDBMS platform because of its larger surface area for risk.

There are many reasons to not run your systems on software infrastructure that was built over a decade ago. For example, the Query Store feature introduced in SQL Server 2016 has saved me hours of performance troubleshooting time with customers. I've been able to identify a critical performance issue within about 15 minutes and get a resolution seconds later.

Moving to newer software is always balanced against the tolerance for risk and the nature of your business. Many organizations -- especially in the financial and health care sectors -- face a high degree of regulation which limits their flexibility. However, if you are in a position where you can influence these decisions, you now have Microsoft's support to help you move your applications to a more modern data platform.