Creating and altering Business Central Databases

APPLIES TO: Business Central Spring 2019 and earlier

You can create new Business Central databases in the Dynamics NAV Development Environment and by using the New-NAVDatabase cmdlet in the Dynamics NAV Development Shell.

Note

Dynamics NAV Development Environment is DISCONTINUED AFTER: Business Central Spring 2019.

When you create a database, you must specify the SQL Server instance for the database and the authentication type.

Create a database

1. In the Dynamics NAV Development Environment, on the File menu, choose Database, and then choose New.

2. In the Server Name field, enter the name of the SQL Server instance. You can choose the up arrow to select the server from a list of available servers or you can enter the server name manually.

3. In the Authentication field, select the type of authentication that you require. Choose the drop-down arrow to select Database Server Authentication or Windows Authentication.

   • If you select Database Server Authentication, then authentication is performed by the SQL Server instance that you have selected.

   • If you select Windows Authentication, then authentication is performed by the Windows domain controller.

4. In the User ID field, enter your User ID if you have selected Database Server Authentication.

5. In the Password field, enter your password if you have selected Database Server Authentication.

6. To set the network type to be used when connecting to the server, choose the Advanced tab and select the net type from the drop-down list box in the Net Type field. However, it isn't necessary to change the network type from the default setting. The Default net type setting allows Business Central to connect to a server using the default client network type assigned by SQL Server. You can change the net type with the Client Network Utility, which is part of the SQL Server Client Utilities, if they have been installed on the client computer.

7. Choose OK to connect to the server and open the New Database window.

   In the New Database window, enter the information about the database that you want to create. The window contains the same tabs as the Alter Database window. For more information, see sections in this article for the different tabs.

8. Now that you have created a new database, you must configure your Business Central Server instance to access the database and then restart the service. For more information, see Configuring a Business Central Server Instance.

9. You must synchronize the schema for all tables of the new database.

   You can't use the development environment in this case. You must use the Sync-NAVTenant cmdlet of the Business Central Administration Shell, for example:

   Sync-NAVTenant -ServerInstance BC140 
   

Warning

You can always enlarge a database later on, but you cannot make it smaller.

After you have created the database, you can enter program objects and company data. Before you can create company data, you must import some basic data from another Business Central database. The imported data must at least include Data Common to All Companies and Application Objects.

Alter a database

The changes won't take effect until you restart the Business Central Server instance.

Note

You cannot alter a database by using the development environment if the database is deployed on Azure SQL Database.

Database files tab

Increases the size of the database by either increasing the size of one or more of the database files or adding new data files to the database.

If you use secondary data files, then you must increase the size of the primary data file only when the catalog that it contains has become too large. When the catalog has become too large, new SQL Server objects, such as tables, can't be created until you increase the size of the primary data file.

When you use secondary data files, you can't create more space for storing Dynamics NAV data by just increasing the size of the primary data file. You can create more space for storing data by increasing the size of the secondary data files that contain Dynamics NAV information. You can also add new secondary data files in order to store more data.

To open this window, on the File menu, choose Database, choose Alter, and then choose the Database Files tab.

Note

The first data file that is listed on the Database Files tab is the primary file.

Transaction log files tab

Increases the size of the existing transaction log files or adds new files to enable more transactions to be performed in the database. The transaction log grows as new transactions are performed in the database. SQL Server truncates the log after it performs a successful database or transaction log backup.

To open this window, on the File menu, choose Database, choose Alter, and then choose the Transaction Log Files tab.

You can also delete existing transaction log files that are empty. The first transaction log file that is listed is the primary file. You can't delete the primary transaction log file.

Collation tab

You use this tab to set the collation when you're creating a new database.

Important

Do not use this tab to change the collation of an existing database. To change the collation, you must create a new database that uses the correct collation, and then export the data from the old database and import it to the new database. For more information, see Changing Collation of Existing Database.

The Language drop-down list displays the friendly name of the language, not the full Windows collation name. For some languages, there are multiple collations that sort characters differently. For example, the Windows collation languages include multiple Scandinavian languages, some of which sort Aa after Z, Æ, Ø, and some of which sort Aa after A and before B. If you upgrade from Microsoft Dynamics NAV 2009 to Business Central, you upgrade the database to the Windows collations. If you used SQL collation in earlier versions of Dynamics NAV, then after you upgrade, verify that the Windows collation sorts characters in the way that you expect.

