Connect to AlwaysOn Listener in Multisubnet environment
In this article I want talk about a few points about AlwaysOn in Multisubnet environment. AlwaysOn can run on same subnets or on multi-subnet. On AWS (or GCP, Azure) there’s no option we had to deploy in Multi-subnet.
BTW, what’s the reason behind the unique requirements of deploying AlwaysOn on Multi subnet?
There are few published articles or blogs talking about this. Based on my experiences and tests, I’d like to share my thoughts here:
There’s protocol called ARP (Address Resolution Protocol). It was used between layer 2 Link Layer and layer 3 Internet Layer. Layer 2 has MAC address and layer 3 has IP address.
When we failover AlwaysOn one replica to another, ARP needs to broadcast to announce new primary’s MAC address ownership of the listener IP. However, for security reason, AWS VPC does not allow broadcast within it. So, we cannot deploy two replicas within the same subnets in AWS. Same are true for Goggle GCP or Azure.
Here’s the quick test. In order to do this test, you need two parts:
- SQLCMD Switches
- Connect to AG Listener in Multi subnet environment like AWS or GCP
First is the script we use to do the test. Please be noted in order to get the errors you had to remove the ‘-M’ switch, by doing so would turn Multisubnetfailover off.
Follow screenshot shows that after add ‘-M’ you will see the right output as bottom suggested. we run this and we can see the connections sometimes failed but quickly it reconnected:
That’s why we need to use -M in sqlcmd or use multisubnetfailover switch in application connection string like this:
Server=tcp:AGListener,1433;Database=dbname1;Integrated Security=SSPI; MultiSubnetFailover=True
I hope this article helps!
