Replicate data to Azure SQL Database

Applies to Dynamics 365 (online), version 9.x

The Dynamics 365-Data Export Service is an add-on service made available on Microsoft AppSource that adds the ability to replicate Dynamics 365 (online) data to a Azure SQL Database store in a customer-owned Azure subscription. The supported target destinations are Azure SQL Database and SQL Server on Azure virtual machines. The Data Export Service intelligently synchronizes the entire Dynamics 365 data initially and thereafter synchronizes on a continuous basis as changes occur (delta changes) in the Dynamics 365 (online) system. This helps enable several analytics and reporting scenarios on top of Dynamics 365 data with Azure data and analytics services and opens up new possibilities for customers and partners to build custom solutions.

Note

You can use the Data Export Service with:

  • Dynamics 365 (online)
  • Dynamics 365 (online), version 9.0

For information about the programmatic interface for managing configuration and administration of the Data Export Service, see Data Export Service.

Prerequisites for using Data Export Service

To start using the Data Export Service, the following prerequisites are required.

Azure SQL Database service

  • A customer owned Azure SQL Database subscription. This subscription must allow the volume of data that is synchronized.

  • Firewall settings. We recommend that you turn off Allow access to Azure services and specify the appropriate client IP addresses listed in this topic. More information: Azure SQL database static IP addresses used by the Data Export Service

    Alternatively, you can turn on Allow access to Azure services to allow all Azure services access.

    For SQL Server on Azure VM, the “Connect to SQL Server over the Internet” option should be enabled. More information: Azure: Connect to a SQL Server Virtual Machine on Azure (Classic Deployment)

  • The database user must have permissions at the database and schema level according to the following tables. The database user is used in the data export connection string.

    Database permissions required.

    Permission type code Permission name
    CRTB CREATE TABLE
    CRTY CREATE TYPE
    CRVW CREATE VIEW
    CRPR CREATE PROCEDURE
    ALUS ALTER ANY USER
    VWDS VIEW DATABASE STATE

    Schema permissions required.

    Permission type code Permission name
    AL ALTER
    IN INSERT
    DL DELETE
    SL SELECT
    UP UPDATE
    EX EXECUTE
    RF REFERENCES

Azure Key Vault service

  • Customer owned Key Vault subscription, which is used to securely maintain the database connection string.

  • Grant PermissionsToSecrets permission to the application with the id "b861dbcc-a7ef-4219-a005-0e4de4ea7dcf." This can be completed by running the AzurePowerShell command below and is used to access the Key Vault that contains the connection string secret. More information: How to set up Azure Key Vault

  • The Key Vault should be tagged with the Dynamics 365 organization (OrgId) and tenant ids (TenantId). This can be completed by running the AzurePowerShell command below. More information: How to set up Azure Key Vault

Dynamics 365 (online)

  • A Dynamics 365 (online), version 9.0 or later version instance.

  • The Data Export Service solution must be installed. Get it now from Microsoft AppSource.

  • The entities that will be added to the Export Profile must be enabled with change tracking. To ensure a standard or custom entity can be synchronized go to Customization > Customize the System, and then click the entity. On the General tab make sure the Change Tracking option under the Data Services section is enabled.

  • You must have the System Administrator security role in the instance of Dynamics 365 (online).

Web browser

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.

Services, credentials, and privileges required

To use the Data Export Service feature, you must have the following services, credentials, and privileges.

  • A Dynamics 365 (online) subscription. Only users that are assigned the Dynamics 365 System Administrator security role can set up or make changes to an Export Profile.

  • Azure subscription that includes the following services.

    • Azure SQL Database or AzureSQL Server on Azure virtual machines.

    • Azure Key Vault.

Important

To use the Data Export Service the Dynamics 365 (online) and Azure Key Vault services must operate under the same tenant and within the same Azure Active Directory. More information: Azure integration with Office 365

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

What you should know before using the Data Export Service

  • Export Profiles must be deleted and then re-created whenever you perform any of the following actions on a Dynamics 365 (online) instance.

    • Restore an instance.
    • Copy (either full or minimal) an instance.
    • Reset an instance.
    • Move an instance to a different country or 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. More information: How to delete all Data Export Profile tables and stored procedures

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

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

Export Profile

To export data from Dynamics 365 (online), the Dynamics 365 (online) administrator creates an Export Profile. Multiple profiles can be created and activated to synchronize data to different destination databases simultaneously.

