Create an FCI with Azure shared disks (SQL Server on Azure VMs)

APPLIES TO: SQL Server on Azure VM

This article explains how to create a failover cluster instance (FCI) by using Azure shared disks with SQL Server on Azure Virtual Machines (VMs).

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

Prerequisites

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

Add Azure shared disk

Deploy a managed Premium SSD disk with the shared disk feature enabled. Set maxShares to align with the number of cluster nodes to make the disk shareable across all FCI nodes.

Add an Azure shared disk by doing the following:

  1. Save the following script as SharedDiskConfig.json:

    { 
      "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
      "contentVersion": "1.0.0.0",
      "parameters": {
        "dataDiskName": {
          "type": "string",
          "defaultValue": "mySharedDisk"
        },
        "dataDiskSizeGB": {
          "type": "int",
          "defaultValue": 1024
        },
        "maxShares": {
          "type": "int",
          "defaultValue": 2
        }
      },
      "resources": [
        {
          "type": "Microsoft.Compute/disks",
          "name": "[parameters('dataDiskName')]",
          "location": "[resourceGroup().location]",
          "apiVersion": "2019-07-01",
          "sku": {
            "name": "Premium_LRS"
          },
          "properties": {
            "creationData": {
              "createOption": "Empty"
            },
            "diskSizeGB": "[parameters('dataDiskSizeGB')]",
            "maxShares": "[parameters('maxShares')]"
          }
        }
      ] 
    }
    
  2. Run SharedDiskConfig.json by using PowerShell:

    $rgName = < specify your resource group name>
        $location = 'westcentralus'
        New-AzResourceGroupDeployment -ResourceGroupName $rgName `
    -TemplateFile "SharedDiskConfig.json"
    
  3. For each VM, initialize the attached shared disks as GUID partition table (GPT) and format them as New Technology File System (NTFS) by running this command:

    $resourceGroup = "<your resource group name>"
        $location = "<region of your shared disk>"
        $ppgName = "<your proximity placement groups name>"
        $vm = Get-AzVM -ResourceGroupName "<your resource group name>" `
            -Name "<your VM node name>"
        $dataDisk = Get-AzDisk -ResourceGroupName $resourceGroup `
            -DiskName "<your shared disk name>"
        $vm = Add-AzVMDataDisk -VM $vm -Name "<your shared disk name>" `
            -CreateOption Attach -ManagedDiskId $dataDisk.Id `
            -Lun <available LUN  check disk setting of the VM>
     update-AzVm -VM $vm -ResourceGroupName $resourceGroup
    

Create failover cluster

To create the failover cluster, you need:

  • The names of the virtual machines that will become the cluster nodes.
  • A name for the failover cluster.
  • An IP address for the failover cluster. You can use an IP address that's not used on the same Azure virtual network and subnet as the cluster nodes.

The following PowerShell script creates a failover cluster. Update the script with the names of the nodes (the virtual machine names) and an available IP address from the Azure virtual network.

New-Cluster -Name <FailoverCluster-Name> -Node ("<node1>","<node2>") –StaticAddress <n.n.n.n> -NoStorage

Configure quorum

Configure the quorum solution that best suits your business needs. You can configure a Disk Witness, a Cloud Witness, or a File Share Witness. For more information, see Quorum with SQL Server VMs.

Validate cluster

Validate the cluster in the UI or by using PowerShell.

To validate the cluster by using the UI, do the following on one of the virtual machines:

  1. Under Server Manager, select Tools, and then select Failover Cluster Manager.
  2. Under Failover Cluster Manager, select Action, and then select Validate Configuration.
  3. Select Next.
  4. Under Select Servers or a Cluster, enter the names of both virtual machines.
  5. Under Testing options, select Run only tests I select.
  6. Select Next.
  7. Under Test Selection, select all tests except Storage

Test cluster failover

Test the failover of your cluster. In Failover Cluster Manager, right-click your cluster, select More Actions > Move Core Cluster Resource > Select node, and then select the other node of the cluster. Move the core cluster resource to every node of the cluster, and then move it back to the primary node. If you can successfully move the cluster to each node, you're ready to install SQL Server.

Test cluster failover by moving the core resource to the other nodes

Create SQL Server FCI

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

  1. Connect to the first virtual machine by using Remote Desktop Protocol (RDP).

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

  3. 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.

  4. Select Setup.

  5. In SQL Server Installation Center, select Installation.

  6. Select 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 the Azure Shared Disks.

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

  2. After Setup installs the FCI on the first node, connect to the second node by using RDP.

  3. Open the SQL Server Installation Center, and then select Installation.

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

    Note

    If you used an Azure Marketplace gallery image that contains SQL Server, SQL Server tools were included with the image. If you didn't use one of those images, install the SQL Server tools separately. For more information, see Download SQL Server Management Studio (SSMS).

Register with the SQL VM RP

To manage your SQL Server VM from the portal, register it with the SQL VM resource provider (RP) in lightweight management mode, currently the only mode supported with FCI and SQL Server on Azure VMs.

Register a SQL Server VM in lightweight mode with PowerShell:

# Get the existing compute VM
$vm = Get-AzVM -Name <vm_name> -ResourceGroupName <resource_group_name>
         
# Register SQL VM with 'Lightweight' SQL IaaS agent
New-AzSqlVM -Name $vm.Name -ResourceGroupName $vm.ResourceGroupName -Location $vm.Location `
   -LicenseType PAYG -SqlManagementType LightWeight  

Configure connectivity

To route traffic appropriately to the current primary node, configure the connectivity option that's suitable for your environment. You can create an Azure load balancer or, if you're using SQL Server 2019 CU2+ and Windows Server 2016 (or later) you can preview the distributed network name feature instead.

Limitations

Next steps

If you haven't already done so, configure connectivity to your FCI with a virtual network name and an Azure load balancer or distributed network name (DNN).

If Azure shared disks are not the appropriate FCI storage solution for you, consider creating your FCI using premium file shares or Storage Spaces Direct instead.

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

For more information see: