AWS Open Source Blog

Deploying a highly available Microsoft SQL Server on Linux on AWS

In this post, we walk through how to successfully design and build a highly available Microsoft SQL Server on Linux on Amazon Web Services (AWS). This post provides high-level insight into the components necessary to create this solution, including Microsoft SQL on Linux, ClusterLabs Pacemaker (Pacemaker) open source clustering software, leading Linux distributions, and AWS.

Why might you want to do this? If you are an existing Microsoft SQL Server on Windows customer, for example, transparently replacing Windows Server with a lower-cost Linux distribution could help lower costs, without sacrificing performance, enterprise features, and functionality.

The following sections describe the AWS components and configurations required to create a Microsoft SQL Server on Linux cluster using Pacemaker.

Microsoft SQL Server on Linux

Microsoft released the first production-ready version of Microsoft SQL Server on Linux in late 2017 and a subsequent version in 2019. Microsoft SQL Server on Linux 2017 and 2019 support high availability options when implemented with Pacemaker, using the “external” cluster mode.

Microsoft provides a Pacemaker resource agent, called mssql. The mssql resource agent installs with the mssql-server-ha package and is configurable for Failover Cluster Instances (FCI) or availability groups (AG). The mssql-server-ha package is available from Microsoft’s SQL Server on Linux repositories.

Pacemaker

Pacemaker was established in 2004 and is predominantly a collaboration between Red Hat and SUSE. Pacemaker comprises several open source projects that together can create highly available and flexible Linux solutions. Over time, Pacemaker has been extended by other projects and Linux distributions to provide rich functionality that supports virtually any application or hardware failure mode, large or small. Pacemaker orchestrates cluster-related activities using the Pacemaker resource manager and software agents. If you are familiar with Microsoft Windows clustering, Pacemaker is analogous with the Microsoft Failover Cluster Manager.

A specific set of Pacemaker agents and Microsoft SQL Server on Linux configuration are required to allow Microsoft SQL Server on Linux to operate in a highly accessible fashion.

Linux distributions and versions

Pacemaker software is available in most mainstream Linux distributions and generally installed on-demand, using a Linux distribution application manager (e.g., APT or YUM). Linux repositories may have different versions of Pacemaker and often different agent packages. Selecting the right Linux distribution is outside the scope of this blog post. Nevertheless, you should confirm that the Pacemaker agent packages contain the agents you need and that Microsoft supports the Linux distribution you plan to use for Microsoft SQL Server on Linux. Refer to the documentation for supported Linux distributions.

Note: Older versions of Linux distributions may not include a full set of the agents required by AWS.

Pacemaker agents

Pacemaker agents provide different options, depending on their function.There are two Pacemaker agent types:

  • Resource agent: Allows Pacemaker to manage cluster resources and contain the logic of what the cluster resource manager should do when monitored cluster resources fail.
  • Fencing agent: Manages poorly behaving cluster nodes by powering off, network disconnection, or disabling access. The fencing action ensures that clients can only connect to the active cluster node, avoiding split-brain issues. In some documentation, fencing is called STONITH (Shoot The Other Node In The Head, the original feature name).

Note: For AWS, we need environment-specific agents to facilitate Pacemaker operations and workflows. These Pacemaker agents act as the go-between Pacemaker and AWS services, such as Amazon Virtual Private Cloud (Amazon VPC) route tables, Amazon Elastic Compute Cloud (Amazon EC2) instances, Amazon Route 53, and Elastic IP addresses. For Instance, Pacemaker could stop an Amazon EC2 Instance with a fencing activity if the application or cluster node stops responding or becomes unreachable.

Next we will highlight the required Pacemaker agents, what they do, high-level operations, and sample configuration commands.

aws-vpc-move-ip

The aws-vpc-move-ip resource agent provides Multi-Availability Zones (Multi-AZ) capability, in which we can have nodes deployed in different Availability Zones. This takes advantage of the AWS Multi-AZ architecture, providing geographically dispersed nodes within a region.

aws-vpc-move-ip manipulates the cluster nodes’ network configuration and Amazon VPC routing tables, using AWS Command Line Interface (CLI) and Python code. The agent also adds a second IP address to the active node’s network interface (NIC), often called an IP Overlay address. The agent creates a route in a VPC’s routing table to route network traffic with the IP Overlay address as the active node’s destination. In the event of the active node failure or manual cluster resource move operation, the aws-vpc-move-ip resource agent creates the IP Overlay address on the secondary nodes NIC, attempts to fence the Amazon EC2 instance hosting the active cluster node, and updates the routing table to route user traffic to the VPC IP Overlay address now hosted on the secondary node.

For Microsoft SQL Always On availability groups, availability replicas can be deployed on two Microsoft SQL Server on Linux instances in separate Availability Zones and managed by Microsoft SQL Server, including database replication, with aws-vpc-move-ip replacing the native Microsoft SQL Server Network Listener functionality.