The Export Profile is the core concept of the Data Export Service. The Export Profile gathers set up and configuration information to synchronize data with the destination database. As part of the Export Profile, the administrator provides a list of entities to be exported to the destination database. Once activated, the Export Profile starts the automatic synchronization of data. Initially, all data that corresponds to each selected entity is exported. Thereafter, only the changes to data as they occur to the entity records or metadata in Dynamics 365 (online) are synchronized continuously using a push mechanism in near real time. Therefore, you don’t need to set up a schedule to retrieve data from Dynamics 365 (online).

Only entities that have change tracking enabled can be added to the Export Profile. Notice that, most of the standard Dynamics 365 entities which capture data are change tracking enabled. Custom entities must be explicitly enabled for change tracking before you can add them to an Export Profile. More information: Enable change tracking to control data synchronization

The Data Export Service does both metadata and data synchronization. Each entity translates into one table, and each field translates into a column in the destination database table. Table and column names use the schema name of the Dynamics 365 metadata.

Once activated, an Export Profile gathers statistics for data synchronization that helps in operational visibility and diagnostics of the data exported.

Data synchronization available with an Export Profile

Category Feature Supported data types
Initial Sync Metadata - Basic Data Types Whole Number, Floating Point Number, Decimal Number, Single Line of Text, Multi Line of Text, Date and Time data types.
Initial Sync Metadata - Advanced Data Types Currency, PartyList, Option Set, Status, Status Reason, Lookup (including Customer and Regarding type lookup). PartyList is only available for export version 8.1 and above.
Initial Sync Data - Basic Types All basic data types.
Initial Sync Data - Advanced Types All advanced data types.
Delta Sync Modify Schema - Basic Types Add or modify field change, all basic data types.
Delta Sync Modify Schema - Advanced Types Add or modify field change, all advanced data types.
Delta Sync Modify Data - Basic Types All basic data types.
Delta Sync Modify Data - Advanced Types All advanced data types, such as PartyList.

Create an Export Profile

Ensure that following requirements are met before creating an Export Profile.

  • The Data Export Service solution is installed in your Dynamics 365 (online) instance.

  • Maintain the SQL Database connection string in the Key Vault and copy the Key Vault URL to provide in the Export Profile. More information: Azure: Get started with Azure Key Vault

  • The entities to be added to the Export Profile are enabled for change tracking. More information: Enable change tracking to control data synchronization

  • Your SQL Database service has enough storage space to store the Dynamics 365 data.

  • You are a System Administrator in the Dynamics 365 (online) instance.

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

  2. Review the notice, and click Continue or Cancel if you don't want to export data.

  3. Click New to create a new Export Profile.

  4. In the Properties step, enter the following information, and then click Next to continue without connecting to the Key Vault. Clicking Validate uses the Key Vault URL you provided to connect to the Key Vault.

    • Name. Unique name of the profile. This field is mandatory.

    • Key Vault Connection URL. Key Vault URL pointing to the connection string stored with credentials used to connect to the destination database. This field is mandatory. More information: How to set up Azure Key Vault

      Important

      The Key Vault Connection URL is case-sensitive. Enter the Key Vault Connection URL exactly as it is displayed after you run the Windows PowerShell commands in this topic.

    • Schema. Name for an alternative database schema. Only alphanumeric characters are valid. This field is optional. By default, dbo is the schema that is used for the destination SQL Database.

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

    Properties tab in Create Export Profile dialog box

  5. In the Select Entities step, select the entities that you want to export to the destination SQL Database, and then click Next.

    Select Entities tab in Create Export Profile dialog box

  6. In the Select Relationships step, you can synchronize the M:N (many-to-many) relationships that exist with the entities you selected in the previous step. Click Next.

    Create Export Profile - Manage Relationships - Select Relationships

  7. In the Summary step, click Create and Activate to create the profile record and connect to the Key Vault, which begins the synchronization process. Otherwise, click Create to save the Export Profile and activate later.

    Summary tab in Create Export Profile dialog box

Modify an existing Export Profile

You can add or remove the entities and relationships in an existing Export Profile that you want to replicate.

  1. In 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 an Export Profile

  3. On the Actions toolbar, click MANAGE ENTITIES to add or remove entities for data export. To add or remove entity relationships, click MANAGE RELATIONSHIPS.

    Manage entities or entity relationships

  4. Select the entities or entity relationships that you want to add or remove.

    Select the entities or entity relationships to add or remove

  5. Click Update to submit your changes to the Export Profile.

Important

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. To drop an entity table, see How to delete Data Export Profile tables and stored procedures for a specific entity.

