Bearbeiten

Freigeben über

Installation considerations for Microsoft SQL Server and Business Central

  • Artikel

This article describes the requirements for installing and configuring Microsoft SQL Server to work with Business Central.

Business Central can run on Microsoft SQL Server and Microsoft Azure SQL Database. For a list of supported editions of SQL Server, refer to SQL Server Requirements.

Using Microsoft SQL Server

Storage

Use different disks or partitions for:

  • Windows operating system
  • Data files for system databases
  • Log files for system and user databases
  • Data and log files for the TempDB database

For optimal read/write performance, format disks used for SQL Server data files with a 64-KB block size.

Virus scanning

Use antivirus software to protect computers running Microsoft SQL Server. Learn more in Configure antivirus software to work with SQL Server.

Memory

Maximize available memory on the server for optimal read performance, based on the SQL Server version and edition. Refer to the SQL Server documentation for maximum values.

SQL Server components

When installing Microsoft SQL Server for use with Business Central, install at least these components:

  • Database Engine Services
  • Client Tools Connectivity
  • Management Tools - Complete

Setup options for Microsoft SQL Server

When you're running Microsoft SQL Server Setup, you must provide additional information. Your responses can affect how you use SQL Server with Business Central.

Full-text and semantic extractions for search

APPLIES TO: Business Central 2024 release wave 2, version 25, and later

Full-text and Semantic Extractions for Search must be installed on the SQL Server instance. Learn how to check whether this feature is installed and install it at Install and Configure Semantic Search.

Business Central uses the full-text feature to support the modern search capability on fields in list pages. Learn more in Searching.

TempDB database configuration

For servers with fewer than eight cores, create as many TempDB data files as the number of cores. For servers with more than eight cores, start with eight data files and increment by four files at a time, if needed.

Make sure that all data files for the TempDB database are of the same size.

Consider placing TempDB data and log files on a local SSD if using SAN storage.

Data file and log file configuration

Autogrowth of the database or transaction log files in production can degrade performance because transactions must queue up and wait for SQL Server to grow the file before it can begin to process transactions again. This behavior can create bottlenecks. Grow data and log files during off-peak periods and by 10% to 25% of the current size. We don't recommend disabling autogrow. In an emergency, it's better that to have SQL Server autogrow files than run out of disk space and bring the database down.

Max degree of parallelism (MAXDOP)

The SQL queries generated by the Business Central Server are of OLTP (Online Transaction Processing) type, including many small transactions. We recommend running Business Central with MAXDOP set to the value 1 as a starting point. Depending on the number of cores on your database server, you can increase this number. It isn't recommended to set MAXDOP to 0, because one query might use all resources on the databases, lowering availability for other queries.

On SQL Server 2016 and later, MAXDOP can be set on the database level, changing a database scoped configuration.

Both advanced server configuration options and database scoped configurations can be set by using SQL Server Management Studio. Learn more in Server configuration: max degree of parallelism.

Note

If you're running SQL Server Enterprise Edition, index maintenance can be done in parallel. If you run maintenance jobs to do this work in off-peak hours, you might want to set MAXDOP back to 0 while running these jobs. On SQL Server 2016 and later, it's possible to set MAXDOP directly in the Rebuild Index Task wizard.

Instance configuration

If you plan to install the Business Central Demo database and want Business Central Setup to use an already installed version of SQL Server (and not install SQL Server Express), create a SQL Server instance named BCDEMO before you run Setup. Otherwise, Setup installs SQL Server Express automatically, even if there's a valid version of SQL Server already on the computer. If you don't plan to install the Demo database, or if you have no objection to using SQL Server Express, you're free to use the default instance and Instance ID on the Instance Configuration page, or to specify any instance name.

Database engine service

A separate Windows service runs each SQL Server instance. The following two things are important to configure for these services

Startup options

Enable trace flags 1117 and 1118 as startup options. These trace flags are incorporated into the default settings. Startup options can be set by using SQL Server Configuration Manager. Learn more in SQL Server Configuration Manager: Configure server startup options.

Service account

We recommend using dedicated domain user accounts for the Windows services running your Business Central Server instances and your SQL Server instances instead of a Local System account or the Network Service account.

The Business Central Server account must have privileges on the SQL Server instances and on Business Central databases. Learn more in Provisioning the Server Service Account.

Consider adding the service account for the SQL Server engine to the Perform Volume Maintenance Tasks security policy.

Database configurations

After Business Central is installed, it's important to check a few settings on the Business Central databases. This task is especially important for databases upgraded from previous versions of SQL Server.

Statistics

Set the AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS options on Business Central databases to ON (the default setting). SQL Server uses a threshold that adjusts according to the number of rows in the table. With this change, statistics on large tables are updated more often. Learn more in Statistics options.

Even with AUTO_UPDATE_STATISTICS on, we recommend running a periodic SQL Agent job to update statistics because AUTO_UPDATE_STATISTICS is triggered according to the row thresholds. On large tables with tens of millions of records (such as Value Entry, Item Ledger Entry, and G/L Entry), a small percentage of data in a given statistic such as [Entry No.] can change and have a material effect on the overall data distribution in that statistic. This behavior can cause inefficient query plans, resulting in degraded query performance until any threshold is reached. We recommend using the T-SQL procedure "sp_updatestats" to update statistics because it only update statistics for data that changes. We recommend creating a SQL Agent Job that runs daily or weekly (depending on transaction volume) during off-peak hours to update all statistics for changed data.

Index fragmentation

Another important administration task that helps to reduce data size and improve performance is to reduce fragmentation for tables and nonclustered indexes. Learn more in Resolve index fragmentation by reorganizing or rebuilding indexes.

Other database options

We recommend setting the database option PAGE_VERIFY to the value CHECKSUM for all databases (including TEMPDB) because it provides the most robust method of detecting physical database corruption. CHECKSUM is the default setting for new installations.

Backup

Set up backups for both system and user databases. Regularly test restore procedures.

Using Microsoft Azure SQL Database

You can deploy a Business Central database to Azure SQL Database. Azure SQL Database is a cloud service that provides data storage as a part of the Azure Services Platform.

To optimize performance, deploy the Business Central Server instance that connects to the database on a virtual machine in Azure. Additionally, the virtual machine and SQL Database must be in the same Azure region.

For development and maintenance work on Business Central applications, if the Business Central is installed on the same virtual machine in Azure as the Business Central Server, then you can connect to the Azure SQL database from the development environment.

Learn more in Deploying a Business Central Database to Azure SQL Database.

Data Encryption between Business Central Server and SQL Server

When SQL Server and Business Central Server run on different computers, encrypt the connection to secure the data channel. Learn more at Encrypting Connections to SQL Server.

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.

Data Access
Troubleshooting: SQL Server Connection Problems
Deployment