Old compatibility Database on newer version of SQL server

  • Hi All,

    SQL server 2008 databases moved (upgraded) on SQL server server 2017 , but because of application dependency compatibility level is not changed it is same 2008 (100) .

    So whether Microsoft will support these DB in future or not ? asking this because SQL server 2008 version is going to end of support .

    Regards,
    Shivrudra W

  • Microsoft supports database platforms (SQL server versions), not user databases.

    Database compatibility level 100 (sql2008) is supported by SQL2017 (see link below).

    So, as far your db (compat level 100)  is hosted on SQL2017 , it's supported system.

    If you change compat level to 90 (sql2005), you will get an error message that it's not supported:

    alter database [DB] set compatibility_level=90

    Msg 15048, Level 16, State 1, Line 11

    Valid values of the database compatibility level are 100, 110, 120, 130 or 140.

     

    Link:

    https://docs.microsoft.com/en-us/sql/relational-databases/databases/view-or-change-the-compatibility-level-of-a-database?view=sql-server-2017

     

  • Releases of SQL Server normally support compatibility mode for all releases they were supported at the time they were released. When SQL Server 2017 was released SQL Server 2008-2016 were also supported, and so 2017 has compatibility mode 100 available. SQL Server 2019 is very likely to be released while 2008 is still supported (despite it being so close to end of support) and so Compatibilty 100 is unlikely to be removed from the RTM version (it's in the preview versions).

    After 2019, however, I can say with almost full certainty that Compatibility mode 100 will not be supported; as SQL Server 2008 will be completely out of support by that time. As a result you will need to ensure your database is in a compatibility mode that is supported by that future release.

    Considering that 2008 is about to go entirely out of support though, now seems like the best time to start improving your application to not rely on SQL Server 2008 compatibility. As your upgrading the SQL Server 2017 you have quite a few years to complete the upgrades before that version is out of support; but there's no time like the present to start working through.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • It's supported at the moment. If you were to upgrade to SQL 2019 it might not be then, but they won't remove that option in SQL 2017.

    https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017

  • Sorry, I was slow to post Submit- I see Thom has already got there.

  • Beatrix Kiddo wrote:

    Sorry, I was slow to post Submit- I see Thom has already got there.

    The documentation is a good add the the topic, as it's actually far more informative that I was expecting.

    Interestingly, there's no heading for "Differences Between Compatibility Level 110 and Level 100" but after reading a few times, I noticed that Differences Between Lower Compatibility Levels and Levels 110 and 120 actually gives details of differences  between 100 and 110. (I'll raise on their GitHub.)

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • So far it's supported. Who knows if that will survive RTM, though I suspect it will. MS wants to get people to upgrade and this is an important feature.

    2019-05-13 10_15_55-Database Properties - sandbox

    I have heard a few people say that they are not going to deprecate things anymore and will support them. Their work in Azure has given them the idea of keeping old code around. It won't be fixed, and there might be changes in behavior that you don't expect, but the keyword compatibility and most of the behavior should remain the same.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply