Upgrade Assistant for SQL Server (Ch.6 of 14): Run Upgrade Advisor
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]]
This chapter contains:
6.0 Run Upgrade Advisor
Upgrade Advisor analyzes the objects in your SQL Server database, and generates a report that identifies issues to address to ensure a successful upgrade. Typical issues are deprecated or changes TSQL syntax and deprecated SQL Server features that your application is using. Upgrade advisor scans your database schema, database code – stored procedures, functions, triggers, etc. – and trace files for things that will cause issues when the database is upgraded. Be sure to include the playback trace file in the input to upgrade advisor so you can be sure that the code that will be executed for the Upgrade Assistant tests is ready for the upgrade process. If you find that there are application issues that must be fixed in the application – the application using the wrong TSQL syntax for example – you will need to rerun the playback capture with the new application code before doing the upgrade.
Note that many of the issues that the Upgrade Advisor finds can be detoured by setting the COMPATABILITY LEVEL to an earlier version. This will cause SQL Server to interpret TSQL code as a previous version of SQL Server would. While this may be necessary if you don't have access to the code to change the TSQL syntax, you should always strive to make the application work with the COMPATABILITY LEVEL set to 110. This will allow your applications to take full advantage of SQL Server 2012 and make the next upgrade go much easier.
**Important: You must fix these issues before you proceed to the next step in Upgrade Assistant. **Upgrade Assistant was created to work in conjunction with Upgrade Advisor, and each utility focuses on different tasks.
Run Location
If you are using multiple computers for the test, run Upgrade Advisor on the baseline server. Database issues identified on the baseline server must also be fixed on the test server before you upgrade. For more information, see [[SQL Server Upgrade Assistant-Create a Multiple Computer Test Environment|Multiple Computer Test Environment]]
Once you have fixed any issues that Upgrade Advisor identified, you are ready to run the baseline trace before the upgrade: [[SQL Server Upgrade Assistant-Replay Baseline Trace on SQL Server 2005/2008|Replay Baseline Trace on SQL Server 2005/2008]]