Synapse Link for Dataverse

Question

Hi everyone,

I tried the functionality of Azure synapse link (Synapse workspace) by replicating the dynamics (dataverse) tables on a Data lake through the tool made available (make power apps).

The database, however, appears to be composed of external tables that do not allow the typical functions of an AZURE SQL database, so every operation is blocked.
Example I would need to create some views but there is no such possibility. I also downloaded SSMS 18.10 in order to use Auzure studio but here I can't make a select saying that I have invalid credentials, doing some research I tried to create a master key and then try to insert the storage endpoint to create an external table, but here too it refuses my commands.

What I would like to do is replicate the Dynamics database in an AZURE DB as it happened with the DATA EXPORT SERVICE (DES). I also tried through AZURE DATA FACTORY with the pipeline made available but it is very cumbersome and each flow is to be done by single table.

I also tried through TDSEndpoint but access to the DB is read-only and obviously does not allow you to write views and or access them if not via power BI or SSMS with Azure authentication.

SOMEONE KNOWS how to replicate the DES functions and then replicate the dynamics DB in an AZURE SQL? Thank you very much I am desperate.

Answer 1

Hello @Giovanni Amodio ,

Thanks for the question and using MS Q&A platform.

Could you please confirm if you would want to replicate the Dataverse data to Azure SQL DB or Azure Synapse Analytics or Azure Data Lake Gen2?

Currently using Azure Synapse Link, you can only export data from:

• Dataverse to Azure Synapse Analytics
• Dataverse to Azure Data Lake Storage Gen2

However the database that was create by Synapse link is a read-only database. You’d need to create another database to persist the views.

Or another workaround is to use Synapse copy pipelines to replicate the tables (using Auto create tables feature in Copy activity) in your desired Azure SQL database once after copying the Dataverse tables to Azure Synapse using Synapse link.

Hope this will help. Please let us know if any further queries.

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

• Please don't forget to click on or 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