SQL Server 2008 R2 failover cluster in Azure

Load Balancer
Managed Disks
Virtual Machines
Virtual Network

Many businesses rely on SQL Server 2008 R2 failover clusters to manage their data. However, support for SQL Server 2008 R2 and for Windows Server 2008 R2 has ended. Regular security updates are no longer available.

Customers who would like to migrate to Azure often can't change their infrastructure. The Azure shared disks feature makes migration possible in this situation. With this feature and a Windows Server 2008 R2 failover cluster, users can replicate their on-premises deployment in Azure. There's no need for third-party software to manage shared storage.

With this solution, users can:

Potential use cases

This architecture benefits organizations that rely on SQL Server 2008 R2 failover clusters to provide fault-tolerant data management. Examples include businesses in reservations, e-commerce, and logistics.

Architecture

Architecture diagram showing a Windows Server 2008 R2 failover cluster that uses an Azure shared disk to manage shared storage.

A dotted line surrounds most components, including an Azure Load Balancer, two virtual machines, and a file share witness. The line indicates that these components are part of a virtual network. Inside that network, a horizontal, blue rectangle represents an availability set. It contains the two virtual machines and their disks. Lines run between each virtual machine and an S M B file share witness. A black, vertical rectangle contains the file share witness and runs through the availability set. On the top border of that rectangle is the Load Balancer. A line extends from the Load Balancer to the outside of the virtual network rectangle. Outside the virtual network rectangle on the bottom is an Azure shared disk. A line connects that disk to the components in the network.

Components

  • Azure Load Balancers balance traffic inside virtual networks. This architecture uses an internal load balancer. This type uses a private IP address and distributes inbound traffic to back-end pool instances. The load balancer directs traffic according to configured load-balancing rules and health probes. The back-end pool instances can be Azure VMs.

  • Azure VMs are on-demand, scalable computing resources that are available with Azure. An Azure VM provides the flexibility of virtualization. But it eliminates the maintenance demands of physical hardware. Azure VMs offer a choice of operating systems, including Windows and Linux.

  • SMB file share witnesses are servers that support the SMB protocol and host a file share. Failover clusters use these servers to provide high availability. When a site outage occurs, a file share witness provides an additional quorum vote that keeps the cluster running.

  • Azure shared disk is a feature of Azure managed disks. These shared disks offer shared block storage that multiple VMs can access. You can use this feature to attach a managed disk to multiple VMs simultaneously.

Alternatives

A few alternatives to this architecture exist:

Considerations

Keep the following points in mind when implementing this architecture.

Security considerations

This solution provides extended security updates for 2008 R2 versions of SQL Server and Windows Server for three years. Without extended support beyond that point, security breaches or data loss may result.

Scalability considerations

Windows Server 2008 R2 limits the number of nodes, or servers in the failover cluster, to 16.

Other considerations

Deploy the solution

Follow these steps to implement this architecture.

Prerequisites

  • A two-node SQL Server 2008 R2 failover cluster on-premises, available for migration. The cluster nodes must be connected to an on-premises domain.
  • An Azure virtual network that is connected to an on-premises network.
    • An Active Directory domain controller that can communicate with the virtual network. This server can either be on-premises or in a separate virtual network.
    • A subnet on which the host cluster is available.
  • A server that can host an SMB file share witness for cluster quorum. This server must be connected to the on-premises domain.

Build a Windows Server 2008 R2 failover cluster on Azure

Follow these steps to set up the cluster.

Deploy the VMs on Azure

  1. Create two Azure VMs that run Windows Server 2008 R2.

  2. Configure an availability set to guarantee VM redundancy and availability.

Configure the VMs

  1. Check that the correct name resolution for resources in Azure virtual networks is in place.

  2. Take these steps with each VM:

    1. Join the VM to the domain. The PowerShell script that deploys VMs assigns them private IP addresses. Classic, on-premises database cluster configurations that have no direct exposure to the internet use this type of assignment. To manage the cluster nodes deployed into the Azure virtual network from the on-premises network, you have two options:

      • Establish a route path from your on-premises system that supports the relevant protocols.
      • Use Azure Bastion to connect to the VMs.
    2. Add a domain administrator to the machine.

    3. Install the KB3125574 rollup update. This update extends the failover cluster so that it supports the custom probe that Azure Load Balancer uses.

Deploy the Azure shared disk

  1. Use an Azure Resource Manager template (ARM template), such as the template in Reference code, to deploy an Azure shared disk. Currently you can't use the Azure portal for this purpose.

  2. Use the Azure portal to connect the Azure shared disk to both VMs. For this operation, use the Attach existing disks function on the Disks page of each VM. Currently ReadOnly host caching is not available for premium SSDs Azure shared disks.

  3. Sign in to one of the VMs and take these steps:

    1. Initialize the Azure shared disk. Select MBR for the partition style.
    2. Create a new simple volume on the disk.
    3. Format the disk. This operation can take several minutes and might require you to restart the VM.
  4. Reboot the second VM to give it access to the disk.

  5. Check that the disk is visible on both VMs. But note that this visibility is only temporary. Later, the cluster will take over disk management.

Configure the cluster

