Upgrading from SQL2000 database to SQL Express 2008 R2

Question

We have a web application which uses a MSSQL 2000 backend database. We are currently paying a ridiculous amount for Shared Hosting, with the database costs alone costing us $150 per month (MSSQL 100mb extra space is $40 per month). Our database size is 896.38 MB

I am looking at getting a Virtual Private Server and upgrading the database to a MSSQL2008 Express database.

I am aware that the Express version is limited to a 10GB database (with R2), and is constrained to a single CPU. I have also been offered SQL Server 2008 Web Edition for $19/per month, but I cannot find many details on the difference between Express and Web. Any suggestions here?

What I would also like to know is: If we upgrade the database to MSSQL 2008 database, is there any issues with possible data transformations in the future? I.e. Is it possible to download and mount it with SQL Server 2008 Standard edition? I'm more concerned about how to get data in and out of the database through SQL Management tools.

Are there any other issues that I might face?

Thanks, Mike

Answer 1

To quote MSDN:

In SQL Server 2008, you can restore a database backup that was created by using SQL Server 2000, SQL Server 2005, or SQL Server 2008. However, backups of master, model and msdb that were created by using SQL Server 2000 or SQL Server 2005 cannot be restored by SQL Server 2008. Also, SQL Server 2008 backups cannot be restored by any earlier version of SQL Server.

So, yes, you can backup a SQL2000 database and restore it to SQL2008, the only caveat being the master, model and msdb cannot be transferred this way, so you will not be able to migrate those. Thats probably not going to be an issue though - on shared hosting you are unlikely to have access to master, model, and msdb anyway.

However, if you need to migrate back to your SQL2000 DB from SQL2008 you will find that more difficult as the 2008 backup file will not restore on 2000. You are probably best to ensure you only need a "one way" migration path, and completely avoid the need to rollback the migration.

Regarding comparing Web and Express, refer here. Expanding the "Scalability & Performance" node shows that with Web Edition you get up to 4 CPU's, as much RAM as you like, and DB size up to 524PB (thats PetaBytes!) So Web Edition should last you for a while once you outgrow Express.