Backup Techniques for SQL Server on Azure Virtual Machines

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.

IaaSext

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

opsbackup

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.

thevault.JPG

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.

 

iaasvmex

Head for the backup section.

backupsmain

Configure as you require.

confif

Summary

A decision matrix from Microsoft for you.

matrix.JPG

Advertisements

2 thoughts on “Backup Techniques for SQL Server on Azure Virtual Machines

  1. Pingback: Dew Drop – October 18, 2019 (#3054) | Morning Dew

  2. Pingback: Backing Up SQL Server on Azure VMs – Curated SQL

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s