Discontinued Features in SQL Server 2012 – Part 2

Previously I discussed a couple of features where there is a discrepancy between the documentation and the implementation as to their status as either discontinued or deprecated in SQL Server 2012. Today, I’m looking at another discontinued feature which you will need to identify and address before upgrading to SQL Server 2012 – database compatibility level 80.

A database’s compatibility level is used by the database engine to ensure that most behavior is consistent to that version for the specific database.  This database-level setting is intended as a work around to allow applications to upgrade to a newer version of the database engine for which usually all databases can derive some benefits without requiring the application to make any changes (in theory). If an individual application database is using a feature or syntax previously supported for which the behavior changed in the later version, then you can set a down-level compatibility to keep the original behavior from that version….until your application development team or your vendor can make the code compatible with the current version of SQL Server.  

Beginning with SQL Server 2008, Microsoft’s announced life cycle policy for SQL Server was that a new version will attempt backwards compatibility with only the 2 previous versions. Since databases in SQL Server 2012 are compatibility level 110, then only compatibility level 100 (used for both SQL Server 2008 and SQL Server 2008 R2) and compatibility level 90 (SQL Server 2005) are additionally supported.  Thus, database compatibility level 80 (SQL Server 2000) will not be supported in SQL Server 2012 and is therefore classified as a discontinued feature.

If you are a fairly new DBA who inherited your database systems and don’t know their history, you’d better check all the databases carefully for compatibility level.  While SQL Server 2008 and SQL Server 2008 R2 both support databases in 80, 90, and 100 compatibility mode, SQL Server 2005 supports databases in 70, 80, and 90 compatibility modes (and allows 60 & 65!).  Thus, although you can upgrade a SQL Server 2005 instance to SQL Server 2012 – you need to check that all of the application databases are at compatibility level 90. You might be surprised (or not) to find that you still have databases which exist with compatibility level 6x on your SQL Server 2005 instances! 

So, how do you find these? One way is to use a multi-server query using a CMS group containing all your servers…

Select [name],[compatibility_level] From sys.databases

Where [compatibility_level]< 90;

How do you fix it? Well, ultimately you have to run one of these commands:

— If source system is SQL 2005

ALTER DATABASE reallyold_database

SET COMPATIBILITY_LEVEL = 90;

— If source system is SQL 2008 or 2008 R2 and compatibility_level is 80,

— then you might as well take it as far as you can go!

ALTER DATABASE reallyold_database

SET COMPATIBILITY_LEVEL = 100;

But, first you’ll have to figure out what is keeping it from being at the higher compatibility level already.  Check the SQL Server documentation under “ALTER DATABASE Compatibility Level (Transact-SQL)” for details of differences between compatibility levels. Be sure to check the SQL Server 2008 version of the documentation for differences between the 80 and 90 compatibility levels. In some cases, it is simply that the database was upgraded and the former DBA didn’t realize that they needed to change the compatibility level after the upgrade.  And, of course, don’t you now forget to change upgraded databases to compatibility level 110 after you complete the upgrade to SQL Server 2012!