If you set the Validate Collation check box, then collation languages that run with a different non-Unicode code page from your system non-Unicode code page are filtered out of the Language drop-down list. An example scenario of when you might want to choose a collation language that has a different code page from your system code page is if you want to prepare a Japanese database on a Danish computer.

Options tab

Specifies database options that you set when you created the database. For example, you must select the Single User option before you perform any database tests. You must clear this option when the tests are completed.

To open this window, on the File menu, choose Database, choose Alter, and then choose the Options tab.

Access section

Field	Description
Single user	Specifies that only one user can access the database at a time. You can use this setting when you're performing administrative functions such as testing or restoring the database. By limiting access to the database to one user, you make sure that the database isn't changed when you're testing it. Important: Clear this check box when you're finished to give other users to access the database.

Settings section

Field	Description
Recovery Model	Determines the kind of information that is written to the transaction log and therefore the kind of recovery model that you want to use in this database. Note: The Full and Bulk-logged recovery models are similar, and many users of the Full recovery model will use the Bulk-logged recovery model occasionally. Option: Bulk-logged Description: The transaction log contains only limited information about certain large-scale or bulk copy operations. The Bulk-logged recovery model provides protection against media failure combined with the best performance and the minimal use of log space for certain large-scale or bulk copy operations. The backup strategy for bulk-logged recovery consists of: * Database backups. * Differential backups (optional). Option: Full Description: The details of every transaction are stored in the transaction log. This information can be used when you apply transaction log backups. The Full recovery model uses database backups and transaction log backups to provide complete protection against media failure. If one or more data files are damaged, media recovery can restore all the committed transactions. Incomplete transactions are rolled back. Full recovery lets you recover the database to the point of failure or to a specific point in time. All operations are fully logged to guarantee that the database is recoverable. This includes bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data. The backup strategy for full recovery consists of: * Database backups. * Differential backups (optional). * Transaction log backups. Option: Simple Description: The database can be recovered to the point at which the last backup was made. However, you can't restore the database to the point of failure or to a specific point in time. To do that, select either the Full or Bulk-logged recovery model. The backup strategy for simple recovery consists of: * Database backups. * Differential backups (optional).
ANSI NULL default	Specifies whether the database default NULL settings for column definitions and user-defined data types are to be applied. When you select this option, all user-defined data types or columns that haven't been explicitly defined as NOT NULL are set to allow NULL entries. Columns that have been defined by using constraints follow the constraint rules, regardless of this setting.
Recursive triggers	Specifies recursive trigger settings. Triggers can have direct recursion or indirect recursion. Direct recursion occurs when a trigger occurs and performs an action that causes the same trigger to be fired again. Indirect recursion occurs when a trigger occurs and performs an action that causes a trigger on another table to occur. This second trigger updates the original table, which causes the first trigger to occur again.
Torn page detection	Enables SQL Server to detect incomplete input/output operations that have been caused by power failures or other system outages.
Auto shrink	Specifies whether SQL Server can periodically shrink data files and transaction log files.

Integration tab

Specifies database settings that determine how Dynamics NAV integrates with SQL Server and external tools.

To open this window, on the File menu, choose Database, choose Alter, and then choose the Integration tab.

Objects options

Field	Description
Convert Identifiers	Defines characters that you want to map to the underscore character in the names of all SQL Server objects, such as tables, columns, and constraints. If these characters occur in tables or fields in Dynamics NAV, then they're converted to underscores in the SQL Server names. When the conversion is complete, you must close and reopen the database before you can use the new identifiers.

License options

Field	Description
Save license in database	Specifies that the license file is uploaded and stored in the database instead of on the server. This is useful if you're hosting several databases with separate license files on the same server.

Advanced tab

Specifies how locking is handled in the database and specifies the start ID for elements on new objects.

Locking options

Field	Description
Lock timeout	Specifies whether a session waits to place a lock on a resource that has already been locked by another session. If you clear this field, then the session waits indefinitely.
Timeout duration (sec)	Specifies the maximum length of time that a session waits to place a lock on a resource that has already been locked by another session. The default value is 10 seconds.

Designer options

Field

Description

Start ID (UidOffset)

Specifies the start ID for elements on new objects. When you create a new table, page, report, codeunit, query, or XMLport, the elements have IDs that are offset by the Start ID (UidOffset) value that you specify. Object elements include containers, groups, fields, parts, DataItems, columns, filters, variables, functions, or text constants. You must specify an Integer that is greater than or equal to 0. The default value is 1.