Azure Synapse Link for SQL on Azure VM

John K 186

As the Dataverse Data Export Service DES will retire in Nov 2022, I am checking how to push data from the Azure Synapse link in datalake to SQL on Azure VM.

I've checked the ADF approach mentioned on the following page.

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-pipelines?tabs=data-factory

It uses the ADF dataflow task, but I cannot write to SQL on Azure VM as dataflow doesn't support using the self-hosted Integration Runtime SHIR, which can copy data to SQL on Azure VM.

Could you advise how we can use ADF to write synapse link data from datalake to SQL on AVM using ADF? Do you have any other template or approach?

Bhargava-MSFT 30,496 Reputation points Microsoft Employee

2022-10-21T20:24:39.227+00:00

Hello @John K ,
Welcome to the MS Q&A platform.
I am currently checking with my internal team on this ask. I will get back to you with more details.
Bhargava-MSFT 30,496 Reputation points Microsoft Employee

2022-10-21T21:34:06.913+00:00

Hello @John K ,
Here is an update:

You can use ADF's copy activity for this requirement. Please make sure to grant ADF's managed identity access to Data Lake.

Source: Azure SQL Connection (use Synapse serverless SQL endpoint)
Target: SQL on Azure VM (via Self-Hosted Integration Runtime)
John K 186 Reputation points

2022-10-21T22:04:28.123+00:00

Hi
My source is the data lake not the source mentioned by you .

Source: Azure SQL Connection (use Synapse serverless SQL endpoint)

Can you show an example with a video or screenshot to explain your solution
Bhargava-MSFT 30,496 Reputation points Microsoft Employee

2022-10-24T17:24:15.27+00:00

Hello @John K ,
Adding a new link for 'Azure synapse link for Dataverse', it is either to a storage account or to an Azure synapse serverless pool.
When connecting the Azure synapse link for Dataverse to Azure synapse, then the lake database will be created on the serverless SQL pool.
using the copy activity, you can push data from the synapse serverless pool to your Azure SQL on VM.
Please let me know if you have any further questions.
Bhargava-MSFT 30,496 Reputation points Microsoft Employee

2022-10-26T23:19:53.807+00:00

Hello @John K ,
I am checking in to see if you have any further questions here.
John K 186 Reputation points

2022-11-03T09:38:54.527+00:00

Hi sorry for the delayed reply
Is it possible to do this without using Azure Synapse pool .
I am looking for ADF pipeline that can pull data directly from the data lake into SQL on Azire VM using self hosted IR
Bhargava-MSFT 30,496 Reputation points Microsoft Employee

2022-11-03T19:14:19.297+00:00

Hello @John K ,
Thanks for the reply.
Please correct me if my understanding is wrong. You are not using the lake database on the serverless SQL pool.
When you added a new link for the 'Azure synapse link for Dataverse,' you have chosen the Storage Gen2 account, and your data lake is created on the Gen2 storage account.
You are looking for a solution to copy the data lake(on the Gen2 storage account) to the SQL on Azure VM.
Please let me know if this is correct; I will again go back to my internal team and get back to you.
John K 186 Reputation points

2022-11-04T07:58:54.323+00:00

Hi @Bhargava-MSFT

Thanks for your reply.
Yes, for me, Azure synapse link for Dataverse is putting data into the storage account and I want ADF pipeline to pick the data from the storage account and put that into the SQL on Azure VM ( using self-hosted IR).

We are not using the lake database on the Serverless pool ( Synapse).
It will be great if you can guide as Nov 22 deadline for DES end-of-support and end-of-life is approaching fast.

Thanks
Bhargava-MSFT 30,496 Reputation points Microsoft Employee

2022-11-04T20:34:55.047+00:00

Hi @John K ,
Thank you for the confirmation
Let me check with my internal team and get back to you.

1 answer

Bhargava-MSFT 30,496 Reputation points Microsoft Employee

2022-11-10T02:46:46.337+00:00
Hi @John K ,
Sorry for the delayed response.
Here are the two options provided by my internal team on this.

Option 1
a.Disconnect the current export job to ADLS
b.Setup a Synapse workspace
c.Reconnect the integration with Synapse Link and map it to the synapse workspace ( see the below screenshot )
d.Use Pipeline Copy Activity to move data from Synapse Serverless SQL (Dynamics Entities) into SQL IaaS using SHIR

Option 2
a.Use Data Flows to read from Dataverse and Stage them in an Azure Storage account in CSV/Parquet format
b.Use Copy Activity to read from the Staged copy and send it to SQL IaaS.

I hope this helps.

------------------------------

Please don't forget to click on and upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

Want a reminder to come back and check responses? Here is how to subscribe to a notification

If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
Please sign in to rate this answer.

1 person found this answer helpful.
Bhargava-MSFT 30,496 Reputation points Microsoft Employee

2022-11-16T17:43:19.167+00:00

Hi @John K ,
I am checking to see if you have any further questions here.

-------
If this answers your question, please consider accepting the answer by hitting the Accept answer button, as it helps the community.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Azure Synapse Link for SQL on Azure VM

1 answer

Your answer