Export to SQL

This step will discuss options for accessing the database directly using SQL commands, allowing for a variety of interactions other than that offered by Dynamics 365 and Power platform.

This is particularly useful when needing to process large volumes of data

Export to SQL

Common Data Service for Apps provides access to the data using the user interfaces or API, there is no direct access to the underlying database. This architecture has certain limitations when it comes to processing large volumes of data.

For example for the purposes of data warehousing, reporting, or using Azure machine learning and analytics tools. Replicating CDS data using Extract, Transform, Load (ETL) tools is possible but inherently complex to maintain.

Data Export Service is a service made available on Microsoft AppSource that adds the ability to replicate Dynamics 365 for Customer Engagement apps data to an Azure SQL Database store in a customer-owned Azure subscription.

• Note: The Data Export Service requires Dynamics 365 for Customer Engagement apps subscription, it is not available on Common Data Service for Apps plans.

The Data Export Service intelligently synchronises the preconfigured subset of Customer Engagement apps data initially and thereafter synchronises on a continuous basis as changes occur in the Dynamics 365 for Customer Engagement apps.

The Data Export Service simplifies the technical and administrative complexity of deploying and managing a data export solution – automatically managing both schema and data.

Use Cases

Having your data in Azure SQL database enables several possibilities, such as to set up an operational database for reporting with Power BI, build a staging area for a data warehouse, or to build machine learning models.

In addition, a synchronised database can also help offload any read-only workload centric queries, which reduces the load on the main Dynamics 365 system.

The key use case that the Data Export Service can help is to build scalable operational or enterprise BI reporting with Power BI in Azure backed by Azure SQL database.

This can also assist organisations migrating from on-premises installations where SQL Server Reporting Services (SSRS) have been used extensively to report on Dynamics 365 data.

Securing the Data

Data is exported to Azure SQL does not enforce the CDS security model. Anyone given access to the database has full access to the tables unless you implement your own security.

Installation and Configuration

Installation of the Data Export Service is done by a system administrator via the AppSource.

After Data Export Service is installed, one or more Export Profiles are created, each defining a set of entities to replicate the data from Dynamics 365 to a destination database.

Export profiles configure entities and relationships to synchronise data with the destination database. Destination schema is automatically created and updated based on entity and relationship definition.

Note: The Data Export Service uses change tracking feature for incremental data synchronisation. Ensure that all entities selected for synchronisation have the change tracking enabled.

The process of creating Export Profile is as follows:

To create a profile, a number of Azure services such as Azure SQL database are required. Your Azure administrator will provision and configure the required Azure services.

Activating the profile will start the initial synchronisation that may take a while depending on the volume of data in your instance and capacity of your Azure SQL database.

Troubleshooting

From time to time some operations fail. The Data Export Service retries a few times. However, failures may still occur. You can force a resynchronisation of only failed records or a resynchronisation of all records.

You can view information about synchronisation status by opening the profile record and using the Failed Records command. For more detailed analysis you can download complete log for failed records using Download Failed Records command.

Insufficient database storage and timeouts with Azure SQL database are the most common reasons for synchronisation failures, often due to inadequate Azure SQL Database plan. You may need to work with the administrator of your Azure service to resolve insufficient capacity issues.