Migration from SQL Server 2000 to 2005

I have a fairly large web application that was built with SQL Server 2000 and .NET 1.1. We're planning on moving to .NET 2.0 eventually, but there's a lot of work to be done before that happens. In the short term, we're looking to purchase a new database server and would like to upgrade to SQL Server 2005 at the same time. Is it as simple as recreating our databases on this new system and changing the connection string, or are there other gotchas? As far as what we're doing with SQL Server 2000, it's really nothing more than tables, views, and stored procedures. Thanks in advance for any advice.

Before the migration, consider the following issues.

1. What is the Edition of SQL Server 2000?

2. What is the Edtiion of SQL Server 2005 you want to upgrade to?

3. What is the processor architecture? If it is IA64, then you must upgrade to 64-bit. If it is x64, then SQL Server 2000 has only 32-bit builds. At the same time, SQL Server 2005 has 64-bit builds. You cannot upgrade 32-bit SQL Server 2000 to 64-bit SQL Server 2005 directly.

4. What is the operating system running SQL Server 2000? If SQL Server 2005 editions requires a different operating system, is it acceptable to upgrad the operating system? For example, SQL Server 2005 Enterprise edition requires server operating systems such as Windows 2000 Advanced Server 2004, Windows Server 2003 Enterprise Edition SP1, and the coming Longhorn server.

5. Any special business and applicaiton logic?

Please also download the latest BOL from MSDN website for detailed information.

Once you decide to migrate, try side by side upgrade instead of in-site upgrade. SQL Server 2000 can be upgraded to SQL Server 2005 directly (if editions and processor architecture builds match). At the same time, SQL Server 2000 and SQL Server 2005 can be installed side by side successfully on the same machine if the resources allow. If resources allow, please follow the following recommendation.

1. Install SQL Server 2005 side by side with SQL Server 2000, i.e., not direct upgrade.

2. While SQL Server 2000 is still running, i.e., your application is still running, duplicate/copy data from SQL Server 2000 to SQL Server 2005.

3. Tune SQL Server 2005 and test SQL Server 2005.

4. When you think SQL Server 2005 is tested well and works well, switch to SQL Server 2005.

5. You may uninstall SQL Server 2000. This is optional.

Anyway, please back up your database before performing any major operations like this.

|||Well, luckily we'll be buying a new machine to eventually replace our existing database server. So we don't need to worry about actually "upgrading" anything currently in production. We'll just be migrating the actual databases to the new system.

Our new system will be x64 running Windows Server 2003 and SQL Server 2005.
|||

Another great source of information in the Upgrade Portal

http://www.microsoft.com/sql/solutions/upgrade/default.mspx

This has pointer to lots of documents on Upgrading and it also points you to the Upgrade Advisor. If you run this tool against you SQL 2000 DB, SQL Batch Files and a SQL Trace file it can help determine if there is any potential incompatibilities.

Thanks

Michelle