Install and Configure SQL Server 2017 Availability Groups on Linux - Part 1

By: Edwin Sarmiento   |   Updated: 2017-11-22   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | > Availability Groups

Problem

In a previous tip on Installing SQL Server vNext CTP1 on Red Hat Linux 7.2, we read about how we can install SQL Server 2017 on a Linux operating system. We would like to evaluate running SQL Server 2017 Availability Groups on Linux. How do we go about building the Linux environment for SQL Server 2017 Availability Groups?

Solution

SQL Server 2017 is the very first version of SQL Server that supports running on top of the Linux operating system. The SQL Server product team has done an amazing job of making SQL Server the same database engine regardless of the underlying operating system. For this series of tips, you will be configuring SQL Server 2017 Always On Availability Groups on top of a Linux operating system. As a DBA, this is a great opportunity to learn about the Linux operating system to properly manage SQL Server on both the Windows and Linux.

If you have been working with SQL Server Always On Availability Groups in previous versions, there are several things to consider in the SQL Server 2017 version. The one that I would like to highlight is that you can now deploy SQL Server Always On Availability Groups without a Windows Server Failover Cluster (WSFC) or Pacemaker (Linux cluster resource manager). This is not designed to be a high availability solution, but rather for scale-out readable secondary replicas. For high availability configuration, you need to have a cluster resource manager – WSFC on Windows or Pacemaker on Linux. Now, this also means that the way you used to install and configure SQL Server Always On Availability Groups has changed. In previous versions, you must have a cluster resource manager – specifically WSFC - first before you can configure SQL Server Always On Availability Groups. With SQL Server 2017, you can configure the cluster resource manager either before or after configuring SQL Server Always On Availability Groups.

Here’s a high-level overview of the process involved in installing and configuring SQL Server 2017 Always On Availability Groups on Linux.

1. Install and configure SQL Server 2017 on the Linux cluster nodes
2. Create the SQL Server 2017 Always On Availability Group
3. Configure Pacemaker - the Linux cluster resource manager
4. Add the SQL Server 2017 Always On Availability Group as a resource in the cluster

Step #3 can easily be the first step, depending on your preference. You can start with installing and configuring Pacemaker and get it ready prior to configuring SQL Server 2017 Always On Availability Group, similar to how it was in previous versions of SQL Server on Windows. That choice is up to you.

The configuration used in this tip consists of three (3) Linux servers running CentOS 7 – a free, enterprise-class, community-supported distribution based on Red Hat Enterprise Linux (RHEL). This gives you the opportunity to play around with the Linux operating system without the hassle of registering for a Red Hat Subscription. And because the distribution is based on RHEL, the instructions for installing and configuring SQL Server on Linux RHEL apply to CentOS 7.

The Linux servers were installed using the Infrastructure Server option and including the High Availability Add-Ons.

Below are the details of the implementation. The servers can access the Internet to download the necessary components.

The servers have also been added to the DNS for name resolution, including the Availability Group listener name.

Unlike when a SQL Server Always On Availability Group is tightly integrated with a WSFC, you need to manually add their corresponding DNS entries.  This is simply a mapping of the server hostname with its IP address. You can either ask your DNS administrator to perform this task for you or you can do it yourself, assuming you have administrative privileges on the DNS server.

Install and configure SQL Server 2017 on the Linux cluster nodes

Installation and configuration of SQL Server 2017 on Linux varies depending on the Linux distribution that you are using. This tip will reference installation and configuration of SQL Server 2017 on RHEL.

You can use your preferred SSH client to connect to the Linux servers. Refer to this tip on Using ssh to connect to Linux for the SQL Server DBA for additional information.

NOTE: Perform these steps on all of the Linux servers. Remember to log in with super user (root) privileges when performing these steps.

To install and configure SQL Server 2017 on the Linux hosts,

• Run the command below to download the Microsoft SQL Server Red Hat repository configuration file. A repository configuration file contains information about the collection of packages together with the dependencies that SQL Server will use.

sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo  
   

• Run the command below to install SQL Server on Linux. This will download the SQL Server installation packages for RHEL. Once the download completes, it will automatically run the installation.

sudo yum install -y mssql-server  
   

• After the package installation completes, run the command below to configure SQL Server using the default configuration. Follow the prompts to choose your SQL Server edition and set the sa password.

sudo /opt/mssql/bin/mssql-conf setup  
   

• After the configuration completes, run the command below to install SQL Server Agent on Linux.

sudo yum install mssql-server-agent   
   

• Similar to when configuring SQL Server Agent on Windows, you need to restart the SQL Server daemon (service) in order for the changes to take effect.

sudo systemctl restart mssql-server  
   

• Run the command below to enable the SQL Server daemon (service) to launch on server boot.

sudo systemctl enable mssql-server  
   

• Run the command below to verify that SQL Server on Linux is running.

sudo systemctl status mssql-server
   

• Run the command below to enable the Linux firewall to allow remote connections. This uses the default SQL Server port number 1433. By default, FirewallD is the firewall solution available on RHEL/CentOS.

sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent  
   

• Run the command below to reload the new firewall rule added.

sudo firewall-cmd --reload
   

After installing and configuring SQL Server 2017 on Linux, you can test connectivity by using SQL Server Management Studio and running simple queries. Since the servers are running Linux, the sys.dm_os_host_info dynamic management view is use instead of the sys.dm_os_windows_info.

SELECT @@SERVERNAME, @@VERSION, host_platform, host_distribution, host_release
FROM sys.dm_os_host_info
GO   

In this tip, you’ve installed and configured SQL Server 2017 on a Linux operating system using the CentOS 7 distribution in preparation for configuring SQL Server Always On Availability Groups. In the next tip in this series, you will go thru the process of configuring SQL Server Always On Availability Groups on the Linux servers.

Next Steps

• Review the previous tips on SQL Server on Linux
  • Installing SQL Server vNext CTP1 on Red Hat Linux 7.2
  • Using ssh to connect to Linux for the SQL Server DBA
  • Configure SQL Server on Linux
  • SQL Server Configuration Manager for Linux
  • How to Stop, Start, Enable and Disable SQL Server Services on Linux
  • Top 10 Linux Commands for SQL Server DBAs
  • SQL Server on Linux Tips
• Read more on the following topics
  • Install SQL Server and create a database on Red Hat

About the author

Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips