SQL Server Incremental Backups

[ekisner]

To start with, support case is 02298847.

I feel like I'm running into a brick wall with support, so I'm turning to the forums - perhaps for additional clarity, perhaps for a resolution. As it stands, the problem makes the Windows Agent completely untenable for a physical SQL server because the storage array is constantly being forced to do very expensive spinning disk reads where it is built to cache data.

We have physical SQL servers running Win2012R2, SQL 2014 - one standard and one enterprise. OS is installed on local spinning disks, Data and Log drives are presented via FC from a hybrid storage array. Both display the same behaviour.

Job Backup mode is "Entire Computer", to a Veeam Repository, App aware processing enabled, running every 4 hours.

The problem is that every 4 hours it doesn't do an incremental pass. It's doing a full pass. Every single job reports the "total backup size" as being the sum of the MDF and LDF files plus whatever incremental data exists on the OS drive.

To troubleshoot and help identify the problem, I did the following test procedure at a time when all of the staff who would be processing data were asleep (3am) and with all automated jobs disabled:

1) Stopped SQL server service and SQL Agent
2) Ran a backup, which took approximately 40 minutes and backed up the 450 or so GB it usually backs up
3) Ran a second backup immediately after the first finished, which backed up just 3GB over the course of several minutes
4) Started SQL Server service and Agent
5) Immediately after confirming both services were up, I ran a third backup, which took approximately 40 minutes and backed up the 450 or so GB again, in spite of the fact that there was a very near zero amount of data changing on the service over those last 3 or so minutes.

Help? This is far from an incremental job, which is really what we need. Full passes are killing our performance. If we can't get this working, I'll have to simply uninstall the agent and use something like Mirroring or an Always On group to replicate to a VM, and back up the VM. I'd like to avoid that hassle.

[Dima P.]

Hi Erik,

Can you elaborate why you perform a frequent imaged based backup instead of transaction log backup? Last should be less resource consuming, so should have less impact on your production. Image backup can be performed daily (during non-production hours) while transaction log backup can be performed every X minutes.

[ekisner]

To be honest I had no idea that option was even there. I'm experimenting with that now, and will follow up with that to find out if that resolves our issue... the resolution though will involve whether or not the database files are getting incrementally backed up rather than getting a full pass every time the job runs, as it is the underlying problem - having the problem occur less frequently is a workaround (one I'll be happy to implement, but I'd still be hoping for more).

Backup job is underway now, I'll follow up tomorrow after the schedule has had time to do stuff.

[Dima P.]

Erik,

Please check this functionality (it was designed specifically for a case like yours). Meanwhile I'll review your case and talk with the teams.

[ekisner]

Absolutely, the job is running and I will be certain to follow up with the current behaviour. To my knowledge, the matter was escalated up to a different tier of support, so the brick wall I mentioned was avoided!

Testing methodology will be to retain the 4 hour schedule, with 15 minute transaction log shipping, as this will allow me to investigate whether the underlying files are undergoing incremental or full passes more quickly. Once I know how it works, I'll scale the schedule back to daily with transaction log shipping, to mitigate the impact of the problem while support works with me.

[ekisner]

The impact of the problem is significantly lessened with the transaction log shipping in place. The problem itself remains. I will continue working with support, and update here for anyone else interested.