Tutorial: Create SQL Cluster(FCI) on RHEL

This post has been republished via RSS; it originally appeared at: SQL Server Support articles.

In the Windows world, SQL Server integrates into Windows Server Failover Cluster (WSFC) natively, and we have a dedicated installation for SQL Server Cluster. However, on Linux, you need to install standalone SQL Server instance in the nodes first, and then configure the instance as a SQL Server cluster instance.

 

 

I use SQL Server 2019 with RHEL 8.x in this tutorial, but it is possible to use SQL Server 2017 in RHEL 7.x or RHEL 8 to configure FCI.

 

Here is the step by step Video

 

Topology

Liwei_0-1597102650439.png

 

 

 

 

 

 

1.ISCSI target server configuration

 

  1. The two highlighted disks will be the used as Shared Storage.

 

Liwei_1-1597102650482.png

 

 

sdc is for the database files in /var/opt/mssql/data

sdd is for the user databases files. If all your databases are stored /var/opt/mssql/data, feel free to ignore all the steps link to device sdd.

 

2.Run fdisk to create partition.

fdisk /dev/sdc

fdisk /dev/sdd

Liwei_2-1597102650493.png

 

 

Run lsblk again

 

Liwei_3-1597102650483.png

 

 

3.Install targetcli package.

yum -y install targetcli

 

 

4.Run targetcli to create ISCSI target server.

I created two ACLs entries for the node1 and node2:

iqn.2020-08.com.contoso:node1

iqn.2020-08.com.contoso:node2

I’m not going to dig into the targetcli command, please review this article for detail.

Liwei_4-1597102650494.png

 

 

And I have following iscsi settings:

Liwei_5-1597102650497.png

 

5.Run following bash command to expose tcp port 3260

firewall-cmd --add-port=3260/tcp --permanent

firewall-cmd --reload

          

Liwei_6-1597102650445.png

 

 

  1. Enable and restart the target service.

systemctl enable target.service

systemctl restart target.service

 

2.ISCSI initiator configuration.

 

Choose one of the servers that will participate in the FCI configuration. It does not matter which one. I use node1 in this tutorial. Please note, All the steps in this section are performed in node1, unless stated otherwise.

 

1.Install iscsi-initiator-utils  in all nodes.

sudo yum install iscsi-initiator-utils -y

 

2.Edit the /etc/iscsi/initiatorname.iscsi , replace the existing value with following keywords, the one I used in step 4 of section[ISCSI target server configuration]

InitiatorName=iqn.2020-08.com.contoso:node1

Liwei_7-1597102650445.png

 

         

 

3.Discover iSCSI disk of the target.

iscsiadm -m discovery -t st -p <ip of iscsi target server>

Here is the command in this tutorial.

iscsiadm -m discovery -t st -p 10.1.0.8

         

Liwei_8-1597102650446.png

 

 

 

 

  1. (Optional step)After the discovery below database is updated.

ls -l /var/lib/iscsi/nodes

Liwei_9-1597102650447.png

 

 

6.Make the connection to iscsi target.

iscsiadm --mode node --targetname iqn.2020-08.com.contoso:servers  --login

Liwei_10-1597102650447.png

 

 

  1. (Optional step)After logging in, a session with the iSCSI target is established.

iscsiadm --mode node -P 1

Liwei_11-1597102650448.png

 

 

  1. (Optional step)If you review the messages file, you will see following keywords

sudo grep "Attached SCSI" /var/log/messages

Aug  6 01:38:21 localhost kernel: sd 3:0:1:0: [sdb] Attached SCSI disk

Aug  6 01:38:21 localhost kernel: sd 2:0:0:0: [sda] Attached SCSI disk

Aug  6 04:26:01 localhost kernel: sd 6:0:0:0: [sdc] Attached SCSI disk

Aug  6 04:26:01 localhost kernel: sd 6:0:0:1: [sdd] Attached SCSI disk

 

 

9.Create physical volumes on the iSCSI disks.

sudo pvcreate    /dev/sdc

sudo pvcreate   /dev/sdd

Liwei_12-1597102650448.png

 

10.Create volume groups  ‘FCIDataVG1’ and ‘FCIDataVG2’ on the iSCSI disk.

