To improve the SQL Server 2005 upgrade experience, Microsoft provides a new tool called SQL Server 2005 Upgrade Advisor Analysis Wizard. You can use the Upgrade Advisor during the upgrade-planning phase to analyze SQL Server 2000 and 7.0 instances, including components such as DTS, Analysis Services, scripts, and trace files. The tool reports potential problems that might occur during or after the upgrade. Using the Upgrade Advisor is key to making the right choices for a successful upgrade.
The Upgrade Advisor is built on a rule-based engine that the SQL Server Development team created based on internal lab testing, SQL Server 2005 early-adopter experiences, community feedback, and product-team knowledge and experiences. The Upgrade Advisor tool will be released to manufacturing (RTM) before the SQL Server 2005 RTM and will also be included on the SQL Server 2005 RTM media. On the SQL Server 2005 Setup splash screen, Microsoft will provide an option to install Upgrade Advisor. In addition, Upgrade Advisor will be available to download from the Microsoft Web site. The SQL Server 2005 product team plans to update the Upgrade Advisor tool to add new rules and functionality even after the SQL Server 2005 release, and updated versions of the tool will be available for download. Note that the Upgrade Advisor tool requires the Microsoft .NET Framework 2.0.
The tool provides two options, one to launch the Analysis Wizard and the other to launch the Report Viewer. For each component you upgrade, the Analysis Wizard generates a report, which it saves in XML format, that you can view by using the Report Viewer.
As Figure A shows, the Upgrade Advisor Analysis Wizard lets you select the component you want to analyze. For each selected component, the Analysis Wizard prompts you to further specify details about what analysis you want it to perform. For Database Server, you can specify that the wizard analyze all databases or a list of databases. The Upgrade Advisor tool thoroughly analyzes the selected databases, including stored procedures and other T-SQL text (i.e., the text in the syscomments system table). You can also provide one or more trace files and SQL script files for the wizard to analyze against the specified databases. The Upgrade Advisor tool can analyze DTS packages saved on the disk or in the specified SQL Server instances.
Once the Analysis Wizard finishes analyzing the selected components, it creates a report that you can view by using the Report Viewer. As Figure B shows, the Upgrade Advisor report lists potential problems, guidance for when to fix them, and a description of how to fix or work around them. The Report Viewer lets you filter the report, mark problems as resolved, and export the report to CSV format.
