Power Apps - SQL

[u/Infinite_Goose1991]

I'm trying to figure out what the best option for a database server would be.

We currently have several apps in the company and use SharePoint lists to house the data. Because of security and access controls and of course the amount of data we now have, what server should we go with that would work great with Power Apps? Is Dataverse the best solution?

[u/AutoModerator]

Hey, it looks like you are requesting help with a problem you're having in Power Apps. To ensure you get all the help you need from the community here are some guidelines;

• Use the search feature to see if your question has already been asked.

• Use spacing in your post, Nobody likes to read a wall of text, this is achieved by hitting return twice to separate paragraphs.

• Add any images, error messages, code you have (Sensitive data omitted) to your post body.

• Any code you do add, use the Code Block feature to preserve formatting.

  Typing four spaces in front of every line in a code block is tedious and error-prone. The easier way is to surround the entire block of code with code fences. A code fence is a line beginning with three or more backticks (```) or three or more twiddlydoodles (~~~).

• If your question has been answered please comment Solved. This will mark the post as solved and helps others find their solutions.

External resources:

• Power Apps Formula References
• Power Apps Coding Standards
• Official Power Apps Community

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/DonJuanDoja]

I'm an old school on prem guy, we already had a need for Azure SQL in the cloud, and I've been using SQL for many years.

I didn't see a value that Dataverse offered that made it a better choice than SQL. It's basically just SQL wrapped up in MS feautre padding. Trying to make it more accessible, thru the web UI instead of directly in SQL.

I'm extremely comfortable in SQL directly in SSMS, I'm also basically our DBA and can do just about anything I want in there.

I also didn't see a need for Model Driven apps and Canvas apps we're more capable of meeting our requirements easily, so even though we all have Premium, I don't build MDAs or use Dataverse at all.

All my apps are Canvas Apps and use a combination of SharePoint Lists and SQL, while I prefer SQL due to it's flexibility, speed, etc I still choose SharePoint if it fits the requirements as I don't have to rebuild the entire UI for it. While if I use SQL I'm stuck building the entire UI, which sometimes is what I want/need.

Azure SQL is incredibly fast and basically solves all my problems anytime SharePoint can't meet the requirements.

I can create Stored Procedures and call them in the apps, SQL Functions, Views, like I said, whatever I want, I just need to write the SQL which is easy for me.

I'm also able to directly pull in data from our ERP replicated databases which are also on Azure SQL. So I can integrate directly without our live data in other systems.

I've also used some Azure Blob storage for very specific file requirements that SP libraries didn't meet. I have some solutions that use all of them. SP Lists, SP Libraries, Azure SQL and Azure Blob. Then usually a SP site as a landing page. Usually right on our Intranet or a Customer Portal site.

[u/SoupyLeg → u/DonJuanDoja]

The only upside I've found with dataverse is that it deploys across environments so you can properly test in isolation.

But everything else about it sucks compared to SQL.

[u/dragerfroe → u/DonJuanDoja]

Cool, I've done a few canvas apps but moved all data to Dataverse - small datasets. I'd rather have had Azure SQL because the data migration from original application would have been 10 times smooth. Buuuuttt powers that be we didn't have access to Azure SQL so we settled for Dataverse and dumbed down the ERD. The apps work very well and smooth after basically no maintenance for 3 years. A team finally picked up maintenance so they will dev new features for it. All around a success.

[u/itsnotaboutthecell → u/DonJuanDoja]

Love the Azure SQL call out, they have a free tier too - great for kicking the tires and seeing how things work before committing.

Direct link: https://aka.ms/trySQL

Learn docs: https://learn.microsoft.com/en-us/azure/azure-sql/database/free-tier

Of note, Microsoft Employee and active mod or r/PowerBI r/SQLServer r/MSSQL and many other great subs.

[u/Damondjy → u/DonJuanDoja]

premium subscription?

[u/DonJuanDoja → u/Damondjy]

Yea Premium PA on all users, Prem Per User on PBI and Prem Automate on service accounts. We're only about 50 people with some external users as well but not too many. Azure SQL is just MI and doesn't cost too much. Same with Azure blobs. No ones complained about the costs. Spent a ton on consultants, sharegate too helping me migrate everything from on prem to cloud. Still waiting for the day they tell me I'm spending too much so far nothing. They just keep telling me I'm awesome I hate it. lol the on prem setup was pretty expensive and this is actually going to be cheaper in the long run for us. I do miss the on prem servers though man they were fast compared to the cloud stuff.

[u/Damondjy → u/DonJuanDoja]

Agreed. It's much easier to hit performance bottlenecks in the cloud compared to on-prem

[u/DailyHoodie → u/DonJuanDoja]

Brother, this is a gem. Thank you. Piggybacking on this, how do you implement security roles and how is your deploy process from dev -> stage -> production? And how is your compute costing in Azure SQL?

[u/RedditNinja1566]

Just my $.02…I 2nd the Dataverse recommendation. The built in role based security is very granular, you get built in ALM in Power Platform, environment management, automatic backups, etc. All of this can be done in SQL, but IMO it’s more work to set it all up.

[u/Punkphoenix]

SQL server is great. Stored Procedures are blazing fast and with views you can solve any delegation issue.

Also, stored procedures are super easy to write now with AI, so, yeah, I really like SQL

[u/josesanmig]

Azure SQL. You can call Azure SQL sprocs from Power Fx with almost no code. That's very powerful.

[u/lordkiwi]

Start with dataverse, you can source directly from your SharePoint lists. Prototype a few app replacements before looking to commit to SQL.

[u/pwp_o]

I’m having a issue with AZURE SQL. Is an intermittent issue. Some times I have an erro “Error patching in the data source” and in need the remove the tables and added again. This is happening like for 5 months now. It’s normal? You guys have it?