Power Platform | Dataverse or Azure SQL or both?

„We are the creative force of our life, and through our own decisions rather than our conditions, if we carefully learn to do certain things, we can accomplish those goals.“

– Stephen Covey

At the beginning of this month, I shared an article around a typical upcoming question regarding automation tools. Today, I’d like to share some insights about another question that is pretty common before start creating apps with low-code tools like Power Apps and the need for a robust and scalable database to store all the app data collected – using Azure SQL or Microsoft Dataverse or both?

Let´s get started with above visual outlining the main purpose and intention of both. No surprise, same as last time you mainly need to drive a decision around IaaS, PaaS or SaaS. Agree, Disagree with the short summary?

What I´d like to ensure when a question like this is upcoming, that we all share the same common definition around what we´re talking about. Therefore, we should clarify what kind of Azure SQL we’re talking about. Too many times we’re calling out Azure SQL and have something in mind, but in fact there’re multiple options both on IaaS and PaaS side. Let´s take a look.

From previous article you already know about the level of support and management you would need to be responsible for comparing IaaS and PaaS offer with SaaS. When someone in a Design Thinking workshop would say:

„Hey, I’d prefer to go with Azure SQL for creating new apps, or beginning app modernization…

I wouldn’t say no, but I would challenge the impact of such decision made early stage and ask for knowing the details already. For those new to Azure SQL, Microsoft is offering a course via Channel 9 which covers Azure SQL for Beginners in six compact modules with great intro and demonstration videos. I recommend taking a look into this, even if you´ve been working with Azure SQL for a longer time – a refresh of learning can´t be wrong. I won´t cover all of the content of this course today, instead focus on principles you will find inside Microsoft Dataverse as well. Those are:

My first question would be around knowing the amount of tasks it takes in comparison of setup, configuration and deployment between Azure SQL and Microsoft Dataverse – a question of IaaS, PaaS and SaaS specifics. When performing a business value assessment, this is pretty important even if this effort could be reduced. But many times I see those facts being missed inside a business value assessment or simply ignored due to these tasks already been completed due to a different project the Azure SQL infrastructure was needed for.

A second question would be around security and role-based access control to granular shape the access on data.

Of course the amount of data collected would grow over time; instead of a single app, multiple apps may require access to same data, such as sharing contact information across multiple apps. Visualizing data with Power BI or performing some analysis of data might cause additional user access in future.

Therefore, continuous monitoring and management of security would be recommended. Above visual outlines some typical tasks you would find in a checklist when preparing your Azure SQL governance concept. Next would be a question around Auditing. Depeneding on which kind of applications you´re going to create, this feature can become handy – assuming multiple users would be able to edit a single record.

Above visual again outlines some checkpoint items on your governance concept that you would setup. If you´re interested in all the details around this with demos – again, please make use of above Channel 9 course. Our next topic is something typical when talking about data in the cloud – Data Encryption. Below you can find some valuable insights around this topic.

Next, in many of app scenarios there´s sensitive data that depending on rules users are granted access to while others shouldn´t be able to access or visualize the data. Azure SQL in this case offers Dynamic Data Masking. So a question from me would be around use-cases that might cause this specific situation and how to deal with it. Below visual shows the role of a normal end user and a data officer. You can see the differences inside Social Security Number and email address.

Almost at the end, we will find two additional topics. First a question regarding maintenance around performance. Again, something that carefully should be considered when performing a business value assessment. Below visual highlights the two main ongoing streams. Depending on scale and Azure SQL infrastructure there may be more to be considered in an enterprise organization. Don´t underestimate this effort, as slow-performance on accessing the app data might cause a bad app user experience that ends up in not using the app or finding workarounds.

Last, but not least for a desaster recovery concept there´s our last question around a backup and restore concept on Azure SQL. Again, effort that should be recognized in business value assessments comparing IaaS, PaaS and SaaS. Some insights around this can be found in the following visual.

You may now ask, why I did outline all these principles around Azure SQL? This is to provide you a guidance when comparing the options of Microsoft Dataverse (SaaS) or Azure SQL (IaaS, PaaS). You can find many of above listed principles inside Microsoft Dataverse as well – though feature names could be different. Take a look at the following visual and focus on Environment Lifecycle in regards to Deployment. Move next to Security + Compliance for RBAC, Auditing, Data Encryption and Dynamic Data Masking (Field Level Security). Then return to Environment Lifecycle in regards to Backup, Copy and Reset for Disaster Recovery. Are you surprised of seeing similarities?

Let’s shortly deep dive and take a closer look behind the scenes – what’s behind Dataverse? Dataverse is a SaaS offer that is „backed“ on Azure and can be categorized in compute & storage + events & extensibility. No surprise, inside the storage you do find Azure SQL Elastic Pool. So is Microsoft Dataverse the low-code canvas of Azure SQL? Well Dataverse is way more than a database only. It is a service on its own. Dataverse stores Table Data in Azure SQL. But it also stores Table data in Azure Storage, Cosmos DB, Azure Data Lake and Cognitive Search – all in an intelligent way without you being in need of making a decision. All this is exposed via T-SQL, OData- and REST API.

In addition and as you would expect it from a SaaS offer, it scales with demand without being in the need of taking care of the infrastructure behind this. You need more power regarding some jobs or reports – Dataverse will handle this for you. You need more power reagrding the storage – Microsoft Dataverse can handle this for you as well. Of course it may cause you to invest in a capacity add-on from a licensing perspective in this case, but no invest into additional infrastructure. Asking yourself where you can learn more about Microsoft Dataverse? A learning path could be found via MS Learn. And don´t forget about the events & extensibility, which will allow you to build specific integration scenarios that might be needed for your apps. Additionally, take into account based on customer feedback, Microsoft recently extended Dataverse for Teams capabilities to ensure your quick start.

Does by saying all this – Microsoft Dataverse should be in favor of Azure SQL? Of course not, there´re reasons for using Azure SQL and there´re reasons for using Microsoft Dataverse. In fact, I´ve seen many enterprise organizations using both. Plant control towers in manufacturing industry that are heavily related on Azure IoT Services, Azure Digital Twins, Data which needs to be visualized and analyzed + causing actions – for instance an onsite repair service that should be triggered automatically based on thresholds.

Above visual outlines the power of Azure SQL to build modern apps, both in cloud and on-premises scenario. Adding Microsoft Dataverse to it, simply evolves this story and offers another option for modernizing your app infrastructure while allowing an easy extension with low-code tools offered via Power Platform. The final decision is up to yours.

Until then,…