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

SQL Server Upgrade Wizard Versus BCP or DTS

One critical decision that a DBA faces is determining the proper tool for the SQL Server 2000 Upgrade. The Microsoft SQL Server 2000 Upgrade Wizard is available for free as well as BCP (Bulk Copy) or DTS (Data Transformation Services). The Upgrade Wizard is typically the natural choice because Microsoft has built this tool specifically for the upgrade to verify the objects properly migrate, conduct exhaustive integrity checks and deliver the needed error handling. A second option is BCP or DTS, to migrate the data from SQL Server 6.5 to 2000. With this option it is a requirement to script the appropriate DDL (Data Definition Language) and DML (Data Manipulation Language) from the SQL Server 6.5 environment and apply the scripts to SQL Server 2000 in the proper order and verify no errors have occurred. Once these steps are completed, then it is necessary to compare row and object counts between the SQL Server 6.5 to 2000 environments. This would be followed by post upgrade testing with either the Upgrade Wizard or the BCP\DTS option prior to the production release.

As far as selecting the appropriate upgrade tool, one must assess the upgrade requirements in order to determine the ideal tool. Most upgrades can be categorized as one of the following:

1. Complete server upgrade where all of the database on a single server are upgraded to another dedicated server
   
    
2. Single database is upgraded to a shared SQL Server
   
    
3. Consolidation of multiple databases into a single database
   
    
4. Consolidation of multiple SQL Servers to a single SQL Server

Although these are the typical scenarios, more may exist depending on the unique characteristics of your environment. With the four scenario’s listed above, I recommend the Microsoft Upgrade Wizard for most scenarios for the following reasons:

• A high level of effort is needed to duplicate the Upgrade Wizard functionality with the same level of error handling for BCP/DTS. Further, based on basic testing BCP/DTS is not substantially faster in most situations to justify the additional DBA time to setup and test this alternative.
  
   
• The Upgrade Wizard in an automated fashion manages the upgrade of all database objects and particular SQL Server configurations. Unfortunately, BCP and DTS impose a manual process from SQL Server 6.5 to 2000 with the ability to only migrate data. In this scenario, it is necessary for the DBA to properly manage the remainder of the code i.e. Logins, Users, Stored Procedures, etc. It is also necessary to allocate time for rebuilding indexes which can be a very time consuming proposition that is sometimes overlooked.
  
   
• If you need to consolidate all the data from numerous SQL Server 6.5 databases to a single SQL Server 2000 database, I recommend first upgrading all of the databases to SQL Server 2000. Then leverage the advanced DTS features between the SQL Server 2000 databases for consolidation purposes. Another consolidation option is to use backup and restore commands to consolidate SQL Servers. If time is of the essence, use DBAssociates' SQL LiteSpeed for 50 to 90% time savings in order to expedite the consolidation process.
  
  • For additional information about SQL LiteSpeed check out – http://www.edgewoodsolutions.com/partners/dbassociates.asp
    
    
• If the entire SQL Server is being upgraded, the SQL Server Upgrade Wizard can automatically migrate the Scheduled Tasks. To accomplish this, it is necessary to upgrade the MSDB database and select the appropriate Scheduled Tasks options in the Wizard interface. During consolidation scenarios where SQL Server Scheduled Tasks need to be migrated to Jobs, handle those items individually via scripts. Unfortunately, BCP and DTS in this scenario will not be able to assist in the process from SQL Server 6.5 to 2000 and it will be necessary to script those items.
  
   
• When Replication is setup in the environment, it will be necessary to un-subscribe and re-subscribe for the Upgrade. Remote Servers could become an issue during consolidation, as Server and database names could change. As such, it may be easiest to leverage the Upgrade Wizard and select the appropriate replication settings in the interface to simplify the process or remove replication and reestablish following the upgrade. Once again BCP and DTS cannot assist in automating or expediting the replication items due to the limited capabilities between SQL Server 6.5 and 2000.

I do not want to be labeled as only having a hammer and seeing everything as a nail, but I believe in most circumstances the Upgrade Wizard will address most upgrade needs at most companies. The next article in the series will elaborate on the Upgrade Wizard steps in order to complete the upgrade under the circumstances listed above.