Request sandbox database refreshes
You can use Microsoft Dynamics Lifecycle Services (LCS) to request a refresh of the database for Microsoft Dynamics 365 for Finance and Operations to a sandbox user acceptance testing (UAT) environment. A database refresh lets you copy the transactional database of your production environment into the target, sandbox UAT environment. This process includes copying the Financial Reporting database. If you have another sandbox environment, you can also copy the databases from that environment to your target, sandbox UAT environment.
This functionality lets you use production data to test upcoming code changes in a UAT environment. You can also copy a production database into a UAT environment for debugging purposes.
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 Application Lifecycle Management capabilities to our customers without relying on human or manual processes, the Lifecycle Services team has introduced an automated Refresh Database action. To refresh your sandbox environment with data from another environment, you can perform this action as part of the Self-service action that is in Public preview. This process is outlined below, and is fully-supported functionality.
- Visit your target sandbox 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 is present in the target sandbox that 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. This is because the data is either environment specific, or could cause operational issues such as sending realistic email out of a non-production environment. These data 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.
- Mail provider is reset to SMTP in the SysEmailParameters table to prevent accidental, outbound mail using the Exchange provider.
- 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 for the administrator and Microsoft service accounts are disabled.
- All batch jobs are set to Withhold status.
The System Administrator account in the target environment (UserId of 'Admin') is reset to the value 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 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.
Database refresh via service request
As of October 2018, database refresh requests must be signed off before another refresh of the same environment can be started. This is to support future automation of database movement operations. To sign off, visit your Environment Details page and click the Signoff button. You can create many database refresh service requests out in to the future, however you must sign off in between each one.
Service requests for database refresh will not be accepted for servicing dates after January 31, 2019. After this date, all refresh operations will be performed using the self-service actions outlined above.
The Microsoft Service Engineering team will take your environment offline, complete the refresh, and then bring the environment back online. You can expect the downtime period to be approximately two hours. The period after you enter your request and before our Service Engineers take action will be longer than your environment's downtime. In the future, we will provide a self-service method that you can use to perform your database refreshes.
In LCS, on the Project home page, select Service requests.
On the Service requests page, select Add on the toolbar, and then select Database refresh.
In the Request for database refresh dialog box, follow these steps:
- In the Environment name field, select the environment to refresh.
- In the Database field, the database to refresh is always the Microsoft Dynamics AX database or the Finance and Operations database. Other databases, such as Entity store aren't currently supported for database refresh.
- Carefully read and acknowledge the statements that have check boxes next to them.
After you submit your request, you are returned to the list of work items. Here, you can view the status of the request, or reschedule or cancel the request.
Ensure no prior servicing request on your environment is awaiting signoff or rollback. Visit your Environment details page and sign off any completed refresh or package deployment.
When the Service Engineering team has acknowledged that they can complete your request, the status of the request is changed to Request accepted. At this point, you can follow any of these steps:
- Wait for the Service Engineering team to complete the refresh. When the restore is completed, the status is changed to Succeeded.
- Reschedule the request by selecting the ID, or by selecting the request and then selecting Reschedule on the toolbar. You can then change the dates and times for the downtime window.
- Cancel the request by selecting the request and then selecting Cancel on the toolbar.
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.
- Requests must be submitted 5 hours before the desired downtime window, to help ensure that resources will be available to complete the request.
- 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 disabled. This process allows the Admin user to delete or obfuscate data before allowing others 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 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.
- All batches that were set to run are set to Withhold status, to stop batches from running before the environment has been reconfigured.
- The SMTP server configuration, all email addresses, and all Print management settings, including network printers are removed.
- 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.
Incompatible version of Financial Reporting between source and target environments
The database refresh process (self-service or via service request) cannot be completed successfully if the version of Financial Reporting is different between the source and target environment. To resolve this issue, update both environments to have the latest version of Financial Reporting.
- Visit the Asset Library in your implementation project, and then click Software deployable package.
- Click the Import button and find the latest Microsoft Dynamics Financial Reporting binary update package and select this for import.
- Apply this package to both the source and target environments to ensure they are both using the latest version.
For instructions about how to determine the version you're using, watch the How to find the version of Report designer video.
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.