locked
SQL 2016 application on SQL 2017 RRS feed

  • السؤال

  • Hi,<o:p></o:p>

    We need to run a SQL 2016 application on the organisations SQL farm which is SQL 2017 running in compatibility mode 140.
    This farm hosts multiple database belonging to other clinical systems. They are all using compatibility mode 140.<o:p></o:p>

    The required application for SQL 2016 requires the compatibility mode to be set to 130. <o:p></o:p>

    We are being told that even though SQL 2017 can be pulled down and run in compatibility mode 130. The issue with this is that all the other clinical system databases will have to be revalidated to work on compatibility mode 130 and will involve a lot of work for Trust ICT and administrators for the other systems.<o:p></o:p>

    Is this true? Will the other systems have to be downgraded from 140 to 130? If so is there a way around this?<o:p></o:p>

    Many Thanks<o:p></o:p>


    25/ذو الحجة/1441 07:44 ص

الإجابات

  • On an SQL Server instances you can have databases with different compatibility level, and these databases would normally not affect each other. I say normally, because if there are cross-database queries there can be some interesting things happening. But if the databases are for different applications, it is not likely that the run queries to the other databases.

    So if your IT department it would affect other databases on the instance if you set this particular database to compat level 130, they are wrong.

    However, I am aware of that in healthcare there are many regulations etc, and not all of these make sense.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • تم الاقتراح كإجابة بواسطة Cathy JiMicrosoft contingent staff 27/ذو الحجة/1441 06:33 ص
    • تم وضع علامة كإجابة بواسطة w1cka 01/محرم/1442 01:45 م
    25/ذو الحجة/1441 08:07 ص
  • So the database using SQL Server 2016 application will be new database or any one of the databases using compatibility level 140 ?. If it is new database like Erland said you can run it easily without actually disturbing other DB's but if you are going to change DB compatibility of one of the SQL Server 2017 database then you need to check but again it would no be that much of a work. if it does not works you can change compatibility level back to 140 its just one small change from SSMS GUI or TSQL

    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

    • تم الاقتراح كإجابة بواسطة Cathy JiMicrosoft contingent staff 27/ذو الحجة/1441 06:33 ص
    • تم وضع علامة كإجابة بواسطة w1cka 01/محرم/1442 01:45 م
    25/ذو الحجة/1441 05:44 م
  • The compatibility level is a database level setting, not a server level setting.  So as Erland said, this only affects the single database, unless you have cross database queries.

    However, setting the compatibility level does not make it 100% compatible with the previous version.  The engine is still SQL 2017 and only certain functionality is changed. 

    Please see:

    https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15#differences-between-compatibility-levels

    • تم الاقتراح كإجابة بواسطة Cathy JiMicrosoft contingent staff 27/ذو الحجة/1441 06:33 ص
    • تم وضع علامة كإجابة بواسطة w1cka 01/محرم/1442 01:45 م
    25/ذو الحجة/1441 08:21 م
    المجيب على الأسئلة
  • Hi w1cka,

    The database compatibility level was mainly used to control whether new features introduced with a particular version of SQL Server were enabled or not and whether non-supported old features were disabled or not. Refer to Differences between Compatibility Level 130 and Level 140.

    As others mentioned, SQL server compatibility is a database level setting, not a server level setting. It will not effects other databases on the instance, unless you have cross database queries. We can change it using T-SQL or SSMS UI. Please refer to View or Change the Compatibility Level of a Database.

    If the response helped, please click "Mark as Answer" and up vote it.

    Best regards,
    Cathy 

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.


    27/ذو الحجة/1441 06:33 ص
  • Hi w1cka,

    Any update? If the replies could help you, please kindly click "Mark as Answer".By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    Best regards,
    Cathy

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.



    28/ذو الحجة/1441 08:05 ص

جميع الردود

  • On an SQL Server instances you can have databases with different compatibility level, and these databases would normally not affect each other. I say normally, because if there are cross-database queries there can be some interesting things happening. But if the databases are for different applications, it is not likely that the run queries to the other databases.

    So if your IT department it would affect other databases on the instance if you set this particular database to compat level 130, they are wrong.

    However, I am aware of that in healthcare there are many regulations etc, and not all of these make sense.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • تم الاقتراح كإجابة بواسطة Cathy JiMicrosoft contingent staff 27/ذو الحجة/1441 06:33 ص
    • تم وضع علامة كإجابة بواسطة w1cka 01/محرم/1442 01:45 م
    25/ذو الحجة/1441 08:07 ص
  • So the database using SQL Server 2016 application will be new database or any one of the databases using compatibility level 140 ?. If it is new database like Erland said you can run it easily without actually disturbing other DB's but if you are going to change DB compatibility of one of the SQL Server 2017 database then you need to check but again it would no be that much of a work. if it does not works you can change compatibility level back to 140 its just one small change from SSMS GUI or TSQL

    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

    • تم الاقتراح كإجابة بواسطة Cathy JiMicrosoft contingent staff 27/ذو الحجة/1441 06:33 ص
    • تم وضع علامة كإجابة بواسطة w1cka 01/محرم/1442 01:45 م
    25/ذو الحجة/1441 05:44 م
  • The compatibility level is a database level setting, not a server level setting.  So as Erland said, this only affects the single database, unless you have cross database queries.

    However, setting the compatibility level does not make it 100% compatible with the previous version.  The engine is still SQL 2017 and only certain functionality is changed. 

    Please see:

    https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15#differences-between-compatibility-levels

    • تم الاقتراح كإجابة بواسطة Cathy JiMicrosoft contingent staff 27/ذو الحجة/1441 06:33 ص
    • تم وضع علامة كإجابة بواسطة w1cka 01/محرم/1442 01:45 م
    25/ذو الحجة/1441 08:21 م
    المجيب على الأسئلة
  • Hi w1cka,

    The database compatibility level was mainly used to control whether new features introduced with a particular version of SQL Server were enabled or not and whether non-supported old features were disabled or not. Refer to Differences between Compatibility Level 130 and Level 140.

    As others mentioned, SQL server compatibility is a database level setting, not a server level setting. It will not effects other databases on the instance, unless you have cross database queries. We can change it using T-SQL or SSMS UI. Please refer to View or Change the Compatibility Level of a Database.

    If the response helped, please click "Mark as Answer" and up vote it.

    Best regards,
    Cathy 

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.


    27/ذو الحجة/1441 06:33 ص
  • Hi w1cka,

    Any update? If the replies could help you, please kindly click "Mark as Answer".By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    Best regards,
    Cathy

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.



    28/ذو الحجة/1441 08:05 ص