Rajendra Gupta
Relaunch the AG dashboard

Configure cross-platform SQL Server Always On Availability Groups

August 10, 2022 by

This article for the Always On Availability Groups series will show how to configure SQL Server Always On Availability Groups between Windows and Linux SQL instances.

Introduction

Microsoft supports SQL Server on Linux, and it has many of the same features as the Windows version. You can restore databases from Windows to Linux SQL or vice versa. The Linux SQL works with Red Hat, Ubuntu, SUSE enterprise, Kubernetes containers, and Docker.

Windows-based SQL instance supports SQL Server Always On Availability Groups for high availability and disaster recovery. If you are not familiar with Windows AG configuration, refer to the extensive series on Always on Availability Group (Toc at the bottom).

If you have both Windows and Linux SQL Server, is it possible to configure an availability group between them? Let’s explore this in this article.

Requirements

We require the following environment for implementing Windows and Linux Always On.

  • A Windows-based SQL instance: For this article, we use SQL Server 2019 developer edition.
    • SQL instance: WindowsSQL
  • A Linux SQL instance: Refer to SQL Server on Linux category and deploy a SQL instance on Ubuntu. The Linux SQL instance should be running with SQL 2019 developer edition.
    • SQL instance: LinuxSQL

Steps to configure SQL Server Always On Availability Groups between Windows and Linux SQL

The article configures cross-platform AG between a Windows and Linux SQL instance. You cannot use a cluster here because there is no cluster mechanism to manage Linux and Windows servers.

Windows to Linux secondary SQL Server Always On Availability Group

Step 1: Configure the Always-on for Windows SQL instance

For the demo, we configure the SQL Server Always On Availability Group configuration as below.

  • Primary AG: Windows SQL instance
  • Secondary AG: Linux SQL instance

Enable Always on Availability Group in Windows

Launch SQL Server Configuration Manager and put a check on the option – Enable Always On Availability Group.

Enable Always on Availability Group in Windows

Click Ok and restart the SQL service to enable the availability group.

Warning

Mixed mode authentication

Connect to Windows SQL instance, click on security In the SQL Server properties, and make sure it uses mixed-mode authentication (SQL Server and Windows authentication mode) for SQL instance.

Mixed mode authentication

Configure host file

In the host file, specify the IP address and hostname of the Linux server. Open the host file in notepad from C:\Windows\System32\drivers\etc and add Linux hostname, IP address.

For my environment, I enter the IP address and hostname of the Linux server in the following format.

192.168.0.102 linuxsql

Windows Firewall

If you use a Windows firewall, make sure it allows ports 1433 and 5022 for communication.

Create a database user

Create a database login and password on the primary replica instance using the CREATE LOGIN and CREATE USER statement.

Create a master key and certificate and, then back up the certificate with a private key

In this step, we create a database master key and certificate. Further, we take the certificate and its private key backup. Later, we need to use this certificate backup to deploy the certificate on the Linux SQL instance.

  • Create master key:
  • Create Certificate
  • Backup certificate and encrypt it with a password

Step 2: Configure the Always-on for Linux SQL instance

Enable Always on Availability Group in Linux

SQL Server on Linux provides mssql-conf utility to enable HADR always-on functionality. Run the following command for enabling hadr via mssql-conf.

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1

Once you enable the HADR feature, restart the SQL Service with the command –

sudo systemctl restart mssql-server.service

Configure host file

In the Linux environment, the host file is located at /etc/hosts. Use the vi editor to enter the Windows SQL Server name and IP address. The following screenshot specifies the 192.168.0.100 IP address for Windows OS.

Configure host file

Configure Linux firewall

If you use the firewall in Linux, make sure it allows port 5022 for endpoints communication.

Create a database user

Connect to the Linux SQL instance and create a login with the following script.

Step 3: Copy the certificate and private key from Windows to Linux server

We need to copy the certificate backup and its private key from Windows to Linux SQL Server. You can copy files from Windows server to Linux with an application such as FileZilla. We need to save the certificate and private key in the /var/opt/mssql/data.

Also, set the owner and group permissions to the mssql user using following script.

