Forum Discussion
Advocate ISQL Server 2019 Mirroring delays
Advocate IShafiUllah-bc ShivekMaharaj The delay is definitely due to backoff. The table I referenced that appeared in the cdc tables right away, but took an hour to appear in Fabric, only showed a ReplicatorBatchLatency of 102. While that is a longer delay than many of the other updates, it doesn't account for an hour delay.
I also know it wasn't due to the SQL endpoint metadata synchronization, as I watched exactly when the records came into the workspace monitoring. On top of that, we have the new metadata sync turned on for this workspace, so the updates should appear quickly.
Quiet tables and busy tables are definitely performing differently, as we saw hundreds of updates, with thousands of rows, come in while we were waiting for this one small quite table to sync. We are not going to constantly apply an unnecessary update to the quite tables just to prevent it from backing off.
To the suggestions of adding a freshness check, that doesn't really make sense, as dimension tables are updated infrequently, so you can't just wait for the dimension table to update, as there may not be an update for that time period.
We are looking into options to move from SQL Server 2019 to 2025 so that we can take advantage of change feed (instead of Change Data Capture).
- ShafiUllah-bc
Resolver II
eric_stephan-sk Thanks for testing it that carefully. A ReplicatorBatchLatency of 102 against an hour of waiting does point at backoff, and you're right that a freshness gate doesn't fit dimensions that may not change for days. I'll drop that suggestion.
On SQL Server 2025, the docs support the move, with a few things to plan for:
1. How it differs: 2016 to 2022 mirroring uses CDC. In 2025 the engine scans the transaction log at high frequency and publishes each table's changes to the Fabric landing zone, and the overview says changes can be published as fast as every 15 seconds.
2. What it needs: Azure Arc with the Azure Extension for SQL Server, on-premises Windows only for now (not Azure VMs, not Linux), and you can't mirror a database that still has CDC enabled. So plan to disable CDC on the source as part of the cutover.
3. DDL behavior: in 2025, a DDL change restarts a full snapshot for that table, so schedule schema changes accordingly.
One honest gap: the backoff sentence in the overview sits right after the 2025 description, and nothing says 2025 is exempt from it. Before committing to the upgrade, test one quiet table on a 2025 instance and compare the change time with Last completed in Monitor replication.
References:
https://learn.microsoft.com/en-us/fabric/mirroring/sql-server
https://learn.microsoft.com/en-us/fabric/mirroring/sql-server-limitations
https://learn.microsoft.com/en-us/fabric/mirroring/overview
