Azure, Open Source and ...

Configuring SQL Server AlwaysOn on Azure IaaS for SharePoint 2013


With recent announcement of support for SQL Server AlwaysOn on Azure IaaS, in this post I am going over the steps to configure a Listener for automatic failover of a SharePoint 2013 content database from a primary replica to a secondary.

The environment I used to complete the steps is running on Azure IaaS and consists of a domain controller, two WFE/Query servers, two App servers and two SQL servers.

As a prerequisite I configured Windows Clustering Service on both SQL machines (SQL1 & SQL2) and also created an AG called “MTC” with MTC-SQL1 as Primary and MTC-SQL2 as Secondary. I also added a sample content DB (called wss_contnet) to “MTC” AG: 


Next I installed KB2854082 on both SQL servers and used the latest Azure PowerShell  July 2013 on my client machine to successfully complete the steps.

Both SQL servers should be part of the same Cloud Service which should be different from other cloud services assigned to other client machines (i.e. all SharePoint servers).

To allow access through a listener I created a load-balanced endpoint with direct server return option for both MTC-SQL1 and MTC-SQL2 by running the following PS snippet:

   1: $AGServers = "MTC-SQL1","MTC-SQL2" 
   2: $ServiceName = "mtcsp" 
   3: $EndpointName = "Listener" 
   4: $EndpointPort = "9090" 
   5: ForEach ($node in $AGNodes) 
   6: { 
   7: Get-AzureVM -ServiceName $ServiceName -Name $node | Add-AzureEndpoint -Name $EndpointName -Protocol "TCP" -PublicPort $EndpointPort -LocalPort 1433 -LBSetName "$EndpointName-LB" -ProbePort 59999 -ProbeProtocol "TCP" -DirectServerReturn $true | Update-AzureVM 
   8: }

After successfully running the cmdlet the endpoints should look like this for both SQL servers:


I also added firewall rules to both SQL servers to allow inbound access through TCP port 59999 and 9090 for the listeners.

Next on the primary SQL (MTC-SQL1) I added a new Listener (SQLListener) by using the SSMS, accepting default settings and set the port to 9090. At this point you need to document “Cluster Network Name” and “IP Resource Name” by opening Failover Cluster Manager:


We also need to get the public IP address of  cloud service assigned to both SQL servers by going to Azure Portal, after gathering all the required parameters as last step I ran the following PS to assign the public IP address, the Probe Port, etc. to failover cluster:

   1: $ClusterNetworkName = "cluster network 2" 
   2: $IPResourceName = "MTC_10.5.160.101" 
   3:  $CloudServiceIP = "Your Public IP Address" 
   4:  Get-ClusterNetwork Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$CloudServiceIP";"ProbePort"="59999";SubnetMask="";"Network"="$ClusterNetworkName";"OverrideAddressMatch"=1;"EnableDhcp"=0}

To test and validate I reconfigured CLICONFG on all SharePoint servers to point to Public IP address and selected port (i.e. 9090), detached and re-attached the content db using the new SQL Alias and successfully did a failover from MTC-SQL1 to MTC-SQL2 using SSMS: