Guide for How to Test Your Application for Compatibility with SQL Server 2012

ISVs are getting ready for the release of SQL Server 2012 by checking code to be sure it will support the new release. Microsoft provides tools that will help you in your testing.

This article provides the steps you can take to be sure your application is customer-ready for SQL Server 2012.

Customer Expectations

For your customers, upgrading to SQL Server 2012 may be as simple as upgrading a couple of instances for a small company or department, but large companies might have hundreds of SQL Server installations, some of which the IT department may not even be aware of. Customers will use tools such as the Microsoft Assessment and Planning Toolkit (also known as the MAP Toolkit) to find applications to be upgraded.

Your customers can even upgrade to SQL Server 2012 in place. In-place upgrade are supported for the following versions of SQL Server (note minimum required service pack level):

Test Your SQL Installs For Best Practices

Your Database Admins use the SQL Server 2008 R2 Best Practices Analyzer to scan your instance and databases for potential violations of best practices. ISVs can use it to check that your installations support best practices out-of-the-box. It's a good idea to clean these up before doing an upgrade. You should also check to see if you might have left your instance with any special trace flags and whether you still need them.

Might even be a good idea to make this part of your build process for each check in to be sure the SQL you are shipping complies with best practices out of the box.

The Microsoft SQL Server 2008 R2 BPA is a diagnostic tool that performs the following functions:

Gathers information about a Server and a Microsoft SQL Server 2008 or 2008 R2 instance installed on that Server

Determines if the configurations are set according to the recommended best practices

Reports on all configurations, indicating settings that differ from recommendations

Indicates potential problems in the installed instance of SQL Server

Recommends solutions to potential problems

Run Upgrade Advisor

Next, you should scan the instance and all of its databases for potential upgrade blockers using SQL Server 2012 Upgrade Advisor . This tool runs a set of rule-based checks to identify common upgrade blockers, and produces a nice actionable report as its output.

You should then systematically address all of the rule violations before attempting to upgrade. It's important to note that Upgrade Advisor uses static analysis, and will not detect issues that are hidden in application source code or that may only occur at runtime.

When you run Upgrade Advisor, the Upgrade Advisor Home page appears. From the Home page, you can run the following tools:

Upgrade Advisor Analysis Wizard

Upgrade Advisor Report Viewer

Upgrade Advisor Help

The first time that you use Upgrade Advisor, run the Upgrade Advisor Analysis Wizard to analyze SQL Server components. When the wizard finishes the analysis, view the resulting reports in the Upgrade Advisor Report Viewer. Each report provides links to information in Upgrade Advisor Help that will help you fix or reduce the effect of the known issues.

The reports might contain an "other upgrade issues" item. This item links to a list of issues that are not detected by Upgrade Advisor, but might exist on your server or in your applications.

Perform AppCompat Testing

The final step is to use the Upgrade Assistant Tool for SQL Server 2012 (UAFS) to perform AppCompat testing for you applications. UAFS allows you to capture the interaction between your application and SQL Server and save it as a test workload. You then replay the test workload against the original SQL Server version to establish a baseline, and then again against SQL Server 2012.

The output of these two workload replays is then systemically examined for differences. In some cases, differences may be the result of an upgrade blocker that must be addressed in your application source code.

NOTE: AppCompat testing with UAFS is only as good as the test workload. If your workload covers most of the different types of interaction that your application has with SQL Server, it should be fairly comprehensive. But if you only test a "hello world" workload, you won't get much useful information out of it.