Table details for the destination Azure SQL Database

The Data Export Service creates tables for both data and metadata. A table is created for each entity and M:N relationship that is synchronized.

Once an Export Profile is activated, these tables are created in the destination database. These are system tables and will not have the SinkCreatedTime and SinkModifiedTime fields added.

Table name Created
<Prefix>_GlobalOptionsetMetadata Upon Export Profile activation.
<Prefix>_OptionsetMetadata Upon Export Profile activation.
<Prefix>_StateMetadata Upon Export Profile activation.
<Prefix>_StatusMetadata Upon Export Profile activation.
<Prefix>_TargetMetadata Upon Export Profile activation.
<Prefix>_AttributeMetadata Upon Export Profile activation.
<Prefix>_DeleteLog Upon Export Profile activation when the delete log option is enabled.

Resolving synchronization issues

Even after several retry attempts, record synchronization failures may occur from database storage constraints or table locking due to long running queries. To resolve these failures you can force a resynchronization of only failed records or a resynchronization of all records.

  1. View your export profiles to look for any that have record synchronization failures. You do this by viewing the data profiles in the Synchronization area or by opening a Export Profile , such as this profile that has a contact entity record synchronization failure.

    DataExport_failed_records_exist

  2. Examine the source of the synchronization failure and resolve it. More information: Error handling and monitoring

  3. After the problem has been resolved, resynchronize the failed records.

    Note

    Failed records synchronization is a public preview feature.

    • A preview feature is a feature that is not complete, but is made available before it’s officially in a release so customers can get early access and provide feedback. Preview features aren’t meant for production use and may have limited or restricted functionality.
      • We expect changes to this feature, so you shouldn’t use it in production. Use it only in test and development environments.
      • Microsoft doesn't provide support for this preview feature. Microsoft Dynamics 365 Technical Support won’t be able to help you with issues or questions. Preview features aren't meant for production use and are subject to a separate supplemental terms of use.
    1. Sign in to your Dynamics 365 (online) instance and go to Settings > Data Export.

    2. Open the Export Profile that includes record synch failures.

    3. On the Export Profile toolbar, click RESYNC FAILED RECORDS.

    4. Click Ok upon successful resynchronization of the failed records on the confirmation dialog.

    Notification of a successful resynchronization

    1. Verify that the Export Profile doesn’t contain failed record notifications by opening the data export profile and viewing the Failed Notifications counter on the PROPERTIES & OVERVIEW tab, which should be 0. Click REFRESH on the Export Profile toolbar to make sure the Failed Notifications value is current.

    Zero records failed  indication

  4. If the record synchronization failures persist after you've tried resynchronizing by following the previous steps, drop the tables, types, and stored procedures from the destination database, and then remove, and add back the entities to the Export Profile.

    1. Delete the associated database objects in the destination Azure SQL Database. For example, if you experience persistent leads entity synchronization issues, drop the leads tables, types, and stored procedures from the destination Azure SQL Database. More information: How to delete Data Export Profile tables and stored procedures for a specific entity

    2. Remove the entity, such as the leads entity, from the Export Profile. More information: Modify an existing Export Profile

    3. Add the entity, such as the leads entity, back to the Export Profile and then activate the profile.

Error handling and monitoring

To view the synchronization status of an Export Profile, go to Settings > Data Export and open the Export Profile. On the ENTITIES tab, the synchronization status is displayed including a Failed Records column for records that could not be synchronized. For any failed records, a list of those records including the status reason can be downloaded by clicking FAILED RECORDS on the command bar.

Export Profile command bar - Failed Records button

In the Export Profile you can click PROPERTIES & OVERVIEW to display the properties of the profile. Click RELATIONSHIPS to view the relationships synchronization status.

How to view detailed information about the records that failed to sync

Viewing the failed record logs can help you determine the cause of synchronization failures. To view failed records in the destination Azure destination database, use Azure Storage Explorer, a free standalone app that allows you to easily work with Azure Storage data. More information: Azure Storage Explorer.

  1. In Dynamics 365, go to Settings > Data Export.

  2. In the In the All Data Export Profile view, select the Export Profile that has failed notifications.

    Failed notifications

  3. On the Actions toolbar, click FAILED RECORDS.

    Failed records toolbar button

  4. In the Download Failed Records dialog box, click Copy Blob URL, and then click Ok.

    Download failed records dialog box

    Note

    The blob URL is valid for up to 24 hours. If the URL exceeds the 24 hour period, repeat the steps described earlier to generate a new blob URL.

  5. Start Azure Storage Explorer.

  6. In Azure Storage Explorer, click Connect to Azure Storage.

  7. Paste the URL from your clipboard in to the Connect to Azure Storage box, and then click Next.

    Storage url

  8. On the Connection Summary page, click Connect.

  9. Azure Storage Explorer connects to the destination database. If failed records exist for the Export Profile, Azure Storage Explorer displays failed record synchronization folders.

