Critical Upgrade Decisions: Making the Right Configuration Choices
When a team sets out to migrate from SQL Server 6.5 to 2000, the first hurdle is the array of settings that can silently alter behavior. Unlike newer versions where the default environment is more uniform, early SQL Server releases shipped with configurations that differ across installations. A misstep here can cause a harmless-looking query to return nulls or throw errors only after the system is live. The key is to review each setting in the context of the production workload before the upgrade begins.
ANSI NULLs is the most common culprit. In 6.5 the default was OFF, meaning a comparison to NULL behaved as true if both sides were NULL. SQL Server 2000 switched to ON by default, so the same code that worked before would now return false. The fix is simple: search for any occurrences of “= NULL” or “
Quoted identifiers also warrant attention. A 6.5 instance usually ran with QUOTED_IDENTIFIER OFF, which interprets double quotes as string delimiters. In 2000 the default is ON, allowing double quotes to wrap object names. If the code contains statements like SELECT * FROM "Customer", the 2000 engine will treat "Customer" as a literal string and return an error. The safe approach is to enforce QUOTED_IDENTIFIER ON in all contexts or, better yet, audit the code for double-quoted identifiers and replace them with brackets or remove the quotes.
Reserved words can surface during a migration because the list of keywords expanded between releases. An object named “Table” or “Index” might have worked in 6.5 but will now collide with a reserved keyword in 2000. Scanning the database for names that match the new keyword list and renaming them before the upgrade prevents syntax errors that would otherwise surface during the migration step or even at runtime. If a name change is unavoidable, wrap the identifier in brackets in every usage to preserve the old behavior.
System objects and metadata access changed as well. In 6.5 developers sometimes queried internal tables like sysobjects or systables directly. 2000 introduced INFORMATION_SCHEMA views and ANSI‑compliant catalog views that are the recommended path. If the application contains hard‑coded references to system tables, these need to be migrated to the new views or to stored procedures that hide the implementation details. A quick scan of the code base for any SELECTs from system tables and replacing them with the appropriate catalog view often resolves a host of subtle bugs.
Replication requires its own set of considerations. Transactional replication, snapshot replication, and merge replication all behave differently in 2000, and the underlying catalog tables are more tightly coupled to the broker infrastructure. Before the upgrade, disconnect all replication agents, capture the current configuration, and re‑establish subscriptions after the new server is live. If a replication path involves remote servers that will also be upgraded, plan the sequence carefully so that name resolution remains stable during the transition.
Registry and configuration files also influence the upgrade outcome. SQL Server 2000 introduces new registry keys for analysis services and query languages, and these keys can be left over from a 6.5 instance if a side‑by‑side install is attempted. It is prudent to delete any remnants of the older installation or to perform a clean install on a new server and migrate data only. Likewise, ensure the SQL Server configuration manager is set to the correct service accounts and that all dependent services (SQL Agent, SQL Browser) start without error after the upgrade.
Finally, plan for compatibility mode and recovery options. SQL Server 2000 introduces compatibility modes 80, 70, and 65 that allow a database to run as if it were an earlier version. If an application relies on a 6.5–only feature, set the database compatibility level to 65 and test thoroughly. Also decide on a recovery model - Simple, Bulk‑Logged, or Full - based on the backup strategy and transaction log growth expectations. These choices affect both performance and recoverability post‑upgrade, so document them clearly before the migration step.
Choosing the Right Upgrade Tool: Wizard vs BCP/DTS
Deciding on a migration tool is often the most critical early choice in an upgrade project. The Microsoft Upgrade Wizard is the go‑to solution for most environments because it automates object migration, applies configuration changes, and verifies integrity. In contrast, bulk copy utilities like BCP or data transformation services (DTS) provide a lower‑level approach that requires manual scripting and validation. Each path has distinct trade‑offs in effort, risk, and control.
The Upgrade Wizard is built around a guided, wizard‑style interface that walks the DBA through every step of the migration. It connects to the source 6.5 instance, collects schema metadata, generates DDL for each object, and then executes the creation scripts on the target 2000 instance. The wizard also copies data, recreates logins, users, and security settings, and even offers to rebuild indexes. Because every operation is scripted behind the scenes, the risk of human error is minimized. Additionally, the wizard performs post‑migration checks that flag missing objects, data inconsistencies, or permission mismatches.
Using BCP or DTS, on the other hand, involves a more manual process. The DBA must first generate a full set of DDL statements by querying the source instance’s catalog. These scripts are then run against the target instance to create the schema. Next, data is extracted from each table using BCP or a DTS package, transferred to the target server, and re‑imported. Each of these steps requires careful ordering: logins must be created before users, tables before indexes, and constraints after data is loaded. Even after a successful import, the DBA must manually verify row counts, foreign key integrity, and security settings. This process is error‑prone and can take significantly longer than the wizard, especially in environments with many objects.
Which tool should you choose? The answer depends on the scope of the migration. If the upgrade involves an entire server with dozens of databases and the goal is a clean, repeatable process, the Upgrade Wizard is usually the most efficient. It handles the majority of the heavy lifting and reduces the likelihood of a post‑migration failure that would otherwise require a roll‑back. When the project is a single database move or a selective set of tables, BCP/DTS can offer more granular control. For example, you might want to migrate only a subset of data based on a date range, or you might need to transform column values during the import. In these cases, DTS’s ability to apply transformations is invaluable.
Another factor is the availability of a clean target environment. The Upgrade Wizard expects a fresh installation of SQL Server 2000, or at least a server that has not yet had a database created. If you must migrate into an existing 2000 instance that hosts other critical workloads, you may prefer BCP/DTS to avoid interfering with existing objects. You can still use the wizard in “partial” mode to migrate a single database, but this requires careful planning to avoid name collisions and permission conflicts.
Performance considerations also influence the choice. In practice, the wizard is not significantly slower than BCP/DTS for most scenarios. The difference in execution time is often offset by the time saved in debugging and verifying the migration. BCP/DTS may appear faster in data movement because it streams data directly, but this advantage disappears when you factor in the time spent preparing scripts, managing dependencies, and performing post‑migration validation.
Regardless of the tool, the DBA should maintain a rigorous test plan. Create a sandbox environment that mirrors production as closely as possible, run the migration, and validate each database’s schema, data, and performance. Only after passing these tests should the migration be scheduled for production downtime. The Upgrade Wizard’s integrated validation steps help expedite this process, but they are most effective when paired with a comprehensive test harness that covers application logic, stored procedure execution, and query performance.
Implementing a Redundant Upgrade Architecture for Safety and Speed
Even the most carefully planned migration can fail due to unforeseen circumstances: a power outage, a corrupted backup, or a network glitch that interrupts the data transfer. A redundant upgrade architecture mitigates these risks by keeping the original 6.5 server alive until the new 2000 environment is fully validated. The approach involves a dedicated migration server, a clear fail‑over path, and a sequence of controlled data movement steps.
The first component is the migration server. Install a fresh instance of SQL Server 2000 on a new machine (or a temporary virtual machine if budget constraints exist). Ensure that this server is fully patched and runs under a dedicated service account with minimal privileges beyond what is required for the upgrade. This isolation guarantees that the migration process does not interfere with other workloads and that any failures remain contained.
Next, prepare the source 6.5 environment. Back up every database, including system databases (master, model, msdb), and store the backups in a secure, off‑site location. Also capture the configuration of logins, users, and server-level settings. These artifacts provide a safety net that can be used to restore the 6.5 instance if the migration fails and a rollback is required.
The migration flow is then divided into three distinct phases: (1) transfer of data and schema to the migration server, (2) validation of the migrated environment, and (3) promotion of the migration server to production. In phase one, use the Upgrade Wizard to copy all databases from the 6.5 instance to the migration server. Because the migration server is isolated, you can run the wizard without affecting production workloads. The wizard will generate DDL, rebuild objects, and copy data. Simultaneously, use BCP or DTS to copy log data and any other auxiliary tables that the wizard might skip.
Once phase one completes, conduct a comprehensive validation. Verify that all databases exist, that user accounts map correctly, and that foreign keys and constraints are intact. Run a representative set of application queries and stored procedures against the migrated databases to ensure that performance meets expectations. Pay particular attention to the critical upgrade decisions - ANSI NULLs, quoted identifiers, and compatibility mode - by running targeted tests that compare output with the 6.5 environment.
Phase three is the promotion step. When the migration server passes validation, schedule a short downtime window for the final cutover. First, synchronize any changes that occurred on the 6.5 instance since the last backup (for example, if a transaction log backup is available, restore it on the migration server). Next, re‑apply the Upgrade Wizard to copy any remaining objects or data that may have changed. Finally, reassign the service account, update connection strings in the application, and point any dependent services to the new 2000 instance. At this point, the original 6.5 server can be decommissioned or repurposed, and the migration server becomes the new production environment.
This redundant architecture offers several advantages. It provides an immediate rollback path: if something goes wrong during cutover, the original 6.5 instance is still running and can continue to serve users. It also reduces downtime because the majority of the migration work occurs on a separate server, leaving production online until the final switch. Moreover, by isolating the migration on its own machine, you avoid the risk of corrupting production data or misconfiguring existing services.
When planning the redundant upgrade, document every step, assign clear ownership, and maintain a change log. Even though the architecture is robust, human error can still happen, so having a clear runbook that outlines what to do at each checkpoint minimizes surprises. Finally, incorporate the upgrade plan into your broader IT change management process, so approvals, communication, and testing are coordinated across all stakeholders. With a redundant upgrade architecture in place, you can confidently transition to SQL Server 2000 while keeping the business running smoothly.
No comments yet. Be the first to comment!