Share
Report
This blog post is about an unsupported exercise to create a monster with pieces of different bodies, like Mary Shelley’s Frankenstein.
IMPORTANT DISCLAIMER
What will follow is not supported and discouraged by Microsoft officially.
You can read the official documentation post here
Exporting Databases in the Admin Center - Business Central | Microsoft Docs
And underlined in bold by the following blog post
Tattoo well in mind that the operations of building such monster will always end in having some pieces missing or not perfectly fitting.
A couple of examples: AppSource apps and 1st party app versioning. But there are more.
AppSource apps. These are embedded in the application database and not part of the restore environment. AppSource app data in SQL Server will be restored, of course, but you cannot use that nor surfacing through the UI since you do not have the AppSource app deployed on-prem. Unless you have access to AppSource apps (you are the owner) or runtime packages from these.
1st party app versioning. Despite of you might think that SaaS and On-Premises are branched with exactly same version number, SaaS is evolving with hotfix/patching strategy in the application and platform between each minor update. In other words, Base Application and other 1st party app most probably will differ in, at least, revision number comparing what is deployed in SaaS and its equivalent On-Premises Cumulative Update (CU).
Building the Monster
- Deploy On-Premises platform files and SQL BAK from DVD with the same major and minor version and localization as your SaaS bacpac. In this blog we will use 18.5 IT localized version and Demo Database BC (18-0) that will name it Demo18.
- Be sure to have task scheduler disabled in customsettings.config file, import the developer license and restart the service. At this stage, you should be able to login to your Demo18 database.
- Restore the tenant bacpac and give it a name (e.g. ProdSaaS). Be sure to download and use the latest SQL Server Management Studio version.
- Change Demo18 SQL Server Compatibility Level to match the same as ProdSaaS. In this case: SQL 2019 (150). You can use TSQL scripts or SSMS, tab Options.
- Run an elevated PowerShell ISE and use the following variables to automate the process. Change paths and names with your own, if needed.
$serverInstance = 'BC180'
$serviceAccount = 'EUROPE\DTACCONI'
$sqlDbServer = 'HEISEMBERG'
$sqlInstance = 'SQL2019'
$demoDatabase = 'Demo18'
$appDatabase = 'AppDatabase'
$tenantDatabase = 'ProdSaaS'
Import-Module 'C:\Program Files\Microsoft Dynamics 365 Business Central\180\Service\navadmintool.ps1'
- Publish, synchronize and install all your own Per Tenant Extensions (PTE) in Demo18. NOTE: it is super important that the .app files that you are deploying are exactly the same that has been deployed in ProdSaaS and without any further compilation (otherwise there will be a different Package ID). If you rename the .app file into 01xxx.app, 02xxxx.app, etc. to respect the appropriate order of deployment, then you can use the following script to deploy all of them in one row.
$appCollection = Get-item 'C:\TEMP\RESTORE\PTE\*.app'
$appCollection
foreach ($appItem in $appCollection)
{
Publish-NAVApp -ServerInstance $serverInstance -PackageType Extension -Path $appItem.FullName -SkipVerification -Force
}
Get-NAVAppInfo $serverInstance | ForEach-Object {Sync-NAVApp -ServerInstance $serverInstance -Name $_.Name -Force }
Get-NAVAppInfo $serverInstance | ForEach-Object {Install-NAVApp -ServerInstance $serverInstance -Name $_.Name -Force }
Sync-NAVTenant $serverInstance -Force
Get-NAVAppInfo -ServerInstance $serverInstance -TenantSpecificProperties -Tenant default | Format-List
- Extract Application database (AppDatabase) from Demo18
Export-NAVApplication -DatabaseServer $sqlDbServer -DatabaseInstance $sqlInstance -DatabaseName $demoDatabase -DestinationDatabaseName $appDatabase -ServiceAccount $serviceAccount
- Put AppDatabase in sigle user mode and change collation to Latin1_General_100_CS_AS and then back to multi user. You can do this step with SSMS or using TSQL script. NOTE: the generic rule here is that collation must be the same as the ProdSaaS one. Change Latin1xxx with the one that you have in ProdSaaS.
- Merge AppDatabase just created with ProdSaaS database.
Export-NAVApplication -DatabaseServer $sqlDbServer -DatabaseInstance $sqlInstance -DatabaseName $appDatabase -DestinationDatabaseName $tenantDatabase -ServiceAccount $serviceAccount
- Change applicationversion field in ProdSaaS system tables in order to match exactly the Base Application major version, minor version, build and revision as reported in Nav App Installed App In this case, as example: 18.5.29545.30194. Below the 4 system table where this has to be changed, together with other important changes that needs to be performed in these tables.
- dbo$ndo$dbproperty (change applicationversion field value to 18.5.29545.30194)
- dbo$ndo$tenantdatabaseproperty (change applicationversion field value to 5.29545.30194. Change collation field value to Latin1_General_100_CI_AS or, more in general, to the same as Demo18 database)
- dbo$ndo$tenantdatabaseversion (delete all entries except the latest one and in the one that you keep change applicationversion field value to 5.29545.30194)
- dbo$ndo$tenantproperty (change tenant=default)
- Export Nav App Installed App table content from Demo18 database and ProdSaaS database into an excel spreadsheet.
- For records that exists in both spreadsheets. Change Major version, Minor version, Build, Revision and Runtime package ID field values of Nav App Installed App records in ProdSaaS excel spreadsheet to match exactly the same Major version, Minor version, Build, Revision and Runtime package ID that you find in Demo18 excel spreadsheet. Apply these changes to Nav App Installed App records in ProdSaaS database table.
- For records that exists in ProdSaaS excel spreadsheet but does not exists in Demo18 excel spreadsheet. DELETE record in Nav App Installed App ProdSaaS database table.
- Delete all records in “Installed Application” table in ProdSaaS database (these will be recreated anyway).
- Set the service to connect to ProdSaaS and restart it.
Set-NAVServerConfiguration $serverInstance -KeyName Multitenant -KeyValue False
Set-NAVServerConfiguration $serverInstance -KeyName DatabaseName -KeyValue $tenantDatabase
Set-NAVServerConfiguration $serverInstance -KeyName EnableTaskScheduler -KeyValue False
Restart-NAVServerInstance $serverInstance -Force
NOTE: below the typical error in Event Viewer Application log when Nav App Installed App table records has not been appropriately hacked:
The runtime package ID '00000000-0000-0000-0000-000000000000' with package ID 'xxxx-xxxx…' for the tenant 'default' does not correspond to an installed app.
- Sync tenant
Sync-NAVTenant $serverInstance -Tenant default -Mode ForceSync -Force
Get-NAVTenant $serverInstance
Get-NAVAppInfo $serverInstance -Tenant Default -TenantSpecificProperties
- Create User and assign SUPER permission set
New-NAVServerUser $serverInstance -WindowsAccount $serviceAccount -FullName DTACCONI
New-NAVServerUserPermissionSet $serverInstance -UserName $serviceAccount -PermissionSetId SUPER
And now you have your own Frankenstein single tenant (legacy mode) environment on-premises.
Once again, be aware that these kinds of hacking are prone to changes by Microsoft, errors and data loss. This is just an exercise to understand the reason why it is not a supported task by Microsoft.
All responses (
2,091
