Services

Restore Azure database to Tier-one

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

Please read the other related articles on this link https://kaya-consulting.com/category/lcs/

Which Agile methodology to use during ERP implementations

Agile SCRUM vs KANBAN

Do not use Agile SCRUM on customer implementations. With this one liner we start this blog. Why not and what should you use then?
In general, we see these days that all ERP implementations are done in an Agile way; the waterfall methodology has become an exception. But there are various Agile methodologies, the most common ones are SCRUM and KANBAN.

Customer projects

The method to use is important at Customer projects in our role of implementation partner (ERP/CRM implementations) but also on the ISV products we maintain ourselves as Kaya. Leading factor to determine which methodology to pick is not the methodology itself but how an organization is working and organized. Beside that the necessary skillset for building and releasing software plays a role too. Those skills are usually less present at a customer compared to a Microsoft, or other software, partner.

ISV products

In case you are in full control of your resources & requirements, planning fixed (release) dates is working fine. We see this at the implementation of ISV products. So, for those using Agile SCRUM would be a good choice.
However, on Customer implementations, we as a partner are not always in full control. Some examples are:

• Decisions that requirements are out of scope (read not before go-live)
• Capacity of customer resources are hard to plan. A partner has no control over it.
• Customer must accept the solution and usually they need support on the testing as they are not used to this.

So, the planning of a customer implementation needs much more flexibility. This flexibility can be better arranged with KANBAN. To have a closer look at what KANBAN is use following link https://www.scrum.org/resources/kanban-guide-scrum-teams.

Differences between Kanban and Scrum

Most important differences between KANBAN and SCRUM are:

	Scrum	Kanban
Cadence Regular	fixed length sprints	Continuous flow
Release methodology	At the end of each sprint	Continuous delivery
Roles	Product owner, scrum master, development team	Stakeholder
Key metrics	Velocity	ead time, cycle time, WIP
Change philosoph.	Avoid changes during the sprint.	Change can happen

Let us start with the Cadence. The functional confirmation of D365 F&S has a higher throughput than the development of code extensions. Beside the code extensions we also have the ISV solutions and the MS monthly updates.

This brings me to the next point. Release mythology: a code release is different from replicating a setup in a target environment.

Then we have the Roles. There are several Scrum Roles, where customers usually are not accustomed to, and the theory is that Kanban has no roles. To my opinion, this is not the case there is a stakeholder, wo defines what he needs and signs of when his wishes are realized. To a certain extend this resembles the Product owner role.

The change philosophy of KANBAN is more flexible. In case you have not started on a card, you can always put it back in the backlog. We see this happen when the Implementation gets disrupted by day to day topics or customer changes scope or priority.
The impact of using KANBAN at a customer implementation has an additional dimension. This dimension is the division between Development and Operations. In general, we can say the Partner is development and the customer is operations. This helps to determine who is responsible for what.

The next diagram narrows it even more clear on extensions to be delivered during implementation. The Partner (DEV) delivers a combined build into the asset library, it is up to the Customer (OPS) to continue from that point onwards.

OPS	DEV	DEV	OPS	OPS
Customer	Partner		Customer	Customer
Backlog	DEV	TEST	Acceptance	Production
	Code changes	Code changes	LCS asset	LCS asset
		Manual setup	Manual setup	Manual Setup

Additions to Kanban

Beside the concept of KANBAN and DEVOPS, Kaya also uses the phrase requested today, delivered tomorrow.

• When a Developer is finished, the solution is automatically available next day in TEST
• When a Tester is finished, he has in general a release candidate feature for the Customer
• The customer can almost do cherry picking on the release candidate features. The ones the customer select will be available next day in the combined build in the LCS asset library.

Are we in control?

So, are we in control? Yes! But be careful there is still a risk. This is so called work in Progress (WIP). The WIP on Acceptance is extremely important, all acceptance task must be completed before going to production. Who is responsible? The Customer (stakeholder) is responsible, but of course supported by the partner (which task should be completed first, dependencies, risks etc.). How can the Customer control it? Simply throttle on the requests that are picked from the backlog.

For other DEVOPS tips have a look at https://kaya-consulting.com/category/lcs/