sudo chown mssql:mssql /var/opt/mssql/data/dbm_certificate.pvk
sudo chown mssql:mssql /var/opt/mssql/data/dbm_certificate.cer

Check directory space

Step 4: Create certificate on Linux instance

This step creates a certificate using the certificate backup and private key copied from Windows SQL.

  • The script specifies SQL user dbm_user for authorization.
  • Specify the decryption password in the section – DECRYPTION BY PASSWORD. This password should be the same password that we used while taking certificate backup using ENCRYPTION BY PASSWORD.

Create certificate

Step 5: Create endpoints on Windows (primary) and Linux (secondary) AG replicas

Execute the following CREATE ENDPOINT script on both Windows and Linux AG replicas. It uses port 5022 for communication.

The script also starts the endpoint (STATE=STARTED), and grants connect permission to the SQL login on the endpoint. It species the certificate for authentication purposes.

Create endpoint

Step 6: Create an availability group on the primary replica

Execute the following script on the Windows SQL (primary replica) to create the availability group. The script uses the following values.

  • CLUSTER_TYPE= NONE: We do not have a cluster in the cross-platform SQL Server Always On Availability Group. Therefore, use the CLUSTER_TYP as NONE.
  • AVAILABILITY MODE: Use Asynchronous commit availability mode.
  • SEEDING MODE: Use the manual seeding mode because the database paths are different in Windows and Linux.
  • FAILOVER_MODE: It only supports manual failover.
  • SECONDARY_ROLE: Allow all connections for the secondary AG.

Step 7: Join the availability group on the secondary replica

On the secondary replica, use ALTER AVAILABILITY GROUP statement to join the availability group.

Step 8: Add a database into the availability group

Create a new database into the primary replica and do the following steps.

  • Take a full backup on Windows (primary ) SQL
  • Copy the full backup onto Linux SQL using FileZilla.
  • Restore the backup into NORECOVERY MODE in Linux SQL instance.
  • Run the following statement on the primary replica to add the database into the availability group

Step 9: Launch AG dashboard and view its health

Connect to the primary SQL instance in SSMS and launch the AG dashboard. As shown below, the availability group state is healthy. The AG is an asynchronous state; therefore, its synchronization state is Synchronizing. AG dashboard

SQL Server Always On Availability Group failover in cross-platform systems

You can failover from primary to secondary replica in the cross-platform cluster in two ways.

  • Manual failover without the data loss
  • Forced failover with possible data loss

Let’s explore both approaches for the AG failover.

Manual failover without the data loss

You can follow the steps below for manual failover without data loss.

  • Connect to the primary replica and modify the AG mode to Synchronous commit.

It changes the replica into a Synchronized state.

changes the replica into a Synchronized state inSQL Server Always On Availability Group

Take the availability group into offline stat using the following ALTER AVAILABILITY GROUP statement.

ALTER AVAILABILITY GROUP [AG1] OFFLINE

Promote the secondary target replica to primary:

The following statement changes the role of old primary to secondary. Execute the following statement on the old primary replica.

The secondary database in the availability group remains in the SUSPEND state. You can resume availability group synchronization using the following statement on the secondary instance.

Now, you can relaunch the AG dashboard from the new primary replica, and it shows that the Linux SQL instance is working as a primary replica.

Relaunch the AG dashboard

Forced manual failover with data loss

Suppose the primary replica instance is down. Therefore, to bring the database available in the secondary replica, you require forced failover, and it might have data loss involved

On the secondary replica (new primary replica), initiate the forced failover using the FORCE_FAILOVER_ALLOW_DATA_LOSS parameter.

Remove the original primary replica from the availability group:

If the old primary replica comes online, it tries to take the primary role. Therefore, once it comes online, immediately take availability group AG1 offline on the original primary:

Then drop the availability group and database on the original (old) primary replica.

Conclusion

This article configured the cross-platform SQL Server Always On Availability Groups between Windows and Linux SQL Server. It is helpful for manual disaster recovery, migrations, and read-scale workloads. It does not support automatic failover because there is no cross-platform cluster configuration.

Rajendra Gupta
617 Views