Veeam SQL Transaction Log Backups

Veeam Backup & Replication provides powerful features for backing up virtual environments.  One of these features is the ability to provide application aware backups.  Application aware backups allow organizations to maintain transactional consistency in backups of virtual machines housing applications such as Microsoft SQL Server.  In this post we will take a look at Veeam Backup & Replication application aware backups of Microsoft SQL and the various settings as they relate to transaction logs, etc.

Why Application Aware backups are important

Using “application aware” backups are extremely important when it comes to making sure applications are transactionally consistent.  When turning on application aware settings for a virtual machine backup job, uncommitted operations that reside in memory are flushed from memory and written to disk.  This happens prior to the backup being taken so that all transactions are committed before the backup runs.

As mentioned, this ensures that backups are in a consistent state with is a tremendous benefit.  Additionally this keeps an administrators from having to run another process to bring the application such as a Microsoft SQL Server up to a transactionally consistent state.  This process can be different for each application, however, in the case of SQL Server, this generally involves replaying logs.  In a true DR situation, it is a much better position to be in to have your backups already in a consistent state for application data.

Veeam Application Aware Settings

To create or alter a job to be “application aware”, we are concerned with the settings found on the Guest Processing configuration screen.  Here we find the checkbox Enable application-aware processing.  When we flag a job to be application aware, we also need to have guest OS credentials that have permissions to access the operating system as well as the application data.  If you don’t already have credentials setup, click the Add button.  If you have already set credentials, you can click the Test Now button.  Otherwise to drill further into the configuration, click Applications button.

When we click the Applications button, we are presented with the Application-Aware Processing Options.  Click the Edit button to set our options.

On the General tab, we generally will want to leave the defaults here.  Typically, we want to have a successful processing of application data before we succeed the job.  This is recommended.  On the Transaction logs settings, we have two settings here:

• Process transaction logs with this job (recommended) – This is typically what we want and allows Veeam to interact with the transaction logs.
• Perform copy only (lets another application use logs) – This option allows for another application to use the transaction logs. Typically, if we have another application that is doing our transaction log backups, we would only want Veeam to perform a copy only job.

On the SQL tab, this is the tab we especially want to pay attention to as here we can fine tune our interaction with the SQL Server transaction logs.  The first radio button, Truncate logs (prevents logs from growing forever) selection allows for Veeam Backup & Replication runtime process to truncate the logs after the backup is complete.

If we do not want to truncate logs, we can select the Do not truncate logs (requires simple recovery model). However, the third option is especially interesting as far as application items are concerned.  We can select Backup logs periodically (backed up logs are truncated).

This option works in tandem but also independently from the VM image level job.  We can for instance schedule the VM image level backup job for a daily operation and then schedule the transaction log backup for “every X number of minutes”.  This allows us to backup the SQL transaction logs much more frequently than our VM image level backup.

We also have options on how long we want to Retain log backups.  We can select:

• Until the corresponding image-level backup is deleted – This option applies our retention policy of the image level backup to the transaction log backups
• Keep only last X days of log backups – This setting defaults to 15 days. This option cannot be set longer than the image level backup retention period.

  

In the logs of the transaction log backup in Veeam, you can see the statistics for the log backup

Additionally, we see in the other part of the job window, we can see the number of excluded databases which includes the databases that are set to simple mode.

Below we see the beautiful thing about having the transaction logs shipped to our backup server.  When we restore data to our SQL server, we now have the ability to Restore to a specific point in time (requires transaction log backups).  We can set the slider where we want it almost like a DVR.  Notice the first time (9:52 A.M.) is when our image level VM backup took place.  And the times after that are transaction log driven.

Final Thoughts

Making sure to perform virtual machine backups with application aware processing turned on allows for consistent application data in each backup.  Additionally, Veeam SQL Transaction Log Backups allow transaction log shipping to have even more granular restore points past the VM image level backup.  This is a great feature to keep in mind when backing up virtual machines with Veeam Backup & Replication.  Never backup SQL Server or other application servers without using application aware processing.