Compatibility Level

29 January,2014 by Jack Vamvas

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

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Compatibility Level


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer