This article is part of the serie on the SQL Server Upgrade Assistant:

Upgrade Assistant Tool for SQL Server 20121.0 Introduction

   1.1 Upgrade Assistant Process 

   1.2 Upgrade Assistant Results

   1.3 Required Knowledge and Skills

2.0 System Requirements

3.0 Create a Test Environment 

   3.1 Single Computer Test Environment 

   3.2 Multiple Computer Test Environment 

   3.3 Install SQL Server Upgrade Assistant 

4.0 Capture a Test Workload

5.0 SQL Server 2005/2008 Baseline System

6.0 Run Upgrade Advisor

7.0 Replay Baseline Trace on SQL Server 2005/2008

8.0 Set Up Playback Test System

9.0 Replay Trace on SQL Server 2012

10.0 Compare Trace Files

11.0 Advanced Topics

12.0 Fixing Differences

13.0 Troubleshooting 

14.0 Support  


This chapter contains:


1.1 Overview of Application Compatibility Testing Process

Application compatibility testing using Upgrade Assistant is simple provided you prepare your test environment correctly in advance. The following summarizes the key steps involved:

  1. Prepare test environment – set up test computers with necessary software and appropriate configuration.  You should never conduct application compatibility testing using a production environment. Depending on the amount of load you want to subject your test systems to, you will also need one or mare computers to run the Distributed Replay software to replay the test load on the database servers. 
  2. Capture test workload – backup relevant databases and capture a representative trace of your application’s workload that will be used in testing.  This workload is usually captured from the produciton servers.
  3. Set up a baseline environment – transfer the relevant files and databases to your baseline test environment –  run Upgrade Advisor – review Upgrade Advisor results (analyze database schema, trace files and script files for potential compatibility issues) and develop fixes for any problems discovered.
  4. Replay test workload to establish baseline results – replay the captured trace workload on your test computers and capture relevant execution data and trace files
  5. Upgrade baseline environment – upgrade the baseline SQL Server instance to SQL Server 2012
  6. Replay test workload to establish upgrade results – replay the captured trace workload on your upgrade environment and capture relevant execution data and trace files
  7. Compare results – compare and review data from the baseline and upgrade test workload replays to identify potential compatibility issues.  The differences are presented to you in a report that allows you to navigate through the differences detected, filter out irrelevant differences, and analyze differences to determine if they will require changes to your applications to compensate.

 This process requires some minimal SQL Server skills to execute.  The required skills are listed here:  SQL Server Upgrade Assistant-Required Knowledge and Skills.

Upgrade Assistant automates most of the tasks in each step of your testing. If you follow the instructions provided, your testing should be smooth and productive.  The next step in the process is to review the results produced by the upgrade assistant: [[SQL Server Upgrade Assistant-Upgrade Assistant Results]].

There are several steps which must be performed consistently for each database that is upgraded.  To help track the upgrade process and ensure nothing falls through the cracks, we have developed a couple checklists which can be used to track the upgrade process and the resolution of any differences found by the Upgrade Assistant. Upgrade Assistant Checklists