SQL Server Vs SharePoint for building Power Apps standalone

Steel Contributor

We want to start a new project for audit management:-

1) the system contain these main entities and the relation between them:-

- Project

- TaskList. One Project can have many TaskList. and one TaskList can have one to zero Project

- Task. One Task can have one TaskList. and one TaskList can have many Task.

- TaskVersionHistory. One Task can have zero to many TaskVersionHistory. and one TaskVersionHistory can have one and only one Task

- TaskPersonnel. One TaskPersonnel can have one and only one Task. and One Task can have zero to many TaskPersonnel.

- TaskMilestones. similar relation to TaskPersonnel and Task.

 

2) The system need to be available on  mobile devices and have the Offline capabilities for submitting data and reading data.

 

Now we have settled on using Power Apps >> but we are not sure about the data source (SharePoint or SQL Server). Now the customer have Microsoft Business Premium licenses for their users, so both approaches can be used + we have the needed skills in SharePoint + SQL Server. but we are not sure about which data source most suitable to our project.

 

Now from my point of view we have those pros and cons:-

 

SharePoint pros

- we can define custom permission for each item. for example we can define that tasks can only be accessed by the TaskPersonnel

- Power Automate fully support SharePoint, such as defining custom permissions on the items

 

SharePoint cons

- we it comes to build relation between entities SharePoint will fail

- using Lookup fields to define the relation will not work

- trying to do advance filtering for example get the TaskVersionHistory for a Project will be very hard (almost impossible) without getting delegation warning

 

SQL server pros

- we can define advance filtering inside SQL views without worrying about delegation warning

- we can define foreign keys inside SQL server to build the relation

 

SQL server cons

- we are going to use explicit connector, so we need to manage the user access inside the database. for example our app will be used by 200 users.

- defining custom permissions seems not straightforward as in SharePoint case.

 

so can anyone advice about pros/cons of using Sql Server and SharePoint in ur case?

Thanks

7 Replies
Hi,
Using SQL Server in Power Platform requires a Power Platform Premium plan, something you don't have with Microsoft 365 Business Premium

@jcgonzalezmartinAha ok thanks for the info i thought Microsoft Business Premium  will be sufficient ..

@john john If your customer is willing to spend on premium licenses needed for the SQL connector, then I would add a 3rd option - dataverse. There is a bit of a learning curve in getting things set up, but you should be able to achieve everything you outlined in the high level requirements.

 

The SharePoint connector can be slow at times and I have seen response times of up to 90 000ms at times. However if additional premium licenses is not an option then this is your only option.

@Abdurahman_Jardinebut why you prefer dataverse over sql server?

@john johnI wouldn't say I prefer it. It is another option to consider. It depends on the use case and the infrastructure.

 

1 simple example would be if you need to spin up a SQL environment to meet your requirements then you have to consider the license costs around SQL as well ontop of the power platform premium licenses per user. Where the dataverse provides a relational database capability natively if the users are licensed for premium.

As mentioned above M365 Business Premium is a M365 Plan which include the base Power Platform Licence, so basically you need to stay within the M365 tenant (SQL/Dataverse are not classed as within the Tenant).

Power Apps licence to utilise SQL or Dataverse which are proper databases (as compared to SharePoint Lists which are not), you need to consider if the cost if worth it for the business. It can get very expensive as you can either licence Per User or Per App.

If you do go down the premium licence path, a whole new world of development functionality opens up, and Microsoft pitches this for enterprises, against other comparison products which are also very expensive.

If you stick with SharePoint lists research it's limitations, it's not a database, but you can certainly build great line of business applications with it

Happy SharePointing!!

Agreed with Warwick. It sounds like you don't have much choices other than SharePoint, without making it a costly licensing exercise.