Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2005 Upgrade Considerations for DBAs and Developers


By:   |   Read Comments   |   Related Tips: More > Upgrades and Migrations

Attend these FREE SQL Server 2017 webcasts >> click to register


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:


Last Update:


signup button

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools