The official SQL Server AlwaysOn team blog.
In this blog, you will learn how to configure AlwaysOn Availability Groups end-to-end using Windows Azure VMs. The steps here are the GUI near-equivalent of the script-based tutorial, which has been published at Tutorial: AlwaysOn Availability Groups in Windows Azure. The end-to-end test lab is designed to help you go through all the basic paces of deploying an end-to-end AlwaysOn scenario in Windows Azure VMs. There are some notable differences between on-premise HADR SQL Server deployments and Windows Azure VM deployments, which I have written in detail at SQL Server High Availability and Disaster Recovery in Windows Azure Virtual Machines. Hopefully, walking through the steps can help you identify these differences and avoid potential pitfalls.
Briefly, the steps in this blog demonstrate how to create an AlwaysOn Availability Groups deployment in Windows Azure VMs with the following characteristics:
The figure below is a graphical representation of the solution.
Below is the outline of the steps:
You begin with a new Windows Azure trial account with the Virtual Machine preview enabled. Once you have finished your account and preview feature sign-up, your Windows Azure portal should look similar to the following screenshot:
It may take some time for the storage account to be created. When the storage account is created, your Windows Azure portal will look similar to below:
Next, you need to give CORP\Install the necessary permissions for configuring Windows Service Failover Clustering (WSFC).
Next, you create the three VMs you will use which includes a WSFC cluster node and two SQL Server VMs. To create each of the VMs, go back to the Windows Azure portal, then click New, then Compute, then Virtual Machine, and then From Gallery.
In this section, you will create three more VMs in Windows Azure.
Operating system selection
Windows Server 2008 R2 SP1, December 2012
Microsoft SQL Server 2012 Evaluation Edition
Virtual Machine Name
New User Name
Connected to an existing virtual machine
Virtual Network Subnets
Create availability set (SQLHADR)
When you select Connect to an existing virtual machine and select ContosoDC (the only choice), it is the same as telling Windows Azure to place the VM in the same cloud service as ContosoDC, which is ContosoSQLsvc as I have specified it in my screenshot earlier. Again, this cloud service name is unique on the internet.
It does not take that much time to configure the three VMs, but be prepared to wait for a long time for all three VMs to be provisioned. Depending on the circumstances, it can take anywhere from a few hours to a full day for Windows Azure to fully provision three VMs.
Once the three VMs are fully provisioned, you need to join them to the corp.contoso.com domain and grant CORP\Install administrative rights to the machines. To do this, follow the steps below for each of the three VMs.
The address 10.10.2.4 is the address assigned to a VM in the 10.10.2.0/24 subnet in a Windows Azure virtual network, and that VM is ContosoDC. To verify ContosoDC's IP address, use the nslookup contosodc in the command prompt, as shown below.
In this section, you create the WSFC cluster that will host the availability group you will create later. By now, you should have done the following to each of the three VMs you will use in the WSFC cluster:
All these are prerequisites on each VM before you can join it to the WSFC cluster.
Also, note that the Windows Azure virtual network does not behave in the same way as an on-premise network, you need to create the cluster in the following order:
Finally, you are ready to move on. Follow the steps below to fully configure the cluster.
Failover Cluster Manager should now show that your cluster has three nodes and list them in the Nodes container, as shown below.
In this section, you will do the following on both ContosoSQL1 and contosoSQL2:
The actions above can be performed in any order, nevertheless the steps below will walk through them in order. Follow the steps for both ContosoSQL1 and ContosoSQL2:
You are now ready to configure an availability group. Below is an outline of what you will do:
Follow the instructions below:
Your AlwaysOn Dashboard should look similar to the one shown below. You can see the replicas, the failover mode of each replica and the synchronization state.
CAUTION: Do not try to fail over this clustered service. All failover operations should be performed from within AlwaysOn Dashboard in SSMS. For more information, see http://blogs.msdn.com/b/sqlalwayson/archive/2012/03/30/do-not-use-windows-failover-cluster-manager-to-perform-availability-group-failover.aspx.
The new availability group is now online, but it is still not yet accessible from the internet. If your database client, such as an IIS server, runs within the same Windows Azure virtual network, then it is able to connect to the database servers directly within the virtual network and no additional steps are required. However, if your database client runs elsewhere, such as in your corporation's private network, you must open endpoints in Windows Azure to allow database access.
Follow the steps below:
The private TCP port 1433 is the default port SQL Server uses to accept incoming remote connections. Note also that the public port 1 is actually opened on the cloud service ContosoSQLsvc which you created in the beginning, which is the only way to access the virtual machines. If you haven't noticed in the remote desktop connections, you have been connecting to different VMs by via the unique ports that Windows Azure has assigned to them within the cloud service.
You are done! You can remotely connect to these servers by pointing to the cloud service and the respective external port: ContosoSQLsvc.cloudapp.com:1 for ContosoSQL1 and ContosoSQLsvc.cloudapp.com:2 for ContosoSQL2.
Great article for such a detailed, step-by-step guide to setting up AlwaysOn AG on Windows Azure.
I went through the lab successfully, and I have 2 comments to bring up:
1. It took me a second try to get the domain controller ContosoDC to have the right IP (10.10.2.4). Now I don't exactly recall what may need to be changed from the guide, but it is likely in step #10 of section "Create Domain Controller Server" that I had to specify a unique DNS name (other than ContosoSQLSvc on the screen), and used Virtual Network instead of Affinity Group (ContosoAG)
2. Now I have this 3-node cluster/AG set up, I am wondering the role of the cluster node ContosoWSFCNode in this lab. According to the steps, after being added to the cluster, there is NO mentioning to that server anymore. Could someone please clarify?
The ContosoWSFCNode is there to provide a 3-vote quorum for the failover cluster so that if one node goes down, the rest of the nodes still have quorum (majority vote of 2 out of 3). Without this extra node, the 2-vote quorum would not survive a single-node failure because 1 out of 2 votes is not majority. So this extra node is there to help maintain the high availability of the cluster after a single-node failure.
Note that this node does not have SQL Server installed because it actually does nothing database-related.
Hi Cephas Lin,
how do we connect to the AG1 without an ip address. we are not looking at having any connections from the outside at this point.
At the moment, you cannot use the client to connect to the AG's IP address OR network name because the AG listener is not YET related. You can connect directly to the replicas using the replicas' hostnames or IP addresses. If you want to use AG listener, you'll need to wait for it to be supported some time later.
On section "Create Availability Group", step 27, mention to set the Readable Secondary option to "Yes" and the documentation here msdn.microsoft.com/.../jj870962.aspx says to set this option to "No". With "Yes", I got an error not allowing a client to change to the replica on primary failure. BTW, which value should take this option for the Primary role?
The above link talks about a patch available to make AG Listener support on Win 2012 server on Azure. Has anyone tried this to see if it works ?
Is there a way to configure a SQL Always On environment with a connection to VMs in a different cloud service?
I followed all the given steps, still in Create WSFC Cluster step, I am unable to add node WSFC node in cluster.
windows cluster freezes at “waiting for notification that node 'WSFCNode' is a fully functional member of the cluster” followed by 'unable to successfully cleanup' message.
Any pointer will be appreciated.
Thanks in advance.
It would be nice to have the same lab without the Azure instructions/advertisement.
I have the same problem, where the WSFCNode freezes when trying to connect to the cluster
I had the same issue adding the WSFCNode. I deleted and re created it with Windows 2012 r2 and then was able to add it with no issue.
Is it possible to update it now that Azure has allowed for a better experience for SQL allways on feature?