Comprehensive data protection for all workloads
Post Reply
Bunce
Veteran
Posts: 259
Liked: 8 times
Joined: Sep 18, 2009 9:56 am
Full Name: Andrew
Location: Adelaide, Australia
Contact:

SQL: Do not truncate logs ignored

Post by Bunce »

Hi All,

I may have misinterpreted the setting, but something I've never quite understood is the 'Do not truncate logs' functionality when Veeam is backing up a SQL box.

Like many SQL Admins we're quite anal and want to have the full ability to do Point-In-Time restores and the only way to achieve this is to perform trans log backups independent of Veeam. These are offloaded to a separate disk or server where they can then be used for restored and be backed up (by Veeam if required).

We do however want to still maintain a Veeam image level backup of the SQL box so this runs nightly however with Application-Aware processing enabled, even with 'Do Not truncate logs' selected, the logs are truncated which ruins the existing trans log backup chain. As a result, we have to disable application-aware processing completely on that VM which isn't ideal.

I'm wondering why Veeam doesn't exectue the 'Copy-Only-Backup' SQL option that would satisfy the 'Do not truncate logs' option. Our Dev's use this when we need to perform ad-hoc backups without invalidating the existing trans-log backup chain and I believe has been around since SQL 2005 so should offer conformity across all versions..
Gostev
Chief Product Officer
Posts: 31428
Liked: 6633 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: SQL: Do not truncate logs ignored

Post by Gostev »

Hi Andrew, in fact we have already added this option (VSS copy only) to the next release. Thanks!
cronosinternet
Influencer
Posts: 21
Liked: 9 times
Joined: Oct 31, 2012 1:05 pm
Full Name: Lee Christie
Contact:

Re: SQL: Do not truncate logs ignored

Post by cronosinternet » 5 people like this post

Unless something drastic has changed, the "Do not truncate logs" does indeed work as specified.

In our testing we concluded that with application-aware *or* VMware tools quiescence turned on, then SQL receives a fake "backup database" to virtual device series of instructions. Obviously no real backup is taken as such.

This has the following two caveats

1. Differential backups do indeed get broken as SQL is unable to refer to the prior "full backup" as its associated with the virtual device. No workaround.
2. When doing a point in time restore, instead of using the database backup history in the msdb database, and achieving an easy restore, you have to manually select your SQL .bak and .trn files and perform a restore that way.

We do exactly as you do, we take an image using Veeam but make SQL Backups to a separate disk (marked as independent so Veeam ignores it). Full backups on a Sunday, Diff backups on other days, Trn backups every 15 minutes. Works a treat but we turn off *all* application-aware and VM tools quiescence.

Our conclusion was
a) never use VMware tools quiescence on a windows server, only Linux
b) only ever use application-aware backups for servers where you *do* need that functionality. Domain controllers, exchange boxes, SQL servers that have no separate backups etc.

But as I say, the "do not truncate logs" option works fine. Are you sure you're seeing that; could it be a differential backup you are trying?

As you've said, implementing the virtual copy-only backup is a true fix.
Bunce
Veteran
Posts: 259
Liked: 8 times
Joined: Sep 18, 2009 9:56 am
Full Name: Andrew
Location: Adelaide, Australia
Contact:

Re: SQL: Do not truncate logs ignored

Post by Bunce »

Yeah we do Diff's as well so its probably that - I didn't look into it too deeply after noticing that the MSDB backup history was bust to be honest.
dbarroco
Influencer
Posts: 12
Liked: never
Joined: Apr 14, 2011 6:59 pm
Full Name: David B
Contact:

Re: SQL: Do not truncate logs ignored

Post by dbarroco »

Hello, thank you for this info as it got me thinking on the obvious, but I could not quite understand what Lee's concluded in the end.

I do use SQL weekly full, daily diff and hourly trans backup to dedicated backup store elsewhere. On top of that Veeam (6.5) backs up the vm each night.

The 'do not truncate log' option is selected, but the application aware is enabled, as i always took it as a precautionary step regarding the database -hence recommended, although Lee states that for the users doing dedicated sql backups this should be disabled. On a db running 24/7 production state i wondered if this would not compromise its state somehow.

