Setting up Azure Synapse Link for Dynamics 365/Dataverse

Published 03/30/2022

With the Data Export Service deprecated and reaching end-of-life November 2022 we must update our processes to use Azure Synapse Link. We will cover initial setup of the data lake and Azure Synapse workspace plus a few ways to interact with your data in this post (Synapse Studio, SSMS, Azure Storage Explorer).

I will be following up with another post soon where we will create a .NET console application to interact with our data lake using Entity Framework Core so stay tuned for that!

For more information about Azure Synapse please see Microsoft official documentation:

• https://docs.microsoft.com/en-us/powerapps/maker/data-platform/azure-synapse-link-synapse
• https://docs.microsoft.com/en-us/azure/synapse-analytics/get-started-create-workspace

[1] Set up the data lake

1. Create a storage account in Azure and make sure to enable “hierarchical namespace” under “Advanced” settings.

2. Make sure the region you set up your storage account in is the same region your CRM instance is deployed. If you pick the wrong region the Maker Admin will let you know when trying to connect.
3. Confirm that all users responsible for managing the Dataverse connection have Owner and Storage Blob Data Contributor roles on the storage account.

[2] Set up Azure Synapse Workspace

1. Search for Azure Synapse Analytics and create a new workspace. Make sure this is in the same region as your storage account and Dynamics instance.

2. Provide security, networking, tagging information and proceed creating your workspace. This will take a couple minutes but once complete we will have our resources set up and we are ready to move over to the Maker Admin.

[3] Connect Dataverse to Azure Synapse

1. Open your environment in the Maker Admin and navigate to Data > Azure Synapse Link.

2. Before creating a new link confirm the entities that you would like to sync have “change tracking” enabled.
   If you have recently enabled change tracking and your entities are not showing up give it some time. I have had to wait 20 minutes before new entities would appear.

3. Select “New link” and complete the form identifying the Azure resources you will be using.

4. Choose the tables that you would like to synchronize and save. In this example we will be syncing my team table from my NHL stat database.

5. If you see a permissions error at this stage confirm that you have an Owner role assignment on the storage account (not to be confused with Storage Blob Data Owner).
6. If successful within a few minutes you will be able to manage the Synapse Link configuration from the Maker Portal. Selecting “Manage tables” from the nav will allow you to add/remove Dataverse tables from synchronization. Let's move on to consuming this data.

[4] Working with your data lake

Azure Synapse Studio

I have barely scratched the surface on Azure Synapse but have been blown away working with Azure Synapse Studio. I was definitely not excited when I heard MS was taking away DES, however, I feel better after using this suite for a bit.

To reach the studio you can select “Go to Azure Synapse Analytics workspace” from the Maker Admin or from the Synapse Workspace in the Azure portal.

Work directly with the data lake as if it were a SQL database and get results directly in Synapse Studio. Running queries in the studio is slow but when hitting the serverless endpoint from a console application it was not nearly as bad.

Azure Storage Explorer

Download: https://azure.microsoft.com/en-us/features/storage-explorer

A must have tool but honestly not the way I prefer to work with this data. If you want to download/review your data in its natural state this tool makes that very quick and easy.

SSMS

Navigate to Azure and open your Synapse Workspace. Find your 'serverless SQL endpoint' in the overview and use that to connect via SSMS.

Work with your data as you normally would.

Final thoughts

I will admit that I was not happy to hear about deprecation of the Data Export Service. Fair to say that I am suffering from MS deprecation fatigue but after spending some time I am beginning to get excited about this new suite of tools.

In my next post, we will take something like we built here and hit the serverless endpoint from a .NET console application. Hope this was useful to you in your setup.

Do not hesitate to leave a comment or question below!

Related Posts