Moving SQL2005 app to SQL2008

Question

I will be moving our production SQL2005 application to SQL2008 soon. Any things to lookout for before/after the move? Any warnings, advices?

Thank you!

Answer 1

Check this article for known breaking changes.

Answer 2

The upgrade adviser can also help.

Look at the execution plans with production data in the database.

Though my best advice is to test, test, test.

When people started moving from 2000 to 2005 it wasn't the breaking features that were show stoppers it was the change in how queries performed with the new optimizer.

Queries that were heavily optimized for 2000 now performed poorly or even worse erratically leading people to chase down non-problems and generally lowering the confidence of the end users.

Answer 3

Change your compatibility level on the database after moving it to the 2008 server. By default, it will still stay at the old compatibility level. This will let you use the new goodies in SQL 2008 for that database.

If you're using the Enterprise Edition of SQL 2008 and you're not running at 80-90% CPU on the box, turn on data compression and compress all of your objects. There's a big performance gain on that. Unfortunately, you have to do it manually for every single object - there's not a single switch to throw.

If you're not using Enterprise, after upping the compatibility level, rebuild all of your indexes. (This holds pretty much true for any version upgrade.)