V12 Veeam Plug-in for MS SQL vs agent based image backup

[mark49808]

Hi, trying out the new plug-in for MS SQL in v12. Seems to work as advertised.

But I'm trying to understand the use case for it vs the Windows agent with application aware processing. Seems the plug-in gives me much more flexibility around which specific databases to back up and retention on each which is great.

However, I'm also trying to understand what benefit it really gets me, is it seems it's just "redirecting" the native SQL backup job to use the SOBR. How is this "better" than just backing up a file share containing the SQL backup files written with native SQL tools?

Wondering if there is a short list of what I'm getting vs missing in the agent (image based) vs plug-in approach. Space savings, retention, restore capability, etc? Basically help me sell this to our DBAs as they are using the built in tools today and are not easy to convince otherwise. Wrapping transaction logs in a third party tool/format may not go over well. Also it seems the image-based approach would be better for space savings? Synthetic fulls, etc? At the expense of flexibility?

Thanks in advance for any pros/cons.

[Regnor]

The main advantage of the plugin is it's integration in VBR. You can centrally monitor the backup status and work with the backups. The DBA on the other side can independently of the backup admin use the plugin, so he is still capable of doing the backups.
I've seen DBA scripts failing without the backup software noticing it. Then a restore was necessary and only outdated BAKs were available.
So I would say the SQL plugin is the best tradeoff between database and backup admins.

Compared to image-based backups you have a much better or granular control over what and how you backup the SQL server. Though I would still create image backups, regardless of the SQL backups, as it offers you the possibility to recover both the databases and the server itself.
At the end it depends on your requirements and if you have dedicated database admins who need access to the backups.

[HannesK]

Hello,
yes, there is a list why one would want to use the plugin

- Save disk space (costs): select databases to back up; Back up mirrored databases (AlwaysON) only once
- SQL installations on CSVs (cluster shared volumes)
- Back up new databases without waiting for image-based backup
- SQL admin wants full control
- Avoid VSS limitations
- Efficient log shipping directly to repository. With VM- or agent-based backup, we compress logs to the drive with most free space or SqlTempLogPath. Only after that compression, we send it to the repository. That means temporary disk space is needed for log files

Best regards,
Hannes

[kaffeine]

Thanks HannesK for summarizing the benefits of the new plug-in.

A question regarding its usability in a scenario where a DB is handled by 3 backup jobs:
# 1x Backup Job for a full backup at night
# 1x Backup Job for a diff backup in the middle of the day
# 1x Backup Job for TLog backups every 5-10 Min in between (during productive hours)

According to the restore wizard manual, one must select a backup job to start a restore procedure. Supposing one would choose the TLog job (which backups the TLogs every few minutes and sends them to VBR Server), would the restore procedure search for the corresponding diff and backup files available on the repository for the DB at hand? Or is the restore procedure restricted to the generated backup data from the chosen backup job on the wizard?

Regards

[PetrM]

Hello,

First of all, thanks to everyone for the feedback!

In the restore manual, we're talking about the backup job on Veeam B&R side which is created automatically (a.k.a "job object") and handles backup sessions of DBs that belong to the same SQL Server deployment, for example, standalone server or Always On group. This job is also linked with the actual backup container (Node "Backups - Disk") on a repository in which full, differential, and tlog backups of every DB are stored. It's not about SQL Server agent job that you create on SQL server side. During restore, you just select a desired point in time and we automatically find the necessary full, diff, and t-log backups on the repository to use as a source of data.

By the way, we're also going to get rid of the requirement to have this job on Veeam B&R side in our future releases to improve usability of the restore procedure.

Please let us know if you have any other questions.

Thanks!

[myFist]

Hello!

I see the possibility to meet the 3-2-1 rule with a copy job, but is a tape integration possible too?
And if we use the plug-in, we should change the image-based backup job to Guest Processing SQL Logs copy only, right?

Best regards

[HannesK]

Hello,

1) there is no integration for "plugins to tape" in V12. "file-to-tape" could be used, but I would avoid it because of complexity.

2) correct. only one backup type can back up logs.

