When you upgrade to a newer version of the Microsoft SQL Server database engine, by default, the database compatibility level is set to the version you’re migrating from. This allows you to test the upgrade the database engine without introducing potentially disruptive functional changes. Eventually you will want to update the compatibility level to take advantage of the new functionality available with that version. You should be aware that changing the compatibility level requires careful planning and testing since new functionality is introduced. Changing this attribute has a significant impact on your N4 database.
What is the compatibility level?
Each version of the database engine has an associated compatibility level for the database functionality. By default, when you upgrade SQL Server to a newer version, the database retains the pre-upgrade compatibility level as long as it is supported for that database engine, or else it is set to the lowest supported compatibility level. The compatibility level is a feature that allows you to upgrade the database engine while keeping the pre-upgrade functions.
This means that when you migrate your database to a newer version of SQL Server, your database will function as it were on the previous version, until you change the compatibility level for that database. For example, if you upgrade the database engine from SQL Server 2016 (compatibility level 130) to SQL Server 2019 (150), the database functionality will remain as SQL Server 2016 (compatibility level 130) until you change it.
When do you change the compatibility level?
Before you change the compatibility level for your N4 database, you must test the new version in a test environment. You should not change to the new version if you have not tested its new features and checked for possible issues.
If you don't make the change properly, some query execution plans might execute incorrectly and you would see slow performance issues. When the compatibility setting of a database is changed, many internal database objects are automatically recompiled accordingly. You may see slow performance for some time due to the recompiling of queries and stored procedures. You must test all stored procedures that your application executes to ensure that they work properly.
Navis recommends that you follow these steps for safely upgrading your compatibility level. If you change the compatibility level in a different way, other than what is described here, you could inadvertently change the compatibility level for other databases that are on the same instance.
You should plan for downtime because you will need to make sure that no users are connected during the change.
Make sure that all users are logged off. Never change the compatibility level while users are connected. You should stop the application before the compatibility level is changed.
To change the compatibility level for SQL Server:
Stop all N4 services (on page 1).
Set AUTO_UPDATE_STATISTICS_ASYNC to OFF:
ALTER DATABASE [N4DatabaseName]
SET AUTO_UPDATE_STATISTICS_ASYNC OFF
Set the database in single-user mode:
ALTER DATABASE [N4DatabaseName]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Change compatibility level:
ALTER DATABASE [N4DatabaseName]
SET COMPATIBILITY_LEVEL = [chosenLevel]
Set the database in multi-user mode:
ALTER DATABASE [N4DatabaseName]
SET MULTI_USER
Set AUTO_UPDATE_STATISTICS_ASYNC to ON.
ALTER DATABASE [N4DatabaseName]
SET AUTO_UPDATE_STATISTICS_ASYNC ON
Start N4 (on page 1).
For more information about compatibility levels, see the documentation on the Microsoft website (https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sqlallproducts-allversions).