patternMinor
AlwaysOn - Availability Group Listener in a multi-subnet cluster
Viewed 0 times
multigrouplisteneravailabilitysubnetalwaysoncluster
Problem
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
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?
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?
Solution
To flesh out the accepted answer a little bit for anyone looking for code examples and order of operations, I had to:
-
First create the Availability Group on the primary node
-
Add a static subnet IP to the AG listener on the primary node.
-
Connect to the secondary node and JOIN it to the Availability Group
-
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):
-
First create the Availability Group on the primary node
-
Add a static subnet IP to the AG listener on the primary node.
-
Connect to the secondary node and JOIN it to the Availability Group
-
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);
GOCode Snippets
: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);
GOContext
StackExchange Database Administrators Q#80671, answer score: 4
Revisions (0)
No revisions yet.