Advertisement
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.
