Sql Server compatibility mode

Question

We're currently running a server on Compatability mode 8 and I want to update it.

• What are the implications of just going in and changing it?
• What is likely to break?
• Is there anything that checks the data will survive before I perform it?
• Can I rollback to mode 8 without performing a restore and without loss of data?

Answer 1

Compatability mode disables the features of the newer version, personally I haven't really worked with many databases that have issues, the key thing that was a problem in our environment is after moving to 9, you can no longer use Enterprise Manager to view the database.

A backup/restore is a good option, and I also believe you can flip it back without issue.

Answer 2

If you're going from 80 to 90, the differences are minimal. Going from 65 to 70+ can cause severe impact (NULLs are stored differently).

Implications - your SPs can return different results than you'd expect Likely to break: functions, SPs Data should survive; nothing in there should affect things.
Moving from 80 to 90 and back only takes a few seconds. Yes, you can move back and forth.

http://msdn.microsoft.com/en-us/library/bb510680.aspx

some gotchas: http://mapamdug.blogspot.com/2006/03/sql-server-2005-gotcha-1.html

Answer 3

1. Compatibility mode does not affect storage. It's just a flag. Nothing will change in the data or queries. Only query execution will get affected.
2. Nothing - or lots of things. Did you use syntax marked as obsolete and subject to deletion in 2000? Did you use parethesis when providing hints in queries? Did you use query execution hints? If yes, it's better to revise your database first, remove obsolete syntax, put the parenthesis back and dig the BOL to find which hints are going to slow down your fine-tuned query on new engine.
3. No. But the data will survive. In fact, if you are able to run your database on server2005, even in mode 8, you're using new data format already.
4. Yes, you can roll back. It's not transforming, it's just setting a flag which says "My queries are that compatible."

Answer 4

(I did say it was only if you were moving from 6.5, which stored nothing in char() fields when NULL - 70 and greater use the whole of the field, which can cause massive size changes)

VBStreets is right on his points - and definitely on point 3 - when you first ran the database on 2005 it converted the data structure. If you take a backup, it cannot be restored on prior versions, regardless of the compatability level.