Database Mirroring in Microsoft Fabric: Replicating Azure SQL and Snowflake into OneLake

Introduction

Picture this: your organization's transactional data lives in Azure SQL Database, your analytics team uses Snowflake as a secondary warehouse, and your BI engineers keep writing increasingly creative hacks to join those two data sources together in Power BI. Every morning, someone kicks off a pipeline that bulk-copies millions of rows into a staging area, transformations run for two hours, and by the time the dashboard refreshes the data is already stale. You've probably lived some version of this story.

Database Mirroring in Microsoft Fabric is Microsoft's answer to this problem. Rather than orchestrating scheduled bulk loads, Mirroring creates a continuous, near-real-time replication stream from your source database directly into OneLake, landing data as Delta Parquet files. From there, every Fabric workload — Lakehouse SQL, Spark notebooks, a Fabric Warehouse, Power BI Direct Lake reports — can consume that data without any additional movement. The "one copy" philosophy that underpins OneLake's design becomes genuinely achievable because the data flows in once and stays there, always current, always queryable.

By the end of this lesson you will understand how Mirroring works under the hood, how to configure it for both Azure SQL Database and Snowflake, how to manage its operational lifecycle, and how to avoid the pitfalls that trap teams who set it up without thinking through the architecture. This is not a wizard-clicking tutorial — we're going to reason about the technology so you can make good decisions when requirements shift.

What you'll learn:

• How Fabric Mirroring captures changes from source databases using change-based replication and how it differs from traditional ETL pipelines
• Step-by-step configuration of Mirroring connections for Azure SQL Database (including network, authentication, and CDC requirements) and Snowflake
• How mirrored data is organized in OneLake as Delta tables and what that means for downstream consumers
• How to monitor replication lag, handle schema changes, and operationalize Mirroring for production workloads
• Architecture trade-offs: when Mirroring is the right tool and when a Dataflow Gen2 or data pipeline is actually a better fit

---

Prerequisites

Before diving in, you should be comfortable with:

• Navigating the Microsoft Fabric portal and understanding the workspace/capacity model. If you haven't stood up a Fabric capacity yet, start with Fabric Capacities and Workspaces.
• Basic familiarity with Azure SQL Database and Snowflake — connection strings, roles, and the concept of a database user with elevated permissions.
• A general understanding of what OneLake is and how it stores data as Delta tables. OneLake Explained is the prerequisite reading.
• An F2 or higher Fabric capacity (or a Fabric trial). Mirroring is not available on Power BI Premium P SKUs. Free trial capacities support Mirroring as of this writing.

---

