| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | ms.custom | |
|---|---|---|---|---|---|---|---|---|---|---|
SQL Server high availability for Linux deployments |
Learn about the high availability options for SQL Server on Linux, such as availability groups, failover cluster instances (FCI), and log shipping. |
rwestMSFT |
randolphwest |
randolphwest |
10/19/2022 |
sql |
linux |
conceptual |
|
[!INCLUDE SQL Server - Linux]
Starting with [!INCLUDEsssql17-md], [!INCLUDEssnoversion-md] is supported on both Linux and Windows. Like Windows-based [!INCLUDEssnoversion-md] deployments, [!INCLUDEssnoversion-md] databases and instances need to be highly available under Linux. This article covers the technical aspects of planning and deploying highly available Linux-based [!INCLUDEssnoversion-md] databases and instances, as well as some of the differences from Windows-based installations. Because [!INCLUDEssnoversion-md] may be new for Linux professionals, and Linux may be new for [!INCLUDEssnoversion-md] professionals, the article at times introduces concepts that may be familiar to some and unfamiliar to others.
[!INCLUDEssnoversion-md] availability options for Linux deployments
Besides backup and restore, the same three availability features are available on Linux as for Windows-based deployments:
- Availability groups - SQL Server on Linux
- Failover Cluster Instances - SQL Server on Linux
- Log shipping - SQL Server on Linux
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 [!INCLUDEsssql17-md]. A WSFC doesn't exist in Linux. Clustering implementation in Linux is discussed in Pacemaker for availability groups and failover cluster instances on Linux.
While some Linux installations might be installed with an interface, most aren't, 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. Ifsuis 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
sudobefore executing anything. Many of the examples in this article usesudo.
Some common commands, each of which have various switches and options that can be researched online:
cd- change the directorychmod- change the permissions of a file or directorychown- change the ownership of a file or directoryls- show the contents of a directorymkdir- create a folder (directory) on a drivemv- move a file from one location to anotherps- show all of the working processesrm- delete a file locally on a serverrmdir- 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.
This section covers tasks that are common to all Linux-based [!INCLUDEssnoversion-md] deployments.
Copying files from one server to another is a task that anyone using [!INCLUDEssnoversion-md] on Linux should be able to do. This task is very important for AG configurations.
Things like permission issues can exist on Linux and on Windows-based installations. However, those familiar with how to copy from server to server on Windows might 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 might not be installed. If it isn't, OpenSSH needs to be installed first. For more information on configuring OpenSSH, see the information at the following links for each distribution:
When using scp, you must provide the credentials of the server if it isn't the source or destination. For example, using
scp MyAGCert.cer username@servername:/folder/subfoldercopies the file MyAGCert.cer to the folder specified on the other server. You must have permissions - and possibly ownership - of the file to copy it, so chown might 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. For more information on configuring Samba, see the information at the following links for each distribution:
Windows-based SMB shares can also be used; SMB shares don't need to be Linux-based, as long as the client portion of Samba is configured properly on the Linux server hosting [!INCLUDEssnoversion-md] and the share has the right access. For those in a mixed environment, this would be one way to use existing infrastructure for Linux-based [!INCLUDEssnoversion-md] 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 [!INCLUDEsssql11-md], 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. A good source of information on SMB and Linux is SMB3 in Samba.
Finally, using a network file system (NFS) share is an option. Using NFS isn't an option on Windows-based deployments of [!INCLUDEssnoversion-md], and can only be used for Linux-based deployments.
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 might 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 [!INCLUDEssnoversion-md] deployments on Linux.
| Port Number | Type | Description |
|---|---|---|
| 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 | [!INCLUDEssnoversion-md] - 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 | [!INCLUDEssnoversion-md] - default port used for an AG endpoint; can be changed when creating the endpoint |
| 5403 | TCP | Pacemaker |
| 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) |
For additional ports that might be used by Samba,see Samba Port Usage.
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-availabilityOn a Windows-based [!INCLUDEssnoversion-md] installation, some components are installed even in a basic engine install, while others aren't. Under Linux, only the [!INCLUDEssnoversion-md] engine is installed as part of the installation process. Everything else is optional. For highly available [!INCLUDEssnoversion-md] instances under Linux, two packages should be installed with [!INCLUDEssnoversion-md]:
- [!INCLUDEssnoversion-md] Agent (
mssql-server-agent) - the high availability (HA) package (
mssql-server-ha)
While [!INCLUDEssnoversion-md] Agent is technically optional, it is the [!INCLUDEssnoversion-md] scheduler for jobs and is required by log shipping, so installation is recommended.
On [!INCLUDE sssql17-md] with CU 4 and later versions, [!INCLUDEssnoversion-md] Agent is included in the Database Engine package, but you still need to enable it. On Windows-based installations, [!INCLUDEssnoversion-md] Agent isn't optional.
Note
For those new to [!INCLUDEssnoversion-md], [!INCLUDEssnoversion-md] Agent is [!INCLUDEssnoversion-md]'s built-in job scheduler. It is a common way for DBAs to schedule things like backups and other [!INCLUDEssnoversion-md] maintenance. Unlike a Windows-based installation of [!INCLUDEssnoversion-md] where [!INCLUDEssnoversion-md] Agent is a completely different service, on Linux, [!INCLUDEssnoversion-md] Agent runs in context of [!INCLUDEssnoversion-md] itself.
When AGs or FCIs are configured on a Windows-based configuration, they are cluster-aware. Cluster awareness means that [!INCLUDEssnoversion-md] 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 [!INCLUDEssnoversion-md] clustered functionality is up, running, and functioning properly. Because clustering is external not only to [!INCLUDEssnoversion-md] 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 [!INCLUDEssnoversion-md] resource agent for Pacemaker. To install the mssql-server-ha package, see Deploy a Pacemaker cluster for SQL Server on Linux.
The other optional packages for [!INCLUDEssnoversion-md] on Linux, [!INCLUDEssnoversion-md] Full-Text Search (mssql-server-fts) and [!INCLUDEssnoversion-md] Integration Services (mssql-server-is), aren't required for high availability, either for an FCI or an AG.
To provide high availability and disaster recovery for your [!INCLUDE ssnoversion-md] services, choose from a wide variety of industry-leading tools. This section highlights [!INCLUDE msconame-md] partner companies with high availability and disaster recovery solutions supporting [!INCLUDE ssnoversion-md].
| Partner | Description |
|---|---|
| DH2i | DxEnterprise is Smart Availability software for Windows, Linux & Docker that helps you achieve the nearest-to-zero planned and unplanned downtime, unlocks huge cost savings, drastically simplifies management, and gets you both physical and logical consolidation. - Deploy availability group with DH2i for SQL Server containers on AKS - Tutorial: Set up a three node Always On availability group with DH2i DxEnterprise |
| HPE Serviceguard | HPE SGLX offers context-sensitive monitoring and recovery options for Failover Cluster Instance and Always On Availability Groups. Maximize uptime with HPE SGLX without compromising data integrity and performance. - Tutorial: Set up a three node Always On availability group with HPE Serviceguard for Linux. |
| Pacemaker | Pacemaker is an open source high-availability cluster resource manager. With Corosync, an open source group communication system, Pacemaker can detect component failures and orchestrate necessary failover procedures to minimize interruptions to applications. - Pacemaker for AGs and FCIs on Linux - Deploy a Pacemaker cluster for SQL Server on Linux |