none
some question after running Data Migration Assistant

    Question

  • as was advised here on forum I run Data Migration Assistant against current SQL.

    This allows to see Compatibility issues between Existent old DBs and future releases of SQL.

    Current SQL server has 2005 instance with 30 small DBs.

    There are couple of very old DBs that are at compatibility Level 80 (SQL 2000).

    There are no plans now for moving all these DBs.

    I prepared a fresh SQL 2012 and at this time only one DB will be imported from old server for merging with a new app DB that will use DB on 2012.

    From Data Migration Assistant report I can conclude that the same compatibility issues do exist in all releases above 2005.

    I thought about implementing SQL 2016 but when checked Supported Compatibility Level Values I thinks that staying with SQL 2012 will be more appropriate in case of a need to transfer 2005 (Level 90) DBs.

    And that's because SQL Server 2016 Supported Compatibility Level starts from 100 (that is server 2008) - Levels 100 -140.

    However, SQL 2012 supports 90, 100 and 110. So SQL 2005 (level 90) does supported.

    Am I right in doing this assumption?

    And one more...

    Could DB of SQL 2005 (level 90) converted for use on server 2016 (level130)?

    Thanks.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis



    • Edited by pob579 Monday, February 20, 2017 3:39 PM
    Monday, February 20, 2017 3:36 PM

Answers

  • Yes, SQL Server 2012 supports CL 90, 100 or 110:

    It should be possible for you to take a SQL2005 backup and restore it to SQL2016 instance:
    You can upgrade from any version 2005+ to any other version

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Monday, February 20, 2017 3:57 PM
    • Marked as answer by pob579 Monday, February 20, 2017 4:40 PM
    Monday, February 20, 2017 3:50 PM
  • Phil,

    could you answer the last question:

    Could DB of SQL 2005 (level 90) converted for use on server 2016 (level130)?

    Or what is usually performed in a situation when there is a need to use latest SQL server without missing old DB's data?

    Thanks.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    • Marked as answer by pob579 Monday, February 20, 2017 4:39 PM
    Monday, February 20, 2017 3:55 PM

All replies

  • Yes, SQL Server 2012 supports CL 90, 100 or 110:

    It should be possible for you to take a SQL2005 backup and restore it to SQL2016 instance:
    You can upgrade from any version 2005+ to any other version

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Monday, February 20, 2017 3:57 PM
    • Marked as answer by pob579 Monday, February 20, 2017 4:40 PM
    Monday, February 20, 2017 3:50 PM
  • Phil,

    could you answer the last question:

    Could DB of SQL 2005 (level 90) converted for use on server 2016 (level130)?

    Or what is usually performed in a situation when there is a need to use latest SQL server without missing old DB's data?

    Thanks.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    • Marked as answer by pob579 Monday, February 20, 2017 4:39 PM
    Monday, February 20, 2017 3:55 PM
  • Yes, I edited my first post with that additional info you requested.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Marked as answer by pob579 Monday, February 20, 2017 4:39 PM
    • Unmarked as answer by pob579 Monday, February 20, 2017 4:39 PM
    Monday, February 20, 2017 3:58 PM
  • Thanks Phil! Very informative Blog in your link.

    So it's practically eliminates a worry about implementing SQL server at the latest possible version - SQL 2016.

    I got answers about all advantages of going with a latest version in another thread.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis


    • Edited by pob579 Monday, February 20, 2017 4:44 PM
    Monday, February 20, 2017 4:39 PM