1

In my production environment I have two database Servers know as DBN1 and DBN2. I have made a cluster between two and DBN1 is my primary database server. Now I used Always on availability feature on both servers and databases were synchronized.

Today I am encountering an issue. There is a small question mark icon coming on the DBN1 which is shown by DBN2 always on availability properties. Moreover it is also not showing that it is a primary database. Why this is happening ?

This is my primary server output & follow the arrow

enter image description here

This is my secondary server output & follow the arrow

enter image description here

0

The secondary replica does not know the role / status of that DBN1 replica

When trying to failover to the secondary, the status of the primary is not known:

enter image description here

it finds the current primary replica but not the primary replica status.

When failing over from the primary it does find this information.


When you select the properties of the replica with the question mark on the secondary replica:

enter image description here

Properties

enter image description here

Role: Unknown

Whereas on the primary replica all roles and their sync states are known.


sys.dm_hadr_availability_replica_states

Executing this query returns multiple lines on the primary, but only the one for the secondary on the secondary replica's.

select * from sys.dm_hadr_availability_replica_states;

This behaviour would be reversed after failover.


Extra checks

When looking at the query that gets executed when opening the properties of the unknown replica on the secondary replica, the sys.dm_hadr_availability_replica_states dmv is also used with a left join in a much bigger query.

An interesting part here is the OperationalState column:

ISNULL(arstates.operational_state, 6) AS [OperationalState],

Meaning if the replica is not present in that dmv, such as when executing on the secondary, return 6 as the OperationalState.

This state is not explicitly noted in the documentation on the dmv:

operational_state tinyint Current operational state of the replica, one of:

0 = Pending failover

1 = Pending

2 = Online

3 = Offline

4 = Failed

5 = Failed, no quorum

NULL = Replica is not local.

For more information, see Roles and Operational States, later in this topic.

The 'Unkown' state is also not noted in the documentation.

In this case 6 is used when the role is not known.

It does not always appear to be 6, when refreshing the availability replica's on the secondary:

enter image description here

the query used sets the unknown replica's role status to 3:

ISNULL(arstates.role, 3) AS [Role],

Which is offline on the dmv's documentation.

And the connected state column will be 2

ISNULL(arstates.connected_state, 2) AS [ConnectionState],

Which is also not noted in the documentation:

0 : Disconnected. ...

1 : Connected. ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.