Speed up SQL Server 2022 backups with RHEL Logical Volume Manager

One of the most powerful—and often underutilized—storage features in Red Hat Enterprise Linux (RHEL) is the ability to take point-in-time consistent snapshots using the Linux Logical Volume Manager (LVM2). This works only if you create your file systems on LVM2 volumes when configuring your RHEL system.

After installation, these are easy to set up through the RHEL Web Console as well. With LVM2 snapshots, you can create virtual images of a volume at a particular instant without having to stop your applications to do so.

But there’s a catch! To maintain application consistency, you need to be able to tell your applications to make sure data and metadata in memory and on disk are all in a consistent state. Otherwise, attempts by applications to recover data when running on one of these snapshots can fail. 

Available in public preview from Microsoft, SQL Server 2022 adds a number of new features that can help system and database administrators who are running the database on RHEL. In particular, SQL Server 2022 adds Transact-SQL (T-SQL) snapshot backup support through new ALTER DATABASE, BACKUP and RESTORE commands that allow databases to get to a consistent state so that stable storage snapshots can be taken. A metadata backup must then be performed which, when combined with the snapshots, will provide consistency. The database remains online throughout the entire process.

The end result is very similar to the Virtual Shadow Service (VSS) functionality that SQL Server has supported for many years on Microsoft Windows Server, but you can now benefit from this feature on Linux as a RHEL administrator. 

Here’s how it works.

Backup step 1: Freeze the database

The first step is to freeze a database using a new ALTER_DATABASE option SUSPEND_FOR_SNAPSHOT_BACKUP. You’ll do this using the following T-SQL statement:

ALTER DATABASE ExampleDB SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON

In this example, our database is named ExampleDB. You can run T-SQL commands using Azure Data Studio, sqlcmd or mssql-cli. You can even have the RHEL System Role for Microsoft SQL Server run the command for you as part of automation using the mssql_input_sql_file parameter.

Backup step 2: Take an LVM snapshot  

Once you have suspended the database, the next step is to take a snapshot of the file system. For this example, let’s assume that you have /var/opt/mssql/data/exdb mounted on /dev/vg001/exdb. For the example volume exdb, you can do this with the following LVM2 command:

# lvcreate --size 10G --name exdb-ss1 --snapshot /dev/vg001/exdb

In this example, you are allocating 10GiB for your snapshot. Calculating the space you’ll need in the volume group (vg001 in this example) is important for this command to succeed. You must have adequate space to accommodate future changes to the database. LVM2 also allows you to expand the size of your snapshot at a later time, and the system will notify you as your volume gets close to full.

With a traditional LVM2 snapshot volume, any existing blocks removed or modified by your database will have to be copied into the volume as the source volume is updated. You need to plan ahead and allocate enough space for this. In addition, you want to make sure the snapshot volume has enough additional space allocated to accommodate your application-level metadata backup, as it will be stored in the snapshot volume. 

As an alternative, we could use thin provisioned snapshots. When thin snapshots are used, allocation is dynamic. Blocks are shared between pools. When a block of data is modified or deleted, a metadata update is performed instead of a full copy operation, resulting in reduced I/O operations. An added benefit of using LVM2 thin volumes is that you don’t need to specify the –size parameter—you just need to have available space in your shared storage pool, as in this example:

# lvcreate --name exdb-ss1 -kn --snapshot /dev/vg001/exdb-thin

Here the -kn option indicates that the thin volume snapshot should be created without the skip activation flag, which is enabled by default. Activation allows the volume to be mounted as described below.

One caveat with thin volumes is that they should never be overprovisioned for mission-critical workloads. For this use case, you should always be sure to have ample space to accommodate your application requirements both to deliver the best performance and protect the integrity of your data. 

Backup step 3: Backup the metadata

At this point you still need to backup metadata associated with the database. Before you do that, you need to mount your snapshot so that you can store the metadata backup alongside the actual snapshot data. Doing this will allow you to be better organized. Note that because this is a snapshot, it shares the UUID with the original volume, so you account for that in our example:

# mkdir -p /var/opt/mssql/backups/exsb-ss1
# mount -o nouuid mount /dev/vg001/exdb-ss1  /var/opt/mssql/backups/exdb-ss1

Now you can use a T-SQL command with any of the utilities outlined in step 1 to do your metadata-only backup:

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

This process will automatically unfreeze the database.

Restore the database

In the event your original database is lost or corrupted, you can restore the database by copying files from your snapshot volume back to the original.

# cd /var/opt/mssql/backups/exdb-ss1 
# cp --preserve=all -R * /var/opt/mssql/data/exdb 

Next, use the T-SQL RESTORE command with the METADATA_ONLY option. 

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

Note that you’ll probably also want to perform point-in-time recovery using any log backups taken after the snapshot FULL backup.

Conclusions

It’s worth noting that my example assumes that the volume manager is running on a local host, but SQL Server supports NFSv4.2 volumes as well. If you consume an NFS volume that’s running LVM2 on a different host, you can offload processing and memory consumption related to snapshots to that remote RHEL-based NFS server. In this case, you’ll have to take your snapshots remotely on that NFS host. But once you’ve taken the snapshot, you can export it to be mounted on the client system where you’re running SQL Server and back up to it just as in the example above.

An additional alternative is to use one of the many enterprise storage arrays that offer their own snapshotting capabilities In that case you would substitute the native snapshot commands for your favorite storage solution.

This whole process completes in much less time than traditional backups. While snapshots are never a substitute for offsite backups, they can accelerate a number of backup and recovery scenarios. What’s more, once you have your snapshot, you are free to copy its contents to a remote location which may then address your disaster recovery requirements.

Of course, most important of all, if your SQL Server 2022 database is running on RHEL, you now don’t need to take it offline to perform snapshot backups.

For additional details, see the SQL Server 2022 Preview documentation. To learn more about the advantages of running RHEL with Microsoft SQL Server, I encourage you to explore the Red Hat Enterprise Linux SQL Server use case page.