0

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.

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
2
  • Thanks for the clarification, however i still don't understand how the WSFC handles the switch between nodes, when the data center issues an IP address they tie it to a specific server/node, when a fail happens in node1, how can node2 handles traffic if clients connects to that IP that was issued for node1? could this be my problem?
    – Ahmed Galal
    Jul 29, 2017 at 18:15
  • @AhmedGalal I explain this in the listener switch section of the answer. The IP address will not come online if it belong to a different subnet than the node can host. If you look in the cluster log, you'll see a similar warning message. The ip that can be hosted is brought online and dns updated if possible. You'll want to explore the fundamentals of Windows Clustering Networking as it's completely outside of SQL Server. If you want to see the sequence of event, do a manual failover and look at the cluster log, it'll show the offline, move, and online of the resources.
    – Sean Gallardy
    Jul 30, 2017 at 18:14

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.