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/
Are those SLAs a Fool’s Bargain?
Alright, a question for you guys. Have you ever heard of a SLA (service level agreement)? It’s the thing you agree on with your implementing partner after they have successfully implemented your new system. If you’ve come so far, congratulations. You’ve probably managed to overcome a lot of hiccups along the way but you made it, you now have a system that meets all your needs.
But your journey is not over yet. There has to be maintenance in order to keep the system fresh. Because of this you come to a maintenance agreement with your partner. Of course not just for AX but also for BizTalk, SQL, BI and all the other stuff. Now you can feel confident that along the way your system will always be up to date and it gives you the ultimate guarantee nothing can go wrong anymore, but does it? What does it guarantee? And what is the cost of this guarantee?
What you agreed on in SLA
Most service level agreements -SLA- probably contain nothing more than the following things:
- The right to have your system updated.
- A promise that they will help you with it and a discount on the hours spent helping you.
- Technical support
- They may even include a number of free minutes, you can use to call someone at a helpdesk. What you don’t know is that while you’re on hold and listen to the beautiful tune, your minutes run out.
What you actually wanted
You just wanted a system that runs smoothly and makes you a satisfied user. But your partner will push you to do all the updates because otherwise everything will inevitably go wrong. This is kind of comparable to the millennium madness. Remember? So many people were convinced that all the electronic devices were going to crash, some even made a lot of money because of others` ignorance, and eventually, when the clock ticked 12, nothing happened. Everything was working as it was supposed to be. With SLA’s it’s kind of the same. They just want you to be scared enough to spent a lot of money on something that is not always necessary, the same goes for updates.
But what are your options? Can you just do nothing? Or is there a way to avoid all these costs and headaches? I will let you know in the next blog 😉 but meanwhile subscribe to our newsletter so you can receive our latest blogs straight to your inbox.
What do you think about SLA`s and updates? Share your thoughts and if you liked this post, please share it.
Lean consulting
Lean Consulting is an attitude, an approach, and an implementation methodology that eliminates nonvalue-add activities in a software implementation, reimplementation, or release upgrade. It empowers the client to control the cost of their software project by economically guiding them toward self-sufficiency and ownership of their ERP system.
One of the most important characteristics of a Lean Consulting team is that it is smaller. At times, a niche specialist may be required to implement a certain complex application, but the Lean Consultant will have a greater breadth and depth of application knowledge. With the breadth of knowledge Lean Consultants have, fewer consultants are needed. A smaller, more knowledgeable team is more efficient. Like familiarity, having fewer consultants reduces communication errors and redundancy, translating directly to savings.
Lean Consulting starts with a highly interactive consulting team. Each member of the lean team is selected for their strengths from a larger roster in order to meet the unique challenges of a project. But the roster from which they are chosen must be small enough that the team members have all worked together on many implementations. Familiarity is the very foundation of any team. A team that has worked together many times before has reduced communication errors and they trust each other’s strengths, thereby eliminating redundant analysis.
Another crucial characteristic of a Lean Consulting team is experience. Lean Consulting is all about cutting out the waste to make the process more efficient, and in the world of consulting, time really is money. The more time a consultant spends learning on the job, whether it’s learning about the client’s industry, or figuring out how to implement an obscure piece of the functionality, the more money is wasted. In Lean Consulting, the learning curve is reduced to a minimum. Lean Consultants have 8 to 15 years of experience with the software platform, so they know how to implement it. They have diverse industry experience, so that they can bring best business practices to every implementation without learning on the job. The deep knowledge and understanding of the software that each Lean Consultant has is invaluable when implementing something as robust as an ERP system.