Upgrade Assistant Tool for SQL Server 2012 (Ch.1 of 14)
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
This article is part of the serie on the SQL Server Upgrade Assistant:
[[Upgrade Assistant Tool for SQL Server 2012]]1.0 Introduction
[[SQL Server Upgrade Assistant-Upgrade Assistant Process|1.1 Upgrade Assistant Process]]
[[SQL Server Upgrade Assistant-Upgrade Assistant Results|1.2 Upgrade Assistant Results]]
[[SQL Server Upgrade Assistant-Required Knowledge and Skills|1.3 Required Knowledge and Skills]]
[[SQL Server Upgrade Assistant-System Requirements|2.0 System Requirements]]
[[SQL Server Upgrade Assistant-Create a Test Environment|3.0 Create a Test Environment]]
[[SQL Server Upgrade Assistant-Create a Single Computer Test Environment|3.1 Single Computer Test Environment]]
[[SQL Server Upgrade Assistant-Create a Multiple Computer Test Environment|3.2 Multiple Computer Test Environment]]
[[SQL Server Upgrade Assistant-Installing SQL Server Upgrade Assistant|3.3 Install SQL Server Upgrade Assistant]]
[[SQL Server Upgrade Assistant-Capturing a Test Workload|4.0 Capture a Test Workload]]
[[SQL Server Upgrade Assistant-SQL Server 2005/2008 Baseline System|5.0 SQL Server 2005/2008 Baseline System]]
[[SQL Server Upgrade Assistant-Run Upgrade Advisor|6.0 Run Upgrade Advisor]]
[[SQL Server Upgrade Assistant-Replay Baseline Trace on SQL Server 2005/2008|7.0 Replay Baseline Trace on SQL Server 2005/2008]]
[[SQL Server Upgrade Assistant-Set Up Playback Test System|8.0 Set Up Playback Test System]]
[[SQL Server Upgrade Assistant-Replay Trace on SQL Server Code Name Denali|9.0 Replay Trace on SQL Server 2012]]
[[SQL Server Upgrade Assistant-Compare Trace Files|10.0 Compare Trace Files]]
[[SQL Server Upgrade Assistant-Advanced Topics|11.0 Advanced Topics]]
[[SQL Server Upgrade Assistant-Fixing Differences|12.0 Fixing Differences]]
[[SQL Server Upgrade Assistant-Troubleshooting|13.0 Troubleshooting]]
[[SQL Server Upgrade Assistant-Support|14.0 Support]]
Return to [[SQL Server 2012 Upgrade and Application Compatibility]]
** **