Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

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


By:   |   Read Comments   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > 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.

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.

Server Details
LINUXHA-SQLAG1 OS: CentOS 7 (Linux 3.10.0-693.el7.x86_64)
  IP Address: 10.10.10.55/24
LINUXHA-SQLAG2 OS: CentOS 7 (Linux 3.10.0-693.el7.x86_64)
  IP Address: 10.10.10.56/24
LINUXHA-SQLAG3 OS: CentOS 7 (Linux 3.10.0-693.el7.x86_64)
  IP Address: 10.10.10.57/24
Availability Group Listener Name LINUX-SQLAG
  IP Address: 10.10.10.58

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

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  
   
command to download the Microsoft SQL Server Red Hat repository configuration file
  • 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  
   
command to install SQL Server on Linux
  • 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  
   
command to configure SQL Server using the default configuration
  • After the configuration completes, run the command below to install SQL Server Agent on Linux.
sudo yum install mssql-server-agent   
   
command to install SQL Server Agent on Linux
  • 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  
   
restart the SQL Server daemon (service) in order for the changes to take effect
  • Run the command below to enable the SQL Server daemon (service) to launch on server boot.
sudo systemctl enable mssql-server  
   
enable the SQL Server daemon (service) to launch on server boot
  • Run the command below to verify that SQL Server on Linux is running.
sudo systemctl status mssql-server
   
verify that SQL Server on Linux is running
  • 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  
   
enable the Linux firewall to allow remote connections
  • Run the command below to reload the new firewall rule added.
sudo firewall-cmd --reload
   
command to reload the new firewall rule added

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   
test connectivity by using SQL Server Management Studio and running simple queries

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


Last Update:


next webcast button


next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools