Quick Steps to Replicate Data from Dynamics 365 Online to Azure SQL Database

If you want to access a large set of data from Dynamics 365 (online) CRM, the usual way is to either use the API’s provided by Microsoft or query the system using the Fetch XML. This could affect the CRM application performance as we will be directly querying the production system. Also, there are many limitations on the reporting side of CRM when using the Fetch XML. To avoid these issues, we can replicate the Dynamics Database with an external Azure SQL server using the Microsoft Dynamics 365 Data Export Service. Using the Data Export Service, we can use the Azure database as a Data Warehouse which has a copy of the required CRM entities inside. In this post, we will see how we can install and configure the Microsoft Dynamics 365 Data Export Service.

What is Data Export Service?

Data Export Service is provided as a free add-on in the AppSource portal by Microsoft. This helps to synchronize data with CRM and Microsoft Azure SQL Database or Microsoft SQL Server on Microsoft Azure virtual machines. Below given are the prerequisites for using this service.

Prerequisites

• A Dynamics 365 for Customer Engagement apps version 9.0 or later version instance
• A user that is assigned with the Dynamics 365 for Customer Engagement apps System Administrator Security role
• Change tracking enabled for the CRM entity which needs to be synced
• Azure SQL Database
• Azure Key Vault (operating under the same tenant and within the same Azure Active Directory)
• Data Export Service add-on

How to install Data Export Service

Navigate to the Microsoft AppSource portal and search for Data Export Service. Then click on ‘Get it Now’ as shown below.

This will take you to the organization selection page. Once you select the organization in which you are planning to install the Data Export Service, click on Agree.

This will initiate the installation in your selected instance. The installation will take a few minutes.

Setting up the Azure Key Vault

The Azure Key Vault is used to store the Azure database connection string. This is mandatory and can be easily set up by following the link: How to set up Azure Key Vault. Microsoft has given a PowerShell script, which we need to run as the Azure account administrator permission to create the vault values as required. Once the vault is set up, open it and copy the secret identifier URL required at the time of creating the export profile.

Setting up the Azure Database for the synchronization

To open the database firewall for the Data Export Service, we need to enable the ‘Allow access to Azure service” option in the database firewall as given below.

Also, the SQL User provided inside the connection string in the Azure Vault should have CREATE TABLE, CREATE TYPE, CREATE VIEW, CREATE PROCEDURE, ALTER ANY USER and VIEW DATABASE STATE Database permissions and ALTER, INSERT, DELETE, SELECT, UPDATE, EXECUTE and REFERENCES Schema permissions.

Setting up the export inside CRM

Once the Data Service Installation is done, we will be able to see the Data Export module inside the CRM Settings as shown below.

• Click on the new button to create an export profile. The export profile asks for all the information required to set up the sync like the key vault URL, entities to sync, retry details etc.

• Press Validate to validate the Key vault connection. If the connection is working, then a success popup will come. Press next.

• Select the entities to sync.

• Select relationships if required.

• Click on create and activate.

This will create all the supporting Databases and stored procedures in the Azure database and start the initial push of data. Data Export Service syncs both metadata and data for the selected entities.

Conclusion

As seen above, the Data Export Service helps us set up a data replication flow easily. These exported data can be consumed by tools like Power BI to create complex reporting models which were not possible earlier.