Dual Sync between SQL Server & Dataverse

(1) Share

Report

Posted on by Dhruvin Shah

I have source database as Azure SQL.

I created Dataverse table by connecting Azure SQL as connector which refreshes Dataverse Data every day!

I build one Power Apps connecting data from Dataverse. Now, I need sync mechanism that whenever, a new entry is added to Dataverse that will be sync with Azure SQL(We can sync real time or may be every day).

How to achieve this Dual Sync - SQL to Dataverse and Dataverse to SQL?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

ChrisPiasecki 6,422 Most Valuable Professional on at

Like (5)

Report

Hi @Dhruvin,

Two way data integration in general becomes significantly more complex to manage, and more costly because now you have storage costs for both data sources.

I would consider exploring Virtual Tables so you can write data directly into your SQL database but still use it like a Dataverse table and bring it into Model Driven Apps. You will save on storage costs, have your data up to date in real time, and won't have to deal with complex syncing and reconciliation between two systems for when issues occur in the sync.

There is a newer Virtual Connector Provider which basically builds on top of the Connector ecosystem specifically for Virtual table use. SQL Server is the first provider available.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No
vsslasd 349 on at

Like (0)

Report

Hi @ChrisPiasecki
We have a use case where we have a SQL Server View which we created is the Datasource for a Dataverse virtual table. This is great, because we don't need a dataflow to populate the changing data, and this data is Read Only in the Dataverse.

We want to create a writeable Dataverse table in the Dataverse which can be joined to the read only virtual table, where we view, edit and store about five fields or columns and can use lookup columns to other tables in the Dataverse.

I'm hearing that cannot be done currently. How can we join a read only Dataverse Virtual Table derived from a SQL Server View, which can be displayed in a D365 form which includes a linked writeable Dataverse data table with a just a few fields which is joined on a common primary key?

Was this reply helpful? Yes No
ChrisPiasecki 6,422 Most Valuable Professional on at

Like (1)

Report

Hi @vsslasd,

Unfortunately, SQL Server views are not supported for the Sql virtual connector provider yet last I checked.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No
carligp 2 on at

Like (0)

Report

Hallo Chris, the primary problem is , the business data (From Erp Software) are stored on SQL Server DB, and for use Off Line solution with power app, now is available only for Datavers table (Not Virtual Table).
So maybe the question came alone, how to sincornize Data Db from SQL to Dataverse ? Do you have some alternative ?
Maybe use a one way from Sql to Dataverse for store the copy of data and for return data !!
Thanks in advance.

Was this reply helpful? Yes No
Shashank Bhide 942 Moderator on at

Like (0)

Report

Hi @carligp
Can you use power automate triggers?

https://www.youtube.com/watch?v=pJjILoOUl1Y

Was this reply helpful? Yes No