SQL Server 2012 / 2014 Always-on in Microsoft Windows Azure

We have heard enough of the new feature in SQL Server 2012 that replaces our conventional Database Mirroring notion. It is the Always-on Availability groups feature. I have carried out several tests setting up Always-on on my virtual data-center in my laptop. One fine day at office, I was assigned a project to build an Always-on availability group in Microsoft Azure.

Let’s first go through the prerequisites.

1. Availability group replicas must reside in a single Cloud service.
2. Availability group Listener IP should be same as the public Cloud service IP assigned to each replica.
3. Two endpoints has to be created for the Cloud Service in Azure dashboard. One for the probe port(Through which the Azure Load Balancing services monitors the health of each virtual machine) and one for Always-on Listener port. Probe port should be different from the Listener port. For eg. Give 59999 for Probe port and 1433 for the always-on listener port.
4. Install the hotfix 2854082 to enable SQL Server Availability Group Listeners on Windows Server 2008 R2 and Windows Server 2012-based Microsoft Azure virtual machines.

Limitations in Azure:

1. Each virtual machine in Azure could have only one Ethernet card and one private IP. This limitation stops us from using a private IP for Always-on availability group. Even if we give a private IP for the listener, the IP will not ping or connect over the network.
2. The client application must reside on a different cloud service than the one that contains your availability group VMs. Azure does not support direct server return with client and server in the same cloud service.
3. Only one availability group listener is supported per cloud service because the listener is configured to use the cloud service IP address.
4. If you are creating a listener for a hybrid environment, the on-premises network must have connectivity to the public Internet in addition to the site-to-site VPN with the Azure virtual network. When in the Azure subnet, the availability group listener is reachable only by the public IP address of the respective cloud service. Ref: http://msdn.microsoft.com/en-us/library/dn425027.aspx

I went ahead and started working on it. I asked for 4 virtual servers.

1. Configured one as the Domain Controller (sherbaz.com), AD and DNS. Named it ALWAYSONAD.sherbaz.com
2. Added the 3 remaining servers to the domain. They were named WSFC.sherbaz.com, REPLICA-1.sherbaz.com and REPLICA-2.sherbaz.com.
3. Created hostname/IP (VWSFC/any available IP address) in the DNS configured on ALWAYSONAD.
4. Configured windows failover cluster on WSFC with the virtual name and IP created in Step 3.
5. Added nodes REPLICA-1 and REPLICA-2 to the failover cluster.
6. Installed standalone SQL Server default instances on REPLICA-1 and REPLICA-2
7. Created a test database on REPLICA-1. Took full backup+log backup. Restored them on to REPLICA-2 instance in no-recovery mode.
8. Configure an availability group with any name (For eg. alwayson) adding the test database.
9. Configure Listener with any virtual name (For eg. valwayson) and available private IP (Just for time being. The cluster admin and the SSMS does not allow the use of public IP. We will change the IP to the public cloud service IP in the next step).
10. Run below command in Powershell to change the IP given in step 9 to the public IP.

    # Define variables
    $ClusterNetworkName = "" # the cluster network name
    $IPResourceName = "" # the IP Address resource name 
    $CloudServiceIP = "X.X.X.X" # IP address of your cloud service
    Import-Module FailoverClusters
    
    # In this blog post, we used "Cluster Network 1" as the Cluster Network Name since we had only one subnet.
    # IPResourceName is the always-on listener network resource name
    # as displayed in cluster administrator GUI (Right click on the Network resource name in Always-on group/role
    # in cluster administrator GUI).
    
    # If you are using Windows 2012 or higher, use the Get-Cluster Resource command below.
    # If you are using Windows 2008 R2, use the cluster res command. Both commands are commented out.
    # Choose the one applicable to your environment and remove the # at the beginning of the line to
    # convert the comment to an executable line of code. 
    
    # Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$CloudServiceIP";"ProbePort"="59999";SubnetMask="255.255.255.255";"Network"="$ClusterNetworkName";"OverrideAddressMatch"=1;"EnableDhcp"=0}
    # cluster res $IPResourceName /priv enabledhcp=0 overrideaddressmatch=1 address=$CloudServiceIP probeport=59999  subnetmask=255.255.255.255
    

You could now open up a SQL Server Management Studio and try connecting to the Public IP assigned to always-on listener. If connected, you are good to go. I will soon post some screenshots. 🙂

References:

• http://blogs.msdn.com/b/sqlalwayson/archive/2013/08/06/availability-group-listener-in-windows-azure-now-supported-and-scripts-for-cloud-only-configuration.aspx