DEPLOYING MICROSOFT SQL SERVER 2017 FAILOVER CLUSTERS IN AZURE RESOURCE MANAGER (ARM)

Before beginning, make sure you read the DataKeeper Cluster Edition Release Notes for the latest information. It is highly recommended that you read and understand the DataKeeper Cluster Edition Installation Guide.

Steps required to deploy a 2-node SQL Server Failover Cluster in a single region using Azure Resource Manager

Note: This guide does not apply to the Azure Classic portal.

DataKeeper Cluster Edition allows you to take locally attached storage, whether it uses Premium or Standard Disks, and replicate those disks synchronously, asynchronous, or a mix or both, between two or more cluster nodes. In addition, a DataKeeper Volume resource is registered in Windows Server Failover Cluster which takes the place of a Physical Disk resource. Instead of controlling SCSI-3 reservations like a Physical Disk Resource, the DataKeeper Volume controls the mirror direction, ensuring the active node is always the source of the mirror. For SQL Server and Failover Cluster the DataKeeper volume is similar to a Physical Disk and is used the same way Physical Disk Resources would be used.

PREREQUISITES

  • You have used the Azure Portal (http://portal.azure.com) before and are comfortable deploying virtual machines in Azure IaaS

THE EASY WAY TO DO A PROOF-OF-CONCEPT

The Azure Resource Manager has the ability to use Deployment Templates to rapidly deploy applications consisting of interrelated Azure resources. Many of these templates are developed by Microsoft and are readily available in their community on Github as Quickstart Templates. Community members are also free to extend templates or to publish their own templates on GitHub. One such template entitled “SQL Server 2014 AlwaysOn Failover Cluster Instance with SIOS DataKeeper Azure Deployment Template” published by SIOS Technology completely automates the process of deploying a 2-node SQL Server FCI into a new Active Directory Domain.

To deploy this template click on Deploy to Azure in the template.

Refer to https://github.com/SIOSDataKeeper/SIOSDataKeeper-SQL-Cluster to rapidly provision a 2-node SQL cluster.

DEPLOYING A SQL SERVER FAILOVER CLUSTER INSTANCE USING THE AZURE PORTAL

While the automated Azure deployment template is a quick and easy way to get a 2-node SQL Server FCI up and running, there are some limitations. For one, it uses a 180 Day evaluation version of SQL Server, so it cannot be used in production unless you upgrade the SQL eval licenses. Also, it builds an entirely new AD domain so if you plan to integrate it with your existing domain it will have to be rebuilt manually.

PROVISIONING THE DOMAIN CONTROLLER (DC1)

To build a 2-node SQL Server Failover Cluster Instance in Azure, you will need a basic Virtual Network based on Azure Resource Manager (not Azure Classic) and at least one virtual machine up and running configured as a Domain Controller. This guide does not cover these steps. We will refer to the domain controller as DC1 for the rest of this guide. When creating DC1 you may choose either Windows Server 2008R2 or Windows Server 2012R2. The only other requirements for DC1 are that it be the same type (Premium or Standard) as the cluster nodes, SQL1 and SQL2, and be in the same Availability Set. Once the Virtual Network and Domain Controller are configured, you will provision two more virtual machines which will act as the two nodes in the cluster.

Example:

DC1 – Our Domain Controller and File Share Witness

SQL1 and SQL2 – The two nodes of our SQL Server Cluster

PROVISIONING THE TWO CLUSTER NODES (SQL1 AND SQL2)

Using the Azure Portal, provision both SQL1 and SQL2 exactly the same way. There are numerous options to choose from including instance size, storage options, etc. This guide is not meant to be an exhaustive guide to deploying SQL Server in Azure. There are a few key things to keep in mind when creating your instances, especially in a clustered environment.

Availability Set – It is important that both SQL1, SQL2, and DC1 reside in the same Availability Set. By putting them in the same Availability Set we are ensuring that each cluster node and the File Share Witness reside in different Fault and Update Domains. This helps guarantee that during both planned maintenance and unplanned maintenance the cluster will continue to be able to maintain quorum and avoid downtime.

STATIC IP ADDRESS

Once each VM is provisioned, change the settings of the IP address to Static so that the IP addresses of the cluster nodes will not change.

STORAGE

For Storage information refer to Performance best practices for SQL Server in Azure Virtual Machines. At a minimum add at least one additional disk to each of your cluster nodes. DataKeeper can use Premium or Standard disks, but Azure requires you to configure data disks to use the same type as the OS disk. If you created VMs that reside on Premium disks, then you must attach Premium data disks as well. DataKeeper is compatible with Storage Pools so you may attach multiple data disks if your chosen VM size allows it.

CREATE THE CLUSTER

After both cluster nodes (SQL1 and SQL2) have been provisioned as described above and added to your existing domain, the cluster can be created. Before creating the cluster, both the appropriate .NET framework and Failover Clustering Features must be enabled on both cluster nodes.

Once these features have been enabled, you are ready to build your cluster. The following steps can be performed both via PowerShell and the WSFC GUI. It is recommended that PowerShell be used to create your cluster. Note: If the Failover Cluster Manager GUI is used, a duplicate IP address will be issued to the cluster that is not attached.

Azure VMs are required to use DHCP. By specifying a “Static IP” in the Azure portal when the VM was created, something similar to a DHCP reservation was established. It is not exactly a DHCP reservation because a true DHCP reservation removes the IP address from the DHCP pool. Instead, specifying a Static IP in the Azure portal means that if that IP address is still available when the VM requests it, Azure will issue that IP to it. However, if your VM is offline and another host comes online in that same subnet it could be issued that same IP address.

There is another side effect to the way Azure has implemented DHCP. When creating a cluster with the Windows Server Failover Cluster GUI, when a host uses DHCP (which is required), there is not an option to specify a cluster IP address. Instead it relies on DHCP to obtain an address. DHCP will issue a duplicate IP address, usually the same IP address as the host requesting it. The cluster creation will usually complete, but you may encounter errors and need to run the Windows Server Failover Cluster GUI from a different node in order to get it to run. Once it is running, change the cluster IP address to an address that is not currently in use on the network.

To avoid this scenario, create the cluster via PowerShell by specifying the cluster IP address as part of the PowerShell command.

To create the cluster run the following New-Cluster command:

New-Cluster -Name cluster1 -Node sql1,sql2 -StaticAddress 10.0.0.101

After the cluster is created, run the following cluster validation:

Test-Cluster

CREATE FILE SHARE WITNESS

Since there is no shared storage, you must create a file share witness on another server in the same Availability Set as the two cluster nodes. Putting it in the same availability set ensures that you only lose one vote from your quorum at any given time. Refer to http://www.howtonetworking.com/server/cluster12.htm for information on how to create a File Share Witness. For this example, the file share witness was put on the domain controller, DC1. For more information on cluster quorums refer to https://blogs.msdn.microsoft.com/microsoft_press/2014/04/28/from-the-mvps-understanding-the-windows-server-failover-cluster-quorum-in-windows-server-2012-r2/.

INSTALL DATAKEEPER

During the installation use all of the default options.

The service account used must be a domain account, and must also be in the Local Administrators group on each node in the cluster.

Reboot the servers once DataKeeper is installed and licensed on each node.

CREATE THE DATAKEEPER VOLUME RESOURCE

To create the DataKeeper Volume Resource start the DataKeeper UI and connect to both of the servers.

Connect to SQL1

Connect to SQL2

Once you are connected to each server, create your DataKeeper Volume in the Navigation Pane, right click on Jobs and choose Create Job.

Give the Job a name and description.

Choose your source Server, IP and Volume. The IP address chosen will determine the replication network.

Choose your target server.

Choose your options. If two VMs are in the same geographic region we recommend using synchronous replication. For long distance replication we recommend using asynchronous replication with some level of compression. Since both SQL1 and SQL2 are in the same region, select Synchronous here.

Click Yes to register a new DataKeeper Volume Resource in Available Storage in Failover Clustering.

The new DataKeeper Volume Resource will appear in the Available Storage cluster group.

INSTALL THE FIRST CLUSTER NODE

You are now ready to install your first node. The cluster installation will proceed the same as any other SQL cluster. Start the installation on the first cluster node using the New SQL Server failover cluster installation option.

The DataKeeper Volume Resource is recognized as an available disk resource, just as if it were a shared disk.

Make a note of the IP address you select here. It must be a unique IP address on your network. This IP address will be used later when creating the Internal Load Balancer.

ADD THE SECOND NODE

After the first node is installed successfully, start the installation on the second node using the Add node to a SQL Server failover cluster option.

CREATE THE INTERNAL LOAD BALANCER

The failover clustering in Azure is different than traditional infrastructures. The Azure network stack does not support gratuitous ARPS, so clients cannot connect directly to the cluster IP address. Instead, clients connect via a load balancer resource which redirects them to the active cluster node. Thus an Internal Load Balancer must be created. This can all be done through the Azure Portal as shown below.

A Public Load Balancer can be used if your client connects over the public Internet. Since our clients reside in the same vNet, we will create an Internal Load Balancer. It is important that the Virtual Network is the same as the network where your cluster nodes reside. The Private IP address that you specify must be EXACTLY the same as the address you used to create the SQL Cluster Resource.

After the Internal Load Balancer (ILB) is created add a backend pool. Through this process you will choose the Availability Set where your SQL Cluster VMs reside. However, when you choose the actual VMs to add to the Backend Pool, be sure not to choose the VM hosting your file share witness, DC1. You do not want to redirect SQL traffic to your file share witness.

The next step is to add a Probe. The probe we add will probe Port 59999. This probe determines which node is active in our cluster.

Finally, a load balancing rule is needed to redirect the SQL Server traffic. A Default Instance of SQL uses port 1433. You can add rules for 1434 or others depending upon your application requirements. It is very important that Floating IP (direct server return) is Enabled.

FIX THE SQL SERVER IP RESOURCE

The final step in the configuration is to run the following PowerShell script on one of your cluster nodes. This will allow the Cluster IP Address to respond to the ILB probes and ensure that there is no IP address conflict between the Cluster IP Address and the ILB. Note: You will need to edit this script to fit your environment. The subnet mask is set to 255.255.255.255, this is not a mistake, leave it as is. This creates a host specific route to avoid IP address conflicts with the ILB.

# Define variables

$ClusterNetworkName = “”

# the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 of higher to find the name)

$IPResourceName = “”

# the IP Address resource name

$ILBIP = “”

# the IP Address of the Internal Load Balancer (ILB)

Import-Module FailoverClusters

# If you are using Windows Server 2012 or higher:

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

# If you are using Windows Server 2008 R2 use this:

#cluster res $IPResourceName /priv enabledhcp=0 address=$ILBIP probeport=59999 subnetmask=255.255.255.255

Feedback

Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Post Comment