Backing Up MS-SQL on RedHat 9

[MILJW002]

Hi All,

I've recently started exploring MS-SQL on RedHat 9, and as part of that looking at the backup and recovery strategy for this. I'm familiar with the native support for MS-SQL on Windows Server for backups (works well), and from some initial reading understand Veeam doesn't support MS-SQL on Linux.

I am using a pre and post script for MySQL where it flushes the tables and locks the DB, then releases it with the post script and seems to be working well.

After some Googling, I think it should be possible to do similar with MS-SQL on Linux. I found a RedHat 9 article that is talking about their snapshot technology (https://www.redhat.com/en/blog/speed-sq ... me-manager), and using it for making consistent MS-SQL backups/snapshots.

The commands appear to be similar to the MySQL methodology, but a little different.

It suggests to use T-SQL to:
1) prepare the DBs for a SNAPSHOT BACKUP
2) Backup the metadata for the DBs (which then releases the locks).

So, rough plan is:

PRE SCRIPT
1) Backup step - Freeze the database
ALTER DATABASE ExampleDB SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON

POST SCRIPT
1) Backup step Backup the metadata - this then releases the DBs
BACKUP DATABASE ExampleDB TO DISK = ‘/var/opt/mssql/backups/mssql-ss1.bkm' WITH METADATA_ONLY,COPY_ONLY,NOFORMAT,MEDIANAME='LVM vg001/exdb-ss1'

RESTORE
Then when it comes time for recovery there is a command to restore the DB to a consistent state.
RESTORE DATABASE ExampleDB FROM DISK = '/var/opt/mssql/data/exdb/exdb-ss1.bkm' WITH METADATA_ONLY


I'm going to explore this in the home lab, but sharing incase anyone else has already done this, and if anyone has any tips and/or tricks to share.

James

[MILJW002]

Actually, just looking a bit more, the SQL commands would likely be

1) Prepare for Backup (before snapshot)
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;


2) After snapshot
BACKUP SERVER
TO DISK = '/var/opt/mssql/backup/snapshot-db.bkm'
WITH METADATA_ONLY, FORMAT;

[PetrM]

Hi James,

In fact, I have nothing to add to the plan above. I never tested it on Red Hat 9 but your idea seems reasonable, just test how backup and restore work. By the way, if we imagine that our plug-in for Microsoft SQL Server is compatible with Linux-based systems, would you use it?

Thanks!

[MILJW002]

Hi PetrM,

Thanks for the feedback. I’m still testing it, but seems to be working. I based the overall pre/thaw scripts on what I’m using for MySQL, and seems to be working. I do want to test the restores more to be confident.

I do need to look at the Veeam SQL plugin in more depth. I’m familiar with the Veeam Application Aware processing, so initially was thinking that way. I like having the DB in the Veeam backup chains. I need to read more about the SQL plugin.

I’ve run Windows MS-SQL DBs since the early 2000’s, but only recently started looking at MS-SQL on Linux (RHEL9). The OS footprint is so much smaller, and the performance does seem to be a lot better than on Windows.

[PetrM]

Hi James,

Just keep in mind that the SQL plug-in supports only Windows-based SQL instances. If you run some SQL workloads on Windows, the plug-in is a powerful tool to protect them without a doubt. Also, I'd appreciate to get your feedback as well.

Thanks!