Home
Dynamics 365
Dyn365CE
Data Export Service for D365 CDS v9.1x and Azure SQL

Data Export Service for D365 CDS v9.1x and Azure SQL

Hi Geeks,

Sharing something I have recently refreshed myself on with D365 for apps

Data export service (DES)

This is a standard OoB plugin form Microsoft to Sync data between Common data service and Azure SQL database. This process is commonly used for customers who want to use copy of CDS data to Azure SQL, Azure SQL being Datawarehouse and build reports on top of it, also migrate data further down the line after doing some transformations on the Azure SQL. This plugin allows only delta changes to Sync from Dynamics to Azure SQL, after initial sync.

Prerequisite:

Azure SQL database subscription on Azure and right permissions assigned.
Enable the Change tracking option on the Entity settings for entities that requires for data sync in CDS
Install the data export service form App source on the Dynamics instance
Azure Key Vault subscription

We have two stages on this Setup.

Create the resources on Azure manually or by using powershell script to automate
Create the Export profile and sync the data in Dynamics

Stage 1:

Manual setup

Create profile schema on Azure SQL database -> give permissions required and copy the connection string
Create a Key Vault on Azure
Create a Secret within the Key Vault
Add a Tag to the Key Valut with Tag name -> CE Organisation GUID (get from developer resources in Dynamics), Tag value -> Connectionstring of SQL
Copy the Secret Identifier generated from the Secret

Run Powershell script

Run the script as in this link on Azure https://docs.microsoft.com/en-us/power-platform/admin/replicate-data-microsoft-azure-sql-database#SetupAzureKV
Copy the Secret Identifier from the powershell output

Stage 2:

Create of export profile

Create export profile in CRM by going to Settings -> Data export. Give names accordingly, Key Vault URL will be the Secret Identifier copied from above stage, validate and click next

Mange entities that are required to Sync to Azure SQL, then click Next.

Finally Click Create & Activate

Do the initial sync and validate by querying in the Azure SQL database

FAQs

– error on creation of Export profile -> Give the CRM user the permissions on the Key vault as Get

– profile error on Creation of export profile -> Check the Secret value is having the right SQL connection string and resolve. Still issue arises, then drop the tables and profile schema in SQL and recreate

Format of parameters in powershell script:

Input:

$subscriptionId = ‘xxxx.xxxxx’

$keyvaultName = ‘Samplekey’

$secretName = ‘SecreteName’

$resourceGroupName = ‘sQLRG’

$location = ‘centralus’

$connectionString = ‘AzureSQLconnectionString’

$organizationIdList = ‘ContosoSalesOrg1_id, ContosoSalesOrg2_id‘

Output:

Secret Identifier = ‘https://<azureserver>.vault.azure.net/secrets/Secret-SS-CRM-082020/e0e112d2943344473a159bf2efddd46c1‘

Helpful links: