Rajendra Gupta
Announcing SQL Server Linux - The Official Microsoft Blog https://blogs.microsoft.com/blog/2016/03/07/announcing-sql-server-on-linux/

SQL Server Always On Availability Groups for SQL Server Linux instances

October 6, 2020 by

In this 29th article of the SQL Server Always On Availability Groups series, we configure the AG between SQL Server Linux instances.

Introduction

SQL Server 2017 & 2019 works on the cross-platform operating system: Windows and Linux. You can use SQL Server on Red Hat Enterprise Linux (RHEL), SUSE Linux Enterprise Server, and Ubuntu. You can install it on-premise or cloud solutions such as Azure, AWS.

Announcing SQL Server Linux - The Official Microsoft Blog

As we can use SQL on both platforms, it is essential to understand the high-availability and disaster recovery mechanism on the Linux SQL as well. In the previous articles (see ToC at the bottom), we explored various aspects of SQL Server Always On Availability Groups for Windows-based servers. In this article, we will configure the availability groups between two Linux VM’s.

Environment details

In this article, we create an availability group between two Linux instances. For this purpose, we require two virtual machines in the Oracle VM VirtualBox. You can follow my earlier article SQL Server 2019 on Linux with Ubuntu, and prepare the virtual machines with Ubuntu OS.

I use the following virtual machines in this article:

  • linuxnode2: 10.0.2.51 – Initially, it acts as the primary replica
  • linuxnode3: 10.0.2.50 – Initially, it acts as the Secondary replica
  • Domain Controller and DNS: VDITest3 (10.0.2.15)
  • SSMS client on Windows VM: SQLNode3 (10.0.2.44)

Configure the SQL Server Linux VM’s for static IPs

By default, once you create a virtual machine, it gets a dynamic IP address. The dynamic IP address might change when you reboot the OS. We need to assign the static IP address using the network configuration of Ubuntu.

Connect to the virtual machine linuxnode3, launch terminal and install the ifconfig utility like below. We use the ifconfig command to check the network interfaces and associated IP addresses.

Linux VM's for static IP's

Now, open the network settings, and it shows you two network interfaces. I use one interface for internet connectivity and the other for the static IP configuration.

network settings

Open the enp0s3 interface and assign a static IP, subnet mask, and DNS, as shown below.

assign a static IP

Apply the configuration and verify the IP address using the ifconfig command. It shows the IP address in the inet section.

 IP address using ifconfig command

Verify the hostname for your virtual machine using the sudo cat /etc/hostname command. If required, you can open a vi editor and modify the hostname as per your requirement.

Verify the hostname

Similarly, configure the linuxnode2 virtual machine for the static IP address and verify its hostname.

virtual machine for the static IP address

Verify host names

Host file entry in the virtual servers

In this article, we do not have servers as a member of the domain. To resolve the IP address with the server name, we can either add an entry in the DNS or update the host file.

To update the host file, run the following command:

In this, add the IP address and hostname of both virtual machines, as shown below.

Host file entry in the virtual servers

Now, you should test ping to another node with the hostname, and it returns the IP address.

Ping response from linuxnode3 to linuxnode2:

Ping response from linuxnode3 to linuxnode2

Ping response from linuxnode2 to linuxnode3:

Ping response from linuxnode2 to linuxnode3

Install SQL Server on SQL Server Linux server(linuxnode3)

We install the SQL Server 2019 on both the virtual machines. To install SQL Server 2019, launch the terminal and use the following steps.

Import the public repository GPG keys:

In this step, import the GPG public keys from the Microsoft URL. It should return status OK as a successful script execution.

Register the SQL Server 2019 Ubuntu repository

In this step, we use the add-apt-repository command to register the latest SQL Server 2019 Ubuntu repository available at the specified package URL.

You can browse the URL and check the SQL Server repository.

Register the SQL Server 2019 Ubuntu repository

Register the 2019 Ubuntu repository

Update the package lists

Run the apt-get command to update the package list and repositories.

Update the package lists

Install SQL Server on Ubuntu

The below command downloads the SQL Server installations and installs the SQL Server without configuring it.

Install SQL Server on Ubuntu

Install SQL Server on Ubuntu further screenshots

To configure the SQL Server, it shows the command in the output for configuring the edition, language, SA password.