What Mirroring Actually Is (and What It Isn't)

Before touching any configuration screen, let's build a mental model, because Mirroring is frequently confused with two other patterns: shortcuts and pipelines.

A shortcut (covered in depth separately for ADLS and S3 sources) is a pointer. When you query a shortcut, Fabric reaches across the network to the source system at query time. No data is copied into OneLake storage. Shortcuts are brilliant for object storage, but they don't work against live OLTP databases because you can't continuously query a production SQL Server or Snowflake instance for every Power BI report render.

A data pipeline or Dataflow Gen2 is a scheduled or triggered batch movement. You define the source, the destination, the frequency, and Fabric copies a snapshot on that schedule. The data age is bounded by the schedule interval — if you run hourly, your data can be up to an hour stale. This is perfectly fine for many workloads and remains the right tool when you need complex transformation logic during ingestion.

Mirroring sits in a third category: continuous, change-based replication with no user-managed scheduling. Here's what actually happens at the infrastructure level:

1. Initial snapshot: When you first enable Mirroring on a table, Fabric reads the current full state of that table and writes it into OneLake as Delta Parquet files. For large tables this can take minutes to hours depending on row count and column widths.

2. Change feed: After the snapshot completes, Fabric switches to incremental change capture. For Azure SQL Database, it uses Change Data Capture (CDC), a SQL Server feature that reads the transaction log and surfaces inserts, updates, and deletes as row-level change events. For Snowflake, Fabric uses Snowflake's Dynamic Tables or Streams infrastructure (depending on the version of the connector) to capture changes from Snowflake's internal change tracking mechanism.

3. Delta merge into OneLake: Change events are continuously read by a Fabric-managed replication process (running inside the Fabric backend, not your capacity compute), converted to Delta format, and merged into the OneLake Delta table. Inserts become new rows, updates are applied as upserts using the primary key, and deletes are either physically removed or soft-deleted depending on configuration.

4. Delta table availability: The resulting Delta table is immediately available to any Fabric workload via the Lakehouse SQL Analytics Endpoint, Spark, or Direct Lake semantic models. No additional ETL step is required.

The trade-off is control: you cannot inject transformation logic into the Mirroring stream. What lands in OneLake is a faithful structural replica of the source. Transformations belong downstream, in Spark notebooks, warehouse views, or Dataflow Gen2 jobs that read from the mirrored tables.

---

Enabling CDC on Azure SQL Database

Mirroring from Azure SQL Database requires Change Data Capture to be enabled. CDC is a SQL Server feature that causes the database engine to write change events to dedicated change tables in a hidden schema (cdc). If CDC isn't enabled, the Mirroring setup wizard in Fabric will tell you — and it will even offer to enable it for you, but only if your account has the right permissions. Let's do it manually so you understand what's happening.

Database-Level CDC

Connect to your Azure SQL Database with a login that has db_owner or sysadmin equivalent. Then:

-- Enable CDC at the database level
USE [YourDatabaseName];
GO

EXEC sys.sp_cdc_enable_db;
GO

-- Verify
SELECT name, is_cdc_enabled
FROM sys.databases
WHERE name = DB_NAME();

The is_cdc_enabled column should return 1. This enables the CDC infrastructure — the capture job, the cleanup job, and the cdc schema — but it doesn't start capturing any specific table yet.

Table-Level CDC

You need to enable CDC on each table you want to replicate. Let's say you're mirroring an orders table in the sales schema:

EXEC sys.sp_cdc_enable_table
    @source_schema = N'sales',
    @source_name   = N'orders',
    @role_name     = NULL,          -- NULL means no separate access role required
    @supports_net_changes = 1;      -- Enables the net changes function
GO

The @supports_net_changes = 1 parameter is important. It tells CDC to also maintain a "net changes" view that collapses multiple changes to the same row within a capture window into a single record — an insert followed by two updates is surfaced as one insert with the final state. Fabric's replication engine uses this for efficiency.

Creating a Dedicated Mirroring User

Never use your sa login or a db_owner account as the Mirroring service account. Create a least-privilege user:

-- Create a login (at server level)
CREATE LOGIN fabric_mirror WITH PASSWORD = 'use-a-strong-password-here';

-- In the target database
USE [YourDatabaseName];
GO

CREATE USER fabric_mirror FOR LOGIN fabric_mirror;

-- Required permissions
ALTER ROLE db_datareader ADD MEMBER fabric_mirror;

-- CDC-specific permission: read from the cdc schema change tables
GRANT SELECT ON SCHEMA::cdc TO fabric_mirror;

-- Permission to execute CDC functions
GRANT EXECUTE ON SCHEMA::cdc TO fabric_mirror;

Network Connectivity

Azure SQL Database has a firewall that blocks connections by default. Fabric's Mirroring backend needs to reach your SQL server. You have three options:

1. "Allow Azure services" firewall rule: In the Azure portal, go to your SQL server's networking settings and enable "Allow Azure services and resources to access this server." This is the simplest option and works because Fabric's backend runs within Azure.

2. On-premises data gateway: If your SQL server is not Azure SQL but rather SQL Server on a private network or in an Azure VNet with no public endpoint, you can route Mirroring through an on-premises data gateway. This is the same gateway used by Power BI for private network connectivity.

3. Azure Private Link + VNet integration: For enterprise environments where enabling "Allow Azure services" is not acceptable from a security standpoint, you can use Fabric's managed private endpoint feature (available on F64+) to create a private network path. This is more complex to configure but provides the strongest network isolation.

---

Configuring Mirroring from Azure SQL Database in Fabric

With CDC enabled and your user created, you're ready to configure the mirror. In Fabric, navigate to your target workspace and select New item. Under the Data Engineering or Data Warehouse section, find Mirrored database (the exact category name may shift as Fabric evolves, but the item is called "Mirrored database" or similar).

The wizard will ask you to create a new connection or select an existing one. For a new connection:

• Connection type: Azure SQL Database
• Server: your fully qualified server name (yourserver.database.windows.net)
• Database: the specific database name
• Authentication: SQL authentication (username/password) or Entra ID (recommended)
• Credentials: the fabric_mirror user credentials you created

Once the connection is established, Fabric will test connectivity and validate that CDC is enabled. If CDC isn't enabled at the database level, you'll see a warning with an option to enable it automatically (requires that your connection credential has db_owner or higher). If individual tables don't have CDC enabled, Fabric will offer to enable them during table selection.

Selecting Tables

After connection validation, you'll see a list of tables in the database. Check the tables you want to replicate. A few considerations:

• Start selective. Don't mirror every table in a 300-table OLTP database just because you can. Mirror the tables your analytics workloads actually need. Mirroring creates ongoing replication overhead and storage costs.
• Large reference tables with infrequent changes are excellent candidates — dimension tables, product catalogs, lookup tables.
• High-volume fact tables are the primary value driver — orders, transactions, events — but also the ones where CDC overhead is most noticeable on the source.
• Tables without primary keys will replicate but updates and deletes may not be applied correctly. Fabric uses the primary key for upsert logic. A table with no PK will receive append-only replication — all change events land as new rows, which will cause data duplication. Either add a PK to the source table or be aware of this limitation.

After selecting tables, give the mirrored database item a name. Fabric will create a new item in your workspace that represents the mirrored database. Under the hood, this item owns a folder structure in OneLake.

What Gets Created in OneLake

Once Mirroring starts, navigate to your workspace's OneLake storage (you can do this through the Lakehouse explorer or via the OneLake file explorer). You'll see a folder structure like:

<workspace>/
  <mirrored-database-name>/
    Tables/
      sales.orders/
        _delta_log/
          00000000000000000000.json
          00000000000000000001.json
          ...
        part-00000-<guid>.snappy.parquet
        part-00001-<guid>.snappy.parquet

The table name preserves the source schema prefix (sales.orders becomes the table name in the Delta structure). Each table is a full Delta table — it has a _delta_log for ACID transaction history and one or more Parquet files.

---

Configuring Mirroring from Snowflake

Mirroring from Snowflake is architecturally similar but uses a different change capture mechanism and has different authentication and network requirements.

Snowflake Prerequisites

Snowflake Mirroring requires:

1. Change Tracking enabled on source tables: Snowflake's native change tracking feature must be enabled at the table level. This is simpler than SQL Server CDC — it's a single ALTER statement per table.

2. A dedicated service account with appropriate roles and privileges.

3. Network policy allowing Fabric's IP ranges, or a Snowflake Private Link configuration.

Let's handle each.

Enabling Change Tracking in Snowflake

-- Enable change tracking on a specific table
ALTER TABLE analytics.public.orders
SET CHANGE_TRACKING = TRUE;

-- Verify
SHOW TABLES LIKE 'orders' IN SCHEMA analytics.public;
-- Look for the CHANGE_TRACKING column in the output showing TRUE

Change tracking in Snowflake adds overhead comparable to CDC in SQL Server — Snowflake must maintain metadata about row versions. The storage cost is modest but the performance impact on INSERT/UPDATE/DELETE operations is worth measuring on your specific workload.

You also need to set a DATA_RETENTION_TIME_IN_DAYS on the tables being mirrored. Snowflake's change tracking is built on Time Travel, and if Fabric's replication falls behind by more than the retention period, it will need to re-snapshot the table entirely. For most production scenarios, set this to at least 3 days:

ALTER TABLE analytics.public.orders
SET DATA_RETENTION_TIME_IN_DAYS = 3;

Creating a Snowflake Service Account for Mirroring

-- As ACCOUNTADMIN or SECURITYADMIN
CREATE ROLE fabric_mirror_role;

-- Grant usage on the warehouse, database, schema
GRANT USAGE ON WAREHOUSE your_warehouse TO ROLE fabric_mirror_role;
GRANT USAGE ON DATABASE analytics TO ROLE fabric_mirror_role;
GRANT USAGE ON SCHEMA analytics.public TO ROLE fabric_mirror_role;

-- Grant read access and change tracking access on each table
GRANT SELECT ON TABLE analytics.public.orders TO ROLE fabric_mirror_role;
GRANT SELECT ON TABLE analytics.public.customers TO ROLE fabric_mirror_role;

-- If you want to grant on all current and future tables in the schema:
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.public TO ROLE fabric_mirror_role;
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics.public TO ROLE fabric_mirror_role;

-- Create the service user
CREATE USER fabric_mirror_user
    PASSWORD = 'use-a-strong-password'
    DEFAULT_ROLE = fabric_mirror_role
    DEFAULT_WAREHOUSE = your_warehouse;

GRANT ROLE fabric_mirror_role TO USER fabric_mirror_user;

Configuring the Snowflake Mirror in Fabric

Back in Fabric, create another Mirrored database item and select Snowflake as the source type. You'll need:

• Account identifier: the Snowflake account identifier in the format orgname-accountname or the older account.region.cloud format
• Warehouse: the virtual warehouse to use for the initial snapshot query (choose an appropriately sized warehouse — XS for small tables, larger for multi-billion row tables)
• Database and schema: the Snowflake database and schema containing your tables
• Authentication: username/password or key-pair

The table selection experience is identical to the Azure SQL flow. Select your tables, confirm, and Fabric begins the initial snapshot.

One important difference from Azure SQL Mirroring: Snowflake snapshots use Snowflake compute (your Snowflake warehouse) for the initial read. Large initial snapshots will consume Snowflake credits. Subsequent incremental reads are lighter but still run through your Snowflake warehouse. This is a real cost consideration — Mirroring is not entirely "free" on the Snowflake side.

---

Understanding the Mirrored Database Item and Its SQL Endpoint

Once replication is running, the Mirrored database item in Fabric gives you a SQL Analytics Endpoint — the same technology used by Fabric Lakehouses. This is a serverless T-SQL query engine that reads directly from the Delta files in OneLake.

From the Mirrored database item, you can:

• Run T-SQL queries against the mirrored tables in the built-in query editor
• Copy the SQL connection string and connect from SSMS, Azure Data Studio, or any ODBC-compatible tool
• Create views on top of the mirrored tables (views live in the mirrored database item, not in OneLake itself)
• Create a Power BI semantic model directly from the mirrored database item

What you cannot do from the mirrored database SQL endpoint is write data back. It's read-only by design — the only write path is the Mirroring replication process.

Cross-Database Queries

One of the most powerful patterns is combining mirrored data with data in a Fabric Warehouse or Lakehouse using three-part name cross-database queries. If you've read the Fabric Warehouse T-SQL article, you'll know Fabric supports querying across items within the same workspace using three-part names.

For example, joining your mirrored Azure SQL orders with a transformed customer dimension in a Fabric Warehouse:

-- In Fabric Warehouse query editor or SQL endpoint
SELECT
    o.order_id,
    o.order_date,
    o.amount,
    c.customer_name,
    c.segment,
    c.lifetime_value
FROM [your-mirrored-db].[sales].[orders] AS o
INNER JOIN [your-warehouse].[gold].[dim_customer] AS c
    ON o.customer_id = c.customer_id
WHERE o.order_date >= DATEADD(day, -30, GETDATE())
  AND o.status = 'completed';

This query reads from two different items within the same Fabric workspace. The SQL engine handles the federation. No data movement, no staging tables, no pipeline required for this join.

---

Monitoring Replication and Understanding Lag

Mirroring is not instantaneous. There is always some lag between a change committed in the source database and the corresponding change appearing in OneLake. Understanding the components of that lag helps you set appropriate expectations and troubleshoot when things drift.

Sources of Replication Lag

For Azure SQL Database:

1. CDC capture latency: SQL Server's CDC capture job runs continuously but with a configurable polling interval. The default is 5 seconds. Changes committed to the source table appear in the CDC change tables within this window.
2. Fabric read polling: Fabric's Mirroring backend polls the CDC change tables on a schedule. Empirically, this adds another 10–60 seconds of delay under normal load.
3. Delta write and commit: The changes are converted to Delta format and committed to OneLake. For small change batches this is fast (seconds). For large batches after a period of accumulated changes, this can take longer.

Total typical lag for Azure SQL Database: 30 seconds to 5 minutes under normal operating conditions.

For Snowflake:

1. Stream polling: Fabric polls Snowflake's change stream. Snowflake processes changes in micro-batches.
2. Snowflake warehouse startup: If the Snowflake warehouse auto-suspended, it needs to resume before the read can execute. This adds 10–60 seconds.
3. Delta write and commit: Same as above.

Total typical lag for Snowflake: 1–15 minutes under normal operating conditions, with warehouse cold-start being the primary variable.

Monitoring in the Fabric Portal

In the Mirrored database item, there's a Monitor tab that shows:

• Replication status per table: Running, Initializing, Stopping, Error
• Last replicated time per table: the timestamp of the most recent change batch successfully committed to OneLake
• Rows replicated: cumulative count of change events processed

The lag you care about is the delta between "last replicated time" and current time. If this grows consistently over minutes or hours rather than seconds, something is wrong. Common causes:

• Source CDC retention exhausted: If Fabric falls behind and the CDC change tables on the source are pruned before Fabric reads them, Mirroring must re-snapshot the table. You'll see the status flip briefly to "Initializing" on that table.
• Network connectivity issues: Transient network errors between Fabric's backend and the source database. Usually self-healing within minutes.
• Schema changes on the source: More on this below.

---

Handling Schema Changes

Schema evolution is one of the hardest operational challenges in any replication system, and Fabric Mirroring is no exception.

What Happens When You Add a Column

If you add a nullable column to a source table in Azure SQL Database:

-- On the source database
ALTER TABLE sales.orders
ADD promotional_code NVARCHAR(50) NULL;

Fabric Mirroring will detect this schema change and evolve the Delta table schema in OneLake to add the new column. Existing rows will show NULL for that column. New rows will replicate the value from the source. This scenario works well.

What Happens When You Drop a Column

Dropping a column is more disruptive. Fabric will stop replication on the affected table and flag it in an error state. You must manually intervene: either stop Mirroring for that table and restart it (which triggers a re-snapshot) or, if your use case allows, avoid dropping columns on mirrored tables.

What Happens When You Rename a Table

Table renames break Mirroring — from Fabric's perspective, the old table disappeared (triggering an error) and a new table with a different name appeared (not in the mirroring configuration). You need to update the Mirroring configuration to remove the old table and add the new one.

What Happens When You Rename a Column

Similar to renaming a table: Fabric sees the old column as deleted and the new column as added. It will add the new column and leave the old column with NULLs going forward. Depending on your tolerance for this, you may need to re-snapshot the table.

The safest rule of thumb: treat your source schema as a contract with Fabric Mirroring. Column additions are safe. Everything else — drops, renames, type changes — requires planning.

---

Downstream Consumption Patterns

Mirrored data in OneLake is most valuable when downstream workloads are designed with the continuous replication model in mind. Let's look at the main patterns.

Pattern 1: Direct Lake Power BI Reporting

This is the flagship use case. Because mirrored tables are Delta tables in OneLake, they're eligible for Direct Lake mode in Power BI. A Direct Lake semantic model can serve reports without importing data — it reads column data directly from the Parquet files in OneLake at query time, framing results in memory.

The combination is compelling: data replicates continuously from your operational database, and Power BI always reflects the most recently replicated state without any scheduled refresh. If your replication lag is 2 minutes and your Power BI report opens a fresh framing every time a user interacts with a slicer, you're effectively serving near-real-time operational intelligence from Power BI.

To wire this up, create a new semantic model from the Mirrored database item (using the "New semantic model" option in the Fabric portal), select the mirrored tables you want to include, and publish. The semantic model will use Direct Lake by default.

Pattern 2: Medallion Architecture with Mirroring as Bronze

If you're implementing a medallion architecture, Mirroring slots naturally into the bronze layer. The mirrored tables are your raw, unmodified operational data — the faithful replica of the source. Silver and gold transformations then run against bronze using Spark notebooks or Dataflow Gen2.

The advantage over traditional bronze pipelines is that bronze is now always current. You don't batch-load bronze on a schedule; it arrives continuously. Your silver transformation job still runs on a schedule (every 15 minutes, hourly, whatever your SLA demands), but it's always reading fresh bronze data.

A typical Spark notebook transforming mirrored orders into a silver enriched fact table might look like:

from pyspark.sql import functions as F
from delta.tables import DeltaTable

# Read from the mirrored bronze table (as a Delta table in the same workspace)
bronze_orders = spark.read.format("delta").load(
    "abfss://your-workspace@onelake.dfs.fabric.microsoft.com/"
    "your-mirrored-db.MirroredDatabase/Tables/sales.orders"
)

# Apply silver transformations
silver_orders = (
    bronze_orders
    .filter(F.col("status").isin(["completed", "refunded"]))
    .withColumn("order_month", F.date_trunc("month", F.col("order_date")))
    .withColumn("net_amount", 
        F.when(F.col("status") == "refunded", -F.col("amount"))
         .otherwise(F.col("amount"))
    )
    .withColumn("is_promotional", F.col("promotional_code").isNotNull())
    .select(
        "order_id", "customer_id", "order_date", "order_month",
        "amount", "net_amount", "status", "is_promotional"
    )
)

# Write to silver Lakehouse
(silver_orders
 .write
 .format("delta")
 .mode("overwrite")  # or merge for incremental logic
 .option("overwriteSchema", "true")
 .saveAsTable("silver_lakehouse.fact_orders_silver")
)

Pattern 3: Combined Mirroring + Warehouse for Analytics

For teams that want SQL-centric analytics with curated schema management, the pattern is:

1. Mirroring brings raw operational data into OneLake continuously
2. A Fabric Warehouse applies transformations, business logic, and modeling via T-SQL views and stored procedures
3. Power BI connects to the Warehouse (or mirrored database directly)

This is architecturally equivalent to the medallion pattern but implemented entirely in SQL. The Warehouse's views reference the mirrored tables using cross-database queries, meaning the warehouse tier adds logic without copying data.

---

When Mirroring Is the Wrong Tool

Mirroring is genuinely excellent for its target use case — near-real-time replication of stable OLTP schemas. But it's the wrong tool in several situations:

When you need transformation during ingestion. Mirroring replicates structure faithfully. If your source has denormalized data that needs splitting, encrypted columns that need masking, or a schema that needs significant reshaping before it's useful downstream, Mirroring gives you the wrong data in the wrong shape. Use Dataflow Gen2 or a pipeline with transformation logic.

When the source doesn't support CDC or change tracking. Legacy databases, flat files, REST APIs — none of these are candidates for Mirroring. Use pipelines for these sources.

When your source is already cloud object storage. If your data lives in ADLS Gen2, Amazon S3, or Azure Blob, use OneLake shortcuts or pipelines. Mirroring is for relational database sources.

When you need guaranteed exactly-once delivery with strict ordering. Mirroring offers at-least-once semantics. In extremely high-throughput scenarios with concurrent transactions, you may see occasional duplicate delivery of change events. The Delta table merge process handles this gracefully for upserts, but if you're building an event sourcing architecture where every individual event matters, Mirroring's micro-batch approximation may not fit.

When source schema changes frequently. If the source team drops and recreates tables, changes column types, or renames columns regularly, Mirroring will require constant babysitting. Agree on schema stability as a prerequisite before committing to Mirroring.

---

Hands-On Exercise

This exercise guides you through configuring Mirroring for an Azure SQL Database table and querying the result. You'll need an F2+ Fabric capacity and an Azure SQL Database you can configure.

Step 1: Prepare the Source Database

Using SSMS or Azure Data Studio, connect to your Azure SQL Database and run:

-- Create a sample orders table if you don't have one
CREATE TABLE dbo.mirror_orders (
    order_id     INT           NOT NULL PRIMARY KEY,
    customer_id  INT           NOT NULL,
    order_date   DATE          NOT NULL,
    amount       DECIMAL(10,2) NOT NULL,
    status       NVARCHAR(20)  NOT NULL,
    created_at   DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME()
);

-- Enable CDC on the database
EXEC sys.sp_cdc_enable_db;

-- Enable CDC on the table
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'mirror_orders',
    @role_name     = NULL,
    @supports_net_changes = 1;

-- Seed some initial rows
INSERT INTO dbo.mirror_orders (order_id, customer_id, order_date, amount, status)
VALUES
    (1001, 5001, '2024-01-15', 249.99, 'completed'),
    (1002, 5002, '2024-01-16', 89.50,  'completed'),
    (1003, 5001, '2024-01-17', 429.00, 'processing'),
    (1004, 5003, '2024-01-18', 15.00,  'completed'),
    (1005, 5004, '2024-01-19', 880.00, 'pending');

Also create your Mirroring service account:

CREATE LOGIN fabric_mirror_lab WITH PASSWORD = 'P@ssw0rd#Lab123!';
CREATE USER fabric_mirror_lab FOR LOGIN fabric_mirror_lab;
ALTER ROLE db_datareader ADD MEMBER fabric_mirror_lab;
GRANT SELECT ON SCHEMA::cdc TO fabric_mirror_lab;
GRANT EXECUTE ON SCHEMA::cdc TO fabric_mirror_lab;

Ensure "Allow Azure services" is enabled in your SQL server's firewall settings in the Azure portal.

Step 2: Create the Mirrored Database in Fabric

In your Fabric workspace, click New item, search for Mirrored database, and select it. In the setup dialog:

• Choose Azure SQL Database
• Create a new connection using your server name, database name, and the fabric_mirror_lab credentials
• Wait for Fabric to validate connectivity and CDC status
• Select the mirror_orders table from the list
• Name the item Lab_Mirror_AzureSQL and confirm

Fabric will start the initial snapshot. In the Monitor tab of the new item, watch for the status of dbo.mirror_orders to transition from Initializing to Running.

Step 3: Query the Mirrored Data

Once the status shows Running and a "Last replicated time" is visible, open the SQL endpoint of the Lab_Mirror_AzureSQL item. You can do this by clicking the item name to open it, then selecting the SQL endpoint connection string from the toolbar.

In the query editor:

-- Basic query of mirrored data
SELECT * FROM [dbo].[mirror_orders]
ORDER BY order_id;

-- Aggregate that would be common in a report
SELECT
    status,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
FROM [dbo].[mirror_orders]
GROUP BY status
ORDER BY total_revenue DESC;

Step 4: Observe Incremental Replication

Go back to your source SQL database and make some changes:

-- Insert a new order
INSERT INTO dbo.mirror_orders (order_id, customer_id, order_date, amount, status)
VALUES (1006, 5005, CAST(GETDATE() AS DATE), 320.00, 'processing');

-- Update an existing order
UPDATE dbo.mirror_orders
SET status = 'completed'
WHERE order_id = 1003;

-- Delete a row
DELETE FROM dbo.mirror_orders
WHERE order_id = 1004;

Wait 60–120 seconds, then re-query the mirrored table in Fabric. You should see:

• order_id = 1006 has appeared
• order_id = 1003 now shows status = 'completed'
• order_id = 1004 is gone

This is the live replication cycle working end-to-end.

Step 5: Inspect the Delta Table in OneLake

Open the OneLake File Explorer (or navigate via the Fabric portal to the workspace's OneLake storage). Find the mirrored database folder and navigate into Tables/dbo.mirror_orders/_delta_log/. Open the first few .json transaction log files and observe the Delta log entries — you'll see the initial snapshot recorded as a single transaction, then each change batch as subsequent commits.

---

Common Mistakes & Troubleshooting

Mistake 1: Selecting all tables without evaluating CDC impact

Teams who mirror 50+ tables simultaneously from a production OLTP database often see noticeable CPU and I/O increases on the source. Always pilot with 3–5 tables, measure source resource utilization over 48 hours, then scale up incrementally.

Mistake 2: Forgetting that schema changes stop replication

The most common production incident: a developer alters a table schema on the source (drops a column, renames a column), and the Mirroring status for that table flips to Error. The team doesn't notice because the Monitor tab isn't being watched. Set up alerts on the workspace-level activity or build a monitoring notebook that queries the replication status and sends alerts when lag exceeds a threshold.

Mistake 3: Not running OPTIMIZE on high-churn mirrored tables

High-write tables accumulate Delta log files rapidly. Without periodic OPTIMIZE compaction, Direct Lake models begin falling back to DirectQuery mode (Fabric's automatic fallback when Delta framing takes too long), defeating the point of using Direct Lake. Schedule a nightly Spark notebook:

# Run in a scheduled Spark notebook
from delta.tables import DeltaTable

high_churn_tables = [
    "abfss://workspace@onelake.dfs.fabric.microsoft.com/mirror.MirroredDatabase/Tables/dbo.mirror_orders",
    "abfss://workspace@onelake.dfs.fabric.microsoft.com/mirror.MirroredDatabase/Tables/dbo.transactions",
]

for table_path in high_churn_tables:
    dt = DeltaTable.forPath(spark, table_path)
    dt.optimize().executeCompaction()
    print(f"Optimized: {table_path}")

Mistake 4: Confusing replication lag with data quality

Some teams see a 3-minute lag and assume Mirroring is broken. Build a simple validation query that compares row counts between source and mirrored tables to distinguish lag (expected, temporary delay) from data quality issues (missing rows, duplicate rows, wrong values):

-- Run on source
SELECT COUNT(*) AS source_count, MAX(created_at) AS latest_source_ts
FROM dbo.mirror_orders;

-- Run on Fabric SQL endpoint
SELECT COUNT(*) AS mirror_count, MAX(created_at) AS latest_mirror_ts
FROM [dbo].[mirror_orders];

If counts match and timestamps are close, replication is healthy. If counts diverge and stay diverged over 30+ minutes, investigate.

Mistake 5: Using Mirroring for event streams

Some teams try to mirror a high-throughput event logging table (10,000+ inserts per second) expecting near-real-time analytics. Mirroring's micro-batch model cannot handle this rate without significant lag accumulation. For true event streams, use Fabric Eventstreams and Eventhouses instead.

Mistake 6: Ignoring Snowflake warehouse auto-suspend settings

If your Snowflake warehouse auto-suspends after 60 seconds (common for cost management), every Mirroring polling cycle that falls in a quiet window will incur a cold-start delay. On a workload where changes arrive every few minutes, this means your effective replication lag is cold-start time (30–60 seconds) + query time rather than just query time. Consider setting a longer auto-suspend (5–10 minutes) for the warehouse designated to Mirroring, or creating a dedicated XS warehouse for Mirroring with a 5-minute auto-suspend.

---

Summary & Next Steps

Database Mirroring in Microsoft Fabric fundamentally changes the economics and complexity of keeping operational data available for analytics. Instead of engineering pipeline schedules, managing bulk copy windows, and accepting data that's hours old by design, Mirroring gives you a continuously updated replica of your Azure SQL Database or Snowflake tables in OneLake, available to every Fabric workload without additional data movement.

The technology is most valuable when:

• You need sub-5-minute latency between operational events and analytical visibility
• Your source schema is relatively stable (column additions are fine; drops and renames are operational events)
• Your downstream consumption includes Direct Lake Power BI reports, Spark notebooks, or cross-database T-SQL queries in a Fabric Warehouse
• You want to eliminate scheduled bulk ETL and its associated maintenance burden

The operational commitments that come with Mirroring are real: you need to monitor replication status, handle schema change events, run periodic Delta optimization on high-churn tables, and understand that Mirroring is not a fire-and-forget system. It rewards teams who invest in observability.

Next steps to deepen your understanding:

• Build a medallion architecture on top of your mirrored bronze tables, adding silver and gold transformations via Spark notebooks.
• Explore Direct Lake mode to understand how Power BI connects directly to your mirrored Delta tables for near-real-time reporting without scheduled refreshes.
• For workloads where sub-second latency matters (IoT telemetry, clickstream, payment events), complement Mirroring with Fabric Eventstreams and Eventhouses for the high-frequency path.
• Review the Fabric Lakehouse vs Warehouse decision guide to determine whether your transformation and serving layer should be a Lakehouse, a Warehouse, or both, given the types of queries your analytics teams need to run against mirrored data.