How to Use Microsoft Dynamics 365 Data Export Service

Microsoft Dynamics 365 Data Export Service (DES) can help you achieve near-real-time replication to harness your data’s power within your organization. Here’s how.

Many of our clients struggle with quickly and efficiently exposing their application data to wider audiences. For example, they may have a wealth of customer data in one of Microsoft’s customer engagement apps, such as Dynamics 365 Sales, but need to move the data into an enterprise database for easier access or consolidated reporting.

In this post, I will discuss Microsoft’s DES and Dynamics 365 Customer Engagement (D365 CE) offerings, two tools that work together to allow you to improve your organization’s decision-making and what-if analysis — but only if you use an SQL Server database as your data’s destination, such as SQL Server VM or Azure SQL.

After describing what DES is, we’ll look at how to set up DES, things to watch out for during setup, and the tool’s practical applications. After reading this blog, I hope you will feel more confident in your ability to benefit from your organization’s Dynamics Data Export Service.

What Is Microsoft Dynamics 365 Data Export Service?

DES is a change data capture (CDC) service Microsoft provides within its Dynamics framework. It uses the replication functionality embedded in SQL Server, which hosts your on-premise or online Dynamics 365 applications. Microsoft has created a profile and other tools to help expedite the setup and administration of this service. Still, at its core, DES is a CDC service offered across multiple database platforms.

However, in this case, you are required to use a SQL Server database as your destination (i.e., landing location.) This ensures the base replication functionality will work as expected. Once you have configured the necessary components, your selected entities or tables will sync, initially populating your destination database. From there, only changed records (“delta changes”) are sent instead of every record, allowing for more efficient processing.

The result is near-real-time replication. We typically see between five seconds to two minutes lag time between changes made inside your D365 environment and changes landing on your destination database. Lag time depends on many factors, including performance characteristics of the destination SQL Server environment, the load on D365, the number of concurrent changes in D365, and other factors.

Once up and running, you can view or monitor the replication activities inside D365 or within SQL Server Management Studio (SSMS). The image below is from one of our prototyping environments. It replicates dozens of D365 CE tables from our online tenant to an Azure SQL destination database.

Microsoft provides this service free of charge, and they deliver it as a solution they import into your Dynamics 365 environment. You must have a licensed destination SQL Server environment, which could be SQL Server VM or Azure SQL.

The Practical Applications For DES

One of the biggest challenges for our clients is providing available access to key data elements within their companies. D365 CE stores significant amounts of information that can help empower your organization’s decision-making and what-if analysis.

D365 CE is typically the “source of truth” for data. For example, your sales team may use it to store contacts, leads, opportunities, quotes, service levels (i.e., – cases), and key sales activities. Their ability to pull this information into a standardized data model across your organization can add significant value to your business. Here is a typical depiction of how many organizations use D365 CE:

Another consideration is using DES as a basis for backing up and archiving records within Dynamics. We will have another blog on this topic shortly. It will show how you can use Dynamics 365 Data Export Service not only to back up your data to Azure SQL for archiving but also to import those records back into D365 CE if needed.

How To Set Up Dynamics 365 DES

Dynamics is available via Microsoft’s AppSource. You simply import it into your online instance where you want DES to run. However, before you can get this service running, you need to accomplish some prerequisites.

The initial prerequisites are:

1. Azure SQL database service: Users with access to an Azure subscription that includes Azure SQL Database or Azure SQL Server on Azure virtual machines

2. Azure Key Vault service: Users with access to Azure subscription that includes Azure Key Vault

3. Microsoft Dynamics Customer Engagement apps: System Administrator role required

4. Pop-ups enabled on your web browser.

For additional information on setting up this service, review Microsoft’s document about How to Replicate Data to Azure SQL Database Using Data Export Service. To see how you can set up your system, please watch this video for a step-by-step walk-through of the installation process.

Key Things to Remember About D365 Data Export Service

When setting up the service, keep the following limitations and capabilities in mind about how this service works.

1. You must delete and recreate export profiles whenever you perform any of the following actions on an environment:
   1. Restore
   2. Copy (either full or minimal)
   3. Reset
2. Move (to a different country or region)
3. DES does not work for environments configured with Enable Administration mode turned on
4. DES does not drop (delete) the associated tables, columns or stored procedure objects in the destination Azure SQL database when the following actions occur:
   1. An entity is deleted
   2. A field is deleted
   3. An entity is removed from an export profile
5. DES tracks failed records in a separate table when resolving sync issues. A failed records synchronization feature will re-apply failed records when completed manually through the D365 CE Data Export form.

During testing, we have found the service to be very stable. However, keep in mind the more new functionalities and changes you add to the system the higher the likelihood of sync errors. Adding or removing fields in entities works as expected, but you will get a sync error if you re-add a previously removed field.

Conclusion

Having organizational-wide views of your data systems gives you valuable insights into how you’re using your system. We have found that DES is a powerful tool for accomplishing this goal once we have established SQL Server as the data’s destination. This allows us to better determine how you are using key functionality within Dynamics. However, other data replication tools, such as Oracle’s Goldengate or HVR, may be better for your environment. Working with a qualified vendor will help you make the best choice for your organization.