should the application aware be disabled even so?

thank you
David
Vitaliy S.
VP, Product Management
Posts: 27025
Liked: 2709 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL: Do not truncate logs ignored

Post by Vitaliy S. » 1 person likes this post

If you're already doing backups of SQL Server using native tools, then disabling AAIP would still allow you to restore the backed up VM data (SQL Server) successfully. However, if your current backup approach works fine right now, then there is no need to disable it.
cronosinternet
Influencer
Posts: 21
Liked: 9 times
Joined: Oct 31, 2012 1:05 pm
Full Name: Lee Christie
Contact:

Re: SQL: Do not truncate logs ignored

Post by cronosinternet » 1 person likes this post

dbarroco wrote:Hello, thank you for this info as it got me thinking on the obvious, but I could not quite understand what Lee's concluded in the end.

I do use SQL weekly full, daily diff and hourly trans backup to dedicated backup store elsewhere. On top of that Veeam (6.5) backs up the vm each night.

The 'do not truncate log' option is selected, but the application aware is enabled, as i always took it as a precautionary step regarding the database -hence recommended, although Lee states that for the users doing dedicated sql backups this should be disabled. On a db running 24/7 production state i wondered if this would not compromise its state somehow.

should the application aware be disabled even so?

thank you
David
Are you sure that the Application Aware aspect is working correctly? Certainly on the versions of SQL we looked at, a differential backup requires comparison against the most recent full backup. SQL looks in the msdb backup history to locate the most recent full backup and because Veeam invokes a "fake" virtual backup, there is no full backup to compare against; this is why differential fails.

Check your MSSQL errorlog - at the time your veeam backup runs, is it full of "Database Backed Up" entries?

You mention 24x7 production - in which case the restore procedure depends on the failure

a) Developer mistake (accidentally dropped table or something) - no need to use Veeam, process a point in time restore using your SQL backups.
b) Complete system disaster (deleted VM, corrupted VM, site down etc) - restore the image of the system from your Veeam backup, then effectively delete the databases and restore those (again, using point in time) from your SQL backups. Assuming your SQL backups aren't affected by the same disaster.

People overly worry about things like SQL/Exchange sometimes. Remember that without application aware your Veeam backup is no different to a normal VMware snapshot. Reverting to a snapshot or non-aware backup is just like pulling the power cords out of a machine. Not to be advised but I've never seen a windows server corrupted due to a power loss. SQL's database recovery from a crash is very good.

cheers
Lee.
uweiss
Novice
Posts: 6
Liked: never
Joined: Dec 05, 2013 10:35 am
Contact:

[MERGED] Separate SQL Backup

Post by uweiss »

Hello,

Some days ago i created two jobs within SQL Mgmt Studio to do a weekly full backup and a diff backup every three hours. Today i recognized that diff backups are getting smaller after VBR backup was running. After reading some other threads, it looks like VBRs log truncation makes them unusable. Now i want to put together how valid backups can be done, and would like to verify this is correct.

1. With VBRs log truncation
Instead of doing a weekly full backup, change it to a daily full backup AFTER VBR jobs were running.
Do the VBR backups are still consistent and usable then?

2. Turn off log truncation
Turn off log truncation within VBR backup job.
So the backup jobs are consistent in any case. What about VBR backups?
We use DBs in simple recovery mode only, so no need for log backups to truncate logs.

Oh, btw., because Veeam still thinks not having an agent to restore SQL f.ex. is a feature... No, it's NOT...


Thank you
Urs
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: SQL: Do not truncate logs ignored

Post by foggy »

Hello, generally, if you're doing SQL backups using native tools, you do not need to enable logs truncation in Veeam B&R.
cronosinternet
Influencer
Posts: 21
Liked: 9 times
Joined: Oct 31, 2012 1:05 pm
Full Name: Lee Christie
Contact:

Re: SQL: Do not truncate logs ignored

Post by cronosinternet »

Hi uweiss

1. Disable application aware processing, either at the job level or at the individual VM level.
2. Ensure you're not using VMware Tools Quiescence (job level)
3. Respectfully, running databases in simple recovery mode, but then performing a diff every 3 hours - you're contradicting yourself. You either need the ability to restore to point in time or you don't. You will also be wasting a lot of space with diff backups ever 3 hours.

