How to Architect Real-Time Replication with Mirrored Azure SQL Databases

Why this matters: Mirrored Azure SQL databases offer a low-latency path to bring transactional data into OneLake without writing custom ETL pipelines. Understanding the underlying replication mechanics is essential to avoid unexpected data staleness or capacity bottlenecks in production environments.

In a traditional ETL-heavy architecture, moving data from Azure SQL Database to an analytics platform requires building, scheduling, and monitoring pipelines using tools like Azure Data Factory or custom Spark jobs. This approach incurs compute overhead and inevitably introduces a "refresh lag" that makes real-time analytics difficult.

-- The "Old Way": Manual pipeline configuration
EXEC sp_add_jobstep @job_name = 'Load_Sales_Data', 
    @command = 'EXEC dbo.usp_ExtractToBlobStorage';
-- Then orchestrate a copy activity to ADLS Gen2
-- Then trigger a notebook to write to Delta tables

Mirrored Azure SQL databases in Microsoft Fabric change this pattern by abstracting the ingestion layer. Instead of orchestrating movement, you enable a continuous replication process that streams data directly into OneLake in Delta format. The database becomes a source that "pushes" changes to the Fabric workspace, effectively eliminating the need for custom orchestration logic.

Prerequisites

To implement mirroring, ensure your environment meets the following baseline requirements:

• Azure SQL Database: Must be a single database (Elastic Pools are not supported for the source) with a Premium or Business Critical service tier.
• Fabric Capacity: A Fabric F-SKU or Trial capacity is required in the target workspace.
• Authentication: The identity used to configure the mirror must have Contributor permissions on the Azure SQL resource and the Fabric workspace.
• Network: Public network access must be enabled on the Azure SQL instance, or the instance must be reachable via the Fabric managed identity if using private links (check official Microsoft Learn documentation for specific networking configurations).

Photo by Alfred on Unsplash

Step 1: Enabling the Mirroring Connection

The mirroring process begins in the Fabric portal. Navigate to your workspace, select New, and choose Mirrored Azure SQL database.

When prompted, you will enter the server name and database name. Fabric will attempt to establish a connection using the credentials you provide. Ensure that the Azure SQL firewall settings allow "Azure services and resources to access this server."

{
  "datasource": "sql-server-name.database.windows.net",
  "database": "production-db-name",
  "authType": "ServicePrincipal",
  "tenantId": "<your-tenant-id>"
}

Once the connection is validated, Fabric initializes the metadata and begins the synchronization process.

Step 2: Selecting Objects for Replication

After the connection is established, the "Mirroring configuration" interface appears. This is where you decide the scope of your replication. You can choose to replicate the entire database or specific schemas and tables.

Be mindful of the replication load. While mirroring is designed to be efficient, selecting hundreds of tables—especially those with high update volume—can impact the performance of the source Azure SQL database.

Step 3: Monitoring Initial Load and Latency

Once you trigger the mirroring, Fabric performs an initial snapshot followed by continuous incremental updates. You can monitor this in the Mirroring status tab within the Fabric item view.

The replication process is asynchronous. Data changes in the source are captured from the transaction logs and applied to the Delta tables in OneLake. Because this relies on the source database's transaction log throughput, latency is generally measured in seconds, but it is not instantaneous.

You can verify the status programmatically by querying the sys.dm_db_mirroring_status (or the equivalent Fabric monitoring view) to ensure the replication state is Active.

Step 4: Accessing Data via Direct Lake

The primary benefit of mirroring is that the resulting Delta tables in OneLake are immediately available for consumption. Because the data is stored in the Delta format, you can leverage Direct Lake mode in your Power BI semantic models.

Direct Lake bypasses the need to import data into memory or use DirectQuery, providing the performance of Import mode with the real-time nature of DirectQuery. To use this, create a new semantic model in your workspace, select the mirrored tables, and ensure the storage mode is set to Direct Lake.

-- Example of a measure that benefits from Direct Lake performance
Total Sales = SUM(FactSales[Amount])

Photo by Airalo on Unsplash

Common pitfalls

• Unsupported Data Types: Not all SQL data types are supported for mirroring. Review the official Microsoft Learn documentation for the latest list of unsupported types (e.g., XML, FILESTREAM, or specific CLR types). If your source tables contain these, the mirroring process for those specific tables will fail.
• Source Performance: Although mirroring is designed to be low-impact, it still consumes transaction log resources. On heavily loaded transactional systems, monitor the log_reuse_wait_desc in sys.databases to ensure the replication process isn't causing log growth issues.
• Schema Changes: Automatic handling of DDL (Data Definition Language) changes is a work in progress. While simple schema additions might be handled, more complex changes (like dropping a column or changing a primary key) may require you to pause and resume mirroring or reset the connection entirely.
• Capacity Throttling: If your Fabric capacity reaches its limit, the replication process may be throttled, leading to increased latency. Keep an eye on the Fabric Capacity Metrics app to distinguish between query workload spikes and background ingestion spikes.

Conclusion

Mirrored Azure SQL databases represent a significant shift toward a "zero-ETL" analytics architecture. By treating the transaction log as the source of truth for downstream analytics, you reduce the complexity of your data pipeline and improve the freshness of your reporting.

Try it: Select a non-critical development database in Azure SQL, enable mirroring in a Fabric workspace, and compare the latency of a report connected via Direct Lake against a traditional Power BI Import model.

---

Tags: #microsoftfabric #azure #sql #dataengineering

Cover photo by Rick Rothenberg on Unsplash.

---

This article was drafted with AI assistance and is intended for technical review and fact-checking before publication.