I have had quite a few partners asking me how to connect a NAV / Business Central container to a SQL Server placed on the host. Even though this sounds like a simple question, it isn’t easy to answer.
When you start a standard NAV or Business Central Container, you will have everything installed in the container, Business Central, Web Server, SQL Server and the database folder containing the database files.
If you just want to run a container with the standard CRONUS database, there is IMO no reason at all to not just use the built-in database. For most AL Extension Development that is the case. But, what if you have your own database backup with a lot of demo data, which is necessary for your development. In that case, the database backup file might take forever to restore into a container and the container might not even have space for the database.
For this reason, a lot of people turn to the solution of trying to host the database server on the host and connecting the container to that.
It just isn’t that easy…
The reason why it isn’t that easy is, that the host and the container are 2 different machines, even if you are running Process Isolation.
Normally when running SQL Server on the same machine as the Service Tier, you will used the Shared Memory protocol in SQL Server and as such you don’t have to enable TCP/IP, open firewalls, handle host name resolution, etc. – it just works.
When running SQL Server on a different machine you need to handle all these things and to add to the complexity, the computer might have a dynamic IP address and if you are running containers with NAT networking, you container cannot directly see the host.
So what can we do?
The solution to this problem is IMO to restore your database backup to a container and then extract the database files (.mdf + .ldf) and place them on the host in a folder, which is shared with the Container.
Then override the SetupDatabase script to attach these files to the SQL Server inside the container.
This drawing is slightly misleading as the database server in the container actually doesn’t know that the file is on the host – for the database server, the file is local.
What are the benefits of this:
and the flipside:
First of all, you need the .mdf + .ldf files. There is a function in NavContainerHelper called Extract-FilesFromNavContainerImage which can extract selected files from a container image. Running this code:
$imageName = "mcr.microsoft.com/businesscentral/onprem:w1-ltsc2019"
$path = "c:\temp\hostdbfolder"
Extract-FilesFromNavContainerImage -imageName $imageName -path $path -extract database -force
will place the database files from the latest BusinessCentral on premises container in c:\temp\hostdbfolder\databases.
There is another function called Extract-FilesFromStoppedNavContainer, which can extract the files from a stopped container. This function will only work with a single tenant container, where the database is in its original location. If you need this to work with multi-tenancy, you can probably easily decipher how this is done here: https://github.com/Microsoft/navcontainerhelper/blob/dev/ContainerHandling/Extract-FilesFromStoppedNavContainer.ps1
Creating a container, using a new database created by attaching the .mdf + .ldf files on the host, can be done using this script:
$credential = New-Object pscredential 'admin', (ConvertTo-SecureString -String 'P@ssword1' -AsPlainText -Force)
$containerName = "mytemp"
$attachdbSetupDatabaseScript = "https://raw.githubusercontent.com/Microsoft/nav-docker/master/override/attachdb/SetupDatabase.ps1"
New-NavContainer -accept_eula `
-imageName $imageName `
-containerName $containerName `
-additionalParameters @("--volume $path\databases:c:\mydb") `
-myScripts @($attachdbSetupDatabaseScript) `
-auth "NavUserPassword" `
-Credential $credential `
Basically, you share the folder with the .mdf + .ldf files to c:\mydb inside the container and then you override the SetupDatabase script with the script located here: https://raw.githubusercontent.com/Microsoft/nav-docker/master/override/attachdb/SetupDatabase.ps1