Using VSS-Copy instead of VSS-Full doesn't fix the issue, as VSS shouldn't be called on a disk where SQLServer files are stored.
I've added more details in the issue.
Last edited:
PBS breaking customer SQL backups. Backups without FS-Freeze?
- Thread starter ozdjh
- Start date
I've added more details in the issue.
Hi,
it is still unclear to me, which versions of the qemu-ga have this option. Neither an installer of the latest(?) version asks me for this, nor do i have the Registry key. Can someone clarify please?
From my understanding for Snapshot Backups of the underlying blockdevice vss-copy ist totaly sufficient and way less complicative for the guests workload.
thnks.
Greetings!
I've found some commits on the qemu-repo that were made on 09.03.2023: (DD/MM/YYYY)
* main switch
* installer option
The 'latest-stable' linked in the proxmox wiki was (as I am writing this) updated on 09.01.2023 (see: Download site)
So it cannot contain the patch..
Edit: the actual repositories that are used as source for the 'fedorapeople.org' downloads don't have any mention of 'VssOption' in them:
* virtio-win-pkg-scripts
* kvm-guest-drivers-windows
Edit2:
Just as a reference - these are the errors we are seeing: (for each database that exists)
I've found some commits on the qemu-repo that were made on 09.03.2023: (DD/MM/YYYY)
* main switch
* installer option
The 'latest-stable' linked in the proxmox wiki was (as I am writing this) updated on 09.01.2023 (see: Download site)
So it cannot contain the patch..
Edit: the actual repositories that are used as source for the 'fedorapeople.org' downloads don't have any mention of 'VssOption' in them:
* virtio-win-pkg-scripts
* kvm-guest-drivers-windows
Edit2:
Just as a reference - these are the errors we are seeing: (for each database that exists)
BACKUP failed to complete the command BACKUP DATABASE master. Check the backup application log for detailed messages
BackupVirtualDeviceFile::TakeSnapshot: failure on backup device '{UUID}'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).
BackupVirtualDeviceFile::SendFileInfoBegin: failure on backup device '{UUID}'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).
Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3013
Last edited:
Greetings!
It might be that the feature will be included in the next virtio-win iso (see: issue kvm-guest-drivers-windows)
But it is unknown when that will be available.
It might be that the feature will be included in the next virtio-win iso (see: issue kvm-guest-drivers-windows)
But it is unknown when that will be available.
Last edited:
Greetings!
As a workaround: In the meantime we have disabled the 'SQLWriter' service and are creating local backup-dumps using the MSSQL-Agent-Jobs. (that are included in the VM backups)
As a workaround: In the meantime we have disabled the 'SQLWriter' service and are creating local backup-dumps using the MSSQL-Agent-Jobs. (that are included in the VM backups)
As a follow-up - It seems a new virtio-win iso got released a few weeks ago:
https://fedorapeople.org/groups/virt/virtio-win/direct-downloads/stable-virtio/
It might support the option to switch from VSS-Full to VSS-Copy. (haven't tested it yet)
https://fedorapeople.org/groups/virt/virtio-win/direct-downloads/stable-virtio/
It might support the option to switch from VSS-Full to VSS-Copy. (haven't tested it yet)
Last edited:
Having the same issue on one of our customer, is there any Information to get COPY_ONLY via the VssOption-Switch in the registry?
Ok, found it:
VSS-Options
VSS-Options
Code:
Implementation, put the desired backup type number inside Regkey value
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\QEMU Guest Agent VSS
Provider\VssOption, so that the program can query the desired backup type.
VSS backup types:
number type
1 VSS_BT_FULL
2 VSS_BT_INCREMENTAL
3 VSS_BT_DIFFERENTIAL
4 VSS_BT_LOG
5 VSS_BT_COPY
Last edited:
The info is also available in the linked docs from MS that you quoted
Missing the point here. The registry value needs to be called "VssOptions" and contain a number. The Win32 API documentation only notes the names of the backup types.The info is also available in the linked docs from MS that you quoted
Having broken SQL Backups is detrimental to business interests.
We all know "No Backups - No Mercy" - but having SQL backups with broken chains ain't funny.
If PVE is used to host VMs which may have content that is a blackbox to the hoster, this can go sideways quite quickly if the hoster backups up the VMs using PBS.
I think it would be important to document this either in the PBS documentation or the wiki, as in neither one the word "SQL" can be found... (Source 1, Source 2)
We run all our VMs with the QUEMU agent option "freeze-fs-on-backup=0" set. It's not ideal, but it's the lesser of 2 evils. Restoring a client backup (which is an incredibly rare event anyway) results in the same outcome as if the VM lost power and restarted at that point in time. Sure, it's not 100% consistent, but it's much better than trying to explain to a client that they've lost their business databases because the VM level backups we took broke their SQL backups.
You're right, its a tradeoff that might be an option for some.
In our case, this isn't an option.
I'm aware that it isn't really a proxmox issue at hearth, but I still think there should be documentation about it - so people are aware.
I also have to correct myself, there is documentation here, describing your solution.
Filesystem Freeze & Thaw on Backup
By default, guest filesystems are synced via the fs-freeze QEMU Guest Agent Command when a backup is performed, to provide consistency.
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 SQLWriter VSS module in a mode that breaks the SQL Server backup chain for differential backups.
For such setups you can configure Proxmox VE to not issue a freeze-and-thaw cycle onbackup by setting the freeze-fs-on-backup QGA option to 0. This can also bedone via the GUI with the Freeze/thaw guest filesystems on backup for consistency option.
Disabling this option can potentially lead to backups with inconsistent filesystems and should therefore only be disabled if you know what you are doing
I think it would be great to add a section or a link to this, as well as additional text regarding the VSS-BT-COPY option.
Quick correction:
This isn't true. Copy-Only Backups are out of band Backups. See here.
Also, it doesn't solve the issue of breaking backups without knowledge of the content of the VM. If you have a customer who rents a VM where you as hoster don't know the content, you can't know where the files are stored. If you inform the customer "You need to make sure to set this registry key if you don't want your MSSQL backups to break!" then it's the customers responsibility to implement this.
Last edited:
Finding this page and wondering now that the requested registry entry has been added, is it agreed that the correct way to handle this is to set the registry entry to set KEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\QEMU Guest Agent VSS Provider\VssOption to VSS-Copy and enable "freeze-fs-on-backup=1". ?
I've done a small writeup here: https://blog.datact.ch/backup-mssql-server-with-proxmox