SQL Server – How to Migrate Database and Set Compatibility Level

compatibility-levelmigrationsql server

We are planning on migrating from SQL Server 2008R2 to SQL Server 2014 (or 2016).

One 3rd party application is not supported after version 2008R2.

Is it OK to migrate this application database to 2014, and then change the Compatibility Level of that database to 100?

Is changing the compatibility level to 100 in effect the same as having it on SQL Server 2008R2? Or are there some potential issues?

Best Answer

In general, it is not advisable to run a database in an older compatibility mode permanently, and the relevant Microsoft documentation from ALTER DATABASE Compatibility Level (Transact-SQL) is:

Use compatibility level as an interim migration aid to work around version differences

So the best option is to avoid doing it if possible.

Having said that, in the real world, we have ridiculous numbers of databases out there on our servers, and security concerns are pressuring us to get off of older versions of SQL Server and Windows. Meanwhile, the application vendor is out of business, or the organization won't upgrade it because it is planning to retire the application (and maybe has been planning to do that for the past few years), or it would just cost too much to upgrade it, etc. So I think it is not a rare situation where databases are being run permanently in an older compatibility mode. Limit your risk by:

  • Thoroughly testing the solution
  • Maybe don't do this with a database that has a massive amount of code, a lot of users, or a high rate of transactions
  • Advise management of the risk
Related Question