SQL Server – Add New Subnet to Availability Groups

availability-groupssql server

I have a two node Availability Group system which hosts several AvailabilityGroups. Each AG has it's own listener.

The system was set up in an IP-subnet with very few free addresses left. How can i create new AvailabilityGroups in a new subnet?

Best Answer

I assume that you do not want to setup a multi-subnet clustering where each node is setup on a different subnet.

To be able to add a listener on a new subnet in the cluster the cluster computers need to be on that subnet sou you will need to add ip addresses on the new subnet to the existing network adapters on the servers (or install dedicated ones) and then you can add ip address on that subnet to the Availability Group. Then you will need to setup routing so other computers can connect to the listener or have the computers on that will connect to the listener on that subnet as well.

Depending on your network infrastructure and routing setup it might even be simpler to change the subnet mask to get more ip addresses. Going from 10.0.12.0/24 to 10.0.12.0/23 (mask: 255.255.254.0) would give you 256 more ip addresses to play with.

Related Solutions

AlwaysOn – Availability Group Listener in a Multi-Subnet Cluster

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):

: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

Sql-server – SQL Server 2014 AG Listener

Creating a listener creates (permissions must allow, otherwise the entry will be made by the network team) a DNS entry, and has a dependency to an IP address. It logically follows that each listener needs its own IP address, because how else will traffic know where to be directed?

See Failover Cluster Manager>[Cluster Name]>Services and applications> [Availability Group name]. Under Server Name section, the listener name is displayed and shows the IP Address underneath, which is reflected as a Dependencies entry if you open the properties of the listener name.

If you find a way around it, I'd sure like to know, because it doesn't make sense to me that it would work. Good luck!

Best Answer

Related Solutions

AlwaysOn – Availability Group Listener in a Multi-Subnet Cluster

Sql-server – SQL Server 2014 AG Listener

Related Question