Tutorial on Synchronization of Dynamics CRM Database with Azure SQL DB via Data Export Service

Synchronize Microsoft Dynamics 365 CRM Database with MS Azure SQL Database

These days, in this modern era of Cloud Technology, most of the clients desperately desire to sync up their Dynamics CRM data with their Azure Cloud storage. This blog is to demonstrate how we can replicate and synchronize Microsoft Dynamics 365 CRM database with MS Azure SQL Database using the Dynamics 365 Data Export Service.

Pre-Requisites:

• A valid Dynamics-365 CRM Instance
• An MS Azure Subscription for Azure SQL Database (For more information - https://azure.microsoft.com/en-in/services/sql-database/).
• Azure Key-Vault (For more information - https://azure.microsoft.com/en-in/services/key-vault/).

Steps with Screenshots:

• Login to your MS Dynamics CRM instance. Go to Settings → Dynamics Marketplace and search for Dynamics 365 Export Service.

  Note: Data Export Service is an add-on that allows replicating Microsoft Dynamics 365 CE Online Data to Azure SQL Database.

  

• Choose your Organization and install. Wait for few minutes till the solution gets completely installed.

  

• Once the solution is installed, go to Settings → Data Export. (Pop-up window will open a page for Data Export Authentication, so we need to enable pop up - https://discovery.crmreplication.azure.net/). Click on New to create a new Data Export File.

  

• We need the Azure Key Vault URL here which stores the database connection string securely.

  

• When we click on the information icon, a dialog box with a PowerShell script pops up. It requires the values of our Subscription Id, Key-vault Name, Secret Key, Resource Group Name, Location, Connection String, Organization ID List and Tenant ID.

  

• Now login to your Azure Account from portal.azure.com; create a new Azure SQL DB. Please refer to the below screenshot where we have created a Database HDMA_ExportDB_CE. Note down the connection string.

  
  

• Go to the Firewall Settings and turn off the Firewall.

  

• To get the Tenant ID of your Dynamics CRM instance, go to Settings → Customizations → Developer Resources.

  

• Not down all the following values and put them in our PowerShell script.

  

• In the PowerShell, run the following commands.

  • Install-Module AzureRM
  • Import-Module AzureRM

• Then run the Script. Once it succeeds, copy the generated URL.

  

• Go your Azure Key-Vault and check if the newly created Secret appears. Copy the URL in that Secret.

  

• Open the newly created Key-Vault Secret note down the Secret Identifier, as shown in the screenshot below.

  

• Move back to you Dynamics CRM environment, open the Export Profile Wizard. Paste the Key Vault URL that we copied above.

  

• Validate the Profile, select entity/entities, Create and Activate.

  
  

• Wait for some time till the Sync-up finishes.

  

Unit-Testing:

• Move to your Azure SQL Database.
• Run a simple SQL query, example – SELECT name FROM dbo.Account;
• See the Results section.

Conclusion:

In this tutorial, we have discussed how the Dynamics CRM Database can be synced up with an Azure SQL Database from our wide experience as a Microsoft Dynamics Company in India. Now whatever change (Creation/Deletion/Modification) is made in the records collection of the entity selected in the Data Export Profile in CRM, the same would be reflected in the Azure SQL Database. An Azure DB Administrator can monitor all these changes from the Azure Portal.