Forum Discussion

nlucero's avatar
nlucero
Advocate 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.

nlucero's avatar
nlucero
Advocate I
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.

WLY168's avatar
WLY168
Advocate I
1 year ago

I can confirm that this access right solves my problem when using a "Service Principal" identity as registered in Entra against the workspace. i.e. the identity when registering the connection for the Mirror must pre-exist in SQL with the relevant logon, user and grants. You cannot create logins using the preview brower based session for querying Azure SQL Database. (You must use SQL Management Studio).

Assuming workspace name XXX and using Azure SQL Database (NOT managed instance)

 

1. Use management studio to execute

CREATE LOGIN [XXX] FROM EXTERNAL PROVIDER;



2. Change to target Azure SQL Database and create user 

CREATE USER [XXX] FOR LOGIN [XXX]

The documentation makes mention of a server role [##MS_ServerStateReader##] to which the login is added, but this does not make sense in the context of Azure SQL database where you cannot access master.


3. Grant the rights as specified in the documentation.

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


Within Entra you will have to find the app registration for the workspace and generate a secret. Use that secret, the Entra Tenant ID and the Client ID of XXX (from Entra) to register the Service Principal  based authenticated connection while registering the mirror connection.

I managed to stop and start the replication with impunity and many variations without causing any corruption to the target Azure SQL Satabase META data.

My only caveat is that I am working in a Trial Capacity that I cannot pause as per nlucero !!!