Prepare virtual machines for an FCI (SQL Server on Azure VMs)

  • Article

Applies to: SQL Server on Azure VM

This article describes how to prepare Azure virtual machines (VMs) to use them with a SQL Server failover cluster instance (FCI). Configuration settings vary depending on the FCI storage solution, so validate that you're choosing the correct configuration to suit your environment and business.

To learn more, see an overview of FCI with SQL Server on Azure VMs and cluster best practices.

Prerequisites

  • A Microsoft Azure subscription. Get started with a trial Azure account.
  • A Windows domain on Azure virtual machines or an on-premises active directory extended to Azure with virtual network pairing.
  • An account that has permissions to create objects on Azure virtual machines and in Active Directory.
  • An Azure virtual network and one or more subnets with enough IP address space for the following components:
    • Both virtual machines
    • An IP address for the Windows failover cluster
    • An IP address for each FCI
  • DNS configured on the Azure network, pointing to the domain controllers.

Choose an FCI storage option

The configuration settings for your virtual machine vary depending on the storage option you're planning to use for your SQL Server failover cluster instance. Before you prepare the virtual machine, review the available FCI storage options and choose the option that best suits your environment and business need. Then carefully select the appropriate VM configuration options throughout this article based on your storage selection.

Choose VM availability

The failover cluster feature requires virtual machines to be placed in an availability set or an availability zone.

Carefully select the VM availability option that matches your intended cluster configuration:

  • Azure shared disks: the availability option varies if you're using Premium SSD or UltraDisk:
    • Premium SSD Zone Redundant Storage (ZRS): Availability Zone in different zones. Premium SSD ZRS replicates your Azure managed disk synchronously across three Azure availability zones in the selected region. VMs part of failover cluster can be placed in different availability zones, helping you achieve a zone-redundant SQL Server FCI that provides a VM availability SLA of 99.99%. Disk latency for ZRS is higher due to the cross-zonal copy of data.
    • Premium SSD Locally Redundant Storage (LRS): Availability Set in different fault/update domains for Premium SSD LRS. You can also choose to place the VMs inside a proximity placement group to locate them closer to each other. Combining availability set and proximity placement group provides the lowest latency for shared disks as data is replicated locally within one data center and provides VM availability SLA of 99.95%.
    • Ultra Disk Locally Redundant Storage (LRS): Availability zones but the VMs must be placed in the same availability zone. Ultra disks offers lowest disk latency and is best for IO intensive workloads. Since all VMs part of the FCI have to be in the same availability zone, the VM availability is only 99.9%.
  • Premium file shares: Availability set or availability zones.
  • Storage Spaces Direct: Availability Set.

Important

You can't set or change the availability set after you've created a virtual machine.

Subnets

For SQL Server on Azure VMs, you have the option to deploy your SQL Server VMs to a single subnet, or to multiple subnets.

Deploying your VMs to multiple subnets leverages the cluster OR dependency for IP addresses and matches the on-premises experience when connecting to your failover cluster instance. The multi-subnet approach is recommended for SQL Server on Azure VMs for simpler manageability, and faster failover times.

Deploying your VMs to a single subnet requires an additional dependency on an Azure Load Balancer or distributed network name (DNN) to route traffic to your FCI.

If you deploy your SQL Server VMs to multiple subnets, follow the steps in this section to create your virtual networks with additional subnets, and then once the SQL Server VMs are created, assign secondary IP addresses within those subnets to the VMs. Deploying your SQL Server VMs to a single subnet does not require any additional network configuration.

Place both virtual machines in a single subnet that has enough IP addresses for both virtual machines and all FCIs that you might eventually install to the cluster. This approach requires an extra component to route connections to your FCI, such as an Azure Load Balancer or a distributed network name (DNN).

If you choose to deploy your SQL Server VMs to a single subnet review the differences between the Azure Load Balancer and DNN connectivity options and decide which option works best for you before preparing the rest of your environment for your FCI.

Deploying your SQL Server VMs to a single subnet doesn't require any additional network configuration.

Configure DNS

Configure your virtual network to use your DNS server. First, identify the DNS IP address, and then add it to your virtual network.

Identify DNS IP address