Once you run the command, it asks for several inputs.

  • Edition of SQL Server: You need to enter the serial number for the SQL Server version. For the developer instance, specify the code 2

    Edition of SQL Server

  • Accept the license terms and conditions
  • Specify the language for your SQL Server installation

    Accept the license terms

  • Enter the password for the SA login in SQL Server

    Enter the password

Once the SQL Server setup completes, verify the SQL service status. As shown below, it is in the active (running) status.

verify the SQL service status

Similarly, install SQL Server 2019 on the linuxnode2 as well.

Install Azure Data Studio on SQL Server Linux virtual machines

Azure Data Studio works on the Linux operating system as well. You can install the Azure Data Studio and launch it to create the connections to SQL Server Linux.

Install Azure Data Studio

In the new connection window, specify the server name and SQL Server authentication details.

Install Azure Data Studio

Here, we can see connections to both SQL instances are successful in Azure Data Studio.

SQL instances are successful

Configure the SQL instance for high availability

In the Windows SQL Server, we enable the SQL Server Always On using the SQL Server Configuration Manager. SQL Server Linux does not have a GUI configuration manager. We use mssql-conf utility for enabling the HADR.

You need to restart SQL services using the following command:

SQL instance for high availability

You need to configure the high availability on both SQL nodes linuxnode2 and linuxnode3.

Enable the AlwaysOn_health extended event session

Connect to both SQL instances (linuxnode2 & linuxnode3) and execute the below query to enable and start the extended event session for the SQL Server Always On Availability Groups.

Enable the AlwaysOn_health extended event session

Create the Master key, Certificate and backup the Certificate on the primary replica

Connect to the SQL instance that you want to configure as a primary replica and execute the following queries:

  • Create a database master key and encrypt with a password

  • Create a certificate with the CREATE CERTIFICATE statement

  • Backup the certificate and the private key

    It creates a certificate backup along with the private key file at the SQL Server Linux default data folder /var/opt/mssql/data/

    Create the Master key, Certificate and backup the Certificate on the primary replica

Copy the backup and private key file on another SQL node

In this step, we need to copy the files from the primary replica to the secondary replica.

This command copies both files from the source SQL server (linuxnode2) to the destination server (linuxnode3) in the /tmp directory.

In the output, you can verify it coped both *.cer and *.pvk file in the secondary replica.

Copy the backup and private key file

Now, connect to the linuxnode3 using terminal and copy these Certificate, private key file into the /var/opt/mssql/data.

You could copy the files directly to the data directory in the secondary replica if your account has permission to write in the SQL data folder.

Run the ls command to view the file in the /var/opt/mssql/data directory.

copy this Certificate

We need to verify the permissions for the Certificate and private key. SQL account (mssql) should be able to access these files.

Using the ls-lrt command, I show the owner of these files are the root user

verify the permissions

To change the ownership, run the chown command, as shown below.

Rerun the ls-lrt command and verify that ownership is now with the mssql account.

change the ownership

Now, connect to the secondary replica instance and create the Certificate using the files present in the /var/opt/mssql/data directory. In case you use a different directory, your SQL account should have permissions to access the files.

create the Certificate

Create HADR endpoints on both primary and secondary replica

In this step, create the HADR endpoints on both the primary and secondary replica. This endpoint is for communication between the primary and secondary replica instances. By default, it uses the 5022 port. You need to specify the certificate name in the AUTHENTICATE section.

Note: You should allow port 5022 in the firewall so that primary and secondary replica can communicate on it.

To open the firewall port in Ubuntu, run the following command.

Create HADR endpoints

Create the SQL Server Always On Availability Group

We use the following CREATE AVAILABILITY GROUP statement to create the Availability group. Let me explain the query and its argument values here.

  • Define a name for the availability group in the CREATE AVAILABILITY GROUP statement. (CREATE AVAILABILITY GROUP [ag2])
  • CLUSTER_TYPE: We do not have configured a Linux cluster between the linuxnode2 and linuxnode3 servers. Therefore, use the value NONE in the CLUSTER_TYPE
  • ENDPOINT_URL: Specify the endpoint URL for the respective SQL instance. It is in the format of TCP://[server]:[port]
  • AVAILABILITY_MODE: We can use asynchronous commit for the cluster less Linux SQL AG
  • FAILOVER_MODE: In an asynchronous commit, we only have manual failover
  • SEEDING_MODE: We use automatic seeding to create the secondary database and synchronize it with the primary replica
  • Grant permissions to create the database on the secondary replica

