Backups having an image problem: VEEAM and SQL Log Truncation

Backing up and restoring databases from an image based recovery solution is very different to traditional SQL Server recovery methods, as I’ve found out with VEEAM Version 7.

As VEEAM creates image based backups, what is glaringly missing from a DBA point of view is full and transaction log backups. However, VEEAM offers a feature called Application Aware Image Processing (AAIP), which uses the Volume Shadow Copy Service (VSS) to create consistent backups of SQL Server data and log files. This feature has an interesting configuration option that plenty of my clients pick called Truncate Logs, which as the name suggests, truncates SQL Server database transaction logs whenever an image backup of the virtual machine is completed.

Now in order to truncate a transaction log for a database configured to use the FULL recovery model, the transaction log needs to be backed up, VEEAM executes the following command in order to achieve this.


BACKUP LOG [Database Name] TO DISK = ‘NUL’

What this does is effectively backup the transaction log to nowhere, thereby truncating it without backing it up (not a good idea in most cases). However often I’ve found databases where transaction logs are not truncated even though the VEEAM AAIP Truncate Log option is selected, leading to excessive growth of transaction log files.

Ruling out authentication or permissions issues, the first time I encountered this problem, I queried the master.sys.databases table and found that these databases have a log_reuse_wait_desc value of LOG_BACKUP. This means that the transaction logs of these databases need to be backed up in order for the inactive portions of the log to be truncated and reused. But we know that VEEAM executed a BACKUP LOG command on each of these databases, so what’s the issue?

I queried the msdb.dbo.backupset table and found that the offending databases had no record of any backup ever occurring. Remembering that a FULL backup of a database needs to have occurred at least once prior to that databases transaction log being backed up, from SQL Server Management Studio I executed a FULL database backup for each database.

Following the next scheduled VEEAM backup, I checked the database transaction logs and found that they had been truncated, so a native SQL Server FULL database backup needs to be executed at least once for the log truncation AAIP option to work as intended.

However that got me thinking about what was really going on, and how this VEEAM AAIP Truncate Log option may be placing my clients’ recovery processes at risk. Imagine a scenario where an enterprise’s DBA is doing the right thing and diligently performing regular transaction log backups based on a defined Recovery Point Objective (RPO) being met.

Unbeknownst to the DBA, the enterprise’s backup administrator is also scheduling a VEEAM image based backup with the AAIP Truncate Log option enabled. In this scenario, without the DBA’s knowledge, VEEAM is truncating the transaction log without backing it up. This scenario would result in missing log records if point in time recovery from transaction log backups was attempted, thereby placing the RPO at risk.

The reason for enabling the AAIP Truncate Log option is to control the size of the transaction log. However this is only ever an issue when databases are using the FULL recovery model, which should only be selected if there is an RPO that requires it. This suggests that if the FULL recovery model is required, then regular transaction log backups should be scheduled in order to achieve the RPO. As transaction log backups truncate the transaction log, there should be no need to configure VEEAM to truncate the log.

The other option is to select the SIMPLE recovery model, however SQL Server automatically truncates the log once transactions are committed to the data file, and become inactive. Therefore there is no requirement for VEEAM to truncate the transaction logs of databases using the SIMPLE recovery model either.

There really is no valid reason to enable the VEEAM AAIP Truncate Log option. It attempts to solve an issue that should not be occurring in the first place, glossing over the real issue of a lack of understanding of database recovery models and transaction log behaviour.

My recommendation to clients is to continue using VEEAM and AAIP, but to disable the Truncate Log option. All databases using the FULL recovery model should have native SQL Server FULL and Transaction Log backups scheduled regularly, as appropriate for the RPO that you are trying to achieve. I don’t see any issues with using VEEAM as a single backup and recovery solution for databases using the SIMPLE recovery model.

I see great value in utilising image based backup and recovery solutions like VEEAM, however it is always important for all those responsible for protecting their enterprise’s data assets to understand the technologies they are implementing and how to configure them correctly.

The good news is that AAIP for SQL in VEEAM Version 8 is significantly improved, allowing transaction log backups to be scheduled from within the VEEAM AAIP interface.

Cheers

Phil
@RoarSQL

Advertisements

2 Comments

  1. Hi Phil, have you encountered the issues associated with the stun affect of Veeam (actually its how VMware processes snapshots)? This is one my main dislikes of using products like Veeam that hooks into the VMware snapshot processing, on a busy SQL Server the stun affect can cause timeouts that play havoc with application operations.

    • Hi Rob, thanks for your contribution. Fortunately I haven’t had to troubleshoot the stun effect in a production environment. But from what I understand, this occurs when a VM is paused while a snapshot delta is being written. It can become a significant performance issue when the snapshot is being created at the same time as there is high write activity on the VM. So yes I agree with you that taking snapshots of VMs hosting highly transactional SQL Server workloads is probably not a very good idea.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s