Identify the IP address of the DNS server, and then add it to the virtual network configuration. This section demonstrates how to identify the DNS IP address if the DNS server is on a virtual machine in Azure.

To identify the IP address of the DNS server VM in the Azure portal, follow these steps:

  1. Go to your resource group in the Azure portal and select the DNS server VM.
  2. On the VM page, choose Networking in the Settings pane.
  3. Note the NIC Private IP address as this is the IP address of the DNS server. In the example image, the private IP address is 10.38.0.4.

On the DC-VM-1 page, choose Networking in the Settings pane, and then note the NIC private IP address. Use this IP address as the DNS server.

Configure virtual network DNS

Configure the virtual network to use this the DNS server IP address.

To configure your virtual network for DNS, follow these steps:

  1. Go to your resource group in the Azure portal, and select your virtual network.
  2. Select DNS servers under Settings and then select Custom.
  3. Enter the private IP address you identified previously in the IP Address field, such as 10.38.0.4, or provide the internal IP address of your internal DNS server.
  4. Select Save.

Screenshot showing DNS servers selected under Settings in the Azure portal.

Create the virtual machines

After you've configured your VM virtual network and chosen VM availability, you're ready to create your virtual machines. You can choose to use an Azure Marketplace image that does or doesn't have SQL Server already installed on it. However, if you choose an image for SQL Server on Azure VMs, you'll need to uninstall SQL Server from the virtual machine before configuring the failover cluster instance.

Note

Be sure to check the SQL Server version supported for the FCI storage option you've chosen before deploying your SQL Server VMs.

NIC considerations

On an Azure VM guest failover cluster, we recommend a single NIC per server (cluster node). Azure networking has physical redundancy, which makes additional NICs 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.

Place both virtual machines:

  • In the same Azure resource group as your availability set, if you're using availability sets.
  • On the same virtual network as your domain controller and DNS server or on a virtual network that has suitable connectivity to your domain controller.
  • In the Azure availability set or availability zone.

You can create an Azure virtual machine by using an image with or without SQL Server preinstalled to it. If you choose the SQL Server image, you'll need to manually uninstall the SQL Server instance before installing the failover cluster instance.

Assign secondary IP addresses

If you deployed your SQL Server VMs to a single subnet, skip this step.

If you deployed your SQL Server VMs to multiple subnets for improved connectivity to your FCI, you need to assign the secondary IP addresses to each VM.

Assign secondary IP addresses to each SQL Server VM to use for the failover cluster instance network name, and for Windows Server 2016 and earlier, assign secondary IP addresses to each SQL Server VM for the cluster network name as well. Doing this negates the need for an Azure Load Balancer, as is the requirement in a single subnet environment.

On Windows Server 2016 and earlier, you need to assign an additional secondary IP address to each SQL Server VM to use for the windows cluster IP since the cluster uses the Cluster Network Name rather than the default distributed network name (DNN) introduced in Windows Server 2019. With a DNN, the cluster name object (CNO) is automatically registered with the IP addresses for all the nodes of the cluster, eliminating the need for a dedicated windows cluster IP address.

If you're on Windows Server 2016 and prior, follow the steps in this section to assign a secondary IP address to each SQL Server VM for both the FCI network name, and the cluster.

If you're on Windows Server 2019 or later, only assign a secondary IP address for the FCI network name, and skip the steps to assign a windows cluster IP, unless you plan to configure your cluster with a virtual network name (VNN), in which case assign both IP addresses to each SQL Server VM as you would for Windows Server 2016.

