4

I'm running into some difficulty getting the AG Listener created for a 3 node cluster where node 3 is on a separate subnet.

I'm encountering an error that states

Create failed for Availability Group Listener 'AO-AGL'
None of the IP addresses configured for the availability group listener can be hosted by the server 
'NODE3'. 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. (Microsoft SQL Server, Error: 19456)

How do I handle this situation. This blog post (http://mohdsohail.wordpress.com/2013/02/14/adding-seconday-replica/) is the only thing I could find for reference and it isn't very clear on the solution. He states adding a second IP address. However, what exactly does this look like in DNS? Do I have one DNS hostname with two IP addresses?

4 Answers 4

4

To flesh out the accepted answer a little bit for anyone looking for code examples and order of operations, I had to:

  1. First create the Availability Group on the primary node

  2. Add a static subnet IP to the AG listener on the primary node.

  3. Connect to the secondary node and JOIN it to the Availability Group

  4. Connect BACK to the secondary node and add the second static subnet IP address (which belongs to the subnet of the secondary node) to the AG listener.

Here is a partial sample of the code showing the steps above (obviously with dummy IPs and node names):

:Connect PrimaryAGInstName

USE [master]

GO

CREATE AVAILABILITY GROUP [PrimaryAGInstName]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE [DBName]
REPLICA ON N'PrimaryAGInstName' WITH (ENDPOINT_URL = N'TCP://PrimaryAGInstName.Domain.Info:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),
    N'SecondaryAGInstName' WITH (ENDPOINT_URL = N'TCP://SecondaryAGInstName.Domain.Info:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));

GO

:Connect PrimaryAGInstName

USE [master]

GO

ALTER AVAILABILITY GROUP [PrimaryAGInstName]
ADD LISTENER N'AG_Listener_Name' (
WITH IP
((N'12.345.67.8', N'255.255.248.0')
)
, PORT=1919);

GO

:Connect SecondaryAGInstName

USE [master]

GO

ALTER AVAILABILITY GROUP [PrimaryAGInstName] JOIN;

GO


:Connect PrimaryAGInstName

USE [master]

GO

ALTER AVAILABILITY GROUP [PrimaryAGInstName]
ADD LISTENER N'AG_Listener_Name' (
WITH IP
((N'12.567.89.0', N'255.255.248.0')
)
, PORT=1919);
GO
2
2

Aside from the typical functions you need to perform when adding a new node to an Availability Group (creating an endpoint, adding a replica, joining the Availability Group etc.), there are two steps that are most relevant to adding a third node that is in a different subnet:

  1. Make sure that NODE3 is in the same failover cluster that NODE1 and NODE2 are on. A simple way is to run this on NODE1 from an Administrative Powershell console: Add-ClusterNode NODE3 -NoStorage. Once this is complete you should be able to see two cluster networks in failover manager - one for the subnet that NODE1 and NODE2 are on, and one for NODE3's subnet. This is crucial, as it allows you to add a listener IP on any of these subnets.

  2. Provision an IP from NODE3's subnet (let's say it's 10.10.10.10), and add it as a second listener IP using SSMS on NODE3: ALTER AVAILABILITY GROUP [MYAG] MODIFY LISTENER 'MYAGLISTENER' (ADD IP ('10.10.10.10', '255.255.255.0')). NOTE: No port is needed - it will use the port already established in the listener.

1

This error was resolved by adding a second IP to the listener. The IP address for both subnets cannot be an IP address already in use, i.e. the IP address of one of the nodes. A DNS entry is also not needed since the Listener creates the DNS entry as part of the process.

0
1

A more simplified resolution of this issue: AG Wizard First, Listener Second

Use the New Availability Group Wizard to create the AG group but purposely choose not to create a listener as shown in Figure A. After the Wizard has completed, then create the listener clicking Add Listener from the Availabilty Group Listeners context menu. Within the resulting GUI, simply add a static IP address for each subnet the AG group represents as shown in Figure B.

Figure A: In the AlwaysOn Wizard don't create the listener

enter image description here

Figure B: After the Wizard is configured (without the listener), THEN create the Listener with an IP address for each different subnet

enter image description here

Done!

p.s. It would be nice to simply add the listener during the execution of the Wizard but the Wizard GUI would not allow me to add Static IP addresses--hopefully this will change and become a one-step process.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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