Rajeev Pentyala – Microsoft Power Platform

For unversed, The Data Export Service (DES) is an add-on service available on AppSource that adds the ability to replicate data to target destinations such as Azure SQL Database and SQL Server on Azure virtual machines.

In this article, lets see how to replicate CDS data to Azure SQL Server Database.

Prerequisites:

• Subscribe to Dynamics 30 days trial and get Office 365 account.
• Connect to Power Apps maker portal using Office 365 account and create a simple Model Driven App with ‘Account’ entity.
• Install ‘Data Export Service’ add-on from AppSource.
• Subscribe to Azure 30 days trial using the same Office 365 account.

• Azure SQL Server and Database.
• Generate Azure Key Vault URL using Power Shell.

Installing ‘Data Export Service’ from AppSource:

• From your Model Driven App, go to Settings -> Microsoft AppSource
• Search for ‘Data Export Service’ and click on ‘Get it now’.

• In the next steps, select your Organization and click on ‘Agree’.

• Wait for the solution to get installed.

Configuring Azure SQL Server and Database:

• Connect to your Azure Portal and create a new SQL Database Server  resource.

• Post creation, copy the ‘Server name’ and ‘Server admin’ and ‘Subscription ID’, which you would need in next steps.

• Now we need to create a new Database where you would replicate your CDS data to.
• You can either create new Database from Azure Portal or from SQL Server Management Studio (SSMS). I am using SSMS to create the new Database.
• Connect to Azure SQL server from SSMS using ‘Server name’ and ‘Server admin’ details captured in previous steps.

• Create a new Database and copy the name (i.e., DES in my case).

• Now go to Azure Portal and open the DES SQL database. Click on ‘Connection Strings’.

• Copy the ‘ADO.NET’ connection string which you would need in next steps.

Generate Azure Key Vault URL using Power Shell and create Export Profile:

• To connect to Azure SQL Server Data Export Service(DES) requires the SQL Server connection string.
• DES will not accept the plain connection string.
• DES accepts only Azure key vault URL with connection string stored as Secret.

As a standard practice, we configure Azure Key Vault URL using PowerShell script using following steps:

• From the Model Driven App, go to Settings -> Data Export
  • Note: Make sure you disable pop-up blockers.
• Click on ‘+New’ to create ‘Export Profile’.

• From the pop-up, fill the details other than ‘Key Vault URL’ and click on information icon.

• Copy the PowerShell helper script from the pop-up.

• Now open the ‘Windows PowerShell’ and open a new window.
• Paste the PowerShell helper script copied from previous step.
• In the ‘PLACEHOLDER’ section provide the details specific to your Azure subscription.
  • $subscriptionId – Use the copied value from previous steps.
  • $keyvaultName – Your desired name.
  • $secretName – Your desired name.
  • $resourceGroupName – Either use existing or new. If a resource group doesn’t already exist a new one will be created.
  • $location – provide the location (i.e., East US/West US/…)
  • $connectionString – Use the copied value from previous steps.
  • $organizationIdList – Dynamics organization ID. From the Model Driven App, go to Settings > Customizations > Developer Resources. The organization Id is under ‘environment Reference Information’.
  • $tenantId – You can get it from ‘Azure Portal -> Azure Active Directory -> App registrations -> Endpoints‘ by copying the highlighted value as below.
  • 
• Once all the required details provided, your PowerShell looks as below.

• Run the script which creates a new Azure Key Vault. You should get output as below.

• Now that the ‘Key Vault URL’ is formed, copy the value from Azure Portal -> Key vaults -> Secrets’

• Go back to the DES ‘Data Export Profile’ pop up and paste the ‘Key Vault URL’.
• Click on ‘Validate’ to make sure the connection works.

• Click ‘Next’ and pick your CDS entities which would want to replicate to Azure SQL.

• As a last step click on ‘Create & Activate’ to complete the set up.

• Give it sometime and you should see the Metadata tables and ‘Account’ entity tables created in your target (i.e., Azure SQL Server).

Notes:

• Data Export Service can be used with model-driven apps in Dynamics 365, such as Dynamics 365 Sales and Customer Service.
• Only entities that have change tracking enabled can be added to the Export Profile. Enable change tracking to control data synchronization
• To use the Data Export Service the model-driven apps in Dynamics 365 and Azure Key Vault services must operate under the same tenant and within the same Azure Active Directory.
• The Data Export Service does not drop (delete) the associated tables, columns, or stored procedure objects in the destination Azure SQL database when the following actions occur and these items must be dropped manually.
  • An entity is deleted.
  • A field is deleted.
  • An entity is removed from an Export Profile.

Refer article to know more about Data Export Service. To troubleshoot connectivity issues refer article

🙂