In the previous blog post I did a quick overview building a SQL VM (imaged) in Azure. It is now time to clarify some backup techniques because it can get confusing.

At a high level there are 3 techniques.

• Automated backup.
• Azure backup for SQL VM (that’s what MS call it).
• Manual backup, for example backup to URL.

I prefer not setting up manual backups to storage accounts, I have done it, I just find it painful to  setup/support/fix. So my choice would be automated backup vs “Azure backup” for SQL VM. What’s the difference?

Automated Backup

This feature provides an automatic backup service for SQL Server Standard and Enterprise editions running in a VM in Azure. This service is provided by the SQL Server IaaS Agent Extension. (Further details later). opt for version 2 where you can, it can get very clever where with the nature of this feature it does allow backup schedules to be determined based on log growth (you can turn this off if you want). Basically, you need not worry about the backups, just make sure you know how to restore from URL using TSQL.

As mentioned this only works if you have the IaaS extension, confirm this via the portal.

Where do you configure the settings? Well don’t get confused, if like me you would head over to operations > Backup right?

Wrong! This is the Azure backup section which is the other option I was talking about – This uses the recovery services vault.

Azure Backup for SQL VMs.

As mentioned this uses the recovery vault – the GUI looks like the below. This does have some good advantages. I do like the idea of it being a central repository for all your backups, including other workloads that Azure Backup supports forming your single view in Azure. I can see bigger companies utilizing this feature over the automated backup. Based on what I have read you can actually restore from that tool, so no need to write TSQL commands.

For more information see – https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-backup-recovery#azbackup because I am yet to fully test it out.

Settings for Automated Backup

Going back to Automated Backups, where can you tweak the settings? Where is it ? Under SQL Server Configuration > Manage SQL VM.

 

Head for the backup section.

Configure as you require.

Summary

A decision matrix from Microsoft for you.