Before you upgrade your SQL Server 6.5 system to SQL Server 2000, you need to prepare. Here are the upgrade-preparation essentials:

  • If your database is on a server that's running Windows NT 4.0, make sure that Service Pack 4 (SP4) or later is applied.
  • Ensure that your SQL Server 6.5 database has at least SP5 for an in-place upgrade or SP3 if you're upgrading to another server.
  • Run Database Consistency Checker (DBCC) utilities to check database consistency, and make any needed repairs.
  • Make full backups of each user database plus master, model, and msdb.
  • Ensure that you have available disk space (formatted and recognized by the OS) equivalent to 1.5 times the space required for the SQL Server 6.5 databases.
  • Ensure that you have 200MB of free space on the system drive, where the \program files directory and the shared executables are located.
  • Increase tempdb from 50MB to 100MB; remove it from RAM if that's where it is.
  • Increase the master database by 10MB.
  • Increase pagefile size to equal physical RAM plus 16MB for the Upgrade Wizard.
  • Generate SQL scripts for each user database; include all database and security objects.
  • Break replication; remove all subscribers, publications, and publishers (after making notes about the replication topology and publications); remove all replication jobs.
  • Ensure that all SQL Server service accounts (SQL Executive, SQL Agent) are set up as domain accounts and are members of the local Administrators group.
  • Deactivate any stored procedures that are set to run at database startup.
  • Drop all constraints, including declarative referential integrity (DRI).
  • Drop all indexes, both nonclustered and clustered.

After the upgrade, you'll need to take the following steps:

  • Redefine clustered and unique indexes.
  • Redefine nonclustered indexes.
  • Redefine constraints.
  • Redefine replication publishers and subscribers.
  • If appropriate, reduce tempdb.
  • Apply SP1.
  • Make an installation backup of each user database plus master, model, and msdb.
  • Test all stored procedures to ensure that they work as expected.
  • Migrate security to the new model.
  • Use Windows authentication.
  • Use fixed roles for administrator tasks.
  • Create roles, assign permissions to them, then add members to each role.
  • Investigate SQL Server 2000's new features, and integrate them into your system as appropriate.