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!

[elyes]

Hello everyone,

I'm seeking clarification regarding the use of Veeam Backup & Replication 12 for backing up dump directories on virtual machines. Here's my context:

• Context:
  
  We are using the latest version of Veeam Backup & Replication 12.
  • On our virtual machines, database dumps are stored in specific directories (/var/backup/).
  • We want to back up these dump directories without installing the Veeam agent on each virtual machine.

• Questions:
  
  • Is it necessary to install a Veeam agent on each virtual machine to perform backups of dump directories?
  • Has anyone successfully configured this type of backup using Veeam Backup & Replication 12 without deploying an agent on each virtual machine? If so, could you please share your experience or any advice?

Thank you in advance for your responses and assistance!

[PetrM]

Hi Elyes and Welcome to Veeam R&D Forums!

If you are using vSphere or Hyper-V for your virtual workloads, you can easily create a backup job and add the relevant VMs to it. The guest components will be automatically installed on VMs during the execution of the job. However, have you considered utilizing our plug-in for Microsoft SQL Server? This plug-in allows you to directly stream SQL native backups to the Veeam infrastructure.

Thanks!

[elyes]

Hi PetrM & Thank you for the response!

To clarify, our database engine is MySQL, not Microsoft SQL Server. We are specifically looking to back up directories containing MySQL database dumps stored in /var/backup/ on our virtual machines.

Given this, do we still need to install the Veeam agent on each VM to back up these directories, or is there another recommended method for this scenario?

Thank you again for your assistance!

[PetrM]

Hi Elyes,

It seems you didn't see my reply in this topic yesterday. Let's continue the discussion about MySQL there.

Thanks!