Edit

Share via

Troubleshoot Fabric mirrored databases from Azure SQL Database

This article covers troubleshooting steps troubleshooting for mirroring Azure SQL Database.

For troubleshooting the automatically configured mirroring for Fabric SQL database, see Troubleshoot mirroring from Fabric SQL database.

Changes to Fabric capacity or workspace

Changes to the Fabric capacity or workspace can affect mirroring. For more information, review the effects on mirroring from Changes to Fabric capacity.

Azure SQL Database troubleshooting

Cause Result Recommended resolution
Workspace deleted Mirroring stops automatically and disables the change feed in Azure SQL Database In case mirroring is still active on the Azure SQL Database, execute the following stored procedure on your Azure SQL Database: exec sp_change_feed_disable_db;.
Persistent errors Mirroring is disabled for the affected database To ensure your compute resources aren't affected and to protect your source Azure SQL Database, mirroring will be disabled on any persistent errors. Review sys.dm_change_feed_errors and resolve the underlying errors before re-enabling the database for mirroring.
"Users can access data stored in OneLake with apps external to Fabric" setting disabled "Replicator - Tables Cannot Reach Replicating Status" Enable the Tenant setting Users can access data stored in OneLake with apps external to Fabric.

For additional troubleshooting scenarios, see Troubleshoot Fabric Mirrored Databases - Microsoft Fabric.

T-SQL queries for troubleshooting

If you're experiencing mirroring problems, perform the following database level checks using Dynamic Management Views (DMVs) and stored procedures to validate configuration.

  1. Execute the following query to check if the changes properly flow:

    SELECT * FROM sys.dm_change_feed_log_scan_sessions;

  2. If the sys.dm_change_feed_log_scan_sessions DMV doesn't show any progress on processing incremental changes, execute the following T-SQL query to check if there are any problems reported:

    SELECT * FROM sys.dm_change_feed_errors;

  3. If there aren't any issues reported, execute the following stored procedure to review the current configuration of the mirrored Azure SQL Database. Confirm it was properly enabled.

    EXEC sp_help_change_feed;

    The key columns to look for here are the table_name and state. Any value besides 4 indicates a potential problem.

  4. If replication is still not working, verify that the correct managed identity object has permissions.

    1. In the Fabric portal, select the "..." ellipses option on the mirrored database item.
    2. Select the Manage Permissions option.
    3. Confirm that the managed identity name shows with Read, Write permissions.
    4. Ensure that AppId that shows up matches the ID of the managed identity of your Azure SQL Database logical server.

  5. Contact support if troubleshooting is required.

Managed identity

Either the System Assigned Managed Identity (SAMI) or the User Assigned Managed Identity (UAMI) of the Azure SQL logical server must be enabled, and one of them must be the primary identity.

Note

Support for User Assigned Managed Identity (UAMI) is currently in preview.

Verify the correct primary identity using the following Transact-SQL query:

SELECT * FROM sys.dm_server_managed_identities;

For more information, see Create an Azure SQL Database server.

Permissions for managed identities

Both the System Assigned Managed Identity (SAMI) and the User Assigned Managed Identity (UAMI) for the Azure SQL logical server must have Read and Write permissions on the mirrored database item in Microsoft Fabric.

When you create the mirrored database from the Fabric portal, the permission is granted automatically. If you encounter error Unable to grant required permission to the source server. User does not have permission to reshare during the setup, ensure you have a member or admin role in the workspace with sufficient privilege. When you use API or CI/CD to create the mirrored database, make sure you grant the permission explicitly.

Don't remove SAMI and/or UAMI Read and Write permissions on Fabric mirrored database items. If you accidentally remove the permissions, mirroring Azure SQL Database doesn't function as expected. No new data can be mirrored from the source database.

If you remove Azure SQL Database SAMI and/or UAMI permissions, or permissions aren't set up correctly, refer to the steps outlined in the tutorial section to set it up.

Errors from stale permissions with Microsoft Entra logins

Before using Microsoft Entra ID authentication, review the limitations in Microsoft Entra server principals.

Database users created using Microsoft Entra logins can experience delays when being granted roles and permissions. This could result in an error such as the following in the Fabric portal:

"The database cannot be mirrored to Fabric due to below error: Unable to retrieve SQL Server managed identities. A database operation failed with the following error: 'VIEW SERVER SECURITY STATE permission was denied on object 'server', database 'master'. The user does not have permission to perform this action.' VIEW SERVER SECURITY STATE permission was denied on object 'server', database 'master'. The user does not have permission to perform this action. SqlErrorNumber=300,Class=14,State=1, Activity ID: ..."

During the current preview, the following commands should be used to address these issues.

  • Drop the user from the user database.
  • Execute DBCC FREESYSTEMCACHE('TokenAndPermUserStore') to clear security caches on the database.
  • Execute DBCC FLUSHAUTHCACHE to clear the federated authentication context cache.
  • In the user database, re-create the user based on the login.

Transaction log usage

Transaction log usage for a database enabled for mirroring can continue to grow and hold up log truncation. Once the transaction log size reaches the max defined limit, writes to the database fail. To safeguard from this, mirroring triggers automatic reseed of the whole database when the log space used exceeds a threshold of total configured log space. To diagnose this and learn about automatic reseeding, see Automatic reseed for Fabric mirrored databases from Azure SQL Database.

Reseeding has automatically started

Fabric Mirroring from Azure SQL Database can automatically reseed under certain conditions, at the individual table level or for the entire database. To learn more, Automatic reseed for Fabric mirrored databases from Azure SQL Database.

Related content

  • Last updated on