Dynamics 365 Data Export Service

May 6, 2017

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.

Steps required to get this add-on configured.

Execute a PowerShell Script that will create the link and the service between the CRM DB and the Azure SQL DB for exporting data

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 .

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

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

Login to your Azure Portal

Click on “SQL Databases”

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.

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

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

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.

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

In CRM Navigate to Settings –Dynamics Marketplace

Search for Dynamics 365 Export Service

Select the Dynamics 365 tenant to add the Data Export Add-On.

Agree to the Terms and Conditions

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

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 thisarticle 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.

$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 )

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

The entities to be added to the Export Profile the needs to have the “change tracking” enabled.

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”

Populate fields as required . Refer to the below table for details on each

Field

Description

Name

Unique name of the profile. This field is mandatory.

Prefix.

Prefix to be used for the table names created in the destination database. This helps you easily identify the tables created for the Export Profile in the destination database. When specified, make sure that the prefix is less than 15 characters. This field is optional and only alphanumeric characters are allowed.

Retry count

The number of times a record is retried in case of a failure to insert or update in the destination table. This field is mandatory. Acceptable values are 0-20 and the default is 12.

Retry interval

The number of seconds to wait before a retry in case of a failure. This field is mandatory. Acceptable values are 0-3600 and the default is 5.

Write Delete Log.

Optional setting for logging deleted records.

"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

Select the entities that you want to export to the destination SQL Database, and then click Next.

You can synchronize the N:N (many-to-many) relationships that exist with the entities you selected in the previous step. Click Next.

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

In Microsoft Dynamics 365 (online), go to Settings> Data Export

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 !

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.

The Data Export Service doesn’t work for Microsoft Dynamics 365 (online) sandbox instances that are configured with Enable administration mode turned on.

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:

The Azure SQL Database service can be in the same or a different tenant from the Microsoft Dynamics 365 (online) service.

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

Post navigation

11 thoughts on “Dynamics 365 Data Export Service”

Need urgent help!!
We are using this data export service. However attachments which are stored in CRM are shown as null in the database. all the details like document type, document size etc are exported in the database, but document body is showing as null.

Is data export service support AAD (active directory password) authentication ? Is there any limitation to use only SQL user authentication?
I tried a lot . Data export failing to connect to sql server with AAD auth when I did validation but I able to connect same sql server with sql user authentication .

Follow me on Twitter

About This Blog

I am a Dynamics CRM Consultant based in Melbourne. I started my CRM journey with Dynamics CRM 3.0 and have worked in various CRM implementation across many industries. I have dedicated this blog to write about all D365 related tips and tricks I come across in my consulting career. If you there is any D365 related query , I will be more than happy to help and I can be contact via the above details.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.