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

Hi Folks,

We are back & all set to go with part 3 which is the crucial part of this 5-part series.

After setting up the NFS server now we do perform our actions on our nodes (ubusqlclnod1nfs & ubusqlclnod2nfs)

1) Install the NFS package on both the servers (ubusqlclnod1nfs & ubusqlclnod2nfs)

   sudo apt-get update

   sudo apt-get install nfs-common

2) Disable the firewall on both the servers (Not recommended on Production servers)

    sudo ufw disable

3) confirm that you can see the NFS share by running the below command on both the servers

   showmount -e <ipaddress of your nfs server>

   

 4) confirm that your SQL Server services are stopped on your primary server.

     systemctl status mssql-server

 5) Mount the database files to point to shared storage

      On the primary node only, save the database files to a temporary location by running the below commands.

   

      Su mssql  (switch the root to mssql)

      mkdir /var/opt/mssql/temp (create a temporary directory to hold the system DB files)

      cp /var/opt/mssql/data/*  /var/opt/mssql/temp  (copy the SQL files)

      ls /var/opt/mssql/temp             (check if the files have been copied)

      rm -rf /var/opt/mssql/data/*     (delete the SQL files)

      exit (switch back to root)

6) Edit /etc/fstab to include the mount command (this should be done on all the nodes)   

Note from Microsoft: If using a File System (FS) resource as recommended below, there is no need to preserve the mounting command in /etc/fstab. Pacemaker will take care of mounting the folder when it starts the FS clustered resource. With the help of fencing, it will ensurethe FS is never mounted twice.

7) Run mount -a command for the system to update the mounted paths.

8)check if it has been succeed using mount command
   mount

9) Copy the database and log files that you saved to /var/opt/mssql/temp to the newly mounted share /var/opt/mssql/data. This only needs to be done on the primary node. Make sure that you give read write permissions to 'mssql' local user.

chown mssql /var/opt/mssql/data

chgrp mssql /var/opt/mssql/data

su mssql

cp /var/opt/mssql/temp /*  /var/opt/mssql/data

rm /var/opt/mssql/temp/*

exit

10) After the above steps try to start SQL Services on primary

    

      sudo systemctl start mssql-server

      sudo systemctl status mssql-server

11) check with SQLCMD whether the connection is successful or not.

Note: when I tried to perform Initially on RTM version of 2017 I got the below error when performing the mount operation & Microsoft has confirmed that it was a bug. 
https://social.msdn.microsoft.com/Forums/en-US/fbc812c0-5a07-4127-8dc7-83ab99f2a153/configure-red-hat-enterprise-linux-shared-disk-cluster-for-sql-server-fcbopen-failed-could-not?forum=sqldatabaseengine

Do ensure you update it to latest CU at the time of writing this I made use of CU3.



12) stop the services on primary.

      sudo systemctl stop mssql-server

13) Repeat the below steps on the secondary server

  sudo systemctl stop mssql-server

 sudo systemctl status mssql-server

 su mssql

 rm /var/opt/mssql/data/* -f

 exit

sudo systemctl start mssql-server

sudo systemctl status mssql-server
     
Note: we are not mounting anything here as we already edited /etc/fstab on secondary as part of step 6.

15) just see if you can connect to the secondary instance using SQLCMD & stop the services on secondary.

sudo systemctl stop mssql-server

sudo systemctl status mssql-server

In the next post we will be going through pacemaker & corosync installation