Configure failover cluster instance - iSCSI - SQL Server on Linux

Άρθρο
09/23/2024

Applies to: SQL Server - Linux

This article explains how to configure iSCSI storage for a failover cluster instance (FCI) on Linux.

Configure iSCSI

iSCSI uses networking to present disks from a server known as a target to servers. The servers connecting to the iSCSI target require that an iSCSI initiator is configured. The disks on the target are given explicit permissions so that only the initiators that should be able to access them can do so. The target itself should be highly available and reliable.

Important iSCSI target information

While this section doesn't cover how to configure an iSCSI target since it's specific to the type of source you use, ensure that the security for the disks that will be used by the cluster nodes is configured.

The target should never be configured on any of the FCI nodes if using a Linux-based iSCSI target. For performance and availability, iSCSI networks should be separate from networks used by regular network traffic on both the source and the client servers. Networks used for iSCSI should be fast. Remember that network does consume some processor bandwidth, so plan accordingly if using a regular server.

The most important thing to ensure is completed on the target is that the disks that are created are assigned the proper permissions so that only those servers participating in the FCI have access to them. An example is shown here from the Microsoft iSCSI target where linuxnodes1 is the name created, and in this case, the IP addresses of the nodes are assigned so that NewFCIDisk1.vhdx appears to them.

Instructions

This section covers how to configure an iSCSI initiator on the servers that serve as nodes for the FCI. The instructions should work as is on Red Hat Enterprise Linux (RHEL) and Ubuntu.

For more information on iSCSI initiator for the supported distributions, see the following links:

Choose one of the servers that will participate in the FCI configuration. It doesn't matter which one. iSCSI should be on a dedicated network, so configure iSCSI to recognize and use that network. Run sudo iscsiadm -m iface -I <iSCSIIfaceName> -o new where <iSCSIIfaceName> is the unique or friendly name for the network. The following example uses iSCSINIC:
```
sudo iscsiadm -m iface -I iSCSINIC -o new
```
Here's the expected output.
```
New interface iSCSINIC added
```
Edit /var/lib/iscsi/ifaces/iSCSIIfaceName. Make sure it has the following values completely filled out:
- iface.net_ifacename is the name of the network card as seen in the OS.
- iface.hwaddress is the MAC address of the unique name that will be created for the following interface.
- iface.ipaddress
- iface.subnet_Mask
See the following example:

Find the iSCSI target.

sudo iscsiadm -m discovery -t sendtargets -I <iSCSINetName> -p <TargetIPAddress>:<TargetPort>

<iSCSINetName> is the unique/friendly name for the network, <TargetIPAddress> is the IP address of the iSCSI target, and <TargetPort> is the port of the iSCSI target.

Here's the expected output.

10.181.182.1:3260,1 iqn.1991-05.com.contoso:dcl-linuxnodes1-target
10.201.202.1:3260,1 iqn.1991-05.com.contoso:dc1-linuxnodes1-target
[2002:b4b5:b601::b4b5:b601]:3260,1 iqn.1991-05.com.contoso:dcl-linuxnodes1-target
[2002:8c9:ca01::c8c9:ca01]:3260,1 iqn.1991-05.com.contoso:dcl-linuxnodes1-target

sudo iscsiadm -m node -I <iSCSIIfaceName> -p TargetIPAddress -l

<iSCSIIfaceName> is the unique/friendly name for the network and <TargetIPAddress> is the IP address of the iSCSI target.

Here's the expected output.

Logging in to [iface: iSCSINIC, target: ian.1991-05.com.contoso:dcl-linuxnodesl-tar get, portal: 10.181.182.1,3260] (multiple)
Login to [iface: iSCSINIC, target: ian.1991-05.com.contoso:dcl-linuxnodesl-tar get, portal: 10.181.182.1,3260] successful.

Check to see that there's a connection to the iSCSI target.

sudo iscsiadm -m session

The output looks similar to the following example:

tcp: [1] 10.105.16.7:3260,1 iqn.1991-05.com.contoso:dcl-linuxnodes1-target (non-flash)

Check iSCSI attached disks.

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

Screenshot of the grep command and the response to the command showing the attached SCSI disks.

