Dataverse Vs SQL Azure

[IsaiahA]

Hi:

I am currently planning to build  a system that I hope would have 100s or thousands of users. However, I wanted to use SQL Server (SQL Azure) rather than Dataverse (based upon my reading Dataverse has SQL Azure backend). I have several years of experience, using SQL Server, and  I am late in the game to Dataverse. Could someone advise a better choice between the two? 

Thanks,

Isaiah A.

[dpoggemann · Accepted solution]

Hi @IsaiahA ,

This is only my opinion, hopefully it will be helpful...

Dataverse is a fundamental shift in your application development from code to low-code / no-code and leveraging the many capabilities within the Dataverse to speed development and features that would take time to build with a traditional application.  I know you can leverage Power Apps Canvas Apps for low-code development on top of a SQL Server environment but it does not take advantage of many features that Model-Apps do.  Some key items that are drivers for me:

1. Scalability - Model apps leveraging the Dataverse have proven to scale to thousands of concurrent users with Microsoft's performance studies with applications like Dynamics 365 for Sales.  
2. Security - Dataverse has many security features built in including encrypting data at rest in Azure, very strong role based security, field level security, and more.
3. Accessibility - Microsoft's Model Apps are built with ADA accessibility in mind
4. Environments - Ability to create new environments very quickly for sandboxes, etc. and backup / restore very quickly
5. Auditing - Built out of the box.  Enable auditing at field level within your tables and the model app already has ways to view these audit records
6. Activities - Leverage Activity tables (Email, Calendar, Task) and relate these to other tables.  Configure integration with Exchange within environment
7. Document Management - Utilize Attachments (File, Image) to a record, not highly recommended, or integrate to SharePoint
8. Microsoft Teams integration to model apps is native and getting stronger every day
9. Export / Import - Many out of the box features that can be leveraged within Dataverse and Model Apps to import and export data
10. Model App - UI Components - Utilize the Dataverse to build forms, views, charts on top of your data with no code and then bring these together into the model apps.  The ability to bring this information together quickly is amazing.

Overall, Microsoft states that Power Apps development decreases development time by 70+% in a project and I really believe this statistic is valid when you are talking about Create / Read / Update / Delete (CRUD) type applications leveraging Dataverse and Model Apps.  

Now, I am not saying SQL Server is bad, it is amazing and you can really do many things with your data that you would not be able to do in Dataverse like creating cross database joins, materialized views, and the list goes on and on.  It will depend on your data environment and complexities if Dataverse makes sense but I would take a hard look at it as it really brings you into a whole new development paradigm with ability to build applications at a rate and pace that would excite your customers.

Hope this helps.  Please accept if answers your question or Like if helps in any way.

Thanks,

Drew

[dpoggemann · Accepted solution]

Hi @IsaiahA ,

One thing to be conscience of as well is pricing...  Microsoft is changing their pricing for Power Apps Per App license to $5 per user / month starting in October (unless this changes) and it would be for any internal organization users.  Customers, Suppliers, etc. you would build a Power Apps Portal environment and leverage licensing there to extend to end users. 

Thanks,

Drew

[IsaiahA]

Hello Drew,

Thank you.

Isaiah A. 

[IsaiahA]

Thanks again, Drew.

Isaiah A. 

[rampprakash]

Hi @IsaiahA ,

Apart from that,

1. If we using SQL we need to design FORNT end, but if we are having Dataverse, there is no need of that
2. Security role configuration is easy

3. Easy to move solution from one Environment to another environment by just click on button

4. We can easily write Business rules for validation without developer too

[IsaiahA]

Hi:

I do not understand what you meant by if we are using SQL need to write [FRONT} end, and that Dataverse does not require that. I thought both of them need some some form of front-end to be written. I appreciate your contribution. In my conclusion, based upon the responses that I got, I will learn to use both (using SQL Server and Dataverse backend). If Excel can be used, and if SharePoint can be used, then, using SQL Server as well is a good option, and it will depend on the type of application. Dataverse has evolved, and I will definitely be learning more about it. 

Sincerely, 

Isaiah A.  

[dpoggemann]

Hi @IsaiahA ,

It is similar to what I put in point 10 with the UI components.  Dataverse provides the ability to "configure" user interface forms without requiring any HTML / Javascript / etc. and the UI form can utilize Business Rules and Business Process Flows as well which would need to be built from scratch or using another toolset requiring development usually...

Overall I would suggest looking at Model Apps if you have not done this.  This is where you will gain significant productivity gains in relational data based applications.

Thanks much!

Drew

[EricRegnier]

Hi @IsaiahA, here's another quick article that can help: https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/Dataverse-is-not-a-database/ba-p/75621... 

[PockyMaster]

Maybe a bit contrasting to the comments of dpoggeman, 

There are scenarios where the Dataverse can speed up development. And there are many where it isn't.

Everything that is not supported out-of-the-box will take a lot more development time than what it normally takes.

Think about validation rules, defaults etc. There might be scenarios where the provided tools work, but not for me.

Also be super careful with refactoring. It's not really straightforward to see where dependencies are if you decide to drop a column. Not with the modern tools at least. You need to revert back to the legacy interface to get some help. Changing column types in case you made a mistake? -> Remove the field from all your forms, views etc. Drop the field, and try to memorize where you had them all before.

Could be that there are tools for this that I'm not aware about. But this is not the best experience (now).

Also calculated fields are somewhat limited. 

So my take:

Works fine if you get everything right at once and don't mind the performance penalties or the more expensive storage model, or versioning, or backup, or ...