Veeam Application Aware processing vs. Native SQL Backups

Introduction

Veeam offers various possibilities for taking care of SQL backups. However, some of the below options are mutually exclusive meaning that if you configure e.g. both Option 1 and 2, SQL and Veeam backups will interfere with each other, rendering the database backups completely unusable. Hence configuring these settings require planning and caution.

Option 1: Use ONLY Veeam's application aware processing

In this case, every time a server backup is run, SQL is also backed up in a special way where transaction logs are truncated. These backups are considered full SQL backups. If you choose this use-case, DO NOT enable SQL native backups!

Pro: you can configure a secondary backup repository e.g. a cloud repo as opposed to storing your database backups only locally. (This can come in handy in case of a disaster recovery operation needs to take place.)

Contra: this SQL backup type runs together with the VM backup. Though you can restore ONLY SQL from such a backup, VM backups usually take place only once a day which might not meet the requirements of your business continuity plan. E.g. if a frequent log backup is needed. 

Configuration

To see how to configure a job in Veeam Backup and Replication, click here.

At the Guest Processing step of a backup job configuration, tick in Enable application-aware processing.

Give the credentials at the Guest OS credentials. (This is usually an administrator account of the VM that you are backup up - it needs to have sysadmin access on the SQL server.)

Edit the VM which contains the SQL server and choose your settings.

The default is:

• Require successful processing (we strongly recommend this option)
• Process transaction logs
• And on the SQL tab: Truncate logs

In case your job contains more VMs but only one (or a few) needs application aware processing, edit the given VM (which doesn't need application aware processing), and on the General tab click Disable application processing). Thus you put the VM into "Copy only" mode, meaning that the files of the VM will only be copied as they are during a normal VM backup process.

Option 2: Use ONLY native SQL backup

In this case, DO NOT enable application aware guest processing on the Veeam job. Only configure the SQL native backup.

Pro: you can configure as frequent log backups as you need (even 15 minute intervals).

Contra: you cannot configure a secondary target location (e.g. a cloud repo).

Configuration

Please, see this article to configure SQL native backup.

What happens if you enable both Option 1 and Option 2?

Backup chains

There are 3 types of SQL backups if you are backing up your SQL server natively from the SQL Server Management studio: full, differential and transaction log backups.

If you use the simple recovery model, you won't have transaction log backups but you still can set up differential and full backups.

Differentials and log backups are always built on the last full backup + ALL the differentials and log backups that were created between the last full and the differential that you want to restore.

For example: In the screenshot, I want to restore the state that was backed up in the last transaction log. I cannot ONLY restore that one log backup. All three backups (1 full + 1 differential) need to be restored so that the log backup is usable.

If you tick in the last log backup, the other two backups will be selected automatically.

Interference in the backup chains

Veeam will take a full backup every time the backup job runs. SQL will detect that a full backup was run so it will continue that backup chain, building all subsequent differential and log backups on that Veeam full backup. BUT it will have no access to the Veeam full backup. So if you try to restore that backup chain, your full backup will be missing so SQL won't be able to restore the subsequent differentials and log backups.

The restore will fail.

You may still be able to restore the database from your VM backups, however you'll lose the granularity of your differentials and log backups.

Option 3: Hybrid mode - use both Veeam and SQL backup with special settings

Veeam is extremely flexible in terms of possible settings. If you need the granularity and simplicity of restore of your local log backups AND need a daily full backup on e.g. a secondary (cloud) target, it's possible to have both.

Pro: you can set up the native SQL backups on your SQL server. This means that you will have a more flexible scheduling possibility, while you can still have a full backup transferred to a secondary location every time the Veeam VM backup is run without interfering each other.

Contra: the cloud backup will reflect a daily status (not as granular). Perfect for disaster recovery scenarios, but it depends on your company's policies and needs.

Configuration

In the Guest Processing tab during the job configuration, click the virtual machine that you need to configure with application aware processing. 

Click Edit...

On the General tab, under Transaction logs, choose Perform copy only (lets another application use logs)

Click OK and run through the rest of the wizard.

Even after a Veeam VM backup, you will still see and have your complete backup chain available in SQL:

Option 4: Use Veeam Transaction Log Backups

With this option, (just as with Option 1), DO NOT enable SQL native backups. They will interfere with Veeam's backups.

Pro: you will have great granularity in terms of scheduling (and available restore points) not only locally, but also on a possible secondary target.

Contra: restores require serious preparations. You need to have a Staging SQL server to make use of your log backups.

Configuration

In the Guest Processing tab during the job configuration, click the virtual machine that you need to configure with application aware processing. 

Click Edit...

On the General tab, leave the following defaults:

• Require successful processing (we strongly recommend this option)
• Process transaction logs

On the SQL tab, choose Backup logs periodically (backed up logs will be truncated) and choose the frequency you need (default is 15 minutes).

Choose the settings of how long to retain log files based on your company's policies.

Click OK and run through the rest of the wizard.

Why to turn off native SQL backup with Option 4?

If you enable native SQL backup while letting Veeam do the log backups too, you'll see both your own native backup chain and the Veeam Backup of your logs in the restore window. However, this is misleading. Your backup chain is no longer usable.

If you try to restore such a full backup + a Veeam log backup (or multiple log backups), the full backup might work, because full backups contain every bit of information a restore needs. However, you will not be able to "build" the Veeam log backups on top of that: you'll get a "The system cannot find the file specified" error.

If you check the specified path, it will be empty.

Veeam knowledge base contains the explanation:

Veeam Agent for Microsoft Windows copies transaction log files from the log archive destination (set by the Microsoft SQL Server administrator) to a temporary folder on the Veeam Agent computer file system.

...

Veeam Agent for Microsoft Windows transports transaction log backup copies from the temporary folder to the backup location and saves them as VLB files. As soon as copies of transaction log backups are saved to the backup location, transaction log backups in the temporary folder on the Veeam Agent computer are removed.

https://helpcenter.veeam.com/docs/agentforwindows/userguide/sql_backup_hiw.html?ver=50 

If you check your backup folder, you'll see the VLB files.

These files can be used through the restore process from Veeam Backup of Replication, Application Items restore, where you can specify a point in time which you'd like to restore - based on the backed up transaction logs.

However, this feature requires the use of a staging SQL server.

 

More about SQL backups and Veeam here.