SQL Server Clustering on Linux(Ubuntu)--> Part 1

Hi Mates,

In this post we are going to see how to perform SQL Clustering on Linux.

Unlike windows in Linux we need to make use of third party software's like pacemaker & corosync to perform clustering.

As the diagram below shows storage is presented to two servers. Clustering components - Corosync and Pacemaker - coordinate communications and resource management. One of the servers has the active connection to the storage resources and the SQL Server. When Pacemaker detects a failure the clustering components manage moving the resources to the other node.

Note:At this point, SQL Server's integration with Pacemaker is not as coupled as with WSFC on Windows. From within SQL, there is no knowledge about the presence of the cluster, all orchestration is outside in and the service is controlled as a standalone instance by Pacemaker. Also for example, cluster dmvs sys.dm_os_cluster_nodes and sys.dm_os_cluster_properties will no records.

I will provide you a high level overview of how to carry out this & divided this in to 5 parts.

We should have 3 machines 2 machines with SQL installed & which we treat them as nodes. The other one would be NFS(Network File System) Server.

My machines for them are: ubusqlclnod1nfs, ubusqlclnod2nfs, ubunfsstorage.

I made use of Oracle Virtual box for performing this.

Here are the steps for part-1:

1) Install SQL Server on the 2 machines (ubusqlclnod1nfs, ubusqlclnod2nfs). I am not going through in detailed fashion as you can find the same in the below link.

2)For the purpose of configuration I designated one node as primary(ubusqlclnod1nfs) and the other as secondary(ubusqlclnod2nfs). 

stop and disable the SQL Services on secondary server(ubusqlclnod2nfs) & primary server(ubusqlclnod1nfs)

sudo systemctl stop mssql-server
sudo systemctl disable mssql-server

3) Backup Synchronous Server Master Key (Since SQL Server is run by local user mssql in Linux, different nodes can not authenticate other nodes, so you need to back up the synchronization key from Primary to other nodes so that it can succeed Decryption Server Master Key):

Backup the original machine-key on the Secondary server(ubusqlclnod2nfs)

sudo su
cd /var/opt/mssql/secrets
mv machine-key machine-key.original.bak

4) copy the machine-key from the primary to secondary. Below are the commands to perform the same. However I faced issues when making use of SCP hence used WINSCP. We need to make use of chmod even when we make use of Winscp otherwise you will get Permission denied error. 

FYI...we don't get SSH by default we need to install it on the machine using 
sudo apt install openssh-server

To find out the ipaddress of your machine make use of ifconfig command & check for inet addr field for Ethernet

sudo su
cd /var/opt/mssql/secrets/
scp machine-key root@**<Secondary Node IP Address>**:/var/opt/mssql/secrets/  

Example: scp machine-key root@  

5) check whether the file has copied on to secondary folder. you should see 2 files over there & ensure you make the owner as mssql by using below command.

chown mssql:mssql machine-key


6) create a sql login user for pacemaker process on primary side to run sp_server_diagnostics. We can treat this as NT Authority\System which runs on Windows machine. There is no need to grant sysadmin permissions however to avoid any surprises I had given sysadmin. It is just enough grant to view server state permissions.

start the services on primary: systemctl start mssql-server.
connect to primary & run the below commands: 

create login pacemaker with password='xxxxxxxx'
alter server role sysadmin add member pacemaker

7) Again stop & disable the SQL services on primary node

    sudo systemctl stop mssql-server
    sudo systemctl disable mssql-server

8) configure each node hosts to ensure mutual recognition. By editing /etc/hosts accordingly.

Part-2 will provide you the details about sharing the NFS.


bikram said…
Nice one to Vamshi..