Creating databases in Business Central

Article
12/29/2023

APPLIES TO: Business Central 2019 release wave 2 and later

This article describes how to create new databases in Business Central for storing application and business data.

Overview

Application and tenant databases

There are two types of databases: application and tenant.

The application database stores data that defines the application and its business logic. This data includes descriptions of the objects and the code of your application. Essentially, it includes data that are common to all tenants. It's the application database to which you publish your extensions, including the base application, system application, and other extensions.
The tenant database is often referred as just the tenant. The tenant database stores the actual business data for specific companies, for example, data that users enter and modify by using the application. It's the tenant database on which you install extensions that are published to the application database.

In a single-tenant deployment architecture, the application data and business (tenant) data are stored in the same database. That is, there's only one database. If you want to set up single-tenant deployment, you only have to create an application database.

If you have a multitenant deployment architecture, you create an application database and one or more tenant databases.

Supported collations

Business Central supports Windows collations only. For a list of Windows collations, see Windows Collations in the SQL Server documentation.

Create an application database

To create an application database, for either a single-tenant or multitenant deployment, you use the New-NAVApplicationDatabase cmdlet. This cmdlet is available in the Business Central Administration Shell. You use the New-NAVApplicationDatabase cmdlet to create either new database or initialize an existing empty database to make it an Business Central application database.

If you create a new database, the cmdlet adds a database in SQL Server. The database includes the tables and data required for a Business Central application database. The cmdlet creates a master data file (MDF) and log data file (LDF). Using the cmdlet, you can set the database name, the collation, and where to store the data files. Other database options are set for you.
If you use the cmdlet with an existing database, the cmdlet modifies the existing database to include Business Central application tables and data. You configure a database beforehand, setting options that aren't done by the cmdlet, such as options for the data files (MDF/NDF/LDF) and their filegroups, table partitioning, and more.

Tip

Starting in Business Central 2021 release wave 1, the application database will automatically include the platform symbols.

To create an application database, complete the following steps:

Run the Business Central Administration Shell as an administrator.

Make sure that the Window user that you run as has the appropriate privileges on the SQL Server as described in Assign privileges on the Business Central database-level.
Run the New-NAVApplicationDatabase cmdlet to create a new database or initialize an existing database.
```
New-NAVApplicationDatabase [-DatabaseServer <database server name>\<database server instance>] -DatabaseName <String> [-DatabaseLocation <String>] [[-Collation] <String>] [-ApplicationDatabaseCredentials <PSCredential>]
```
To create a new application database, set -DatabaseName to the name you want to give the database, and the -Collation to the wanted collation. If you omit the -Collation, then Latin1_General_100_CS_AS is used by default. Optionally, set the -DatabaseLocation to the directory path where you want to store the data files; otherwise the database files will be stored in the default SQL Server location.

To use an existing empty database, set the -DatabaseName to the name of the existing database. You can't use the -Collation parameter to change the current collation of the database.

The following example creates a new database called MyBCApplicationDB. The database is given the collation Latin1_General_100_CS_AS on the SQL Server instance BCDEMO. The database files are stored in the default data directory for the SQL Server instance (for example, C:\Program Files\Microsoft SQL Server\MSSQL13.BCDEMO\MSSQL\DATA).
```
New-NAVApplicationDatabase -DatabaseServer .\BCDEMO -DatabaseName "MyBCApplicationDB" 
```
When the database has been successfully created, text similar to the following displays:
```
DatabaseServer      : .\BCDEMO
DatabaseName        : MyBCApplicationDB
DatabaseCredentials :
DatabaseLocation    :
Collation           : Latin1_General_100_CS_AS
```

Connect Business Central Server instance to the new database.

For example:

Set-NAVServerConfiguration -ServerInstance BC -KeyName DatabaseName -KeyValue "MyBCApplicationDB"

Run the Set-NAVApplication cmdlet to set the application version on the new database.

To set the application version, use the -ApplicationVersion parameter. The value must have the format major.minor.[build[.revision]], such as '18.1', '18.1.0', or 18.1.0.0'. For example:
```
Set-NAVApplication -ServerInstance BC -ApplicationVersion 18.1.0.0 -Force
```
This step is required to synchronize your tenant and extensions later. This step sets a value to the applicationversion column in the $ndo$dbproperty table of the application database.
Give the Business Central Server service account privileges to the database.

For more information, see Provisioning the Business Central Server Service Account.

If you have a multitenant deployment, go to the next section about creating a tenant database.
If you have a single-tenant deployment, you can now synchronize the tenant. For a multitenant deployment, go to the next section.

Run the Sync-NAVTenant cmdlet to synchronize the tenant. For example:
```
Sync-NAVTenant -ServerInstance BC
```
This step creates the tenant-related tables in the database.

Create a tenant database

Complete the followings step to create a new tenant database in a multitenant deployment.

In SQL Server, create a new database.

Important

Set the collation to the same as the application database.
Give the Business Central Server service account privileges to the database, like you did with the application database.

For more information, see Provisioning the Business Central Server Service Account.
Mount the database as a tenant to the application.

To mount the tenant, use the Mount-NAVTenant cmdlet. For example:
```
Mount-NAVTenant -ServerInstance BC -DatabaseName "BCTenantDB" -DatabaseServer .\BCDEMO -Tenant BCTenant1 -AllowAppDatabaseWrite
```
Note

For now, we recommend that you use the -AllowAppDatabaseWrite parameter. Later, you can dismount and mount the tenant again without the parameter if needed.
Synchronize the tenant.

Use the Sync-NAVTenant cmdlet. For example:
```
Sync-NAVTenant -ServerInstance BC -Tenant BCTenant1 -Mode Sync
```
This step creates the tenant-related tables in the database.

Next steps

Complete the following steps to get the application up and running on your tenants.

Publish the system symbols and extensions that make up your application. For example, publish the system application, base application, Microsoft extensions, and third-party extensions.

See Publishing and Installing an Extension.

For Business Central 2021 release wave 1 and later, you don't have to publish the symbols, because they've been automatically published for you.
Synchronize and install extensions on the tenant.

See Publishing and Installing an Extension.
Add a company to the database.

To add a company, run the New-NAVCompany cmdlet.
Export and import existing permissions sets.

A new database won't include any permission sets except for the SUPER permission set. Also, there will only be one user, typically for your account. You can either create permissions sets from scratch or export the sets from an existing database. As a minimum, it's a good idea to export the BASIC permissions set. The BASIC permission set grants the minimum permissions required for any user to access the application.

For more information, see Export Permission Sets and Special Permission Sets.

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.