Introducing SQL Database in Microsoft Fabric

Microsoft Fabric has traditionally concentrated on analytical databases, data transformation, and data visualization. Recently, Microsoft has expanded its offerings by incorporating a SQL database into Microsoft Fabric. This brings the Software as a Service benefits of Fabric, such as the ability to provision a database with just a couple of clicks, and the advanced SQL capabilities of Azure SQL Database together to enable operational databases in the same Fabric environment as your analytical and AI databases.

This allows Fabric to become your complete data platform and support all your organization’s AI, analytical and operational data workloads.

Note: As of March 2025, SQL database in Fabric is in Public Preview, so there may be changes before it becomes Generally Available.

Key Capabilities

SQL Database on Fabric is based on the same Microsoft SQL Database Engine that powers SQL Server, Azure SQL Managed Instance, and Azure SQL Database. This means it supports the same Transact-SQL language as those other products, and you can connect to it using your favorite tools such as SQL Server Management Studio (SSMS) and Visual Studio.

Data in your SQL database is automatically replicated into OneLake and converted to Delta Parquet. This makes your Fabric SQL database data available for analytics without developing ETL and in real-time!

Fabric SQL database also automatically optimizes your data workloads, adding and removing indexes based on the queries that are performed while also automating governance though native integration with Microsoft Purview sensitivity labels.

Comparison to Other Options

Overall, Fabric SQL database is very similar to Azure SQL database, but there are a few important differences to be aware of:

• Fabric SQL database only supports Entra authentication. You cannot use SQL authentication or Windows authentication.
• Fabric SQL database will automatically scale up and down as well as pause and resume. It can scale to a maximum of 32 vCores and 4 TB of storage.
• Backups are automatic with a 7-day retention period and zone-redundant (ZRS) storage.
• The collation is SQL_Latin1_General_CP1_CI_AS and can’t be updated.

Refer to the Microsoft documentation at Features comparison: Azure SQL Database and SQL database (preview) - Microsoft Fabric | Microsoft Learn for more details.

OneLake Replication

SQL database in Fabric automatically mirrors its data into Fabric OneLake to the same workspace where the database itself resides. Mirroring starts automatically when the database is created, and all supported tables and their supported columns are mirrored.

The mirrored data is stored as Delta parquet files in OneLake and accessed using the read-only SQL analytics endpoint.

There are a few limitations of mirroring, such as dynamic data masking settings are not propagated to the mirrored table, and columns that store objects such as image, text or xml are not supported. Refer to Limitations of Mirroring for SQL database (preview) - Microsoft Fabric | Microsoft Learn for more details.

You can monitor replication by clicking on the Replication tab of your Fabric SQL database and select Monitor replication. This will show the overall status for the database and list each table along with the number of rows replicated and the date and time that replication was last completed. Any warnings or errors will also be shown.

Power BI reports can use direct lake mode on the mirrored data. This allows you to have near real-time reporting. Since the reporting queries are accessing the mirrored data, they won’t impact the operational database.

Backup and Recovery

Fabric automatically takes full, differential and log backups to allow point-in-time restore (PITR) to any time within the last seven days. Deleted databases can also be restored within the 7-day retention period. You can’t change the schedule of backup jobs or disable them. Backups are store in zone-redundant storage (ZRS) across 3 availability zones in the primary region.

To restore a database, click the ellipsis next to the database name and choose restore database. Then provide a new name for the restored database and select the point-in-time to restore to, which can be any time between the earliest and latest restore point. The database will be restored to the same workspace as the original database, and you can’t overwrite an existing database with a restore.

Migration

Microsoft has not yet focused on migrating existing databases from other platforms to Fabric SQL Database. However, since Fabric SQL Database is very similar to Azure SQL Database you can use tools such as the Azure SQL migration extension for Azure Data Studio to perform an assessment and identify issues in your existing databases that may impact migration.

Fabric SQL Database runs under database compatibility level 160 which is equivalent to SQL Server 2022. Conceptually, the evolution of SQL Server 2022+, Azure SQL Database and Fabric SQL provides a develop once, deploy anywhere model. They each offer baseline feature parity which supports most applications across all database versions.

The SqlPackage utility can be used to export an existing database to a .bacpac file and then import the data into a Fabric SQL Database. For example, suppose you have the AdventureWorksDW database running on-premises in SQL Server 2022. First, install the latest version of SqlPackage following the instructions at https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16 since older versions do not support Fabric SQL Database.

You can then export your database using the command below:
SqlPackage /Action:Export /TargetFile:"C:\AWDW.bacpac" /SourceConnectionString:"Server=tcp:yourservername,1433;Initial Catalog=AdventureWorksDW2022;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;Authentication=Active Directory Integrated"

Then create an empty database in Fabric and go to settings and the Connection strings page and replace the server name and database name (Initial Catalog) in the command below.

