locked
Impact of changing compatibility level RRS feed

  • Question

  • We have a few databases that we will migrate from SQL Server 2005 to SQL 2012 but want to retain functionality of the old application. Is it possible to change the compatibility level of such databases without breaking the exisiting legacy applications?

    any help would be great

    Friday, July 6, 2018 5:03 AM

Answers

  • We have a few databases that we will migrate from SQL Server 2005 to SQL 2012 but want to retain functionality of the old application. Is it possible to change the compatibility level of such databases without breaking the exisiting legacy applications?

    any help would be great

    As they say, it depends.

    When you upgrade to SQL 2012, there are possibilities:

    1) You change the compat level to 110, everything works like before and everyone is happy.
    2) You change the compat level 110, things start to break, but when you change the compat level to 90 happiness is back in town.
    3) No matter what you set the compat level to, things remain broken.

    It all depends on what the application uses.

    On the top of my head, I can recall one thing that falls into the second point and that is if you have an object (table, column, stored procedure etc) called Merge and it is used without being enclosed in brackets or quotes. This is because MERGE is now a reserved keyword.

    Features that could put you in the last point on the list are:
    1) Old syntax of RAISERROR without parentheses.
    2) COMPUTE BY clause.
    3) HTTP endpoints.
    4) Old-style outer joins.

    I believe that there is an upgrade assistance that can give you some guidance, but in the end you need to test.

    By the way, SQL 2012 is a version that is already out of mainstream support, so you are aiming a little low.

    • Marked as answer by Sonia_73 Tuesday, July 10, 2018 1:38 PM
    Friday, July 6, 2018 8:38 AM
  • 1. Yes, you can retain the compatibility level for a database as 90 (after being moved to 2012) so that it continues as if it were on 2005. 

    2. Note that not all behaviors can be retained by changing the compatibility level.

    3. You can still continue to use any new features that are offered while being on compatibility level 90. 

    P.S. don't rely on compatibility level, instead, make changes to the application so it works on the newer compatibility level. The features/options that currently work for you may not work when you move to an even higher version.

    Hope this helps!


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Marked as answer by Sonia_73 Tuesday, July 10, 2018 1:39 PM
    Friday, July 6, 2018 5:15 AM
  • We have a few databases that we will migrate from SQL Server 2005 to SQL 2012 but want to retain functionality of the old application. Is it possible to change the compatibility level of such databases without breaking the exisiting legacy applications?

    any help would be great

    When you change compatibility level its highly unlikely that legacy app will break, some features might start failing because of the unsupported or removed functionality in new version. Even if something breaks you can quickly change it back to 90 so this is very easy task but make sure you read below thread before proceeding.

    https://dba.stackexchange.com/questions/210646/sql-server-upgrading-compatibility-level-from-2005-to-2012/211127#211127


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    • Edited by Shanky_621MVP Friday, July 6, 2018 6:51 AM
    • Marked as answer by Sonia_73 Tuesday, July 10, 2018 1:39 PM
    Friday, July 6, 2018 6:50 AM

All replies

  • 1. Yes, you can retain the compatibility level for a database as 90 (after being moved to 2012) so that it continues as if it were on 2005. 

    2. Note that not all behaviors can be retained by changing the compatibility level.

    3. You can still continue to use any new features that are offered while being on compatibility level 90. 

    P.S. don't rely on compatibility level, instead, make changes to the application so it works on the newer compatibility level. The features/options that currently work for you may not work when you move to an even higher version.

    Hope this helps!


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Marked as answer by Sonia_73 Tuesday, July 10, 2018 1:39 PM
    Friday, July 6, 2018 5:15 AM
  • We have a few databases that we will migrate from SQL Server 2005 to SQL 2012 but want to retain functionality of the old application. Is it possible to change the compatibility level of such databases without breaking the exisiting legacy applications?

    any help would be great

    When you change compatibility level its highly unlikely that legacy app will break, some features might start failing because of the unsupported or removed functionality in new version. Even if something breaks you can quickly change it back to 90 so this is very easy task but make sure you read below thread before proceeding.

    https://dba.stackexchange.com/questions/210646/sql-server-upgrading-compatibility-level-from-2005-to-2012/211127#211127


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    • Edited by Shanky_621MVP Friday, July 6, 2018 6:51 AM
    • Marked as answer by Sonia_73 Tuesday, July 10, 2018 1:39 PM
    Friday, July 6, 2018 6:50 AM
  • We have a few databases that we will migrate from SQL Server 2005 to SQL 2012 but want to retain functionality of the old application. Is it possible to change the compatibility level of such databases without breaking the exisiting legacy applications?

    any help would be great

    As they say, it depends.

    When you upgrade to SQL 2012, there are possibilities:

    1) You change the compat level to 110, everything works like before and everyone is happy.
    2) You change the compat level 110, things start to break, but when you change the compat level to 90 happiness is back in town.
    3) No matter what you set the compat level to, things remain broken.

    It all depends on what the application uses.

    On the top of my head, I can recall one thing that falls into the second point and that is if you have an object (table, column, stored procedure etc) called Merge and it is used without being enclosed in brackets or quotes. This is because MERGE is now a reserved keyword.

    Features that could put you in the last point on the list are:
    1) Old syntax of RAISERROR without parentheses.
    2) COMPUTE BY clause.
    3) HTTP endpoints.
    4) Old-style outer joins.

    I believe that there is an upgrade assistance that can give you some guidance, but in the end you need to test.

    By the way, SQL 2012 is a version that is already out of mainstream support, so you are aiming a little low.

    • Marked as answer by Sonia_73 Tuesday, July 10, 2018 1:38 PM
    Friday, July 6, 2018 8:38 AM