From reporting replicas to Microsoft Fabric: mirroring Azure SQL and SQL Server for analytics

#1. The transformation

Before: nightly SSIS/ADF jobs copy OLTP data into a reporting database or warehouse; reports read yesterday's data; reporting queries sometimes hit production.

After: Fabric mirroring replicates changes from the SQL source into OneLake in near real time as Delta tables. Power BI (Direct Lake), the SQL analytics endpoint, notebooks and pipelines all use the same copy.

#2. Supported sources (check the docs for current status)

#3. Prerequisites (Azure SQL Database example)

• Fabric capacity (F SKU or trial) and a workspace.
• System-assigned managed identity enabled on the logical server.
• A login/user for Fabric with the required permissions (for example db_owner or the documented minimal set).
• Network: public access with firewall rules for Fabric, or private networking with the supported gateway options.
• Tables need a primary key (or a unique index per the current requirements).

#4. Set up

Fabric workspace → New item → Mirrored Azure SQL Database → connection (server, database, authentication) → choose all tables or a selection → Mirror database.

Watch the Monitor replication view: status per table, rows replicated, last sync time.

#5. Use the data

• SQL analytics endpoint: read-only T-SQL over the mirrored Delta tables (views, joins with other OneLake data).
• Power BI Direct Lake semantic models on top — no import refresh schedules.
• Notebooks / Spark for data science; shortcuts to combine with other sources.

#6. Costs

Mirroring compute and storage for replication up to a free amount tied to your capacity size (see current Fabric pricing); queries on the mirrored data use your Fabric capacity as usual. Compare with the ETL VMs, SSIS runtimes and reporting databases you can retire.

#7. Limits to plan for

• Some data types and table types are not replicated (check the list: for example certain spatial/xml types or tables without a primary key).
• Schema changes on mirrored tables: some are handled, some require reseeding the table.
• It's a read copy for analytics — not a DR solution and not writable.

#8. Retire the old path

1. Run both paths in parallel; compare report numbers.
2. Switch reports to the mirrored data.
3. Retire the ETL jobs and the reporting replica/database (and their licences).

Tip

Start with the reports that hurt most (heavy queries on production, or business asking for fresher data). Near-real-time data without touching the OLTP system is an easy win to show stakeholders.