S2D supports two types of architectures - converged and hyper-converged. The architecture in this document is hyper-converged. A hyper-converged infrastructure places the storage on the same servers that host the clustered application. In this architecture, the storage is on each SQL Server FCI node.

Licensing and pricing

On Azure Virtual Machines you can license SQL Server using pay as you go (PAYG) or bring your own license (BYOL) VM images. The type of image you choose affects how you are charged.

With PAYG licensing, a failover cluster instance (FCI) of SQL Server on Azure Virtual Machines incurs charges for all nodes of FCI, including the passive nodes. For more information, see SQL Server Enterprise Virtual Machines Pricing.

Customers with Enterprise Agreement with Software Assurance have the right to use one free passive FCI node for each active node. To take advantage of this benefit In Azure, use BYOL VM images and then use the same license on both the active and passive nodes of the FCI. For more information, see Enterprise Agreement.

Example Azure template

You can create the entire solution in Azure from a template. An example of a template is available in the GitHub Azure Quickstart Templates. This example is not designed or tested for any specific workload. You can run the template to create a SQL Server FCI with S2D storage connected to your domain. You can evaluate the template, and modify it for your purposes.

Before you begin

There are a few things you need to know and a couple of things that you need in place before you proceed.

What to know

You should have an operational understanding of the following technologies:

One important difference is that on an Azure IaaS VM guest failover cluster, we recommend a single NIC per server (cluster node) and a single subnet. Azure networking has physical redundancy which makes additional NICs and subnets unnecessary on an Azure IaaS VM guest cluster. Although the cluster validation report will issue a warning that the nodes are only reachable on a single network, this warning can be safely ignored on Azure IaaS VM guest failover clusters.

Additionally, you should have a general understanding of the following technologies:

At this time, the SQL Server IaaS Agent Extension is not supported for SQL Server FCI on Azure. We recommend that you uninstall the extension from VMs that participate in the FCI. This extension supports features, such as Automated Backup and Patching and some portal features for SQL. These features will not work for SQL VMs after the agent is uninstalled.

What to have

Before following the instructions in this article, you should already have:

A Microsoft Azure subscription.

A Windows domain on Azure virtual machines.

An account with permission to create objects in the Azure virtual machine.

An Azure virtual network and subnet with sufficient IP address space for the following components:

Both virtual machines.

The failover cluster IP address.

An IP address for each FCI.

DNS configured on the Azure Network, pointing to the domain controllers.

With these prerequisites in place, you can proceed with building your failover cluster. The first step is to create the virtual machines.

Step 1: Create virtual machines

The availability set groups virtual machines across fault domains and update domains. The availability set makes sure that your application is not affected by single points of failure, like the network switch or the power unit of a rack of servers.

If you have not created the resource group for your virtual machines, do it when you create an Azure availability set. If you're using the Azure portal to create the availability set, do the following steps:

In the Azure portal, click + to open the Azure Marketplace. Search for Availability set.

Click Availability set.

Click Create.

On the Create availability set blade, set the following values:

Name: A name for the availability set.

Subscription: Your Azure subscription.

Resource group: If you want to use an existing group, click Use existing and select the group from the drop-down list. Otherwise choose Create New and type a name for the group.

Location: Set the location where you plan to create your virtual machines.

The official SQL Server images in the Azure Gallery include an installed SQL Server instance, plus the SQL Server installation software, and the required key.

Choose the right image according to how you want to pay for the SQL Server license:

Pay per usage licensing: The per-second cost of these images includes the SQL Server licensing:

SQL Server 2016 Enterprise on Windows Server Datacenter 2016

SQL Server 2016 Standard on Windows Server Datacenter 2016

SQL Server 2016 Developer on Windows Server Datacenter 2016

Bring-your-own-license (BYOL)

{BYOL} SQL Server 2016 Enterprise on Windows Server Datacenter 2016

{BYOL} SQL Server 2016 Standard on Windows Server Datacenter 2016

Important

After you create the virtual machine, remove the pre-installed standalone SQL Server instance. You will use the pre-installed SQL Server media to create the SQL Server FCI after you configure the failover cluster and S2D.

Alternatively, you can use Azure Marketplace images with just the operating system. Choose a Windows Server 2016 Datacenter image and install the SQL Server FCI after you configure the failover cluster and S2D. This image does not contain SQL Server installation media. Place the installation media in a location where you can run the SQL Server installation for each server.

After Azure creates your virtual machines, connect to each virtual machine with RDP.

When you first connect to a virtual machine with RDP, the computer asks if you want to allow this PC to be discoverable on the network. Click Yes.

If you are using one of the SQL Server-based virtual machine images, remove the SQL Server instance.

After the virtual machines are created and configured, you can configure the failover cluster.

Step 2: Configure the Windows Failover Cluster with S2D

The next step is to configure the failover cluster with S2D. In this step, you will do the following substeps:

Add Windows Failover Clustering feature

Validate the cluster

Create the failover cluster

Create the cloud witness

Add storage

Add Windows Failover Clustering feature

To begin, connect to the first virtual machine with RDP using a domain account that is a member of local administrators, and has permissions to create objects in Active Directory. Use this account for the rest of the configuration.

One of the features of S2D is that it automatically creates a storage pool when you enable it. You are now ready to create a volume. The PowerShell commandlet New-Volume automates the volume creation process, including formatting, adding to the cluster, and creating a cluster shared volume (CSV). The following example creates an 800 gigabyte (GB) CSV.

After this command completes, an 800 GB volume is mounted as a cluster resource. The volume is at C:\ClusterStorage\Volume1\.

The following diagram shows a cluster shared volume with S2D:

Step 3: Test failover cluster failover

In Failover Cluster Manager, verify that you can move the storage resource to the other cluster node. If you can connect to the failover cluster with Failover Cluster Manager and move the storage from one node to the other, you are ready to configure the FCI.

Step 4: Create SQL Server FCI

After you have configured the failover cluster and all cluster components including storage, you can create the SQL Server FCI.

Connect to the first virtual machine with RDP.

In Failover Cluster Manager, make sure all cluster core resources are on the first virtual machine. If necessary, move all resources to this virtual machine.

Locate the installation media. If the virtual machine uses one of the Azure Marketplace images, the media is located at C:\SQLServer_<version number>_Full. Click Setup.

In the SQL Server Installation Center, click Installation.

Click New SQL Server failover cluster installation. Follow the instructions in the wizard to install the SQL Server FCI.

The FCI data directories need to be on clustered storage. With S2D, it's not a shared disk, but a mount point to a volume on each server. S2D synchronizes the volume between both nodes. The volume is presented to the cluster as a cluster shared volume. Use the CSV mount point for the data directories.

After you complete the wizard, Setup will install a SQL Server FCI on the first node.

After Setup successfully installs the FCI on the first node, connect to the second node with RDP.

Open the SQL Server Installation Center. Click Installation.

Click Add node to a SQL Server failover cluster. Follow the instructions in the wizard to install SQL server and add this server to the FCI.

Note

If you used an Azure Marketplace gallery image with SQL Server, SQL Server tools were included with the image. If you did not use this image, install the SQL Server tools separately. See Download SQL Server Management Studio (SSMS).

Step 5: Create Azure load balancer

On Azure virtual machines, clusters use a load balancer to hold an IP address that needs to be on one cluster node at a time. In this solution, the load balancer holds the IP address for the SQL Server FCI.

Type: The load balancer can be either public or private. A private load balancer can be accessed from within the same VNET. Most Azure applications can use a private load balancer. If your application needs access to SQL Server directly over the Internet, use a public load balancer.

Virtual Network: The same network as the virtual machines.

Subnet: The same subnet as the virtual machines.

Private IP address: The same IP address that you assigned to the SQL Server FCI cluster network resource.

subscription: Your Azure subscription.

Resource Group: Use the same resource group as your virtual machines.

Location: Use the same Azure location as your virtual machines.
See the following picture:

Configure the load balancer backend pool

Return to the Azure Resource Group with the virtual machines and locate the new load balancer. You may have to refresh the view on the Resource Group. Click the load balancer.

Click Backend pools and click + Add to add a backend pool.

Associate the backend pool with the availability set that contains the VMs.

Under Target network IP configurations, check VIRTUAL MACHINE and choose the virtual machines that will participate as cluster nodes. Be sure to include all virtual machines that will host the FCI.

Click OK to create the backend pool.

Configure a load balancer health probe

On the load balancer blade, click Health probes.

Click + Add.

On the Add health probe blade, Set the health probe parameters:

Name: A name for the health probe.

Protocol: TCP.

Port: Set to an available TCP port. This port requires an open firewall port. Use the same port you set for the health probe at the firewall.

In the preceding script, set the values for your environment. The following list describes the values:

<Cluster Network Name>: Windows Server Failover Cluster name for the network. In Failover Cluster Manager > Networks, right-click on the network and click Properties. The correct value is under Name on the General tab.

<ILBIP>: The ILB IP address. This address is configured in the Azure portal as the ILB front-end address. This is also the SQL Server FCI IP address. You can find it in Failover Cluster Manager on the same properties page where you located the <SQL Server FCI IP Address Resource Name>.

<nnnnn>: Is the probe port you configured in the load balancer health probe. Any unused TCP port is valid.

Important

The subnet mask for the cluster parameter must be the TCP IP broadcast address: 255.255.255.255.

After you set the cluster probe you can see all of the cluster parameters in PowerShell. Run the following script:

Get-ClusterResource $IPResourceName | Get-ClusterParameter

Step 7: Test FCI failover

Test failover of the FCI to validate cluster functionality. Do the following steps:

Limitations

On Azure virtual machines, MSDTC is not supported on Windows Server 2016 and earlier because:

The clustered MSDTC resource cannot be configured to use shared storage. With Windows Server 2016 if you create an MSDTC resource, it will not show any shared storage available for use, even if the storage is there. This issue has been fixed in Windows Server 2019.