HomeDockerRunning Business Central in Docker using SQL on the host

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 1 describing the easiest way to do it. With the latest version of BcContainerHelper you can (with 1 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

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.

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 begins 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 -nation 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 operating this script, I will have 3 databases in my SQL Server:

Running Business Central in Docker using SQL on the host

No SQL processes operating in my container:

1629211804 558 Running Business Central in Docker using SQL on the host

and after some initialization, a operating Business Central Container:

1629211805 994 Running Business Central in Docker using SQL on the host

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 routinely 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.

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 clean) and databaseName (or pattern), like:

1629211805 443 Running Business Central in Docker using SQL on the host

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

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 clean) and databaseName, like:

$artifactUrl = Get-BCArtifactUrl -nation dk
Restore-BcDatabaseFromArtifacts `
    -artifactUrl $artifactUrl `
    -databaseServer localhost `
    -databasePrefix prefix `
    -databaseName CRONUS `
    -multitenant:$untrue `
    -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

Go to the source

Most Popular