Dynamics 365 Data Export Service: a Solution for CRM Online direct data access

Each customer I’ve ever worked for on switching from On-Premise to Cloud Dynamics CRM deployment asked questions like:

• Are we going to be able to run all our complex SSRS reports?
• Will our custom integration with our legacy systems still work?

Finally, I can now answer: YES! … And it works great.

Additionally, each of us, on some projects, had to come up with solutions on how to replicate Dynamics 365 (online) CRM data without the added complexity of managing an additional ETL tool; or how to keep synced data up-to-date where all updates in CRM Online are quickly synced without running frequent scheduled jobs.

But now we have the new Dynamics 365 – Data Export Service, a new Dynamics 365 App … and it’s free to use with Dynamics 365 Online.

Data Export Service Overview

Data Export Service allows us to replicate Dynamics 365 Online data to a Microsoft Azure SQL Database store in a customer-owned Microsoft Azure subscription. Data Export App allows us to synchronize the entire Dynamics 365 schema and data initially and then continues to synchronize data updates from Dynamics 365 Online organisation. The supported target destinations are Microsoft Azure SQL Database and Microsoft Azure SQL Server on Microsoft Azure virtual machines. Furthermore, Data Export service provides an interface for managing configuration and ongoing administration from within Dynamics 365.

Having your data in Azure SQL database enables several possibilities, since it can be used for custom reporting, integration with legacy systems or for building machine learning models.

Prerequisites

Prerequisites to use Data Export Service are:

• For Dynamics 365
  • Instance must have December 2016 update for Microsoft Dynamics 365 or a later deployed
  • Data Export Service App must be added to your Dynamics 365 instance
  • All Entities that will be exported must be enabled with change tracking and
  • User that runs the App must have the Dynamics 365 System Administrator security role
•  For Azure SQL Database service
  •  A customer owned Azure SQL Database subscription. This subscription must allow the volume of data that is synchronized.
  • Firewall settings should be set that Allow access to Azure services is turned off and that all appropriate Azure SQL database static IP addresses used by the Data Export Service are specified. More information can be found here: https://technet.microsoft.com/en-us/library/mt744592.aspx#SQLDB_IP_addresses. Alternatively, Allow access to Azure services can be can turned on to allow all Azure services access.
  • For SQL Server on Azure VM, the “Connect to SQL Server over the Internet” option should be enabled.
  • The database user must have appropriate permissions at the database and schema level. More information can be found here: https://technet.microsoft.com/en-us/library/mt744592.aspx#Prereq_DES
•  And finally, Customer owned Azure Key Vault subscription, which is used to securely maintain the database connection string must exist. More information on Azure Key Vault settings can be found here: https://technet.microsoft.com/en-us/library/mt744592.aspx#SetupAzureKV

 Data Export service Capabilities

There are quite a few capabilities that Data Export service includes:

• Metadata changes such as addition or modification of fields are automatically synchronized
• Full initial data synchronization
• Push-based delta synchronization as changes occur in Dynamics 365. Delta changes are pushed in few minutes
• Built-in recovery for addressing transient failures
• Delete log table to track deletes along with their timestamps
• Timestamps to compute delta based on sync time
• Monitoring and diagnostics on sync progress

Furthermore, the service also exposes a REST-based API that is divided into two groups:

• Metadata operations for exploring Dynamics 365 organizational structure, relationships, and connection information
• Profiles operations for configuring and managing each data replication.

The API documentation can be found here: https://discovery.crmreplication.azure.net/swagger/ui/index#

Data Export service Setup Overview

As mentioned above, Data Export service can be installed from AppSource. Once installed, login to Dynamics 365 as administrator and from the Settings menu:

Next step will be to define data export profiles. Creating a new Data Export Profile is a wizard driven process and it requires us to select the entities and relationships with a few other additional profile specific settings such as name, table prefix, or enable delete log.

Quick reminder: The only entities shown in the list are the ones for which change tracking is enabled.

Export profiles, once activated, start the data synchronization – first with metadata sync, followed by initial data sync, and then with continuous delta data sync.

Metadata sync automatically creates and updates tables in the destination database.

During initial sync, the existing data in Dynamics 365 for added entities is synchronized with the destination database. After initial sync, delta sync starts, and any create, modify or delete record change for entities added to export profile is automatically synchronized.

Progress on all of the above can be followed with status and notification counters from Dynamics 365 UI.

Metrics for data synchronization during initial sync and delta sync are shown in terms of notification counters. A notification encapsulates a record change – it maps to create, modify, or delete change of a record. There are 3 main metrics that are captured:

• Total notifications show all notifications to be processed for data synchronization.
• Successful notifications show all notifications that have been processed successfully and the corresponding insert/update/delete change that has been synchronized to the destination database.
• Failed notifications show all notifications that have failed during processing.

Notification counters at the end of initial sync show a count for all records synchronized from Dynamics 365. For any errors that occurred during synchronization, you can click Download Failed Records to obtain a URL to connect with Azure Storage Explorer to see the log of failures and address the reason behind those failures such as database is down or out of space.

More information and detailed steps to setup Data Export service can be found here: https://blogs.msdn.microsoft.com/crm/2017/04/11/introduction-to-dynamics-365-data-export-service/

Conclusion

All in all, it’s a great new feature that helps us with customers where we need to move the on-premise Dynamics CRM to the cloud and we need to save time and cost for all custom reporting or integrations with legacy systems.

Our customers are very excited after we introduced them to Data Export service, how about yours?