Dyota's blog

BC to Fabric - stuck sync

I had a problem where I had added some tables and columns to an existing BC to Fabric mirroring setup and it broke some tables. The data was these in the data lake, but it just wasn't being read in to the SQL endpoint.

Here is what AI told me to do, unedited.

Fixing a stuck mirrored-table schema sync (Fabric)

Symptom

Why the obvious fix doesn't work

The SQL endpoint has a refreshMetadata REST API that forces it to resync its schema cache from the underlying Delta tables. This is the correct fix only if the Delta table itself already has the new columns and the SQL endpoint just hasn't noticed yet.

It returns a per-table status:

Status Meaning
Success Synced new data/schema
NotRun No changes detected — nothing to sync
Failed Sync attempt failed

If a table shows NotRun even after calling this, it means the Delta table's own schema hasn't changed — the problem is upstream, in the mirroring/replication pipeline itself, not in the SQL endpoint's cache. Calling refreshMetadata repeatedly will not fix this.

Root cause (what's actually happening)

The Fabric portal's mirroring monitoring UI shows a bare "Errors" flag with no detail. The actual per-table error is only visible via the REST API getTablesMirroringStatus. In the case we hit, the replication for one specific table had silently stopped with System.InternalError at the exact moment the source schema changed (new BC columns added), while every other table in the same mirrored database kept replicating fine. Once a table's CDC stream chokes on a schema change like this, it does not recover on its own — it just sits there, stuck, forever reporting the same stale lastSyncDateTime.

Diagnostic steps

0. Prerequisites

$token = (Get-AzAccessToken -ResourceUrl 'https://api.fabric.microsoft.com').Token
$headers = @{ Authorization = "Bearer $token" }

# List workspaces, find yours by name
Invoke-RestMethod -Uri "https://api.fabric.microsoft.com/v1/workspaces" -Headers $headers -Method Get |
    Select-Object -ExpandProperty value | Format-Table id, displayName

# List items in that workspace, find the MirroredDatabase item
$WorkspaceId = '<workspace-guid>'
Invoke-RestMethod -Uri "https://api.fabric.microsoft.com/v1/workspaces/$WorkspaceId/items" -Headers $headers -Method Get |
    Select-Object -ExpandProperty value | Where-Object type -eq 'MirroredDatabase' | Format-Table id, displayName

1. Check per-table mirroring status/errors (the thing the UI won't tell you)

$WorkspaceId        = '<workspace-guid>'
$MirroredDatabaseId = '<mirrored-database-item-guid>'

$token = (Get-AzAccessToken -ResourceUrl 'https://api.fabric.microsoft.com').Token
$headers = @{ Authorization = "Bearer $token" }

$uri = "https://api.fabric.microsoft.com/v1/workspaces/$WorkspaceId/mirroredDatabases/$MirroredDatabaseId/getTablesMirroringStatus"
$resp = Invoke-RestMethod -Uri $uri -Headers $headers -Method Post -Body '{}' -ContentType 'application/json'

# Overview: which tables are unhappy
$resp.data | Select-Object sourceTableName, status,
    @{n='errorCode'; e={$_.error.errorCode}},
    @{n='lastSync';  e={$_.metrics.lastSyncDateTime}} |
    Format-Table -AutoSize

# Full detail (including error message) for one specific table
$resp.data | Where-Object sourceTableName -eq '<TableName-1234>' | ConvertTo-Json -Depth 5

A table stuck on a schema change looks like this: status: Replicating, error.errorCode: System.InternalError, and a lastSyncDateTime that is stale (older than every other table) and matches roughly when the source schema changed.

2. (Optional) Confirm the Delta table itself doesn't have the new columns yet

$token = (Get-AzAccessToken -ResourceUrl 'https://storage.azure.com').Token
$headers = @{ Authorization = "Bearer $token" }

$tablePath = "Tables/dbo/<TableName-1234>/_delta_log"
$uri = "https://onelake.dfs.fabric.microsoft.com/$WorkspaceId/$MirroredDatabaseId/$tablePath`?resource=filesystem&recursive=false&directory=$tablePath"
$logFiles = (Invoke-RestMethod -Uri $uri -Headers $headers -Method Get).paths |
    Where-Object { $_.name -match '\.json$' } | Sort-Object name

# Check the newest commits for a "metaData" action (= schema change record)
foreach ($f in ($logFiles | Select-Object -Last 10)) {
    $content = Invoke-RestMethod -Uri "https://onelake.dfs.fabric.microsoft.com/$WorkspaceId/$MirroredDatabaseId/$($f.name)" -Headers $headers -Method Get
    if ($content -match '"metaData"') { Write-Host "$($f.name): has a metaData (schema change) action" }
}

If none of the recent commits contain a metaData action, the schema genuinely hasn't changed in the Delta table — confirming the mirroring pipeline is the blocker, not the SQL endpoint.

The fix: stop/start mirroring to force a resync

There is no per-table "retry" API. The reliable fix is to stop and restart mirroring for the whole mirrored database. This forces a full re-snapshot of every table in it (not just the broken one), which clears the stuck error.

$WorkspaceId        = '<workspace-guid>'
$MirroredDatabaseId = '<mirrored-database-item-guid>'

$token = (Get-AzAccessToken -ResourceUrl 'https://api.fabric.microsoft.com').Token
$headers = @{ Authorization = "Bearer $token" }

$stopUri  = "https://api.fabric.microsoft.com/v1/workspaces/$WorkspaceId/mirroredDatabases/$MirroredDatabaseId/stopMirroring"
$startUri = "https://api.fabric.microsoft.com/v1/workspaces/$WorkspaceId/mirroredDatabases/$MirroredDatabaseId/startMirroring"
$statusUri = "https://api.fabric.microsoft.com/v1/workspaces/$WorkspaceId/mirroredDatabases/$MirroredDatabaseId/getMirroringStatus"

# 1. Stop
Invoke-WebRequest -Uri $stopUri -Headers $headers -Method Post -Body '{}' -ContentType 'application/json' | Out-Null
Write-Host "Stop requested."

# 2. Start immediately — the API will reject this with 'OperationNotAllowedInCurrentStatus'
#    while it's still in "Stopping" state, so retry until it's accepted.
$started = $false
for ($i = 0; $i -lt 20 -and -not $started; $i++) {
    try {
        Invoke-WebRequest -Uri $startUri -Headers $headers -Method Post -Body '{}' -ContentType 'application/json' | Out-Null
        $started = $true
        Write-Host "Start accepted."
    } catch {
        Write-Host "Not ready yet ($($_.ErrorDetails.Message)), retrying in 10s..."
        Start-Sleep -Seconds 10
    }
}

# 3. Poll overall status until it's back to Running
do {
    Start-Sleep -Seconds 10
    $status = (Invoke-RestMethod -Uri $statusUri -Headers $headers -Method Post -Body '{}' -ContentType 'application/json').status
    Write-Host "$(Get-Date -Format HH:mm:ss) DB status: $status"
} while ($status -notin @('Running', 'Stopped'))

Verify it actually worked

$tblUri = "https://api.fabric.microsoft.com/v1/workspaces/$WorkspaceId/mirroredDatabases/$MirroredDatabaseId/getTablesMirroringStatus"

# Poll the specific table until it's out of "Snapshotting" with no error
do {
    Start-Sleep -Seconds 20
    $resp = Invoke-RestMethod -Uri $tblUri -Headers $headers -Method Post -Body '{}' -ContentType 'application/json'
    $t = $resp.data | Where-Object sourceTableName -eq '<TableName-1234>'
    Write-Host "$(Get-Date -Format HH:mm:ss) status: $($t.status) error: $($t.error.errorCode)"
} while ($t.status -eq 'Snapshotting')

Then confirm the new columns are visible on the SQL endpoint itself (sqlcmd / any T-SQL client):

SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '<TableName-1234>'
ORDER BY ORDINAL_POSITION;

If a column is still missing, calling the SQL endpoint's refreshMetadata API once more (see Invoke-FabricSqlEndpointRefresh.ps1 in this folder) may be needed to nudge the cache — but only bother with that after confirming the Delta table/mirroring status is healthy, otherwise you're just repeating the wrong fix.

Gotchas

#fabric