Failed record synchronization folder structure and log files

The Failed Records Azure Blob storage URL points to a location that has the following folder structure:

  • data. This folder contains failed data notifications and the associated JSON for record data.

  • metadata. This folder contains failed metadata notifications and the associated JSON for metadata.

  • failurelog. This folder contains logs that provides information about the synchronization failure and the reason the failure occurred.

  • forcerefreshfailurelog. This folder contains errors from the last run of the Data Export Service Failed Records command used to resynchronize failed records.

  • unprocessablemessages. This folder contains the data notifications that were not processed either due to deletion of data or metadata and the associated JSON.

    The failurelog and forcerefreshfailurelog folders are structured Year\Month\Day\Hour so that you can quickly locate the latest failures. All failure records older than 30 days are deleted.

    Here's an example log file that indicates a contact entity record synchronization failure.

Entity: contact, RecordId: 459d1d3e-7cc8-e611-80f7-5065f38bf1c1, NotificationTime: 12/28/2016 12:32:39 AM, ChangeType: Update, FailureReason: The database 'tempdb' has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.  
The statement has been terminated.  

Common reasons for record synchronization failures

Here are a few reasons why record synchronization failures may occur.

  • Insufficient storage for the destination database. Before you try to resynchronize the failed records, increase or free Azure SQL Database storage as appropriate. When this problem occurs, a message similar to this is recorded to the failure log.

    The database 'databasename' has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.

  • Synchronization timeouts with Azure SQL Database. This can occur during the initial synchronization of a data export profile when large amounts of data are processed at one time. When this issue occurs, resynchronize the failed records. Resolving synchronization issues

Best practices when using Azure SQL Database with Data Export

  • To avoid synchronization errors due to resource throttling, we recommend that you have an Azure SQL Database Premium P1 or better plan when you use the Data Export Service. More information: Azure SQL Database resource limits and SQL Database Pricing

  • Set the Azure SQL Database to use read committed snapshot isolation (RCSI) for workloads running concurrently on the destination database that execute long running read queries, such as reporting and ETL jobs. This reduces the occurrence of timeout errors that can occur with the Data Export Service due to read\write conflicts.

How to set up Azure Key Vault

Run the Windows PowerShell script described here as an Azure account administrator to give permission to the Data Export Service feature so it may access your Azure Key Vault. This script displays the key vault URL required for creating the Export Profile that is used to access the connection string.

Before running the script, replace the placeholders for the following variables.

  • $subscriptionId. The Key Vault resource group you want to use. If a resource group doesn’t already exist a new one with the name you specify will be created. In this example, ContosoResourceGroup1 is used.

  • $location. Specify the location where the resource group is, or should be, located, such as West US.

  • $connectionString. The connection string to the Azure SQL Database. You can use the ADO.NET connection string as it is displayed in your Azure dashboard.

  • $organizationIdList = Comma separated list of allowed Dynamics 365 organizations, listed by organization Id (organizationId), to enable for Data Export Service. To find an organization's Id, in Dynamics 365 go to Settings > Customizations > Developer Resources. The organization Id is under Instance Reference Information.

  • $tenantId. Specifies the Azure Active Directory tenant Id to which the Key Vault subscription.

Important

An Azure subscription can have multiple Azure Active Directory tenant Ids. Make sure that you select the correct Azure Active Directory tenant Id that is associated with the instance of Dynamics 365 that you will use for data export.

Azure SQL database static IP addresses used by the Data Export Service

In Azure SQL Database, click Set server firewall, turn Allow access to Azure services to OFF, click Add client IP, and then add the IP addresses appropriate for the region of your Azure SQL Database. More information: Azure: Configure an Azure SQL Database server-level firewall rule using the Azure Portal

Region IP address
West US 40.112.139.218
East US 23.96.92.86
West Europe 40.68.252.224
East Asia 52.175.24.148
Southeast Asia 52.163.231.218
Central India 52.172.191.195
South India 52.172.51.15
North Europe 52.169.117.212
Japan West 138.91.22.196
Japan East 13.73.7.177
Brazil South 191.235.81.249
Australia Southeast 40.115.78.163
Australia East 13.73.202.160
Canada Central 52.228.26.31
Canada East 40.86.251.81
United Kingdom South 51.140.71.166
United Kingdom West 51.141.44.218

