locked
Compatibility_level on SQL Server 2012 in-place upgrade RRS feed

  • Question

  • Hi all,

    Based on BOL, master and user databases' compatibility_level will not be changed to 110 on SQL Server 2012 in-place upgrade.

    Database Compatibility Level After Upgrade

    The compatibility levels of the tempdb, model, msdb and Resource databases are set to 110 after upgrade. The master system database retains the compatibility level it had before upgrade.

    If the compatibility level of a user database was 90 or 100 before the upgrade, it remains the same after upgrade. If the compatibility level was 80 or less before upgrade, in the upgraded database, the compatibility level is set to 90, which is the lowest supported compatibility level in SQL Server 2012.

    I get the some result in our environment. So in-place upgrade SQL 2008 instance to SQL 2012 (both standalne and clustered), master/user databases' compatibility_level is 100. I also try to use SQL 2012 new function OFFSET and FETCH, it works.

    My concern is, whether we have to manually update the compatibility_level to 110? As SQL 2012 new features seem work even with compatibility_level <110, is there any problem if we leave it?
    (I didn't find any document about this, and related change metioned as a step for SQL 2012 in-place upgrade.)

    Any thoughts?


    MCSE:Data Platform

    Thursday, January 9, 2014 10:50 AM

Answers

  • So the idea is that if you have applications that are dependent on behaviour that changes, you can stay with the old compat level. I believe that there is some upgrade assistant to investigate whether this applies to you.

    On the top of my head, I don't recall any differences between compat levels 100 and 110, but there surely are some. But there are no new reserved keywords as I recall. And some things break even with the old compat level. (COMPUTE BY and the old RAISERROR syntax for instance.) So I don't think it is particularly brave to change the compatibility level. Change it back if something really breaks.

    The risk if you don't change it now, is that you have code developed on a dev server with compat level 110, which breaks when you deploy in production. And this happens one year from now, and no one recalls that this server has compat level 100, so it causes a lot of confusion and waste of time.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Stephanie Lv Friday, January 10, 2014 9:14 AM
    Friday, January 10, 2014 8:33 AM

All replies


  • My concern is, whether we have to manually update the compatibility_level to 110? As SQL 2012 new features seem work even with compatibility_level <110, is there any problem if we leave it?
    (I didn't find any document about this, and related change metioned as a step for SQL 2012 in-place upgrade.)

    Any thoughts?


    MCSE:Data Platform

    Hello,

    Certain database behavior is compatible with Version of SQL Server installed so it is important to change compatibility level to 110 so as to avoid any anomaly.Below article will tell you what you should consider while changing compatibility level

    http://technet.microsoft.com/en-us/library/bb510680.aspx

    Hope this helps


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by Olaf HelperMVP Thursday, January 9, 2014 7:22 PM
    Thursday, January 9, 2014 11:01 AM
  • I would recommend that you change the compat level, unless you know that you use feature which requires the older level.

    Many new features in SQL 2012 works also with compat level 100, but necessarily not all, and there is risk for confusion when things does not work as expected.

    Note that chaning the compat level is not a one-time switch, but it is possible to switch back.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Olaf HelperMVP Thursday, January 9, 2014 7:22 PM
    Thursday, January 9, 2014 11:33 AM
  • Thanks Erland!

    As leaving lower compat level in this case is by design, there should be some reason, which makes me feel a little nervous if i manually change the level to 110 without any verification that it's totally safe to do so.


    MCSE:Data Platform

    • Marked as answer by Stephanie Lv Friday, January 10, 2014 9:05 AM
    • Unmarked as answer by Stephanie Lv Friday, January 10, 2014 9:05 AM
    Friday, January 10, 2014 2:17 AM
  • So the idea is that if you have applications that are dependent on behaviour that changes, you can stay with the old compat level. I believe that there is some upgrade assistant to investigate whether this applies to you.

    On the top of my head, I don't recall any differences between compat levels 100 and 110, but there surely are some. But there are no new reserved keywords as I recall. And some things break even with the old compat level. (COMPUTE BY and the old RAISERROR syntax for instance.) So I don't think it is particularly brave to change the compatibility level. Change it back if something really breaks.

    The risk if you don't change it now, is that you have code developed on a dev server with compat level 110, which breaks when you deploy in production. And this happens one year from now, and no one recalls that this server has compat level 100, so it causes a lot of confusion and waste of time.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Stephanie Lv Friday, January 10, 2014 9:14 AM
    Friday, January 10, 2014 8:33 AM
  • Sorry, marked my reply by mistake!

    All you said is quite fair to me. Scripts are developed at 110 on DEV server, while production server is 100. But one thing i can do now is to change the compat level to 110 on test enviornment, if everything works good, go ahead to production for change.

    Thanks for your input!


    MCSE:Data Platform

    Friday, January 10, 2014 9:14 AM
  • Sorry, marked my reply by mistake!

    All you said is quite fair to me. Scripts are developed at 110 on DEV server, while production server is 100. But one thing i can do now is to change the compat level to 110 on test enviornment, if everything works good, go ahead to production for change.

    Thanks for your input!


    MCSE:Data Platform

    Hello,

    If you see the link which I posted in my first reply It has difference in compatibility level between 100 and 110.That will be surely helpful


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, January 10, 2014 9:20 AM
  • It helps indeed Shanky!

    I'm trying to figure out old styles will be broke out at 110, and see if we have these stuff involved in our existing scripts. I find a detail list here - http://technet.microsoft.com/en-us/library/ms144262.aspx


    MCSE:Data Platform


    • Edited by Stephanie Lv Friday, January 10, 2014 9:38 AM add
    Friday, January 10, 2014 9:30 AM
  • I'm trying to figure out old styles will be broke out at 110, and see if we have these stuff involved in our existing scripts. I find a detail list here - http://technet.microsoft.com/en-us/library/ms144262.aspx

    Note that this list is discontinued feature, so they will break no matter the compat level.

    The sections Breaking Changes and Behaviour Changes just below in the TOC are more interesting.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, January 10, 2014 2:07 PM