Preview the new system role support for Microsoft SQL Server Always On availability groups

Red Hat Enterprise Linux (RHEL) 8.5 introduced support for a Microsoft SQL Server system role that automates the installation, configuration and tuning of Microsoft SQL Server on RHEL. You can learn more about the existing functionality in the RHEL documentation or in this previous blog post.

The role is being expanded to support SQL Server clustering, specifically support for Always On availability groups, Microsoft’s shared-nothing clustering scheme. Availability groups replicate databases between a primary (writer) virtual or physical server (node) and one or more replicas, which provides continuous access to data in the event of hardware or software failures. The new capabilities will be delivered in a future release of RHEL.

What are RHEL System Roles?

RHEL System Roles are a collection of Ansible roles and modules that can help automate the management and configuration of RHEL Systems. These can help provide consistent and repeatable configuration, reduce technical burdens and streamline administration. For more information on getting started with RHEL system roles, refer to the Introduction to RHEL System Roles article, and see the list of available system roles.

The Microsoft SQL Server system role can be used in conjunction with other system roles, such as the storage and network system roles. For example, you could use the storage system role to create filesystems that will be used by SQL Server and use the network system role to create a bonded network interface on the system.  

Setting up availability groups with SQL Server on Linux is handled a bit differently than how they are set up with SQL Server on Windows. On Windows Server, for example, certificate/key management is handled by Active Directory. On Linux, you set up a certificate/key infrastructure to secure the data replication channels. The new role functionality automates this setup. The result is that certificates and keys stored in the Linux file system are encrypted using passwords that you supply.

Another change with configuring Availability groups with SQL Server on Linux is that they are managed through system-level clustering. If you’re migrating from a Windows server to a Linux server, the cluster setup experience is very different.

On Red Hat Enterprise Linux, the RHEL High Availability Add-on (RHEL HA), delivers Pacemaker and Corosync services that serve this function. Database administrators coming to Linux for the first time from Windows Server often lack the knowledge on how to set up clustering services in a Linux environment. The Microsoft SQL Server system role now works with the HA Cluster system role, to allow you to configure both sides of the solution. Getting this right is extremely important because, in this architecture, it’s RHEL HA’s job to manage automated failover should the primary SQL Server fail.

RHEL HA also manages the floating IP address that is used when writing to the primary database via the availability group Listener Service. A SQL Server HA resource agent, provided by Microsoft, handles communication between SQL Server and the Pacemaker service included with RHEL HA.

It is the job of the resource agent to periodically check the health of SQL Server nodes and to bring them down in a controlled fashion if they are exhibiting any issues (for example, if a host is becoming severely starved for memory). If Pacemaker is unable to reach a node, it first blocks it from being reached (this is known as “fencing”), then decides what to do with the other nodes in the cluster.

For example, if the node that became unreachable was the primary, a different replica node will get promoted to the primary job. Configuring either availability groups or high availability incorrectly can create configurations that seem to work, but will result in unexpected failures or data loss on failover. Using the Microsoft SQL Server system role (which in turn utilizes the HA Cluster system role) is the best way to set this all up.

SQL Server on Linux also requires at least three nodes to form a cluster instead of a minimum of two nodes and an SMB file share. As on Windows, with Linux you have a primary, which accepts writes, at least one replica and either a third node which can either be a replica or a configuration-only replica (which doesn’t replicate the data, but does replicate configuration).   

Both primary and secondary replica nodes must be able to store the full contents of the database being managed by SQL Server. An SQL Server database will continue to be available as long as it has at least two of those nodes active in its availability group (as noted above, one node can be a configuration-only replica). The new functionality in the role simplifies the configuration of the primary, secondary replica and configuration-only replica nodes.

The new enhancements mean that you can use the role to set all of this up in addition to existing capabilities that allow you to tune, configure and deploy fully functioning SQL Servers.

Preview available in Ansible Galaxy

While the new extended version of the system role isn’t yet officially supported by Red Hat, we have made it available as a preview for customers to try out in Ansible Galaxy. A full description of the parameters is included in the upstream README.md file as well as examples of how to deploy an availability group with RHEL HA on bare metal, VMware and in Microsoft Azure.

Try out this latest release of the Microsoft SQL Server system role today by visiting Ansible Galaxy. If you have any feedback regarding this new upstream functionality, please open an issue on the upstream GitHub issue tracker.