Known issues

Deleted records may get reinserted into entity table after a synchronization failure

When you recover from synchronization failures, records that had been previously deleted may get reinserted back into the originating entity table. To work around this issue when synchronization failures occur, follow these steps.

  1. Create Export Profiles that are Write Delete Log enabled. Re-create existing Export Profiles that don't have Write Delete Log enabled.

  2. Create and execute a SQL query for the Azure SQL destination database that searches for records in the DeleteLog table. If one or more records are found it indicates the presence of deleted records.

  3. If one or more records exist in the DeleteLog table, create and run a SQL query that detects instances where the record Id for a record found in the DeleteLog table matches the record Id for a record in an EntityName table and the versionNumber in the deleteLog is greater than the versionNumber on the record in the EntityName table. When a record Id match occurs, delete the record from the EntityName table. For example, if a record Id in the AccountId column of the DeleteLog table matches a record Id in the AccountId column of the AccountBase entity table and the versionNumber in the DeleteLog is greater than the versionNumber in the Account table, delete the record from the AccountBase entity table.

    Important

    Depending on your business needs and requirements, we recommend that you execute the SQL queries for record deletion frequently, but during non-operational hours.

    Example query for entity record deletion.

DELETE FROM [dbo].[prefix_account] A
WHERE id IN (SELECT CONVERT(uniqueidentifier, recordid) FROM [dbo].[prefix_DeleteLog] DL WHERE DL.entityname ='account'
AND DL.VersionNumber &gt; A.VersionNumber)

Entities that don't support data export

The entities listed here, although they support change tracking, aren't supported for data export using the Data Export Service.

Entity Table Name Work Around
Activity ActivityPointerBase Select the specific activity entities for export, such as Phone Call, Appointment, Email, and Task.

Privacy notice

By using the Data Export Service, when you activate a data export profile from within Dynamics 365, the data of the entities added to the profile is sent to Azure. The initial synchronization includes all the data associated with the entities added to the export profile, but thereafter synchronization includes only new changes, which are continuously sent to the Data Export Service. Data sent to the Data Export Service is stored temporarily in Azure Service Bus and Azure Storage, processed in Azure Service Fabric, and finally synchronized (inserted, updated, or deleted) to the destination database specified in your Azure subscription. After the data has been synchronized, it is deleted from Azure Service Bus and Azure Storage. If there is a failure during data synchronization, minimal data corresponding to entity type, record ID, and sync timestamp is stored in Azure Storage to allow for downloading a list of records that were not updated.

An administrator can deactivate the data export profile at any time to stop data synchronization. In addition, an administrator can delete the export profile to remove any failed record logs and can uninstall the Data Export Service solution to stop using the Data Export Service.

Data synchronization happens continuously between Dynamics 365 and the Data Export Service in a secure manner. Data is encrypted as it is continuously exchanged between Dynamics 365 and the Data Export Service.

Azure components and services that are involved with the Data Export Service are detailed in the following sections.

Microsoft Azure Trust Center

Azure Service Fabric

This provides the API and compute Azure VMs to process record synchronize notifications received from Dynamics 365 and then process them to insert, update, or delete record data in the destination database. Micro-services that are deployed on virtual machines managed by the Azure Service Fabric runtime handle all the compute services related to data synchronization.

Azure Service Bus

This provides the message bus into which Dynamics 365 inserts the synchronization notification messages that are processed by compute nodes in Azure Service Fabric. Each message stores information, such as the org id and record, for which for which to sync data. Data in the Azure Service Bus is not encrypted at rest, but is only accessible by the Data Export Service.

Azure Blob Storage

Data is temporarily stored in Azure Blob Storage in case the record sync notification’s data is too large to store in a message or a transient failure is encountered to process the synchronization notification. These blobs are encrypted by leveraging the latest feature in the Azure Storage SDK, which provides symmetric and asymmetric encryption support and integration with Azure Key Vault.

Azure SQL

The Azure SQL Database stores data export profile configuration and data synchronization metrics.

See also

AppSource: Dynamics 365 - Data Export Service
What's new with Microsoft Dynamics 365 ‒ Data Export Service? Manage your data
Data Export Service Team Blog: Introduction to Dynamics 365 – Data Export Service