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.
- After the refresh operation is completed, you must sign off on the operation before you can perform another servicing operation, such as package deployment, database movement, or upgrade.
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.
- 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:
- Any previous servicing operation, such as a package deployment or prior database refresh, must be signed off from your environment details page.
- 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.
We recommend that you complete this procedure even if you don't use Retail components, because Retail functionality is included in all environments.
Before you continue, you must make sure that the following prerequisites are met:
The appropriate updates are applied to the target environment:
If the target environment runs the 7.3 release (December 2017), apply this update:
- KB 4091254
If the target environment runs the July 2017 release (7.2), apply these updates:
- KB 4035399
- KB 4045801
- KB 4091255
If the target environment runs version 1611 (November 2016, the 7.1 release), apply these updates:
- KB 4025631
- KB 4035355
- KB 4035492
- KB 4010947
Both the default channel database and the default channel data group must be named Default. If you've renamed them, you must change the names back.
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.
Refresh is denied for environments running Platform update 12 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 12 (November 2017) 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.
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:
- In the Project asset library, on the Software deployable packages tab, use the Import button to add the latest Financial Reporting Binaries asset to your project.
- Apply this binary package to your UAT 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.
We'd love to hear your thoughts. Choose the type you'd like to provide:
Our feedback system is built on GitHub Issues. Read more on our blog.