SQL Server 2005 Upgrade Advisor

Friday Mar 31st 2006 by Gregory A. Larsen

Before you start migrating your existing applications to SQL Server, you should consider looking into what the SQL Server 2005 Upgrade Advisor can do for you. Greg Larsen discusses what the Upgrade Advisor is and how to use it.

Have you started looking at what issues you might have migrating your existing applications to SQL Server 2005? If not then maybe as a first step, you should consider looking into what the SQL Server 2005 Upgrade Advisor can do for you. This article will discuss what the Upgrade Advisor is and how to use it.

What is the Upgrade Advisor?

The SQL Server 2005 Upgrade Advisor is a downloadable tool from Microsoft. This tool can be used to assess if your SQL Server 7.0 and 2000 environments are ready to be migrated to SQL Server 2005. The Upgrade Advisor examines your existing SQL Server environments to determine if there are any issues that would prevent your environment for being successfully upgraded to SQL Server 2005. The advisor is an easy to use wizard that does not update your existing environment, but instead only scans it and reports issues. This allows you to examine the reports and then plan appropriately to make sure your 7.0 and 2000 components are migrated successfully.

As previously stated the Upgrade Advisor is a downloadable tool. Since Microsoft first introduced the Upgrade Advisor, they have come out with a number of newer versions. I would suggest you download the latest version of the Upgrade Advisor, and then periodically check back and download any new versions as you move forward with your migration to SQL Server 2005. Here is a link where you can download the newest version of SQL Server 2005 Upgrade Advisor:


What SQL Server Components Does the Upgrade Advisor Scan?

The Upgrade Advisor can scan the following SQL Server components: Analysis Services, Reporting Services, Notification Services, Data Transformation Services, and instances of SQL Server. The Upgrade Advisor has the capability to detect what services are running on a given Server. Understand there are some caveats to getting the Upgrade Advisor to scan some services. These caveats are documented in the "SQL Server Components" section of the SQL Server 2005 Upgrade Advisor documentation.

Using the Upgrade Advisor

When you first bring up the upgrade advisor, the following window will be displayed:

Click for larger image

You can start the analyze process by clicking on the "Launch Upgrade Advisor Analysis Wizard" link near the bottom of this window. This window is also used to view reports generated by the Upgrade Wizard. This window also provides you a link to the Upgrade Advisor online documentation. To get to the online help documentation use one on the "Read …" links on this window. If you want to read about the different issues that the Upgrade Wizards scan for, then you should look in the "Resolving Upgrade Issues" section of the online documentation. This window also provides a link to check and see if there are new versions of the Upgrade Advisor.

After you launch the Upgrade Advisor wizard and get past another welcome window, you will get to a window that looks like this:

On this window, you can specify the server you want to have the Upgrade Wizard run against, as well as the components you want the wizard to scan. On the other hand, if you are not exactly sure what components are on any given server, then you can also let the Upgrade Wizard detect which components are available, by clicking on the "Detect" button. Once you have checked the components to analyze, the "Next>" button will be available and you can click on it to proceed to the next window of the wizard.

On the next window, you will identify how you want to authenticate to the instance you are scanning. Once you have entered your connection information and clicked on the 'Next' button the following window will be displayed:

On this window, you select the databases, as well as any trace files, or SQL batch files you want to scan. Note the documentation for the Upgrade Advisor says some upgrade issues are only found in trace files and/or batch files. So make sure if you have batch files, or traces, that you remember to include these objects in your scans.

Once you have specified what needs to be scanned you click the "Next>" button. Doing this will display a window where you can confirm the setting for your scan. If you are satisfied with your settings, you can then click on the "Run" button to start the scan. When the scan starts, the following progress window will be displayed.

Depending on what you have decided to scan, your scan could take a considerable amount of time. Here you can see I am scanning a SQL Server instance and my DTS packages. When the scan completes you need to review the statuses and messages for of each action. Once the analysis has been completed, the grayed-out "Close" button will be available. Clicking on this button will take you back to the welcome window, where you can view the upgrade advisor report.

Viewing Upgrade Advisor Reports

The Upgrade Advisor creates a directory under the "My Documents" folder to store upgrade advisor reporting information. For each server analyzed there is a different folder created. Only a single set of report information is retained for a given server instance. Each time you run the Upgrade Advisor against the same server it overwrites the information saved from the prior execution of the upgrade advisor.

You can view the reports by clicking on the "Launch Upgrade Advisor Report Viewer" hyperlink from the main Upgrade Advisor window ("Welcome to SQL Server 2005 Upgrade Advisor"). When you do this, the following window will be displayed:

Click for larger image

On this window you can select the reports you what to view. You can use the drop down menu at the top of this window to select the server for which to view reports, and what instances or components you want to view.

In addition to selecting which report you what to view you can use the "Filter By" drop down to filter your report the following ways: All issues, All upgrade issues, Pre-upgrade issue, All migration issue, and Resolved issues. The above report shows all the Data Transformation Services issues for a server named "SERVER1".

Note that the above report has a "When to fix" column. This column identifies at what time during your migration process you need to fix this issue. On this report there are "Before", "After" and "Advisor" when to fix issues.

This window only shows the issues descriptions. To show the detailed information about each issue you need to expand the issue by clicking on the "+" sign next to the issue. When I click to expand the "Replace functionality of Dynamic Properties task after package migration" item the following window is displayed:

Click for larger image

Here you can see I have 10 objects that the upgrade advisor identified as having problems with this issue. To view a report that lists the 10 objects click on the hyperlink labeled "Show affected objects". Additional information about this issue and how to resolve it can also be viewed by clicking on the "Tell me more about this issue and how to resolve it" hyperlink.

There is no easy way to print all the issues identified by the SQL Server 2005 Upgrade Advisor. I have identified this issue to Microsoft, and they said it would be a great enhancement. Hopefully in future versions of the upgrade advisor they will include a print process to make it easier to get a hard copy version of the report. Microsoft did offer a work around to the missing print function. They suggested that you use the "Export Report" function.

Each issues window contains an "Export Report" link. If you click on this link, you can export the data used in all the issues into a comma delimited file (CSV). You can then use this CSV file as input into excel so you can format it for printing. Another option for printing is to open each issue, select all the text, and then paste the text into a word document. Then print the word document once you have cut and pasted all issues information.


The SQL Server 2005 Upgrade Advisor is a great tool for helping you migrate to SQL Server 2005. It should be one of first steps you use in the process of migrating your applications and databases to SQL Server 2005. The output from the advisor will help identify the issues you have to consider in upgrading your SQL Server 2000 and 7.0 components to the new release of SQL Server. Remember SQL Server 2005 Upgrade Advisor is a work in progress, so you should periodically check to see if there are new versions during your migration planning process.

» See All Articles by Columnist Gregory A. Larsen

Mobile Site | Full Site