Microsoft Fabric ‐ Mirroring Azure SQL Database to Microsoft Fabric OneLake - ivinnyaraujo/dataengineer-datascience-python GitHub Wiki

Mirroring in Microsoft Fabric simplifies integrating Azure SQL Databases into the broader data ecosystem, eliminating the need for complex ETL processes and multiple technologies to create a centralised data hub within Fabric. It incurs no additional cost and leverages Fabric's capacity-based storage. It also protects operational databases from analytical queries.

Azure SQL databases can be continuously replicated in near real-time into Fabric's OneLake, where data is stored in Delta Parquet tables. This enables the use of advanced business intelligence, AI, data engineering, data science, and data sharing capabilities.

Leveraging Fabric's Delta tables in Power BI can also significantly improve report performance, scalability, and cost-efficiency compared to querying Azure SQL Database directly:

  • Improved Performance: The optimised Delta architecture, with features like partitioning and indexing, enables faster queries and quicker report refreshes in Power BI.
  • Scalability: OneLake can handle large datasets seamlessly, allowing for scalability as data volumes grow without impacting performance.
  • Cost Efficiency: Delta tables are designed for analytical workloads, which can lead to reduced query costs compared to Azure SQL.

image

Mirroring items in Fabric: (1) Mirrored database item; (2) SQL analytics endpoint; (3) Default semantic model.

When set up mirroring in a Fabric workspace, it automatically creates three itens:

  • Mirrored Database Item: This takes care of replicating data into OneLake and converting it to Parquet format, making it ready for analytics. This opens up possibilities for things like data engineering, data science, and other downstream tasks.
  • SQL Analytics Endpoint: A SQL endpoint is created, enabling direct querying of the data. Modifications are not allowed, as the endpoint provides a read-only copy.
  • Default Semantic Model: A default semantic model is also generated to help organize and structure your data for analysis.

Note that the System Assigned Managed Identity (SAMI) of the Azure SQL logical server must be enabled to publish data to Fabric OneLake. Otherwise, the error below will appear during the mirroring process.

image

To enable SAMI, open SQL Server in Azure Portal, navigate to the Security section in the resource menu, select Identity, and select Status to On. Be sure to save the changes made.

image

Enable SAMI in Azure Portal.

Steps to Mirroring Azure Database

  • Open the Fabric Dashboard: Log in to your Microsoft Fabric environment and navigate to the Fabric dashboard.

  • Create a New Item: On the dashboard, locate and click on the "New Item" button, usually found at the top or within the navigation panel.

  • Select Mirrored Azure SQL Database: From the list of available options, choose "Mirrored Azure SQL Database" to initiate the process of mirroring your Azure SQL database.

image

image

  • Create a Database Connection (if not already configured).

image

  • Select the Database and Tables to Mirror: Choose the database and specific tables you wish to replicate. You can also enable the option to automatically mirror any new tables added to the source database in the future.

image

image

  • Monitor Replicated Tables: Use Fabric Monitor Replication to track all replicated tables and view details such as the number of rows for each table.

image

Monitor replication in Microsoft Fabric.

image

Itens created in Microsoft Fabric from the mirrored database.

image

  • Track New Row Inserts: New rows were inserted into the [dbo].[Customers] table, and the mirrored row count was monitored via Fabric Monitor Replication. The data appeared in Fabric nearly in real time, with a maximum observed latency of approximately 1 minute and 20 seconds.

image

Adding new rows to [dbo].[Customers] table in Azure Portal.

image

Near real-time data update between Azure database and Fabric Mirrored database.

  • Create Shortcuts: Create shortcuts from the mirrored database tables to the Golden Lakehouse in a separate workspace within Microsoft Fabric.

image

Create Shortcuts.

image

  • Access Fabric Mirrored Data in Power BI from OneLake

image

Limitations

Refer to Limitations and behaviors in Microsoft Fabric mirrored databases from Azure SQL Database (Preview) to know current limitations.

Sources: