Forum Discussion

nlucero's avatar
nlucero
Icon for Advocate I rankAdvocate I
1 year ago
Solved

Azure SQL Mirrored DB says "metadata tables are corrupted" when stopping then restarting replication

I created a Mirrored SQL DB in fabric that connects to a source Azure SQL DB via a service principal. I was able to get the replication up and running as expected. It began mirroring tables as expect...
data engineering
data pipeline
  • nlucero's avatar
    nlucero
    1 year ago

    I heard back from MS support. TL;DR: you need to grant VIEW PERFORMANCE DEFINITION to the managed identity fabric is using to connect to the db:

    GRANT SELECT, ALTER ANY EXTERNAL MIRROR, VIEW PERFORMANCE DEFINITION TO [User];

    The official docs only mention the need for `SELECT, ALTER ANY EXTERNAL MIRROR`.

     

    This does not fix a database that already has corrupted metadata, but it makes it so that the reproduction steps I mention in the first post do not generate a metadata corruption in the first place.

     

    I believe MS is planning to push a fix for this before the end of the month, but I don't know whether that will be just an update to the documentation or a code fix that makes the "ANY EXTERNAL MIRROR" permission sufficient.

v-ssriganesh's avatar
v-ssriganesh
Icon for Community Support rankCommunity Support
1 year ago

Hi nlucero,
Thank you for reaching out to the Microsoft Fabric Forum Community.

After thoroughly reviewing the details you provided, here’s how you can troubleshoot this without needing to restore your source database:

  • Go to the mirrored database in the Fabric portal and use the Monitor Replication section. Look for outdated timestamps or error alerts on your tables. This will confirm if replication is stalled.
  • In the Azure Portal, ensure the System Assigned Managed Identity (SAMI) for your Azure SQL logical server is enabled (check under Identity settings). Then, in Fabric, go to the mirrored database item, select Manage Permissions, and confirm the SAMI has Read and Write access.
  • Check your Azure SQL Database allows connections from Azure services (check Networking settings in the Azure Portal). If you’re using a private endpoint, you may need to set up a virtual network data gateway in Fabric to maintain a stable connection.

If the error persists, you might need to delete the mirrored database item in Fabric (this won’t affect your source database) and create a new one using the same connection details. This can often resolve metadata issues without touching the source database.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

nlucero's avatar
nlucero
Icon for Advocate I rankAdvocate I
1 year ago

Thanks for the reply v-ssriganesh - I confirmed all of the steps above that they're not the root problem. Details below. But, in short, I am able to see the same error in the DB itself when I run

EXEC sys.sp_change_feed_enable_db  
    @destination_type = 2;  
GO

The full error message is: 

Msg 22710, Level 16, State 1, Procedure sys.sp_synapse_link_enable_db_internal, Line 415 [Batch Start Line 0]
Could not update the metadata. The failure occurred when executing the command 'SetTridentLink(Value = 1)'. The error/state returned was 22697/1: 'Cannot enable fabric link on the database because the metadata tables are corrupted.'. Use the action and error to determine the cause of the failure and resubmit the request.

So, it appears that the corrupted metadata tables are in my Azure SQL DB, independent of Fabric, and I don't know how to uncorrupt or reset them. Further, it will be a problem if the metadata tables become corrupted any time I stop replication and/or turn off my Fabric compute.

 

More Detail:

 

This is the view of the mirrored db in my Fabric portal. Notice that even though I previously had tables succesfully mirroring, the portal won't even list them when this error is thrown.

I went to "manage permissions" and confirmed that my SAMI is configured with Read/Write permissions on the db. I then went into the connection definition itself, removed the credentials, and then re-added them to confirm that the connection parameters are valid and the connection is active (it is).

 

Also, the database does allow connections from Azure services. I confirmed this. But, additionally, Fabric is having no problem connecting to that Azure SQL DB in general. I opened a new Data Factory job and successfully confirmed a connection to the same DB using the same access credentials. So, the connection itself doesn't seem to be the problem. As summarized above, I'm getting the "currupt metadata" error in the Azure SQL DB itself, even when not accessing from Fabric.