If a planned or unplanned event occurs, Pacemaker signals Microsoft SQL Server on Linux via the mssql resource agent to promote the secondary database replica to the primary database replica. Then creates the IP Overlay address on the secondary cluster node and updates the routing table directing user traffic to the newly promoted database replica. The following diagrams describe normal and failed operations.

aws-vpc-move-ip Topology, showing normal operation and failed

The following example configuration command creates and starts the aws-vpc-move-ip resource agent on the primary node:

pcs resource create <MSSQL_LISTENER_NAME> ocf:heartbeat:aws-vpc-move-ip \
ip=<MSSQL_LISTENER_IPADDR> interface=<NETWORK_INTERFACE_NAME> \
 routing_table=<VPC_ROUTING_TABLE_ID> op monitor timeout="30s" interval="60s"
  • <MSSQL_LISTENER_NAME> is the user-selected name that represents the availability group.
  • <MSSQL_LISTENER_IPADDR> is the Overlay IP address. Note: The Overlay IP address must fall outside any VPC-defined subnets.
  • <NETWORK_INTERFACE_NAME> is the cluster nodes NIC device name. Note: The NIC device name is Linux distribution-specific.
  • <VPC_ROUTING_TABLE_ID> identifies the routing table used to redirect network traffic.

The following code snippet provides an example with representative inputs. Note: The interface is the default Ubuntu NIC device name.

pcs resource create ra_aws_vpc_move_ip ocf:heartbeat:aws-vpc-move-ip \
 ip=10.1.1.11 interface= “ens5” routing_table= rtb-0e85b84caaae1c5a8 \
op monitor timeout="30s" interval="60s"

aws-vpc-route53

The aws-vpc-route53 resource agent provides the ability to manipulate Domain Name Service (DNS) records in an Amazon Route 53 private hosted DNS zones, using AWS CLI and Python code. The agent determines the active node’s IP address and creates a DNS A record for the active node, enabling the active node’s DNS name resolution. The agent updates the DNS A record if the active node changes to the secondary node. The aws-vpc-route53 agent works with aws-vpc-move-ip to provide precise DNS name resolution for the active node. aws-vpc-route53 ensures that DNS always reflects the active cluster nodes IP address, allowing users to access no matter where they are.

Using the DNS approach has the advantage of being easy to implement; however, there are disadvantages. For example, DNS updates only occur once every time to live (TTL) cycle, no failure detection, and DNS is not aware of load, geography, or services. Also, DNS is best suited to supporting administration console access.

Note: aws-vpc-route53 is not necessary to create a Microsoft SQL Server on Linux AG.

The following diagram shows normal and failed operations.

aws-vpc-route53, showing normal and failed operations

The following example configuration command creates and starts the aws-vpc-route53 resource agent on the primary node.

pcs resource create <ROUTE53__NAME>ocf:heartbeat:aws-vpc-route53 \
hostedzoneid=<HOSTED_ZONE_ID> fullname=<SERVICE_FULLNAME> \
profile=<CLI_PROFILE> op monitor depth="0" timeout="180" interval="300"
  • <ROUTE53__NAME> is the user-selected name that represents the resource agent.
  • <HOSTED_ZONE_ID> is the hosted zone ID of Route 53.
  • <SERVICE_FULLNAME> is the Fully Qualified Domain Name (FQDN) for the service that hosts the Overlay IP address.
  • <CLI_PROFILE> is the name of the AWS CLI profile of the root account.

The following code snippet provides an example with representative inputs.

pcs resource create ra_aws-vpc-route53 ocf:heartbeat:aws-vpc-route53 \
 hostedzoneid=Z22XDOABCDEFGH1 fullname=cluster.mydomain.com \
profile=cluster op monitor depth="0" timeout="180" interval="300"

fence_aws

The final Pacemaker agent is a fencing agent. Fencing agents are necessary to ensure user data integrity and the only way to recover from specific failure conditions. In principle, Pacemaker clusters can function without fencing; however, operating clusters in a production environment without a fencing solution is not recommended.

fence_aws performs a fencing action on unhealthy active nodes Amazon EC2 instances. The fencing agent maps each EC2 instance to the corresponding cluster node. If the Pacemaker resource manager determines an issue with a cluster node, it executes the configured fencing action. Typically, fence_aws reboots or shuts down the impacted EC2 instance, avoiding any guest operating system or network issues. During the AWS EC2 Instance reboot process, the aws-vpc-move-ip resource agents execute and moves the active cluster node to a secondary cluster node.

The following example configuration command creates and starts the fence_aws fencing agent on the primary node.

