Large incremental size due to SQL backup: any ideas

[rmehta]

A customer has a SQL server where a process updates all the tables on a daily basis; this generates a very large incremental (800GB) everyday. The data on the table changes everyday and not required to be backed up. I am not sure what best way to protect this server. Customer is okay with a weekly restore point.

At the moment; this server generates over 200GB of incremental data everyday and many times backup fails

[HannesK]

Hello,
hmm, not sure I got it right... first I read 800GB incremental every day and last sentence I read 200GB per day... that's 4x difference. Whether it's large or not is hard to say without the overall amount of data. But for database servers, change rate per day is expected to be high. Can be 30-50% or even more. So that's normal.

many times backup fails

why does it fail? If it's a lack of resources (which I assume because the text is about large incremental backups), then more resources (network, storage, whatever the bottleneck is) would be the solution.

If the reason is unknown, please check with support why the backup fails (please post the case number for reference)

Just to be sure: you are using the Veeam change block tracking (CBT) driver?

Best regards,
Hannes

[Regnor]

I know simliar cases. We just had to accept the high change rate and accordingly sized the repository; if capacity is a problem for you, you could lower the retention in the primary repository (if possible without violating the customer's SLA).
As an alternative you could play with the compression level in the backup job. Perhaps you're lucky and the data can be compressed and you benefit from a higher level. But keep in mind that it will increase the load on the SQL server; so carefully track cpu usage during backup.

[rmehta]

I am sorry; it reads 800GB(approx) of change and generates 200GB of actual data (after dedupe and compression) which gets written to the repository. As we are backing up at the volume level and the databases actually sit on a different volume; customer is okay for us to have the following schedule. Unfortunately, because we have chosen to do volume level backup with Veeam agent; I am not sure if we can exclude the volume where databases sit; and just back it up once a week to achieve what the customer requires.
Database Frequency
System Databases Daily
Halo_Repository_prod Daily
SSLDBA Daily
SSLMDW Daily

All other database (Halo_* and LP_Src) Weekly
Analysis Server
Database Frequency
All cubes (Halo*) Weekly

[dasfliege]

I jump in on this topic:
Our customer has a SQL server with a 3.7TB DB drive. Every 12 hours, 2.4TB are beeing read and 1TB written. They do have one recurring maintenance job that is reorganizing indexes and stuff but is only running once a week. So that does not explain why we have such a high daily incremental size. It is almost impossible that 1/3 of the data is changing every day.

Does anyone experienced similar problem or has any hint where we could search for possible reasons?

[Dima P.]

dasfliege,

Just to make sure - this SQL server backup is running in volume level backup and CBT driver is installed, right?

[rmehta]

yes that CBT driver being used. In our senario customer has a custom script which rewrites database values on certain days. I had moved the backup to occur a bit earlier and the situation has improved a bit; we see less failures now. However, it would be good to know if there is a way to achieve the outcome that customer wants. Could we may be try and configure volume level backup and setup file exclusions to database files path and then another job which runs only weekly and only protects the complete volume which has the database files.

[dasfliege]

dasfliege,

Just to make sure - this SQL server backup is running in volume level backup and CBT driver is installed, right?

I'm sorry. I didn't saw that this post is part of the veeam agent forums. In my case it's a virtual machine.
I have opened a support case yesterday and according to my logs, CBT is beeing used properly. vSphere is reporting that really that much data has been changed. I'll tried reseting CBT now and will see today if that helped in any way. I may have to open a case with VMWare if the problem persists.

Sorry for the confusion.

[HannesK]

Unfortunately, because we have chosen to do volume level backup with Veeam agent

I would say "fortunately" because file level backup would be even worse

if we can exclude the volume where databases sit; and just back it up once a week to achieve what the customer requires

yes, that is possible if everything is distributed on different volumes (configure the volumes you need). But I would avoid that complexity and fix the situation that daily backup can be done for everything. If the system is too slow for proper backup, then I don't want to be the guy who has to restore it in case of emergency

It is almost impossible that 1/3 of the data is changing every day.

it's aligned with what I wrote earlier (30-50% per day) - which is what customers told me during the last years.

[rmehta]

We reconfigured the backup job
- one job backs up all the volume except for the SQL data/log volume
- another job captures volume containing SQL Log and databases data

Backup 1 job completes fine processing speed as per job is apprx 24MB/s (diffrent volumes have diffrent read speed from 7 MB/s to 20 Mb/s)
However, the backup containing SQL volumes is still erroring out; it runs for over 20 hours and most of the time errors due to snapshot storage could not grow in time.

[HannesK]

Hello,
I'm not sure what the question is, but the speed is lower than in my old home-lab... so there seems to be something wrong with the environment.

Best regards,
Hannes

[rmehta]

yes the issue was indeed with the envrioment; the Raid caching was off and set to Wright through on the server hosting repository; we changed it to WB and issue resolved