none
Adding 4th Secondary Node in SQL2k16 Server RRS feed

  • Question

  • Hello,

    We already have two nodes in one site and the third node at a different site as part of AG in SQL2k16 residing on a Win2k12 r2 OS. The Listener has two ip addresses, one belonging to the local subnet and another one belonging to the remote subnet. All the three nodes of the cluster are OK.

    We added 2 more nodes to the windows cluster. No issues. However, when we try to add one of the new nodes as a new replica inside SSMS from the primary node (using GUI method), after validation though the wizard, we are getting following error:

    TITLE: Microsoft SQL Server Management Studio

    ------------------------------

    Failed to join the instance 'SQLInstance4' to the availability group 'AOGrp1'. (Microsoft.SqlServer.Management.HadrModel)

    For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=15.0.18131.0+((SSMS_Rel).190606-1032)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476

    ------------------------------

    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    None of the IP addresses configured for the availability group listener can be hosted by the server 'SQLInstance4'. Either configure a public cluster network on which one of the specified IP addresses can be hosted, or add another listener IP address which can be hosted on a public cluster network for this server.

    Failed to join local availability replica to availability group 'AOGrp1'.  The operation encountered SQL Server error 19456 and has been rolled back.  Check the SQL Server error log for more details.  When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command. (Microsoft SQL Server, Error: 19456)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.5366&EvtSrc=MSSQLServer&EvtID=19456&LinkId=20476

    We selected the option of configuring Listener to the new replica later, but still we received the above error. Basically, our current config looks as follows:

    AOGrp1 -->only one AO group

    SQLInstance1 = subnet1 (primary node)

    SQLInstance2 = subnet1 (secondary)

    SQLInstance3 = subnet2 (secondary) --> remote location and for DR purpose

    SQLInstance4 = subnet1 -->newly added to the windows cluster (to be secondary)

    SQLInstance5 = subnet1-->newly added to the windows cluster (to be secondary)

    Listener = one ip address of the subnet1 and one ip address of subnet2

    As said, the first three SQLInstance1, 2 and 3 are working OK the Listener has no issues. The error is seen only when we try to add the 4th instance to the AG. Nothing on the sql server logs on any of the nodes relating to this error.

    Will greatly appreciate your help.

    Thanks.

    Victor


    Victor

    Wednesday, October 9, 2019 3:48 AM

All replies

  • Hi vr123,

     

    >>None of the IP addresses configured for the availability group listener can be hosted by the server 'SQLInstance4'. Either configure a public cluster network on which one of the specified IP addresses can be hosted, or add another listener IP address which can be hosted on a public cluster network for this server.

     

    Would you please check if  the ip address of listener for the subnet1 is the same as the ip address for SQLInstance4?  This error can be resolved by adding a right IP to the listener. The IP address for all subnets cannot be an IP address already in use, i.e. The IP address of one of the nodes.

    For more details ,please refer to  https://blog.sqlauthority.com/2017/02/13/sql-server-fix-error-19456-none-ip-addresses-configured-availability-group-listener-can-hosted-server/ 

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Thursday, October 10, 2019 3:20 AM
  • No, they are different ip addresses.

    Victor

    22 hours 23 minutes ago
  • Have you tried re-configuring the listener after removing the listener and adding the new replica to the availability group?

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    15 hours 41 minutes ago