D365FO - Copy of databases across environments

When working with Dynamics 365 Finance and operations implementations, it is an obvious step to perform database movements across environments. We have been doing this in the previous version (AX2012) as well, where we had to change the environments related settings after the database copy manually.

Now we D365FO, we might end up in different scenarios based on the Tier-level of the environment. So I have tried to identify the #4 major scenarios in here and share the approach I have used. 

1. Copy of DB from Tier 1 env. to another Tier 1 env.
2. Copy of DB from Tier 1 env. to a Tier 2 env.
3. Copy of DB from Tier 2 env. to another Tier 2 env.
4. Copy of DB from Tier 2 env. to a Tier 1 env.

The below picture is an illustration of different types and corresponding solutions on a high level (sorry for my not-so-good-handwriting 😅)

#1 Tier 1 -> Tier 1 DB copy: 

This scenario can happen quite common if you have multiple tier-1 environment in your on-going project like, DevBoxes, Configuration env. or even an internal-test env. You might find yourself in a situation where you have the latest configuration in either a config/test environment and then you want that configuration to be moved to a DevBox for the developer to test an ongoing development with appropriate configuration and master data. 

In such scenarios, A simple SQL Server backup from Source environment and then restore to the target environment, followed by a full database synchronization, would suffice. There are several blogs online which could help you with SQL backup and restore, so not going into any further details in here. 

A tip however, is that, you should run the Environment reprovisioning tool available in LCS > Shared Library in order to get all the settings updated accordingly. If it is a devbox, you can do without this step as well. 

You can just use LCS > Maintain > Apply updates > select Environment reprovisioning tool from Asset library

Approach is different if you have an on-premise devbox - again lot of material available online already. 

#2 Tier 1 -> Tier 2 DB copy: 

This scenario could occur when you have been maintaining a Tier-1 environment with the Golden configuration and you would like to import that into your UAT or ultimately to your PRODUCTION environment. The process for this has been always quite complex - due to the fact that Tier-1 environment host their dynamics database on SQL Server, where as Tier-2 and above environments host their dynamics database on Azure SQL. 

And the database settings in Azure SQL are quite different from that of SQL Server, for example, there won't be any tempDB's associated, there won't be any windows/AD users associated with Azure SQL. 

So if you intend to copy SQL DB to an Azure SQL DB, the process involves running several scripts to make the mandatory changes. Several blogs available describing this process. 

That said, a tip is to challenge the cost vs risk assessment when choosing a Tier-1 environment for Golden configuration. Given the fact that, Microsoft now doesn't allow DB access to any Tier-2 and above environments. To run the scripts would involve Microsoft DSE team. So go for Tier-2 environment for Golden configuration to avoid the hassle. 

#3 Tier 2 -> Tier 2 DB copy: 

This scenario could happen when moving databases from Test environment to another Test (or) UAT environment. This is however pretty straight forward given the tools available via LCS. 

So as described in the above image. It is as simple as selecting Move database from LCS > Maintain and Export database to a .bacpac file, which will be stored in the Asset library automatically. 

And then go to the Target environment and again from LCS > Maintain > Move database > Import by choosing the saved .bacpac file.

The tip here is ensure that the database size is less than 200 GB for the best results. Microsoft has now improved the scripts to handle databases more than 200 GB, of course, but takes a lot of time to perform the import as well.  

#4 Tier 2 -> Tier 1 DB copy: 

This scenario would occur when you want to get either the PRODUCTION data or mostly your UAT data into a DevBox for troubleshooting purposes. The process earlier was as mentioned in the above picture, to create a copy of Azure SQL database and perform some scripts on top of it. However, I haven't tried this. I will update more about this in another post. 

A tip to perform the PRODUCTION database troubleshooting is to follow the step-by-step method from MS Docs.  

That's it for today. Please share your comments/feedback, if any. Thanks and Happy D365ing 😄