Edit

Change Collation of an Existing Business Central

  • Article

You can't change the collation directly in the current database. To change the collation, you must create a new database that uses the correct collation. Then, export the data from the original database and import it to the new database. You'll use SQL Server Management Studio and the Business Central Administration Shell.

Single-tenant versus multitenant deployment

The process is similar for single-tenant and multitenant deployments, but there are some differences. A single tenant deployment has one database, but a multitenant deployment has both an application database and a tenant database. In the steps that follow, if you have a single-tenant deployment, consider references to the application database and tenant database as the same database. When running cmdlets in a single tenant deployment, you can either omit the -Tenant parameter or use default as the tenant ID.

Changing the collation if extensions are installed on the tenant

Follow these steps if one or more extensions are installed on the tenant. If there are no installed extensions, you can also use this procedure or the one that follows.

  1. Use SQL Server Management Studio to create new databases that are configured to use the wanted collation.

    For a single-tenant deployment, you only have to create one database. For a multitenant deployment, you must create a database for the application and another database for the tenant.

    Make sure that the service account of the Business Central Server instance that will connect to the database has proper permission to the database. For more information, see Provisioning the Microsoft Dynamics NAV Server Account.

  2. Open the Business Central Administration Shell as an administrator.

  3. Export the application objects, application data, and tenant data from the original database(s) to a .navdata type file.

    To export the data, run the Export-NAVData cmdlet as shown in the following example:

    Export-NAVData -ServerInstance <server instance> -Tenant <tenant ID> -IncludeApplication -IncludeApplicationData -IncludeGlobalData -AllCompanies -FilePath <file>

    Replace <file> with the folder path and name that you want to assign the exported file. The file must have the extension .navdata, for example, c:\temp\MyDB.navdata.

    In a multitenant deployment, this exports data from the application database and the tenant database into the same file.

  4. Import the application objects and application data from the exported file to the new application database.

    To import the data, run the Import-NAVData cmdlet with -IncludeApplication and -IncludeApplicationData switch parameters. For example:

    Import-NAVData -DatabaseServer <database server name> -DatabaseName <new application database name> -IncludeApplication -IncludeApplicationData -FilePath <file>

  5. Connect the new application database to the Business Central Server instance.

    Set-NAVServerConfiguration <server instance> -KeyName DatabaseName -KeyValue <new application database>

    For more information, see Connect a Server Instance to a Database.

  6. Restart the server instance.

    Restart-NAVServerInstance -ServerInstance <server instance>

  7. (Multitenant only) Mount the new tenant database to the server instance.

    To mount the tenant, use the Mount-NAVTenant cmdlet, for example:

    Mount-NAVTenant -ServerInstance <server instance> -DatabaseName <new tenant database name> -DatabaseServer <server\instance> -Tenant <tenant ID>

  8. Synchronize the tenant database with the application.

    To synchronize the database, run the Sync-NavTenant cmdlet.

    Sync-NAVTenant -ServerInstance <server instance> -Tenant <tenant ID>

  9. Synchronize the tenant database with the published extensions.

    To synchronize the database with extensions, use the Sync-NAVApp cmdlet:

    Sync-NAVApp -ServerInstance <server instance>  -Tenant <tenant ID> -Name "<extension name>" -Version <version number>

    For Business Central 2019 release wave 2 and later (version 15), you may be using the System Application, Base Application, Application extensions. If so, synchronize these extensions first, in the listed order.

  10. Import the tenant data from the exported file to the new tenant database.

    To import the data, run the Import-NAVData cmdlet with -IncludeGlobalData and -AllCompanies switch parameters. For example:

    Import-NAVData -ServerInstance <server instance> -Tenant <tenant ID> -FilePath <file> -IncludeGlobalData -AllCompanies

Changing the collation if no extensions are installed on the tenant

  1. In SQL Server Management Studio, create a new database that uses the wanted collation.

    Make sure that the service account of the Business Central Server instance that will connect to the database has proper permission to the database. The service account must be a member of the db_owner role of the database. For more information, see Provisioning the Microsoft Dynamics NAV Server Account.

  2. Export the application objects, application data, and tenant data from the original database to a .navdata type file.

    To export the data, run the Export-NAVData cmdlet as shown in the following example:

    Export-NAVData -DatabaseServer <database server name> -DatabaseName <original database name> -IncludeApplication -IncludeApplicationData -IncludeGlobalData -AllCompanies -FilePath c:\temp\MyDB.navdata

  3. Import the data from the original database to the new application database, run the Import-NAVData cmdlet as shown in the following example.

    Import-NAVData -DatabaseServer DatabaseServerName -DatabaseName NewDatabaseName -IncludeApplication -IncludeApplicationData -IncludeGlobalData -AllCompanies -FilePath c:\temp\MyDB.navdata

    For more information, see Import-NAVData cmdlet.

  4. Connect the new database to the Business Central Server instance.

    For more information, see Connect a Server Instance to a Database.

  5. Restart the Business Central Server instance.

  6. Synchronize the database.

    From the Business Central Administration Shell, run the Sync-NavTenant cmdlet.

    Sync-NAVTenant -ServerInstance <server instance>

See Also

Creating and Altering a Database
Synchronizing the Tenant Database with the Application Database