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

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.)