Remedy - Server - Changing the Compatibility Level for the Remedy database on a MSSQL instance

Version 5
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Remedy AR System Server


    COMPONENT:

    AR System


    APPLIES TO:

    Microsoft SQL Server 2016 and higher.



    QUESTION:

    Does affect to the Remedy performance if the compatibility level is changed in MSSQL 2016 for the ARSystem database?
    For example issuing:
    ALTER DATABASE SET COMPATIBILITY LEVEL = xxx


    ANSWER:

    Note - This is not a Remedy feature but a MSSQL feature. Any queries need to be addressed to the Database vendor.

    The compatibility level is a feature in MSSQL to allow older Databases versions work with the latest MSSQL instances version.
    This means for example that having MSSQL 2016 instance it can contain a database which was originally created with MSSQL 2012 for example.
    MSSQL 2016 (as an example) will set automatically a "compatibility level" value for this Database version (2012) to ensure it backwards compatible with the current MSSQL instance engine and it works as expected.

    Below is a table with the compatible levels:
    Source: Microsoft

                                                                                                                                                                                                                                                                                                                                                                  
    ProductDatabase Engine VersionDefault Compatibility Level DesignationSupported Compatibility Level Values
    SQL Server 2019 (15.x)15150150, 140, 130, 120, 110, 100
    SQL Server 2017 (14.x)14140140, 130, 120, 110, 100
    Azure SQL Database single database/elastic pool12140150, 140, 130, 120, 110, 100
    Azure SQL Database managed instance12140150, 140, 130, 120, 110, 100
    SQL Server 2016 (13.x)13130130, 120, 110, 100
    SQL Server 2014 (12.x)12120120, 110, 100
    SQL Server 2012 (11.x)11110110, 100, 90
    SQL Server 2008 R210.5100100, 90, 80
    SQL Server 200810100100, 90, 80
    SQL Server 2005 (9.x)99090, 80
    SQL Server 2000 (8.x)88080
      
    As an example, A Remedy Database originally created in MSSQL 2012 and running on a MSSQL instance 2016 will have the compatibility level set to 110.
    New databases created on a MSSQL 2016 instance, by default will have the compatibility level set to 130.

    Why change the compatibility level?
    In order to use the latest features available for example in MSSQL 2016 the compatibility level of the database has to match the value expected for this version. In other words, a database created in MSSQL 2012 and running on a MSSQL 2016 instance does not benefit of the new features available in MSSQL 2016.

    Can the compatibility level for the Remedy Database be changed?
    If the Remedy Database (or any other database) was created in MSSQL 2012 and is running in MSSQL 2016 it will have a value of 110 as expected to ensure it works correctly as it worked when MSSQL 2012 engine was being used.

    It is not BMC's scope to work issues as a result of having changed the compatibility level and neither BMC provides best practices, settings or procedures to perform this operation.
    Changing the compatibility level incorrectly can indeed have an impact in the functionality of the application as well as in the performance since MSSQL engine will work differently for each level.
    If the Remedy database is "migrated" to a higher compatibility level, this needs to be addressed to Microsoft.

       

     

       Additional information:
    https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?redirectedfrom=MSDN&view=sql-server-ver15

      

     


    Article Number:

    000175123


    Article Type:

    FAQ/Procedural



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles