Deploy Microsoft SQL Server Always On to Linux with AWS Launch Wizard

Customers have been asking for additional ways to install SQL Server, with more control of the deployment. Previous releases of the AWS Launch Wizard supported deployments of Always On Availability Groups to Windows Server. With Launch Wizard’s latest release, you can deploy Always On configurations, in addition to single node deployments, to Windows Server or Linux on Amazon Elastic Compute Cloud (EC2).

Launch Wizard lets customers easily size, configure, and deploy Microsoft SQL Server Always On applications on EC2, in addition to other applications such as SAP, without needing to manually identify and provision individual resources. Launch Wizard guides you through a set of sizing and customization questions, and provides recommendations together with feedback on pricing. This enables you to create a production-ready, highly available application that meets your budget, is optimized from the outset for security, reliability, efficiency, and which conforms to the best practices in the AWS Well-Architected Framework.

In this post I highlight the new support for deployment of SQL Server Always On topologies on Linux.  This helps enable customers to easily and consistently run their SQL Server workloads on Linux-based operating systems, without needing to purchase Windows Server licenses. Then, I walk through the steps to deploy an Always On application on Linux using the Launch Wizard. For more information about new support for single node deployments in this post, you can visit the user guide.

SQL Server on Linux – what’s different?

The SQL Server database engine runs the same way on both Windows Server and Linux. However, there are some fundamental changes to certain tasks when using Linux. Thankfully the Launch Wizard can help you adjust to these changes and configure highly available solutions.

One key difference between running SQL Server Always On applications on Linux and Windows is related to failover clustering. When you deploy Always On Availability Groups on a Windows Server host you can take advantage of Windows Server Failover Cluster (WSFC) and Active Directory as built-in features that support failover clustering. However, neither WSFC nor Active Directory are available to support failover clustering on Linux. Instead, to support failover clustering for SQL Server on Linux, Launch Wizard configures Pacemaker and virtual IP resources for you, so that the IP never changes when failover occurs.

Clustering solutions use a quorum, or a majority vote, in event of a failure to determine which node should take over as the primary node. These clustering solutions often have a minimum of three nodes. However, Launch Wizard can provision and configure a solution with a two-node cluster.

To make a quorum in a two-node cluster, you need to configure an extra resource to act as a cluster witness. Often, a file share is used for highly available deployments on Windows. SQL Server on Linux doesn’t use a file share witness. Instead a third SQL Server node is configured, holding only cluster configuration metadata. For reference, here’s what this solution looks like:

Regardless of whether you opt for a two-node configuration, or more, that’s a lot of provisioning and configuration work. The Launch Wizard does this all for you, whether you are targeting Windows or Linux. This can save considerable time and effort. An additional output from the Launch Wizard is a reusable AWS CloudFormation template that you can use as a baseline for subsequent deployments.

Deploying a SQL Server Always On solution on Linux

To see the new support in action, let’s walk through a deployment of SQL Server Always On to Linux.

In the AWS Management Console, I navigate to the Launch Wizard home and select Choose application. Then, I select the type of application to deploy and select Create deployment to start Launch Wizard.

The first step informs me that a role will be created in my account, granting permissions to Launch Wizard to access other AWS services to provision and configure resources on my behalf.

After selecting Next, I begin configuring the settings for my Always On application. First, I select the target operating system (Linux), and then the deployment model. In this case it is a highly available deployment, but I also have the option of a single instance deployment. Many of the options that follow will be familiar if you’ve read the original AWS News Blog post.

I give my deployment a name and can also select the ARN of an Amazon Simple Notification Service (SNS) topic to which notifications and alerts are sent.

I can elect to launch the resources in an existing Virtual Private Cloud (VPC), if it conforms to the requirements, or create a new VPC. In this case, I am electing to create a new VPC. A recommended best practice is to tag resources, so I set a name tag of “sqhalinux-vpc.” I also need to specify the name of a key pair so that I can connect to the launched instances. In this case, I chose my key pair named “steve-demo@us-east-1.”

Next, I configure the SQL Server settings. I set the administrative password and select whether to use a license-included image provided by AWS or a custom AMI for Bring-Your-Own licenses. Note that for BYOL licenses, the custom AMI to use must meet certain requirements detailed in the documentation.

