Running Business Central in Docker using SQL on the host

This is not my first blog post about how to use SQL Server on the host, but it is definitely the one describing the easiest way to do it. With the latest version of BcContainerHelper you can (with one Run-BcContainer command) create a container which uses SQL Server on the host as database engine for the container.

The functionality was actually enabled in version 1.0.14, but there are a few bug fixes, so I recommend that you use the latest BcContainerHelper

Prerequisites

In order for the scripts in this blog post to work, you need to install SQL Server on the Docker Host with mixed mode authentication. The scripts will connect using Windows Authentication and the container will connect using Username/Password authentication.

ReplaceExternalDatabases

New-BcContainer has a new parameter called –replaceExternalDatabases. This switch will only take effect if you also specify databaseServer, databasePrefix, databaseName and databaseCredential.

When ReplaceExternalDatabases is included, the New-BcContainer will do 4 things, which it typically doesn’t do:

  1. Remove all databases on databaseServer with databaseInstance (might be empty) where the name starts with databasePrefix. This is done using a new function called Remove-BcDatabase.
  2. Restore databases (single or multitenant) from the selected artifacts to databaseServer with databaseInstance (might be empty) starting with databasePrefix. This is done using a new function called Restore-BcDatabaseFromArtifacts.
  3. Start the container using databaseServer with databaseInstance (might be empty) as database server and databasePrefix+databaseName as database name, connecting using databaseCredential for authentication.
  4. Create the default tenant (if multitenant) and add the default super user specified in $credential.

The following script should setup a container using SQL Server on the host:

$password = $passwordSecret.SecretValue
$licenseFile = $licenseFileSecret.SecretValueText
$auth = "UserPassword"
$credential = New-Object pscredential -ArgumentList 'admin', $password
$artifactUrl = Get-BcArtifactUrl -country us

$containerName = "bcserver"

$databaseParams = @{
    "databaseServer" = 'host.containerhelper.internal'
    "databaseInstance" = ''
    "databasePrefix" = "$containerName-"
    "databaseName" = 'CRONUS'
    "databaseCredential" = New-Object pscredential 'sa', $password
    "multitenant" = $true
}

New-BcContainer @databaseParams -replaceExternalDatabases `
    -accept_eula `
    -containerName $containerName `
    -credential $credential `
    -auth $auth `
    -artifactUrl $artifactUrl `
    -licenseFile $licenseFile `
    -updateHosts

Note: It uses $containerName followed by a minus as database prefix, meaning that all databases on the SQL Server on the host with a name starting with bcserver- will be deleted according to the #1 in the description above.

After running this script, I will have 3 databases in my SQL Server:

No SQL processes running in my container:

and after some initialization, a running Business Central Container:

Note: If you are using an SQL Server (not the host), you need the SQL Server Client Tools installed on the host – the functions Remove-BcDatabase and Restore-BcDatabaseFromArtifacts (used by the replaceExternalDatabases functionality) both have a dependency on SQLPS and the smoServer DLL.

If you want to create a second container, using the same database, you can use this script:

New-BcContainer @databaseParams `
    -accept_eula `
    -containerName "$($containerName)2" `
    -credential $credential `
    -auth $auth `
    -artifactUrl $artifactUrl `
    -updateHosts

Only difference is really that the licenseFile and the replaceExternalDatabases parameters are NOT included.

Note: In the latest BcContainerHelper, the containerHelper will automatically share encryption keys between containers on the same host, which are connecting to the same external database. If you are creating a second container on a different host, you will need to copy the encryption key to the second host yourself.

Remove-BcDatabase

The replaceExternalDatabases functionality uses the Remove-BcDatabase function to remove existing databases. You can of course use this function manually as well, simply by specifying databaseServer, databaseInstance (if not blank) and databaseName (or pattern), like:

Note: that this is a dangerous command to run on a SQL Server.

Restore-BcDatabasesFromArtifacts

The replaceExternalDatabases functionality uses the Restore-BcDatabasesFromArtifacts function to restore the database backup file from a set of artifacts to a database server specified by databaseServer, databaseInstance (or blank) and databaseName, like:

$artifactUrl = Get-BCArtifactUrl -country dk
Restore-BcDatabaseFromArtifacts `
    -artifactUrl $artifactUrl `
    -databaseServer localhost `
    -databasePrefix prefix `
    -databaseName CRONUS `
    -multitenant:$false `
    -async

Specifying -async means that the function won’t wait for the restore to complete. A file with the databasePrefix followed by databasescreated.txt will be created in the containerhelper folder when the restore is complete. Without async, the function will wait for the restore to complete.

Note: The New-BcContainer uses async and will add an override to the container SetupDatabase.ps1 script which waits for the databasescreated file to appear. If something fails in the database restore, this might lead to a endless loop.

Enjoy

Freddy Kristiansen
Technical Evangelist

12 thoughts on “Running Business Central in Docker using SQL on the host

  1. Hi Freddy,

    Awesome, I am running a container with a database on host and it’s working but only if I turn off the Windows Firewall for the Public Profile! Very weird! Do you have any idea why this is happening?
    I can’t even ping host.containerhelper.internal when the firewall is turned on for the public profile.
    Posted a question on stackoverflow but on answers 😦

    Like

  2. HI Freddy,
    Thanks for this update. When I try to use the script, it keeps on failing with the below error. I am using SQL Server 2019. Mixed mode authentication is already enabled.

    Waiting for database creation to finish…………………………………………………………..
    Import Encryption Key
    Cannot establish a connection to the SQL Server/Database.

    This could be due to one of the following reasons:

    * SQL Server is not started.
    * The database does not exist.
    * Proper permissions have not been given to the NAV Server Account.

    Try again later or contact your system administrator.
    at , C:\run\setupDatabase.ps1: line 152
    at , C:\Run\my\SetupDatabase.ps1: line 2
    at , C:\Run\navstart.ps1: line 121
    at , C:\Run\start.ps1: line 359
    at , : line 1
    Initialization of container bcserver failed
    At C:\Program Files\WindowsPowerShell\Modules\BcContainerHelper\2.0.6\ContainerHandling\Wait-NavContainerReady.ps1:43 char:17
    + … throw “Initialization of container $containerName failed” …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : OperationStopped: (Initialization …bcserver failed:String) [], RuntimeException
    + FullyQualifiedErrorId : Initialization of container bcserver failed

    Like

  3. Pingback: Restoring your online Business Central database locally | Freddys blog

Leave a Reply to freddykristiansen Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s