Create a physical volume on the iSCSI disk.
```
sudo pvcreate /dev/<devicename>
```
<devicename> is the name of the device from the previous step.
Create a volume group on the iSCSI disk. Disks assigned to a single volume group are seen as a pool or collection.
```
sudo vgcreate <VolumeGroupName> /dev/devicename
```
<VolumeGroupName> is the name of the volume group and <devicename> is the name of the device from Step 6.
Create and verify the logical volume for the disk.
```
sudo lvcreate -Lsize -n <LogicalVolumeName> <VolumeGroupName>
```
<size> is the size of the volume to create, and can be specified with G (gigabytes), T (terabytes), etc., <LogicalVolumeName> is the name of the logical volume, and <VolumeGroupName> is the name of the volume group from the previous step.

Here's the expected output.
```
Logical volume "FCIDataLV1" created.
```
The following example creates a 25-GB volume.
Execute sudo lvs to see the LVM that was created.
Format the logical volume with a supported filesystem. For EXT4, use the following example:
```
sudo mkfs.ext4 /dev/<VolumeGroupName>/<LogicalVolumeName>
```
<VolumeGroupName> is the name of the volume group from the previous step. <LogicalVolumeName> is the name of the logical volume from the previous step.
For system databases or anything stored in the default data location, follow these steps. Otherwise, skip to Step 13.
1. Ensure that SQL Server is stopped on the server that you're working on.
```
sudo systemctl stop mssql-server
sudo systemctl status mssql-server
```
2. Switch fully to be the superuser. You don't receive any acknowledgment if successful.
```
sudo -i
```
3. Switch to be the mssql user. You don't receive any acknowledgment if successful.
```
su mssql
```
4. Create a temporary directory to store the SQL Server data and log files. You don't receive any acknowledgment if successful.
```
mkdir <TempDir>
```
  <TempDir> is the name of the folder. The following example creates a folder named /var/opt/mssql/TempDir.
```
mkdir /var/opt/mssql/TempDir
```
5. Copy the SQL Server data and log files to the temporary directory. You don't receive any acknowledgment if successful.
```
cp /var/opt/mssql/data/* <TempDir>
```
  <TempDir> is the name of the folder from the previous step.
6. Verify that the files are in the directory.
```
ls <TempDir>
```
  <TempDir> is the name of the folder from previous steps.
7. Delete the files from the existing SQL Server data directory. You don't receive any acknowledgment if successful.
```
rm - f /var/opt/mssql/data/*
```
8. Verify that the files have been deleted. The following image shows an example of the entire sequence from c through h.
```
ls /var/opt/mssql/data
```
9. Type exit to switch back to the root user.
10. Mount the iSCSI logical volume in the SQL Server data folder. You don't receive any acknowledgment if successful.
```
mount /dev/<VolumeGroupName>/<LogicalVolumeName> /var/opt/mssql/data
```
  <VolumeGroupName> is the name of the volume group and <LogicalVolumeName> is the name of the logical volume that was created. The following example syntax matches the volume group and logical volume from the previous command.
```
mount /dev/FCIDataVG1/FCIDataLV1 /var/opt/mssql/data
```
11. Change the owner of the mount to mssql. You don't receive any acknowledgment if successful.
```
chown mssql /var/opt/mssql/data
```
12. Change ownership of the group of the mount to mssql. You don't receive any acknowledgment if successful.
```
chgrp mssql /var/opt/mssql/data
```
13. Switch to the mssql user. You don't receive any acknowledgment if successful.
```
su mssql
```
14. Copy the files from the temporary directory /var/opt/mssql/data. You don't receive any acknowledgment if successful.
```
cp /var/opt/mssql/TempDir/* /var/opt/mssql/data
```
15. Verify the files are there.
```
ls /var/opt/mssql/data
```
16. Enter exit to not be mssql.
17. Enter exit to not be root.
18. Start SQL Server. If everything was copied correctly and security applied correctly, SQL Server should show as started.
```
sudo systemctl start mssql-server
sudo systemctl status mssql-server
```
19. Stop SQL Server and verify that it has shut down.
```
sudo systemctl stop mssql-server
sudo systemctl status mssql-server
```
For things other than system databases, such as user databases or backups, follow these steps. If only using the default location, skip to Step 14.
1. Switch to be the superuser. You don't receive any acknowledgment if successful.
```
sudo -i
```
2. Create a folder to be used by SQL Server.
```
mkdir <FolderName>
```
  <FolderName> is the name of the folder. The folder's full path needs to be specified if not in the right location. The following example creates a folder named /var/opt/mssql/userdata.
