Floating IP with SQL Always On

Question

Hi,

me setting up an environment where I have:

I was reading that I can create virtual machines on azure and use them for SQL Always On in conjunction with load balancer.

Doubts arose:

When I create a LISTENER in SQL Always On, every connection will be directed to the primary server. That's how I understood it in the tests I did recently. Great!

If the primary server becomes unavailable, the cluster will automatically make the secondary as primary and I started recording my data on the secondary, which will now be primary. And I will have access to all information that has been replicated.

When I use the simulated scenario in the Azure documentation with load balancer, I thought:

1 - This means that I will not use a LISTENER, as I will add the load balancer IP in (Service Name) and this will be my LISTENER.

So when I access the load balancer IP will user 1 have access to the data in the database that is on server A and user 2 will have access to the data in the database that is on server B? Will this base be synchronized in both directions?

That makes sense?

2 - Is it in this scenario that it is important to use the floating IP with SQL Always On?

Ref .: https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-load-balancer-portal-configure

Thank you.

Answer 1

When I create a LISTENER in SQL Always On, every connection will be directed to the primary server. That's how I understood it in the tests I did recently. Great!

Listener can direct connection to secondary replica as well when configured for read only routing. If not then yes listener directs connection to primary replica.

This means that I will not use a LISTENER, as I will add the load balancer IP in (Service Name) and this will be my LISTENER.

Listener is still needed but the question arise then why do you need Load Balancer in AG configured on Azure VM. I will answer that, the answer is perfectly explained in why-do-i-need-a-load-balancer-for-an-availability-group-listener-in-azure. In on-premises when WSFC does a failover the new primary replica node broadcasts the address of listener and registers it on its NIC so that it becomes clear that this new replica is now primary replica. Now coming to Azure, in azure you have VNETS which DOES NOT have broadcasting feature due to additional security and hence to overcome this feature Internal Load Balancer is required (ILB). This load balancer will have same IP as that of AG listener and application will connect using this IP. This load balancer will have Probe setup on certain port and this load balancer with help of probe and AG listener will know which node is primary replica and direct application to it. This process of having a ILB with probe port would not require a Broadcast hence it is used. I hope this is clear.

So when I access the load balancer IP will user 1 have access to the data in the database that is on server A and user 2 will have access to the data in the database that is on server B? Will this base be synchronized in both directions?

No, like you already know ILB with help of probe and AG listener will direct connection to primary replica and only this replica will have database in read/write mode.

Is it in this scenario that it is important to use the floating IP with SQL Always On?

Floating IP is nothing but an IP which can be binded with multiple nodes( its floating between all replicas in AG). In this case ILB IP is floating because it has to access and connect with all backend pools, basically all the replicas IP participating in AG.

Does that answers your question ?

Answer 2

Hi @Salves ,

> This means that I will not use a LISTENER, as I will add the load balancer IP in (Service Name) and this will be my LISTENER.

No, we still need to configure availability group listener. But the listener IP address as the same address of load balancer.

Please refer to Tutorial: Configure a SQL Server availability group on Azure Virtual Machines manually.

> Is it in this scenario that it is important to use the floating IP with SQL Always On?

Yes. If you're using an Azure virtual machine for the SQL server, enable floating IP.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.