Creating a two node SQL Failover Cluster on Azure

With the addition of S2D to Windows Server 2016, it is now possible to run a two node SQL 2016 Server failover cluster in Azure or any platform without the need for shared storage. Previously this was only possible by utilising a third party tool such as SIOS DataKeeper. Typically we would deploy an environment like this using RM templates but for demo proposes I will step through manually.

Deploy two Server 2016 Nodes, remember to the create the two VMs in the same availability set. It is recommended that at least two data disks are added per node (don't format the data disks).

Add the machines to the domain

Setting up the Servers

Install the Failover Cluster tools

$nodes = ("evDB01","evDB02") Invoke-Command $nodes {Install-WindowsFeature Failover-Clustering -IncludeAllSubFeature -IncludeManagementTools}

With the tools installed we can validate the setup and create a very basic cluster don't add storage at this stage. There is slight flaw when setting up a cluster manually in Azure, the cluster is automatically given the IP address of the NIC on the first machine, effectively knocking it out of the cluster. You have to connect to the other node, fire up cluster manager and manually change the cluster IP.

Base Cluster

As its Server 2016 and we are using Azure I am going to utilise a cloud witness.

Set-ClusterQuorum -CloudWitness -AccountName storageaccountname -AccessKey storageaccountaccesskey

Shared Storage

Not we can enable S2D and configure the shared storage

On both nodes run the command: Enable-ClusterS2D
On one of the nodes I will then create 2 virtual disks:
New-Volume -StoragePoolFriendlyName S2D* -FriendlyName VDisk01 -FileSystem CSVFSREFS -Size 100GB
New-Volume -StoragePoolFriendlyName S2D* -FriendlyName VDisk02 -FileSystem CSVFS
REFS -Size 100GB

These disks are accesible via the paths:
C:\ClusterStorage\Volume1 C:\ClusterStorage\Volume2

We can now utilise this disks during the SQL Cluster install.

Installing SQL

Start the Cluster install on the first node this will install all the required SQL binaries. During the install has select the two volumes above as the cluster storage. The cluster install will require a SQL Name and a unique IP. Once completed add thes second node to the cluster.

Load Balancer

The clusters IP addresses don't actually connect to the vNET you have to utilise an internal load balancer to achieve this. The load balancer should be added to the same vNET and subnet as the DB servers. You also need to stimulate the same static IP and used for the SQL cluster name in the step above.

Backend Pool: add the VMs to the pool

Health Probe: Create a probe pointed at Port 59999

Rules: Port and Backend Port 1433, Point at the health probe created above, No session persistance and Enabled Floating IP (direct server return)

Probe

The last piece of the puzzle is too enable the probe response on the VMs.

$ClusterNetworkName = "Cluster Network 1" $IPResourceName = "SQL IP Address 1 (evDBCluster)" $ILBIP = "10.10.0.92" [int]$ProbePort = 59999

Import-Module FailoverClusters

Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ILBIP";"ProbePort"=$ProbePort;"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}

Once completed stop and SQL role in the cluster and start it again.

You should now have a functional two node cluster. Remember to test before using this type of setup in production.