nerdexam
Microsoft

DP-300 · Question #435

Hotspot Question You have an Azure subscription that contains an Always On availability group named AG1. AG1 contains three replicas named Server1, Server2, and Server3. You execute the following…

The correct answer is The statement will only return databases that are members of AG1. = Yes; The statement will return all the databases for which Server1 is the secondary replica. = Yes; The statement will return all the Always On availability groups for which Server3 is a secondary replica. = No. The query executes on Server1 and joins sys.dm_hadr_database_replica_states with sys.availability_databases_cluster, which scopes results to databases participating in Always On Availability Groups - so it will only return AG1 member databases (Statement 1: Yes). Since the…

Submitted by alyssa_d· Mar 6, 2026Implement a high availability and disaster recovery environment - specifically querying and interpreting Always On AG replica states using DMVs such as sys.dm_hadr_database_replica_states and sys.availability_databases_cluster

Question

Hotspot Question You have an Azure subscription that contains an Always On availability group named AG1. AG1 contains three replicas named Server1, Server2, and Server3. You execute the following T-SQL statements on Server1. For each of the following statements, select Yes if the statement is true, otherwise select No. NOTE: Each correct selection is worth one point. Answer:

Exhibits

DP-300 question #435 exhibit 1
DP-300 question #435 exhibit 2

Answer Area

  • The statement will only return databases that are members of AG1.Yes
  • The statement will return all the databases for which Server1 is the secondary replica.Yes
  • The statement will return all the Always On availability groups for which Server3 is a secondary replica.No

Explanation

The query executes on Server1 and joins sys.dm_hadr_database_replica_states with sys.availability_databases_cluster, which scopes results to databases participating in Always On Availability Groups - so it will only return AG1 member databases (Statement 1: Yes). Since the query filters where is_local = 1 AND is_primary_replica = 0, it returns databases where Server1 is acting as a secondary replica, not the primary (Statement 2: Yes). Statement 3 is No because the DMV sys.dm_hadr_database_replica_states when queried on Server1 only reflects the local node's view and AG membership visible from Server1 - it does not provide a cross-server view of Server3's role as a secondary replica in other availability groups outside of what Server1 participates in; the query context is Server1, not Server3.

Topics

#Always On Availability Groups#Dynamic Management Views#SQL Server High Availability#T-SQL System Catalog

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full DP-300 Practice