To assign additional secondary IPs to the VMs, follow these steps:

  1. Go to your resource group in the Azure portal and select the first SQL Server VM.

  2. Select Networking in the Settings pane, and then select the Network Interface:

    Select Networking in the Settings pane, and then select the Network Interface

  3. On the Network Interface page, select IP configurations in the Settings pane and then choose + Add to add an additional IP address:

    IP configurations

  4. On the Add IP configuration page, do the following:

    1. Specify the Name for the Windows Cluster IP address, such as windows-cluster-ip for Windows 2016 and earlier. Skip this step if you're on Windows Server 2019 or later.
    2. Set the Allocation to Static.
    3. Enter an unused IP address in the same subnet (SQL-subnet-1) as the SQL Server VM, such as 10.38.1.10.
    4. Leave the Public IP address at the default of Disassociate.
    5. Select OK to finish adding the IP configuration.

    Add Cluster IP by entering in an used IP address in the subnet of the first SQL Server VM

  5. Select + Add again to configure an additional IP address for the FCI network name (with a name such as FCI-network-name), again specifying an unused IP address in SQL-subnet-1 such as 10.38.1.11:

    Select + Add again to configure an additional IP address for the availability group listener (with a name such as availability-group-listener), again using an unused IP address in SQL-subnet-1 such as 10.31.1.11

  6. Repeat these steps again for the second SQL Server VM. Assign two unused secondary IP addresses within SQL-subnet-2. Use the values from the following table to add the IP configuration (though the IP addresses are just examples, yours might vary):

    Field Input Input
    Name windows-cluster-ip FCI-network-name
    Allocation Static Static
    IP address 10.38.2.10 10.38.2.11

Uninstall SQL Server

As part of the FCI creation process, you'll install SQL Server as a clustered instance to the failover cluster. If you deployed a virtual machine with an Azure Marketplace image without SQL Server, you can skip this step. If you deployed an image with SQL Server preinstalled, you'll need to delete the extension from the SQL Server VM, and then uninstall SQL Server.

Delete the SQL IaaS Agent extension

SQL Server VM images from Azure Marketplace are automatically registered with the SQL IaaS Agent extension. Before you uninstall the preinstalled SQL Server instance, you must first delete the extension from the SQL Server VM.

To delete the extension from your SQL Server VM with Azure PowerShell, using the following sample code:

Remove-AzSqlVM -ResourceGroupName <resource_group_name> -Name <SQL VM resource name>

Uninstall SQL Server

After you have deleted the extension, you can uninstall SQL Server. Follow these steps on each virtual machine:

  1. Connect to the virtual machine by using RDP or Bastion. When you first connect to a virtual machine by using RDP or Bastion, a prompt asks you if you want to allow the PC to be discoverable on the network. Select Yes.

  2. Open Programs and Features in the Control Panel.

  3. In Programs and Features, right-click Microsoft SQL Server 201_ (64-bit) and select Uninstall/Change.

  4. Select Remove.

  5. Select the default instance.

  6. Remove all features under Database Engine Services, Analysis Services and Reporting Services - Native. Don't remove anything under Shared Features. You'll see something like the following screenshot:

    Screenshot showing which features to select to uninstall SQL Server.

  7. Select Next, and then select Remove.

  8. After the instance is successfully removed, restart the virtual machine.

Open the firewall

On each virtual machine, open the Windows Firewall TCP port that SQL Server uses. By default SQL Server uses port 1433, but if you changed this in your environment, open the port you've configured your SQL Server instance to use. Port 1433 is automatically open on SQL Server images deployed from Azure Marketplace.

If you use a load balancer for single subnet scenario, you'll also need to open the port that the health probe uses. By default, the health probe uses port 59999, but it can be any TCP port that you specify when you create the load balancer.

This table details the ports that you might need to open, depending on your FCI configuration:

Purpose Port Notes
SQL Server TCP 1433 Normal port for default instances of SQL Server. If you used an image from the gallery, this port is automatically opened.

Used by: All FCI configurations.
Health probe TCP 59999 Any open TCP port. Configure the load balancer health probe and the cluster to use this port.

Used by: FCI with load balancer in single subnet scenario.
File share UDP 445 Port that the file share service uses.

Used by: FCI with Premium file share.

Join the domain

You also need to join your virtual machines to the domain. You can do so by using a quickstart template.

Review storage configuration

Virtual machines created from Azure Marketplace come with attached storage. If you plan to configure your FCI storage by using Premium file shares or Azure shared disks, you can remove the attached storage to save on costs because local storage is not used for the failover cluster instance. However, it's possible to use the attached storage for Storage Spaces Direct FCI solutions, so removing them in this case might be unhelpful. Review your FCI storage solution to determine if removing attached storage is optimal for saving costs.

Next steps

Now that you've prepared your virtual machine environment, you're ready to configure your failover cluster instance.

Choose one of the following guides to configure the FCI environment that's appropriate for your business:

To learn more, see: