SQL Server Compatibility Mode

  • Article

drive 023

I drew the short straw at the 2008 Launch event last week in Birmingham; speak for one hour on upgrading SQL Server.  I found this very difficult because on the one hand you can usually just do the upgrade, once you have done all the pre-requisite impact analysis and planning and also there isn't a huge  amount to show, before during or after the upgrade.

One point I did want to stress is to get read of all the deadwood and redundant code as part of the exercise.  If you are moving from 2000 to 2005 or have done so already you can leave the newly upgrade database 2000 compatibility mode, and there is even a 2000 compatibility mode in SQL Server 2008 (as well as one for 2005).  Great! just back up that old 2000 database and restore to 2005/8 and relax. I wouldn't do this myself unless there was a third party application that isn't yet 2005 compliant.  Why?

  • It isn't going to run as quickly
  • The longer you leave the proper migration work the less likelihood there is that anyone will know what the code does as they will have moved on.

The reason I do like compatibility mode is that while it is on you can then set up profiler to watch for any events that use this feature.  This can then be used to track down the stuff you need to change..

And why the picture of 25 tons of rubble? Well I am upgrading my drive so I got rid of all the redundant drives (2 of them each 100mm thick!) before installing drive v3 as the new one would crack if laid on top of previously cracked layers.  Like upgrading SQL Server it's boring, but worth it in the end.

Technorati Tags: SQL Server 2008,SQL Server 2000,upgrade,SQL Server 2005