Finding the Primary Replica of an Availability Group

Hi mlwebster​ <

I think it may be best to have the SQL health check / monitoring done on the LTM side and let iquery status inform the GTM where to send the traffic.

You configure a custom MSSQL monitor on the local LTMs that queries the SQL Server's Dynamic Management Views (DMVs). This ensures the local VIP is only marked UP when its corresponding SQL server is the current Primary replica. When a failover occurs, the DC1 VIP goes down, the DC2 VIP comes up, and the GTM automatically directs traffic to DC2.

Would that scenario above work for you?  If so here are high level steps that should be tested in a non-prod environment first.  Be aware I have not tested this myself but from a monitor/iquery perspective this functionally should work and achieve what you want to do.  

Make sure the  monitor has s permission to query the cluster state. On your SQL servers, create a dedicated SQL login with the VIEW SERVER STATE permission. Ensure this login is created and synchronized across all replicas in the Always On Availability Group.

LTM Custom Monitor

On both the DC1 and DC2 LTMs, create a custom MSSQL monitor. This monitor will interrogate the local database to check its role.

• Type: MSSQL
• Database Name: master
• Send String: SELECT role_desc FROM sys.dm_hadr_availability_replica_states WHERE is_local = 1
• Receive String: PRIMARY
• User Name / Password: (The credentials you created in Step 1)

Apply this monitor to the LTM pools housing your SQL server nodes.

With this monitor in place, the mechanics work automatically:

1. While DC1 is the Primary replica, its monitor receives PRIMARY. The DC1 LTM marks the pool and VIP as UP.
2. DC2 is the Secondary, so its monitor receives SECONDARY (failing the health check). The DC2 LTM marks its VIP as DOWN.
3. Your GTM, communicating with both LTMs via the iQuery mesh (big3d), sees that only the DC1 VIP is available. It resolves the WIP to DC1.
4. During a failover: SQL in DC2 becomes Primary. The DC2 LTM monitor succeeds (bringing the VIP UP) and the DC1 monitor fails (bringing the VIP DOWN). The GTM instantly detects this state change and begins resolving the WIP to DC2.

The built-in F5 MSSQL monitor uses Java Database Connectivity (JDBC) under the hood. It works well for IPv4, but it is not compatible if your pool members are using IPv6 route domains. If you run into JDBC compatibility issues, you can easily replicate this logic using an External monitor type with a simple bash script invoking sqlcmd or tsql.

If you eventually want to direct reporting/read-only traffic to the secondary replica, you can create a second set of VIPs/WIPs with a monitor that expects SECONDARY as the Receive String.