SQL Server 2016 has been released recently. How do you check if your product is compatible with SQL Server 2016? Here’s the process I usually go through …

Research

I start by doing some research - looking at the discontinued, deprecated, breaking changes and behavioural changes listed by Microsoft and searching the application and database for occurrences. If any occurrences are found, I’ll note them down and include them in the next stage of the process which is testing.

Here are the lists published by Microsoft …

Discontinued Features

• Database Engine
• SSIS (none)
• SSRS
• SSAS

Deprecated Features

• Database Engine
• SSIS (none)
• SSRS
• SSAS

Breaking Changes

• Database Engine
• SSIS (none)
• SSRS
• SSAS

Behavior Changes

• Database Engine: No article found
• SSIS (none)
• SSRS
• SSAS

Upgrade Advisor

I will also run the SQL 2016 Upgrade Advisor on the database. This should identify stuff from the lists above, as well as potential improvements. Again, I’ll note any recommendations down for the testing stage. Here’s the link to the Upgrade Advisor

Hardware and Software Requirements

The last bit of my research is to check the new hardware and software Requirements for SQL 2016. I’ll check these against the 2014 requirements to determine what’s changed. The main changes I’ve found are:

• Required .NET version is now 4.6
• Minimum processor speed is now x64 processor: 1.4 GHz. So, no more x86

I will then update my product’s hardware and software requirements accordingly.

Tests

I’ll then create a test environment with my application running on top of a SQL Server 2016 database. Before I start my testing, I’ll double check the following:

• SQL’s stats are up-to-date: EXEC sp_updatestats
• SQL’s compatibility level is correct (should be 130): SELECT compatibility_level FROM sys.databases WHERE name = @dbname

Problem areas of the app will be noted down and taken to the next stage which is forming a plan the changes.

Testing Discontinued Features, Deprecated Features, Breaking Changes and Behavioural Changes

I’ll start by testing the areas of the app that I noted down from the research - to double check that the app is impacted and to determine how wide spread and severe the impact is.

Automated Testing

I’ll then run the product’s automated UI tests on the SQL Server 2016 database.

Performance Testing

I’ll then carry out performance tests in key areas to compare against our 2014 benchmarks. Usually, performance is better, but if an area is significantly slower, I’ll treat that has a problem that needs resolving.

Testing Potential Improvements

I’ll then go on to testing suggested improvements by the upgrade advisor. Any significant improvements will be noted down and included in the action plan in the next stage.

Action Plan

I’ll then form a plan from the test results. Changes will be logged in our issue management system, prioritised, scheduled and eventually implemented. Compatibility is declared when all the changes have been completed in a release.

What process do you go through when testing your products against new versions of SQL Server?