SqlPackage /Action:Import /SourceFile:"C:\AWDW.bacpac" /TargetConnectionString:"Server=tcp:yourfabricwsid.database.fabric.microsoft.com,1433;Initial Catalog=yourfabricdbnameandid;Multiple Active Result Sets=False;Connect Timeout=30;Encrypt=True;Trust Server Certificate=False;Authentication=Active Directory Interactive"

This will run for a few minutes to create the database schema and load the data for each table. Note: When you run the import you may see a warning, “A project which specifies SQL Server 2022 or Azure SQL Database Managed Instance as the target platform may experience compatibility issues with Fabric mirrored SQL database (preview).” You can ignore this.

Copilot and AI Capabilities

Copilot for SQL database has several capabilities to help improve your productivity. These include:

• Code completion in the query editor
• Fixing errors in your SQL code
• Adding comments to code
• A chat pane that can translate natural language to SQL. You can use this to generate queries or even to define new tables.

To test out table creation, I asked Copilot to “Create a table that will store a sudoku board”. Copilot provided an explanation that a Sudoku board consist of 9 rows and 9 columns and stores values from 1-9. It then produced the below table creation statement:

CREATE TABLE dbo.SudokuBoard (

   RowNumber INT NOT NULL,

   ColumnNumber INT NOT NULL,

   Value INT,

   PRIMARY KEY (RowNumber, ColumnNumber)

);

This is valid, but not optimal. Since each column will only store numbers between 1 and 9, the TINYINT data type, which holds numbers between 0 and 255, should have been used instead on INT.

Fabric SQL Database is also an AI-ready database. It supports the Vector data type and provides built-in vector functions such as vector_distance. It also can invoke an HTTPS REST endpoint which can be used to call services such as OpenAI. Refer to SQL-db-in-Fabric-Workshop/sqldev/03 - Incorporating Artificial Intelligence at main · Azure-Samples/SQL-db-in-Fabric-Workshop · GitHub for an end-to-end example of incorporating AI in Fabric SQL database.

Costs and Use Cases

A Fabric SQL Database will incur three types of charges:

• Compute
• Storage
• Backups

For determining compute costs, 1 Fabric capacity unit = 0.383 SQL database vCores. So, if you provision an F16 capacity, it will provide 16 * 0.383 = 6.128 vCores for your database. Alternatively, if you have a larger capacity and the database is not heavily used you may not incur added compute cost since the Fabric SQL database autoscales.

Database storage is billed based on the amount of storage used every hour. For example, 1 TB costs about $24/month.

Backup billing will start after April 1, 2025, so backup costs are not currently known.

The table below compares the costs for Fabric, Azure SQL Database, and Azure SQL Managed Instance for a hypothetical database that needs 6 cores and 256 GB storage. (Managed Instance uses 8 vCores, since 6 is not an option in the Azure Pricing Calculator.) All options assume Pay as You Go and East US region.

Based on this, other options are more cost effective than Fabric if you need a database that will be continuously used and require a dedicated capacity. Of course, a Fabric capacity can support multiple workloads including lakehouses, warehouses, Power BI, and Data Factory in addition to Fabric SQL Database. For more lightly used databases that will benefit from Fabric’s autoscaling or will fit within the unused capacity of an existing Fabric capacity, Fabric SQL database can be a great option.

Applications that will benefit from near real-time reporting are also a natural use case for Fabric SQL Database. For example, a customer satisfaction survey application using a Fabric SQL Database would let management see up to minute results and quickly take action to address issues. Power BI reports can be embedded directly in the application to provide a great user experience without worrying about reports slowing down the application. The reports can also combine the SQL Database content with other data in your Fabric OneLake to make them even more compelling.

Conclusion

Fabric SQL Database can be an excellent choice for an organization’s operational database needs. It provides several benefits:

• Software as a Service. You can provision a database with a few clicks and the Fabric platform takes care of scaling and backups.
• Real-time reporting. Since Fabric SQL Database automatically mirrors data to OneLake you can have real-time reports without affecting the operational database, and without developing ETL code.
• Copilot assistance. With Copilot built-in you can accelerate your development efforts for creating tables and performing queries.
• AI ready. AI features such as the vector data type and ability to call REST APIs make it a great database for AI applications.
• Governed. Natively integrated with Purview sensitivity labels which extend into your analytical stores such as the Fabric lakehouse and Power BI semantic model.
• Cost-effectiveness. Fabric SQL Database will autoscale based on the workload to keep costs to a minimum. While other options may be less expensive for a database that is running under load 24x7, if usage varies or if you have available Fabric capacity then Fabric SQL Database can be an inexpensive choice.

As a Microsoft Partner with 10 Advanced Specializations, a Featured Fabric Partner, and a Fabric Databases Featured Partner, Spyglass MTG can assist with any aspect of Fabric, as well as other Microsoft technologies including AI, Azure, and PowerPlatform.

We are here to help, and if you have any questions, please contact me at kfeit@spyglassmtg.com.