Compatibility Level

Question: On a database audit , I noticed an old compatibility level on a database. If a database is it an older compatibility level  - what does it mean for using the newer features?

Answer: This is a good question because  often on forums and conversation with other DBAs and developers – there is some disagreement on the purpose of database compatibility levels

My general approach is to view database compatibility level as way of allowing SQL Server to behave similar to the older version. It doesn’t restrict you from exploiting new functions and features which are added to new versions

 The Microsoft recommendation is exploit compatibility levels as a temporary measure , allowing the database to be added to a newer version – but continue accessing older features , allowing an opportunity for a migration to the latest compatibility level with the command ALTER DATABASE SET COMPATIBILITY_LEVEL

 As a DBA,  part of the upgrade migration is to assess the impact on code when restoring a database to a newer version. A great tool to assist in reviewing a database is the Upgrade Advisor

A good example of the impact of features is to check the Microsoft site for  Differences Between Compatibility Level 90 and Level 100 – http://technet.microsoft.com/en-us/library/bb510680.aspx

 Read More

SQL Server – Daily Health Check Script with Powershell