This article presents a solution that can benefit organizations that rely on SQL Server 2008 R2 failover clusters to manage their data. The solution uses the Azure shared disks feature and a Windows Server 2008 R2 failover cluster to replicate on-premises deployment in Azure.
Architecture
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 SMB 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.
Download a Visio file of this architecture.
Dataflow
As part of a virtual network, Azure Load Balancer redirects clients by associating a routable, private IP address with the cluster.
Two Azure virtual machines (VMs) run SQL Server 2008 R2 on Windows Server 2008 R2.
An availability set that includes the VMs guarantees the solution's redundancy and availability.
A server message block (SMB) file share witness provides an extra quorum vote to keep the cluster running after site outages.
An Azure shared disk makes it possible to attach a managed disk to both VMs simultaneously.
Components
Azure Load Balancer balances traffic inside virtual networks. This solution 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 Virtual Machines is an on-demand, scalable computing resource. 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 extra 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:
Migrate your workload to an Azure SQL Database service.
Purchase Extended Security Updates to use with your current implementation.
Move SQL Server 2008 R2 deployments to Azure VMs, and use third-party software to manage shared storage.
Scenario details
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.
Tip
For those customers who need some more time to upgrade and modernize their SQL Server and Windows Server 2008/2008 R2 on Azure, we provide one additional year of free extended security updates, only on Azure. For more information, see Extended Security Updates for SQL Server and Windows Server 2008 and 2008 R2.
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:
- Keep their current infrastructure.
- Rehost workloads in Azure with no application code changes.
- Get free extended security updates for 2008 R2 versions of SQL Server and Windows Server.
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.
Considerations
These considerations implement the pillars of the Azure Well-Architected Framework, which is a set of guiding tenets that can be used to improve the quality of a workload. For more information, see Microsoft Azure Well-Architected Framework.
Security
This solution provides extended security updates for SQL Server and Windows Server 2008 and 2008 R2 for one extra year of free extended security updates, up to July 2023. Without extended support beyond that point, security breaches or data loss can result.
Scalability
Windows Server 2008 R2 limits the number of nodes, or servers in the failover cluster, to 16.
Other considerations
The Azure shared disk feature only works with ultra disks and premium SSDs. Among those disk types, only certain disk sizes support sharing.
Use the Azure pricing calculator to explore the cost of running this scenario.
Deploy this scenario
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's 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
Create two Azure VMs that run Windows Server 2008 R2.
When you deploy a VM, Azure Hybrid Benefit for Windows Server is activated by default. With this benefit and Software Assurance, you can use your on-premises Windows Server licenses. However, if you'd like to use a different license and turn off Azure Hybrid Benefit for Windows Server, follow the official guidelines.
Windows Server 2008 R2 is no longer available on Azure Marketplace, but two other deployment options exist:
- Run a PowerShell script.
- Upload a generalized Virtual Hard Disk (VHD) to Azure. From the image of the VHD, you can create a new VM with the required operating system.
Note
Don't use the SQL Server 2008 R2 SP3 on Windows Server 2008 R2 image that is available in Azure Marketplace. This image pre-configures SQL Server as a standalone instance. However, the solution architecture requires a cluster configuration, making the Azure Marketplace image unsuitable.
Configure an availability set to guarantee VM redundancy and availability.
Configure the VMs
Check that the correct name resolution for resources in Azure virtual networks is in place.
Take these steps with each VM:
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.
Add a domain administrator to the machine.
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
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.
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 isn't available for premium SSDs Azure shared disks.
Sign in to one of the VMs and take these steps:
- Initialize the Azure shared disk. Select MBR for the partition style.
- Create a new simple volume on the disk.
- Format the disk. This operation can take several minutes and might require you to restart the VM.
Reboot the second VM to give it access to the disk.
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 need to take some extra 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:
Add the failover clustering feature:
servermanagercmd -install Failover-ClusteringCreate the failover clustering:
cluster /cluster:"<cluster name>" /create /nodes:"<node 1> <node 2> /ipaddr:<cluster ip address>/255.255.255.0Open the failover cluster management console.
- Add the disks in the available storage.
- Run the cluster validation to check the configuration.
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.
Use the management console
In each VM, take the following steps:
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.
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.
Sign in to one VM and use the Create Cluster wizard to create a cluster.
- This operation creates a Cluster Name Object (CNO) and adds it to the domain.
- By default, the operation uses Dynamic Host Configuration Protocol (DHCP) to set the cluster IP address.
Note
If the process of creating a cluster generates a critical error at the end, you can ignore it.
Assign the cluster a valid IP address by following these steps:
From one of the two nodes, run this command:
Net start clussvc /fqFrom the Failover Cluster Manager administrative tool, open the properties of the cluster IP address.
Change the address from DHCP to Static.
Assign the address an unused Azure virtual network IP address. This IP address can't 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 can't 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:
On the server that can host an SMB file share, take these actions:
- Create a folder and share it.
- Give the CNO change and read permissions.
Sign in to one of the VMs and take these actions:
- Open the Configure Cluster Quorum wizard.
- In Select Quorum Configuration, select Node and File Share Majority.
- In Configure File Share Witness, enter the shared folder.
Alternatively, you can configure the quorum through a command line:
Open a command line as an administrator.
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:
To speed up the deployment procedure, slipstream SQL Server 2008 R2 and SQL Server 2008 R2 SP3.
Sign in to one of the VMs and take these steps:
- From the Server Manager administrative tool, add the .NET Framework 3.5.1 feature.
- Open the New SQL Server failover cluster installation administrative tool.
- In the network configuration, disable DHCP and enter an unused virtual network IP address.
- In the database engine configuration, check that the Azure shared disk is selected in Data Directories.
Sign in to the other VM and take these steps:
- From the Server Manager administrative tool, add the .NET Framework 3.5.1 feature.
- 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:
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.
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.255These commands configure a Transmission Control Protocol (TCP) listener for the SQL failover cluster IP address. The
clustercommand provides a TCP port, or a probe port.Take the SQL Server offline and then restart it to apply these changes.
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 don't install the KB3125574 rollup update, the cluster command fails. By default, you can't configure the subnet mask 255.255.255.255 for a Windows Server 2008 R2 cluster.
Reference code
- PowerShell script to deploy VMs on Azure
- ARM Template to create a shared disk
- PowerShell script to deploy an Azure internal load balancer
Next steps
To transfer data from your on-premises database to the newly created cluster, consider these migration strategies:
- Restore a database backup on the new installation.
- Use SQL Log Shipping to replicate the databases on the new server.
To learn more about solution components, see these resources:
- Virtual machines in Azure
- Availability options for Azure Virtual Machines
- What is Azure Load Balancer?
- Introduction to Azure managed disks
- Share an Azure managed disk
- Server Message Block Overview
- What is Azure SQL?