The SQL Database workload in Microsoft Fabric, announced at Ignite 2024, marks a major milestone. This new feature introduces a SQL Server database, similar to Azure SQL Database, but offered as a Software-as-a-Service (SaaS) solution instead of the traditional Platform-as-a-Service (PaaS) model.
Cutting through the noise, we took a closer look at SQL Database in Fabric, and this blog details what we uncovered.
Setup
Creating a Fabric SQL Database is as simple as naming it—no extensive configurations needed. In comparison to the PaaS version, the setup process is much more user-friendly, offering a fully functional SQL Database for transactional processing in no time.
SQL Feature Set
According to Microsoft’s documentation, there are currently no specific limitations on T-SQL syntax. This means features like clustered index creation and the merge statement are fully supported. However, there are certain restrictions on some interactions, which are detailed here: Fabric SQL Database limitations
Performance Monitoring
Fabric SQL Database provides multiple options to monitor performance easily, thanks to its user-friendly interface. Automatic tuning also is supported just like Azure SQL Database, making it simple to work with. Additionally, you can use the Capacity Metrics app to monitor the workload and observe trends on the overall Fabric capacity.
Security
Microsoft Entra ID and Microsoft Entra Service Principal are supported, but SQL authentication, unlike other Fabric items, is not available. This is significant since many third-party applications rely solely on SQL authentication.
The security of the database can be controlled through both Workspace permissions (Admin, Member, Contributor and Viewer) and database-level permissions (by GRANT, REVOKE)
Scaling
Running the SQL Database on fabric capacity is beneficial as it utilizes the same compute model for all Fabric items. Auto-scaling is listed as a feature in the Fabric SQL Database documentation, but more details are yet to be published. We’re looking forward to exploring how Fabric capacity and its nuances like Bursting/Smoothing play a role in the SQL Database.
Business Continuity
Fabric SQL Database, much like Azure SQL Database, features point-in-time restore, with continuous background backups that are simple to restore when required. Similarly, Zone redundancy is incorporated in Fabric SQL Database, ensuring high availability.
Fabric Integration
Fabric SQL Database offers the major advantage of being an HTAP (Hybrid Transactional and Analytical Processing) system, with near-real-time data replication to Fabric through internal mirroring. This makes it ideal for applications that require HTAP-style implementations. However, if you’re looking for real-time updates via CDC, keep in mind that Fabric SQL Database does not currently support it.
The integration of SQL Database within Fabric is so smooth that it could easily be mistaken for the Warehouse item. You can work with data pipelines and Dataflow Gen 2, and even use the mirrored database to interact with data through Spark notebooks.
Just like other items, Workspace roles influence how the SQL Database permissions are handled and by sharing the SQL Database, granular permissions can be provided through SQL GRANT/REVOKE. In addition to that, there is an intuitive GUI for creating roles in Fabric SQL Database and providing access at schema level.
Currently, Visual Query is not available in SQL Database, but it would be a useful addition, especially given its presence in the SQL Analytics Endpoint.
Analytics
For organizations that don’t require a full Data Warehouse, SQL Database can serve as an ideal analytics system. This comes with the benefit of having tighter integration with other tools like Data pipeline and Dataflow gen 2, compared to hosting the SQL Database through Azure. Users get access to all SQL features, which aren’t available in Lakehouse/Warehouse, such as the ability to enforce primary keys on tables.
Data can be consumed in Direct Lake mode with Power BI, using the Semantic Model created through mirroring. This provides a combination of full SQL syntax and Direct Lake mode within a single environment. This architecture also allows long-term scalability, as users can migrate from SQL Database to Lakehouse/Warehouse through mirrored data.
Closing thoughts
Since Fabric’s debut, there have been several important updates, and the addition of SQL Database is undoubtedly a significant step forward. We’re curious to see how Microsoft will continue to improve this feature. As a SaaS offering, Fabric SQL Database is easy to set up and operate. At this stage, we’re cautiously optimistic and looking forward to future updates!
