Edit

Setting SQL Compatibility Level to Optimize Database Performance

  • Article

If your Business Central database is running on Azure SQL Database or SQL Server 2016 or later, set the database's compatibility level to match the database server. This will equip the database with the latest optimization features of Azure SQL Database or SQL Server. This is particularly relevant for demonstration databases that are installed by using the Business Central Setup because the default compatibility level matches SQL Server 2014.

To change the compatibility level

You change the compatibility level of the database by using SQL Server Management Studio. There are two ways to do this:

  • Open the database properties, select the Options page, and then set the Compatibility Level:.

    For more information, see View or Change the Compatibility Level of a Database.

  • Run the following query:

    ALTER DATABASE <database name> SET COMPATIBILITY_LEVEL = { 140 | 130 }

    where:

    • <database name> is the name of the database to be modified.
    • 140 sets the database to be compatible with SQL Server 2017
    • 130 sets the database to be compatible with SQL Server 2016 and Azure SQL Database

    For more information, see ALTER DATABASE (Transact-SQL) Compatibility Level.

Note

The compatibility level for Azure SQL Database is subject to change. Refer to Azure SQL Database documentation for latest compatibility level.

Integrating directly on SQL Server objects

Warning

During operations such as upgrade and app/extension synchronization, the Business Central Server synchronizes the metadata model defined in AL to physical tables on the SQL Server database.

While it's possible to integrate directly with SQL Server objects on the database (bypassing the Business Central Server), it's not recommended or even supported.

Altering SQL objects created by the Business Central Server directly on the database can break operations such as upgrade and app/extension synchronization.

Adding additional SQL objects such as triggers or stored procedures directly on the database can break operations like upgrade and app/extension synchronization, but will also break system integrations that depend on such additional objects if the table schema created by the Business Central Server synchronization changes.

See Also

Optimizing SQL Server Performance
Microsoft SQL Server documentation'