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.
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.
Click Ok and restart the SQL service to enable the availability group.
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.
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.
|
1 2 3 4 5 6 |
CREATE LOGIN dbm_login WITH PASSWORD = 'Test@1234'; Go CREATE USER dbm_user FOR LOGIN dbm_login; GO |
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:
|
1 2 3 |
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Test@1234'; |
- Create Certificate
|
1 2 3 |
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm'; |
- Backup certificate and encrypt it with a password
|
1 2 3 4 5 6 7 8 9 |
BACKUP CERTIFICATE dbm_certificate TO FILE = 'C:\temp\dbm_certificate.cer' WITH PRIVATE KEY ( FILE = 'C:\temp\dbm_certificate.pvk', ENCRYPTION BY PASSWORD = ' Test@1234' ); GO |
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 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.
|
1 2 3 4 5 6 |
CREATE LOGIN dbm_login WITH PASSWORD = 'Test@1234'; Go CREATE USER dbm_user FOR LOGIN dbm_login; GO |
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
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.
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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE AVAILABILITY GROUP [ag1] WITH (CLUSTER_TYPE = NONE) FOR REPLICA ON N'WindowsSQL' WITH ( ENDPOINT_URL = N'tcp://WindowsSQL:5022', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, SEEDING_MODE = MANUAL, FAILOVER_MODE = MANUAL, SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL) ), N'LinuxSQL' WITH ( ENDPOINT_URL = N'tcp://LinuxSQL:5022', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, SEEDING_MODE = MANUAL, FAILOVER_MODE = MANUAL, SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL) ) GO |
Step 7: Join the availability group on the secondary replica
On the secondary replica, use ALTER AVAILABILITY GROUP statement to join the availability group.
|
1 2 3 4 |
ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE) |
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
|
1 2 3 4 |
ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [ImportDB] GO |
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.
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.
|
1 2 3 4 5 6 7 8 9 10 |
ALTER AVAILABILITY GROUP [ag1] MODIFY REPLICA ON N'linuxsql' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT); Go ALTER AVAILABILITY GROUP [ag1] MODIFY REPLICA ON N'WindowsSQL' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT) GO |
It changes the replica into a Synchronized state.
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:
|
1 2 3 4 |
ALTER AVAILABILITY GROUP AG1 FORCE_FAILOVER_ALLOW_DATA_LOSS; |
The following statement changes the role of old primary to secondary. Execute the following statement on the old primary replica.
|
1 2 3 4 |
ALTER AVAILABILITY GROUP [AG1] SET (ROLE = SECONDARY); |
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.
|
1 2 3 |
ALTER DATABASE [ImportDB] SET HADR RESUME |
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.
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.
|
1 2 3 4 |
ALTER AVAILABILITY GROUP [AG1] FORCE_FAILOVER_ALLOW_DATA_LOSS; |
Remove the original primary replica from the availability group:
|
1 2 3 4 |
ALTER AVAILABILITY GROUP [AG1] REMOVE REPLICA ON N'linuxsql'; |
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:
|
1 2 3 |
ALTER AVAILABILITY GROUP [AG1] OFFLINE |
Then drop the availability group and database on the original (old) primary replica.
|
1 2 3 4 5 6 7 |
DROP AVAILABILITY GROUP [AG1]; USE [master] GO DROP DATABASE [ImportDB] GO |
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023