Earlier in the post I noted that Windows Server File Clustering (WSFC) is not available for SQL Server on Linux. Therefore, I cannot use it to detect when or how a failover happens. For Linux deployments, Launch Wizard instead installs and configures Pacemaker. For the Pacemaker cluster settings, I need to provide is a name, a password for the cluster user account, and a username and password for the SQL Pacemaker account. These are used by SQL Server to communicate with the Pacemaker cluster.

The SQL Server nodes also share signed certificates with each other so a storage location. In this case, I specify the name of an existing Amazon Simple Storage Service (Amazon S3) bucket.

Finally, I can set names for the primary and secondary SQL Server nodes, the additional configuration node that is used to ensure a quorum, and additional names for the database and availability group. I found it easier to assign names, letting me easily identify the nodes. If you choose not to assign names, Launch Wizard will apply randomly generated ones.

Selecting Next takes me to settings for my infrastructure requirements. Based on application type, the Launch Wizard can either pre-select a combination of compute and storage, or you define requirements based on instance type. For this post, I’m going to proceed with the suggested infrastructure.

Next, the Launch Wizard shows the estimated cost based on my selections. This is another major feature of the Launch Wizard. Knowing the cost of my selections in advance allows me to refine my choices to better suit my budget prior to deployment.

Selecting Next takes me to a summary of my selections. Once satisfied, I simply click Deploy to start the deployment, sit back, and let Launch Wizard do all the heavy lifting of launching my highly available SQL Server Always On deployment.

Deployment can take some time, and I can view the current status by clicking on the application name.

When the deployment completes, I can review the status. In my Amazon EC2 console, I see the new instances that were created (including primary and secondary nodes, and the configuration instance to satisfy the quorum). Note that my requested node names have been applied to the instances, instead of Launch Wizard-supplied values. As noted, I found this made it easier to identify nodes for future use.

Handling Failovers

To see failover in action, let’s see what happens when the primary node is lost. To do this, I’ll simulate an actual loss by terminating services on the node. That triggers failover processing.

Since all three nodes in the cluster are in a private subnet of the VPC with no access from the public internet, I create a Windows instance in the public subnet of the VPC to act as a bastion host. I also update the security group (created by the Launch Wizard) to permit SSH access to the nodes from the instance. With that done, I open a remote desktop connection to the Windows instance and open SSH sessions to the primary and secondary nodes. Because the pcs and crm_mon commands I’m going to use need to be run as root, in each SSH session I first run sudo -i to elevate permissions (if I don’t run the commands as root they will report that they cannot find the cluster).

In the connection onto the primary node l can check the status of the cluster by running the command pcs cluster status. Three nodes are listed, with the names I requested.

On the connection onto the secondary node, I run crm_mon. This gives me continuously updating output onto activities in the cluster.

Next, I trigger the failover. In the connection to the primary node, I run the command killall -9 corosync to terminate the node. I then switch back to the connection onto my secondary node to watch. With the loss of the primary node, the cluster elects to promote the secondary node to primary. I can watch the progress of the node failover from the continuously updating crm_mon command output. In the following image, we see Pacemaker detect that the primary node has suddenly gone offline

I won’t bore you with snapshots of the entire failover process. Although, I admit I was riveted to the screen the first time I saw it!

Wrap-up

Running an Always On configuration on Linux requires some changes to operational activities from similar configurations on Windows. I encourage everyone to read the documentation for more information.

In this post, I’ve examined how Launch Wizard can help make configuration and deployment of Always On configurations of SQL Server on Linux simpler and easier, and with predictable cost. Launch Wizard takes care of resource provisioning and configuration for me, saving me considerable time and effort. And, Launch Wizard can be used to also benefit from these time and effort savings if I’m targeting Windows for my SQL Server deployments, or other applications such as SAP.

The AWS Launch Wizard is available at no extra charge, you only pay for the resources that are provisioned to run your solution.

AWS can help you assess how your company can get the most out of cloud. Join the millions of AWS customers that trust us to migrate and modernize their most important applications in the cloud. To learn more on modernizing Windows Server or SQL Server, visit Windows on AWS. Contact us to start your migration journey today.