Sql-server – SQL Server 2016 Always ON high availability group listener configuration

availability-groupssql serversql-server-2016

I have 2 servers running Windows Server 2016 with Microsoft SQL Server 2016 Enterprise. I have created a fail over cluster in Windows and joined both nodes without Active Directory. Then I created an availability group with both nodes and everything is fine and the database is synchronized.

Now I created the AG listener, I am not using DHCP, so I assigned a static IP. What I don't understand is how the fail over is going to work; when I connect to the listener everything is fine when Node1 is primary, when I do manual failover to Node2, I can't connect.

How will the listener switch to node2? Should the listener have 2 IPs for both nodes?

BTW, the servers are not on a private LAN.

Best Answer

What I don't understand is how the fail over is going to work

This is documented in a high level in Microsoft Docs.

when I connect to the listener everything is fine when Node1 is primary, when I do manual failover to Node2, I can't connect.

That depends on your connection string, DNS infrastructure, and availability group setup.

If it's a single subnet, there shouldn't be an issue. If it's multi-subnet, you'll want to instruct the client driver to resolve all ips in parallel using the MultiSubnetFailover=true keyword in older version of .net/java/php/etc. Make sure you're using an updated client driver version that supports said keyword.

There are additional windows clustering private property settings for net names that can influence how these things work, but it doesn't sound like you've made any manual adjustments to these (defaults used) and I won't muddy the waters with it but did want you to be aware that there are additional settings.

How will the listener switch to node2?

This happens at the WSFC level. A failure is detected or SQL Server initiates a failover which moves the resource group (and all contained resources) logically to the new node (where ever and which ever that may be). The resources are then attempted to come online. Part of the process to come on-line for a network name (listener) is to have at least one dependent IP address online and register with DNS among other items. This may or may not be the same IP Address based on how your networking is setup and the location of the new node owning the resources.

Should the listener have 2 IPs for both nodes?

The listener needs at a minimum an IP Address for each subnet it could possibly be online in. It may have 1, 2, 3, ... 7, etc., depending on your specific setup and infrastructure.

Related Question