You can use Microsoft Dynamics Lifecycle Services (LCS) to perform a refresh of the database for Dynamics 365 for Finance and Operations to a sandbox user acceptance testing (UAT) environment. A database refresh lets you copy the transactional and financial reporting databases of your production environment into the target, sandbox UAT environment. If you have another sandbox environment, you can also copy the databases from that environment to your target, sandbox UAT environment.
Copying production data to your sandbox environment for the purpose of production reporting is not supported.
Self-service database refresh
With the goal of providing Data Application Lifecycle Management (also referred to as DataALM) capabilities to our customers without relying on human or manual processes, the Lifecycle Services team has introduced an automated Refresh database action. This process is outlined below:
- Visit your target sandbox on the Environment Details page, and click the Maintain > Move database menu option.
- Select the Refresh database option and choose your source environment.
- Note the warnings and review the list of data elements that are not copied from the source environment.
- The refresh operation will begin immediately.
Refresh operation failed
In case of failure, the option to perform a rollback is available. By clicking the Rollback option after the operation has initially failed, your target sandbox environment will be restored to the state it was before the refresh began. This is made possible by the Azure SQL point-in-time restore capability to restore the database. This is often required if a customization, that is present in the target sandbox, cannot complete a database synchronization with the newly refreshed data.
To determine the root cause of the failure, download the runbook logs using the available buttons before starting the rollback operation.
Data elements that aren't copied during refresh
When refreshing a production environment to a sandbox environment, or a sandbox environment to another sandbox environment, there are certain elements of the database that are not copied over to the target environment. These elements include:
- Email addresses in the LogisticsElectronicAddress table.
- Batch job history in the BatchJobHistory, BatchHistory, and BatchConstraintHistory tables.
- SMTP password in the SysEmailSMTPPassword table.
- SMTP Relay server in the SysEmailParameters table.
- Print Management settings in the PrintMgmtSettings and PrintMgmtDocInstance tables.
- Environment-specific records in the SysServerConfig, SysServerSessions, SysCorpNetPrinters, SysClientSessions, BatchServerConfig, and BatchServerGroup tables.
- Document attachments in the DocuValue table. This includes any Office Templates that were overriden in the source environment.
- Connection string in the PersonnellIntegrationConfiguration table.
- All users except the admin will be set to Disabled status.
- All batch jobs are set to Withhold status.
Some of these elements aren't copied because they are environment-specific. Examples include BatchServerConfig and SysCorpNetPrinters records. Other elements aren't copied because of the volume of support tickets. For example, duplicate emails might be sent because Simple Mail Transfer Protocol (SMTP) is still enabled in the UAT environment, invalid integration messages might be sent because batch jobs are still enabled, and users might be enabled before admins can perform post-refresh cleanup activities.
The System Administrator account in the target environment (UserId of 'Admin') is reset to the value found in the web.config file on the target. This should be the same value as that of the Administrator from Lifecycle Services. To preview which account this will be, visit your target sandbox Environment Details page in LCS. The value of the Environment Administrator field that was selected when the environment was first deployed is updated to be the System Administrator in the transactional database. This also means that the tenant of the environment will be that of the Environment Administrator.
If you have used the Admin User Provisioning Tool on your environment to change the web.config file to a different value, it may not match what is in Lifecycle Services. If you require a different account to be used, you will need to deallocate and delete the target sandbox, and redeploy selecting another account. After this, you can perform another refresh database action to restore the data.
Conditions of a database refresh
Here is the list of requirements and conditions of operation for a database refresh:
- A refresh erases the existing database in the target environment. The existing database can't be recovered after the refresh is completed.
- The target environment will be unavailable until the refresh process is completed.
- The refresh will affect only the Finance and Operations and Financial Reporting databases.
- Documents in Azure blob storage are not copied from one environment to another. This means that attached document handling documents and templates won't be changed and will remain in their current state.
- All users except the Admin user and other internal service user accounts will be unavailable. This process allows the Admin user to delete or obfuscate data before allowing other users back into the system.
- The Admin user must make required configuration changes, such as reconnecting integration endpoints to specific services or URLs.
- All data management framework with recurring import and export jobs must be fully processed and stopped in the target system prior to initiating the restore. In addition, we recommend that you select the database from the source after all recurring import and export jobs have been fully processed. This will ensure there are no orphaned files in Azure storage from either system. This is important because orphaned files cannot be processed after the database is restored in the target environment. After the restore, the integration jobs can be resumed.
- Any user with a role of Project owner or Environment manager in LCS will have access to the SQL and machine credentials for all non-production environments.
Steps to complete after a database refresh for environments that use Retail functionality
If you copy a database between environments, the copied database won't be fully functional until you run the Environment reprovisioning tool to make sure that all Retail components are up to date.
Follow these steps to run the Environment reprovisioning tool.
- In your project's Asset Library, in the Software deployable packages section, click Import.
- From the list of shared assets, select the Environment Reprovisioning Tool.
- On the Environment details page for your target environment, select Maintain > Apply updates.
- Select the Environment Reprovisioning tool that you uploaded earlier, and then select Apply to apply the package.
- Monitor the progress of the package deployment.
Some environment specific records cannot be moved along with the database from the source to the target environment. The following records are not moved to the target environment:
- Retail Self-Service installers
- Retail Cloud Scale Unit channel database configuration record
Database refresh and Retail Cloud Scale Units
As part of database refresh, scale unit channel database records (in the Channel Database form) cannot be moved across environments since they represent environment specific configuration.
For Retail Cloud Scale Units, you can regenerate the channel database record by issuing a re-deployment of your scale units in LCS. For more information, see Database refresh and Initialize Retail Cloud Scale Unit.
Database refresh and Retail Self-Service installers
Self-service installers are stored in environment specific storages that cannot be moved along with the database.
Installers are uploaded to the environment as part of a deployable package. The application and binary packages provided by Microsoft will contain the Microsoft provided installers. The Retail Deployable package produced by extensions through the Retail SDK will contain the extended installers. To make installer section available after database refresh, please apply the desired deployable package to the environment after database refresh.
Refresh is denied for environments running Platform update 11 or earlier
The database refresh process can't be completed if the environment is running Microsoft Dynamics 365 for Finance and Operations, Enterprise edition platform update 11 or earlier. For more information, see the list of currently supported Platform updates.
Incompatible version of Financial Reporting between source and target environments
The database refresh process (self-service or via a service request) can't be completed successfully if the version of Financial Reporting in the target environment is earlier than the version in the source environment. To resolve this issue, update both environments so that they have the latest version of Financial Reporting.
To determine the version you have installed in your source and target environments, visit the View detailed version information link on the Environment Details page.
Search for MRApplicationService and ensure that the target environment is greater than or equal to the source environment.
For customers that are using version 8.1 or later:
- Go to the Update tiles for your UAT environment. Save the updates to your Project asset library.
- Apply this package to your UAT environment.
- Verify that the error has been resolved.
For customers that are using version 8.0 or earlier:
- Review the Environment history of your source environment. Specifically, look for any "Platform and application binary package" that might have been deployed to the source environment and not the target environment.
- Apply this binary package to your target environment.
- Verify that the error has been resolved.
Incompatible application versions between source and target environments
The database refresh process (self-service or via service request) cannot be completed if the Application release of your source and target environment are not the same. This is because the data upgrade process is not executed by database movement operations such as refresh, and data loss can occur.
If upgrading your sandbox UAT environment to a newer Application version (for example, 7.3 to 8.1), be sure to perform the database refresh action prior to starting the upgrade. After your sandbox is upgraded to the newer version, you cannot restore an older production environment database in to the sandbox UAT environment.
Conversely, if your production environment is newer than your target sandbox, you will need to either upgrade the target sandbox prior to the refresh or simply deallocate, delete, and redeploy prior to performing the refresh.