A Look at Microsoft SQL Server Upgrade Advisor

Microsoft SQL Server Upgrade Advisor tool helps the user with the process of upgrading SQL Server 2000 (or 7.0) instances to SQL Server 2005. It analyzes the configuration of installed SQL Server 2000 (or 7.0) components and generates reports. The reports give an idea of the issues that will hinder the upgrade process. The user must address these issues for a successful upgrade.

Installation and Prerequisites

The SQL Server Upgrade Advisor can be installed from the Microsoft download center. The prerequisites for installing upgrade advisor are as follows:

1. Windows 2000 SP4 or a later version, Windows XP SP2 or a later version, or Windows Server 2003 SP1 or a later version.
2. Microsoft Windows Installer 3.1 or a later version.
3. Microsoft .NET Framework 2.0 or a later version. This software can be downloaded form the .NET framework developer center.

The installation procedure is very simple.

After installing Upgrade Advisor, it can be started from the Start menu:

Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server 2005 Upgrade Advisor.

Working of the Upgrade Advisor

Upgrade Advisor consists of an analysis wizard and a report viewer.

Upgrade Advisor Analysis Wizard

The Upgrade Advisor Analysis Wizard gathers information about the server, instances, SQL Server components, and trace files that have to be analyzed. After all the information has been gathered and confirmed, the Upgrade Advisor Analysis Wizard analyzes the SQL Server components. The wizard can be launched from the start page of the upgrade advisor.

Step1:

Step 2:

Click Next.

Type the server name in the textbox provided. The user can type "localhost" or "." if it has to be connected to the local computer. Click Next.

The Detect button accesses the specified server and detects components to analyze:

• SQL Server is detected if an instance of SQL Server 7.0 or SQL Server 2000 is found using the specified server's registry.
• Analysis Services is detected if an instance of Analysis Services is found using the specified server's registry. However, SQL Server 2000 decision support objects are required to scan an instance of Analysis Services.
• Notification Services is detected if an instance of Notification Services is found using the specified server's registry.
• Reporting Services is detected if Reporting Services is found using the specified server's registry.
• Data Transformation Services is detected if the SQL Server 2000 management tools are installed locally and can be detected using the registry.

Here, after analyzing the server, the components detected to be analyzed are SQL Server and DTS packages.

Step 3:

Type the instance name and click Next.

Step 4:

In this step the user can select the databases to be analyzed. The user has to the option to select all databases or he can select a single database by selecting only the checkbox against that database name.

Trace files and SQL batch files can also be analyzed. The user has to mention the path of these files. Click Next.