This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.
Remedy AR System Server
Microsoft SQL Server 2016 and higher.
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
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:
|Product||Database Engine Version||Default Compatibility Level Designation||Supported Compatibility Level Values|
|SQL Server 2019 (15.x)||15||150||150, 140, 130, 120, 110, 100|
|SQL Server 2017 (14.x)||14||140||140, 130, 120, 110, 100|
|Azure SQL Database single database/elastic pool||12||140||150, 140, 130, 120, 110, 100|
|Azure SQL Database managed instance||12||140||150, 140, 130, 120, 110, 100|
|SQL Server 2016 (13.x)||13||130||130, 120, 110, 100|
|SQL Server 2014 (12.x)||12||120||120, 110, 100|
|SQL Server 2012 (11.x)||11||110||110, 100, 90|
|SQL Server 2008 R2||10.5||100||100, 90, 80|
|SQL Server 2008||10||100||100, 90, 80|
|SQL Server 2005 (9.x)||9||90||90, 80|
|SQL Server 2000 (8.x)||8||80||80|
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.