MSSQL Differential Backups, VSS, and PBS

[tcabernoch]

We have a client that wants "differential" MSSQL backups.
I don't know why they want this. Usually we do a Full and hourly Logs.
But they do specifically want 'differential' backups. And they are failing.

Our DBA figured out why its failing. He taught me something about Proxmox today.
I'm posting here for general comment, to see if anyone has experience with it.
It appears we are going to need to apply this reg hack.

This info is all quite well laid out in the following article. Good graphics to explain exactly what's going wrong.
https://blog.datact.ch/post/how-to-correctly-backup-mssql-server-with-proxmox/

"when Proxmox uses the QEMU guest agent to run fsfreeze, it requests a full backup from the SQL VSS writer. That breaks the backup set for all databases because it consolidates / deletes the logs that are needed to restore."

The PVE wiki says what is happening. Not as well as the blog post above, but its official info.
https://pve.proxmox.com/wiki/QEMU/KVM_Virtual_Machines (Search for "Filesystem Freeze & Thaw".)

On Windows guests, some applications might handle consistent backups themselves by hooking into the Windows VSS (Volume Shadow Copy Service) layer, a fs-freeze then might interfere with that. For example, it has been observed that calling fs-freeze with some SQL Servers triggers VSS to call the SQL Writer VSS module in a mode that breaks the SQL Server backup chain for differential backups.

This was the suggested fix.

• Configure the QEMU Guest Agent to use a different VSS variant that does not interfere with other VSS users.

And here, you get the actual regedit you need in order to un-break SQL w VSS w PBS.
https://pve.proxmox.com/wiki/VM_Backup_Consistency

The Qemu-Guest-Agent on Windows guests will use VSS.
By default, it will issue VSS_BT_FULL. If other backup solutions are used directly within the Windows guest, this can break their backup chains.
Since version 0.1.262, the Qemu-Guest-Agent for Windows can be configured to use different VSS semantics.
The setting needs to be done via a Registry entry.

• HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\QEMU Guest Agent VSS Provider\VssOption

If there is no other backup solution involved, leaving it at the default value of 1 (VSS_BT_FULL) should be fine.
As soon as another backup solution is used within the Windows guest, chances are high that it relies on VSS.
To not interfere, the recommended option is to switch the Qemu-Guest-Agent to use the VSS_BT_COPY option.
Therefore, set the VssOption option to the numerical value of 5.

1	VSS_BT_FULL
2	VSS_BT_INCREMENTAL
3	VSS_BT_DIFFERENTIAL
4	VSS_BT_LOG
5	VSS_BT_COPY

​

Quite surprising. Let me know if you have any more info here.

- t