Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases
![](https://i0.wp.com/sharepains.com/wp-content/uploads/2022/09/image-17.png)
In this post I’m looking at the Azure SQL databases option that is part of the Azure Synapse link configuration.
A bit of background
Azure Synapse Links let you configure Dataverse exports to Azure. Quite often this results in a file structure in azure storage that holds csv files. These csv files are organised in such a way that the year (or month) of the records creation in Dataverse decide where your exported data will end up.
So typically you would get 2022.csv, 2021.csv, 2020.csv for records that have been created in the last three years. Any new updates are now appended to these files as updates are made to the records.
If you now want a process to use this data you will have to read the full csv file, only to find out that the last line in the csv file includes the update that you are interested in.
It feels like we need a proper database to do the job here. In this post the steps to setup your Synapse Links.
Create a SQL Database
The next step is to create a SQL database in Azure. This database will be used later on for Synapse Analytics to store its data.
![Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases 1 create SQL database](https://i0.wp.com/sharepains.com/wp-content/uploads/2022/09/image-10.png?resize=640%2C691&ssl=1)
To keep the cost down I’m selecting Development. However in production environments you might want to select Production. In my case £4.30 is is better than £211.
![Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases 2 Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases Microsoft Azure image 12](https://i0.wp.com/sharepains.com/wp-content/uploads/2022/09/image-12.png?resize=640%2C534&ssl=1)
Once the SQL database has been created we should be ready for the next steps.
Please make sure that you have enabled the Allow Azure services and resources to access this server setting.
Create a Synapse workspace
First I went to my Azure Portal and went to the Synapse workspace overview. Then I hit the create option and this is where it all begins.
A big form that needs to be completed.
![Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases 3 Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases Microsoft Azure image 5](https://i0.wp.com/sharepains.com/wp-content/uploads/2022/09/image-5.png?resize=640%2C841&ssl=1)
After completing this form, it looks like this. Note that as always a lot of the naming within Azure is very restricted. So for example the accountname has to be in lower case. And a lot of the below has to be unique as well.
![Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases 4 Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases Microsoft Azure image 6](https://i0.wp.com/sharepains.com/wp-content/uploads/2022/09/image-6.png?resize=640%2C826&ssl=1)
So first of all we had to create some Resource groups. I prefer to create the Managed resource group as this controls the naming a bit better then letting Azure generate this for me but you can also leave that empty.
Then we have an account name. This is the Data lake Generation 2 storage.
Now before hitting the Review + /create button, we will need to set the Security and Network settings for the workspace that we will create.
![Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases 5 Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases Microsoft Azure image 7](https://i0.wp.com/sharepains.com/wp-content/uploads/2022/09/image-7.png?resize=640%2C727&ssl=1)
.
Once the above settings have been completed the Synapse Workspace can be created.
Configure the Linked connection
Once the workspace is created you will find the option to open Azure Synapse Analytics Studio link. This will open your workspace.
![Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases 6 Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases Microsoft Azure image 20](https://i0.wp.com/sharepains.com/wp-content/uploads/2022/09/image-20.png?resize=640%2C340&ssl=1)
Click on the Link connection for the next step.
![Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases 7 Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases Microsoft Azure image 21](https://i0.wp.com/sharepains.com/wp-content/uploads/2022/09/image-21.png?resize=627%2C398&ssl=1)
Now click on the New to create a new linked service. This new linked service will need to be configured as shown below:
![Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases 8 Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases Microsoft Azure image 19](https://i0.wp.com/sharepains.com/wp-content/uploads/2022/09/image-19.png?resize=640%2C378&ssl=1)
Notice that only System Assigned Managed Identity works as the Authentication Type.
![Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases 9 Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases Microsoft Azure image 31](https://i0.wp.com/sharepains.com/wp-content/uploads/2022/09/image-31.png?resize=608%2C1024&ssl=1)
Configure access to the resource group
![Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases 10 Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases Microsoft Azure image 32](https://i0.wp.com/sharepains.com/wp-content/uploads/2022/09/image-32.png?resize=640%2C411&ssl=1)
Click on the add role assignment.
And then select the Owner role and add the Managed Identify that was created earlier.
![Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases 11 Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases Microsoft Azure image 34](https://i0.wp.com/sharepains.com/wp-content/uploads/2022/09/image-34.png?resize=640%2C419&ssl=1)
![Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases 12 Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases Microsoft Azure image 33](https://i0.wp.com/sharepains.com/wp-content/uploads/2022/09/image-33.png?resize=640%2C366&ssl=1)
Create a Azure Synapse Link in Dataverse
The next step is to create an Azure Synapse Link in Dataverse. Do make sure that you tick the box to Connect to your Azure Synapse Analytics workspace!
![Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases 13 Azure Synapse link](https://i0.wp.com/sharepains.com/wp-content/uploads/2022/09/image-17.png?resize=640%2C374&ssl=1)
This is where we select the tables that we want to sync. In my example I will only sync the accounts table. And I will partition my data on a monthly basis so that I get more smaller files.
![Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases 14 Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases Microsoft Azure image 9](https://i0.wp.com/sharepains.com/wp-content/uploads/2022/09/image-9.png?resize=640%2C578&ssl=1)
Now, you can click on the Go to Azure Synapse Analytics workspace. This will open the Azure Synapse Analytics workspace. If you notice that you haven’t got this option available then you didn’t select the earlier mentioned tick box.
![Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases 15 Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases Microsoft Azure image 18](https://i0.wp.com/sharepains.com/wp-content/uploads/2022/09/image-18.png?resize=640%2C361&ssl=1)
Now we want to see our data!
In the Synapse Analytics Workspace, I can now find my tables that I’m syncing. Job done!
![Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases 16 Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases Microsoft Azure image 35](https://i0.wp.com/sharepains.com/wp-content/uploads/2022/09/image-35.png?resize=640%2C379&ssl=1)
Continue Reading Pieter Veenstra’s Article on their blog
Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases
In this post I’m looking at the Azure SQL databases option that is part of the Azure Synapse link configuration. Azure Synapse Links let you configure Dataverse exports to Azure. Quite often this results in a file structure in azure storage that holds csv files.
Blog Syndicated with Pieter Veenstra’s Permission
More About This Author
Array
Modern Workplace26 March 2024Receive the available storage within your SharePoint Online tenant
Modern Workplace20 March 2024Options for Documenting Your Power Apps: Comments, Code, and Controls
Modern Workplace18 March 20242 ways to duplicate SharePoint Lists to support your Power Apps
Modern Workplace14 March 2024Update a Hyperlink Column in SharePoint with Power Automate