cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pheinz
New Member

Sync on premise SQL accounts & contacts into the CDM

Hello,

 

we have an on premise SQL database which contains accounts and contacts. We would like to synchronize those into the accounts and contacts tables of the common data model in powerapps, so we can use them in our model driven app. We do not want to create a new table for them (as far as i know this would be something you can achieve with virtual entities?)

 

Is this possible and can you give me any pointer how to do this? Searching for this topic gives a lot of mixed information and is quite confusing to be honest. 

 

Any help is very much appreciated. Thanks!

2 ACCEPTED SOLUTIONS

Accepted Solutions
PowerPuffKK
Resolver IV
Resolver IV

Hi @pheinz,

 

Virtual entities are the best way to represent data in Dataverse without replicating it.

 

It will not however work in your case unfortunately - virtual entities use OData v4 which can only connect to REST API's. This means that your data would need to be available via an API in order for it to be presented in Dataverse as a virtual entity I'm afraid.

 

Sorry, I know it's probably not the answer you wanted to hear! Here's some docs for reference - https://docs.microsoft.com/en-us/power-apps/maker/data-platform/virtual-entity-odata-provider-requir...

 

If this answer helps you, please mark it as a solution for future forum visitors.

 

Kristine 😊

View solution in original post

ChrisPiasecki
Super User
Super User

Hi @pheinz,

 

You should be able to use the SQL Server Virtual Connector Provider to connect to your on-prem SQL Server if you have an on-premise data gateway configured.

 

You just need to select a SQL Connection that is using the on-prem data gateway and the particular SQL Server you want to connect to.

 

There will be some limitations with using virtual tables which the documentation lists. Also keep in mind that performance will never be as good compared to if the data is stored locally in Dataverse. There will be obvious latency between the cloud and your on-prem server and throughput will be based on your network, so keep those things and mind and try to limit how much data is being returned (out of box views and subgrids will use paging by default). 

 

 

---
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.

View solution in original post

2 REPLIES 2
PowerPuffKK
Resolver IV
Resolver IV

Hi @pheinz,

 

Virtual entities are the best way to represent data in Dataverse without replicating it.

 

It will not however work in your case unfortunately - virtual entities use OData v4 which can only connect to REST API's. This means that your data would need to be available via an API in order for it to be presented in Dataverse as a virtual entity I'm afraid.

 

Sorry, I know it's probably not the answer you wanted to hear! Here's some docs for reference - https://docs.microsoft.com/en-us/power-apps/maker/data-platform/virtual-entity-odata-provider-requir...

 

If this answer helps you, please mark it as a solution for future forum visitors.

 

Kristine 😊

ChrisPiasecki
Super User
Super User

Hi @pheinz,

 

You should be able to use the SQL Server Virtual Connector Provider to connect to your on-prem SQL Server if you have an on-premise data gateway configured.

 

You just need to select a SQL Connection that is using the on-prem data gateway and the particular SQL Server you want to connect to.

 

There will be some limitations with using virtual tables which the documentation lists. Also keep in mind that performance will never be as good compared to if the data is stored locally in Dataverse. There will be obvious latency between the cloud and your on-prem server and throughput will be based on your network, so keep those things and mind and try to limit how much data is being returned (out of box views and subgrids will use paging by default). 

 

 

---
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.

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (2,358)