SQL Alternative

[watts]

Hi,

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.

Thanks!

[rebeccas]

Yeah, I can see that. We already had SQL so it was no additional cost for us. 

I do a lot of Apps with SharePoint list. You do have to maintain the amount of data you keep on one list (esp if you are going to tie to an app) but if you think it through it really isn't that hard to do. My most common method is by moving items (via Flow) to separate list when the status is changed on the item. 

[Delid4ve]

All of my apps link to sql which our in-house software uses, unfortunately the software doesn’t support cloud or any other databases so we have to use the sql connector. Just think it’s a bit of a Liberty when sql is an ms product that you need premium now.

[rebeccas]

I agree...I was disappointed when they changed that part and had to readjust several ways of doing things. Its annoying!

[watts]

@rebeccas , we will be reading and writing to these tables (I assume that's what you meant by just looking?) frequently.  It is my understanding that Excel files lock when they're being accessed, so having multiple users reading/writing to the same Excel docs would pose a problem.  Is that correct?  I haven't actually tested this in PowerApps though, has it worked in your experience?  The other concern would be performance.  What have you seen when using Excel files and SharePoint lists vs SQL Server?

@Delid4ve , yeah upgrading to a dedicated PowerApps license isn't in the cards.  We can't afford to pay as much for this license as we do for rent.

@timl , the closest option I'm looking at is Google App Maker as it is also bundled with the other business tools in G Suite (email, calendar, cloud storage), which would mean moving out of the Microsoft ecosystem entirely.  I'm also considering pre-built software for our industry (Ekos and Keg ID in my case) which at this point would be cheaper and also cut out all the development time, and then moving email and calendar to a free option like Zoho.   Or we could continue to run things off spreadsheets like we are currently.

[timl]

Thanks @watts , it's very interesting to see the options that you're considering.

[Mike2500]

I didn't see anyone suggest excel. SharePoint lists are their own data store, and don't use excel. That said, SharePoint lists can act like a relational database, sort of, but they really don't make a great relational database. One issue is simply performance. While updating individual rows will be fine, if you ever need to update multiple rows then SQL will be far more performant. And, while SQL has been tuned over decades to do joins efficiently, this really wasn't a consideration with SharePoint via Power Apps. If, for example, you need to show a parent-child relationship on a screen, such as a customer and their orders, the typical way to do this in power apps would be two separate calls: fetch the customer, and then do a separate fetch to get the orders for that customer. 

Of course, there's always SPFx (or ASP.Net) if you don't mind code.  

[watts]

@Mike2500 Looking at my apps, I think for the most part I was doing what you describe, showing the customer screen, then using the filter function to grab the order records.  Was there a better way to do that w SQL?

When comparing SharePoint, it looks like things should be mostly similar--I can use the filter and lookup functions to get the records to display--except DateTime fields don't support any sort of delegation, and there are no views.  I was using views to do the heavy lifting in a couple scenarios that required more complex logic; one example in my keg tracking app was to get the most recent entry from the log of actions performed on a keg, then link it to various other tables for details in order to show its current status.  I don't see a good way to accomplish this as neither views, nor delegation of queries on DateTime fields are supported.

[watts]

@timl :  I just wanted to follow up now that we've had some time to sift through our options.  We've actually decided to build full native apps based around Flutter for the front end and Firebase for the back end (both Google).  I had development experience at a previous job before starting my current company, so code isn't scary, it's the speed of development that I was excited about with PowerApps.  Flutter has a steeper learning curve of course, and it's taken some time to build a library that supplies the screens and functionality we will need, but after that initial investment, building out a new workflow has become almost as quick.  Also building a real native app unlocks capabilities--granular permissions, robust caching, external users, etc.--that weren't available in PowerApps.  I'm not sure about the transition from SQL to NoSQL, but initial testing has shown that the combination of hierarchical documents and enums in the code has significantly reduced the number of tables we need and thus the number of relationships between them.  There are no views though, so I'm still waiting to see how it will perform in situations that do in fact require significant joins.  Also Flutter is open source (though Firebase is not), which I now recognize is a plus after this licensing change left a bad taste in my mouth.  It was certainly frustrating, but at this point I'm thankful the timing worked out so that we could get off PowerApps before we were any further down that path.

[timl]

@watts 

Thanks very much for taking the time to report back on what you decided to use.

It sounds like this has worked out very well for you and based on your findings, it's a platform that I'd be keen to learn more about.

Best of luck with your future developments 🙂