fabric-mirroring

# Mirroring in Fabric Mirroring brings an external database or catalog into OneLake as a unit — no ETL pipeline, no orchestration, no compute you allocate. It creates a **`MirroredDatabase`** item plus an autogenerated read-only **SQL analytics endpoint**. There is **no local file artifact** (unlike TMDL or PBIR). You configure mirroring in the portal or through the REST API. ## 1. Three kinds — settle this first Every later answer depends on which kind you are in. All three are called "mirroring", and only one of them copies data. | Kind | Mechanism | Data location | Latency | | --- | --- | --- | --- | | **Database mirroring** | Continuous replication into OneLake as Delta | Copied into OneLake | Changes published as fast as **every 15 s** | | **Metadata mirroring** | Syncs catalog names, schemas, tables; reads through **OneLake shortcuts** | Stays at the source | Source access + shortcut time; no replication step | | **Open mirroring** | *You* write change files to a landing zone; Fabric merges them into Delta | Copied into OneLake | As fast as you land files | **Database mirroring is source-driven, not polled.** The source engine identifies changed data — SQL Server 2025 scans its own transaction log — and publishes to a Fabric landing zone. A replicator engine inside Fabric scans for new files at high frequency and merges them into the target Delta table. **Backoff logic** slows polling for idle tables to avoid loading the source engine, and resumes automatically when changes reappear. **Metadata mirroring supports cross-tenant consumption** precisely because it is shortcuts underneath — you can read live governed data from another tenant via OneLake external data sharing with no copy. ## 2. Which kind each source uses From the `mirroring/overview` platform table as of 2026-08-30. | Source | Kind | | --- | --- | | Azure SQL Database, Azure SQL Managed Instance, SQL Server | Database | | Fabric SQL database | Database — **automatic, nothing to configure** | | Azure Cosmos DB | Database | | Azure Database for PostgreSQL | Database | | Azure Database for MySQL (preview) | Database | | Google BigQuery | Database — **GA as of Aug 2026** | | Oracle, SAP | Database | | SharePoint List (preview) | Database | | Snowflake | Metadata | | Azure Databricks | Metadata (Unity Catalog structure) | | Dremio catalog (preview) | Metadata | | AWS Glue catalog (preview) | Metadata — Iceberg tables stay in S3 | | Azure Monitor (preview) | Metadata — connection-based, surfaces via Eventhouse | | Open mirrored database | Open | Per-source limits, auth, and setup detail: `references/source-matrix.md`. **Read the source's own limitations page before promising behaviour** — the per-source pages carry constraints the general docs don't, and they lag each other (see the table-cap conflict in §8). ## 3. Mirroring vs Copy job vs shortcut vs pipeline Both Mirroring and Copy job are pitched as "no pipeline". They are not interchangeable. - **Mirroring** — you want an *entire external database or catalog* in Fabric, continuously in sync, as a unit. You do not choose a schedule. - **Copy job** (`fabric-copy-job`) — you want *guided many-source → many-destination movement* with full or incremental modes you configure, including into destinations mirroring cannot target. - **Shortcut** — you want *specific* tables, folders, or files referenced in place. Open formats only. Mirroring is your only option if the source stores data in a proprietary format. - **Pipeline / dataflow / eventstream** — you need multi-source transformation logic, your own schedule or trigger, a destination outside OneLake, or streaming. They compose: **mirror once, consume everywhere** — one workspace mirrors the source, other workspaces shortcut to the mirrored tables rather than mirroring the same source again. Shortcuts to mirrored tables are read-only, inherit the mirrored item's security model, and show the mirrored item's error state if replication breaks. ## 4. What you get - A `MirroredDatabase` item and an autogenerated **read-only SQL analytics endpoint** — same experience as the Lakehouse SQL analytics endpoint, same limitations. - **Direct Lake** works over mirrored data with no extra step; it is Delta in OneLake. - **Cross-database queries** with three-part names, joining mirrored databases, warehouses, and Lakehouse SQL analytics endpoints in one T-SQL query. - **Sharing** a mirrored database also grants access to its SQL analytics endpoint, without granting workspace access. ## 5. Cost, retention, and capacity - **Core mirroring compute is free** and does not consume CUs. Querying the data via SQL, Power BI, or Spark bills at normal rates, as do direct OneLake requests. - **Storage is free up to 1 TB per capacity unit** — an F64 gets 64 TB of mirroring storage. You pay OneLake rates above that, or when the capacity is paused. - **A running capacity is required.** Paused → mirroring stops and the item isn't listable. Resumed → status shows **Paused** and you must select **Resume replication** manually. A *long* pause can force a full reseed, because the source transaction log can't truncate while mirroring is stopped. Trial expiry stops mirroring outright. - **Extended capabilities are paid** (§7). - **Retention**: mirroring auto-vacuums old Delta files. Configure via `retentionInDays`, allowed 1–30. The docs conflict on the default — `mirroring/overview` says 1 day for mirrored databases created from the portal after mid-June 2025 (7 for older ones), while `mirrored-database-rest-api` says flatly 7. **Set it explicitly rather than relying on the default.** ## 6. REST API Item CRUD lives under `mirroredDatabases`; start/stop/status are separate operations. **These APIs do not apply to Azure Databricks mirrored databases.** Generic Fabric REST patterns (LRO polling, continuation tokens, the definition envelope) are in `fabric-rest-api`. ``` POST /v1/workspaces/{ws}/mirroredDatabases create GET /v1/workspaces/{ws}/mirroredDatabases list GET /v1/workspaces/{ws}/mirroredDatabases/{id} get PATCH /v1/workspaces/{ws}/mirroredDatabases/{id} rename/describe DELETE /v1/workspaces/{ws}/mirroredDatabases/{id} delete POST .../{id}/getDefinition .../{id}/updateDefinition POST .../{id}/startMirroring .../{id}/stopMirroring POST .../{id}/getMirroringStatus .../{id}/getTablesMirroringStatus ``` **A data source connection must exist first** — create it in the portal or via the Connections API, then reference its ID in the definition. The definition is a single part, `mirroring.json`, base64 as `InlineBase64`: ```json { "properties": { "source": { "type": "<source type>", "typeProperties": { "connection": "<connection GUID>", "database": "xxxx" } }, "target": { "type": "MountedRelationalDatabase", "typeProperties": { "defaultSchema": "xxxx", "format": "Delta", "retentionInDays": 1, "enableDeltaChangeDataFeed": true } } } } ``` - Omit `mountedTables` to mirror the whole database; supply it — an array of `{ "source": { "typeProperties": { "schemaName", "tableName" } } }` — to mirror selected tables. `updateDefinition` refreshes that list to add or remove tables. - `defaultSchema` preserves the source schema hierarchy. Without it, older mirrored databases flatten the schema into the table name and the only fix is recreating the item. (The REST doc's prose misspells this as `deafultSchema`; the property is `defaultSchema`, as its own examples show.) - `connection`, `database`, and `defaultSchema` can only be updated while **Get mirroring status** returns `Initialized` or `Stopped`. - `startMirroring` fails while status is `Initializing`. **The non-obvious prerequisite.** For Azure SQL Database, Azure SQL Managed Instance, Azure Database for PostgreSQL, Azure Database for MySQL, and SQL Server 2025 you must additionally: (1) enable the source server's managed identity, and (2) grant that identity **Read and Write** on the mirrored database — **currently portal-only**, or via the Add Workspace Role Assignment API. Nothing in the create call surfaces this; mirroring simply doesn't work without it. .NET SDK support requires `Microsoft.Fabric.Api` **≥ 1.0.0-beta.11**. ## 7. Extended capabilities (paid, preview) Core mirroring is free; these are not. Billing resumed in all regions the week of 2026-05-25. - **Delta change data feed (CDF)** — row-level insert/update/delete tracking, enabled per mirrored database via `enableDeltaChangeDataFeed`. Available for all mirroring sources including open mirroring partners. Billed on incremental compute for real changes only — no charge for idle periods — and it increases storage through extra `_change_data` files. Enable selectively. - **Mirroring views** — replicates source view logic instead of physical tables. **Snowflake only** in preview. Core mirroring pricing is unaffected by enabling either. ## 8. Limits and gotchas **Capacity limits** - **1,000 tables** per mirrored database. "Mirror all data" takes the first 1,000 sorted by schema then table name; the rest are silently not mirrored. *Per-source pages can be lower and can lag* — `snowflake-limitations` still states 500. - **1 TB of change data per mirrored database per day.** Exceeding it produces "The replication is being throttled and expected to continue at …"; wait it out. **Things that cannot be changed after creation** - **The source database.** Not supported — create a new mirrored database. - **Item ownership.** Not supported. If the owner leaves the organization, recreate the mirrored database. - **`keyColumns`** in open mirroring, once set. **Things that trigger a reseed** - Any DDL change on a mirrored source table reseeds *that* table. - `stopMirroring` then `startMirroring` reseeds **all** tables from scratch. - A long capacity pause can force a full reseed. **Data-shape traps** - **Views are not replicated** by core mirroring — regular tables only. (Mirroring views is the paid preview add-on in §7.) - **varchar truncation.** The SQL analytics endpoint supports `varchar(max)` to 16 MB, but only for tables created **after 2025-11-18**, and per-item caps are lower — 1 MB for mirrored SQL Server / Azure SQL / MI and Fabric SQL database, 2 MB for Cosmos DB. Tables created before that date are `varchar(8000)` and must be recreated. - **Columns with spaces or special characters** (`, ; { } ( ) \n \t =`) replicate via Delta column mapping — but tables already replicating from before that feature need the table removed and re-added, or a full stop and restart, to pick the columns up. **Security does not travel** Row-level security, object-level permissions, dynamic data masking, and Purview sensitivity labels defined on the source are **not** propagated to the replicated data in OneLake. Secure the mirrored item on the Fabric side; see `fabric-security`. **Dropping in the source is asymmetric** - Mirroring *all* data + source table dropped → mirrored table is deleted. - Mirroring a *selective list* + source table dropped → the mirrored table stays, with "The source table doesn't exist" in monitoring. Remove it from the configuration to delete it. - Dropping a source *schema* leaves an empty schema in the SQL analytics endpoint either way. ## 9. Open mirroring landing zone You get a landing zone URL on the mirrored database's Home page. Write Parquet or delimited text (uncompressed, or Snappy / GZIP / ZSTD) into per-table folders: ``` https://onelake.dfs.fabric.microsoft.com/<workspace id>/<mirrored db id>/Files/LandingZone/TableA .../LandingZone/Schema1.schema/TableB ``` Non-negotiable rules: - **Every table folder needs `_metadata.json`** declaring `keyColumns`. Without it, updates and deletes are impossible — everything is an insert. It can be added later, but `keyColumns` is immutable once set. - **`__rowMarker__` must be the final column.** Values: `0` insert, `1` update, `2` delete, `4` upsert. - **File names are 20 digits**, `00000000000000000001.parquet`, continuous and monotonically increasing. Fabric deletes processed files but leaves the last one so you can find your place. - **Omit `__rowMarker__` on the initial load** — the whole file is treated as INSERT, which is faster and gives better metrics. - **Updates must carry the full row**, every column. - Schema folders are named `<schemaname>.schema`. Full protocol — delimited-text properties, supported data types, table and column operations, nonsequential-file mode, the cleanup process — is in `references/open-mirroring.md`. ## 10. When nothing is replicating Work down, in order: 1. **Portal → Monitor replication.** Database status is one of *Running*, *Running with warning*, *Stopping/Stopped*, *Failed*, *Paused*. Check the **Last completed** column per table — empty means that table has never mirrored. Programmatically: `getTablesMirroringStatus`, which returns per-table status plus `processedRows`, `processedBytes`, `lastSyncDateTime`. 2. **Is it a capacity state?** *Paused* means the capacity was paused and resumed — you must select **Resume replication**. 3. **Is the data in OneLake?** Shortcut the mirrored tables into a Lakehouse and query with Spark. If it is there, the problem is downstream. 4. **Is it only the SQL analytics endpoint?** Data in OneLake but not in T-SQL is a metadata-sync delay. Hit **Refresh** on the SQL analytics endpoint page and re-query. 5. **Enable workspace monitoring** for latency: mirrored-database operation logs land in the `MirroredDatabaseTableExecution` table in the monitoring KQL database; `ReplicatorBatchLatency` is the execution-latency value. 6. **Then go source-specific.** Azure SQL DB, Azure SQL MI, MySQL, and PostgreSQL each have their own troubleshooting page with SQL checks. Common non-bugs: **views** aren't replicated; **columns are missing** because their type is unsupported — check the Alert icon next to the table under **Configure replication**. ## 11. Constraints **MUST** - Establish which of the three kinds you are in before answering anything about latency, cost, or where the data physically lives. - Create the source connection before the mirrored database. - Enable the source server's managed identity **and** grant it Read/Write on the mirrored database for Azure SQL DB / MI / PostgreSQL / MySQL / SQL Server 2025. - Put `__rowMarker__` last, and give every open mirroring table folder a `_metadata.json` with `keyColumns`, before expecting updates or deletes. - Read the per-source limitations page before committing to a source. **PREFER** - Setting `retentionInDays` explicitly over trusting a default the docs disagree about. - Mirror once and shortcut, over mirroring the same source in several workspaces. - `defaultSchema` on creation, over discovering flattened schema names later and having to recreate the item. - Enabling delta change data feed selectively, per mirrored database. **AVOID** - Planning on changing the source database, the item owner, or `keyColumns` later — all three mean recreating the item. - `stopMirroring`/`startMirroring` as a routine fix; it reseeds every table. - Assuming source RLS, column permissions, masking, or sensitivity labels reach OneLake. They do not. - Quoting a per-source table cap or limit from memory — the pages disagree with each other and with the general limits. ## 12. See also - `fabric-copy-job` — guided many-source → many-destination ingestion, with configurable full/incremental modes. The other "no-pipeline" item. - `fabric-database` — Fabric SQL database, which mirrors to OneLake automatically with nothing to configure. - `fabric-rest-api` — generic Fabric REST patterns behind §6. - `fabric-security` — the workspace-role and OneLake security model that mirrored-item sharing sits on. - `fabric-eventhouse`, `fabric-realtime-dashboard` — the Azure Monitor mirrored catalog surfaces through an Eventhouse endpoint.