It appears the latest licensing change blocks access to SQL Server and Azure for Office 365 licenses. Does anyone have a recommendation for a standard data source that we could migrate our data to? I just need a standard relational database, and the extra cost of the new PowerApps user licenses would be cost-prohibitive.
Unfortunately, I think that SharePoint is the only alternative.
All the other relational databases such as DB2/Oracle use premium connectors. Even connectors for open source DBs such as MySQL and Postgres are premium, so the cost would work out the same.
Maybe someone else might have another suggestion....
Hi @watts ,
Do you want to migrate your SQL data to another standard data source?
Based on the needs that you mentioned, I agree with @timl 's thought. I think SharePoint may be the better choice for you. You could consider migrate your SQL data to SharePoint List.
Currently, within PowerApps, SharePoint connector is a standard conector, which does not need extra PowerApps license for accessing it, Office 365 license is just enough. And the SharePoint connector is also a Delegable data source, which has an advantage on processing data within PowerApps app.
More details about Delegation in PowerApps, please refer to the following article:
More details about SharePoint connector in PowerApps, please refer to the following article:
Thanks @timl and @v-xida-msft for the responses. It makes sense that out of the provided options, SharePoint lists are the best match. However it was my understanding that SharePoint lists aren't recommended for relational data? For example I run a brewery and to build a simple keg tracking app, I need to have linked tables for kegs, keg transfers, products, accounts, orders, etc. Does this work with SharePoint? And is it possible to create views (or something analogous) in SharePoint?
Hi @watts ,
Based on the 'relationship' in your SQL Tables, I think the LookUp field in SP list could achieve your needs. You could add a LookUp field in one SP List, then reference values from another SP list. After that, within PowerApps, you could do some data processing based on the LookUp relationship.
Currently, SharePoint View is not supported within PowerApps. You could create a View in a SP list, but you could not use it within a PowerApps app (which is different from SQL Server).
If you would like this feature to be added in PowerApps, please submit an idea to PowerApps Ideas Forum:
Yes, it's possible to create relationships and lookup lists with SharePoint.
Here's a link with more details.
There's no way to create views in SharePoint that you can use in PowerApps, so that's a big disadvantage.
The thing to really look out for is delegation. There are far fewer delegable functions/operators in SharePoint compared to SQL Server. This can cause problems in searching for data, particularly when the size of your data grows.
Ok, looking at our current implementation, we would need to use views, so it doesn't look like SharePoint will work. We're going to have to look for something besides PowerApps for this project. Support for views in SharePoint would be nice, but I think what I really need is to be able to use a real database and to have a stable pricing structure that by business can plan around. Thanks for the help @timl and @v-xida-msft.
Thanks for the update. I understand how important it is to have a real database and stable pricing structure. Perhaps you could let us know what you decide to use? This could help others that also find themselves priced out of PowerApps/SQL Server.
One option (if you are just looking) is to use Excel. You can use PowerQuery to connect to SQL and then tie to the Excel sheet in your app.
Another option we have done is we got one ProFlow license (much cheaper than the PowerApps one) and we use Flow to take new items created in SQL and create an item on a SharePoint list. We were never going both ways with SQL (just looking) so this does the same thing. It works well when it is data that doesn't change often, you have to look for modification triggers and modify the list if it does change often in which case the Excel would probably be better.
Only thing to keep in mind if you do the excel route is you will need to create a Flow that refreshes that data periodically or by a trigger in your app.
These are work arounds but will work for some scenarios. We have found a few that is not pulling a ton of data that this works perfectly for.
Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.
Fill out a quick form to claim your user group badge now!
Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities
Features releasing from October 2019 through March 2020
Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.
Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications