Synapse Link for Dataverse

Question

question

GiovanniAmodio-5994 asked Jan 5, '22 matthewlancashire commented May 12, '22

Synapse Link for Dataverse

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.

azure-sql-database azure-synapse-analytics azure-data-lake-storage

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

matthewlancashire · May 12 at 11:03 AM

Hi,
Important question for me is,;
What typical functions did you do in the DES Azure SQL that you are wanting to do?
e.g. Read the data, Update the data, Create more tables to augment the data

Thanks

Matt

0 ·

Answer 1 · 2022-01-07T01:43:50.757Z

KranthiPakala-MSFT answered Jan 7, '22 KranthiPakala-MSFT commented Feb 2, '22

Hello @GiovanniAmodio-5994,

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

· 3

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GiovanniAmodio-5994 · Jan 10 at 05:09 PM

Hello @KranthiPakala-MSFT ,

thanks for the answer I am currently using Synapse Link so from Dataverse to Azure Synapse Analytics, my question is how do I create another replica database since the one creating synapse link is read only?

obviously I would need an incremental copy!

thanks for your patience.

I am attaching my situation to you 163636-2022-01-10-17-37-29-synapseworkspaceforcrm2-azure.png

0 ·

2022-01-10-17-40-11-power-apps.png (24.3 KiB)

2022-01-10-17-37-29-synapseworkspaceforcrm2-azure.png (26.5 KiB)

KranthiPakala-MSFT GiovanniAmodio-5994 · Feb 01 at 12:02 AM

Hello @GiovanniAmodio-5994,

Thanks for response and sorry for the delay. In order to replicate the database, you have to go the ADF or Azure Synapse pipelines. But I see that you have called out that you have challenges taking ADF approach, could you please elaborate a bit about the challenges you have gone through?

And in order to load incremental data you will have to come up with a similar approach as described in this documentation - Incrementally load data from a source data store to a destination data store

0 ·

KranthiPakala-MSFT KranthiPakala-MSFT · Feb 02 at 07:47 PM

Hi there,

Following up to see if you have got a chance to see my previous comment, if so, does the info help or do let us know if any further queries.

Thank you

0 ·

question