sudo vgcreate FCIDataVG1     /dev/sdc

sudo vgcreate FCIDataVG2     /dev/sdd

Liwei_13-1597102650449.png

 

 

  1.  Create logical name for the two groups. Run following commands in node1.

sudo lvcreate -L599G   -n FCIDataLV1 FCIDataVG1

sudo lvcreate -L499G   -n FCIDataLV2 FCIDataVG2 

Liwei_14-1597102650449.png

 

 

Check the device

Liwei_15-1597102650485.png

 

 

 

 

  1.  Format the logical volume with a supported filesystem.

sudo mkfs.xfs /dev/FCIDataVG1/FCIDataLV1

sudo mkfs.xfs /dev/FCIDataVG2/FCIDataLV2

 

Liwei_16-1597102650486.png

 

13.Repeat the step1~step6 in rest of the nodes.

Please note,

1)Do not do step6 in rest of node2 before step12 is completed in node1. Else you maybe not able to failover.

2)All the steps but step 2 are exactly same. Here is the value for node2

Liwei_17-1597102650451.png

 

 

3)After the step6 is executed in rest of the nodes, you will see the same devices as node1.

Here is a screenshot of node2 after step 6 is executed.

Liwei_18-1597102650487.png

 

 

 

3.SQL Server configuration.

Please note, All the steps in section are performed in node1, unless stated otherwise.

 

 

1.Run following queries to create login used by pacemaker

       CREATE LOGIN [sqlpackmaker] with PASSWORD= N'YourStrongP@ssword1'

ALTER SERVER ROLE [sysadmin] ADD MEMBER [sqlpackmaker]

 

2.Drop the default server name and create a new server name. The new server name is SQL Virutal name.

exec sp_dropserver node1

go

exec sp_addserver 'sqlvirtualname1','local'

 

  1. Restart SQL Server to take effect.

sudo systemctl stop mssql-server

sudo systemctl restart  mssql-server

Liwei_19-1597102650452.png

 

 

4.Run following queries to check change.

select @@servername, SERVERPROPERTY('ComputernamephysicalNetBIOS')

Liwei_20-1597102650452.png

 

 

5.Stop SQL Server in all nodes(node1,node2 ).

sudo systemctl stop mssql-server

 

6.Copy the /var/opt/mssql/secrets/machine-key of node1 to node2.

 

 

7.Create temporary directories to store the SQL Server data and log files.

mkdir /var/opt/mssql/tempdir

mkdir /var/opt/mssql/tempuserdata

Liwei_21-1597102650453.png

 

 

8.Copy the SQL Server data and log files to the temporary directories.

