Power Platform Community Forum Thread Details

Hi Everyone,

I have been asked to move an internal resource management system from Java/SQL to PowerApps/SharePoint Lists and feel as though I am going around in circles at the moment as I have colleagues who are both for and against the move.

The current Java system has been developed over several years and the SQL database it sits on has 16 tables that use primary keys, foreign keys and composite keys. The two largest tables have approximately 43000 and 50000 records that date from April 2022. There are also 16 views, approximately 40 stored procedures and 14 functions that support the system, mostly in relation to the need to quickly run reports of varying complexity.

The new system needs to be built exclusively in PowerApps and SharePoint Lists to replicate the functionality of the current system but will also need to incorporate some new functionality.

Those colleagues who are in favour of the move and using SharePoint Lists have argued that they can be used to replicate a relational database. This seems to be supported by the numerous YouTube videos that suggest it is possible to do so. The fact that my organisation has developed numerous PowerApps (of less complexity), that sit on multiple SharePoint Lists (that haven’t been normalised) gives weight to this argument. All of the reports in the current system also need to be moved over to PowerBI.

Can anyone with more experience of these matters than me help with the following questions?

What problems might I encounter moving from SQL to SharePoint lists?
Can I replicate complex SQL relationships? If so, how do I do this?
Am I likely to experience any performance problems?
Are there any long-term problems relating to maintaining the data in the future?
Can I run the equivalent of an SQL query somewhere in SharePoint?
What do I do with SQL views, stored procedures and functions? Is there a way to replicate these in SharePoint?
Is this idea a good one or a bad one?

I have briefly looked at using Dataverse and the Microsoft On-Premises Gateway to link to SQL. I can theoretically access both but there are security concerns with the Gateway and logistical problems with rolling out licenses for everyone in the organisation to be able to access Dataverse.

As I say, I seem to be going around in circles at the moment so any thoughts or advice would be welcome.

Categories:

Building Power Apps

Hi,

I understand your position. I have done this for more than 20years for Microsoft, but in those cases the customers / partners / SIS etc are able to sit in a room, white board and or at least I have the ability to talk directly too then.

This is a complex problem and not one that should be solved in these forms, primarily because there are so many what ifs etc that any answer we give will not be concrete.

But i'll give some details on it.

What problems might I encounter moving from SQL to SharePoint lists?
1. This is a huge question, so some simple ones: You can only load so many rows at a time in the Canvas App, tis true for SQL As well but its much higher in SQL. If you build the app correctly, with good solid delegatable queries its not an issue really.
2. Data management is easier in SQL (in general terms)
3. There are more things to do in SharePoint to secure items (rows / columns etc) but once you do it its easy enough
4. You may or may not be able to convert all the data into the same data type, so precision may or may not be a problem, converting data into Multi-select choices(is nice though)
5. Insert other stuff that "might" be a problem
Can I replicate complex SQL relationships? If so, how do I do this?
1. Read This
2. No, there is no way to do what you are asking because your word is COMPLEX. So no, its not the same.
Am I likely to experience any performance problems?
1. There isn't a way to gaurantee answer this, as i really depends on many things. But will it be as performant as the best created app against a SQL. No. Will it or should it be terrible so EWWW don't do it. No. But my no has so many questions under the covers so again. I would never do this for even a paid customer in email
Are there any long-term problems relating to maintaining the data in the future?
1. Not sure what this means. Are you talking security, backup, restoring, roles and a slew of other things, the answer is "standardly" No, its not an issue, but again I don't know your situation very well
Can I run the equivalent of an SQL query somewhere in SharePoint?
1. I don't mean to sound jerky truly, but my real concern is it doesn't sound like there is a deep understanding of SharePoint and again, making any decisions without a deep dive I don't advice.
2. But the answer is, yes, but not exactly T-SQL, but in your Canvas App, you absolutely can run Filters, LookUps etc and you can do this from web calls, OData, etc etc but there is not a T-SQL like UI, unless you count the Properties in the Canvas App where you type the queries.
3. Create SharePoint integrated canvas apps from scratch to view, edit, add, and delete items in a list created using Microsoft Lists - Power Apps | Microsoft Learn
What do I do with SQL views, stored procedures and functions? Is there a way to replicate these in SharePoint?
1. It really depends on what these do. I know what they are, but I have no idea what they do for you. But for the most part:
  1. Functions and stored procedure code would get re-written in Power Automate and / or the Canvas App Directly or both at the same time.
  2. Here are some things if you are doing configuration in functions or ss.
    1. https://learn.microsoft.com/en-us/sharepoint/dev/declarative-customization/site-design-overview
Is this idea a good one or a bad one?
1. This one I am not even going to give you a partial answer. It is way to low level information. This is the kind of thing people pay to get fully designed and figured out.
2. That being said. Its really about a few things
  1. Can I get all my data into SharePoint and build lists, schemas, roles, security, etc etc etc
  2. Can I mimic what the users do in the current app in a Canvas App (or Model Driven App)
  3. Will it scale long term (I know people that have thousands and thousands of documents and use dozens of SharePoint sites and libraries and lists and would never swap it out to SQL

I am also not sure what the concern is with the Gateway if you wanna use Dataverse (which is online), but Gateways have been used for decades and there is nothing I am aware of that would be any more concern thanother moving parts.

Good Luck. I hope some of what I shared helps you

Cheers
--------------------------------------------------------------------------------
If I had answered your question, please mark your post as Solved
If you like my post please give it a thumbs up
Thanks
Michael Gernaey MCT | MCSE | Expert | Ex-Microsoft
https://gernaeysoftware.com