sql server cluster - state of primary node is unknown on secondary node

Question

sql server cluster - state of primary node is unknown on secondary node

Alibek Ismailov 21

Hi, i have sql cluster (always on) with 2 nodes. When 2nd node was turned off, i added a new db to Primary DB. After Secondary db was turned on, on Secondary db state of Primary db is unknown. Look screenshot for details. On Primary i see that Operational State of Secondary is Unknown.
What is wrong? Adding a new db on primary when secondary db was turned off broke cluster? How to recover cluster?

CarrinWu-MSFT 6,801 Reputation points

2021-06-25T02:52:53.837+00:00

Hi @Alibek Ismailov , welcome to Microsoft Q&A! Which SQL Server version do you used? And could you please check error log to get the error details? The error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files.
Alibek Ismailov 21 Reputation points

2021-06-25T03:39:10.013+00:00

@CarrinWu-MSFT
I didn't find serious errors, only these:
Unable to retrieve registry value 'NodeRole' from Windows registry key 'Software\Microsoft\Microsoft SQL Server\MSSQL15.SDBO\Polybase\Configuration': (null).

The state of the local availability replica in availability group 'DB1' has changed from 'NOT_AVAILABLE' to 'RESOLVING_NORMAL'. The state changed because the local instance of SQL Server is starting up.
If this is a Windows Server Failover Clustering (WSFC) availability group, you can also see the WSFC management console.

The state of the local availability replica in availability group 'DB1' has changed from 'RESOLVING_NORMAL' to 'SECONDARY_NORMAL'.

Cannot join database 'db1' to availability group 'DB1'. The database has already joined the availability group. This is an informational message. No user action is required.
Alibek Ismailov 21 Reputation points

2021-06-25T03:48:47.077+00:00

The target database, 'db1', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.

I use SQL server 2019
CarrinWu-MSFT 6,801 Reputation points

2021-06-28T01:30:21.387+00:00

Hi @Alibek Ismailov , we have not get a reply from you. Any update for this issue?

CarrinWu-MSFT 6,801 Reputation points

2021-06-25T02:52:53.837+00:00

Hi @Alibek Ismailov , welcome to Microsoft Q&A! Which SQL Server version do you used? And could you please check error log to get the error details? The error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files.
Alibek Ismailov 21 Reputation points

2021-06-25T03:39:10.013+00:00

@CarrinWu-MSFT
I didn't find serious errors, only these:
Unable to retrieve registry value 'NodeRole' from Windows registry key 'Software\Microsoft\Microsoft SQL Server\MSSQL15.SDBO\Polybase\Configuration': (null).

The state of the local availability replica in availability group 'DB1' has changed from 'NOT_AVAILABLE' to 'RESOLVING_NORMAL'. The state changed because the local instance of SQL Server is starting up.
If this is a Windows Server Failover Clustering (WSFC) availability group, you can also see the WSFC management console.

The state of the local availability replica in availability group 'DB1' has changed from 'RESOLVING_NORMAL' to 'SECONDARY_NORMAL'.

Cannot join database 'db1' to availability group 'DB1'. The database has already joined the availability group. This is an informational message. No user action is required.
Alibek Ismailov 21 Reputation points

2021-06-25T03:48:47.077+00:00

The target database, 'db1', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.

I use SQL server 2019
CarrinWu-MSFT 6,801 Reputation points

2021-06-28T01:30:21.387+00:00

Hi @Alibek Ismailov , we have not get a reply from you. Any update for this issue?

Answer 1

Hi @Alibek Ismailov ,

Before you add a new database to an Existing Always On Availability Group, you should test on your staging environment first and make Full & Log backups for your databases. After that you could add a new database to your production environment. And there is no need to turn off your secondary replica when you add a new database. Please refer to Add a SQL Server Database to an Existing Always On Availability Group.

The state of the local availability replica in availability group 'DB1' has changed from 'NOT_AVAILABLE' to 'RESOLVING_NORMAL'.

This link shows a similar error, please refer to it to get the steps.

Best regards,
Carrin

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Alibek Ismailov 21

hi @CarrinWu-MSFT i found that it is normal state of cluster, secondary doesnt see state of primary, it shows like unknown. Also i found this: https://learn.microsoft.com/en-us/answers/questions/87199/availability-replica-server-with-question-status-a.html. Thx for help

CarrinWu-MSFT 6,801 Reputation points

2021-06-28T05:15:28.433+00:00

Hi @Alibek Ismailov , good to know that. Could you please do "Accept Answer", it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

sql server cluster - state of primary node is unknown on secondary node

2 answers

Activity