pcs stonith create <FENCE_NAME> region=<REGION> pcmk_host_map="<EC2_INSTANCE_ID_NODE_01>:<NODEINDEX_01>;< EC2_INSTANCE_ID_NODE_02>:< NODEINDEX_02>;< EC2_INSTANCE_ID_NODE_03>:< NODEINDEX03>" power_timeout=240 pcmk_reboot_timeout=480 pcmk_reboot_retries=4 --force
  • <FENCE_NAME> is the user selected name that represents the Fencing Agent.
  • <REGION> is the AWS Region hosting the solution.
  • <EC2_INSTANCE_ID_NODE_01>, <EC2_INSTANCE_ID_NODE_02>, <EC2_INSTANCE_ID_NODE_01> represents the Linux host names for each node.
  • <NODEINDEX_01>, <NODEINDEX_02>, <NODEINDEX_01> represents index of each node.

The following code snippet provides an example with representative inputs.

pcs stonith create fa_ fence_aws region=ap-southeast-2 pcmk_host_map="i-0b6ffa93b9aaa7081:1; i-0b6ffa93b9ccc7081:2; i-0b6ffa93ddd9b7081:3" power_timeout=240 pcmk_reboot_timeout=480 pcmk_reboot_retries=4 --force

Resources

When I initially investigated implementing a high availability Microsoft SQL Server on Linux, the following resources helped and I recommend reading them before you get started. These resources provide additional detail on Linux distributions variations and build instructions.

Sample solution configuration

In this post, I’ve described specific AWS, Pacemaker, and Microsoft SQL Server on Linux requirements for building a Microsoft SQL Server on Linux AG solution. Other components are required, which can be found in the resources in the previous section. The following code example comes from a solution I built and shows the necessary configuration steps to create and configure Pacemaker for a Microsoft SQL Server on Linux AG solution.

echo '--- Pacemaker Cluster Configuration Starting.'

echo '--- Authorising Configuring Pacemaker Cluster Nodes'

sudo pcs cluster auth <NODE_01> <NODE_02> <NODE_03> -u hacluster -p <PMC_CLUSTER_PASSWORD> 

echo '--- Creating PCS Cluster'

sudo pcs cluster setup --name <PMC_CLUSTER_NAME> <NODE_01> <NODE_02> <NODE_03> \
-u hacluster -p <PMC_CLUSTER_PASSWORD> --force 

echo '--- Enable and Starting PCS Cluster.'

sudo pcs cluster start --all --wait

echo '--- Configuring Stonith Fencing.'

sudo pcs stonith create clusterfence fence_aws region=<REGION> \ 
pcmk_host_map="<NODE_01>:<NODEID_01  >;<NODE_02>:<NODEID_02>;<NODE_03>:<NODEID_03>"  \
power_timeout=240 pcmk_reboot_timeout=480 pcmk_reboot_retries=4 --force

echo '--- Creating Cluster Resource Agent (AWS-VPC-MOVE-IP)'

sudo pcs resource create <MSSQL_LISTENER_NAME>ocf:heartbeat:aws-vpc-move- \
ip ip=<MSSQL_LISTENER_IPADDR> interface=<NETWORK_INTERFACE_NAME> \
routing_table=<VPC_ROUTING_TABLE_ID> op monitor timeout="30s" interval="60s"

echo '--- Creating Cluster Resource Agent (MSSQL)'

sudo pcs resource create <MSSQL_AG_NAME> ocf:mssql:ag ag_name=<MSSQL_AG_NAME> \
 --master notify=true meta failure-time=60s --force 

echo '--- Setting PCS cluster properties'

sudo pcs resource update <MSSQL_AG_NAME> meta failure-timeout=60s

sudo pcs property set stonith-enabled=true   

sudo pcs property set start-failure-is-fatal=true  

sudo pcs property set cluster-recheck-interval=75s

echo '--- Adding Cluster Colocation Constraints.'

sudo pcs constraint colocation add <MSSQL_LISTENER_NAME> with <MSSQL_AG_NAME>- \
master INFINITY --force 

echo '--- Adding Cluster Order Constraints.'

sudo pcs constraint order promote <MSSQL_AG_NAME>-master then start <MSSQL_LISTENER_NAME> --force
 
echo '--- Pacemaker Cluster Configuration Completed.'

# Microsoft SQL Server on Linux Always on Availability Groups Build Completed.
#

Conclusion

In this post, we’ve learned about components necessary to implement a highly available Microsoft Server on Linux with Pacemaker on AWS. More information is required to design a complete solution; however, the information presented in this post should help you design and implement a solution.

Also check out the Launch Wizard found in the AWS Management Console. Launch Wizard helps builds a complete Microsoft SQL Server on Linux, 2019 Always On Availability Group. The implementation is production-ready so that you can jump-start your implementation immediately.

We hope that this information is helpful and provides you with the knowledge to get started.

TAGS:
Sandy Millar

Sandy Millar

As an AWS Microsoft Specialised Solution Architect, my role focuses on Microsoft's infrastructure products and services; providing and streamlining the latest Microsoft technology for end-users on AWS. For thirty years, I have operated in all manner of situations, responding successfully to changing roles and technology. Working for software vendors, systems integrators, and solution providers have provided me with many unique opportunities to oversee and deliver high-value outcomes for customers.