How to Use the Microsoft SQL Server 2008 Upgrade Advisor Tool
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.