Disclaimer: This is work in progress intended to consolidate information from various sources.
Last updated: 18-Apr-2024
Mirroring in Microsoft Fabric (adapted after [2]) |
Mirroring
- low-cost, low-latency fully managed service that allows to replicate data from various systems together into OneLake [1]
- supported in Azure SQL Database, Azure Cosmos DB, Snowflake [1]
- ⇒ replaces ETL pipelines
- ⇐ manages the replication of data into OneLake and conversion to Parquet [1]
- changes have a near real-time latency [3]
- uses the source database’s CDC feature [4]
- {benefit} allows users to use a highly integrated, end-to-end, and easy-to-use service [1]
- the mirrored data can be used in Power BI
- ⇐ since tables are all v-ordered delta tables [3]
- {benefit} the delta tables can then be used in every Fabric experience, allowing users to accelerate their journey into Fabric [1]
- {restriction} replication of views is currently not supported [3]
- {benefit} allows to write cross-database queries against the mirrored databases, warehouses, and the SQL analytics endpoints of Lakehouses in a single T-SQL query [1]
- landing zone in OneLake
- stores both the snapshot and change data [3]
- ⇐ improves performance when converting files into delta verti-parquet [3]
- {operation} enable the mirroring
- by creating a secure connection to the operational data source
- an entire database or individual tables can be replicated
- creates three items in the targeted workspace:
- the mirrored database
- a SQL analytics endpoint
- a Default semantic model
- the same source database can be mirrored multiple times
- ⇐ though usually not needed
- scenario: replicating data to different types of environments
- {operation} stopping the mirroring
- stops the replication in the source database, but a copy of the tables is kept in OneLake [3]
- {operation} restarting the mirroring
- results in all data being replicated from the start [3]
- {operation} remove a table from mirroring
- the table is no longer replicated and its data is deleted from OneLake [3]
- {operation} sharing
- users grant other users or groups of users access to a mirrored database without giving access to the workspace and the rest of its items [1]
- access is also granted to the SQL analytics endpoint and associated default semantic model [1]
- shared mirrored databases can be found through either
- Data Hub
- Shared with Me section in Microsoft Fabric
- triggers an initial replication
- {requirement} [licensing] requires Power BI Premium, Fabric Capacity, or Trial Capacity
- {feature} monitoring
- {benefit} allows to gain insights into mirroring operations and when the replica in Fabric OneLake was last refreshed [4]
- [Azure SQL Database]
- {restriction} doesn't support Azure SQL Database logical servers behind an AVN or private networking [2]
- {requirement} update the Azure SQL logical server firewall rules to Allow public network access [2]
- {requirement} enable the Allow Azure services option to connect to your Azure SQL Database logical server [2]
- {restriction}
- access through the Power BI Gateway or behind a firewall is unsupported [3]
- authentication
- SQL authentication with user name and password
- Microsoft Entra ID
- Service Principal
- {troubleshooting} check if the changes properly flow
- via sys.dm_change_feed_log_scan_sessions DMV
- {troubleshooting} check if there are any problems reported
- via sys.dm_change_feed_errors DMV
- {troubleshooting} check if the mirroring was properly enabled
- via sp_help_change_feed stored procedure
- {troubleshooting} disable mirroring
- via sp_change_feed_disable_db stored procedure
AVN - Azure Virtual Network
CDC - Change Data Capture
References:
[1] Microsoft Learn - Microsoft Fabric (2024) What is Mirroring in Fabric? (link)
[2] Microsoft Learn - Microsoft Fabric (2024) Mirroring Azure SQL Database [Preview] (link)
[3] Microsoft Learn - Microsoft Fabric (2024) Frequently asked questions for Mirroring Azure SQL Database in Microsoft Fabric [Preview] (link)
[4] Microsoft Fabric Updates Blog (2023) (link)
Resources:
[1] Tutorial: Configure Microsoft Fabric mirrored databases from Azure SQL Database [Preview] (link)
[2] Microsoft Fabric Updates Blog (2024) Announcing the Public Preview of Mirroring in Microsoft Fabric, by Charles Webb (link)
No comments:
Post a Comment