SQL Server 2005 Upgrade Advisor

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:

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.

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles