Over the last 2 days I had the opportunity to remove the SQL 2000 database out from underneath our Web Service and replace it with SQL 2005. The team was hoping to see an instant gain in SQL performance but that’s not what happened. Performance tests that put the SQL 2000 server at a comfy 69% CPU usage were now using 81% with SQL 2005. Bummer. Where’s the magic Microsoft? Keep in mind we made ZERO changes to the Web Service or SProcs for the test.
We’re not going to give up hope on gaining performance with SQL 2005, the move is just going to be slower for us now since we obviously need to uncover the magic and finding that magic is going to take some work. During this SQL upgrade test I learned a few interesting things that I suppose I should share with you…
- I couldn’t successfully restore a SQL 2000 database backup to my database in SQL 2005. I had a few errors thrown at me but this is one that I documented: “Restore failed for Server ‘TEST-SQL1’. (Microsoft.SqlServer.Smo), Additional information: System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing “TEST” database. (Microsoft.SqlServer.Smo)”
- The SQL2000 database couldn’t be attached in SQL2005 and used successfully. For some reason, when I attached my 1 million+ entry database my largest table was empty?!
- Does attaching to a 2000 .MDF in 2005 migrate the data? According to a forum post by an engineer on the MS SQL 2005 development team, it does migrate the system tables but he says nothing about the custom tables. I couldn’t find any other info on the web on how to migrate data other than using the attach feature as described by ASPFree.com. Microsoft has an upgrade advisor tool but it doesn’t upgrade anything it just does analysis.