Create the SQL Server Always On Availability Group

Join secondary replicas to the SQL Server Always On Availability Group

Execute this query on the secondary replica to join the availability group. We need to specify the cluster type as NONE.

Join secondary replicas

Create a sample database on the primary replica and take a full backup

In the previous step, we configured an availability group. Now, to add a database, create a new database on the primary replica instance and take a full backup so that database meets the AG prerequisite.

Create a sample database

Add a database to the SQL Server Always On Availability Group

Execute the query to add the database [LinuxAGSQL] into the availability group.

It adds the database into the availability group and uses the automatic seeding for secondary data initialization.

Add a database

Verify the secondary database and its synchronization

We can verify that the secondary database is created successfully and is in synchronizing state for the asynchronous commit.

Verify the secondary database

Launch the AG dashboard and verify AG health

You can install the SQL Server Management Studio on a Windows server that has firewall connections open to the Linux server. Open the SSMS, Connect to the primary replica and launch the AG dashboard.

AG dashboard looks adequate for the primary and secondary Linux instances.

Launch the AG dashboard

Perform a failover for Linux SQL Server Always On Availability Group

As you know, we can use a force failover for the asynchronous commit SQL Server Always On Availability Group.

Launch the failover availability group wizard, and it shows a warning for possible data loss. It is acceptable for our lab environment.

Perform a failover

Accept the failover with the potential data loss.

Accept the failover

Connect to the secondary replica using the SA authentication.

SA authentication

Validate the things and perform failover for the [LinuxAGSQL] database.

validate the things

You can execute the following script to perform an AG failover in an asynchronous mode as well.

Launch the AG dashboard from the new primary, and it shows a warning for the secondary replica.

warning for the secondary replica

In the case of force failover, SQL Server suspends data movement, therefore, expand the availability group database in secondary replica and resume data movement.

resume data movement

It resumes data movement for SQL Server Always On Availability Group, and Its dashboard becomes healthy, as shown below.

dashboard becomes healthy

Conclusion

In this article, we configured the SQL Server Always On Availability Group between SQL Server Linux instances on SQL Server 2019. Theses instances do not have domain membership. As SQL Server works on both Windows and Linux, you should be familiar with high availability configurations on both platforms.

Table of contents

A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
Configure failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups
Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups
Add a new node into existing SQL Server Always On Availability Groups
Configure Managed Service Accounts for SQL Server Always On Availability Groups
Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts
Database-level health detection in SQL Server Always On Availability Groups
Automatic Page Repair in SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
Configure a SQL Server Always On Availability Group on the domain-independent Failover Cluster
An overview of distributed SQL Server Always On Availability Groups
Deploy a distributed SQL Server Always On Availability Group
Monitor and failover a Distributed SQL Server Always On Availability Group
Transparent Data Encryption for SQL Server Always On Availability Groups
Configure SQL Server replication for a database in SQL Server Always On Availability Groups
Configuring SQL Server replication for distribution databases in SQL Server Always On Availability Groups
Explore Cross-database MSDTC for distributed transactions in SQL Server Always On Availability Groups
Deploy MSDTC for distributed transactions in SQL Server Always On Availability Groups
Restore an existing availability group database participating in SQL Server Always On Availability Groups
Exploring AG dashboards for monitoring SQL Server Always On Availability Groups
Backup compression in TDE enabled databases in SQL Server Always On Availability Groups
Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups
Refresh SQL Server Always On Availability Group databases using DBATools PowerShell
Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups
Suspend and Resume Data Movement in SQL Server Always On Availability Groups
Explore failover types in SQL Server Always On Availability Groups
Explore SQL Server PolyBase External Tables in SQL Server Always On Availability Groups
SQL Server Always On Availability Groups for SQL Server Linux instances
Column-level SQL Server encryption with SQL Server Always On Availability Groups
Make the most of secondary replicas in SQL Server Always On Availability Groups
Apply SQL Server patches or cumulative updates in SQL Server Always On Availability Groups

Rajendra Gupta
120 Views