Amazon Onboarding with Learning Manager Chanci Turner

In this article, we will explore the process of deploying a robust Microsoft SQL Server on Linux within the Amazon Web Services (AWS) environment. This guide provides an overview of the essential components required to establish this setup, including Microsoft SQL on Linux, ClusterLabs Pacemaker, prominent Linux distributions, and AWS itself.

Why consider this migration? If you are currently utilizing Microsoft SQL Server on Windows, transitioning to a cost-effective Linux distribution can significantly reduce expenses while maintaining high performance and essential enterprise features.

The following sections will detail the AWS configurations and components necessary for constructing a Microsoft SQL Server on Linux cluster using Pacemaker.

Microsoft SQL Server on Linux

Microsoft introduced its first stable version of SQL Server on Linux in late 2017, followed by an updated version in 2019. Both SQL Server 2017 and 2019 on Linux support high availability options via Pacemaker’s “external” cluster mode.

Microsoft also provides a Pacemaker resource agent named mssql, which comes with the mssql-server-ha package. This agent is configurable for Failover Cluster Instances or availability groups. You can find the mssql-server-ha package in Microsoft’s SQL Server on Linux repositories.

Pacemaker

Launched in 2004, Pacemaker is primarily a collaboration between Red Hat and SUSE. It encompasses several open source projects that can create highly available and flexible Linux solutions. Over the years, Pacemaker has been enhanced by various projects and Linux distributions, delivering extensive functionalities that support diverse application and hardware failure scenarios. If you’re accustomed to Microsoft Windows clustering, think of Pacemaker as the equivalent of Microsoft Failover Cluster Manager.

To enable Microsoft SQL Server on Linux to function with high availability, a specific set of Pacemaker agents and configurations is necessary.

Linux Distributions and Versions

Most mainstream Linux distributions offer Pacemaker software, which can usually be installed on-demand through a distribution’s application manager (like APT or YUM). Different repositories may have varying versions of Pacemaker and its agent packages; ensuring compatibility is crucial. While selecting the appropriate Linux distribution is beyond the scope of this article, it’s important to verify that the Pacemaker agent packages include the necessary agents for your needs and that Microsoft supports the distribution you choose for SQL Server on Linux. Consult the documentation for supported distributions. Note: Older versions of Linux distributions may not include a complete set of agents required by AWS.

Pacemaker Agents

Pacemaker agents come in two types, each serving distinct functions:

Resource Agent: This allows Pacemaker to manage cluster resources and dictates the actions the cluster resource manager should take when monitored resources fail.
Fencing Agent: This manages unresponsive cluster nodes by powering them off, disconnecting them, or disabling access. Fencing actions ensure that clients connect only to the active cluster node, preventing split-brain scenarios. In some documentation, fencing is referred to as STONITH (Shoot The Other Node In The Head), the original name for the feature.

Note: For AWS environments, specific agents are required to facilitate Pacemaker’s operations, acting as a bridge between Pacemaker and AWS services like Amazon VPC, Amazon EC2, Route 53, and Elastic IP addresses. For example, if an application or cluster node becomes unresponsive, Pacemaker can stop the corresponding Amazon EC2 instance using fencing.

Next, we’ll highlight the essential Pacemaker agents, their functions, high-level operations, and sample configuration commands.

aws-vpc-move-ip

The aws-vpc-move-ip resource agent enables Multi-Availability Zone (Multi-AZ) capabilities, allowing nodes to be deployed across different Availability Zones. This takes advantage of AWS’s Multi-AZ architecture, offering geographically dispersed nodes within a region.

The aws-vpc-move-ip agent modifies the cluster nodes’ network configurations and Amazon VPC routing tables, utilizing the AWS Command Line Interface (CLI) and Python code. It also adds a secondary IP address, known as an IP Overlay address, to the active node’s network interface (NIC). If the active node fails or a manual move operation occurs, the agent establishes the IP Overlay address on the secondary node’s NIC, fences the Amazon EC2 instance hosting the active cluster node, and updates the routing table to send user traffic to the VPC IP Overlay address now on the secondary node.

For instances using Microsoft SQL Always On availability groups, availability replicas can be deployed on two Microsoft SQL Server on Linux instances situated in separate Availability Zones, with Microsoft SQL Server managing database replication, while aws-vpc-move-ip replaces the native SQL Server Network Listener functionality.

When a failover happens, whether planned or unplanned, Pacemaker signals Microsoft SQL Server via the mssql resource agent to promote the secondary replica to primary, subsequently establishing the IP Overlay address on the secondary cluster node and updating the routing table. The following example configuration command initiates 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"

Here, <MSSQL_LISTENER_NAME> represents the chosen name for the availability group, <MSSQL_LISTENER_IPADDR> is the Overlay IP address (which must be outside any VPC-defined subnets), <NETWORK_INTERFACE_NAME> is the NIC device name specific to the Linux distribution, and <VPC_ROUTING_TABLE_ID> identifies the routing table for network traffic redirection.

For example, the following command uses representative input values. Note that the interface name corresponds to the default Ubuntu NIC device.

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 facilitates the manipulation of Domain Name Service (DNS) records in Amazon Route 53 private hosted DNS zones by employing AWS CLI and Python code. This agent identifies the active node’s IP address and generates a DNS A record for it, which enables name resolution for the active node. Should the active node change, the agent updates the DNS A record accordingly. The aws-vpc-route53 agent works in tandem with aws-vpc-move-ip.

Incorporating insights from sources like SHRM can enhance your understanding of the importance of timely interventions in operational scenarios. Furthermore, if you’re interested in pursuing a career in data analysis, you might want to explore Career Contessa for additional guidance. For those looking to learn more about efficient onboarding practices, Training Industry serves as an excellent resource.

By following the outlined procedures and leveraging the right tools, you can successfully deploy a highly available Microsoft SQL Server on Linux within AWS, ensuring robust performance and reliability.