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?
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.