Starting with SQL Server 2017 (14.x), SQL Server is supported on both Linux and Windows. Like Windows-based SQL Server deployments, SQL Server databases and instances need to be highly available under Linux. This article covers the technical aspects of planning and deploying highly available Linux-based SQL Server databases and instances, as well as some of the differences from Windows-based installations. Because SQL Server may be new for Linux professionals, and Linux may be new for SQL Server professionals, the article at times introduces concepts that may be familiar to some and unfamiliar to others.
SQL Server availability options for Linux deployments
Besides backup and restore, the same three availability features are available on Linux as for Windows-based deployments:
- Always On Availability Groups (AGs)
- Always On Failover Cluster Instances (FCIs)
- Log Shipping
On Windows, FCIs always require an underlying Windows Server failover cluster (WSFC). Depending on the deployment scenario, an AG usually requires an underlying WSFC, with the exception being the new None variant in SQL Server 2017 (14.x). A WSFC does not exist in Linux.
A quick Linux primer
While some Linux installations may be installed with an interface, most are not, meaning that nearly everything at the operating system layer is done via command line. The common term for this command line in the Linux world is a bash shell.
In Linux, many commands need to be executed with elevated privileges, much like many things need to be done in Windows Server as an administrator. There are two main methods to execute with elevated privileges:
- Run in the context of the proper user. To change to a different user, use the command su. If su is executed on its own without a username, as long as you know the password, you will now be in a shell as root.
- The more common and security conscious way to run things is to use sudo before executing anything. Many of the examples in this article use sudo.
Some common commands, each of which have various switches and options that can be researched online:
- cd – change the directory
- chmod – change the permissions of a file or directory
- chown – change the ownership of a file or directory
- ls – show the contents of a directory
- mkdir – create a folder (directory) on a drive
- mv – move a file from one location to another
- ps – show all of the working processes
- rm – delete a file locally on a server
- rmdir – delete a folder (directory)
- systemctl – start, stop, or enable services
- Text editor commands. On Linux, there are various text editor options, such as vi and emacs.
Common tasks for availability configurations of SQL Server on Linux
This section covers tasks that are common to all Linux-based SQL Server deployments.
Ensure that files can be copied
Copying files from one server to another is a task that anyone using SQL Server on Linux should be able to do. This task is very important for AG configurations.
Things like permission issues can exist on Linux as well as on Windows-based installations. However, those familiar with how to copy from server to server on Windows may not be familiar with how it is done on Linux. A common method is to use the command-line utility scp, which stands for secure copy. Behind the scenes, scp uses OpenSSH. SSH stands for secure shell. Depending on the Linux distribution, OpenSSH itself may not be installed. If it is not, OpenSSH needs to be installed first.
When using scp, you must provide the credentials of the server if it is not the source or destination. For example, using
scp MyAGCert.cer [email protected]:/folder/subfolder
copies the file MyAGCert.cer to the folder specified on the other server. Note that you must have permissions – and possibly ownership – of the file to copy it, so chown may also need to be employed before copying. Similarly, on the receiving side, the right user needs access to manipulate the file. For example, to restore that certificate file, the mssql user must be able to access it.
Samba, which is the Linux variant of server message block (SMB), can also be used to create shares accessed by UNC paths such as \\SERVERNAME\SHARE.
Windows-based SMB shares can also be used; SMB shares do not need to be Linux-based, as long as the client portion of Samba is configured properly on the Linux server hosting SQL Server and the share has the right access. For those in a mixed environment, this would be one way to leverage existing infrastructure for Linux-based SQL Server deployments.
One thing that is important is that the version of Samba deployed should be SMB 3.0 compliant. When SMB support was added in SQL Server 2012 (11.x), it required all shares to support SMB 3.0. If using Samba for the share and not Windows Server, the Samba-based share should be using Samba 4.0 or later, and ideally 4.3 or later, which supports SMB 3.1.1.
Finally, using a network file system (NFS) share is an option. Using NFS is not an option on Windows-based deployments of SQL Server, and can only be used for Linux-based deployments.
Configure the firewall
Similar to Windows, Linux distributions have a built-in firewall. If your company is using an external firewall to the servers, disabling the firewalls in Linux may be acceptable. However, regardless of where the firewall is enabled, ports need to be opened. The following table documents the common ports needed for highly available SQL Server deployments on Linux.
|CONFIGURE THE FIREWALL|
|111||TCP/UDP||NFS – rpcbind/sunrpc|
|135||TCP||Samba (if used) – End Point Mapper|
|137||UDP||Samba (if used) – NetBIOS Name Service|
|138||UDP||Samba (if used) – NetBIOS Datagram|
|139||TCP||Samba (if used) – NetBIOS Session|
|445||TCP||Samba (if used) – SMB over TCP|
|1433||TCP||SQL Server – default port; if desired, can change with mssql-conf set network.tcpport <portnumber>|
|2049||TCP, UDP||NFS (if used)|
|2224||TCP||Pacemaker – used by pcsd|
|3121||TCP||Pacemaker – Required if there are Pacemaker Remote nodes|
|3260||TCP||iSCSI Initiator (if used) – Can be altered in /etc/iscsi/iscsid.config (RHEL), but should match port of iSCSI Target|
|5022||TCP||SQL Server – default port used for an AG endpoint; can be changed when creating the endpoint|
|5404||UDP||Pacemaker – Required by Corosync if using multicast UDP|
|5405||UDP||Pacemaker – Required by Corosync|
|21064||TCP||Pacemaker – Required by resources using DLM|
|Variable||TCP||AG endpoint port; default is 5022|
|Variable||TCP||NFS – port for LOCKD_TCPPORT (found in /etc/sysconfig/nfs on RHEL)|
|Variable||UDP||NFS – port for LOCKD_UDPPORT (found in /etc/sysconfig/nfs on RHEL)|
|Variable||TCP/UDP||NFS – port for MOUNTD_PORT (found in /etc/sysconfig/nfs on RHEL)|
|Variable||TCP/UDP||NFS – port for STATD_PORT (found in /etc/sysconfig/nfs on RHEL)|
Conversely, the name of the service under Linux can also be added as an exception instead of the port; for example, high-availability for Pacemaker. Refer to your distribution for the names if this is the direction you wish to pursue. For example, on RHEL the command to add in Pacemaker is
sudo firewall-cmd –permanent –add-service=high-availability
Install SQL Server packages for availability
On a Windows-based SQL Server installation, some components are installed even in a basic engine install, while others are not. Under Linux, only the SQL Server engine is installed as part of the installation process. Everything else is optional. For highly available SQL Server instances under Linux, two packages should be installed with SQL Server: SQL Server Agent (mssql-server-agent) and the high availability (HA) package (mssql-server-ha). While SQL Server Agent is technically optional, it is SQL Server’s scheduler for jobs and is required by log shipping, so installation is recommended. On Windows-based installations, SQL Server Agent is not optional.
When AGs or FCIs are configured on a Windows-based configuration, they are cluster-aware. Cluster awareness means that SQL Server has specific resource DLLs that a WSFC knows about (sqagtres.dll and sqsrvres.dll for FCIs, hadrres.dll for AGs) and are used by the WSFC to ensure that the SQL Server clustered functionality is up, running, and functioning properly. Because clustering is external not only to SQL Server but Linux itself, Microsoft had to code the equivalent of a resource DLL for Linux-based AG and FCI deployments. This is the mssql-server-ha package, also known as the SQL Server resource agent for Pacemaker.
The other optional packages for SQL Server on Linux, SQL Server Full-Text Search (mssql-server-fts) and SQL Server Integration Services (mssql-server-is), are not required for high availability, either for an FCI or an AG.
Pacemaker for Always On Availability Groups and failover cluster instances on Linux
As previous noted, the only clustering mechanism currently supported by Microsoft for AGs and FCIs is Pacemaker with Corosync. This section covers the basic information to understand the solution, as well as how to plan and deploy it for SQL Server configurations.
HA add-on/extension basics
All of the currently supported distributions ship a high availability add-on/extension, which is based on the Pacemaker clustering stack. This stack incorporates two key components: Pacemaker and Corosync. All the components of the stack are:
- Pacemaker – The core clustering component, that does things like coordinate across the clustered machines.
- Corosync – A framework and set of APIs that provides things like quorum, the ability to restart failed processes, and so on.
- libQB – Provides things like logging.
- Resource agent – Specific functionality provided so that an application can integrate with Pacemaker.
- Fence agent – Scripts/functionality that assist in isolating nodes and deal with them if they are having issues.
This solution is in some ways similar to, but in many ways different from deploying clustered configurations using Windows. In Windows, the availability form of clustering, called a Windows Server failover cluster (WSFC), is built into the operating system, and the feature that enables the creation of a WSFC, failover clustering, is disabled by default. In Windows, AGs and FCIs are built on top of a WSFC, and share tight integration because of the specific resource DLL that is provided by SQL Server. This tightly coupled solution is possible by and large because it is all from one vendor.
On Linux, while each supported distribution has Pacemaker available, each distribution can customize and have slightly different implementations and versions. Some of the differences will be reflected in the instructions in this article. The clustering layer is open source, so even though it ships with the distributions, it is not tightly integrated in the same way a WSFC is under Windows. This is why Microsoft provides mssql-server-ha, so that SQL Server and the Pacemaker stack can provide close to, but not exactly the same, experience for AGs and FCIs as under Windows.
Pacemaker concepts and terminology
This section documents the common concepts and terminology for a Pacemaker implementation.
A node is a server participating in the cluster. A Pacemaker cluster natively supports up to 16 nodes. This number can be exceeded if Corosync is not running on additional nodes, but Corosync is required for SQL Server. Therefore, the maximum number of nodes a cluster can have for any SQL Server-based configuration is 16; this is the Pacemaker limit, and has nothing to do with maximum limitations for AGs or FCIs imposed by SQL Server.
Both a WSFC and a Pacemaker cluster have the concept of a resource. A resource is specific functionality that runs in context of the cluster, such as a disk or an IP address. For example, under Pacemaker both FCI and AG resources can get created. This is not dissimilar to what is done in a WSFC, where you see a SQL Server resource for either an FCI or an AG resource when configuring an AG, but is not exactly the same due to the underlying differences in how SQL Server integrates with Pacemaker.
Pacemaker has standard and clone resources. Clone resources are ones that run simultaneously on all nodes. An example would be an IP address that runs on multiple nodes for load balancing purposes. Any resource that gets created for FCIs uses a standard resource, since only one node can host an FCI at any given time.
Similar to roles in a WSFC, a Pacemaker cluster has the concept of a resource group. A resource group (called a set in SLES) is a collection of resources that function together and can fail over from one node to another as a single unit. Resource groups cannot contain resources that are configured as master or slave; thus, they cannot be used for AGs. While a resource group can be used for FCIs, it is not generally a recommended configuration.
WSFCs have various parameters for resources as well as things like dependencies, which tell the WSFC of a parent/child relationship between two different resources. A dependency is just a rule telling the WSFC which resource needs to be online first.
A Pacemaker cluster does not have the concept of dependencies, but there are constraints. There are three kinds of constraints: colocation, location, and ordering.
- A colocation constraint enforces whether or not two resources should be running on the same node.
- A location constraint tells the Pacemaker cluster where a resource can (or cannot) run.
- An ordering constraint tells the Pacemaker cluster the order in which the resources should start.
Quorum, fence agents, and STONITH
Quorum under Pacemaker is somewhat similar to a WSFC in concept. The whole purpose of a cluster’s quorum mechanism is to ensure that the cluster stays up and running. Both a WSFC and the HA add-ons for the Linux distributions have the concept of voting, where each node counts towards quorum. You want a majority of the votes up, otherwise, in a worst case scenario, the cluster will be shut down.
Unlike a WSFC, there is no witness resource to work with quorum. Like a WSFC, the goal is to keep the number of voters odd. Quorum configuration has different considerations for AGs than FCIs.
WSFCs monitor the status of the nodes participating and handle them when a problem occurs. Later versions of WSFCs offer such features as quarantining a node that is misbehaving or unavailable (node is not on, network communication is down, etc.). On the Linux side, this type of functionality is provided by a fence agent. The concept is sometimes referred to as fencing. However, these fence agents are generally specific to the deployment, and often provided by hardware vendors and some software vendors, such as those who provide hypervisors. For example, VMware provides a fence agent that can be used for Linux VMs virtualized using vSphere.
Quorum and fencing ties into another concept called STONITH, or Shoot the Other Node in the Head. STONITH is required to have a supported Pacemaker cluster on all Linux distributions.
The corosync.conf file contains the configuration of the cluster. It is located in /etc/corosync. In the course of normal day-to-day operations, this file should never have to be edited if the cluster is set up properly.
Cluster log location
Log locations for Pacemaker clusters differ depending on the distribution.
- RHEL and SLES – /var/log/cluster/corosync.log
- Ubuntu – /var/log/corosync/corosync.log
To change the default logging location, modify corosync.conf.
Plan Pacemaker clusters for SQL Server
This section discusses the important planning points for a Pacemaker cluster.
Virtualizing Linux-based Pacemaker clusters for SQL Server
Using virtual machines to deploy Linux-based SQL Server deployments for AGs and FCIs is covered by the same rules as for their Windows-based counterparts. There is a base set of rules for supportability of virtualized SQL Server deployments provided by Microsoft in Microsoft Support KB 956893. Different hypervisors such as Microsoft’s Hyper-V and VMware’s ESXi may have different variances on top of that, due to differences in the platforms themselves.
When it comes to AGs and FCIs under virtualization, ensure that anti-affinity is set for the nodes of a given Pacemaker cluster. When configured for high availability in an AG or FCI configuration, the VMs hosting SQL Server should never be running on the same hypervisor host. For example, if a two-node FCI is deployed, there would need to be at least three hypervisor hosts so that there is somewhere for one of the VMs hosting a node to go in the event of a host failure, especially if using features like Live Migration or vMotion.
Unlike a WSFC, Pacemaker does not require a dedicated name or at least one dedicated IP address for the Pacemaker cluster itself. AGs and FCIs will require IP addresses (see the documentation for each for more information), but not names, since there is no network name resource. SLES does allow the configuration of an IP address for administration purposes, but it is not required.
Like a WSFC, Pacemaker would prefer redundant networking, meaning distinct network cards (NICs or pNICs for physical) having individual IP addresses. In terms of the cluster configuration, each IP address would have what is known as its own ring. However, as with WSFCs today, many implementations are virtualized or in the public cloud where there is really only a single virtualized NIC (vNIC) presented to the server. If all pNICs and vNICs are connected to the same physical or virtual switch, there is no true redundancy at the network layer, so configuring multiple NICs is a bit of an illusion to the virtual machine. Network redundancy is usually built into the hypervisor for virtualized deployments, and is definitely built into the public cloud.
One difference with multiple NICs and Pacemaker versus a WSFC is that Pacemaker allows multiple IP addresses on the same subnet, whereas a WSFC does not.
Quorum and STONITH
Quorum configuration and requirements are related to AG or FCI-specific deployments of SQL Server.
STONITH is required for a supported Pacemaker cluster. Use the documentation from the distribution to configure STONITH.
This section documents how a Linux-based cluster can interact with a WSFC or with other distributions of Linux.
Currently, there is no direct way for a WSFC and a Pacemaker cluster to work together. This means that there is no way to create an AG or FCI that works across a WSFC and Pacemaker. However, there are two interoperability solutions, both of which are designed for AGs. The only way an FCI can participate in a cross-platform configuration is if it is participating as an instance in one of these two scenarios:
- An AG with a cluster type of None.
- A distributed AG, which is a special type of availability group that allows two different AGs to be configured as their own availability group.
Other Linux distributions
On Linux, all nodes of a Pacemaker cluster must be on the same distribution. For example, this means that a RHEL node cannot be part of a Pacemaker cluster that has a SLES node. The main reason for this was previously stated: the distributions may have different versions and functionality, so things could not work properly. Mixing distributions has the same story as mixing WSFCs and Linux: use None or distributed AGs.