articles >> general dba >> A Look at Microsoft SQL Server Upgrade ...

A Look at Microsoft SQL Server Upgrade Advisor

By : S.Srivathsani Jul 06, 2007

Page 3 / 3

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 | <server_info> ]

     [ -SqlUser login_id -SqlPassword password ]

     [ -NsSqlUser login_id -NsSqlPassword password ]

     [ -CSV ]

where <server_info> 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:

<Configuration>

     <Server> </Server>

     <Instance></Instance>

     <Components>

          <SQLServer>

          <Databases>

               <Database></Database>

          </Databases>

          <TraceFiles>

               <TraceFile></TraceFile>

          </TraceFiles>

          <BatchFiles>

               <BatchFile></BatchFile>

          </BatchFiles>

               <BatchSeparator></BatchSeparator>

          </SQLServer>

          <AnalysisServices>

          <Databases>

               <Database></Database>

          </Databases>

          </AnalysisServices>

          <ReportingServices></ReportingServices>

          <DataTransformationServices>

               <PackagePath></PackagePath>

          </DataTransformationServices>

          <NotificationServices>

               <NSInstance></NSInstance>

          </NotificationServices>

     </Components>

</Configuration>

 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.