AlwaysOn – Availability Group Listener in a Multi-Subnet Cluster

availability-groupssql-server-2012

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?

Best Answer

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
Related Question