Microsoft Fabric Updates Blog

Announcing Mirroring Azure SQL Database in Fabric for Public Preview

In the era of digital transformation, advanced analytics and an AI driven world, data has emerged as the new oil, powering businesses, and driving decision-making. But what good is this oil if it’s not refined and ready for use when needed? Moreover, managing, and ingesting data into a central platform for analytics and AI is costly and cumbersome process. This is where the importance of near-real-time data replication comes into play. It’s not just about having data; it’s about having the right data at the right time. To address these challenges, we launched Mirroring in Microsoft Fabric at Ignite’23 for private preview.

Today, we are excited to announce the public preview of Mirroring Azure SQL Database, Azure Cosmos Database and Snowflake data sources in Fabric, a new, simple, and frictionless way to replicate a snapshot of these source database in Fabric OneLake in Delta tables that keeps the data in sync in near-real time. The key benefits that Mirroring databases in Fabric enables are:

  1. Reduced total cost of ownership with zero compute to replicate along with generous amounts (terabytes) of storage based on the capacity size.
  2. Zero code with zero ETL
  3. Faster time to operational data, information to derive insights.

This blog will explore the importance of Mirroring Azure SQL database in Fabric, discuss its main features and how it can transform your data strategy.

How does Mirroring Azure SQL Database in Fabric work?

Mirroring Azure SQL Database in Fabric ensures that your source transactional SQL database is always up to date and available in the Fabric OneLake, providing a solid foundation for reporting, advanced analytics, AI, and data science. There is no complex setup or ETL for Mirroring. You setup the mirror from Fabric Data Warehousing experience by providing the Azure SQL server and database connection details, provide selections on what needs mirrored into Fabric, either all data or user selected eligible mirrored tables. And, just like that mirroring is ready to go.

A diagram of a software company

Description automatically generated with medium confidence

Mirroring Azure SQL database creates an initial snapshot in Fabric OneLake after which data is kept in sync in near-real time with every transaction when a new table is created/dropped, or data gets updated.

Key features

Mirroring for Azure SQL Database is built on the SQL’s Change Data Capture (CDC) stack optimized for lake-centric architecture. CDC stores changes locally in the database whereas Mirroring reads data from the harvested database transaction log and publishes the change data to OneLake storage. This change data is transformed into appropriate delta tables landing into Fabric OneLake. Moreover, DDL’s like add/drop column, alter table column <<datatype>>, drop table, rename table and rename column are also supported on actively mirrored tables.

As a SQL database administrator or user, you can also check the status of Mirroring by using these public stored procedure and dynamic management views:

  1. To confirm if Mirroring configuration of the Azure SQL database is enabled correctly, execute the following public stored procedure. The key columns to look for here are the “table_name” and “state”. Any value of “state” column besides “4” indicates a potential problem.
    • exec sp_help_change_feed;
  2. If you’re experiencing mirroring problems, perform the following database level checks using Dynamic Management Views (DMVs) if data changes flow properly:
    • SELECT * FROM sys.dm_change_feed_log_scan_sessions;
  3. If the DMV above doesn’t show any progress on processing incremental changes, execute the below query to check if there are any problems reported:
    • SELECT * FROM sys.dm_change_feed_errors;

The Mirrored data can also be actively monitored from Fabric providing more insights into mirroring operations and when was mirrored data last refreshed.

From here on, the mirrored data in the delta format is ready for immediate consumption across all Fabric experiences and features like Power BI with new Direct Lake mode, Data Warehouse, Data Engineering, Lakehouse, KQL Database, Notebooks and co-pilots work instantly.

Power BI Direct Lake mode

Direct Lake mode is a fast path to load the data from the lake with groundbreaking semantic model capability for analyzing very large data volumes in Power BI. As Direct Lake mode also supports reading Delta tables right from OneLake, the Mirrored SQL database is Power BI ready along with Copilot.

A screenshot of a computer

Description automatically generated

Cross-join Mirrored SQL Databases, Lakehouse’s, Warehouses

Data across any mirrored database (either Azure SQL DB, Azure Cosmos DB or Snowflake) can be cross joined as well enabling querying across any database, warehouse or lakehouse (either as a shortcut to AWS S3 or ADLS Gen 2 etc.)

A screenshot of a computer

Description automatically generated

Explore Data Science and Data engineering insights

Data scientists and data engineers can work with the mirrored SQL data that are created as shortcuts in Lakehouse.

A screenshot of a computer

Description automatically generated

Summary & get started.

To summarize, Mirroring Azure SQL Database in Fabric plays a crucial role in enabling analytics and driving insights from data by:

  1. Timeliness of Insights: Ensures that the most recent data is available for analysis. This allows businesses to make decisions based on the most current situation, rather than relying on outdated information.
  2. Improved Accuracy: The risk of discrepancies between the source and the replicated data is significantly reduced leading to more accurate analytics and reliable insights.
  3. Predictive Analytics and AI: Essential for predictive analytics and AI models that require the most recent data to make accurate predictions and decisions.

To get started and learn more about Mirroring Azure SQL in Fabric, its pre-requisites, setup, FAQ’s, current limitations, and tutorial, click here to read all about it. We hope you enjoy using Mirroring Azure SQL Database in Fabric and we look forward to hearing your feedback and questions. Please stay tuned for more updates and new features coming soon.

Relaterade blogginlägg

Announcing Mirroring Azure SQL Database in Fabric for Public Preview

oktober 7, 2024 från Alex Lin

Introducing Managed VNet Support for Fabric Eventstream! By creating a Fabric’s Managed Private Endpoint, you can now securely connect Eventstream to your Azure services, such as Azure Event Hubs or IoT Hub, within a private network or behind a firewall. This integration ensures your data is securely transmitted over a private network, enabling you to … Continue reading “Secure Data Streaming with Managed Private Endpoints in Eventstream (Preview)”

oktober 4, 2024 från Jason Himmelstein

We had an incredible time in our host city of Stockholm for FabCon Europe! 3,300 attendees joined us from our international community, and it was wonderful to meet so many of you in person. Throughout the week of FabCon Europe, our teams published a wealth of valuable content, and we want to ensure you have … Continue reading “Fabric Community Conference Europe Recap”