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.

Step 5:

For analyzing DTS packages, the user can choose to analyze DTS packages on the server or DTS package files in the file system. If DTS packages in the file system is selected, then the path to the folder that contains DTS packages has to be provided.

Click Next.

Step 6:

Click Run.

Upon clicking "Run," Upgrade Advisor starts the analysis.

When the analysis is complete, the report is written to a file. The report can be viewed by launching the report viewer from this page by clicking "Launch Report" or, if the user wants to view the report later, he can launch the Upgrade Advisor Report Viewer from the Upgrade Advisor start page.

The report is as follows:

A detailed description about issues can be obtained by clicking on the "+" symbol.

To read more about the issue and ways of resolving it, refer to the Upgrade Advisor Help. It provides exhaustive information about issues that can be detected and that cannot be detected. It deals with upgrade issues of SQL Server components like Analysis Services, the database engine, reporting services, etc.

Upgrade Advisor Report Viewer

Reports are generated for every component analyzed in the analysis wizard. A report provides details about known issues that affect an upgrade. The report also provides links to information and suggested actions for addressing the identified issues. The reports can be viewed using the Upgrade Advisor Report Viewer. It can be launched from the start page by clicking "Launch Upgrade Advisor Report Viewer."

The user can select the components in the report and the corresponding upgrade issues are displayed. You can apply a filter from the "Filter By" box to see the following:

1. All issues.
2. All upgrade issues.
3. Pre-upgrade issues.
4. All migration issues.
5. Resolved issues.

The report files are saved in XML format. They can be exported to CSV format.

Upgrade Advisor Command Line Utility

The UpgradeAdvisorWizardCmd utility can be used to run the upgrade advisor from the command line.

Syntax:

UpgradeAdvisorWizardCmd [ -? ] |

[ -ConfigFile filename | ]

[ -SqlUser login_id -SqlPassword password ]

[ -NsSqlUser login_id -NsSqlPassword password ]

[ -CSV ]

where is any combination of the following:

-Server server_name

-Instance instance_name

-NSInstance NS_instance_name

Example 1:

The following example shows how to run Upgrade Advisor from the command line by using default configuration settings and Windows Authentication. This command will analyze all components except Notification Services because the command does not specify a Notification Services instance name.

UpgradeAdvisorWizardCmd -Server SWATI -Instance SQL2000

Example 2:

The following example shows how to run Upgrade Advisor from the command line by using a configuration file and specifying a SQL Server user name and password for connecting to the instance of SQL Server.

The XML file template is as follows:

UpgradeAdvisorWizardCmd -ConfigFile "C:\Config1.xml"

-SqlUser "sa" -SqlPassword "Infosys123"

Upgrade Process

The upgrade process consists of the following steps:

1. Review the "Hardware and Software Requirements for Installing SQL Server 2005" topic in SQL Server 2005 Books Online.
2. Back up data and system settings.
3. Run Upgrade Advisor.
   
   Upgrade Advisor does not modify your data or change settings on your computer.
4. Review the issues identified in the Upgrade Advisor report.
5. Resolve any blocking issues, which will prevent you from upgrading to SQL Server 2005.
6. Resolve any other pre-upgrade issues.
7. Run Upgrade Advisor to verify that issues have been addressed.
8. Run SQL Server 2005 Setup.
9. Resolve any post-upgrade and migration issues.
   

Conclusion

We have now seen how upgrade advisor helps the user in the process of upgrading SQL Server 2000 (or 7.0) instances to SQL Server 2005.Developers and DBAs can use this tool as a guide to understanding the issues that will cause problems during and after the user upgrades or migrates to SQL Server 2005.