SQL Server Upgrade Recommendations and Best Practices Part 2: SQL Server 6.5 to 2000 Critical Upgrade Decisions and Redundant Upgrade Architecture

Redundant Upgrade Architecture - SQL Server 6.5 to 2000

As you begin to work towards upgrading your core business systems to SQL Server 2000, ensure that you develop a comprehensive plan to properly manage the project successfully. An upgrade at the surface appears simplistic, but can become difficult to coordinate with many team members from multiple departments. Typically, numerous team members are responsible for a multitude of tasks such as hardware configurations, testing and code modifications to successfully deliver a reliable platform. For additional IT Project Management information, stay tuned for a Project Management eBook that I will be releasing in the summer of 2003 to address many of these critical Project Management items.

With this being said, Microsoft provides two basic recommendations for upgrading from SQL Server 6.5 to 2000. The options are either via the Pipeline Upgrade or the Machine to Machine Upgrade3.

• Pipeline Upgrade: occurs on a single machine where SQL Server 2000 is installed over SQL Server 6.5 in order to complete the upgrade on a single server. Both the SQL Server 6.5 and 2000 databases are retained during scenarios with sufficient disk space as respective default instances, but only one version of SQL Server can be active at any given time3.
  
   
• Machine to Machine Upgrade: occurs over the network between the SQL Server 6.5 and SQL Server 2000 server with each machine operating independently, but controlled by the Upgrade Wizard3.

Unfortunately, neither of these options delivers a fail safe mechanism for DBAs, nor is a clean SQL Server introduced to your production environment. Even with the Machine to Machine Upgrade some historical system files may be migrated. This scenario introduces the risk of a network glitch on the LAN that could cause problems during critical downtime needed to complete the upgrade. These aspects are critical to efficiently managing the upgrade and the long term SQL Server environment. Further, the options are solely focused on the steps related to the SQL Server Upgrade as opposed to a comprehensive initiative to support the business during each step of the larger project at hand.

A Redundant Upgrade Architecture has been developed to address the needs previously outlined as well as the elimination of an extensive amount of network traffic that could ultimately result in a network glitch. The architecture demonstrated below ensures a successful SQL Server 2000 Upgrade by retaining the SQL Server 6.5 server as a fail safe mechanism and introducing a clean SQL Server 2000 to the production environment. All of the upgrade tasks are performed on the Migration SQL Server, via the Microsoft Upgrade Wizard, with reliable backup\restoration commands used to migrate the data between the three servers i.e. SQL Server 6.5 Production to Migration SQL Server and Migration SQL Server to SQL Server 2000 Production as illustrated in the following diagram.

First, a fail safe mechanism for the SQL Server 6.5 environment is needed to ensure the business system will be available at the completion of the downtime. If the upgrade experiences a situation out of the DBAs control, such as an unexpected power outage, it will be stressful for the DBA to recover the SQL Server 6.5 environment. The DBA needs to complete the recovery in an accurate manner, in order for users to conduct business. Another scenario where the SQL Server 6.5 server acts as a fail safe mechanism is during a production upgrade where there is not confidence in the SQL Server 2000 platform following the upgrade as determined by testing. For example, if a code change is introduced after testing is completed and uncovered during the upgrade, it is prudent to validate the code. If the code implications are not clearly understood, it may be beneficial to revert to the SQL Server 6.5 environment rather than moving forward with the upgrade based on the level of uncertainty. Without the SQL Server 6.5 environment maintained, it will be impossible to return to this platform to conduct business as usual. In the upcoming article, Part 3 - SQL Server 6.5 to 2000 Upgrade Checklist and Application, processes for configuring the SQL Server 6.5 environment to ensure this server is not altered will be detailed.