Stratis-managed file systems for Microsoft SQL Server on Red Hat Enterprise Linux

Last October, I wrote a blog discussing how to speed up Microsoft SQL Server 2022 backups with the RHEL Logical Volume Manager when running SQL Server on Linux. In it, I noted that one of the most powerful—and often underutilized—storage features in Red Hat Enterprise Linux (RHEL) is taking point-in-time consistent snapshots using the Linux Logical Volume Manager (LVM2).

Today, Stratis is an even easier way to take advantage of application snapshots on RHEL. In a nutshell, Stratis is a local storage-management solution for Linux. It focuses on simplicity and ease of use, giving you access to advanced storage features.

Stratis simplifies the initial configuration and Day 2 modification of storage while streamlining the configuration of advanced features, such as snapshots. With Stratis moving from being a technology preview to a fully supported capability of RHEL 9.3, I decided to revisit that blog and show you how to use Stratis to speed up backups. It uses XFS as its file system and is thus fully supported by commercial enterprise applications like Microsoft SQL Server.

In the earlier article, I skipped the manual configuration of LVM, which can be quite complex. Instead, I recommended using the RHEL web console to configure file systems on LVM2 volumes. You can also do this with Stratis because it is fully supported by the web console. Stratis also simplifies configuration from the command line. This example takes you through that process to show how easy it is.

Note that you shouldn't install SQL Server until after completing this procedure. The steps in this example assume you have a fresh installation of RHEL.

Configure a Stratis file system

I'll begin by creating a pool of storage for Stratis to access. Since this storage will be used for SQL Server, and SQL Server typically manages its own encryption at rest and in flight, I won't encrypt the volume at the Stratis level, though that is certainly possible. Similarly, if I had a small amount of fast storage, I could use it as a cache with Stratis. I'll skip that step because the storage is homogenous in this case.

Use the following command to create the initial storage pool on a storage device:

# stratis pool create pool0 /dev/sdb

Create a Stratis pool on the volume. Note that over-provisioning file systems should generally be avoided with databases as it can lead to potential data loss if a file system is overfilled.

Display the pool with the following command:

# stratis pool list
Name                Total / Used / Free    Properties                                   UUID   Alerts
pool0   20 GiB / 532.50 MiB / 19.48 GiB   ~Ca,~Cr, Op   22e208c8-2bb2-4a73-bf72-d854e1d09fac        

I created a storage pool called pool0 with 20 GiBs of capacity, 532.50 MiB of which is in use. This leaves 19.48 GiB of free storage.

The Stratis documentation states that you should allocate a half GiB (512 MiB) for each snapshot you wish to take. You'll have room for 12 snapshots if you allocate 13 GiB for a data volume. Set up the file system using this command:

# stratis filesystem create --size 13GiB pool0 mssqlfs

Next, set up the file system in /etc/fstab. To do this, use the lsblk utility to determine the UUID of the file system:

# lsblk --output=UUID /dev/stratis/pool0/mssqlfs
UUID
a973784f-5379-49e9-9634-0048498fff17

Create a file system entry in the fstab file. The system will use this entry each time it is started. Note that you mount the file system with the noatime flag, something Microsoft recommends as a performance best practice:

# echo “UUID=a973784f-5379-49e9-9634-0048498fff17 /var/opt/mssql xfs defaults,noatime,x-systemd.requires=stratisd.service 0 0” >> /etc/fstab

Create the directory the file system will be mounted on using the mkdir command as follows:

# mkdir /var/opt/mssql

Note that in this example, I put /var/opt/mssql on a separate drive. It may be an even better idea to mount separate drives for use with data, logs and tmp files. See the Microsoft SQL Server on Linux Best Practices for Performance guide for more information.

Don't worry about ownership or permissions for the directory. Both will get set properly when you install Microsoft SQL Server.

Complete the file system configuration process by mounting the file system with the following:

# mount /var/opt/mssql

Configure SQL Server

The next step is to install and configure SQL Server. Red Hat recommends using the Ansible Collection for Microsoft SQL Server management, which is included with RHEL. Check out this post, where Red Hatter Brian Smith walks you through configuring a simple SQL Server instance.

Once you run the role, you'll have a fully functional SQL Server. You can then populate it with a database. In my case, I will name the database ExampleDB.

Back up the database

Follow the steps outlined in my previous blog to back up a database on this server. The process is greatly simplified.

Step 1: Freeze the database

As before, use the new T-SQL commands added in SQL Server 2022 to freeze the database, as seen in this example:

ALTER DATABASE ExampleDB SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON

The database I created after I installed SQL Server is named ExampleDB, and I can run T-SQL commands through my favorite tools, such as Azure Data Studio, sqlcmd, or mssql-cli.

Step 2: Take a Stratis file system snapshot 

This step is a little different from what I did last time. Taking a snapshot with Stratis is much easier than with LVM2. Simply run this command:

# stratis fs snapshot pool0 mssqlfs mssqlfs-snapshot1

The command takes a snapshot of the mssqlfs volume and stores it in a snapshot volume named mssqlfs-snapshot1.

Step 3: Back up the metadata