Best regards,
Hannes

[PetrM]

Hello,

Don't forget that you also have a Copy-Only mode available on the plug-in side so that you can choose where it must be enabled.

Thanks!

[kaffeine]

@PetrM: is there a way to browse all the backed up files (full, incremental & TLOGs) as of yet through the VBR console?

With the traditional backups it's possible to see these on the Backups pane (through right click > Properties). The plug-in jobs have a dedicated node on the Backups pane ("Microsoft SQL" > "Backup Job Name"), but with a right click on the job only one option shown is shown: "Delete from disk".

[HannesK]

Via the "files" section, that would be possible. But that option does not exist for application plugins (SQL, SAP HANA, Oracle) in the home -> backups section.

[PetrM]

Hello,

@kaffeine what you ask is something that I really want to get in our future versions, this feature request fully makes sense but I have no idea about ETA. I'd appreciate it if you could give more details, maybe a couple of examples, what exactly would you like to see in the plug-in backup properties?

Thanks!

[kaffeine]

Hello PetrM,

thanks for the feedback. I would say the current properties view of normal VBR SQL backup jobs would be quite appropriate, something like this:



With this properties pane one can get with a quick glance a very complete overview of the backup contents of each job, namely the available restore points and the corresponding backup files on the backup repo.

The workaround that Hannesk suggested (raw file browsing through the Files section) provides much less info and is quite slower as well (probably because it fetches all the infos ad-hoc from the backup repo filesystem, instead of pulling them locally from the backup job statistics/reports).

[Bkp4273]

I'm also currently evaluating the Veeam Plug-in for Microsoft SQL Server and we also would need this feature to access (and extract!) the native SQL Server backup files (BAK/TRN). Or is there a hidden cmdline argument in MSSQLRecoveryManager.exe to extract the files?

[PetrM]

Hello,

I'm really glad to get all of these requests, thanks a lot for the feedback! At the moment, the only way to see the list of full and differential restore points is on the plug-in side in the restore window (I know it's not very convenient).

@Bkp4273 we don't have an option to extract data (BAK/TRN) from plug-in backups but may I ask you to clarify why you need it?

Thanks!

[Bkp4273]

Hi PetrM, thanks for the feedback.

We would need to extract the BAK/TRN files for long-term archiving and for manual restore purposes (self-built logshipping solution).

[PetrM]

Hello,

@Bkp4273 many thanks for your reply but I'm wondering which functionality we need to provide in the plug-in to fully replace your self-built log shipping solution? I'd appreciate it if you could describe this manual restore scenario that is not covered by the plug-in today.

By the way, the long-term archiving might be a part of GFS retention policy, we're aware of this request but ETA is yet to be determined.

Thanks!

[Bkp4273]

Our log shipping solution works technically similar to the SQL Server Log Shipping by Microsoft - but it's easier to install and has lower bandwidth consumption.

In order to fully replace our self-built log shipping solution, Veeam would need to extend the MSSQLRecoveryManager.exe with a "continuous restore mode" which continuosly restores transaction logs to an existing database which is in standby/readonly mode.

Currently this isn't possible, since "--recovery_state=standby" only works for a one-shot restore - not for continuous restores of transaction logs.

And great to hear that Veeam might consider to add long-term archiving as part of the GFS mechanism!

[PetrM]

Hello,

Very interesting info! We'll consider all these requests but I cannot say now when we can start implementation and what ETA is.

Thanks!

[Bkp4273]

Hello Petr,

there's one additional feature request that we would suggest:

A new command line argument called e. g. "--verifyonly" in MSSQLRecoveryManager.exe to run a RESTORE VERIFYONLY (e. g. for all files of the current backup chain) to validate the backup files (since currently it's not possible to access the BAK/TRN files).

But if Veeam allows to extract the BAK/TRN files, then we could do this ourselves on the extracted files ...

[PetrM]

Hello,

I like the idea with VERIFYONLY! Moreover, a few days ago there was a request for SureBackup for SQL plug-in. It could be a valuable improvement but today it is unclear when we'll start working on it. Anyway, the request is noted.

Thanks!