Background

Several times in the past, I was always troubled by the limitations of using Online because of the fact that you are unable to access the database. This causes certain restrictions specially on producing reports. Another reason for this is that we are unable to back up all the data that we want without needing help from Microsoft. That’s why I have always preferred using On-Premise in the past.

A new feature introduced by Dynamics 365 would alleviate our concerns above, read through and explore with me, let’s find out how.

What is Data Export Service?

This is a new and free add-on for your D365 subscription designed to aid in integration requirements. The tools usage is best summarized by Microsoft as seen on TechNet:

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

Prerequisites

  1. Create your Dynamics 365 Online environment. Register Trial Here!
  2. You should have either the supported target destinations which are Microsoft Azure SQL Database or Microsoft Azure SQL Server on Microsoft Azure virtual machines. For this series, we would use the Microsoft Azure SQL Database.
  3. Azure Key Vault Subscription to store database connection string
  4. Install PowerShell Azure Extensions. Follow this!

Link Office 365 Account to Azure Subscription

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. To achieve this, follow the steps outlined below:

  1. Add Office 365 tenant to your Azure subscription.
    1. Sign in on the Azure Portal (Classic).
    2. Navigate to Active Directory.

  1. Click on New > Directory > Custom Create.
  2. On the Add directory page, select Use Existing Directory.

  1. Now that you’re signed out, sign in using the Global Administrator of your Office 365 tenant.
  2. On the Use the ‘<organization name>’ directory with Microsoft Azure? window, click Continue, then click on Sign out now on the next screen.
  3. Now log in back using your Azure credentials.
  4. Under Active Directory, you should now be able to see your Office 365 tenant.

  1. Change the directory associated with Azure subscription.
    1. Under Settings, click Edit Directory upon selecting your azure subscription.
    2. On the Change the associated directory, make sure that the correct directory is selected and click Next.
    3. Verify the affected accounts before clicking on Complete.
  2. Add your Office 365 account as co-administrators on Azure Active Directory.
    1. Under Settings, click on Administrators, then click on Add button at the bottom.

  1. Enter the Office 365 account and select the Azure Subscription. Click Complete.

  1. Test the Office 365 account to sign in to the Azure portal.

Create Microsoft Azure SQL Database and add Users

  1. Create Azure SQL Database.
    1. Log on to your Microsoft Azure subscription.
    2. Navigate to SQL Databases to create a SQL database.
    3. Enter the parameters as seen on the screen below. (Except for the Name)

It is recommneded that you turn off the Allow access to Azure services firewall and specify specific client IP addresses instead.

  1. Click on Add Client IP. Refer to this list of IP to use depending on your region: Azure SQL database static IP addresses used by the Data Export Service.

** I have switched to the New Azure Portal from this step onwards.

  1. Click Save.
  1. Create User to be used to for Data Export Service.
    1. Open the Microsoft SQL Server Management Studio.
    2. Use the server name to connect to your Azure SQL database.
      1. The server name is found on the Azure Portal Overview section of your database.

    1. Input the following parameters as seen on the screen below. The login info should be the one you specified in Step 1 of Create Azure SQL Database section.

    1. Once you have connected successfully, notice that the tables are empty.

  1. Create user to be used for the Data Export Service on the master database.

  1. Create and assign appropriate rights to the user on your own database.

Install Data Export Service

  1. Within CRM, navigate to Dynamics Marketplace.

  1. Search for Data Export Service and click Get it now.

  1. Give permission to share account information, and click Continue.
  2. Select your Dynamics 365 Organization, and click Agree.

  1. This will initiate installation of the Data Export Service.

  1. Navigate back to CRM. Under Settings, click on Data Export.

  1. Click on Ok. (Allow pop-ups for this window to appear.)

  1. Click on +New to create an export profile.

Hold off this screen; we’ll get back here. We just need to make use of the Key Vault script first and create Key Vault on our subscription.

Create Azure Key Vault

  1. Copy the PowerShell Script and populate with the ff. parameters:

    1. subscriptionId

Navigate to Azure Portal > SQL Database > Overview.

    1. keyvaultName

Provide your own keyvaultName.

    1. secretName

Provide your own secretName.

    1. resourceGroupName

Navigate to Azure Portal > SQL Database > Overview.

    1. location

Navigate to Azure Portal > SQL Database > Overview.

    1. connectionString (ADO.NET)

Navigate to Azure Portal > SQL Database > Overview. Populate with your Username and Password created in section Create User to be used to for Data Export Service.

    1. organizationIdList

Go to CRM > Settings > Customizations >Developer Resources > Instance Reference Information > ID.

    1. tenantId

Navigate to Azure Portal > Azure Active Directory > App Registrations > Endpoints. Copy the first one which is the Federation Metadata Document. Copy only the GUID.

  1. Run the PowerShell Script.

Note that it would ask you to sign in to your Office 365 account.

  1. Output of PowerShell Script should show you the Connection Key Vault URL.

  1. Navigate to Azure Portal > Key Vaults. Select the Key vault you have created. Click Secrets.

  1. Click on the Secret, then click on the Copy button to copy the Secret Identifier. Note that this is similar to the output of the PowerShell script on Step 11. I just showed you what the script has created on the Azure Portal.

Enable Change Tracking (Custom Entities)

  1. Within CRM, navigate to Settings > Solutions > select your Solution.
  2. Under Components > Entities > select a specific entity.
  3. Under Data Services, check the Change Tracking option.
  4. Click Publish.

Create Export Profile

  1. Return to CRM. Under Settings, click on Data Export.
  2. Click +New.
  3. Populate the following fields with the parameters as shown. Key Vault URL is the value copied from the Secret Identifier.

  1. Click on Validate to verify no connection problems arise.
    1. If there are errors, either the URL is incorrect or the Connection String stored within the Key Vault has incorrect credentials.
  2. Click Next.
  3. Select the specific Entities and Relationships you want to sync.

  1. Click Next.
  2. Click on Create & Activate.

  1. You would be seeing this screen once export profiles have been created.

  1. Click on Refresh to see updates. If errors are seen, you could also try refreshing the page. This may be caused by interdependent entities. This would eventually succeed once the tables have been created in the right order.

  1. Connect to your Database to verify that schema and data are in place.

 

Notice that the accounts are now loaded on the database.

Replicate Dynamics 365 Online Data to SQL Database Using Data Export Service

Conclusion

Now that we’ve got the database from CRM Online, various possibilities can arise on whatever we plan to develop—be it integration or reporting. Such things would be a lot easier given the flexibility of this feature. This is probably my favorite feature since it’s fairly easy to configure while it offers a wide range of opportunities. Delta sync will be tackled on future blog posts.

See you around!

If you find this blog helpful, kindly share or comment below.