Balsoft Data Refresh: prepare D365 F&O after every database copy

Every database refresh ends the same way. Production data arrives in the UAT sandbox, and then someone spends the next hour in System administration: enable the testers again, give them their roles back, point the SMTP server and the integration URLs at the test systems, recreate the business event endpoints, release the batch jobs that should run and leave the rest on hold. Do it by hand and something is forgotten. A forgotten SMTP setting sends test mails to real customers. A forgotten URL sends test orders to the production warehouse.

For years we have done this step for our customers with our own tooling, after every copy. We have now rebuilt that tooling as one clean product and decided to give it to the Dynamics 365 community: Balsoft Data Refresh.

What a copy leaves behind

Microsoft documents what a database refresh does not copy or resets on purpose, so that a sandbox does not act like production right after the copy. That is the right default, but the work that follows is always the same for a given environment, and Microsoft leaves it to you. Balsoft Data Refresh closes each of these gaps, and the last row is what makes it fit into any release process:

Set it up once, in production

In Finance and Operations the feature is called Database refresh. It has its own module in the navigation and a workspace.

The setup tables are copied with the database. So you maintain the setup of all environments in production, and every copy brings it along. In the target, Balsoft Data Refresh only runs the part that belongs to that environment.

Environments are identified the way Microsoft does it in the monitoring and telemetry parameters: by environment ID (the Power Platform environment ID, or the LCS environment ID) and an environment mode. Add current environment fills in the ID of the instance you are on. Each environment gets its tasks, which you switch on and put in order.

A failed task does not stop the job unless Stop on first error is set. Every run is recorded as a job with the result of each task and its messages.

The tasks, one by one

Each task has a small setup form of its own, opened with Set up on the task. Here is what each one does and when you need it.

Enable users

After a copy only Admin can sign in. This task enables the users you list for the environment, for example the key users and testers of UAT. Every other user keeps its state, so production users who should not work in the sandbox stay disabled. The setup is one grid with the user ID.

Assign security roles

Testers often need roles in UAT that they do not have in production, for example a key user who tests the approval workflow as approver. This task assigns roles per user and environment, or enables an assignment that is disabled. Roles are stored by their AOT name, which is the same in every environment, unlike the record ID. The form shows the role label, so you still pick "Accounts payable clerk" and not a technical name.

Cancel pending workflows

The copy brings every open workflow instance from production. If they keep running in the sandbox, approvers get tasks and mails for documents that belong to production. This task cancels the pending instances of the workflows you list, or of all workflows when the row has no workflow, in all or in selected companies. It only acts on what is set up: without a row it changes nothing.

Set field values

This is the task that replaces the data package. It writes any value into any table field: the SMTP relay server, the URL of an integration, a file share, a parameter that must differ in test. An optional Only where field with Has value limits the change to matching records, and each row has All companies or a list of companies.

Every row is checked when it is saved, not during the next refresh: the field must exist and must not be a system field, the value must fit the field type, and a table that stores no data is refused. For a sensitive value, such as a password of an integration, the row only names a pipeline secret and the pipeline passes the value at run time.

Create business event endpoints

Endpoints are the part people dread most after a copy, because each one needs a Key Vault, an app registration and a client secret. This task creates endpoints of every Azure type (Service Bus queue and topic, Event Grid, Event Hub, Blob storage) through Microsoft's own endpoint classes and activates their business events in the companies of the endpoint. They show up in the standard Business events workspace exactly as if you had created them there.

The client secret is never stored. The setup row only names it, and the pipeline passes the value with the service call. When you save an endpoint, Balsoft Data Refresh sends Microsoft's test event to it, so a typo in a queue name shows up while you set it up and not after the next refresh.

Release batch jobs

The copy puts every batch job on Withhold, which is good: nothing runs that should not. But some jobs must run in UAT, for example workflow processing, the e-mail distributor or alerts. List them here and the task puts them back to Waiting. All other jobs stay on hold.

Clean up data

A UAT copy of production is large, and testers rarely need five years of journals. Cleanup rules work in three modes: Delete older records keeps a number of days, Delete all records empties a table in all or in selected companies, and Truncate table empties it for all companies at once. Delete actions are followed by default, so the lines of a journal go with its header.

Only transaction, worksheet, staging and miscellaneous tables can be chosen. Master data and parameters are not on the list. Long cleanups run in batch, and the tasks after them follow in the same batch job so the order is kept.

Automate the copy with a pipeline

You can start a run from the menu, but Balsoft Data Refresh is built for one thing above all: a refresh that runs without anyone touching it. The repository contains a sample Azure DevOps pipeline, Pipelines/copy-and-refresh.yml, that does the whole job:

Run it on demand before a test phase, or uncomment the schedule for a weekly refresh. The core of the pipeline is short:

