In this article

Replicate data to Azure SQL Database

01/30/2019

28 minutes to read

Contributors

In this article

Applies to Dynamics 365 for Customer Engagement apps version 9.x

The Dynamics 365 for Customer Engagement apps -Data Export Service is an add-on service made available on Microsoft AppSource that adds the ability to replicate Dynamics 365 for Customer Engagement apps 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 Customer Engagement apps data initially and thereafter synchronizes on a continuous basis as changes occur (delta changes) in the Dynamics 365 for Customer Engagement apps system. This helps enable several analytics and reporting scenarios on top of Customer Engagement apps 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 for Customer Engagement apps

Dynamics 365 for Customer Engagement apps 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.

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 for Customer Engagement apps 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 for Customer Engagement apps

A Dynamics 365 for Customer Engagement apps version 9.0 or later version instance.

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 for Customer Engagement apps.

Web browser

Services, credentials, and privileges required

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

A Dynamics 365 for Customer Engagement apps subscription. Only users that are assigned the Dynamics 365 for Customer Engagement apps 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 for Customer Engagement apps 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 for Customer Engagement apps 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 for Customer Engagement instance.

The Data Export Service doesn’t work for Dynamics 365 for Customer Engagement apps 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 for Customer Engagement apps, the Dynamics 365 for Customer Engagement 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 for Customer Engagement apps 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 for Customer Engagement apps.

Only entities that have change tracking enabled can be added to the Export Profile. Notice that, most of the standard Dynamics 365 for Customer Engagement apps 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 for Customer Engagement apps 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.

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

Click New to create a new Export Profile.

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.

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

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.

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.

Modify an existing Export Profile

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

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.

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.

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.

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

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.

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.

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.

In Customer Engagement apps, go to Settings > Data Export.

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

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

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.

To help improve query performance we recommend the Data Export Service database max degree of parallelism (MAXDOP) be set to 1. More information: MSDN: Server Memory Options

Frequently assess the amount of fragmentation, and when necessary, rebuild the indexes in the Data Export Service database. More information: Reorganize and Rebuild Indexes

Periodically update database statistics on tables and indexed views in the Data Export Service database. More information: Update Statistics

About data synchronization latency

The Data Export Service is architected to synchronize data changes to the destination database using a push mechanism by listening to changes as they happen in Dynamics 365 for Customer Engagement apps. The service strives to push data within a few minutes, but there are number of factors that can influence end-to-end synchronization latency.

Factors that influence the duration of synchronization include the following:

The current work load on Dynamics 365 for Customer Engagement apps.

The data change rate in Dynamics 365 for Customer Engagement apps.

The number of entities added to each export profile and their attributes.

SQL Server performance. For example:

SQL connection setup time.

SQL statement execution time.

Based on our monitoring of the service it's been observed that most on-going delta synchronization finishes in 15 minutes when the service operates under the following conditions:

The synchronization that occurs is a delta synchronization and not the initial synchronization. Delta synchronization is only for data change operations, which include record create, update, and delete transactions. Note that delta synchronization begins once the initial synchronization has finished.

The maximum data change rate in Dynamics 365 for Customer Engagement apps for all the entities in the export profile is less than 3000 records per hour. Any sudden increase in the data change rate due to bulk change of records exceeding the maximum change rate will cause additional latency.

Each entity added to an export profile has less than 150 attributes.

Database connection or SQL statement execution finishes in less than 10 seconds. If this limit is exceeded it will result in additional latency.

When the above conditions are met, 15 minutes is a typical synchronization latency. Microsoft provides no service level agreement (SLA) for the Data Export Service and makes no guarantees or commitments regarding synchronization latency times.

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.

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 for Customer Engagement apps that you will use for data export.

How to delete all Data Export Profile tables and stored procedures

Important

Before you run this SQL statement make sure that you have correctly defined the @prefix and @schema values in the statement.
The Export Profile will need to be re-created after you run this SQL statement.

How to delete Data Export Profile tables and stored procedures for a specific entity

Important

Before you run this SQL statement make sure that you have correctly defined the @prefix, @schema, and @entityName values in the statement. In this example, the leads entity table, types, and stored procedures are dropped.

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.

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.

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.

Unable to create a row greater than the allowable maximum row size (8K)

If your error logs show "Cannot create a row of size which is greater than the allowable maximum row size of 8060", you are running into an issue where you are exceeding the maximum allowable row size limit. The Data Export Service does not support row size greater than maximum allowable row size of 8k. To mitigate this, you need to ensure that you honor the row size limits.

Length of string in source is longer than destination schema for ColumnName

If your error logs show "String length in source longer than destination schema for [ColumnName, MaxDataLength]" you are running into an issue where the string length of your source data is longer than destination. If the string length of your source data is longer than destination, writes to destination will fail.To mitigate this issue, you would either need to reduce size of data or increase the length of column, greater than MaxLength manually in the DB.

Privacy notice

By using the Data Export Service, when you activate a data export profile from within Dynamics 365 for Customer Engagement, 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 Customer Engagement and the Data Export Service in a secure manner. Data is encrypted as it is continuously exchanged between Customer Engagement and the Data Export Service.

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

This provides the API and compute Azure VMs to process record synchronize notifications received from Customer Engagement 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.

This provides the message bus into which Customer Engagement 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 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.