Migrate 2000 -> 2005

I've got the responsibility over a small SQL server with 8 Database and approx. 100GByte data online. Most database are used and need to be available 24*7*366. Also a number of local data transformation packages are defined and used on a regular basis. Furthermore a number of third party applications rely on this database.

My question if it is worth the effort to migrate this to 2005? I know there are all kinds of nice migration wizards but do they really work? What if one of the 3rd party apps don't like 2005?

Love to hear both good and bad experiences migrating -> 2005 .

1. SQL Server 2005 can be installed side by side with SQL Server 2000 successfully. So you can install SQL Server 2005 on the same machine where SQL Server 2000 is installed. But make sure you choose a different instance name for SQL Server 2005. Otherwise, setup will performs upgrading. After you install SQL Server 2005, you can transfer data from SQL Server 2000 to SQL Server 2005. At the same time, your SQL Server 2000 is not affected. After you think SQL Server 2005 runs well and stable, you can switch to SQL Server 2005. If required, you can uninstall SQL Server 2000.

2. You can upgrade SQL Server 2000 to SQL Server 2005 directly. During setup, make sure you choose the same instance name of SQL Server 2000 for SQL Server 2005. Follow the UI, which is very simple.

3. Or you uninstall SQL Server 2000 and saved database, followed by installing SQL Server 2005 with attaching the saved database.

Among, these three approaches, the first one is prefered if your machine resource is not an issue, which is pretty safe. At the same time, you will not affect all application depending on SQL Server 2000.

Before you do any major operations, please back up your database first.

|||

Thanks Chen, did not know 2000 and 2005 can run side by side but this will greatly increase the possibility of a migration. I don't like the idea of an upgrade only to find out that one of the zillion applications doesn't like 2005 and not being able to rollback :-) Still one question: can I attach the 2000 database devices (mdf, ndf and ldf) to a 2005 directly? Or should we go through the full backup/restore route?

What will happen to the data translation packages? 2005 doesn't have data translation packages so we will not be able to modify them.