How to massage your MsDyn365FO environment related data after database refresh from production

In this post I want to talk about how to massage your environment related data after a database refresh from production to a Microsoft Dynamics 365 for Finance and Operations sandbox environment. Massage in this context means that you want to replace/clear your production environment related data/setup, e. g. endpoint references to other production related systems, after copying over the production database to a sandbox environment.

d365fo.tools plus SysClassRunner approach

I think the most common approach is to activate the users via d365fo.tools, which will execute direct SQL against the sandbox database and after this replace/clear the data via direct SQL or SysClassRunner class. In my case, we´re using d365fo.tools to activate all user via the following command:

Enable-D365User -DatabaseServer “servernam.database.windows.net” -DatabaseName “databasename_axdb_1234567890” -SqlUser “sqladmin” -SqlPwd “Password” -Email “%@%”

After activating the users we´re starting a SysClassRunner class to change replace/clear the environment related data. Calling the SysClassRunner is also handled with d365fo.tools via the following command:

Invoke-D365SysRunnerClass -ClassName GwsEnvSysMaintainAfterDbRefresh -Company USMF

Custom service plus admin user approach

The direct SQL approach is a bit more tricky with self-service environments, because you always have to request a JIT after the database refresh. There is a better way to massage your environment related data, by using a custom service in combination with the admin user. With this approach you need to move the code from the SysClassRunner class to a custom service, to call the logic via the custom service. In addition to that you need to add X++ code, to activate all or specific users, to your custom service.

To make sure you´re able to call the custom service after database refresh, you need to add an “Azure Active Directory applications” linked to the admin user. Because the admin user is enabled by default after database refresh, you can use the linked client id to call the custom service to massage your data.

Custom service plus DBMovementAPI user approach

Because using the admin user for the custom service approach is not the nicest way, Lane Swenka and his team is working on feature to use another user for this approach.

With this new feature you can assign the  “Azure Active Directory applications” to a user with the id DBMovementAPI, which will then get enabled after the refresh from production to sandbox in addition to the admin user. For safety reasons it´s recommend to disable the user in the production environment and enable only in the sandbox.

Foolproof via X++ for all approaches

When your using a SysClassRunner class or a custom service, it could happen that you or another users uses the logic in a production environment which will case quite some damage to the environment data. To prevent this scenario I recommend to implement a foolproof via X++ to exit your logic in case it´s called in a production environment. We´re implementing this logic by checking the current environment URL like this:

if (strContains(UrlUtility::GetUrl(), ‘XppGroupies.operations.dynamics.com’))

{

Throw error(‘This a production environment’);

}

Or

if (!strContains(UrlUtility::GetUrl(), ‘sandbox.operations.dynamics.com’))

{

Throw error(‘This a production environment’);

}

Appendix

If´ve received some feedback from Brad Bateman on Yammer to the foolproof logic, I want to share with you here:

One thing I wanted to point out is that UrlUtility::GetUrl() does not work for oData calls (returns null), so if you setup your non-prod X++ code as an oData action, then beware of this. I haven’t tested it with a custom service.

2 thoughts on “How to massage your MsDyn365FO environment related data after database refresh from production

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s