In this step, back up the metadata associated with the backup job. Do this with a T-SQL backup. You can either mount the newly created snapshot as a writable volume and place the backup file there or save the backup to a separate location. In this example, I'll use a separate location, storing it in the /var/opt/mssql/backups/ directory.

BACKUP DATABASE ExampleDB TO DISK = ‘/var/opt/mssql/backups/mssql-ss1.bkm' WITH METADATA_ONLY,COPY_ONLY,NOFORMAT,MEDIANAME='mssqlfs-snapshot1'

Performing this last step will automatically unfreeze the database. The whole process takes seconds to complete.

Script the backup with Python

Of course, you can script all the steps above in .NET, but we're on Linux, and one of the most common Linux approaches to performing all of these steps is using a Python script. You'll need to provide the following parameters to the script, which I've named snapmssql:

• server       - the name of the Linux server running SQL Server
• user         - the database user
• password     - the database user password
• database     - the name of the database to back up
• pool         - the stratis pool containing the database
• filesystem   - the stratis file system containing the database
• Snapshot     - the name of the file system snapshot to create

Next, copy the contents below into a file named snapmssql on your system:

snapmssql python script

#!/usr/bin/python
#
# Usage:
# snapmssql server username password database pool filesystem snap_shot_name
#
import sys
import subprocess
import pyodbc

# Make sure the right number of parameters is passed to the script
argument_count = len(sys.argv)
if  argument_count != 8:
   sys.stderr.write("usage: snapmssql server username password database pool filesystem snapshot")
   sys.exit(1)
else:
   server     = sys.argv[1]
   username   = sys.argv[2]
   password   = sys.argv[3]
   database   = sys.argv[4]
   pool       = sys.argv[5]
   filesystem = sys.argv[6]
   snapshot   = sys.argv[7]

# Connect to the SQL Server
   connectionString = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={server};DATABASE=master;UID={username};PWD={password};TrustServerCertificate=Yes;'
   conn = pyodbc.connect(connectionString)

# BACKUP STEP 1: Freeze the database
   SQL_QUERY = f"""
   ALTER DATABASE {database} SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
   """
   conn.autocommit = True
   conn.execute(SQL_QUERY)

# BACKUP STEP 2: Take a Stratis file system snapshot  
   subprocess.call(['stratis','fs', 'snapshot', pool, filesystem, snapshot])

# BACKUP STEP 3: Backup the metadata
   SQL_QUERY = f"""
   BACKUP DATABASE {database} TO DISK = '/var/opt/mssql/data/{snapshot}.bkm' WITH METADATA_ONLY,COPY_ONLY,NOFORMAT,MEDIANAME='{snapshot}'
   """
   conn.execute(SQL_QUERY)

# Clean up and exit
   conn.close()
   sys.exit(0)

Make the file executable using the command:

$ chmod 755 snapmssql

And run it as the administrator with the sudo command, as in the example below:

$ sudo ./snapmssql localhost sa MyP@$$Word23 ExampleDB pool0 mssqlfs mssqlfs-snapshot1

Restore the database

There is talk of how a backup process can be a "Roach Motel," where backups check in, but they can't check out. Always test your restore process regularly when performing backups! In this case, restoring the database involves two steps: mounting and accessing the snapshot and restoring the metadata. Follow these steps to restore the previously backed-up version of the ExampleDB database.

First, stop the database with this command:

# systemctl stop mssql-server.service

Next, mount the snapshot so that it is accessible. Do this with a regular mount command:

# mount /dev/stratis/pool0l/mssqlfs-snapshot1 /mnt

Copy the contents of the snapshot back to the SQL Server directory and unmount the snapshot to save resources as follows:

# cd /mnt/data
# cp --preserve=all -R  *  /var/opt/mssql/data/
# umount /mnt/data

Restore the metadata using this command:

RESTORE DATABASE ExampleDB FROM DISK = '/var/opt/mssql/backups/mssql-ss1.bkm' WITH METADATA_ONLY

Finally, restart the database with the command:

# systemctl start mssql-server.service

Wrap up

As with the prior web console example, the process completes in less time than a traditional backup. In a traditional backup, you actually have to copy data from the system.

With Stratis snapshots, you're just changing some pointers. That results in a big difference in performance, which is why data professionals tend to prefer file system snapshots for point-in-time backups, especially for mission critical data.

Of course, the danger is not moving the snapshots offsite, so having an offsite backup strategy is essential. Once you have a snapshot, you can copy its contents to one or more remote locations to address disaster recovery requirements. One classic strategy is to have a daily offsite backup for DR and an hourly snapshot for local rollback/recovery, but methods differ based on business continuity requirements.

To summarize, if your SQL Server 2022 database runs on RHEL on a Stratis file system, you no longer need to take it offline to perform snapshot backups.

Learn more

For additional details, check out the Stratis documentation for Red Hat Enterprise Linux to get started then move on to the SQL Server 2022  documentation to review how to create a Transact-SQL snapshot backup.

To learn more about the advantages of running RHEL with Microsoft SQL Server, explore the Red Hat Enterprise Linux SQL Server use case page.