Follow standard procedures to configure the Windows Server 2008 R2 cluster. This section outlines the high-level steps.

You have two options for completing the configuration:

  • The command line: This procedure is straightforward and error-free.
  • The management console: Because of Windows 2008 R2 cluster limitations, this procedure generates some errors related to the cluster IP configuration. You'll need to take some additional recovery steps with this approach.
Use the command line

In each VM, sign in as a domain administrator, open a command line as an administrator, and take the following steps:

  1. Add the failover clustering feature:

    servermanagercmd -install Failover-Clustering
    
  2. Create the failover clustering:

    cluster /cluster:"<cluster name>" /create /nodes:"<node 1> <node 2> /ipaddr:<cluster ip address>/255.255.255.0
    
  3. Open the failover cluster management console.

    1. Add the disks in the available storage.
    2. Run the cluster validation to check the configuration.
  4. Configure the inbound ports firewall to use these values for the SQL Server Default instance:

    1. SQL Server: 1433
    2. Load Balancer Health Probe: 59999

For complete guidelines, refer to Configure the Windows Firewall to Allow SQL Server Access.

Use the management console
  1. In each VM, take the following steps:

    1. From the Server Manager administrative tool, add the Failover Clustering feature. If you run a configuration validation, you'll see a warning message because there's only a network interface in each node.

    2. Configure the inbound ports firewall to use these values for the SQL Server Default instance:

      • SQL Server: 1433
      • Load Balancer Health Probe: 59999

      For complete guidelines, refer to Configure the Windows Firewall to Allow SQL Server Access.

  2. Sign in to one VM and use the Create Cluster wizard to create a cluster.

    Note

    If the process of creating a cluster generates a critical error at the end, you can ignore it.

  3. Assign the cluster a valid IP address by following these steps:

    1. From one of the two nodes, run this command:

      Net start clussvc /fq
      
    2. From the Failover Cluster Manager administrative tool, open the properties of the cluster IP address.

    3. Change the address from DHCP to Static.

    4. Assign the address an unused Azure virtual network IP address. This IP address cannot be used to communicate with the cluster. Since this address isn't associated with an Azure object, it's not visible in the virtual network, and traffic cannot flow to it. An internal load balancer that you deploy in a later step will solve this problem.

Add a cluster witness

Follow these steps to add a file share witness to provide cluster quorum:

  1. On the server that can host an SMB file share, take these actions:

    1. Create a folder and share it.
    2. Give the CNO change and read permissions.
  2. Sign in to one of the VMs and take these actions:

    1. Open the Configure Cluster Quorum wizard.
    2. In Select Quorum Configuration, select Node and File Share Majority.
    3. In Configure File Share Witness, enter the shared folder.

Alternatively, you can configure the quorum through a command line:

  1. Open a command line as an administrator.

  2. Run the following command:

    cluster /cluster:"<cluster name>" res "File Share Witness" /create /group:"Cluster Group" /type:"File Share Witness" /priv SharePath="<witness share>"
    cluster /cluster:"<cluster name>" res "File Share Witness" /online
    cluster "<cluster name>" /quorum:"File Share Witness"
    

Deploy the SQL Server 2008 R2 failover cluster

Follow these steps to bring the cluster online:

  1. To speed up the deployment procedure, slipstream SQL Server 2008 R2 and SQL Server 2008 R2 SP3.

  2. Sign in to one of the VMs and take these steps:

    1. From the Server Manager administrative tool, add the .NET Framework 3.5.1 feature.
    2. Open the New SQL Server failover cluster installation administrative tool.
      1. In the network configuration, disable DHCP and enter an unused virtual network IP address.
      2. In the database engine configuration, check that the Azure shared disk is selected in Data Directories.
  3. Sign in to the other VM and take these steps:

    1. From the Server Manager administrative tool, add the .NET Framework 3.5.1 feature.
    2. In the installation software, select Add node to a SQL Server failover cluster.

Add an internal load balancer

Follow these steps to assign a routable, private IP address to the SQL cluster:

  1. Use a PowerShell script to add an Azure Load Balancer. For the private IP address, use an address that you haven't already used during cluster configuration.

  2. Configure the SQL Server 2008 R2 failover cluster to accept probe requests by running these commands from one of the VMs:

    $IPResourceName = "<Name of the SQL Server IP resource e.g. 'SQL IP Address 1 (sqldbcluster)'>"
    $AILBIP = "<Azure Internal Load Balancer IP>"
    cluster res $IPResourceName /priv enabledhcp=0 address=$AILBIP probeport=59999 subnetmask=255.255.255.255
    

    These commands configure a Transmission Control Protocol (TCP) listener for the SQL failover cluster IP address. The cluster command provides a TCP port, or a probe port.

  3. Take the SQL Server offline and then restart it to apply these changes.

  4. Test the new configuration by checking that the subnet mask in the SQL Server IP configuration has the value 255.255.255.255.

Note

If you didn't install the KB3125574 rollup update, the cluster command will fail. By default, you cannot configure the subnet mask 255.255.255.255 for a Windows Server 2008 R2 cluster.

Reference code

Next steps

To transfer data from your on-premises database to the newly created cluster, consider these migration strategies: