What is Data Export Service  ?

One of the limitations of D365 Online opposed to the On Prem version is that there is no access to the backend SQL Database .The Dynamics Data Export Service is a new Add-On that enables users to replicate the CRM Online Data Base to a Customer Owned Azure SQL Database. The data export service support both Microsoft Azure SQL Database and Microsoft SQL Server on Microsoft Azure virtual machines. The Data Export Service synchronizes the entire Dynamics 365 data initially and thereafter synchronizes delta changes on a continuous basis as they occur in the Microsoft Dynamics 365 (online) system.

So with this tool you can replicate the CRM Online data base to a Azure Hosted SQL data base without any trouble in a matter of minutes . Few of the business scenarios this add-on may come in handy is where

  • The user wants to create a data warehouse with the CRM data
  • The user wants to have access to the backend SQL databases views to create complex reports.

Few things to keep in mind with this add-on

  • This add-on will only work with a Microsoft Azure SQL Database or a Microsoft SQL Server on Microsoft Azure virtual machines.
  • This add-on is for data Export only and cannot be used for data importing purposes back to the source CRM.

What is needed to get this add-on configured.

  1. An Azure Subscription (Non Trial subscription. I will explain why later)
  2. A Dynamics 365 Subscription (30 day trial will be sufficient )

Steps required to get this add-on configured.

  1. Link the Office 365 AD with Azure AD
  2. Create a Azure SQL database instance
  3. Install Dynamics 365 Data Export Add-On
  4. Execute a PowerShell Script that will create the link and the service between the CRM DB and the Azure SQL DB for exporting data
  5. Configure Entities in CRM that needs to be exported to the Azure SQL database.

Azure / CRM Configuration

Step 1: Link the Office 365 AD with Azure AD

Basically in this step what we are doing is linking two separate Azure and Office 365 subscriptions so that we can access the Office 365 tenant from the Azure subscription.

The entire process is explained in this article by Microsoft therefore I won’t be discussing about that in details.

Note : This is where I would suggest using a Non Trial subscription. The reason been when adding a 0365 AD to the Azure AD there is an option in Azure AD to add an “Existing AD” , this option was not visible for me with the trail subscription.

Now in terms of getting a non-Trial subscription there are few workarounds .

  1. Sign up for Pay-As-You go subscription where you will only pay for the services you have used . There is a fee involved here but it would be very minimum
  2. Sign up for a MPN Subscription if your Microsoft ID is associated with your company’s Microsoft profile . Read here for further details.

Step 2: Create an Azure SQL database instance

  1. Login to your Azure Portal
  2. Click on “SQL Databases”
  3. Create a “SQL Databases” – Click on the “Add” button to create a new SQL database.Specify the details required for SQL database. Ensure you create “Blank Database”. Because I am using this for demo purposes I selected the Basic Pricing Tire.
  4. Create a SQL user that will be used by the Data Export Service to write the data to the SQL database. To do this install the latest version of SQL Server Management Studio that supports Azure SQL databases. Connect to the Azure SQL Database. You can get the Server Name and login user account by going to the Azure SQL Database Properties
  5. Create the SQL user that will be used by the Data Export Service to write the data to the SQL database . Ensure you create this user record against the Master Database 
    CREATE Login  [dataexport] WITH PASSWORD = '*****'

GO
  6. Assign the “db ower” Role for the above user account on CRM Database you just created.Ensure you create this user record against the CRM Database. 
    CREATE user  [dataexport] FROM Login [dataexport];

GO

EXEC sp_addrolemember 'db_owner','dataexport';

GO

                                                                                                                                     Note : Because this is a demo I assigned  the dbowner role against  user account which is not the best practice . For the Database permissions required for the data export user account please refer to this article by Microsoft

    Step 3: Install Dynamics 365 Data Export Add-on

  1. In CRM Navigate to Settings –Dynamics Marketplace
  2. Search for Dynamics 365 Export Service
  3. Select the Dynamics 365 tenant to add the Data Export Add-On.
  4. Agree to the Terms and Conditions
  5. This will begin the installation process of the Data Export Service. You can see the Solution under the “Manage your solutions” area on the portal
  6. Once the solution is installed a new menu item will appear on the Settings area in CRM                                  

Step 4: Execute a PowerShell Script

Basically, what we are doing in this step is that, we will be providing details of the Azure Instance and SQL Database, so that the data export service will know which instance to connect and where to store the data. Here will be providing information about the SQL connection string user account (user name and password) we created in Step 2.  Because  this information will contain credentials and connection data , it will be stored in the Azure Key Vault. Read more about Key Vault here.

Before we get started with this step. You must ensure all the Azure PowerShell Extensions are installed. To get the necessary extensions refer to this article by Microsoft

Now let’s get started. First we need to get the PowerShell script. The easiest place to get this is by going to “Settings -> Data Export-> Select New” and click on the blue exclamation mark next to the “Key Vault URL” field.

“Enable pop-ups for the domain https://discovery.crmreplication.azure.net/ in your web browser. This is required for auto-sign in when you navigate to Settings > Data Export”


Once we have the script copy the script to a Note Editor and update the highlighted parameters. Below are details of those parameters and where to find them. 

  • $subscriptionId – Specifies the Azure subscription to which the Key Vault belongs.Goto the Azure SQL Database -> Select Overview 
  • $keyvaultName – Specifies the name of the Key Vault. If the Key Vault does not exist, the script will create one
  • $secretName – Specifies the name of the secret that is put into the Key Vault. The secret holds the destination database connection string.
  • $resourceGroupName – Specifies the Resource Group for the Key Vault. Goto the Azure SQL Database -> Select Overview 
  • $location – Specifies the Azure region where the Resource Group and Key Vault is placed.Goto the Azure SQL Database -> Select Overview 
  • $connectionString – Specifies the destination database connection string that would be placed as a secret in the Key Vault.Goto the Azure SQL Database -> Select Overview (select the ADO.NET connection string)
  • $organizationIdList – Specifies a comma separated list of all the CRM Organization Id which will be allowed to export data to the destination database. (Setting -> Customisations -> Developer Resources )

  • $tenantId – Specifies the Azure Active Directory Tenant Id to which all the specified CRM Organizations belong to Goto the Azure Activre Directory -> Select App Registrations -> End Points (select the Federation Metadata Document)

Step 5: Configure Entities in CRM and Create an Export Profile

  1. The entities to be added to the Export Profile the needs to have the “change tracking” enabled.                                                          
  2. Navigate to Dynamics CRM -> Setting -> Data Export and click new 
    “Enable pop-ups for the domain https://discovery.crmreplication.azure.net/ in your web browser. This is required for auto-sign in when you navigate to Settings > Data Export”
  3. Populate fields as required . Refer to the below table for details on each[table id=2 /] 
    "Once you populate the information. Click on the validate button to see if you can successfully  connect to the Azure Instance. If you are experiencing connection issues , most of the time its related to the SQL connection string , therefore make sure you specify the correct SQL connection sting with the appropriate security privileges 
  4. Select the entities that you want to export to the destination SQL Database, and then click Next.
  5. You can synchronize the N:N (many-to-many) relationships that exist with the entities you selected in the previous step. Click Next.
  6. In the Summary step, click Create and Activate to create the profile record and connect to the Key Vault, which begins the synchronization process.

Modify an existing Export Profile

  1. In Microsoft Dynamics 365 (online), go to Settings> Data Export
  2. In the All Data Export Profile view, select the Export Profile that you want to change.Select the entities or entity relationships that you want to add or remove.                   Click Update to submit your changes to the Export Profile.

Thing to Remember ! 

  1. Export Profiles must be deleted and then re-created when you restore or move a Microsoft Dynamics 365 (online) instance to a different country/region. To do this, delete the Export Profile in the EXPORT PROFILES view, then delete the tables and stored procedures, and then create a new profile.
  2. The Data Export Service doesn’t work for Microsoft Dynamics 365 (online) sandbox instances that are configured with Enable administration mode turned on.
  3. To use the Data Export Service the Microsoft Dynamics 365 (online) and Azure Key Vault services must operate under the same tenant and within the same Microsoft Azure Active Directory. More information:
  4. The Azure SQL Database service can be in the same or a different tenant from the Microsoft Dynamics 365 (online) service.
  5. When you remove an entity or entity relationship from an Export Profile it doesn’t drop the corresponding table in the destination database. Before you can re-add an entity that has been removed, you must drop the corresponding table in the destination database.

 

This has certainly been one of the longest how to posts I have done . But its heaps of fun . I learnt a lot about Azure while doing this and hope this gives you the same exposure . Please get in contact if you have any further questions

 

Sahan Wijayasekera