Analyze Database using SQL Upgrade Advisor

Steps how to analyze Database using SQL Server Upgrade Advisor before upgrading database to SQL Server 2008.

Here I am trying to explain detailed steps how to analyze the database to SQL server 2008 using upgrade advisor. This article helps us in analyzing the database and the issues that may occur during the upgradation of SQL Server 2000 / 2005 to SQL Server 2008 database. 

Upgrade Advisor

Upgrade Advisor Overview


Ÿ  Configuration and objects within installed components

        SQL Server

        Analysis Services

        Notification Services

        Reporting Services

        Data Transformation Services

        Integration Services

Ÿ  Trace files (captured using Profiler/SQL Trace)

Ÿ  T-SQL scripts (any script file)

Generates report of issues that must be addressed

Ÿ  Before – potential showstoppers, must resolve before upgrading

Ÿ  After – should be resolved after completing upgrade

Ÿ  Anytime

Ÿ  Advisory – notices and/or warnings


Windows XP SP2, Windows Server 2003 SP1, Windows Vista, Windows Server 2008 Beta 3

Windows Installer 3.1

.NET Framework 2.0


Ÿ  SQL Server 2000

        Requires decision support objects (DSO) if scanning Analysis Services 2000

        Requires client components if scanning SQL Server 2000 packages

Ÿ  SQL Server 2005

        Requires backward compatibility components if scanning SQL Server 2005 DTS packages that were upgraded from SQL Server 2000

Getting Started

Run Upgrade Advisor from programs menu

Start with documentation

Ÿ  Access from Welcome screen or navigate to installation folder

Ÿ  Review rules included with current version of UA

Check for updates

Ÿ  Ensure new rules are not missed


Ÿ  Analysis Wizard

Ÿ  Report Viewer




Analysis Wizard: Server selection and components


Server name

Ÿ  Local or Remote

Ÿ  Supports named instance (see next section for further details)

Detect components

Select one or more to analyze



Analysis Wizard: Connection parameters


Instance name

Ÿ  Select from drop-down for local instances

Ÿ  Enter instance name manually if not found or for remote instances

        MSSQLServer is the default instance name

Ÿ  Select authentication and user name

        Make sure you have necessary permissions to read objects


Analysis Wizard: SQL Server parameters


Select databases to be analyzed

Ÿ  Pick only the ones you will upgrade

Ÿ  Recommend analyzing databases with large number of objects separately

Trace files

Ÿ  Use a workload trace that presents a good representation of your application’s functionality (broad feature coverage)

Ÿ  Can extend analysis time notably

Batch files

Ÿ  Useful for maintenance and batch scripts



Analysis Wizard: Confirm settings


Final review before running analysis

Note report file path

Ÿ  If running more than once, backup files/folder – new report will overwrite existing report


Analysis Wizard: Analysis in progress


Scans components, trace files and script files

Applies rules against scanned objects to detect compliance/violation

Ÿ  Each object is compared with all relevant rules

Ÿ  Currently 87 (CTP5+), can be added/upgraded independent of SQL Server

Analysis period can range from minutes to days, depending on

Ÿ  Number of objects in component (SQL Server, Analysis Services, DTS, etc…)

Ÿ  Size of trace file and/or script file

Ÿ  Disk performance

Click on “Launch Report” when completed





By Venkat K   Popularity  (3436 Views)