Connecting to Sql Server

(0) Share

Report

Posted on by brandimore

190

Hi There

We are building options in how we can connect to our on Prem Sql Server database. Please bare with me I'm getting more familiar with the Dataverse as I'm still new.

Can someone provide me a document or just list a few options to connect? I'm a bit confused on the Dataverse Gateway vs landing the data right in the Dataverse.

Note, we are looking at using Power Apps and pages. Thank you.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (2)

Verified answer

Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report

Hi @brandimore ,

Overall the best database / data platform to leverage with Power Apps is Dataverse but if you still have an on-premises SQL database you can definitely connect to this with the on-premises data gateway (https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/gateway-reference).

Another option is to look at Virtual Table Connector (still in preview though)... This will allow a good way to connect to SQL Server and utilize in Model-Driven Power Apps. https://learn.microsoft.com/en-us/power-apps/maker/data-platform/create-virtual-tables-using-connectors?tabs=sql

I provided a general answer on pros / cons on Dataverse vs. Azure SQL (https://powerusers.microsoft.com/t5/Microsoft-Dataverse/Dataverse-Vs-SQL-Azure/m-p/1238904#M13480 )

Hopefully this is helpful.

Was this reply helpful? Yes No
brandimore 190 on at

Like (0)

Report

Thank you so much for these details! It definitely answers my overall question. One more quick question if you don't mind. And again, excuse my rookie question here.

If I don't use the on-premise gateway and I go with the dataverse, are you using Power Flows to land data in the dataverse? What's the connectivity from the dataverse to the on prem database? Right now there is no plan to move the database to the cloud, so I'm wondering if we have to replicate the data in the dataverse and what's the best method to do so? Any link you may have would be helpful.

Thank you!

Was this reply helpful? Yes No
brandimore 190 on at

Like (0)

Report

Hi There - Just wondering if you saw my response... Sorry to bug!

Was this reply helpful? Yes No
Verified answer

Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report
Hi @brandimore ,

There are a number of options here including:

Azure Data Factory - This is a great tool that will allow you to connect to the on-premises SQL Server environment and utilize pipelines to push the data to Dataverse. https://youtu.be/X61c6MxB8TE

Dataflows - You can leverage dataflows through the on premises data gateway and push data to Dataverse (https://learn.microsoft.com/en-us/power-query/dataflows/using-dataflows-with-on-premises-data). Note, this has to utilize the on-premises gateway as mentioned.

Power Automate Flows - This would use the on premises gateway as well...

Of the 3 items above, I would think Azure Data Factory might be the best solution that would align to your needs. Of course there are other solutions as well (3rd party).

Was this reply helpful? Yes No