1

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?

  1. What problems might I encounter moving from SQL to SharePoint lists?
  2. Can I replicate complex SQL relationships? If so, how do I do this?
  3. Am I likely to experience any performance problems?
  4. Are there any long-term problems relating to maintaining the data in the future?
  5. Can I run the equivalent of an SQL query somewhere in SharePoint?
  6. 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.

Thanks Becky

Improve this question

1 Answer 1

Reset to default
0

I would push back on the faction that's pushing for the move to SharePoint. There's a lot of nuance to this question, so I'll do my best to address what I feel are the main issues. Keep in mind, this is an opinion, but I've worked on systems that have tried to do this with varying levels of success.

First, SharePoint lists do not scale well, either horizontally or vertically.

Vertically, you start to lose functionality and incur performance penalties as your lists contain more and more items. 5000 items is the "threshold" after which unfiltered views start breaking. There are workarounds, sure, but the performance penalties are real.

Horizontally, SharePoint manages entity relationships via lookup columns. There is a hard limit on how many lookups columns you can add to a SharePoint view. Lookups are computationally very expensive and will cause performance bottlenecks on your application. And if you have multi-level relationships you compound the problem further.

Sprocs and functions, if there's any sort of logic inside these, you're going to have to write custom code to preplicate.

If you need transaction support, forget about that - it's not there.

Here are your choices:

  1. Convince the customer to radically reduce the scope of your application and architect within the limits of what's feasible in SharePoint
  2. Keep the current system
  3. Get ready for a difficult and unsuccessful project

SharePoint is great for event calendars, task lists, document storage, HR nonsense, and other "typical" business type ready made solutions. For anything with real-world scale, complexity, or mission-critical importance, SharePoint is just not built for that.

Good luck.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.