How to Use the Microsoft SQL Server 2008 Upgrade Advisor Tool
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 SettingsNTUserNameMy DocumentsSQL Server 2008 Upgrade Advisor ReportsSERVERNAME”.
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.
The Microsoft SQL Server 2008 Upgrade Advisor is a great tool which is freely available to Database Administrators. This tool identifies most of the potential upgrade blockers on your existing SQL Server instances and can prevent you from a smooth 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. I strongly recommend using this tool in the project planning stage to avoid any last minute surprises during the actual upgrade.