Standalone backup agent for Microsoft Windows servers and workstations (formerly Veeam Endpoint Backup FREE)
Post Reply
rmehta
Service Provider
Posts: 93
Liked: 7 times
Joined: Mar 16, 2016 8:15 pm
Full Name: Rajeev Mehta
Contact:

Large incremental size due to SQL backup: any ideas

Post by 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
Product Manager
Posts: 14287
Liked: 2877 times
Joined: Sep 01, 2014 11:46 am
Full Name: Hannes Kasparick
Location: Austria
Contact:

Re: Large incremental size due to SQL backup: any ideas

Post by 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
Veeam Software
Posts: 934
Liked: 287 times
Joined: Jan 31, 2011 11:17 am
Full Name: Max
Contact:

Re: Large incremental size due to SQL backup: any ideas

Post by 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
Service Provider
Posts: 93
Liked: 7 times
Joined: Mar 16, 2016 8:15 pm
Full Name: Rajeev Mehta
Contact:

Re: Large incremental size due to SQL backup: any ideas

Post by 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
Service Provider
Posts: 238
Liked: 53 times
Joined: Nov 17, 2014 1:48 pm
Full Name: Florin
Location: Switzerland
Contact:

Re: Large incremental size due to SQL backup: any ideas

Post by 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.
Product Manager
Posts: 14396
Liked: 1568 times
Joined: Feb 04, 2013 2:07 pm
Full Name: Dmitry Popov
Location: Prague
Contact:

Re: Large incremental size due to SQL backup: any ideas

Post by Dima P. »

dasfliege,

Just to make sure - this SQL server backup is running in volume level backup and CBT driver is installed, right?
rmehta
Service Provider
Posts: 93
Liked: 7 times
Joined: Mar 16, 2016 8:15 pm
Full Name: Rajeev Mehta
Contact:

Re: Large incremental size due to SQL backup: any ideas

Post by 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
Service Provider
Posts: 238
Liked: 53 times
Joined: Nov 17, 2014 1:48 pm
Full Name: Florin
Location: Switzerland
Contact:

Re: Large incremental size due to SQL backup: any ideas

Post by dasfliege »

Dima P. wrote: Jan 06, 2022 12:43 pm 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
Product Manager
Posts: 14287
Liked: 2877 times
Joined: Sep 01, 2014 11:46 am
Full Name: Hannes Kasparick
Location: Austria
Contact:

Re: Large incremental size due to SQL backup: any ideas

Post by 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
Service Provider
Posts: 93
Liked: 7 times
Joined: Mar 16, 2016 8:15 pm
Full Name: Rajeev Mehta
Contact:

Re: Large incremental size due to SQL backup: any ideas

Post by 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
Product Manager
Posts: 14287
Liked: 2877 times
Joined: Sep 01, 2014 11:46 am
Full Name: Hannes Kasparick
Location: Austria
Contact:

Re: Large incremental size due to SQL backup: any ideas

Post by 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
Service Provider
Posts: 93
Liked: 7 times
Joined: Mar 16, 2016 8:15 pm
Full Name: Rajeev Mehta
Contact:

Re: Large incremental size due to SQL backup: any ideas

Post by 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
Post Reply

Who is online

Users browsing this forum: No registered users and 22 guests