Load Balancer SQL, front end give IP address to all servers and is not possible to failover.

[hcljunior]

I have one Load Balancer with a frontend IP and twoo servers with SQL Always ON in backend.

The IP from front end are in both servers and I can not failover SQL.

Can some help me about this failover? I want to the IP of frontend need to be on the server that have the SQL, the primary server.

[DarwinVinoth]

You have:

A Google Cloud Load Balancer with a frontend IP.

Two backend SQL Always On servers.

The frontend IP is reachable on both servers, but failover is not working because the traffic is not automatically routing to the current primary SQL server.

✅ Important: Key Concept in GCP
In GCP, the frontend IP belongs to the load balancer, not to the backend VMs directly.

Google Cloud Load Balancer does not track SQL Always On state natively.
Failover logic needs to be managed via custom health checks tied to the SQL Always On primary node.

✅ Recommended Solution for GCP SQL Always On Failover:
You need to:

Use a TCP Load Balancer (External or Internal) in GCP.

Configure a custom TCP health check that specifically identifies the primary SQL server.

[hcljunior]

I am using TCP Load Balacer internal.

I have health check configured on Load Balancing, but all backend servers are receiving the IP adress from frontend configuration.

It is the problem, all instances on Load Balance are receiving this IP from frontend. If I imput another instance, number 3, this instance receive this IP too.

[DarwinVinoth]

You mean all the backed sql servers getting the traffic from front end ? 

[hcljunior]

Not, because the SQL has just one primary. But the heathcheck and IP are in both.

The secondary just recive the IP, but not comunicate with applications. And in SQL to do a Failover get problem because of the duplicate IP from frontend in both.