1stages:2  - stage: Copy3    jobs:4      - deployment: copy5        environment: CONTOSO-UAT          # approval before the sandbox is overwritten6        timeoutInMinutes: 3607        strategy:8          runOnce:9            deploy:10              steps:11                - task: PowerPlatformToolInstaller@212                - task: PowerPlatformCopyEnvironment@213                  inputs:14                    authenticationType: PowerPlatformSPN15                    PowerPlatformSPN: CONTOSO-PROD    # service connection of the source16                    TargetEnvironmentUrl: https://contoso-uat.crm4.dynamics.com17                    CopyType: FullCopy1819  - stage: Refresh20    dependsOn: Copy21    jobs:22      - job: refresh23        steps:24          - task: PowerShell@225            inputs:26              pwsh: true27              filePath: Pipelines/scripts/Invoke-DatabaseRefresh.ps128              arguments: >-29                -FinOpsUrl https://contoso-uat.sandbox.operations.eu.dynamics.com30                -TenantId $(TenantId) -ClientId $(ClientId)31                -EnvironmentId UAT -SecretNames ServiceBusAppSecret32                -RequestId $(Build.BuildId)33            env:34              BDR_CLIENT_SECRET: $(ClientSecret)35              BDR_SECRET_ServiceBusAppSecret: $(ServiceBusAppSecret)

The script sends this request to POST /api/services/BDRServices/BDRService/run:

1{2  "_request": {3    "EnvironmentId": "UAT",4    "TaskTypeNames": [],5    "RequestId": "<pipeline run ID>",6    "RunInBatch": false,7    "ReturnResultOnFailure": true,8    "Secrets": [ { "Name": "ServiceBusAppSecret", "Value": "<from the pipeline>" } ]9  }10}

An empty TaskTypeNames runs all active tasks of the environment. The result lists every task with its status, records affected and message. A repeated call with the same RequestId returns the first job instead of running again, so a retry after a timeout is safe. Without ReturnResultOnFailure a failed job comes back as an HTTP error, so even a simple pipeline that only checks the status code does not open the environment after a failed run.

Before the first run you need three things, all listed at the top of the pipeline file:

• A Power Platform service connection for the source environment, with a service principal that may copy environments.
• A variable group DatabaseRefresh, ideally linked to Azure Key Vault, with the tenant ID, client ID and client secret of an Entra ID application, plus one secret per secret your setup names. Register that application in the sandbox under System administration > Setup > Microsoft Entra ID applications, with a user that has the role Database refresh administrator. Because the setup comes from production, register it there and the copy brings it along.
• An Azure DevOps environment for the sandbox with an approval.

A copy of a Finance and Operations environment can take several hours, and a job on a Microsoft-hosted agent stops after 6 hours. For longer copies use a self-hosted agent, or start the copy in the Power Platform admin center and run the pipeline with skipCopy, which only runs the refresh. The script also works on its own, from any machine with PowerShell. For an LCS environment, replace the copy stage with a refresh through the LCS Database Movement API; the refresh stage stays the same.

Safety first

A tool that writes into a freshly copied database must not run in the wrong place:

• It never runs in an environment with the mode Production. The check is in the job itself, so it also applies to service calls.
• It only runs when the current instance is set up as an environment, and the environment ID in a service call must match the instance.
• Enable users and Cancel pending workflows only act on what is set up. Without setup they change nothing.
• One job per environment at a time. A second call is refused while a job is running or scheduled.

How we test it

After every release the model is deployed to a unified developer environment, and a suite of more than 300 end-to-end checks runs against it through the web client and the service: every setup form, every validation, real endpoints of every Azure type with Microsoft's test event, service runs, and the cleanup of everything the tests created.

That suite found a bug that is worth knowing about if you write X++ against business events. BusinessEventsEndpoint::findByName goes through the record cache of the endpoint tables. After an endpoint was deleted in the Business events workspace, it could still return the deleted record, so our task skipped the endpoint as "already exists". Reading the table with the cache switched off fixed it:

1private RefRecId existingEndpoint(BusinessEventsEndpointName _endpointName)2{3    BusinessEventsEndpoint standardEndpoint;45    standardEndpoint.disableCache(true);6    select firstonly RecId from standardEndpoint7        where standardEndpoint.Name == _endpointName;89    return standardEndpoint.RecId;10}

Get it

The model, the setup guide, the copy pipeline and the build and release pipelines are on GitHub. Deploy the model, assign the role Database refresh administrator, set up one environment in production and let the pipeline do the next refresh.

The task types are plugins, built like Microsoft's data archive feature. If you need a task we do not have, add it in your own model without changing ours. Bug reports and ideas are welcome as GitHub issues. If you want help to fit it into your release process, talk to us.