1

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?

Improve this question
4
  • 1
    When you say "will only work with SQL 2008R2", do you mean something breaks or is it that later versions are not supported by the vendor?
    – Dan Guzman
    Feb 6, 2018 at 15:19
  • Later versions not supported by the vendor. They say a whole application update would be required. So I'm wondering if the Compatibility Level setting can get around this.
    – Kevin
    Feb 6, 2018 at 15:19
  • 1
    Compatibility level 100 should do it, but your best bet would be to set up a staging environment with the specs you're mentioning on the new 2014/2016 box and do some validation prior to making the full jump in production. Just be weary, as the first thing the vendor might do is run SELECT @@VERSION when being asked for support.
    – SQLDevDBA
    Feb 6, 2018 at 15:20
  • 2
    @Kevin, you'll probably be ok but the vendor might not support that configuration unless they specifically say so. They will probably know more than us about that.
    – Dan Guzman
    Feb 6, 2018 at 16:55

1 Answer 1

Reset to default
2

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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.