SharePoint vs Dataverse vs SQL
Very frequently, our customers wonder about wich data storage to choose from all the options available on Microsoft 365. For this reason, we created a comparative table to explain more clearly the difference between the available options. given the practicality of the table that we created, we decided to make it public so other people can benefit from this explanation and take better decisions when working on development proyects.
Let’s compare
| SharePoint | Dataverse | SQL | |
| Price | – It’s included on the MS 365 Licenses. | $ When trying to use it in Canvas App users require license to open the app. | $$$ To use it as a data source in Power Apps, same license as Dataverse is required, additionally, you need to pay for the SQL database hosting in Azure too, unless you have your own on-premises SQL Server. |
| Data storage | The more licenses the more capacity you get, this is shared between all sites. Data and documents storage are the same. | The more licenses the more capacity you get, this is shared between all environments. Data and documents storage is separated | This is billed based on consumption, so the more the database grows the more expensive it will get. |
| Documents Storage | The best option for documents because it was as a DMS, it has plenty of space and can organize and secure folders easily. | It’s possible to attach documents directly to the Dataverse records, however, the storage is going to be significantly less than SP, so the recommendation is to setup an integration with SP to store the documents there instead. | It was different ways to store documents in the database, however, this needs to be planned carefully by an experienced person, this option should be used when there is a strong justification, otherwise, use cloud storages like SP. |
| Data extraction limit (max query size) | 5,000 items (rows) This is a fixed limit and is regardless of the number of columns or data size. Some strategies are used to get all data, like using refined queries with good filters or use paginated queries (splitting the request to multiple iterations). | 80 MB This is a fixed limit and is regardless of the number of items (rows), because a table could have lots of columns so a single row could have lot of data. Still is a significantly larger limit than SP because is very unlikely that a single row can even reach 1 KB of size, so we are talking about hundreds of thousands of rows. |
In theory depends on the other endpoint capacity to process the query, however, this needs to be consulted with an SQL specialist. |
| Power Apps Delegable functions (queries limitations) | Some supported functions
| A few additional functions
| Many additional functions
|
| Complex Entities relationships | Could use workarounds to try to get something similar but will never be as robust as a real DB, and will be difficult to maintain, specially if the data grows. | Supports complex relationships by default because its database engine is based in CRM technologies, so it was built to support real world situations. A non-technical person can do the setup with some training. | It can be setup as you want, however, it will be necessary advance knowledge and experience with database design and maintenance. It will be necessary to have the help of a Dev or IT person to set it up. |
| Security Settings | Relatively easy to setup when using a few roles, the more roles you have the more complex it can get, it can go as deep as restricting certain rows or items, however, it doesn’t have column-based permissions. | It can support complex security scenarios, all the way down to column-based permissions, however, the security roles setup needs to be planned properly, it can be possible for a non-technical person to learn this, however, needs a few more hours of training than SharePoint. | End user security needs to be designed from scratch, because the security settings it has are for data connections, not for user interface permissions. |
Recommended Scenarios
Why would I need SharePoint?
If you need to store documents SharePoint is the way to go, is hard to find any solid argument against this, is cost-efficient and is the best DMS in the market. Even if your solution stores data in Dataverse or SQL, still, documents can be stored in SharePoint no problem. In terms of solutions that require data storage, is ok but more for low complexity solutions or isolated projects, specially if you want to delegate maintenance to non-technical personnel.
Why would I need Dataverse?
If you need a solution for internal usage in your company that require data storage and is an important solution for the company, then Dataverse is your option. This option gives an excellent range of movement for scalability and integrations. It can also be integrated with Power Pages for interactions with external users, and with SharePoint for document storage, so its basically going to become the convection point of your solutions.
Why would I need SQL?
For internal usage it will be necessary to use SQL if you need to install an enterprise solution that requires it as the database for the system, basically, when the requirement of purchased system demands it. If you are building something from scratch for internal usage, I would think it twice given the advantages of Dataverse. For external usage, SQL could be a good option if you plan to launch to the public a new software or app, for example, a native mobile app or a specialized software, of course there are other database engines on the table for this kind of situations but in this case, we are only using this as a comparison with SP and Dataverse.
