Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server Compatibility Mode

SQL Server Compatibility Mode

  • Comments 1
  • Likes

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.

Comments
  • You mention in your article '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.'. Can you provide any more information on this? We've a number of SQL 2000 databases we want to migrate but we will be running them in compatability mode to start with.

    I see there is a deprecation section in the events available to be tracked in profiler.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment