Update 2021/2/10: Microsoft stopped creating images for Docker in the summer of 2020. We now publish artifacts, which can be used to spin up containers and BcContainerHelper has replaced NavContainerHelper. This blog post reflects the old way of using NAV/BC on Docker and references NavContainerHelper, which is outdated.
Just recently, a new functionality was enable in the Dynamics 365 Business Central admin center. The ability to request a backup. It didn’t take long before I got the first question from a partner, who asked whether they could run this locally using Docker. This blog post describes how to do just that. In this blog post, I will mount the database inside Docker, which won’t work if your online database exceeds 10Gb (due to SQL EXPRESS). The next blog post will describe how to do the same using a SQL Server on the host.
Please Note: Not for production
Please note that following this blog post will not give you an environment you can use for production. First of all, there might be extensions installed in your online environment, which you cannot install locally and furthermore Docker is not supported for production even if Docker would have been supported for production, we would definitely not support sandbox containers in production (as they simulate online and there are a number of things that doesn’t work in sandbox containers).
Requesting the backup
You will find the database export functionality in the admin center. Select the environment and select Database -> Create Database Export and you will see something like this:
Only thing you need to specify is a SAS URI for a blob storage, where you want to receive the backup. Clicking the small (i) after SAS URI will take you to this document, which describes how to create a SAS URI. After requesting the backup you will have to wait for some time before the backup arrives in your storage account.
Downloading the backup
When the backup arrives in the storage account, you will see a blob.
Clicking the blob, you can download the .bacpac file locally and use that, but you can also generate a SAS URL, which also be be used when creating a container. The latter will do an on-demand download of the database and a sample SAS URL will look something like:
What docker image to use
Having downloaded the .bacpac file (or created a direct download URL to the file), next issue is to determine what docker image to use. The .bacpac is a tenant database and doesn’t contain the app database. Furthermore it is stamped with a database version number, meaning that you need to use a platform and an app, which is capable of mounting the database.
Onprem images are only shipped monthly and they don’t contain the same apps as the sandbox images. The likelihood that you would be able to use an onprem image (and thus also an onprem version installed from a downloaded DVD) is slim.
Now, you could try to determine the exact version of your app online and see if you can find a matching image. In your online environment, you can navigate to the Help and Support page to get the platform and app version:
Now, docker images are tagged after application version (as multiple app versions might share the same platform). So you could take the docker version with the version tag 15.0.36560.0, but if we inspect the labels of that image, we will see:
Platform version 15.0.36510.0, which means that my online tenant apparently have some platform updates.
We can investigate all existing docker images using this small script:
$repo = "mcr.microsoft.com/businesscentral/sandbox" (Get-NavContainerImageTags -imageName $repo).Tags | Where-Object { $_ -like "15.*-us-ltsc2019" } | % { $labels = Get-NavContainerImageLabels -imageName "$($repo):$_" Write-Host "Platform: $($labels.Platform) App: $($labels.version)" } Platform: 15.0.36510.0 App: 15.0.36560.0 Platform: 15.0.36510.0 App: 15.0.36560.36626 Platform: 15.0.36510.0 App: 15.0.36590.0 Platform: 15.0.36510.0 App: 15.0.36626.36675 Platform: 15.0.36895.0 App: 15.0.36626.36918 Platform: 15.0.36924.0 App: 15.0.36626.36938 Platform: 15.0.36924.0 App: 15.0.36626.36966 Platform: 15.0.36985.0 App: 15.0.36626.37046 Platform: 15.0.36985.0 App: 15.0.36626.37056 Platform: 15.0.36985.0 App: 15.0.36626.37063 Platform: 15.0.37259.0 App: 15.0.36626.37299 Platform: 15.0.37259.0 App: 15.0.36626.37307 Platform: 15.0.37259.0 App: 15.0.36626.37394 Platform: 15.0.37395.0 App: 15.0.36626.37481 Platform: 15.0.37492.0 App: 15.0.36626.37536 Platform: 15.0.37492.0 App: 15.0.36626.37543 Platform: 15.0.37582.0 App: 15.0.36626.37679 Platform: 15.0.37582.0 App: 15.0.36626.37711 Platform: 15.0.37867.0 App: 15.0.36626.37934
and find that no image fits directly. My recommandation is to just use the latest image, you should be able to mount and sync the database on that.
Create a multitenant container
As always, I assume that you have the latest NavContainerHelper from the PowerShell gallery here, the following will probably work with older versions as well, but my first response to an issue will always be to update NavContainerHelper and try again.
First thing we need to do is to create a multitenant container using the latest sandbox image with us localization.
$auth = "UserPassword" $credential = New-Object PSCredential 'admin', (ConvertTo-SecureString -String 'P@ssword1' -AsPlainText -Force) $imageName = "mcr.microsoft.com/businesscentral/sandbox:us-ltsc2019" $containerName = "test" New-BCContainer ` -accept_eula ` -accept_outdated ` -containerName $containerName ` -imageName $imageName ` -auth $auth ` -Credential $credential ` -multitenant ` -alwaysPull ` -updateHosts ` -licenseFile 'c:\temp\license.flf'
Next thing is to restore the .bacpac in the container, mount the database as a tenant, synchronize and create a user in the tenant. Note that I have placed the .bacpac file in a location, which is shared with the container.
$tenantBacpac = "C:\ProgramData\NavContainerHelper\Production_20191110_02.bacpac" $tenantId = "mydata" Invoke-ScriptInBCContainer -containerName $containerName -scriptblock { Param($tenantId, $tenantBacpac) Restore-BacpacWithRetry ` -bacpac $tenantBacpac ` -databasename $tenantId ` -maxattempts 1 Mount-NavTenant ` -ServerInstance $ServerInstance ` -id $tenantId ` -databasename $tenantId ` -databaseserver localhost ` -databaseinstance SQLEXPRESS ` -EnvironmentType Sandbox ` -OverwriteTenantIdInDatabase ` -Force Sync-NavTenant ` -ServerInstance $ServerInstance ` -Tenant $tenantId ` -Force } -argumentList $tenantId, (Get-BCContainerPath -containerName $containerName -path $tenantBacpac) New-NavContainerNavUser ` -containerName $containerName ` -Credential $credential ` -ChangePasswordAtNextLogOn:$false ` -PermissionSetId SUPER ` -tenant $tenantId
and basically that’s it.
Unless…
Unless you have installed any applications which are not standard MS apps.
The sandbox containers comes with all standard MS apps pre-installed, but if you have installed apps from appsource or installed per-tenant applications, the script above will fail, saying something like this:
In my case, I have the source for both extensions, so I could just install them before mounting, but what if I didn’t?
Fortunately, we can manage both cases. Publishing apps are done using the Publish-BCContainerApp function and removing extensions are done by deleting the corresponding record in the tables NAV App Published App and NAV App Installed App. Note that by removing the apps from these tables we kind of invalidate the schema. There might be tables in the tenant database now which aren’t used, but there is no code referencing the tables, so everything should be ok. Of course, you will not be able to see or touch any data in tables or fields belonging to not-installed extensions.
Instead of the above script, you would have to run a script like the script below to publish the BingMaps app and delete the MyApp app reference:
$tenantBacpac = "C:\ProgramData\NavContainerHelper\Production_20191110_02.bacpac" $tenantId = "mydata" $removeApps = @("MyApp") Publish-BCContainerApp ` -containerName $containerName ` -appFile "C:\Users\freddyk\Downloads\Freddy Kristiansen_BingMaps_15.0.56.0.app" ` -skipVerification ` -sync ` -install Invoke-ScriptInBCContainer -containerName $containerName -scriptblock { Param($tenantId, $tenantBacpac, $removeApps) Restore-BacpacWithRetry ` -bacpac $tenantBacpac ` -databasename $tenantId ` -maxattempts 1 $removeApps | ForEach-Object { Invoke-Sqlcmd -Query "USE [$tenantId] GO DELETE FROM [dbo].[NAV App Published App] WHERE Name = '$_' DELETE FROM [dbo].[NAV App Installed App] WHERE Name = '$_' GO" } Mount-NavTenant ` -ServerInstance $ServerInstance ` -id $tenantId ` -databasename $tenantId ` -databaseserver localhost ` -databaseinstance SQLEXPRESS ` -EnvironmentType Sandbox ` -OverwriteTenantIdInDatabase ` -Force Sync-NavTenant ` -ServerInstance $ServerInstance ` -Tenant $tenantId ` -Force } -argumentList $tenantId, (Get-BCContainerPath -containerName $containerName -path $tenantBacpac), $removeApps New-NavContainerNavUser ` -containerName $containerName ` -Credential $credential ` -ChangePasswordAtNextLogOn:$false ` -PermissionSetId SUPER ` -tenant $tenantId
Now, I can logon to mydata tenant in my container using http://test/BC?tenant=mydata and get:
and I can see that my tenant data is preserved, my BingMaps extension is still installed, configured and data (geocoding) is preserved.
As stated in the beginning, this only works for databases which are less than 10Gb in size due to SQL EXPRESS. In the next blog post, I will describe how to do this with SQL Server Developer edition installed on the host.
Enjoy
Freddy Kristiansen
Technical Evangelist
“which won’t work if your online database exceeds 10Gb (due to SQL EXPRESS)”.
Is it possible to use SQL Server Developer Edition in the container instead of SQL Express? (maybe as another option to your script. It is Christmas soon you know 😉 )
LikeLike
As I wrote in the blog post, the next blog post will describe how to use SQL Server on the host for larger databases. I don’t think putting SQL Developer inside the container is the right direction, but using SQL on the host or on a separate database server – should solve the issue.
Blog post should be out later today/tomorrow.
LikeLike
Thanks – and you are probably right – the overhead will be smaller with an SQL Developer edition on the host.
LikeLike
Agree
LikeLike
Second blog post is ready…
LikeLiked by 1 person
Hi. Thanks for your blog. The script is working fine but when I try to connect using web client, I got below error.
“Something went wrong.
You cannot open the company because you do not have a paid license for production companies.”
I’m using Partner Dev/Demo License.
LikeLike
I would assume that your partner license would work, but I am really not a license expert. Maybe you should raise an issue in support for this?
LikeLike
We had the same issue. Resolved it by following the instructions listed here:
https://www.j3ns.de/d365-business-central/you-do-not-have-a-paid-license-for-production-companies-error-in-restored-business-central-cloud-bacpac/
** Quoting the information here as well: **
> We currently had a customer’s requirement to create a local test environment from a Business Central Cloud backup (BacPac). To achieve this using Docker, FreddyK’s blog post is very helpful and recommended. After we cleared all the hurdles and had a working database, we constantly ran into the following License error in Business Central when switching from CRONUS to a “production” company:
>
> “You cannot open the company because you do not have a paid license for production companies”
>
> Finally, the error can be solved by deleting all old entries from the [dbo].[Tenant License State] table…
>
> …and then cleanly importing your partner license using Import-BcContainerLicense():
>
LikeLike
Pingback: Mounting a database from my online environment using SQL Server on the host | Freddys blog
Hi Freddy,
Unfortunalty, the last docker image seems to not be available yet : version of Base Application is 15.2.39558.40612 (on the BC Production Env), and the lastest available is 15.2.39558.39825. Is there a way to be inform when new docker image are available, please? any notification, newletter or whanever?
Thanks for your help (and all your job about navcontainerhelper)
LikeLike
Get-NavContainerImageTags reveals these 15.2 images:
15.2.39558.0-us-ltsc2019
15.2.39558.39771-us-ltsc2019
15.2.39558.39784-us-ltsc2019
15.2.39558.39792-us-ltsc2019
15.2.39558.39796-us-ltsc2019
15.2.39558.39822-us-ltsc2019
15.2.39558.39825-us-ltsc2019
15.2.39558.40232-us-ltsc2019
15.2.39558.40594-us-ltsc2019
15.2.39558.40802-us-ltsc2019
LikeLike
Hi Freddy,
I have the client custom/app-source app files and I install them in my container in advance before mounting the backup but I am keep getting an error that two of the apps are missing and tenant could not be mounted. I check my container and I see them installed. Do you now why it might be giving error on those two specific apps?
LikeLike
I was able to mount the tenant by removing those two apps.
LikeLike
If the apps are using the same appid, publisher and name – it should work with the apps as well.
LikeLike
In order to get my custom extension to load, I had to update the Package ID on the new tenant with the Package ID from the default tenant.
$myapp = “myapp”
Invoke-ScriptInBCContainer -containerName $containerName -scriptblock { Param($tenantId, $tenantBacpac, $removeApps, $myapp)
Restore-BacpacWithRetry `
-bacpac $tenantBacpac `
-databasename $tenantId `
-maxattempts 1
$removeApps | ForEach-Object {
Invoke-Sqlcmd -Query “USE [$tenantId]
GO
DELETE FROM [dbo].[NAV App Published App]
WHERE Name = ‘$_’
DELETE FROM [dbo].[NAV App Installed App]
WHERE Name = ‘$_’
GO”
}
Invoke-Sqlcmd -query “UPDATE [$tenantId].[dbo].[NAV App Installed App]
SET [Package ID]=(Select [Package ID] FROM [default].[dbo].[NAV App Installed App] WHERE [Name]=’$myapp’)
WHERE [Name]=’$myapp'”
Mount-NavTenant `
-ServerInstance $ServerInstance `
-id $tenantId `
-databasename $tenantId `
-databaseserver localhost `
-databaseinstance SQLEXPRESS `
-EnvironmentType Sandbox `
-OverwriteTenantIdInDatabase `
-Force
Sync-NavTenant `
-ServerInstance $ServerInstance `
-Tenant $tenantId `
-Force
} -argumentList $tenantId, (Get-BCContainerPath -containerName $containerName -path $tenantBacpac), $removeApps, $myapp
LikeLike
For you guys running into the “You cannot open the company because you do not have a paid license for production companies.” problem.
Deleting all the old entries from the “Tenant License State” table and importing your partner license afterwards fixed it for me.
LikeLiked by 1 person
Pingback: “You do not have a paid license for production companies” error in restored Business Central (Cloud BacPac)
Pingback: "You do not have a paid license for production companies" error in restored Business Central (Cloud BacPac)