We often have the need to restore a production database into a sandbox environment. For example to support training of new employees, debug and diagnose a production issue, perform destructive testing, or evaluate configuration changes without affecting the live environment. In the past, you had to open a support request for that to be handled by the Microsoft Service Engineering team. This meant waiting for hours and hours losing a lot of sometimes precious time as in the case of an urgent problem heavily impacting the customer business.
Not so long ago however, Microsoft introduced LCS “Self-Service Database Refresh”. What a cool feature is that! It makes our lives so much easier, at least in the light of Data Application Lifecycle Management (DataALM). In this two-part blog, I will tell you more about it.
Nowadays, we can simply logon to LCS, go to the Environment Details page of the environment we want to refresh the database in and select “Move database” from the Maintain menu. We then choose:
After that, we specify the restore point in time, which is the desired exact date and time of an automatic backup taken earlier, press Submit…
… and there we go. About only one hour later we have the production data available in our sandbox!
Before running the refresh, you might want to back up your newly created financial reports, security configurations, data management templates and/or specific setup data which are not available yet in production and need to be saved.
After that, you should notify the users of the target environment that the environment will be offline for a period of time and that they should log off before that, to avoid angry users having been kicked out automatically in a brutal way.
The production database contains of course a lot of environment-specific data you don’t want to have in your sandbox which needs its own specific settings. When the production database would have been copied as is, this might result in unwanted behavior like duplicate emails being sent because SMTP is still enabled in the target environment, invalid integration messages being sent because batch jobs are still enabled, etcetera. To avoid these kinds of issues in the target environment, Microsoft has taken some necessary precautions by not copying certain elements of the database to the target environment, for example:
- Email address information
- SMTP Relay server in the Email parameters
- Print Management settings of Accounts Payable and Receivable
- Server configurations, batch server groups, network printer details, client and server settings
- Dual-write setup
- All users are disabled except the Admin user, being the only one still available
- All batch jobs are set to Withhold
- All change-tracking on data entities is disabled
- All Microsoft-encrypted fields are cleared (because they can’t be decrypted on a different database server)
Post-refresh cleanup activities
As mentioned already, after the database refresh, only the Admin user has access to the sandbox environment. This allows the Admin user to perform some additional cleanup and reconfiguring activities before admitting the other users back into the system. These are activities like:
- Restoring financial reports, security configurations, data management templates and/or specific setup data from the backups created in the preparation phase
- Reconnecting integration endpoints to specific non-production services or URLs
- Reentering the BYOD Entity store connection string (an encrypted field which was cleared)
- Reconfiguring Exchange rate providers (also containing cleared encrypted fields)
- Reconfiguring Accounts Payable and Receivable Print Management settings
- Specifying environment-specific User options Color themes and legal entity Dashboard company image types to distinguish the sandbox environment more clearly from production
- Adding the various AOS instances to the batch server groups that you require
- Refreshing the data entities needed for Microsoft Power BI reporting
- Reconnecting the environment to the LCS Help configuration for task guides
- Entering the SMTP settings if you want to use email in your sandbox environment
- Reactivating the batch jobs that you want to run in your sandbox environment
- Additional reconfiguration based on your setup and the ISV solutions that are installed
Finally, when all activities have been completed and the system is configured as you require, you can enable the selected users and let them know that they can access the environment again. If many users must be enabled, you can complete this task more quickly by using the Microsoft Excel Add-In.
That’s it for now. In the first part of this blog series, we have seen an example of DataALM, how to run an automated database restore, which preparations can be done, and which post-refresh cleanup activities are needed. In the second (and last) part, I will discuss some options to handle the required reconfigurations more efficiently. Till next time!