Upgrading from SQL Server 2000 to 2008

by

I put together these short notes a while back while helping with an upgrade.

 

Side-by-Side Upgrade versus In-Place Upgrade

There are two fundamental strategies for upgrading:

  • In-place upgrade: uses the SQL Server 2008 Setup program to directly upgrade a SQL Server 2000 instance to SQL Server 2008. The older SQL Server instance is replaced.
  • Side-by-side upgrade: performs operations to move all or data and other database components from SQL Server 2000 to a separate SQL Server 2008 instance.

In this case, we opted for a side-by-side upgrade, where a backup of the current database on SQL Server 2000 was restored to a clean install of SQL Server 2008 R2 and then upgraded in place.

 

Before upgrade

  • Run the Microsoft SQL Server 2008 Upgrade Advisor for all databases (including system databases). Review any deprecated features identified by the upgrade advisor (e.g. Notification Services, backup with TRUNCATE ONLY etc.) (SQL Server 2008’s TSQL parser is stricter and some things that ran in 2000 might fail in 2008, but it is mostly deprecated TSQL language features anyway)
  • If using a SAN: Benchmark the SAN using SQLIO and capture baseline data before performing the upgrade.
  • Run database/application performance benchmarks
  • Formulate and test a rollback plan
  • Ensure database consistency: Run DBCC CHECKDB on databases to be upgraded to ensure that they are in a consistent state before performing the upgrade.
  • Back up all important files: Back up all SQL Server databases from the instance to be upgraded (including master, model and msdb), data and transaction log files, as well as any application files, script files, extract files, etc. so that you can completely restore them if necessary.)
  • Perform transaction log backup for all user databases and disable all the jobs including jobs for taking full backups, jobs to defrag and other maintenance plans. [It is also recommended to take an image of your database server, in case you will have to rollback.]
  • If the operating system of the server is Windows 2003, ensure Windows service pack 1 is installed. This needs to be done on all the nodes of a cluster in a Failover Clustered environment.
  • Reserve enough disk space: Estimate the disk space required to upgrade SQL Server components, user databases, and any database files that might need to be created during the upgrade process. Might need two to three times the amount of disk space during the upgrade process.
  • Ensure enough space for system databases: Configure system databases (master, model, msdb, and tempdb) to auto-grow during the upgrade process, and make sure that they have enough disk space for this growth.
  • Transfer logins: Ensure all logins are transferred to the master database on the target server before upgrading the database.
  • Disable all start up stored procedures: The upgrade process will usually stop and start services multiple times on the SQL Server instance being upgraded. Stored procedures set to execute on start-up might interfere with the upgrade process.
  • Stop replication: Stop any replication and make sure that the replication log is empty at the start of the upgrade process.
  • Quit all applications: Certain applications, including all services with SQL Server dependencies, might cause the upgrade process to fail if local applications are connected to the instance being upgraded.

After upgrade

  • Review all logs
  • Revisit Upgrade Advisor recommendations
  • Rebuild Indexes
  • Reconfigure Log Shipping (if this was in use)
  • Transfer and verify SQL Agent jobs and maintenance tasks
  • Verify security settings: especially if there any cross server and/or cross-domain access privileges
  • Recreate any full text indexes removed from the databases. Re-populate Full-Text catalogs
  • Update the statistics for all the databases. This step may take some time depending on the size of database.
  • Change compatibility level: After initially restoring SQL Server 2000/2005 databases to SQL Server 2008, the compatibility level will still be 80/90, which corresponds to SQL Server 2000/2005 respectively. To take advantage of the new features of SQL Server 2008, you must change the compatibility level to 100.
  • Rebuild Indexes: To optimize query performance.
  • Update usage counters: In earlier versions of SQL Server, the values for the table and index row counts and page counts can become incorrect. To correct any invalid row or page counts, run DBCC UPDATEUSAGE on all databases following the upgrade.

 

Resources