question

AlibekIsmailov-8900 avatar image
0 Votes"
AlibekIsmailov-8900 asked CarrinWu-MSFT commented

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

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?
109036-capture.png


sql-server-general
capture.png (17.1 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AlibekIsmailov-8900, 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.

0 Votes 0 ·

@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.

0 Votes 0 ·

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

0 Votes 0 ·

Hi @AlibekIsmailov-8900, we have not get a reply from you. Any update for this issue?

0 Votes 0 ·
CarrinWu-MSFT avatar image
1 Vote"
CarrinWu-MSFT answered

Hi @AlibekIsmailov-8900,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlibekIsmailov-8900 avatar image
0 Votes"
AlibekIsmailov-8900 answered CarrinWu-MSFT commented

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://docs.microsoft.com/en-us/answers/questions/87199/availability-replica-server-with-question-status-a.html. Thx for help

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AlibekIsmailov-8900, 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!

0 Votes 0 ·