cp /var/opt/mssql/data/*               /var/opt/mssql/tempdir/

cp /var/opt/mssql/userdata/*      /var/opt/mssql/tempuserdata/

 

 

Liwei_22-1597102650454.png

 

 

9.Delete the files from the existing SQL Server data directory in node1.

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

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

 

Liwei_23-1597102650454.png

 

10.Mount the iSCSI logical volume in the SQL Server data folder.

mount /dev/<VolumeGroupName>/<LogicalVolumeName>   <FolderName>

 

Here are the commands in this tutorial.

mount /dev/FCIDataVG1/FCIDataLV1 /var/opt/mssql/data

mount /dev/FCIDataVG2/FCIDataLV2 /var/opt/mssql/userdata

Liwei_24-1597102650489.png

 

 

 

11.Change the owner of the mount to mssql.

chown mssql:mssql /var/opt/mssql/data

chown mssql:mssql /var/opt/mssql/userdata

 

Liwei_25-1597102650455.png

 

12.Change ownership of the group of the mount to mssql.

           chgrp mssql /var/opt/mssql/data

           chgrp mssql /var/opt/mssql/userdata

Liwei_26-1597102650456.png

 

13.Copy the files from temp folders back to /var/opt/mssql/data and /var/opt/mssql/userdata.

cp /var/opt/mssql/tempdir/* /var/opt/mssql/data

cp /var/opt/mssql/tempuserdata/* /var/opt/mssql/userdata

Liwei_27-1597102650457.png

 

 

14.Check the two temp folders and make sure the files are copied.

Liwei_28-1597102650458.png

 

 

  1. Change ownership of files to mssql.

chown mssql:mssql /var/opt/mssql/data/*

chown mssql:mssql /var/opt/mssql/userdata/*

Liwei_29-1597102650459.png

 

 

16.Configure auto mount to make the OS mount the devices automatically.

1) makeGet the UUID. Please downdown the UUID ,TYPE and directory

blkid /dev/FCIDataVG1/FCIDataLV1

blkid /dev/FCIDataVG2/FCIDataLV2 

Liwei_30-1597102650460.png

 

 

2).Edit /etc/fstab to configure auto mount in node1,node2.

Here is a screenshot in node1.

Liwei_31-1597102650496.png

 

Please review this article for more detail

 

 

4.Cluster configuration.

 

1.Edit /etc/hosts to speicfy the node and ips in node1 and node1. Do the same thing in node1 and node2

Liwei_32-1597102650461.png

 

2.Create a file to store the SQL Server username and password for the Pacemaker login. Run the following command in node1 and node2 (The same login name and password specified in step 1 of section [SQL Server configuration]

 

sudo touch /var/opt/mssql/secrets/passwd

sudo echo 'sqlpackmaker' >> /var/opt/mssql/secrets/passwd

sudo echo 'YourStrongP@ssword1' >> /var/opt/mssql/secrets/passwd

sudo chown root:root /var/opt/mssql/secrets/passwd

sudo chmod 600 /var/opt/mssql/secrets/passwd

 

 

3.On both cluster nodes, open the Pacemaker firewall ports. To open these ports with firewalld, run the following command in node 1 and node2

sudo firewall-cmd --permanent --add-service=high-availability

sudo firewall-cmd --reload

 

4.Install Pacemaker packages in node 1 and node2

sudo yum install pacemaker pcs fence-agents-all resource-agents

 

5.Set the password for the default user that is created when installing Pacemaker and Corosync packages. Use the same password in node 1 and node2

sudo passwd hacluster

 

6.To allow nodes to rejoin the cluster after the reboot, enable and start pcsd service and Pacemaker. Run the following command in node 1 and node2

sudo systemctl enable pcsd

sudo systemctl start pcsd

sudo systemctl enable pacemaker

 

7.Create the cluster. Run following command in node1. The password should be as same as the one in step 5.

sudo pcs host auth  node1 node2 

sudo pcs cluster setup  sqlcluster  node1 node2

sudo pcs cluster start --all

sudo pcs cluster enable --all

Liwei_33-1597102650462.png

 

8.Disable the stonith-enabled for test purpose. Run following command in node1.

sudo pcs property set stonith-enabled=false

 

9.Install the FCI resource agent for SQL Server. Run the following commands in node1 and node2

sudo yum install mssql-server-ha

 

10.Create disk resource and this resource belongs to a resource group(RGfci in this demo). Run following command in node1

sudo pcs resource create iSCSIDisk1 Filesystem device="/dev/FCIDataVG1/FCIDataLV1" directory="/var/opt/mssql/data" fstype="xfs" --group fci

sudo pcs resource create iSCSIDisk2 Filesystem device="/dev/FCIDataVG2/FCIDataLV2" directory="/var/opt/mssql/userdata" fstype="xfs" --group fci

 

Liwei_34-1597102650490.png

 

 

Liwei_35-1597102650463.png

 

 

11. Create IP resource that will be used by FCI, and this resource belongs to the same resource group created in previous step.

sudo pcs resource create vip2 ocf:heartbeat:IPaddr2 ip=10.1.0.111 nic=eth0  cidr_netmask=24 --group fci

Liwei_36-1597102650464.png

 

 

12.Create FCI resource. The resource name should be exactly same to the SQL Virtual name created in step 2 in section [SQL Server configuration]

sudo pcs resource create sqlvirtualname1 ocf:mssql:fci   --group fci

Liwei_37-1597102650465.png

 

 

 

 

 

Failover

===

sudo pcs resource move sqlvirtualname1 <NodeName>

 

Liwei_38-1597102743820.png

 

More:Add a node to existing cluster

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.