Native SQL backups

[lowlander]

Hi,

we often see a scenario where database admins are very confident with SQL native back-ups. In some scenario's this is understandable, however resulting in a enormous amount of storage being dumped on a piece of storage.

I am reviewing the options we have to backup these files with Veeam ( for the purpose of a last safety zone ) :
- backup the disk where the SQL native dumps are placed, with a Windows Agent;
- backup the disk where the SQL native dumps are placed, with a Windows Agent and backup the target repository to tape;
- Backup the SQL native files directly to tape;

I was thinking of other options, like deduplication feature of Windows 2019 and Veeam NAS backup for these purposes. But how effective is dedup on OS level or Veeam NAS backup with very large files like SQL dumps ?

What is the best scenario to follow in this use case, and what are pros or cons ?

[HannesK]

Hello,
SQL backups compress very well (just try to zip one). So whatever option you take that has compression will work (assuming the backups are unencrypted). Everything except file-to-tape has compression (well, there you have tape drive compression)

Files with NAS backup are small (64MB)

Best regards,
Hannes

[ejenner]

I tried implementing Veeam NAS backup for backing up an SQL dump just as you are doing but found it ravaged my license capacity to the point where I couldn't backup anything because NAS backup had used up all our licenses. They've linked it with capacity if I remember correctly. So for large backups it'll take 10s of licenses out of your pool and for that reason I couldn't use it. I did mention this to Veeam but they said it was my fault for not spending more.

I have sent our flat-file SQL backups to tape before. But the problem with that scenario is that tape backups are slow and suffer badly from interruption. So if you patch regularly on your network as we do then the servers holding the files and the servers controlling the tapes are either vulnerable to interruption if patching automatically and backups never complete or cause restrictions for the security team who are unable to patch because the server cannot be rebooted for days at a time.

[HannesK]

sure, NAS backup is the most expensive way

If you talk about 10 of licenses, then it sounds like 5-10TB (500GB = 1 Veeam Universal License). That amount of data should be no problem with proper infrastructure in place (at least I know customers backing up much more). It sounds like you have a "special" environment, so in the end there no alternative to agent based backup (as you don't mention VM-based backup, I assume that this is no option anyway)

[lowlander]

Ok, encryption would be a bottleneck in our case. We can consider disable encryption, and use tape out for periodic backups.

I am realy interrested
- in the gains you get when compressing ( for example 1 TB ) bak files with the veeam agent job. Is there a rule of thumb or percentage that indicates the savings basaed on compression ?
- in the findings in space savings on the target repository side, when using NAS backups. Especially while a large 1 TB SQL file seems to me not efficient while Hannes stated files with NAS backups are small ( 64 MB );

hope you both can comment on this

[HannesK]

something around 4x compression is what I see for SQL database backups with default settings. We use LZ4 everywhere per default. So it makes no difference from a backup size perspective. It's the same amount of backups whether NAS backup compresses with LZ4, or agent backup or whatever software is used.

Sure, you can just increase compression level. That will give different values.

Not sure what you mean with the 64MB "not efficient". Veeam NAS backup always stores backups in 64MB chunks except for inline deduplication appliances (there it is 1GB because of the maximum number of limitations of these boxes).

[lowlander]

Ok, clear story. The 64MB chunks for NAS backup I misinterpret being negatively impacted for large files. Good to understand that LZ4 is used in both NAS backup and agent backup.

Thanks !