Problem with Listener Name in Azure Availability Group

I was recently working with a client to migrate a standalone instance of SQL Server on an Azure VM to a new High Availability solution with two new instances, again on Azure VMs, with databases hosted in an Availability Group.

Once everything had been set up, with the AG created and databases synchronising, it was time to turn to the Listener Name. The procedure for creating this for an AG in Azure differs from the standard approach in that an Azure Load Balancer is required instead of it just being created as a Virtual Network Name in AD and an IP address in DNS. Joshua Feirman has a good article here in which he gives an overview of what a Load Balancer is and does. In essence though ‘due to limitations in how the Azure platform handles address routing, clients cannot use the virtual IP address to connect to the replica …. at least not without some involvement from a load balancer resource’.

Once the Load Balancer and its associated technologies – Front-end IP address, Back-end pool, Probes and Load balancing rules – had been created an odd problem manifested itself in that, when re-connecting to the Listener after a failover, it seemed that it was still the previous primary (now secondary) whose databases were displayed and these were obviously inaccessible when non-readable. After much tearing out of hair (of which I have precious little) I traced the problem to the Floating IP property in the Load Balancing rules, which should be enabled but wasn’t in this case (my bad). I still don’t quite understand why this was having the effect it was – still looking into that – but I did come across this article which states, in relation to the Load Balancing rules, that ‘If we choose any value other than what [was stipulated], you would get connectivity issue [sic] from passive/other nodes.’. Which was what I was seeing.