Fabric SQL Database – First look

November 22, 2024 by sabah microsoft fabric

Fabric SQL Database – First look

GOvindarajan D

Data Architect, Data Crafters

SQL Database as an item in Fabric workspace – a significant development that happened ever since the introduction of Fabric. From SQL Database perspective as well, it is a major milestone since it is now available as a SaaS.

Bells and whistles apart, we took a closer look into SQL DB in Fabric, and we discuss the results of our exploration in this blog.

Setup

Setting up a Fabric SQL DB is a simple process, where you just enter the name of the Database and voila! you have a fully working SQL DB that can be used for your transactional processing. Compared to PaaS version, you don’t need to step through lot of configurations to complete your Setup.

SQL Feature Set

As per MS documentation, there are no specific limitations as of now associated with T-SQL Syntax. So, all the features including clustered index creation, merge statement are supported. There are specific limitations on some of the SQL objects which have been listed here: Fabric SQL DB limitations

Performance Monitoring

Fabric SQL DB provides multiple options to monitor performance in an intuitive UI. Automatic tuning is supported, similar to Azure SQL DB, which simplifies maintenance tasks. Additionally, you can use Fabric Capacity Metrics app to monitor the workload and observe trends on the overall Fabric capacity.

Security

Support for Microsoft Entra ID and Microsoft Entra Service Principal exists, but SQL authentication is not supported, just like other Fabric items. SQL authentication is crucial, as it is the only supported mechanism for many third-party applications which use SQL DB.

The security of the database can be controlled through both Workspace permissions (Admin, Member, Contributor and Viewer) and database permissions (by GRANT, REVOKE)

Scaling

Running the SQL DB on fabric capacity is beneficial, as it is utilizes the same compute for all Fabric items. In the Fabric SQL DB documentation, Microsoft mentions auto-scaling as a part of the feature, but additional relevant information is yet to be published. We are eagerly waiting to see how Fabric capacity and its nuances like Bursting/Smoothing play a role in the SQL DB.

Business Continuity

Analogous to Azure SQL DB, Fabric SQL DB provides point-in-time restore through which backups are continuously made in the background and when necessary, can be restored using simple steps. In the same way, Zone redundancy is provisioned in Fabric SQL DB through which it has high availability.

Fabric Integration

The major advantage of having Fabric SQL DB is that, it is an HTAP (Hybrid Transactional and Analytical Processing) system. The records are replicated in near-real time to Fabric through internal mirroring making it ideal for applications that require HTAP type implementations. But if you are expecting real-time through CDC, Fabric SQL DB does not support CDC right now!

The Fabric integration of SQL DB is so good that you would think it is the Fabric Warehouse item. The SQL DB can be used with data pipelines and Dataflow Gen 2. Further, you can use the Mirrored Database of the SQL DB instance through which you can interact with the data using Spark notebooks.

Just like other items, Workspace roles influence how the SQL DB permissions are handled and by sharing the SQL DB, granular permissions can be provided through SQL GRANT/REVOKE. In addition to that, there is an intuitive GUI for creating roles in Fabric SQL DB and providing access at schema level.

At the moment, Visual Query is not present within SQL DB, but this would have been a nice addition given that it is in SQL Analytics Endpoint.

Analytics

For organizations which do not have the necessity for a full-fledged Data Warehouse, they can use the SQL DB as their analytics system. This comes with the advantage of having tighter integration with other tools like Data pipeline and Dataflow gen 2, compared to hosting the SQL DB as a separate resource in Azure. Developers can interact with all the SQL features compared to the limitations that are faced in Lakehouse/Warehouse. For example, one can have an enforced primary key on a table.

The data can be consumed in Direct Lake mode with Power BI using the Semantic Model which is generated through mirroring. Essentially, you have a fully featured SQL syntax coupled with Direct Lake mode and all of them running in the same environment. This architecture also allows you to scale later for big data as you can migrate from SQL DB to Lakehouse/Warehouse through mirrored data.

Closing thoughts

There have been numerous updates ever since Fabric got released and the addition of SQL DB in Fabric is no less than a major breakthrough and we are curious to see how Microsoft will enhance this more. The Fabric SQL DB, as a SaaS offering, is easy to set up and work with and that includes analytical tools as well! Currently, we are cautiously optimistic about this feature, and we are looking forward to seeing more updates on this!

English