SQL Server 2019 Mirroring delays

ShafiUllah-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).

Hi eric_stephan-sk​,

I would be careful about assuming the one-hour delay is something you can solve by changing the backoff interval.

Microsoft describes the backoff logic as part of how Fabric Mirroring reduces unnecessary load on external source systems when activity is low. I could not find a documented setting that allows you to disable it or force every table to be polled on the same fixed cadence.

For SQL Server mirroring, Microsoft still describes incremental replication as near real time, so repeated hour-long delays on tables that are actively changing are worth investigating rather than treating as expected behaviour.

I would first separate where the latency is occurring. In the replication monitor, compare each table's Last completed timestamp with the time the source change actually occurred. If you enable workspace monitoring, the mirrored database logs expose ReplicatorBatchLatency at table level, which is useful for identifying whether the lag is occurring during replication itself.

Microsoft's mirroring troubleshooting guidance also recommends checking whether the data has already reached OneLake. If it is present in OneLake but not visible through the SQL analytics endpoint yet, the delay may be metadata synchronization rather than mirroring.

I would also compare a quiet table and a busy table using the same source and gateway. If both show high replication latency, I would investigate the gateway/source path. If only specific tables show the delay while others continue replicating normally, that gives you a stronger case to take to Microsoft support with the table-level monitoring logs.

For downstream fact/dimension processing, I would not assume that mirrored tables arrive transactionally in sync with one another. I would add a freshness gate or watermark check before starting the dependent workload, especially where a fact table relies on a dimension change being present first.

So my approach would be to treat backoff as service-managed, measure the actual latency by table, and escalate persistent hour-long lag on actively changing tables rather than trying to force a uniform polling interval.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

eric_stephan-sk​ The pattern you describe (quiet tables lag, busy tables don't) fits the backoff. For SQL Server, the mirroring overview only describes it in general terms. For BigQuery and Snowflake, Microsoft spells it out: it's per table, the wait grows exponentially up to one hour when no changes are found, and normal polling resumes once new data is detected. I couldn't find a documented setting to turn it off.

To confirm it's backoff and not the gateway or the source:

• In Monitor replication, compare the table's Last completed time with the time the change landed in the CDC table.
• Turn on workspace monitoring and look at the batches per table. If ReplicatorBatchLatency is small but the batch only starts long after the change, it's the polling interval. If the latency itself is high, look at the gateway (its compute is a listed latency factor) or the source.

MirroredDatabaseTableExecution
| where Timestamp > ago(1d) and OperationName == "Replicating"
| project OperationStartTime, SourceSchemaName, SourceTableName, ProcessedRows, ReplicatorBatchLatency
| order by SourceTableName asc, OperationStartTime asc

For fact and dimension consistency, don't assume tables arrive together. Two options:

• Check freshness before the downstream step runs (Last completed, or a max modified date per table), and only process when the dimensions have caught up.
• Make the joins tolerant of late dimensions, for example with an unknown member that gets corrected on the next load.

One idea to test, not a documented setting: since polling speeds up when changes are detected, a small scheduled update on a quiet dimension might keep it out of backoff. That adds CDC and log volume, so try it on one table first.

If this solves it, please mark it as the solution.

References:

https://learn.microsoft.com/en-us/fabric/mirroring/troubleshooting

https://learn.microsoft.com/en-us/fabric/mirroring/monitor-logs