Altaro Application-consistent processing vs. Native SQL backups

Caution

Never use the SQL native backup together with the snapshot-based backup of Altaro!

Snapshot-based backups are created by a VM backup software, in this case Altaro, which makes use of "Application-consistent" processing. SQL will create that backup as a full backup so all subsequent differentials or log backups will be linked to a full which is not available to SQL, but only to the backup application which created it. 

Option 1: Altaro Application-Consistent processing

Using the application consistent processing of Altaro, you'll have a full, consistent backup created every time your normal VM backup is running. Transaction logs are truncated. If you choose this use-case, DO NOT enable SQL native backups!

Pro: you can enable a secondary target for your backups, e.g. a cloud repository. In case of a disaster recovery situation, it is vital to have such a backup.

Contra: there's no granularity of the backups. It is done as often as you run your normal VM backups.

Configuration

Log in to CMC and set up a Backup Settings Template (click here to see how to do this)

When you are at the step where you need to specify advanced settings, tick in the "Application-Consistent" option. And click Save.

Assign the template as you normally would to the virtual machines where you need Application-consistent processing.

More information here: https://help.altaro.com/support/solutions/articles/43000486447 

Option 2: SQL native backups

In this case, DO NOT enable application-consistent processing on the Altaro 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

What happens if you enable both?

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.

What happens if a snapshot backup is made?

Let's say we have the state depicted in the last screenshot with 1 full, 1 differential and 1 log backup. Now, I set up Altaro to create backups using Application-consistent processing.

A backup is taken of the whole VM:

After taking a backup, the automatic processing of SQL's native backups will run and will create another differential and log backup after a pre-set amount of time.

It's already visible that something is out of place, because we don't see a full backup in the chain:

If we try the restore, we'll be thrown an error.

This is because SQL knows that a full backup has been made when the snapshot-based VM backup was created by Altaro. However, SQL does not have access to the full backup created by Altaro, so the differential and the log backup which are built on the full created by Altaro are not usable anymore. They cannot be restored.

Same happens if we use the Simple recovery model.

With simple we can have full and differential backups:

But if an Altaro backup is created in the chain, it will break the chain making any subsequent differentials unusable.

Conclusion

NEVER use Altaro Application-consistent backups together with SQL native backups.

Use one or the other depending on your needs. If you need a cloud backup of the SQL backups, use Altaro. If you need to have backups every hour and a local backup is enough for you, create native backups of your SQL server.