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 (preview).
Changes to Fabric capacity or workspaceLearn more from Changes to Fabric capacity.
In addition, note the following table for 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;. Any other resource errors Mirroring is disabled To ensure your compute resources aren't affected and to protect on your Azure SQL Database, mirroring can be disabled on any persistent resource errors. "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. 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.
Execute the following query to check if the changes properly flow:
SELECT * FROM sys.dm_change_feed_log_scan_sessions;
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;
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.
If replication is still not working, verify that the correct SAMI object has permissions.
Contact support if troubleshooting is required.
The System Assigned Managed Identity (SAMI) of the Azure SQL logical server needs to be enabled, and must be the primary identity. For more information, see Create an Azure SQL Database server. Enable the SAMI in the Azure portal, in the resource menu under Security, in the Identity page.
After enablement, if SAMI setting status is either turned Off or initially enabled, then disabled, and then enabled again, the mirroring of Azure SQL Database to Fabric OneLake will fail.
The SAMI must be the primary identity. Verify the SAMI is the primary identity with the following T-SQL script: SELECT * FROM sys.dm_server_managed_identities;
User Assigned Managed Identity (UAMI) is not supported. If you add a UAMI, it becomes the primary identity, replacing the SAMI as primary. This causes replication to fail. To resolve:
The System Assigned Managed Identity (SAMI) of the Azure SQL logical server needs to 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 to create the mirrored database, make sure you grant the permission explicitly.
Do not remove SAMI Read and Write permissions on Fabric mirrored database item. If you accidentally remove the permissions, mirroring Azure SQL Database will not function as expected. No new data can be mirrored from the source database.
If you remove Azure SQL Database SAMI permissions or permissions are not set up correctly, use the following steps.
... ellipses option on the mirrored database item.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.
DBCC FREESYSTEMCACHE('TokenAndPermUserStore') to clear security caches on the database.DBCC FLUSHAUTHCACHE to clear the federated authentication context cache.RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4