Restore Azure database to Tier-one

Dick Wenning

8,705

Like ( 0 )

Report

In my current project I get a high demand on request for restoring Azure databases to Tier-one. Out of the box there is no support on it from LCS point of view. But that does not imply that it is not possible.
At this moment we create an additional DEVOPS release pipeline that is manually triggered. The steps of that pipeline are shown in the next picture.

The Steps

The steps we are taking are in general

Request access token to LCS
Download the bacpac file from the LCS asset library
Import the bacpac file into a new database
Stop all services
Swap the new database with the old database
Synchronize the database (there could be new tables & fields)
Reset Financial Reporter
Start all services

With the current security on the MS hosted Tier 1 boxes, it is getting more complex to completing all these steps. The complexity is related to less privileges. You do not have administrator access anymore.
When you want to perform actions that requiring administrator access, you can host a D365 environment hosted on your own azure subscription. Here you can open Pandora’s box, but Microsoft does not have any responsibility or privileges on this environment.
We prefer to have all PowerShell inline, in case you prefer to use local PowerShell, below example shows you how to transfer the parameters to PowerShell files

Now let us go through the steps one by one.

Lets Download the Database

Below is an example for downloading and restoring the Bacpac file. For more details please read https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/database/dbmovement-scenario-exportuat#import-the-database

cd C:\temp\
<# asset list #>
$refreshUrl = “https://lcsapi.lcs.dynamics.com/databasemovement/v1/databases/project/$(LCSPRPOJID)”
$refreshHeader = @{
Authorization = “Bearer $(TOKEN)”
“x-ms-version” = ‘2017-09-15’
“Content-Type” = “application/json”
}
$refreshResponse = Invoke-RestMethod $refreshUrl -Method ‘GET’ -Headers $refreshHeader
$DatabaseAssets = $refreshResponse.DatabaseAssets;
<# find latest BACKUP on LCS #>
$cstzone = [System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId( (Get-Date), ‘W. Europe Standard Time’)
$filedate = Get-Date( $cstzone).AddDays($(DAYS)) -f “yyy-MM-dd”
$BackupName = “GOLD-$filedate”
Write-Output $BackupName
$url = $DatabaseAssets | Where-Object {$_.Name -eq $BackupName} | Select-Object -Property FileLocation
$output= $DatabaseAssets | Where-Object {$_.Name -eq $BackupName} | Select-Object -Property FileName
Write-Output $url.FileLocation
Write-Output $output.FileName
<#remove old downloades#>
<#Remove-Item –path C:\temp* -include *.bacpac -whatif -force>
<# start download #>
Import-Module BitsTransfer
Start-BitsTransfer -Source $url.FileLocation -Destination $output.FileName

$importFile = $output.FileName
Write-Host “##vso[task.setvariable variable=BACKUPNAME]$BackupName”
Write-Host “##vso[task.setvariable variable=FILENAME]$importFile”

The next step is creating the database

<#import Bacpak file#>
cd C:\temp\
$fileExe = “C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\SqlPackage.exe”
& $fileExe /a:import “/sf:$(FILENAME)” /tsn:localhost “/tdn:$(BACKUPNAME)” /p:CommandTimeout=1200

Stop environment

The step for start or stopping the service are not so complex, you can even run them inline on the release pipeline (For start change the stop to start)

<#stop environment#>
net stop W3SVC
net stop DynamicsAxBatch
net stop Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.exe
net stop MR2012ProcessService
net stop ReportServer

Swap Databases

Now all systems are down so we can swap a database.

<#rename Database#>
$server = “.”
Write-Output Drop AXDBOLD
Invoke-Sqlcmd -ServerInstance $server -Query “DROP DATABASE IF EXISTS AXDBOLD”
<#rename Database#>
$server = “.”
Write-Output Drop AXDBOLD
Invoke-Sqlcmd -ServerInstance $server -Query “DROP DATABASE IF EXISTS AXDBOLD”
Write-Output renaim AXDB to AXDBOLD
Invoke-Sqlcmd -ServerInstance $server -Query “ALTER DATABASE AXDB MODIFY NAME = AXDBOLD”
Write-Output renaim new DB to AXDB
Invoke-Sqlcmd -ServerInstance $server -Query “ALTER DATABASE [$(BACKUPNAME)] MODIFY NAME = AXDB”
Write-Output create technical users
Invoke-Sqlcmd -ServerInstance $server -Database axdb -Query “the latest scripts MS”

I will not distribute the latest scripts MS statements, because it can change depended on latest updates from MS. In general, it should be in line with https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/database/dbmovement-scenario-exportuat#update-the-database

Lets Synchronize

The next step is about synchronizing the database, there are several ways to do it

Start Visual studio and synchronize manually
Scripts that need admin privileges that you do not have
Use the next one

<#sync database#>
$command = Join-Path “K:\AosService\PackagesLocalDirectory” “\Bin\SyncEngine.exe”
$connectionString = “Data Source=””.””;Initial Catalog=AXDB;Integrated Security=True”
$arguments = “-syncmode=fullall -binfolder=K:\AosService\PackagesLocalDirectory -metadatabinaries=K:\AosService\PackagesLocalDirectory -connect=””$connectionString”” -fallbacktonative=False -verbosity=Diagnostic -continueOnError=false”
Start-Process -FilePath $command -ArgumentList $arguments -Wait -PassThru -RedirectStandardError “SortError.txt” -RedirectStandardOutput “log.txt”

Management Reporter

And finally reset the database mart, normally you get again that admin privilege issue , but by adding the option -scope local, we bypass this requirement

<#reset management reporter#>
K:
cd K:\MROneBox\MRInstallDirectory
Import-Module .\Server\MRDeploy\MRDeploy.psd1 -scope local
Reset-DatamartIntegration -Reason OTHER -ReasonDetail “” -force

Please be aware the Bacpac to Tier1 is still a work in process, we must update the scripts regularly based on the requirements from MS

Additional we have on the release pipeline an RSAT provisioning step running, for more details please read XXXX