Microsoft SQL Server 2008 Upgrade Advisor Tool

Features of SQL Server 2008 Upgrade Advisor

Microsoft SQL Server 2008 Upgrade Advisor helps database administrators analyze the existing instances of SQL Server 2000 and SQL Server 2005 databases. It identifies features and configuration changes that might affect your successful upgrade to SQL Server 2008. Once the tool does the entire analysis, it documents all the upgrade issues along with providing a link for issue resolution. The tool also identifies all potential upgrade blockers that might occur during or after the upgrade without actually performing the actual upgrade. Database Administrators should utilize this wonderful tool during the upgrade planning phase of the project to analyze SQL Server 2000 and SQL Server 2005 instances. This tool can analyze different components of SQL Server such as SQL Server, Analysis Services, Notification Services, Reporting Services, Data Transformation Services, Integration Services, Scripts and SQL Trace Files. I strongly recommend using this tool in advance to avoid any last minute surprises during the actual upgrade.

How Upgrade Advisor Internally Works

The Upgrade Advisor tool internally utilizes inbuilt rules to identify the potential upgrade blockers. The inbuilt rules are nothing but scenarios which could stop customers from upgrading from the existing version of SQL Server to a higher version. The results after the analysis are logged in an XML file, which can be viewed by using the Upgrade Advisor Report Viewer.

Prerequisites for Installing and Running Upgrade Advisor:
· The .Net Framework 2.0 or a later version
· Windows Installer 4.5 or a later version
· Windows Server 2003 SP 1 or a later version, Windows Server 2008, Windows XP SP2 or a later version, Windows Vista

Using SQL Server 2008 Upgrade Advisor Tool

After the successful installation of SSUA you can access it from Start menu - Click Start --> All Programs --> Microsoft SQL Server 2008 --> SQL Server 2008 Upgrade Advisor.

On the “Welcome to SQL Server 2008 Upgrade Advisor” screen there are three options namely Read more about Upgrade Advisor, Check for updates and Read more about Upgrade Issues. I advise you to check for latest updates before using the tool, as Microsoft keep adding new rules and functionality to the tool to provide better customer experience while upgrade to a higher versions of SQL Server.

In the same screen you can see two more links to the wizards namely “Launch Upgrade Advisor Analysis Wizard” and “Launch Upgrade Advisor Report Viewer”. The “Launch Upgrade Advisor Report Viewer” will launch the report viewer from where you can see reports which were earlier generated by this tool in the previous analysis. You need to click on Launch Upgrade Advisor Analysis Wizard option to launch the Microsoft SQL Server 2008 Upgrade Advisor Analysis Wizard as shown in the below snippet.

Click Next to see SQL Server Components screen where you will be asked to select the SQL Server Components which you want to analyze using this tool. In the Server Name textbox provide the name of the SQL Server which needs to be analyzed.

Once the Server Name is provided, you can either select the components manually or click the Detect button. The detect functionality will automatically detect the SQL Server Components which are installed on the SQL Server that needs to be checked for upgrade issues. In case you want to analyze a server which has multiple instances, then you have to specify just the name of the server in the server name box and select the components manually or allow the tool to detect by clicking Detect button. Once the components are identified click Next to see the Connection Parameters screen.

In the Connection Parameters screen select the Instance Name from the dropdownlist (for this article default instance of SQL Server 2005 is used by this tool for analysis) against which you want to do the analysis. In the Authentication screen you can either select Windows Authentication or SQL Server Authentication. In the above snippet you can see that I have selected SQL Authentication and I have provided the Username and Password to establish the connection. Click Next to see the SQL Server Parameters screen.

In the SQL Server Parameters screen you need to enter the parameters for SQL Server analysis. You can check box All Databases if you want to analysis issues for all the databases present on the SQL Server Instance. Else you can check only those databases against which you want to perform the analysis. It is always a good practice to choose All Databases option when you are planning to do an in-place upgrade. There are two other options like Analyze trace files and Analyze SQL batch file. If you have any you can check the options and provide the path of the files for the analysis. You can also run trace files against Upgrade Advisor. This way you will be able to analyze any adhoc query getting executed from the applications which uses SQL Server. The recommended SQL profile template is SQLProfilerTSQL_Replay, as this will have unique number of queries. Click Next to see DTS Parameters screen.

In the DTS Parameters screen you have radio buttons to choose the location of DTS packages. You can either analysis all the DTS packages on the SQL Server or you can analyze DTS packages which are stored as file. If you want to analyze the DTS packages stored as files then you need to select the second option and provide the path of the DTS package files. Click Next to see the SSIS Parameters screen.

In the SSIS Parameters screen you have radio buttons to choose the location of SQL Server Integration Services (SSIS) packages. You can either analysis all the SSIS packages on the SQL Server or you can analyze SSIS packages which are stored as file. If you want to analyze the SSIS packages stored as files then you need to select the second option and provide the path of the SSIS package files. If your SSIS packages are encrypted then you also need to specify the Password for the package and then Click Next to see the Confirm Upgrade Advisor Settings screen.

In the Confirm Upgrade Advisor Settings screen you can see the summary of all the options which you have selected in the previous screens. Finally click on Run button to capture the analysis using the Upgrade Advisor Tool.

Once you click on Run you will be redirected to Upgrade Advisor Progress screen. You could see in the above snippet that when analysing SQL Server it’s basically running 103 rules. Rules are nothing but scenarios according to Microsoft which will prevent you from successful upgrade. Once the analysis has completed you will see the below screen.
In order to see the detailed analysis report you need to click on Launch Report button which will open up Microsoft SQL Server 2008 Upgrade Advisor Report Viewer as shown in the below snippet. The results of the analysis are basically stored in an XML file in the following folder location “C:\Documents and Settings\NTUserName\My Documents\SQL Server 2008 Upgrade Advisor Reports\SERVERNAME\”.

From the Instance or component dropdownlist you can choose components like SQL Server, Database Transformation Services, and Integration Services etc based on the selections which you have done before in the SQL Server Selections screen. The When to Fix column within the report mentions the stage of upgrade in which the issue needs to be fixed. The options under “When to Fix” are Before, After or Advisory. The one which are classified to be in “Before” category are very important issues as they are upgrade blockers and it needs to be fixed before you starts the upgrade of SQL Server. The issues which are classified to be in “After” category can be fixed after the upgrade of SQL Server as they are of Medium priority. The issues which are classified to be in “Advisory” category are informative messages. The issues which are in “After” and “Advisory” category should not be ignored by the DBA even though they won’t affect the Upgrade. They basically inform you that your server is not configured as per best practices suggested by Microsoft. You can double click on each and every issue to know the steps for the issue resolution. Once you double click an issue you will see the below screen.

Click on “Show affected objects” to see the list of objects which are affected and needs to be fixed. Similarly if you are not sure about how to resolve the issues identified then you can click on “Tell me more about this issue and how to resolve it” link to see the steps to be followed for issue resolution.

Hope this helps!!