Upgrading to SQL Server 2005


The installation guidelines are covered in Chapter 2, so we focus this section mainly on upgrade strategies and considerations for the SQL Server 2005 database component. The upgrade of individual components outside of the database is covered in individual chapters.

A smooth upgrade requires a good plan. When you devise an upgrade plan, you need to break down the upgrade process into individual tasks. This plan should have sections for preupgrade tasks, upgrade tasks, and post-upgrade tasks.

Your preupgrade tasks should take into consideration SQL Server 2005 minimum hardware and software requirements. You should have an inventory of your applications that access the server, database-collation requirements, server dependencies, and legacy-systems requirements such as data-access methods. Plans should be in place for testing the upgrade process and applications. You should have a thorough understanding of backward-compatibility issues and have workarounds or fixes identified. You should also use the upgrade tool to assist in identifying and resolving issues.

The upgrade execution process should be a smooth execution of your well-documented and rehearsed plan.

Post-upgrade tasks should consist of reviewing the upgrade process, bringing the systems back online, monitoring, and testing the system. Specific database maintenance will also need to be performed before releasing the system to the user community. (These and other recommended steps are outlined later in the chapter.) We recommend that you run your database in backward-compatibility mode after the upgrade to minimize the amount of change to your environment. The database-compatibility mode should be updated as part of a follow-up upgrade process along with enabling new SQL Server 2005 features.

As part of deciding your upgrade strategy, we will discuss both the in-place (upgrade) and side-by-side migration methods for upgrading.

Upgrade in Place

The in-place server upgrade is the easier of the two options. This is an all-or-nothing approach to upgrading, meaning that once the upgrade is initiated, there is no simple recovery procedure. This type of upgrade has the added requirement of greater upfront testing to avoid using a complex back-out plan. The benefit of this approach is not having to worry about users and logins remaining in-sync, and database connectivity changes will not be required for applications. In addition, SQL Agent jobs will be upgraded during the upgrade process.

Here's a high-level scenario of an upgrade in place based on Figure 3-1. First, install the prerequisite files on your system. Before upgrading to SQL Server 2005, your server needs, at a minimum, .NET Framework 2.0, SQL Server 2000 with Service Pack 3 or 3a (or greater), or SQL Server 7.0 with Service Pack 4. The next step is to run the system configuration checker (SCC) without affecting the user environment. The SCC examines the destination computer for conditions that would prevent an upgrade from completing, such as not meeting the minimum hardware or software requirements. If such a condition is found, setup will be aborted and the SQL Server 2005 components will be uninstalled. Once verified, the SQL Server setup program is able to lay the 2005 bits and backward-compatibility support files on disk while SQL Server 2000 (or 7.0) is still available to users. However, planning to upgrade a server while users are online is not recommended. The setup program takes the server offline by stopping the existing SQL Server services. The 2005-based services assume control of the master database and the server identity. At this point, SQL Server service takes over the databases and begins to update them while allowing users back into the environment. When a request for data occurs in a database that has only been partially updated, the data associated with this request is updated, processed, and then returned to the user. The next step is to kick off the uninstall procedure for the old binaries. This step occurs only if no remaining SQL Server 2000 or 7.0 instances are on the server. Finally, SQL Server agent jobs get upgraded.

image from book
Figure 3-1

If you would like to change editions as a part of your upgrade, there are some limitations that you must be aware of. SQL Server 2000 Enterprise, Developer, Standard, and Workgroup editions can be upgraded to different editions of SQL Server 2005. SQL Server 2000 Desktop Editions can only be upgraded to either the Express or Workgroup editions of SQL Server 2005. If this is of interest to you, we suggest reading Books Online (BOL) for additional details.

If you are using SQL Server 7.0, there are a few additional items to keep in mind regarding your upgrade. First, SQL Server 7 is not supported on Windows Server 2003. SQL Server 2005 is not supported on NT 4.0. If you are trying to perform a Windows 2000-based in-place upgrade, Microsoft Data Access Components (MDAC) 2.8 SP1, IIS 5.0 or greater, Internet Explorer 6.1, and Windows Installer 3.1 will all have to be installed. If you are stuck in the 1960s and still have a SQL Server 7.0 cluster, side-by-side upgrade is the only option because SQL 7.0 clusters do not support MDAC greater than 2.6. Finally, SQL Server 7.0 based MSX/TSX jobs cannot be upgraded to SQL Server 2005.

Side-by-Side Upgrade

In a side-by-side upgrade, SQL Server 2005 is either installed along with SQL Server 2000 (or 7.0) as a separate instance or on a different server. You may want to select this option as part of a hardware refresh or migration to a new platform such as Itanium or X64. Because of the back-up and restore times involved in a back-out scenario, if you have a sizable database, this is definitely the option to go with.

As part of this method, you can simply back up the databases from the original server and then restore them to the SQL Server 2005 instance. Another option is to manually detach your database from the old instance and reattach it to the new instance. You can also leverage the copy database wizard to migrate your databases to the new server. Although this approach provides for the best recovery scenario, it has additional requirements beyond those of the in-place upgrade, such as maintaining the original server name, caring for application connectivity, and keeping users and their logins in synch. If traces are part of your testing, maintaining the database IDs will also be a concern.

Upgrade in Place versus Side-By-Side Upgrade Considerations

There are numerous factors you should consider before selecting an upgrade strategy. Your strategy should include the need for component-level upgrade, the ability to roll back in case of failure, the size of your databases, and the need for partial upgrade. For many of you, top priorities will be whether you will be able to upgrade to new hardware, to facilitate a change of strategy such as a server consolidation, or to manage a small server outage window for the upgrade.

The arguments in favor of a side-by-side upgrade are:

  • More granular control over upgrade component-level process (database, Analysis Services, and others)

  • Ability to run systems side-by-side for testing and verification

  • Ability to gather real matrix for upgrade (outage window)

  • Rollback strategy as original server is intact

  • Ability to upgrade platforms from a 32-bit to 64-bit server

  • Best for very large databases, as restore time could be sizable

The arguments against a side-by-side upgrade are:

  • Additional hardware may be required for instance upgrade or additional physical server for physical server upgrade.

  • Does not preserve SQL Server 2000 functionality

  • Issue of instance name for connecting applications

  • Space requirement on SAN, especially for very large databases

The advantages of an in-place upgrade are:

  • Fast, easy, and automated (best for small systems)

  • No additional hardware required

  • Applications retain same instance name

  • Preserves SQL Server 2000 "functionality" automatically

The disadvantages of an in-place upgrade are:

  • Downtime incurred because the entire server is offline during upgrade

  • No support for component-level upgrades

  • Complex rollback strategy

  • Very large databases require substantial rollback time



Professional SQL Server 2005 Administration
Professional SQL Server 2005 Administration (Wrox Professional Guides)
ISBN: 0470055200
EAN: 2147483647
Year: 2004
Pages: 193

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net