Sql-server – Changing Compatibility level from 90 to 120 in sql 2014 migration

compatibility-levelsql-server-2008-r2

I have SQL 2008R2 server to migrate to SQL2014 Server.

A side by side upgrade is what we are thinking for few databases in SQL 2008R2 which are having compatibility level of 90 or 80.

Can I migrate these databases (back& restore) to SQL2014 and sql will allow me to change compatibility level to 120 ? if not how can i deal with this?

thanks

Best Answer

Yes you can migrate, but that totally depends upon the results of testing you have done pre-migration. That is , you can test the application on test-staging server before you make any changes in production. As per the results you may get a go-head.

Also, please refer to this MSDN article, to understand the difference between various compatibility levels in SQL server.Therefore you will need to go what has been changed since OLD Compatibility versions till now and how will it behave when you change to new.

As mentioned :

When a stored procedure executes, it uses the current compatibility level of the database in which it is defined. When the compatibility setting of a database is changed, all of its stored procedures are automatically recompiled accordingly.

Also, running upgrade advisor will be helpful during you're migration strategy which shall let you know for any issues, so that they can be looked after accordingly.

In addition you can read Bad habits : Clinging to old compatibility levels and What is the actual behavior of compatibility level 80? from Aaron.

Related Question