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.


  • 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 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



            Leave a comment

            Your email address will not be published.