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

Introduction – SQL Server Upgrades

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