By: Jeremy Kadlec | Comments | Related: > Upgrades and Migrations
Problem
Making the decision to upgrade to SQL Server 2005 should be one that is made with sufficient information, not haphazardly. Understanding the changes needed to your SQL Server 2000 environment prior to upgrading should be one of the first sets of information gathered to determine the level of effort needed to upgrade. Scanning your code for issues, reviewing your configurations and DTS Packages can be a time consuming process. As such, how can I gather the needed information about my SQL Server 2000 environment to determine how much work I have to complete before upgrading to SQL Server 2005?
Solution
As SQL Server 2005 was released, Microsoft also released the SQL Server 2005 Upgrade Advisor which can be installed on your desktop and then connect to instances of SQL Server. This application reviews all of your code (T-SQL, Analysis Services, Data Transformation Services, Notification Services, Reporting Services, etc.) in SQL Server as well as externally in Trace and batch files, then outlines all of the issues that need to be resolved before or after the upgrade process. In this tip we will outline the steps needed to execute and review the
| ID | Description |
|---|---|
| 1 | Download and Installation
Download the SQL Server 2005 Upgrade Advisor - http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en. To access the Microsoft SQL Server 2005 Upgrade Advisor navigate to Start | All Programs | Microsoft SQL Server 2005 | SQL Server 2005 Upgrade Advisor. |
| 2 | Main Menu - On this interface the two primary action items are to 'Launch Upgrade Advisor Analysis Wizard' to analyze a new SQL Server instance or 'Launch Upgrade Advisor Report Viewer' to review an earlier report from a SQL Server instance.
The three other options on this interface are to read more about the tool, check for updates and to read about upgrade issues. In this tip we will select the 'Launch Upgrade Advisor Analysis Wizard' option and walk through this process.
|
| 3 | Welcome Screen - This interface outlines each step in the process and begins the process.
|
| 4 | SQL Server Components Screen - Select the SQL Server name and SQL Server components.
|
| 5 | Connection Parameters Screen - Select the instance name and authentication mode.
|
| 6 | SQL Server Parameters Screen - Select the databases that you want to analyze as well as optionally select trace and batch files.
|
| 7 | DTS Parameters Screen - Select the appropriate radio button to analyze the DTS Packages in MSDB or in the file system.
|
| 8 | Confirm Upgrade Advisor Settings Screen - Overview of the analysis and press the 'Run' process to begin the analysis.
|
| 9 | Upgrade Advisor Progress - Review the progress of the analysis.
Once complete, review the report as shown in the next step.
|
| 10 | Final Report Screen - Review each of the items from the report by expanding the line item.
|
Next Steps
- Install the SQL Server 2005 Upgrade Advisor application on your desktop and execute it against some representative development or test SQL Server environments to gather a preliminary set of information. If you do not have a representative environment, consider backing up and restoring the databases to gather the needed information separate from your production environment.
- Review the output to determine steps you need to take in order to upgrade and determine if any patterns are occurring that can be grouped together and resolved.
- Share the results with your team to determine the appropriate team member(s) to address the need and be sure to spread the knowledge\lessons learned to benefit future development projects.
- Get buy-in from your team members to build a project plan to properly upgrade to SQL Server 2005 and be sure to include sufficient time for testing.
- Check out the following MSSQLTips.com upgrade related tips:
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
