Adding 4th node AO fails

Question

Adding 4th node AO fails

Marco Bakker 146

Hi guys,

I have the following config;

2 W2012R2std\SQL2016ent servers and I want to migrate to 2 W2016dc\SQL2019ent and after that completed to W2019dc\SQL2019ent
Windows cluster level is W2012R2 and finally level W2019
within SSMS I add a 3rd replica, all goes well
when adding the 4th replica, message this is the 4th node and a max of 3 is allowed, unfortunately no screenshot :-(
but it is SQLent edition, so max of 8 is allowed

I really don't understand why this is happening.
Especaillay because I did it before but then with SQLdev as the main difference.

I hope somebody can clarify this issue for me.

kind regards
Marco

Ben Miller (DBAduck) 951 Reputation points MVP

2023-03-22T03:31:54.5+00:00

Can you detail the versions of Windows and SQL for each node/replica? What version of Windows 2012R2 (DataCenter, Enterprise, Standard)?
Seeya Xi-MSFT 15,111 Reputation points Microsoft Vendor

2023-03-22T06:47:47.98+00:00

Hi @Marco Bakker,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,

Seeya
Marco Bakker 146 Reputation points

2023-03-22T17:35:45.1733333+00:00

Hi Ben,

It is W2012R2 standard and SQL2016 enterprise edition. And the new to be added replica's are W2016 datacenter and SQL 2019 enterprise.

Hope this helps.

kr
Marco

Ben Miller (DBAduck) 951 Reputation points MVP

2023-03-22T03:31:54.5+00:00

Can you detail the versions of Windows and SQL for each node/replica? What version of Windows 2012R2 (DataCenter, Enterprise, Standard)?
Seeya Xi-MSFT 15,111 Reputation points Microsoft Vendor

2023-03-22T06:47:47.98+00:00

Hi @Marco Bakker,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,

Seeya
Marco Bakker 146 Reputation points

2023-03-22T17:35:45.1733333+00:00

Hi Ben,

It is W2012R2 standard and SQL2016 enterprise edition. And the new to be added replica's are W2016 datacenter and SQL 2019 enterprise.

Hope this helps.

kr
Marco

Answer 1

Seeya Xi-MSFT 15,111 Microsoft Vendor

Hi @Marco Bakker,

Can you ping between nodes? Make sure that the fourth replica can communicate with the other three replicas and that all necessary ports are open.

Did you join the Windows cluster for this node? You can check the specifics via Failover Configuration Manager. The cluster log in there will record some error messages.

User's image

In the case of four nodes, you also need to configure the Quorum mechanism.

Windows Server Failover Clustering (WSFC) uses a quorum-based approach to monitoring overall cluster health and maximize node-level fault tolerance. A quorum node set is a majority of the voting nodes and witnesses in the WSFC cluster. The overall health and status of a WSFC cluster is determined by a periodic quorum vote. The four nodes will appear 2-to-2.

Please refer to this article:

https://www.sqlshack.com/windows-failover-cluster-quorum-modes-in-sql-server-always-on-availability-groups/

In addition, here is a MS document:

https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/wsfc-quorum-modes-and-voting-configuration-sql-server?view=sql-server-ver16

Best regards,

Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Marco Bakker 146 Reputation points

2023-03-20T09:50:06.8433333+00:00

Hello Seeya,

Thanks for your reply.

But of course the node was joined to the cluster otherwise I could not add it as a replica and yes I could ping this 4th node and no there were no errors, only a popup window appeared with the message that this is the 4th node and only 3 were allowed.

I really don't know what is wrong with the configuration. I've done it before but the only difference was dev edition instead of ent. But dev is the same as ent.
Within a couple of weeks I'm going the same action again and I will pay more attention to the configuration steps and make a screenshot if the message appears again. Hopefully not.

If anybody has some other ideas what could be the reason of this 'failure' please let me know.

kr
Marco
Seeya Xi-MSFT 15,111 Reputation points Microsoft Vendor

2023-03-21T06:53:49.3633333+00:00

Hi @Marco Bakker,

Hope everything goes well. Is it SQL Server 2019 in your cluster when you join the fourth node? SQL Server 2019 (15.x) increases the maximum number of synchronous replicas to 5, up from 3 in SQL Server 2017 (14.x).

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-groups-sql-server?view=sql-server-ver16

Best regards,

Seeya

Adding 4th node AO fails

1 answer

Activity