```
mkdir /var/opt/mssql/userdata
```
3. Mount the iSCSI logical volume in the folder that was created in the previous step. You don't receive any acknowledgment if successful.
```
mount /dev/<VolumeGroupName>/<LogicalVolumeName> <FolderName>
```
  <VolumeGroupName> is the name of the volume group, <LogicalVolumeName> is the name of the logical volume that was created, and <FolderName> is the name of the folder. Example syntax is shown here.
```
mount /dev/FCIDataVG2/FCIDataLV2 /var/opt/mssql/userdata
```
4. Change ownership of the folder created to mssql. You don't receive any acknowledgment if successful.
```
chown mssql <FolderName>
```
  <FolderName> is the name of the folder that was created. An example is shown here.
```
chown mssql /var/opt/mssql/userdata
```
5. Change the group of the folder created to mssql. You don't receive any acknowledgment if successful.
```
chown mssql <FolderName>
```
  <FolderName> is the name of the folder that was created. An example is shown here.
```
chown mssql /var/opt/mssql/userdata
```
6. Type exit to no longer be the superuser.
7. To test, create a database in that folder. The following script creates a database, switches context to it, verifies the files exist at the OS level, and then deletes the temporary location. You can use SSMS or sqlcmd to run this script.
```
DROP DATABASE TestDB;
GO

CREATE DATABASE TestDB
    ON (NAME = TestDB_Data, FILENAME = '/var/opt/mssql/userdata/TestDB_Data.mdf')
    LOG ON (NAME = TestDB_Log, FILENAME = '/var/opt/mssql/userdata/TestDB_Log.ldf');
GO

USE TestDB;
GO
```
  Run the following command in the shell to see the new database files.
```
sudo ls /var/opt/mssal/userdata
```
  Here's the expected output.
```
lost+found TestDB_Data.mdf
TestDB_Log.ldf
```
  Delete the database to clean up.
```
DROP DATABASE TestDB;
GO
```
```
sudo ls /var/opt/mssal/userdata
```
  Here's the expected output.
```
lost+found
```
8. Unmount the share
```
sudo umount /dev/<VolumeGroupName>/<LogicalVolumeName> <FolderName>
```
  <VolumeGroupName> is the name of the volume group, <LogicalVolumeName> is the name of the logical volume that was created, and <FolderName> is the name of the folder. Example syntax is shown here.
```
sudo umount /dev/FCIDataVG2/FCIDataLV2 /var/opt/mssql/userdata
```
Configure the server so that only Pacemaker can activate the volume group.
```
sudo lvmconf --enable-halvm --services -startstopservices
```
Generate a list of the volume groups on the server. Anything listed that isn't the iSCSI disk is used by the system, such as for the OS disk.
```
sudo vgs
```
Modify the activation configuration section of the file /etc/lvm/lvm.conf. Configure the following line:
```
volume_list = [ <ListOfVGsNotUsedByPacemaker> ]
```
<ListOfVGsNotUsedByPacemaker> is the list of volume groups from the output of Step 20 that aren't used by the FCI. Put each one in quotes and separate by a comma. An example is shown here.
When Linux starts, it mounts the file system. To ensure that only Pacemaker can mount the iSCSI disk, rebuild the root filesystem image.

Run the following command, which might take a few moments to complete. You get no message back if successful.
```
sudo dracut -H -f /boot/initramfs-$(uname -r).img $(uname -r)
```
Restart the server.
On another server that will participate in the FCI, perform Steps 1 - 6. This presents the iSCSI target to the SQL Server.
Generate a list of the volume groups on the server. It should show the volume group created earlier.
```
sudo vgs
```

Start SQL Server and verify it can be started on this server.

sudo systemctl start mssql-server
sudo systemctl status mssql-server

Stop SQL Server and verify that it has shut down.

sudo systemctl stop mssql-server
sudo systemctl status mssql-server

Repeat Steps 1 - 6 on any other servers that will participate in the FCI.

You're now ready to configure the FCI.

Κοινή χρήση μέσω

Configure failover cluster instance - iSCSI - SQL Server on Linux

Configure iSCSI

Important iSCSI target information

Instructions

Σχόλια

Πρόσθετοι πόροι

Κοινή χρήση μέσω

Configure failover cluster instance - iSCSI - SQL Server on Linux

Configure iSCSI

Important iSCSI target information

Instructions

Related content

Σχόλια

Πρόσθετοι πόροι