Support for built-in SQL differential backups?

[dasfliege]

We are troubleshooting a case with Veeam, VMWare and Microsoft support, where we have to backup a 4TB SQL database and the backupjob is taking way too much time and writes way too much data, compared to the actual amount of data that has been changed.

According to Microsoft Support, it is an expected behaviour, that if Veeam is triggering a VSS based backup of an SQL instance, they are considering this as a FULL backup. Therefor the complete database must be read and it doesn't matter how much data really changed since the last backup. Interesting thing is, that also VMWare CBT is reporting almost the whole database as changed blocks, which leds to backup jobs taking a huge amount of time and space, because they in-fact always perform a full backup of the partition where the database file is located.

The way around this issue would be, in terms of SQL server, to perform differential backups. Unfortunately there is no option in veeam to do this. So i would have to schedule backups inside SQL server and just backup the files with veeam. This isn't pretty convenient, as this way it would not be possible to perform log backups with veeam.

Another interesting thing is, that we had much faster and smaller backups when we backed up the exact same server with Veeam Agent instead of B&R. I actually don't really understand why, because the logic should be exactly the same? Only explanation that makes any sense is, that if SQL server is executing a VSS based backup, somehow VMWare CBT is considering the whole database as changed. The veeam Agent seems to handle this in a different manner and seems to be able to recognize that in-fact only a incremental backups should be performed.


Has anyone stumbled across the same issue when backing up a quite big database? How do you solved this problem?
Is there any attempt from Veeam to implement a function to perform differential SQL-Backups?

[Andreas Neufert]

2 things are mixed up together here that run independent from each other and do not influence each other.

VMware CBT runs within the VMware Storage Stack in between the VM and the Storage. It will just mark any area of the disk as changed when it was really changed by the VM. It is NOT aware of what is running within the VM, not even aware what operating system or filesystem runs there.
VSS or non VSS does not matter here as VMware CBT is just noting down changes on disk. A VSS snapshot itself will lead into some extra changes on disk for the livetime of the VSS snapshot and the incoming changes on the database. But as this VSS snapshot exists only for some seconds there is not much change on the disk side.

If VMware CBT is reported changes on disk, then they happened.

Yes, Microsoft is right on view of the database a snapshot based backup is always a full backup, but because of CBT you backup only the changes on disk, it is an incremental backup (incremental forever) based on the view of the VM.

Now the question is how to find out what changed all the blocks and why, but before we go to this path we need to check one thing in the Veeam Job statistics. Click on the VM in the statistics, it will give you on the right side then additional information of this VM.
Do you see a line there that VMware Change Block tracking was disabled or could not be enabled? If this is the case, we need to read all the time 100% of the data.

[dasfliege]

Thanks for the clarification. That's exactly what i thought it would work like. However, the statement from microsoft confused me.
CBT is working fine. As written above, we already had cases with veeam and VMWare. Both have confirmed that CBT is working perfectly well and that the blocks really changed for whatever reason. We also did CBT reset and all that stuff.

So i guess i have to continue troubleshooting with Microsoft support in order to find the root cause. Thanks anyway for confirming, that on the veeam side everything is fine, but we have to find our why this particulat SQL server is behaving that strange.

[Andreas Neufert]

Check this one out please:
https://www.winhelponline.com/blog/prog ... d-disk-io/

It will help you to identify which application did the IO and you can go from there to look deeper into the application.

[Andreas Neufert]

As well you could look in the SQL monitor for the Buffer Manager: Page Reads/Sec to get a feeling if they match with the disk IO read from the task manager approach.
You can go to Vmware Monitoring in the vcenter to get additional IO/s statistics there.