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

  • Article

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):

  • SQL Server 2005 (SP4)
  • SQL Server 2008 (SP2)
  • SQL Server 2008 R2

You can your customers can also do side-by-side upgrade.

Because your customers can easily upgrade your application onto SQL Server, you will want to check to be sure your application will not break.

Customers will also want the new features in SQL Server 2012, such as:

  • Always On will provide your customers with straight forward high availability and disaster recovery.
  • Self Service Business Intelligence provides users with power data visualization and reporting tools.
  • Blazing-fast query performance with ColumnStore Index.
  • Write applications once, run most anywhere with SQL Server Data Tools

So you will want to be able to move your customers to move your existing applications to SQL Server 2012.

Review the Changes

There are just a few breaking changes, so it is likely that your application will work as expected. But you should still review your application to spot if it will break.

See Backward Compatibility for the detailed information on SQL Server components including information about deprecated features, discontinued features, breaking changes, and behavior changes.

You will find links to changes in the SQL Server database engine, Analysis Services, Reporting Services, Master Data Services, Integration Services, and database replication.

How to Test Your Application for SQL Server 2012 Compatibility

So as ISVs, you will want to check your code to support SQL Server 2012.

To systematically test your database take these three primary steps:

  1. Check for configuration issues using SQL Server 2008 R2 Best Practices Analyzer.
  2. Use SQL Server 2012 Upgrade Advisor to test for common blockers. Use Upgrade Advisor for Database Engine, Analysis Services, Reporting Services, Integration Services.
  3. Test your data workload at scale using Upgrade Assistant Tool for SQL Server 2012 (UAFS).

Here are the details:

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 tool can be run from PowerShell.

Learn more at Using the Microsoft SQL Server 2008 R2 Best Practice Analyzer.

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.

Use Upgrade Advisor for Database Engine, Analysis Services, Reporting Services, Integration Services.

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.

For More Information

Backward Compatibility provides a landing page for compatibility information for SQL Server components. This content includes information about deprecated features, discontinued features, breaking changes, and behavior changes.

SQL Server 2012 Upgrade and Application Compatibility is the starting point for you and your customers on migrating to SQL Server 2012.

SQL Server 2012 Developer Training Kit Content is designed for developers who want to exploit all the great new improvements in this release.

SQL Server 2012 Early Adoption Cook Book

Get SQL Server 2012 RC0. Click the Download button.

Learn what is new in the Microsoft® SQL Server® code name 'Denali', Community Technology Preview 3 (CTP 3) Product Guide.

 

Bruce D. KyleISV Architect Evangelist | Microsoft Corporation

image