Fabric Mirroring for SQL Server: The On-Premises Path Into the Medallion Model

Fabric Mirroring for SQL Server is generally available and answers a specific question directly: how does an on-premises SQL Server database get into the Bronze layer of a Fabric medallion architecture without a custom pipeline, a containerized Spark job, or a second platform like Snowflake in the middle. Mirroring replicates continuously, lands data as Delta tables in OneLake, and does not require writing or scheduling any ingestion code for the tables it covers.

It is not, however, a universal replacement for a real ingestion pipeline, and it works differently depending on which SQL Server version sits on the source side. This article covers how mirroring actually works, where the two supported mechanisms diverge, what blocks it outright, what it costs, and where a real Data Factory pipeline or Spark notebook is still the correct tool instead.

1The Question This Answers

A common framing for this migration treats it as a three-way choice: adopt Snowflake as the analytics platform, or build a custom ingestion layer using containers and PySpark to move data into Azure, or find a native Microsoft path. Fabric Mirroring collapses most of that decision for the specific, common case of getting relational tables out of an existing SQL Server instance and into an analytics-ready Bronze layer. It does not require Snowflake, and for table-level replication it does not require custom Spark code either, since Fabric’s own Spark notebooks remain available for everything downstream of Bronze regardless of how the data arrived.

2What Fabric Mirroring Actually Does

Mirroring captures an initial snapshot of the tables selected for replication, then continuously applies subsequent changes, converting the result into analytics-ready Delta tables in OneLake as it goes. There is no scheduling involved and no incremental load logic to write. Once configured, the mirrored tables behave like a live, near real-time copy of the source, immediately queryable through SQL, Power BI, or Spark without an intermediate transformation step.

Authentication to the source SQL Server is supported through either SQL authentication with a username and password, or Microsoft Entra ID. A landing zone in OneLake stores both the initial snapshot and the ongoing change data before it is converted into the final Delta format.

3Two Mechanisms, Not One: CDC vs Change Feed

This is the detail most easily missed, because Microsoft markets mirroring as a single feature when it is actually two different implementations depending on source version.

• SQL Server 2016 through 2022 uses Change Data Capture. CDC must be enabled and functioning on the source database, and SQL Server Agent must be running, since CDC’s capture and cleanup jobs depend on it.
• SQL Server 2025 uses a newer change feed mechanism instead of CDC, with its own set of DMVs and stored procedures. It requires the source instance to be registered with Azure Arc, including the Azure Extension for SQL Server, for outbound authentication to Fabric. Critically, a SQL Server 2025 database cannot be mirrored if CDC is already enabled on it, the exact opposite dependency from the 2016 to 2022 path.

Anyone planning a mirroring rollout across a mixed-version estate needs to treat these as two separate configurations with two separate prerequisite checklists, not one feature applied uniformly.

4Prerequisites on the SQL Server Side

• An on-premises data gateway or a virtual network data gateway, installed and online, bridging the source SQL Server network to the Fabric service.
• SQL Server Agent running, for the 2016 to 2022 CDC-based path.
• Primary keys present on every table that needs to be mirrored, since change tracking depends on them.
• An active, running Fabric capacity. A paused or deleted capacity halts replication entirely; no data is replicated while the capacity is not running.
• Sufficient permissions for the dedicated login Fabric uses to connect to and read from the source database.

5The Platform-Support Reversal: Older Versions Reach Further

The intuitive assumption is that the newest SQL Server version gets the newest, most capable integration. For Fabric Mirroring, the opposite is currently true. The CDC-based path for SQL Server 2016 through 2022 is supported on-premises, on SQL Server running in an Azure VM, on non-Azure clouds, and even on Linux, specifically SQL Server 2017 with CU18 or later, and SQL Server 2019 and 2022 on Linux without a CU floor.

The change feed-based path for SQL Server 2025 is currently supported only for genuinely on-premises instances. It does not work against a SQL Server 2025 instance running in an Azure VM, and it does not work on SQL Server 2025 on Linux at all. An organization mid-upgrade to SQL Server 2025 that has also started moving instances into Azure VMs can lose mirroring eligibility on the newer version precisely because of where the VM happens to run, something worth checking before, not after, an upgrade and a cloud migration land in the same project timeline.

6Database-Level Limitations That Block Mirroring Outright

None of these is discovered gradually. Each one causes mirroring configuration to fail outright, so checking this list against every candidate database before scheduling the migration avoids a wasted attempt mid-project.

7The 1,000-Table Cap and How Fabric Chooses Which Tables

Mirroring supports a maximum of 1,000 tables per mirrored database. If the “mirror all data” option is selected on a database with more tables than that, Fabric mirrors the first 1,000 tables sorted alphabetically by schema name and then table name, and silently excludes everything past that point in the alphabetical order. Selecting individual tables instead of “mirror all data” still caps out at the same 1,000-table limit. For a database near or over this threshold, deliberately selecting the specific tables needed rather than relying on “mirror all data” is the safer path, since the alphabetical truncation behavior is easy to miss until a report built on a table near the end of the alphabet comes up empty.

8Cost: What Is Actually Free and What Is Not

Fabric compute used to perform the replication itself does not consume capacity units and is free. Mirroring storage is also free up to a limit tied directly to the purchased capacity size: one free terabyte of mirroring storage for every capacity unit purchased, so an F64 capacity includes 64 free terabytes of mirroring storage. Storage beyond that free allowance, or storage consumed while the capacity is paused, is billed as standard OneLake storage.

