Mirroring SQL Server to Fabric is GA: can it replace your nightly ETL?

Mirroring for SQL Server 2016 to 2025 in Microsoft Fabric is now generally available. What it replaces, what it needs, what it costs and where the limits are.

Brian Bønk20 November 20255 min read

Microsoft Fabric SQL Server Mirroring OneLake

Mirroring for SQL Server in Microsoft Fabric is now generally available. That covers every in-market version from SQL Server 2016 to 2022, and SQL Server 2025, which is generally available as well. Your on-premises databases can now be replicated continuously into OneLake with a feature that has left preview.

For many teams, this goes straight at the nightly ETL job. You know the one: the SSIS package or pipeline that copies yesterday's data into a reporting database, and the morning phone call when it fails. Mirroring offers near real-time replicas in Delta format instead, with no pipeline to build.

Here is what it does, what it needs, and where it stops.

How it works

You set up the mirror in the Fabric portal. You give it the SQL Server and database connection details, then choose to mirror all data or pick the tables you want. Mirroring takes an initial snapshot into OneLake and then keeps it in sync in near real time as data changes.

In Fabric you get a mirrored database item and an autogenerated SQL analytics endpoint. The endpoint is read-only, but you can build views, inline table-valued functions and stored procedures on top of it. Power BI can read it with Direct Lake.

The mechanism depends on the version:

SQL Server 2016 to 2022 uses Change Data Capture (CDC). CDC relies on SQL Server Agent. An on-premises data gateway or VNet data gateway reads the snapshot and the changes and writes them into OneLake.
SQL Server 2025 uses the change feed, the same technology as mirroring for Azure SQL. SQL Server writes directly to OneLake. The gateway is used for control and authentication, and the Azure Arc agent handles outbound authentication.

Prerequisites to check first

Check these first:

Edition and platform. On Windows, SQL Server 2016 to 2022 is supported in Standard, Enterprise and Developer editions. SQL Server 2017 on Linux needs CU18 or later, and 2019 and 2022 on Linux are supported.
SQL Server 2025 specifics. Mirroring from 2025 is supported for on-premises instances. It is not currently supported on Azure VMs or on Linux, and it requires Azure Arc with the Azure Extension for SQL Server.
A gateway. For a server behind a firewall, you need an on-premises data gateway or a virtual network data gateway.
Permissions. The login Fabric uses needs ALTER ANY EXTERNAL MIRROR, which is part of CONTROL or db_owner. You also need the Admin or Member role in the Fabric workspace.
Primary keys. For SQL Server 2016 to 2022, a table without a primary key cannot be mirrored.

What it costs

The pricing model is the part that makes this interesting for reporting workloads. The Fabric compute used to replicate data into OneLake is free and does not consume capacity. Storage for mirrored replicas is free up to one terabyte per capacity unit, so an F64 includes 64 TB of mirroring storage.

You still pay for what you do with the data. Queries through SQL, Power BI or Spark are charged at regular capacity rates. Storage beyond the free limit, or while the capacity is paused, is billed as normal OneLake storage.

Do not forget the source side. During the initial snapshot you can see more CPU and IO on the source. Long-running transactions hold back log truncation until mirroring catches up, so watch the transaction log.

The limits that matter

Read the limitations page in full. The ones I would check first:

A maximum of 500 mirrored tables per database. With "Mirror all data", the first 500 tables in alphabetical order by schema and table name are used.
Mirroring works on the primary database of an availability group. Failover cluster instances are not currently supported.
Several column types are not replicated, including xml, json, vector, geometry, geography, hierarchyid, sql_variant, text, ntext and image. LOB columns over 1 MB are truncated to 1 MB.
datetime2(7) and datetimeoffset(7) lose the seventh decimal. datetimeoffset(7) also loses the time zone.
Row-level security, object-level permissions and dynamic data masking are not carried over to OneLake. You have to secure the mirrored data again in Fabric.
On SQL Server 2025, mirroring is not supported if CDC or replication is enabled on the database.
Stopping mirroring disables it completely, and starting it again reseeds every table from scratch.

The security point matters: permissions defined in the source database do not follow the data into Fabric.

What this means for you

Mirroring is a good fit when your nightly ETL is mostly a copy: tables moved as they are into a reporting database, with modelling done later. In that case you can swap a fragile batch job for a managed, near real-time replica and build your semantic model on top.

It is a weaker fit when the ETL does heavy transformation, depends on unsupported data types, or when you have many hundreds of tables in one database. Mirroring gives you the raw layer. You still need to model, clean and secure.

A practical way to start:

Pick one reporting database whose nightly load causes you pain.
Check version, edition, primary keys and data types against the limitations page.
Install or reuse a gateway, and for SQL Server 2025 connect the instance to Azure Arc.
Mirror a handful of tables and build one report on the SQL analytics endpoint or with Direct Lake.
Watch the source transaction log and CPU for a week before you retire anything.

Takeaway

After two decades in data, I have written more nightly copy jobs than I would like to count. General availability means mirroring is now a supported option for production reporting, with free replication compute and free storage up to your capacity size. The limits are real, so test against your actual schema before you switch off the old job.

Which of your nightly loads is only a copy, and could be the first one to go?

Sources

Enjoyed this? Get the next one by email

Occasional emails about Microsoft Fabric, SQL Server, Power BI and Synapse.