Database Mirroring in Microsoft Fabric
Replicate operational databases to Microsoft Fabric for real-time analytics with database mirroring. Support for SQL Server, Cosmos DB, and Snowflake.
Fabric Mirroring eliminates one of the oldest challenges in enterprise analytics: getting operational data into an analytics platform without degrading production system performance. Traditionally, organizations built complex ETL pipelines with nightly batch loads, accepting that analytics would always be hours or days behind reality. Mirroring replaces this pattern with near real-time, continuous replication from transactional databases to OneLake using change data capture (CDC)—no pipelines to build, no schedules to manage, and minimal impact on source systems. The replicated data arrives in Delta format, immediately queryable with SQL, Spark, and Direct Lake semantic models. Our Microsoft Fabric consulting team implements production mirroring configurations with monitoring and failover strategies.
How Database Mirroring Works
Mirroring operates in two phases that run as a continuous process:
Phase 1: Initial Snapshot
When you first configure mirroring, Fabric performs a full copy of each selected table from the source database to OneLake. This snapshot captures the current state of every row and writes it as Delta Parquet files. For large tables (hundreds of millions of rows), the initial snapshot can take hours—plan accordingly and monitor progress in the Fabric workspace.
Phase 2: Continuous Change Replication
After the initial snapshot completes, mirroring switches to CDC mode. The system captures every insert, update, and delete operation on the source tables and applies them incrementally to the Delta tables in OneLake. Replication latency typically ranges from seconds to a few minutes depending on change volume, source database load, and network conditions.
The process runs continuously without intervention. If the connection is interrupted (network outage, source database maintenance), mirroring automatically resumes from where it left off using CDC checkpoints—no data loss and no manual restart required.
Supported Data Sources
Each source uses a different CDC mechanism, resulting in different capabilities and requirements:
| Source | CDC Mechanism | Supported Objects | Key Requirements | |---|---|---|---| | Azure SQL Database | SQL Server Change Tracking | Tables with primary keys | Enable Change Tracking on source DB | | Azure SQL Managed Instance | SQL Server Change Tracking | Tables with primary keys | Network connectivity to Fabric | | Azure Cosmos DB | Cosmos DB Change Feed | Containers | Enable Change Feed on containers | | Snowflake | Snowflake Streams | Tables and views | Snowflake account with ACCOUNTADMIN | | Azure Databricks | Delta Change Data Feed | Delta tables | Unity Catalog enabled workspace |
Each source has specific prerequisites. Azure SQL Database requires Change Tracking enabled at both the database and table levels. Cosmos DB requires the Change Feed feature enabled on containers. Snowflake requires creating streams and stages with appropriate permissions.
Setting Up Mirroring Step by Step
Step 1: Prepare the Source Database
Before creating a mirrored database, configure the source:
- Azure SQL DB: Enable Change Tracking (ALTER DATABASE SET CHANGE_TRACKING = ON with AUTO_CLEANUP and CHANGE_RETENTION settings). Enable Change Tracking on each table you plan to mirror.
- Cosmos DB: Ensure Change Feed is enabled (it is by default for most configurations). Verify your Cosmos DB account uses the SQL API.
- Snowflake: Create a dedicated service account with SELECT access to target tables. Configure network rules to allow Fabric connectivity.
Step 2: Create the Mirrored Database in Fabric
Navigate to your Fabric workspace, select "New" > "Mirrored Database", and choose your source type. Provide the connection details: server name, database name, and authentication credentials. For Azure SQL, you can use SQL authentication or Microsoft Entra (Azure AD) authentication.
Step 3: Select Tables and Start Replication
After connecting, Fabric displays available tables from the source. Select the tables you need for analytics—do not mirror every table if only a subset is required, as each mirrored table consumes capacity units. Click "Mirror" to begin the initial snapshot.
Step 4: Monitor Replication Status
The Mirrored Database item in your workspace shows real-time status for each table:
- Initial sync in progress: Snapshot is running, showing percentage complete
- Running: Active replication with CDC, showing latency and rows replicated
- Paused: Replication is temporarily stopped (capacity paused or manual pause)
- Error: Replication failed—check error details and source database connectivity
Querying Mirrored Data
Once mirrored data arrives in OneLake, you can query it through multiple Fabric engines:
SQL Analytics Endpoint
Every mirrored database automatically includes a SQL analytics endpoint. Query mirrored tables using standard T-SQL through the Fabric workspace or any tool that connects via TDS (SQL Server Management Studio, Azure Data Studio, Power BI). The SQL endpoint is read-only—you cannot write back to the source through the mirror.
Spark Notebooks
Access mirrored Delta tables from Fabric notebooks using PySpark or Spark SQL. The tables appear in the Lakehouse file system under the mirrored database's OneLake path. This enables data science workloads, complex transformations, and cross-source joins without moving data.
Direct Lake Semantic Models
The most powerful integration is connecting a Direct Lake semantic model directly to mirrored tables. This gives you import-like Power BI performance on data that is minutes old—no scheduled refresh needed. When the mirrored data updates, the Direct Lake model automatically reflects the changes on the next query.
Performance Optimization
Capacity Planning
Mirroring consumes Fabric capacity units (CUs) for both the initial snapshot and ongoing CDC processing. Monitor consumption in the Capacity Metrics app:
- Initial snapshot: Heavy CU consumption proportional to table size. Schedule large initial syncs during off-peak hours.
- Steady-state CDC: Lower CU consumption proportional to change volume. High-churn tables (millions of changes per hour) consume more CUs.
- Concurrent mirrors: Each mirrored database is independent. Ten mirrored databases consume ten times the CUs of one.
Table Selection Strategy
Mirror only the tables needed for analytics. Common strategies:
- Fact tables: Always mirror transaction tables needed for reporting
- Dimension tables: Mirror if dimensions change frequently (product catalog updates). For static dimensions, consider a one-time copy instead.
- Large history tables: Consider mirroring only recent partitions if the source supports partition-level CDC
Latency Tuning
Latency depends on multiple factors:
- Source database load: Heavy write workloads generate more CDC events, increasing latency slightly
- Network bandwidth: Azure sources within the same region have lowest latency
- Table row size: Wide rows (many columns, large text fields) take longer to replicate per change
- Fabric capacity headroom: If capacity is heavily utilized, CDC processing may be delayed
Common Use Cases
Real-Time Operational Dashboards
Replace nightly ETL with mirroring to deliver dashboards that show data from minutes ago instead of yesterday. Sales operations teams can track order volume, inventory teams can monitor stock levels, and customer service teams can see ticket queues—all with near-real-time accuracy.
Cross-Database Analytics
Mirror tables from multiple source databases (Azure SQL, Cosmos DB, and Snowflake) into the same Fabric workspace. Join them using SQL analytics endpoints or Spark notebooks to create unified views that would be impossible with direct queries against isolated operational systems.
Compliance and Audit
Mirrored data in OneLake retains Delta table history through time travel. Compliance teams can query the state of any table at any point in time within the retention window—critical for regulatory audits, dispute resolution, and forensic analysis.
Troubleshooting Common Issues
| Symptom | Likely Cause | Resolution | |---|---|---| | Initial sync stuck at 0% | Network connectivity issue | Verify firewall rules allow Fabric to reach source DB | | High replication latency | Capacity throttling | Check Capacity Metrics app for CU utilization, upgrade SKU if needed | | Tables showing error state | Schema change on source | Mirror may need to be recreated if primary key columns changed | | Missing recent changes | CDC disabled on table | Re-enable Change Tracking on the source table | | Auth failure after password rotation | Stale credentials | Update connection credentials in the mirrored database settings |
Related Resources
Frequently Asked Questions
Is database mirroring real-time?
Mirroring is near real-time, typically with latency measured in seconds to a few minutes depending on change volume and source database performance. It uses CDC for continuous synchronization.
Does mirroring affect my production database?
Impact is minimal. Mirroring uses CDC which has low overhead. For Azure SQL, it uses built-in change tracking. Always monitor source performance during initial setup.