Before and After

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.