Compatibility level 90 is deprecated in MS SQL 2014

As we are going to use MS SQL 2014 in near future, compatibility level setting is one of the important thing we should know. Especially when you are migrating SQL from older version we should set this compatibility level properly otherwise your database migration will be a flop.

In MS SQL 2014 Microsoft declared that compatibility level 90 is deprecated.

What it means for the DBA or a developer?

To know the answer for the above question first you should know what is compatibility level. I will give you a short table below to understand how it is related to each version MS SQL.

Compatibility Level	MS SQL Server Version	Applies to
80	SQL Server 2000	SQL Server 2008 through SQL Server 2008 R2
90	SQL Server 2005	SQL Server 2008 through SQL Server 2012
100	SQL Server 2008 and SQL Server 2008 R2	SQL Server 2008 through SQL Server 2014
110	SQL Server 2012	SQL Server 2012 through SQL Server 2014
120	SQL Server 2014	SQL Server 2014 through SQL Server 2014

 

Now you might be clear what is this compatibility level all about and why Level 90 is deprecated in SQL 2014.

When Microsoft says compatibility level 90 is deprecated which means when you are migrating from SQL server 2005 or older version to SQL server 2014 you have to change the compatibility level from 90 to 100, that is the minimum compatibility level SQL 2014 supports.

Once you install SQL server 2014 then the default compatibility level will be set it as 120. Where as when you migrate your database from older version database retains its existing compatibility level if it is at least 100, which means it retains its compatibility level if the database you are migrating is SQL 2008 or above.

So here comes the catch. What will happen if you are migrating SQL server 2005 or older version. In such cases we have to change the compatibility level to 100 manually.
Microsoft recommends below steps to change the compatibility level,

 

1. Login to the database and set the database to single-user access mode by using ALTER DATABASE SET SINGLE_USER.
   
2. Change the compatibility level of the database.
   

   ALTER DATABASE AdventureWorks2012
   SET COMPATIBILITY_LEVEL = 100;
   GO

3. Put the database in multiuser access mode by using ALTER DATABASE SET MULTI_USER.

Other option is login to the database. Select the DB you wanted to change the compatibility level and then right click to open the properties.

Click on properties and then select Options. Scroll down to change the Database to Single User mode and then click OK.

Now click on the Compatibility level dropdown and select the preferred compatibility level. Once you change the compatibility level then scroll down again and change the database to multi user mode.
You are done now!!!

I would like to offer you further reading in this regard too, here is the MSDN link related to the same topic.

I hope you enjoyed reading this and learnt something interesting.