Upgrade Assistant Tool for SQL Server 2012

Upgrade Assistant Tool for SQL Server 2012

Download Upgrade Assistant for SQL Server

The UAFS code is available for download here:

http://www.scalabilityexperts.com/tools/downloads.html

1.0 Introduction to the Upgrade Assistant for SQL Server

With every new release of SQL Server, Microsoft invests a lot of time and effort ensuring that upgrading from previous versions is as simple and reliable as possible, and that new releases support backward compatibility with applications developed for prior releases.  In most cases you can upgrade a SQL Server instance in place.  You can also perform a side-by-side upgrade by backing up databases running on the previous release, and restoring them to an instance running a newer release.  Any changes to Transact-SQL syntax or usage of deprecated features can usually be found prior to upgrading with a tool called SQL Server Upgrade Advisor.  If you need to emulate the behavior of a previous release, you can set the database compatibility level to match the release that the original database was developed against to keep your applications running until you have time to fix any compatibility issues.  The goal for all applications should be to support the database compatibility level for latest release, which in the case of SQL Server 2012 is database compatibility level “110”.

Because upgrading is so easy, there is a tendency to think that it can be done over a weekend with little effort.  While this may be true for some small, non-mission critical databases, you should never consider upgrading your production servers without a lot of planning, practicing and testing.  This wiki discusses the Upgrade Assistant for SQL Server 2012 tool (Upgrade Assistant or UAFS) which can be a useful addition to your testing plan.

Upgrade Assistant allows you to test the functionality of applications designed for SQL Server 2005, 2008 or 2008 R2 against Microsoft SQL Server 2012. Upgrade Assistant uses workload testing to identify potential compatibility issues that exist in application source code.  These kinds of issues can’t be discovered by just looking at the database and can be difficult, if not impossible to find by just looking at the source code. 

Upgrade Assistant guides you through the steps required to create a test workload on an instance of SQL Server 2005/2008, upgrade the database to SQL Server 2012, and replay that same workload to identify differences in behavior, if any.  In this process, Upgrade Assistant provides automation for backing up and restoring necessary databases, capturing and replaying workload traces and capturing trace results.  Finally, Upgrade Assistant compares the trace results and identifies areas where the test workload replay produced different results on SQL Server 2012.  This trace file comparison is no substitute for complete application testing, but it can help identify differences in the output or behavior of Transact-SQL commands that might cause application problems which your normal tests may not find.  This process is covered in more detail here:  SQL Server Upgrade Assistant-Upgrade Assistant Process.

Due to limitations in the trace replay technology in SQL Server, Upgrade Assistant in its current form is best suited for testing application functionality, not performance.  Upgrade Assistant for SQL Server 2012 incorporates the Distributed Replay feature of SQL Server 2012 to produce a much more realistic load for performance testing.  This allows you to make valid comparisons of performance before and after the upgrade to discover potential performance issues.

Upgrade Assistant should not be confused with SQL Server Upgrade Advisor, which is another useful tool you can use to test an existing SQL Server instance and associated databases for compatibility with SQL Server 2012.  The basic distinction between the two is that Upgrade Assistant uses workload testing to test application behavior, while Upgrade Advisor analyzes your databases in-place for potential compatibility issues. 

This wiki provides prescriptive guidance for how to conduct application compatibility testing with Upgrade Assistant including setting up and running Upgrade Assistant, interpreting the results, and making the changes required to address the issues identified.  The following table of contents contains links to all the pages in this Wiki article. 



Download Upgrade Assistant for  SQL Server

The UAFS code is available for download here: 

 http://www.scalabilityexperts.com/tools/downloads.html

Table of Contents for Upgrade Assistant:

1.0 Introduction (this article)
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


Return to SQL Server 2012 Upgrade and Application Compatibility

Sort by: Published Date | Most Recent | Most Useful
Comments
  • Misspelling on this page "table of cantents"

  • Fixed the "table of cantents" misspelling. Thanks!

  • Suggestions:

    1. Add wiki-wide navigation to the end of every page - non first-timers will probably jump around quite a bit

    2. Provide a consistent transition mechanism - at the minimum, a standard "next page" and "previous page" option at the end of each page (and in the same location). Stretch: direct link to index page at the bottom of each page.

    3. Provide an option to print the entire thing (implies proper formatting) - some users may want to use this offline. Stretch: option to print each page individually. Note that using IE's print page function is not a good solution as that will print all the other stuff users don't care about plus formatting might get messed up also depending on default paper size, frames, etc... (e.g. letter size in US, A4 size in UK, Europe, Asia)

  • Customer has asked for the instructions in a single pdf.  Is this possible please?

  • If you download the UAFS code from the link above, there is a PDF file with all the instructions - what's in the WIKI and also some more detailed instructions

  • You might want to remove some of the MS-installed DTS packages from the UA check.  There look to be 7 for "Repository": PerfCountersCollect, PerfCountersUpload, QueryActivityCollect, etc.  Since they get upgraded when you install 2012, there doesn't seem to be a point to warning about them.

Page 1 of 1 (6 items)