Data Export Service and Versioning of Azure Key Vault URLs
The Microsoft Data Export Service (DES) is a well-documented integration used to replicate CRM data to an external Azure SQL database. Even with its easy-to-use design, there are some limitations about what can be modified after the DES profile is created. One such example is the key vault secret URL.
When the initial profile is created and the key vault string has been input, it can no longer be changed without recreating the profile (which, by relation, requires the connected SQL tables to be dropped in order to start the sync again). If the key vault URL is copied directly, this will leave a static un-editable connection string. However, one small change to the DES profile set up will allow for the secret to be updated without needing to recreate the profile. Here’s how it works:
A standard key vault secret URL is formatted as such:
Once a key vault secret is created, the secret is locked and is unable to be edited – instead, it must be versioned. Azure Key Vault allows for secret versioning with the ability to have multiple versions enabled for the same vault at one time. The primary issue with simply versioning the secret is the new version changes the GUID, which means the URL that was entered in the profile is still pointing at the old secret. In the example below, the GUID in the secret (A) directly corresponds to the GUID on the end of the secret (B). This prevents standard operational updates to the connection string, such as password renewals.
To account for this, simply remove the GUID at the end of the secret URL. This will continue to point at the newest version of the secret, no matter how many times the secret is changed. This little trick can save several hours of work in case a change to the secret is ever needed in the future. Be sure to subscribe to our blog for more tips and tricks.
Happy Dynamics 365'ing!
Data Export Service Installation and Configuration
Are you as excited as we are about the Data Export Service (DES) capabilities available when working with Azure with Dynamics 365 for Customer Engagement (D365 CE) Online? In this blog we’ll look at installing and configuring the DES to export data from D365 CE Online to a SQL database. Let’s get started!
Install and Configure Data Export Service
D365 CE administrator user credentials
Azure Portal organization account user credentials
Target SQL Database SQL User for use by the Data Export Service
Install Data Export Service
Verify that DES Export is installed on the D365 instance containing the data that you need to export.
In D365 CE, check the list of imported solutions at Settings > Solution
1. Log into the Azure portal with organization user credentials and navigate to your key vault.
2. Select Generate/Import and enter the information required:
In our example connecting to a SQL database, the secret Value looked something like this:
Depending on what you are connecting to, your solution may require a different Value format than the example shown here. With the multitude of options to choose from, the connection string specifics for each connection type is out of scope for this blogpost. See https://docs.microsoft.com/en-us/azure/key-vault/quick-create-template or search for ‘How To Configure Data Export Service with WhatYouAreConnectingTo’ to help you along your way for a use case other than SQL.
3. Select Create.
4. Open the newly generated secret and select Current Version to view the details.
5. Add a Tag to the secret
Enter the D365 CE organizationid as the “Tag Name” (Locate the organizationalid in D365 CE by navigating to Settings > Customizations > Developer Resources.)
In this blog, we will uncover how primary fields and look up fields are created and synchronized in the Microsoft Azure SQL Database, in a customer-owned Microsoft Azure subscription using Data Export Service.
Unlike Dynamics 365 On-Premise installations where developers and administrators have some level of control on backend databases and can facilitate an organization's reporting with ETL teams to query data directly from a SQL server for reporting, a Dynamics 365 Online implementation does not allow users to query a CRM database directly in online environments. To overcome this challenge, Microsoft has come up with a Data Export add-on service available on Microsoft AppSource that adds the ability replicate Dynamics 365 data to an Azure SQL Database. This helps facilitate reporting and allows ETL teams to consume data for reporting and analytic purposes.
The Data Export Service add-on transforms data columns while replicating data from Dynamics 365 to an Azure SQL database. Here are a few points that need to be considered:
Get Lookup Values from the Data Export Service Database
The Data Export Service synchronizes lookup fields slightly differently in the Azure SQL database tables than in the same columns created by the Dynamics 365 on-premises database.
Data Export Service creates two columns for each lookup in Dynamics 365 as opposed to three columns in the on-premises database.
<Lookupfield>ID - This column is similar to the id column created by Dynamics 365 in on-premises databases. It holds the GUID of the primary id of the referenced table
<Lookupfield>_entitytype - This column holds the entity schema name of the referenced entity. This is different from Dynamics 365 on-premises database, where the database is used to store the referenced entity's ObjectTypecode in <Lookupfield>Type column.
<Lookupfield>name: Data Export Service never copies the 'name' column in the Azure SQL Database; so it is always empty for every lookup field.
Example: Refer to the screenshot below to compare the differences between the same CustomerId look up field created by Data Export Service in Azure SQL Database vs. the on-premises Database created by Dynamics 365.
Dynamics 365 On-Premises Database:
Azure SQL Database Look up field created by Data Export Service:
TargetMetadata Reference Tables for Lookup
The Data Export Service creates a TargetMetadata master table to hold all the referenced entities and attributes for lookup fields. Each row represents the lookup between the Primary (Referencing) entity and the referencing attribute to Referenced entity and attribute.
Example: The screenshot below shows all the referenced look ups for the contact entity. Here the CreatedBy on contact table is referencing the system user table's systemuserid attribute.
The Data Export Service is only available for Microsoft Dynamics 365 Online, version 8.0 and onwards.
Learn more about Microsoft Dynamics 365 in the PowerObjects blog!