Edit

Configure SMB storage failover cluster instance for SQL Server on Linux

Applies to: SQL Server on Linux

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

Outside Windows, SMB is also called a Common Internet File System (CIFS) share, which Samba implements. On Windows, you access an SMB share with the format \\SERVERNAME\SHARENAME. For Linux-based SQL Server installations, you must mount the SMB share as a folder.

Important source and server information

Follow these tips to use SMB successfully:

  • The SMB share can be on Windows, Linux, or an appliance as long as it uses SMB 3.0 or later. For more information about Samba and SMB 3.0, see SMB 3.0 to check whether your Samba implementation complies with SMB 3.0.
  • The SMB share should be highly available.
  • Set security properly on the SMB share. The following example is from /etc/samba/smb.conf, where SQLData is the name of the share.
[SQLData]
path=/var/smb/SQLData
read only = no
browseable = yes
guest ok = no
writeable = yes
valid users = SQLSambaUser

Instructions

  1. Choose one of the servers to participate in the FCI configuration. It doesn't matter which one.

  2. Get information about the mssql user.

     sudo id mssql

    Note the uid, gid, and groups.

  3. Execute sudo smbclient -L //NameOrIP/ShareName -U User.

    • <NameOrIP> is the DNS name or IP address of the server hosting the SMB share.
    • <ShareName> is the name of the SMB share.

  4. For system databases, or anything stored in the default data location, follow these steps. Otherwise skip to step 5.

    1. Ensure that SQL Server is stopped on the server you're working on.

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

    2. Switch fully to the superuser.

      sudo -i

    3. Switch to the mssql user.

      su mssql

    4. Create a temporary directory to store the SQL Server data and log files.

      mkdir <TempDir>
      • <TempDir> is the name of the folder. The following example creates a folder named /var/opt/mssql/tmp.
      mkdir /var/opt/mssql/tmp

    5. Copy the SQL Server data and log files to the temporary directory.

      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 Step d.

    7. Delete the files from the existing SQL Server data directory.

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

    8. Verify that the files no longer exist.

      ls /var/opt/mssql/data

    9. Enter exit to switch back to the root user.

    10. Mount the SMB share in the SQL Server data folder. This example shows the syntax for connecting to a Windows Server-based SMB 3.0 share.

      mount -t cifs //<ServerName>/<ShareName> /var/opt/mssql/data -o vers=3.0,username=<UserName>,password=<Password>,domain=<domain>,uid=<mssqlUID>,gid=<mssqlGID>,file_mode=0777,dir_mode=0777
      • <ServerName> is the name of the server with the SMB share
      • <ShareName> is the name of the share
      • <UserName> is the name of the user to access the share
      • <Password> is the password for the user
      • <domain> is the name of Active Directory
      • <mssqlUID> is the UID of the mssql user
      • <mssqlGID> is the GID of the mssql user

    11. Verify the mount by running mount with no switches.

      mount

    12. Switch to the mssql user.

      su mssql

    13. Copy the files from the temporary directory /var/opt/mssql/data.

      cp /var/opt/mssql/tmp/* /var/opt/mssql/data

    14. Verify the files are there.

      ls /var/opt/mssql/data

    15. Enter exit to leave the mssql user.

    16. Enter exit to leave the root user.

    17. Start SQL Server. If you copied everything and applied security correctly, SQL Server starts.

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

    18. To test further, create a database to ensure the permissions are fine. The following example uses Transact-SQL; you can use SQL Server Management Studio (SSMS).

      Screenshot showing the creation of the test database.

    19. Stop SQL Server and verify it's shut down. If you plan to add or test other disks, don't shut down SQL Server until you add and test them.

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

    20. If you're finished, unmount the share. Otherwise, unmount after you finish testing or adding disks.

      sudo umount //<IPAddressorServerName>/<ShareName>/<FolderMountedIn>
      • <IPAddressOrServerName> is the IP address or name of the SMB host
      • <ShareName> is the name of the share
      • <FolderMountedIn> is the name of the folder where SMB is mounted

  5. For things other than system databases, such as user databases or backups, follow these steps. If you use only the default location, skip to Step 14.

    1. Switch to the superuser.

      sudo -i

    2. Create a folder for SQL Server to use.

      mkdir <FolderName>

      <FolderName> is the name of the folder. Specify the folder's full path if it isn't in the right location. The following example creates a folder named /var/opt/mssql/userdata.

      mkdir /var/opt/mssql/userdata

    3. Mount the SMB share in the SQL Server data folder. This example shows the syntax for connecting to a Samba-based SMB 3.0 share.

      mount -t cifs //<ServerName>/<ShareName> <FolderName> -o vers=3.0,username=<UserName>,password=<Password>,uid=<mssqlUID>,gid=<mssqlGID>,file_mode=0777,dir_mode=0777
      • <ServerName> is the name of the server with the SMB share
      • <ShareName> is the name of the share
      • <FolderName> is the name of the folder created in the last step
      • <UserName> is the name of the user to access the share
      • <Password> is the password for the user
      • <mssqlUID> is the UID of the mssql user
      • <mssqlGID> is the GID of the mssql user.

    4. Verify the mount by running mount with no switches.

    5. Enter exit to leave the superuser.

    6. To test, create a database in that folder. The following example uses sqlcmd to create a database, switch context to it, verify the files exist at the OS level, and then delete the temporary location. You can use SSMS.

    7. Unmount the share.

      sudo umount //<IPAddressorServerName>/<ShareName> /<FolderMountedIn>
      • <IPAddressOrServerName> is the IP address or name of the SMB host
      • <ShareName> is the name of the share
      • <FolderMountedIn> is the name of the folder where SMB is mounted.

  6. Repeat the steps on the other nodes.

You're now ready to configure the FCI.

Related content

  • Last updated on