SQL databases in Fabric: Because Life's Too Short for Database Configuration Nightmares.
At the end of 2024, databases were introduced to Microsoft Fabric, marking a significant milestone in the evolution of Fabric as an end-to-end data platform. The SQL database is the first to join Microsoft Fabric, expanding the platform's capabilities beyond its original focus on analytics into a versatile solution that can also manage transactional data. This means that users can now leverage Fabric for a broader range of data management needs, integrating both analytical and transactional processes seamlessly. For a deeper dive into what this means for the Fabric platform, you can read more in this blog.
What is it?
SQL databases in Fabric are built on the same engine as SQL Server and Azure SQL Databases. The biggest difference is that SQL databases in Fabric comes as a SaaS offering which means that a lot of hassle with setting up a database is taken away from the user.
Microsoft has in fact made it very easy to create an SQL database. As a user, you only have to provide a name for your database and everything else is automatically set up and managed for you. So, you don’t have to worry about choosing the correct scaling or backup strategy for your new database. All things like performance, indexing, scaling, backups, or retention policies are managed for you.
When you create a database, the following fabric items are also created automatically:
A semantic model
As with a Lakehouse and Warehouse, you are assigned a default semantic model that allows you to create Power BI reports to our data. Best practice remains to create your own custom semantic model.
A SQL analytics endpoint
Like a Lakehouse, you can use the analytics endpoint to query your data. What’s important here is that you query your data from OneLake and not directly from the SQL database.
The convenience Fabric offers, by managing everything for you, comes with a small downside: you have less flexibility to configure all the individual settings of your database to your own liking. This may be a reason to still opt to set up your database in Azure, although I am convinced that in most cases, you actually want to use a database in Fabric. SQL databases in Fabric are more than sufficient.
The simplicity of the platform ensures that the creation of databases in Fabric is very developer-friendly, allowing you to spend most of your time developing your (AI) application instead of setting up the database. Everything is designed to save you time and effort, making the development process smoother and more efficient.
Bridging Transactional and Analytical Data
When you create a SQL database in Fabric, a near real-time copy of your data is automatically made in OneLake in delta-parquet format. This process, called Mirroring, separates the load on your transactional database from your analytical data. Heavy queries on your analytical data won’t affect your SQL database’s performance.
Illustration by Microsoft
You can easily access this analytical data via the SQL analytics endpoint that’s also automatically created for you. The interface of the SQL analytics endpoint allows you to create views to prepare your data for further analysis. By using shortcuts, you can also run queries that access your Lakehouse, Warehouse and Database simultaneously, which is a powerful feature of Fabric.
Additional benefits of integration into Fabric
1. Application Lifecycle Management
Fabric includes application lifecycle functionality. With a few clicks, you can bring your database into source control in Azure DevOps or Git. Each SQL item will be converted into a SQL file, making it easy to track changes. In addition to the various SQL files, you will also find a database project file in your repository. When you want to transfer your database from a development to a production environment, you can either set this up yourself in Azure DevOps, or use the existing Deployment pipelines feature in Fabric.
2. Security
Security can be set up on two different levels begin:
Fabric access controls: These include workspace roles and item permissions, making it easy to manage who can access your databases.
Native SQL access controls: These include SQL permissions and database-level roles, allowing for detailed control over access such as Row Level Security (RLS) and Column Level Security (CLS). You can set up these roles using the Manage SQL security UI in the Microsoft Fabric portal or by using Transact-SQL.
3. Storage and Cost
SQL Databases in Fabric have the same pricing as other items you create in Fabric. You pay separately for computing power and storage, so you only pay for what you use. This means you don't have to increase both computing power and storage just because one of them has increased.
When to Use a SQL Database in Fabric?
Although there are many different use cases for using SQL databases in Fabric, the following examples can provide inspiration if you want to use them yourself:
1. Storing metadata:
A common example is when you need a table to store information about your projects. This could include settings for pipelines driven by metadata or data used to manage your transformations. SQL databases in Fabric are perfect for storing this kind of information.
2. When serving as backend for low-code or no-code apps:
It's very easy to connect Fabric with the Power Platform. By using the existing SQL Server connector, you can quickly create low-code or no-code apps in PowerApps that can display and update data from a SQL database. Think of small applications to track the delivery status of sales orders or an app to manage your customer information.
3. When developing AI applications:
SQL databases in Fabric can handle vectors and work well with large language models (LLMs). This makes them a great place to store your data. They are also perfect for keeping chat logs generated by these applications, ensuring your data is organized and easily accessible.
Conclusion
In summary, Microsoft has made it very easy to create a database with the introduction of databases in Fabric. This allows developers to focus on developing applications without worrying about all the configuration options of a traditional SQL database. The integration also provides a solid connection with the analytical side through near real-time mirroring to OneLake, which ensures ideal integration in Fabric.
Setting up application lifecycle management has never been so easy, and because the database supports the vector datatype, it is the perfect companion for developing various AI applications.
If you still want full control over your database configuration, SQL databases in Azure still offer this option. With the addition of databases to Fabric, the platform is set to deliver a complete end-to-end solution.
If you have any questions about databases in Fabric or Fabric in general, don't hesitate to contact us. We're happy to help and answer your questions!
Want to implement this in your workflow, too?
Esli is an experienced Data Analytics Consultant with several years of experience. His expertise lies in data engineering, analytics, and architecture, primarily on the Microsoft platform.