3 Keys to Configuring Azure Virtual Machines for Use in SQL Server Availability Groups

Author by Concurrency Blog

One of the great features of SQL Server AlwaysOn Availability Groups is that they can span multiple data centers – including Azure. You can create an Availability Group with all replicas on-premises, all replicas in Azure, or a combination of the two.

If you’re going to use Azure for one or more of your replicas, you’ll need to start with creating Azure Virtual Machines. I’ve given you some tips on creating those successfully, but when planning to use them in an Availability Group, there are more considerations.

  1. Choose your Domain Name carefully. The domain name is also known as the “cloud service” or “service name”. This service, or name, puts VMs (and other Azure resources) into a logical group. It is necessary for SQL Servers that will be replicas in the same Availability Group to have the same domain name. This can’t be changed – if you put the VM in the wrong domain, you must drop it and re-create it. Note: domain name/service name/cloud service is going away, to be replaced with the resource group as part of the “Resource Manager Stack”.  In this model, you’ll also need to place the related VMs in the same “resource group”.
  2. After the virtual machine is created, you’ll have to add the Failover Cluster role and create a Windows Server Failover Cluster. After you’ve done that, for each node that is an Azure VM with DHCP, you’ll have to set a static IP address. To do so: in Failover Cluster Manager, click the cluster name in the left pane. Scroll down to the Cluster Core Resources section. Expand the cluster name. Double-click the IP address. Under IP address, choose Static IP Address and set an IP. Click OK.
  3. If you plan to have a multi-subnet Availability Group, you need to create a load-balanced endpoint. An Internal Load Balancer can be created and configured in PowerShell. There is a charge for the ILB on a monthly basis - $.0.004/hour – roughly $3.00/month.  If the replicas are in different Azure subnets, you’ll need to create one for each subnet. After creating the ILB, you need to link it to each VM that will use it by creating an endpoint on the VM. This, too, is done in PowerShell. Instructions can be found here. Another important note in that article is that you must then open the endpoint ports in the firewall. If you miss that step, you’re going to have problems using the Listener!

Preparation is the key to a successful Availability Group deployment. This is even more important when using Azure VMs, due to some of the networking features. Make sure you understand all the pieces you’ll need and plan for them!

 

Author

Concurrency Blog

The latest about Concurrency