Michael J. Swart

Is your application ready to have its SQL Server upgraded to the next version? It’s important to find out because if you’re ready (and you know it) you can proceed with confidence. But if you’re not ready, you can choose to adjust your application, postpone the upgrade or do both.

But there’s some good news, chances are really good that your application is in fact ready for the next version of SQL Server. Focusing on the database engine I want to explore ways to find out for sure.

First, Inspect Your App for Discontinued Features and Other Changes

Bookmark Microsoft’s docs on the subject: SQL Server Database Engine Backward Compatibility. This is your main resource for your upgrade “certification” effort. Microsoft describes Deprecated and Discontinued features and it also describes breaking and behavior changes. The list is well-written and well-organized. Microsoft has really done well with these.

Look through your own application’s code using a code search or other means and check to see whether your application is affected by these changes. Luckily these lists have always been manageable. Microsoft introduces a lot of features, but they don’t deprecate a lot. It’s in their best interest to maintain backwards compatibility.

Just like breakfast, it’s important not to skip this step, it’s the most important one. I’ve seen this inspection step uncover more potential upgrade issues than any of the following steps.

Test Your App on a Production-like Instance

Now it’s time to test your application with the new version. But that’s easier said than done. You’ll need to:

• Get a database server (easy) whose specs are close to what’s running in prod (less easy).
• Find a database with the same size data as prod (a restored backup of prod if you can swing it).
• The ability to generate a workload with the same volume and variety as seen in prod (really tricky).
• Now upgrade to the next version of SQL Server and test.

Even if you can’t perfectly reproduce production hardware, production data or production workloads, most people expect this kind of testing. For that reason alone, this step shouldn’t be skipped. It gives people confidence.

Use the Deprecated Features Performance Counter

SQL Server provides performance counters that track when it uses a deprecated feature. And because they’re SQL Server performance counters, you can inspect the values from a query window like this:

select * 
from sys.dm_os_performance_counters
where object_name like '%Deprecated Features%'
and cntr_value > 0

These counters get reset when SQL Server is restarted. So look at a production server that has been running for a while.

But this list can be tricky. Even against the cleanest, quietest database server, you’re never going to see an empty result set. There are always a number of “problems” that get reported. And it’s not easy to sort out which ones matter. The list indicates that someone used a deprecated feature of SQL Server and that someone could be:

1. Your application
2. SQL Server system processes
3. Some admin or other user writing ad hoc SQL
4. SQL Server Management Studio

Try to determine which problems come from your application and whether you want to do anything about it.

Upgrade Advisor

The SQL Server Upgrade Advisor is a tool that Microsoft provides to help people when upgrading to a new version. It’s in Microsoft’s interest to make the whole process as painless as possible and they do a pretty good job.

The Upgrade Advisor will also look at Analysis Services, Reporting Services and Integration Services, but you’ll be focusing on the Database Engine. Optionally you can provide a workload in the form of a system trace. It’s a nice tool, but it’s rare that it will catch anything in your app that you haven’t caught already with the previous steps.

Other Considerations

Undocumented Features
Are there any undocumented trace flags that you’re using? If you’re depending on behavior of such trace flags, now’s a time to take a fresh look at what you’re trying to accomplish. The behavior is, by definition, undocumented and can change any time.

Database Compatibility Levels Other Than Latest
I like to avoid any compatibility level that’s not the most current. Mostly this is a supportability thing.

As a vendor, I want to support the fewest number of versions possible. If I begin to entertain different compatibility levels, this multiplies the number of environments I have to support.

But maybe you’re not a vendor. If you have just one single database to support. If you choose to take small steps through compatibility levels and versions, you’re probably doubling the amount of testing required.

Known Issues
If you’re not going to the latest Service Pack and/or Cumulative Update, then you should be aware of the fixes you’re choosing not to take.
It’s actually very difficult to keep up to date on what known issues are out there and which ones are severe enough to worry about. For example, if I only use the SQL Server database engine, I’m not going to be impacted by a DAX performance bug in SSAS, but I do care an awful lot about Data Corruption Bugs.
I like to read what Aaron Bertrand has to say on SQLBlog.com or SQLPerformance.com. He’s a good resource when trying to keep up with known issues in SQL Server.

Unknown Issues
Otherwise known as Avoid-RTM. This advice is less relevant lately and I think it might be for a couple reasons. Just speculating here:

• The cynical part of me says that Microsoft will just wait a little bit and call the CU that month SP1 just to appease the people who are waiting.
• The optimist in me sees that Microsoft lately has a really nice cloud-first deployment model where Azure customers are kicking the tires behind the scenes so that when RTM comes around, parts of the database have seen a lot of production hours they wouldn’t have otherwise. In the past I have been burned by database engine bugs in 2005 and 2008. I haven’t been burned by 2008 R2 or 2012. It’s anecdotal, but it makes me hopeful.

Does Version Matter

Not really, I wrote this post with no particular version in mind.

But I do want to mention one particular point that has to do with SQL Server 2014. The new cardinality estimator really affects the behavior of the database engine. It affects plans chosen by the query optimizer and hence it affects the performance of applications that use it.
This is why I advise everyone to give the upgrade to 2014 a little more scrutiny than an upgrade to previous versions. Look for performance regressions. This is especially true if you or other developers are fond of writing queries with a lot of query hints. I understand that regressions are more common in queries that make heavy use of hints.