ASD: How can I determine replica name from info in sys.dm_database_replica_states?

Question

I have named replicas (NOT availability replicas) in my Azure SQL Database instance. When retrieving information from sys.dm_database_replica_states (generally to check secondary_lag_seconds), how can I determine the replica name associated with each record?

Answer 1

@Joseph Harris The current method for identifying a lagging replica involves querying the SYS.DM_DATABASE_REPLICA_STATES view. This allows you to pinpoint the replica_id that is experiencing latency. However, linking this replica_id to its corresponding database name directly from the primary replica is not yet possible. This functionality is under development to enhance the monitoring experience. In the meantime, the DATABASEPROPERTYEX() function can be utilized to map the replica_id to its database name by executing it within the context of each named replica.

Below is the example query that can be executed in context of each named replica to identify the lag, replica_id and database name.

SELECT REPLICA_ID, DB_NAME() AS 'DATABASE NAME',SYNCHRONIZATION_STATE_DESC, LOG_SEND_QUEUE_SIZE/1024.0/1024.0 AS LOG_SEND_QUEUE_SIZE_GB,

LAST_SENT_TIME, LAST_RECEIVED_TIME, LAST_COMMIT_TIME, SECONDARY_LAG_SECONDS FROM SYS.DM_DATABASE_REPLICA_STATES

WHERE REPLICA_ID = DATABASEPROPERTYEX(DB_NAME(),'REPLICAID')

Regards

Geetha

Answer 2

I don't see that information on the DMV you provided, but maybe the following query and the sys.dm_geo_replication_link_status DMV helps:

select 
  partner_server,
  partner_database,
  replication_state,
  replication_state_desc,
  role_desc,
  secondary_allow_connections_desc,
  last_replication,
  replication_lag_sec
from sys.dm_geo_replication_link_status
go