If you are running, or going to run in the future, SQL Server inside Azure Virtual Machines, your favorite high availability solution (HA) is AlwaysOn Availability Groups (AG). Focusing on SQL Server 2012, why AG is or should be your favorite solution? Well, at least for these reasons:
If you want to review and/or compare all possible HA solutions, see the article below:
High Availability and Disaster Recovery for SQL Server in Windows Azure Virtual Machines
If you remember the days in last April when Azure IaaS went in GA, AlwaysOn AG technology was immediately supported, but with a “small” drawback: no support for AG Listener!!!
What did it mean? Practically, it was possible to configure a AlwaysOn AG for SQL Server 2012 inside VMs, but without automatic and transparent redirection on the application side: the nice workaround to this missing functionality was to use Mirroring connection string to “emulate”, on the application side, the AG failover behavior but obviously this worked only with two SQL Server VMs. Starting last July, Microsoft filled the gap and AG Listeners can be used, thanks to the hotfix below:
Update enables SQL Server Availability Group Listeners on Windows Server 2012-based Windows Azure virtual machines
I would like to emphasize that this is a Windows Server 2012 hotfix you have to install inside each Azure VM hosting an AG replica in the Cluster, not an Azure hotfix!
How does it work? In short, the Windows Server Cluster modified the logic of the classic “IP Address” resource, in order to respond to Azure Load Balancer (LB) custom probes and then redirecting the incoming traffic only to the active node/VM hosting the AG Primary replica of the database. That’s why the affected binary file is CLUSRES.DLL that my Cluster experts friends know very well J. If you want to learn more about Load Balancer, Custom Probes and Endpoints in Azure, look at the Michael Washam blog series on Azure IaaS at http://michaelwasham.com/category/iaas.
To give you a better understanding of this mechanism, look at how the clustered IP resource appears after the STEP(4) – POINT(8) in the second tutorial reported later in this post:
NOTE:It is worth noting that the static IP address assigned to the clustered IP address resource is static and the same as the Cloud Service public internet IP address containing the SQL Server VMs!
Now, let me report below the official announcement from the SQL Server blog, there is a nice picture I want to present you that I will use later in my notes and feedbacks:
AlwaysOn Availability Groups Fully Supported on Windows Azure Infrastructure Services
If you want to run over the entire configuration experience, you can follow the nice two tutorials reported below (PowerShell versions also exists), in this precise order:
After completing the second tutorial, this is how the AwaysOn AG Listener appears in SQL Server Management Studio:
Before running through the tutorials, I would recommend you to read my notes and feedbacks reported below, I’m sure this will save your precious time:
Read-Only Routing with SQL Server 2012 Always On Database Availability Groups
About Network Access Control Lists (ACLs)
$ApplicationCloudServiceIPsubnet = "<<<Public Cloud Service IP Address of your application>>>/32"
$ServiceName = "<<<cloud service name containing SQL Server VMs>>>"
$LBSetName = "<<<Load Balancer Set name used for AG Listener configuration"
$acl = New-AzureAclConfig
Set-AzureAclConfig –AddRule –ACL $acl –Order 100 –Action Permit `
–RemoteSubnet $ApplicationCloudServiceIPsubnet–Description "Remote App ACL config"
Set-AzureLoadBalancedEndpoint –ServiceName $ServiceName –LBSetName $LBSetName `
-Protocol tcp –LocalPort 1433 –PublicPort 1433 –ProbePort 59999 `-ProbeProtocolTCP -DirectServerReturn $true –ACL $acl
Network Access Control List Capability in Windows Azure PowerShell
Additionally, I want to provide you some warnings on the two tutorials mentioned above:
Failover Cluster Management snap-in crashes after you install update 2750149 on a Windows Server 2012-based failover cluster
Finally, let me bring to your attention an excellent resource on AlwaysOn Listener troubleshooting recently published:
Troubleshooting Availability Group Listener in Windows Azurehttp://msdn.microsoft.com/en-us/library/windowsazure/dn495646.aspx
That’s all folks, have fun with AlwaysOn in Windows Azure!