locked
SQL Server Upgrade Advisor 2014 - Database Compatibility Level checking RRS feed

  • Question

  • I have the following scenario... I have a database server running SQL Server 2008 R2 (10.50.4260). On that server I restored a database from SQL Server 2005.  Looking at the compatibility level through the properties of the database (using SSMS) and by querying against sys.databases, I can see the database has the compatibility level of 90, which is what I expected.

    In looking to bring this database over to a SQL Server 2014 database server, I have run the SQL Server Upgrade Advisor 2014 tool as well as the SQL Server Upgrade Advisor 2012 tool.  With the 2014 tool, my expectation would be that it would warn me that the compatibility level will be upgraded to 10 (or something), however, I received no such warning.  I ran the 2012 version of the tool as well, expecting that I might get a deprecation warning (or something), no such luck.  I have run the 2014 tool from my computer and from another one, same results.

    Does the upgrade advisor not check for the compatibility level? Or does my configuration maybe have something to do with it (having it on a 2008 R2 server)?  From looking at screenshots of when other people have ran the tool, I was under the impression it checked this. See the link below for an example...

    http://blogs.msdn.com/b/mspfe/archive/2012/12/06/sql-server-upgrade-advisor-considerations-when-upgrading-from-sql-2000-to-sql-2012.aspx

    Thoughts anyone?

    Tuesday, March 3, 2015 8:29 PM

Answers

  • Lydia,

    I agree with regards to the 2012 version.  However, in SQL Server 2014, upgrading a database with a compatibility level of 90 sets the database to compatibility level 100. See link below...

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

    Shouldn't the 2014 version of the advisor tool issue the same warning seen in the screenshot of the msdn blog post we both mentioned?  As in... "After upgrade, compatibility levels <list of levels>, will be set to 100".



    Hi JJordheim,

    According to my test, SQL Server 2014 Upgrade Advisor also only raise warning when database compatibility level is less than 90 in the “SQL Server Parameters” page.

    From your description, when database compatibility level is set to 90, SQL Server 2014 Upgrade Advisor should also raise warning, right? If so , this is a design issue of SQL Server 2014 Upgrade Advisor, personally, I recommend you submit a feedback to the Microsoft Connect at this link https://connect.microsoft.com/SQLServer/Feedback . Your feedback enables Microsoft to offer the best software and deliver superior services.

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    • Marked as answer by JJordheim Thursday, March 5, 2015 3:18 PM
    Thursday, March 5, 2015 8:47 AM
  • Thanks Lydia, I did so... 

    https://connect.microsoft.com/SQLServer/feedback/details/1162550

    • Marked as answer by JJordheim Thursday, March 5, 2015 3:18 PM
    Thursday, March 5, 2015 3:18 PM

All replies

  • Hi JJordheim,

    Based on my test, SQL Server 2014 Upgrade Advisor and SQL Server 2012 Upgrade Advisor only raise warning when database compatibility level is less than 90 in the “SQL Server Parameters” page. And I cannot go ahead as the “Next” button is grayed out. Below are my screenshots.

     

    As per the blog (http://blogs.msdn.com/b/mspfe/archive/2012/12/06/sql-server-upgrade-advisor-considerations-when-upgrading-from-sql-2000-to-sql-2012.aspx), SQL 2012 Upgrade Advisor (also SQL 2014 Upgrade Advisor ) requires the database to be at a compatibility level of 90 or greater. That is to say, when database compatibility level is 90 or greater, SQL Server Upgrade Advisor will not raise warnings in the “SQL Server Parameters” page and it will not prevent you to check for the database.


    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Wednesday, March 4, 2015 8:57 AM
  • Lydia,

    I agree with regards to the 2012 version.  However, in SQL Server 2014, upgrading a database with a compatibility level of 90 sets the database to compatibility level 100. See link below...

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

    Shouldn't the 2014 version of the advisor tool issue the same warning seen in the screenshot of the msdn blog post we both mentioned?  As in... "After upgrade, compatibility levels <list of levels>, will be set to 100".


    • Edited by JJordheim Wednesday, March 4, 2015 2:52 PM
    Wednesday, March 4, 2015 2:51 PM
  • Lydia,

    I agree with regards to the 2012 version.  However, in SQL Server 2014, upgrading a database with a compatibility level of 90 sets the database to compatibility level 100. See link below...

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

    Shouldn't the 2014 version of the advisor tool issue the same warning seen in the screenshot of the msdn blog post we both mentioned?  As in... "After upgrade, compatibility levels <list of levels>, will be set to 100".



    Hi JJordheim,

    According to my test, SQL Server 2014 Upgrade Advisor also only raise warning when database compatibility level is less than 90 in the “SQL Server Parameters” page.

    From your description, when database compatibility level is set to 90, SQL Server 2014 Upgrade Advisor should also raise warning, right? If so , this is a design issue of SQL Server 2014 Upgrade Advisor, personally, I recommend you submit a feedback to the Microsoft Connect at this link https://connect.microsoft.com/SQLServer/Feedback . Your feedback enables Microsoft to offer the best software and deliver superior services.

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    • Marked as answer by JJordheim Thursday, March 5, 2015 3:18 PM
    Thursday, March 5, 2015 8:47 AM
  • Thanks Lydia, I did so... 

    https://connect.microsoft.com/SQLServer/feedback/details/1162550

    • Marked as answer by JJordheim Thursday, March 5, 2015 3:18 PM
    Thursday, March 5, 2015 3:18 PM