sqlfingers.com: Fabric in Practice: SQL Server Near Real-Time Reporting

In Part 1 we covered what Fabric is. In Part 2 we walked through how it's structured (OneLake, Lakehouses, Warehouses), and in Part 3 we looked at how data gets ingested into Fabric. Now let's put it to work.

THE SCENARIO

You have a SQL Server running transactional workloads. It does its job well enough, but now leadership wants a dashboard that reflects current state -- aka, what's happening right now.

You already know the options. Run heavy reporting queries against production. eewgh. Or stand up a reporting replica, build ETL to keep it current, maintain a refresh schedule, and hope nothing breaks on a holiday weekend. It works, but it's expensive and has an awful lot of moving pieces.

Fabric gives you a third path: continuously replicate your SQL Server data into OneLake using Fabric Mirroring, and let Power BI read it using Direct Lake mode. Your SQL Server stays focused on OLTP and your reporting runs against a near real-time copy in Fabric. No pipelines. No refresh schedules. Nice.

Important note on 'Mirroring'

SQL Server Database Mirroring <> Fabric Mirroring

Database Mirroring was deprecated in 2012. Fabric Mirroring is a completely different technology. In Microsoft's words: "Mirroring in Fabric is a low-cost and low-latency solution to bring data from various systems together into a single analytics platform."

HOW IT WORKS

Three components make this scenario work:

1. Fabric Mirroring (SQL Server → OneLake)

Fabric Mirroring continuously replicates selected tables from your SQL Server into OneLake as Delta tables. An initial snapshot is taken, and from that point forward, changes are captured and replicated near real-time.

SQL Server	Mirroring Type	Change Mechanism	Key Requirements
2016–2022	Database	CDC	On-prem gateway; sysadmin for CDC setup
2025 (on-prem)	Database	Change Feed	Azure Arc + Extension

Fabric also supports Metadata mirroring (shortcuts, no data movement) and Open mirroring (API-driven, build your own) for other platforms. See Fabric Mirroring overview.

Both mechanisms replicate changes continuously. Under active load, changes can publish as frequently as every 15 seconds, with backoff logic during low activity (source). Once configured, Fabric creates a mirrored database in your workspace with an autogenerated SQL analytics endpoint — a read-only T-SQL interface you can query with SSMS, VS Code, or anything that speaks T-SQL.

2. Direct Lake Mode (OneLake → Power BI)

Direct Lake is a Power BI storage mode that reads Delta tables directly from OneLake. No data import into a semantic model. No scheduled refresh. No DirectQuery hitting your source. Because the mirrored data is already in Delta format, Direct Lake picks it up natively.

The result: your Power BI reports reflect changes as they land in OneLake. That's it. No upload or refresh required. MSSQLTips has a solid walkthrough of how Direct Lake works under the covers.

3. The End-to-End Flow

SQL Server (OLTP) → Fabric Mirroring (CDC or Change Feed) → OneLake (Delta tables) → Direct Lake (Power BI)

One workload. Three Fabric components -- and your SQL Server doesn't carry the reporting load.

SETTING IT UP (HIGH LEVEL)

The full setup is portal-driven, not T-SQL heavy. Microsoft's step-by-step tutorial covers every detail, but here is what you're walking into:

On the SQL Server side:

For SQL Server 2016–2022: install an on-premises data gateway (or VNet gateway). Create a dedicated login for Fabric with appropriate permissions. If CDC is not already enabled on your source tables, the Fabric setup process will configure it, and the login needs temporary sysadmin to do so.

-- Example: create the Fabric login (SQL Server 2016-2022)
-- Per Microsoft's tutorial
CREATE LOGIN fabric_login WITH PASSWORD = '<strong password>';

-- Grant sysadmin temporarily if CDC is not already enabled
ALTER SERVER ROLE sysadmin ADD MEMBER fabric_login;

-- After mirroring is configured and CDC is enabled,
-- remove sysadmin and grant only what's needed:
ALTER SERVER ROLE sysadmin DROP MEMBER fabric_login;

For SQL Server 2025: connect your instance to Azure Arc and install the Azure Extension for SQL Server. The extension provisions a managed identity that handles authentication to Fabric. No CDC required — SQL Server 2025 uses Change Feed natively. In fact, you cannot use Fabric Mirroring on a SQL Server 2025 database that already has CDC enabled.

In the Fabric portal:

Create a mirrored database item in your workspace, select your SQL Server connection, and choose the tables to replicate (up to 500). Fabric takes the initial snapshot and begins continuous replication.

For Power BI:

From the mirrored database's SQL analytics endpoint, create a new semantic model. The model defaults to Direct Lake mode. Build your report on top of it, and data flows through automatically.

See full details here in Explore data in your mirrored database and Build Power BI Reports with Direct Lake Tables.

WHAT TO KNOW BEFORE YOU COMMIT

CDC adds overhead to your production system.

For SQL Server 2016–2022, Fabric Mirroring requires CDC on the tables you replicate. CDC captures changes from the transaction log — it adds I/O and CPU overhead. Active transactions hold log truncation until the mirrored database catches up (source). Test this under realistic production load before you go live.

Change Feed (SQL Server 2025) is lighter, but not zero.

SQL Server 2025 scans the transaction log at high frequency and publishes changes to OneLake. Microsoft's language is 'least amount of resource tax on the source database' — not none.

'Near real-time' varies.

Replication latency depends on transaction volume, table size, and network throughput. Measure latency under load, during peak hours. That number is your 'near real-time.'

ONE WORKLOAD, ONE SCENARIO

This is not a post about migrating to Fabric. It's one sample scenario where Fabric solves a real problem: getting near real-time analytics from SQL Server without building and maintaining a separate reporting infrastructure.

If you are running SQL Server OLTP with a growing demand for real-time reporting, this is worth evaluating. Start with one workload. Measure the results. Expand from there.

COMING NEXT

In Part 5, we'll look at SQL Database in Microsoft Fabric — an actual transactional database running inside Fabric. What it is, what it isn't, and where it might actually make sense.

SQL Server Consulting

Tuesday, February 10, 2026

Fabric in Practice: SQL Server Near Real-Time Reporting