The general rule of thumb we use is
a) Full backups on a Sunday (say 9PM)
As part of the Full backup plan, add a Cleanup files / cleanup history - files/jobs/tasks older than 2 weeks
b) Diff backups Mon-Sat (again, 9PM)
c) Transaction Log backups every 15 minutes

This gives you the ability to restore to any point in time in the last 2 weeks. Making the cleanup task part of the full backup task means you never accidentally delete a full backup required as the start of a chain. Using diffs keeps the daily backup sizes down.

cheers
Lee.
uweiss
Novice
Posts: 6
Liked: never
Joined: Dec 05, 2013 10:35 am
Contact:

Re: SQL: Do not truncate logs ignored

Post by uweiss »

Hello Lee

Thanks for this detailed answer.

What's the exact reason to not use VMware Tools Quiescence or application aware processing? Do i have to do that, or would it be enough to disable log truncation? Because then, the databases within the VBR backup may are not consistent. Of course, i have the separate backup files, but just in case (you never know...). And i would like to have a consistent database within the VBR backup.

Space isn't a big problem. After 24 hours i have a diff which is around 5-6MB. Not that much changes in these databases. But maybe i change the backup strategy as you have described it. Have to try out what's best for us.

Thanks again
Urs
cronosinternet
Influencer
Posts: 21
Liked: 9 times
Joined: Oct 31, 2012 1:05 pm
Full Name: Lee Christie
Contact:

Re: SQL: Do not truncate logs ignored

Post by cronosinternet »

Disabling log truncation still breaks your backup chain...just that your logs don't get truncated. Look in your MSSQL errorlog, you'll see a BACKUP DATABASE to a virtual device when veeam kicks in if either AAIP or Tools Quiescence are enabled.

Tools quiescence calls a freeze on the DB server and can cause timeouts with busy databases. Its kinda weird.

The reality is this: How often have you had to restore your entire SQL server from a Veeam backup?

We have never had to restore any of our servers, however we've done plenty of restores of individual databases. Hence why our veeam approach is quite basic, but our SQL approach is extensive.

In any event, so you take a veeam backup without any form of application aware. When you restore it, its as if the power cords were pulled from a server - the server will boot, maybe windows will do a checkdisk, then SQL server will start and perform crash recovery which I've never seen fail. The fact you've had to restore your SQL server from an image probably means you have bigger issues ;-)

"Tool for the job".
Novell2
Enthusiast
Posts: 82
Liked: 2 times
Joined: Feb 05, 2010 4:12 pm
Full Name: Zeller Werner
Contact:

[MERGED] Best BK7 Jobdetails for MS SQL2008

Post by Novell2 »

Hi,
I don't have the time to watch the Video for 1h for best practice at the Moment. How is the best practice to set up an BR7 Job for a sql2008 Standard Server?
What is in General the best for SQL - Application Ignore Application processing failure? Transaction logs - Do not truncate logs?
Thanks for your Help!

Novell2
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: SQL: Do not truncate logs ignored

Post by foggy »

Zeller, please review the thread above for some practical tips. Here's another useful topic regarding SQL backup: SQL Backup Jobs.
andrewpetre
Influencer
Posts: 15
Liked: 4 times
Joined: Nov 11, 2013 10:53 pm
Full Name: a p
Contact:

Re: SQL: Do not truncate logs ignored

Post by andrewpetre »

Gostev wrote:Hi Andrew, in fact we have already added this option (VSS copy only) to the next release. Thanks!
This will save my backup scheme if we can get it working as Copy Only. Otherwise I have to start ignoring SQL in all my VMs.

Is there a switch or setting? Or is that just how it's done now by default?

And by version, did this mean dot release or v8? Thanks!
Vitaliy S.
VP, Product Management
Posts: 27025
Liked: 2709 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL: Do not truncate logs ignored

Post by Vitaliy S. »

Hi Andrew,

This is going to be an option in the advanced VSS settings in v8 release.

Thanks!
Post Reply

Who is online

Users browsing this forum: Google [Bot] and 133 guests