Does my Azure SQL Managed Instance has a read-only replica

Here are two SQL scripts you can use to check if your Azure SQL Managed Instance has a read-only replica:

1. Using sys.dm_fts_mirroring_endpoints:

SELECT *
FROM sys.dm_fts_mirroring_endpoints
WHERE mirror_state_desc = 'Synchronizing'
AND is_local = 0;

This script queries the sys.dm_fts_mirroring_endpoints dynamic management view, which contains information about mirroring and read-only replica endpoints. It filters for endpoints that are currently in the “Synchronizing” state and are not local (indicating a remote replica). If any results are returned, then your Managed Instance has at least one read-only replica.

2. Using sys.databases:

SELECT name, is_read_only_replica
FROM sys.databases
WHERE is_read_only_replica = 1;

This script queries the sys.databases system catalog view and filters for databases where the is_read_only_replica column is set to 1. This will directly show you the names of any read-only replica databases on your Managed Instance.

Both scripts will provide you with information about your read-only replicas, if any. The first script gives more details about the endpoint configuration, while the second script simply shows the names of the replica databases. Choose whichever script best suits your needs.

Additional notes:

• These scripts only work on Azure SQL Managed Instances. They will not work on on-premises SQL Servers.
• You need to have the necessary permissions to execute these scripts. The minimum required permission is the VIEW SERVER STATE server-level permission.
• The first script might display results even if you have a secondary replica in a failover group, not a read-only replica. To distinguish between the two, you can check the endpoint_type column in the returned result set. A value of ‘2’ indicates a read-only replica.

I hope this helps!