Tips and Tricks: Upgrading from SQL Server 2000 to 2005

Author: Joseph Cuesta

March 2008

Situation
Your business is growing, and demands on your database are increasing. You want to reap the rewards of Microsoft SQL Server 2005.   

Problem
You are still running SQL Server 2000 on outdated hardware. Generating reports is now taking five times longer than it used to. You are becoming more aware of security concerns that affect businesses everyday.

Solution
Upgrade to SQL Server 2005!

Three Phases of a Successful Upgrade
•    Planning and Research
•    Testing and Process Validation
•    The Production Upgrade

The most important of these three phases is the Planning and Research phase. Here you will make important decisions such as which upgrade path to take, what type of hardware you will be using, etc.

Two Methods of Upgrade
•    Side by Side Upgrade
•    In-place upgrade

The side-by-side upgrade is the most common upgrade path.  It is a simple database detach and re-attach on the SQL Server 2005 instance or a database backup and restore from the older version to the new version.

The in-place upgrade involves upgrading and adapting the databases, settings, and extended features to the SQL Server 2005 engine during the install process.   The in-place upgrade will be the easiest if you are not upgrading to new hardware. By performing an in-place upgrade, logins and users remain in-sync, database connections remain the same for applications, and SQL Agent jobs and other functionality is concurrently upgraded during the installation. Several features, such as log shipping, replication, and cluster environments, have special upgrade considerations that will complicate the upgrade process.

Once you decide which upgrade path you will use, there are more considerations for the different components of SQL Server.
•    DTS Jobs
Moving from DTS to SSIS is a migration involving wizard-driven output along with some manual redesign to complete the process. It is estimated that 70% of the jobs will be successfully migrated. All others will need to be re-developed in SSIS.
•    Upgrading Analysis Services
Dimensions, partitions, storage modes, aggregates, and measures have been preserved in the new release. From an upgrade perspective, Microsoft provides direct in-place upgrade from Analysis Services 2000 to Analysis Services 2005.
•    Upgrading Reporting Services
Because Microsoft released Reporting Services 2000 for the first time early in 2004, you won’t see major architectural changes in the Reporting Services 2005 platform.

Hardware Considerations
The key to determining the correct platform is matching your expected workload and budget to the available options.
•    The 64-bit Intel Itanium Platform
For businesses that need the highest levels of scalability, Intel Itanium 2-based systems are the clear platform choice. SQL Server Standard and SQL Server Enterprise will both run on Itanium processors.
•    The x64 Platform
A primary reason for moving to the x64 platform might be if your current workload is memory constrained and you’ve reached the 32-bit Windows 2GB barrier. All four versions of SQL Server 2005 will run on x64 platforms; but on Express and Workgroup editions they will run in 32 bit mode.
•    The 32-bit Platform

If your current SQL Server 2000 system has available headroom, you can leverage the investment your business has made in its 32-bit servers by continuing to run SQL Server 2005 on them.

TIPS
•    Follow the three phases of the upgrade path
•    Follow all Microsoft recommended procedures
•    Run Upgrade Advisor and take note of the recommendations
•    Use default server configuration settings for most applications
•    Locate logs and the tempdb database on separate devices from the data
•    Provide separate devices for heavily accessed tables and indexes
•    Use the appropriate RAID configuration (RAID 10 for database drives)
•    Use multiple disk controllers
•    Pre-grow databases and logs to avoid automatic growth and fragmentation performance impact
•    Maximize available memory
•    Follow the three phases of the upgrade path

This is just a small piece of the upgrade process; there are many more considerations to take into account. Hopefully, this will shed some light on some of the first steps for a smooth upgrade.  For a comprehensive and detailed discussion of upgrade considerations, see SQL Server 2005 Books Online and the white papers, Webcasts, and other resources listed in “SQL Server 2005 Resources.”

If you need more information, contact Gary Codeluppi at 937-431-1026 x123 or visit the Ross Group Inc web site at www.rossgroupinc.com.