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
Analyzes
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
Requirements
Windows XP SP2, Windows Server 2003 SP1, Windows Vista, Windows Server 2008 Beta 3
Windows Installer 3.1
.NET Framework 2.0
Supports
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
Launch
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
