The first article of the series, Part 1 – Upgrade Overview and Project Planning, detailed the business justification for upgrades to SQL Server 2000 related to TCO (Total Cost of Ownership), automation capabilities and new features from both the Business and DBA perspectives. The next portion of the article outlined a detailed process to break down the SQL Server 2000 project as well as the applicable Upgrade project phases using high level steps. Both sets of information were in simple terms for DBAs new to the Upgrade process, well experienced DBAs needing a refresher or for technical managers interested in the level of effort and overall planning for a SQL Server 2000 Upgrade.
This article, Part 2 – SQL Server 6.5 to 2000 Critical Upgrade Decisions and Redundant Upgrade Architecture, will begin to detail the technical components faced by the DBAs and Developers during the Upgrade process. The technical components detail the Critical Upgrade Decisions related to ANSI NULLS, Quoted Identifiers and other items. In addition, a valuable Redundant Upgrade Architecture is introduced for the Upgrade to prevent a significant set of problems. The combination of the Upgrade decisions and the Redundant Upgrade Architecture can easily make or break the upgrade for your business. Needless to say, these items require fore thought at the inception of the project by the technical staff to prevent management’s biggest fear: no available platform following the upgrade.
Critical Upgrade Decisions
In order to accurately and efficiently upgrade to SQL Server 2000, it is necessary to research critical decisions and determine the appropriate configurations based on the business environment. This is certainly the case with SQL Server Upgrades because depending on the configurations entered during the upgrade and in the SQL Server 2000 environment, code can operate differently causing unexpected results. Further, over the course of SQL Server’s life, Microsoft has implemented a number of default configurations which have subsequently changed between versions. As such, below outlines key SQL Server configurations the upgrade must address due to the potential impacts.
CRITICAL UPGRADE DECISIONS2
ID ITEM SQL SERVER 6.5 SQL SERVER 2000 ADDITIONAL INFORMATION 1 ANSI NULLS
- Default – ANSI NULLS is OFF
- Default – ANSI NULLS is ON
- Validate NULL comparisons are operating properly and ensure IS NULL and IS NOT NULL expressions are being used rather than = NULL or <> NULL
2 Quoted Identifiers
- Default – SET QUOTED_IDENTIFIER OFF
- Default – SET QUOTED_IDENTIFIER ON
- Variables are in denoted by single quotes in T-SQL code
- Keywords are denoted by double quotes in T-SQL code
- See the SET QUOTED_IDENTIFIER’ article in Books Online for additional details
3 SQL Server Keywords
- Basic list of Keywords
- Expanded list of Keywords that are reserved
- Ensure object names are not SQL Server Keywords or rely on the Quoted Identifiers
4 System Objects
- Baseline set of objects
- Additional System Tables, Views, Stored Procedures and Functions
- Introduction of ANSI Views to query data
- Pay close attention to code directly accessing system tables and migrate to use stored procedures and INFORMATIONSCHEMA Views
5 Replication
- Transactional Replication and Remote Servers
- Snapshot and Merge Replication and Linked Server Additions
- See the Replication Overview’ article in Books Online for additional details
6 Registry Settings
- Finite number of Registry Keys
- Support for Multiple Instances and additional applications
- Additional registry keys with the addition of Analysis Services and English Query
7 JOIN Types
- ANSI syntax with WHERE clause comparison
- ANSI JOIN syntax (INNER, OUTER, FULL and CROSS)
- See the Types of Joins’ article in Books Online for additional details
8 Query Plans
- Default – LOOP
- HASH
- MERGE
- NESTED LOOP
- See the Understanding Hash Joins’, Understanding Merge Joins’, Understanding Nested Loops Joins’ articles in Books Online for additional details
9 Database Compatibility Modes
- Not Available
- 65, 70 and 80 Compatibility Modes
- T-SQL command support based on the database configuration
10 Database Recovery Options
- Read-Only Mode
- Standby Mode
- See the Using Standby Servers’ article in Books Online for additional details
11 Database Recovery Models
- Truncate Log on Checkpoint
- Simple, Bulk Logged, Full
- See the Using Recovery Models’ article in Books Online for additional details
12 Sort Orders and Character Sets
- SQL Server Specific
- SQL Server or Windows Locale Options
See the Collation Settings in Setup’ article in Books Online for additional details
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 BCPDTS 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 DBAssociatesIT product, 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. Stay tuned to
http://www.edgewoodsolutions.com/resources/articles.asp for future updates!
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 backuprestoration 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.
Second, the availability of a clean’ production SQL Server 2000 server is beneficial in order to move forward with an issue-free server. Too often, a server with multiple software installations can quickly become a suspect for unexplainable server behavior due to the previous software installations, drivers and subsequent files. Unfortunately, application un-installs are rarely as clean as expected. Typically, the un-install leaves remnants in the registry that can cause more problems in the long term as opposed to instilling confidence in the environment with a clean Windows and SQL Server installation.
For some companies, the needed hardware and software are not an issue while at other organizations the additional hardware can become a major challenge. Ideally, a new piece of hardware that is properly tested should be introduced to the environment as the SQL Server 2000 Production server. It is always in your best interest as a DBA to ensure that production hardware is under warranty with the manufacturer in case a critical problem occurs. Too often production hardware is not under warranty, then low and behold a serious problem occurs that requires expensive equipment replacements and labor that a warranty would have easily resolved.
If a situation arises and hardware is not available in your organization or the budget, consider a short term lease for a temporary server. Another option may be to build a server or use a PC depending on the database’s size. Although this may not be the ideal option, it may resolve the issues at hand and provide the needed level of comfort from a redundancy perspective. Be sure to incorporate these components into your plan as critical steps in order to properly address your upgrade and move forward with the proper hardware and software configurations.
Conclusion
An upgrade is a significant event in your organization and must be planned for accordingly. Planning will ensure confidence in moving forward with the upgraded system or have the ability to revert to the previous system in case an issue arises. As such, a comprehensive project plan should be developed in order to achieve proper technical decision making that is critical to the success of the upgrade. Needless to say, sufficient testing is also required to ensure the selected configurations are meeting your business needs. This confidence can be achieved with the Redundant Upgrade Architecture to ensure a clean SQL Server 2000 server is introduced into the production environment as well as the ability to revert to the SQL Server 6.5 environment when necessary. Good luck!
* Originally Published 05.28.2003 – Jeremy Kadlec – Edgewood Solutions. All rights reserved 2003
Click here to sign up for FREE tech newsletters from murdok!
Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, (www.edgewoodsolutions.com) a technology services company delivering full spectrum Microsoft SQL Server Services on the east coast of the United States primarily in the Washington DC and Boston areas. Jeremy can be reached at 410.591.4683 or jeremyk@edgewoodsolutions.com.
Learn more about how Edgewood Solutions delivers databases at their finest at www.edgewoodsolutions.com.
Copyright 2002-2004 Edgewood Solutions All Rights Reserved
Some names and products listed are the registered trademarks of their respective owners.