What is not free: querying the mirrored data through SQL, Power BI, or Spark consumes Fabric capacity at normal rates, exactly as it would for any other data in OneLake. Mirroring itself is close to free; using the data it lands is not, and a capacity plan built only around the mirroring storage allowance without accounting for downstream query load will undercount the real cost.

9The Restart Gotcha: No Resume, Only a Full Re-Snapshot

If mirroring is stopped, whether deliberately or because the Fabric capacity was paused or deleted, the existing mirrored data in OneLake is retained, but restarting mirroring does not resume from where it left off. It re-replicates all data from the start, meaning a full initial snapshot runs again regardless of how much had already been mirrored before the interruption. For a large database, that turns a brief, planned capacity pause into a multi-hour or multi-day re-synchronization the moment mirroring is turned back on, which is a materially different operational cost than the pause itself implied.

Long-running transactions on the source database carry a related risk: active transactions hold up transaction log truncation until they commit and the mirror catches up, or until the transaction aborts. A source database with unusually long-running transactions can see its own transaction log grow beyond its normal pattern purely because mirroring is waiting on those transactions, a scenario worth monitoring for independently of mirroring’s own health.

10Monitoring Mirroring Health

For the SQL Server 2025 change feed path, Microsoft documents specific DMVs and a stored procedure for checking mirroring health directly on the source: sys.dm_change_feed_log_scan_sessions and sys.dm_change_feed_errors for session and error status, and sp_help_change_feed for a consolidated view, alongside verifying the managed identity permissions in the Fabric portal itself. For the CDC-based path on SQL Server 2016 through 2022, mirroring health checks should follow the same database-level validation used for any CDC-dependent process: confirming the CDC capture and cleanup jobs are running under SQL Server Agent, and that CDC is not falling behind on the source.

11Where Mirroring Stops and a Real Pipeline Still Wins

Mirroring only replicates regular tables. It does not transform data, apply business rules, handle views or non-table objects, or produce the Silver or Gold layers of a medallion architecture on its own. Anything beyond a faithful, table-level copy of the source, meaning data type conversions, deduplication, business logic, or joins across sources, still belongs in Fabric’s own pipelines, dataflows, or Spark notebooks, exactly as covered in the site’s existing Fabric medallion build guide.

A database that trips any of the hard blockers above, that exceeds the 1,000-table cap in a way that cannot be worked around by selective table choice, or that needs data types mirroring does not support, such as JSON, VECTOR, XML, or geometry and geography columns, also needs a conventional pipeline rather than mirroring for those specific tables. A mixed approach, mirroring what qualifies and pipelining the rest into the same Bronze lakehouse, is a normal and reasonable outcome rather than a failure of the mirroring approach.

12A Practical Decision Path for This Migration

• Inventory source databases by SQL Server version first, since the version determines which mirroring mechanism, and which prerequisites, apply.
• Check every candidate database against the hard-blocker list before scheduling any mirroring work.
• For databases near or over 1,000 tables, plan explicit table selection rather than “mirror all data.”
• Identify any tables using unsupported data types up front and route those specifically to a pipeline instead of assuming mirroring will simply skip them cleanly.
• Build the capacity cost model around both the mirroring storage allowance and the expected downstream query load, not the storage allowance alone.
• Treat any planned Fabric capacity pause as a full re-synchronization event afterward, not a free pause, and schedule around that cost.

The recommendation: start with mirroring for every table that qualifies cleanly, since it removes real pipeline development and maintenance work for the tables it covers, and reserve custom pipeline or Spark notebook development specifically for the tables and transformations mirroring cannot handle. Building a full custom PySpark ingestion layer as the default approach, before checking what mirroring already covers, is very likely to be solving a problem Microsoft has already solved for the majority of a typical SQL Server estate.

References

• Microsoft Docs: Microsoft Fabric Mirrored Databases From SQL Server (Microsoft Learn)
• Microsoft Docs: Limitations of Fabric Mirrored Databases From SQL Server (Microsoft Learn)
• Microsoft Docs: Tutorial, Configure Microsoft Fabric Mirroring From SQL Server (Microsoft Learn)
• Microsoft Docs: Frequently Asked Questions for Mirroring SQL Server in Microsoft Fabric (Microsoft Learn)
• Microsoft Docs: Mirroring Overview and Cost of Mirroring (Microsoft Learn)
• Microsoft Fabric Community: Mirroring for SQL Server in Microsoft Fabric, Generally Available (Fabric Blog, 2025)
• Community and Industry Sources: Fabric Mirroring for SQL Server: FabCon Europe 2026 Session (Gethyn Ellis, 2026)
• SQLYARD: Microsoft Fabric: The Complete Guide
• SQLYARD: Building a Microsoft Fabric Medallion Lakehouse and Warehouse
• SQLYARD: SQL Server Transaction Log Full: Every Cause, the Right Fix, and What Not to Do

All technical content on SQLYARD is verified against Microsoft documentation at the time of publication. SQL Server features, cloud service capabilities, licensing terms, and configuration requirements can change between versions and cumulative updates. Always validate against current Microsoft Learn documentation before deploying to production. Fabric Mirroring limitations and platform support are explicitly noted by Microsoft as subject to change and should be reverified against current documentation before a migration is finalized.