Database Backup Options for SQL on Azure IaaS

[read this post on Mr. Fox SQL blog]

Recently I had a requirement to collate and briefly compare some of the various methods to perform SQL Server backup for databases deployed onto Azure IaaS machines. The purpose was to provide a few options to cater for the different types (OLTP, DW, etc) and sizes (small to big) of databases that could be deployed there.

Up front, I am NOT saying that these are the ONLY options to perform standard SQL backups! I am sure there are others – however – the below are both supported and well documented – which when it comes to something as critical as backups is pretty important.

So the purpose of this blog is to provide a quick and brief list of various SQL backup methods!

DB Backup Options for SQL on Azure IaaS

First up I must confess, I am a SQL DBA from way back! As a result that experience has given me a specific view in how I believe SQL backups should (must?) be managed. Having solid and reliable database backups are critical for business recoverability – as such I personally lean towards any process that enables a DBA to more closely manage and control the process themselves (ah, assuming there is a DBA, of course!).

So yeah, anyway, lets just get onto the list shall we?

Available From SQL Version	Backup Option Notes and References
SQL 2012+ (2012 must be SP1 Cu2)	SQL (DBA) Backup to Azure Storage Blob Using the same methods used to backup to local disk, you can now back up to Azure storage by specifying URL as the backup destination. This backup type is managed by the DBA by performing BACKUP commands to Azure Storage Blob. https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-and-restore-with-microsoft-azure-blob-storage-service https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-use-storage-sql-server-backup-restore
SQL 2014+	SQL Managed Backup to Azure Storage Blob Using SQL Managed Backup to Microsoft Azure, you can create a backup plan by specifying only the retention period and location and allowing SQL Server to determine the schedule, or the DBA can set their schedule using advanced settings. SQL Managed Backup schedules, performs, and maintains the backups. If you allow SQL to determine the backup schedule then it will watch the transaction workload of your database and schedule backups accordingly. The backup can be configured at the DB level or SQL instance level. When configuring at the instance level, any new databases are also backed up automatically. Settings at the database level can be used to override instance level defaults on an individual case. https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-managed-backup-to-microsoft-azure
SQL 2016+	File Snapshot Backups for Database Files in Azure Storage Blob SQL File-snapshot backup uses Azure snapshots to provide nearly instantaneous backups and quicker restores for database files stored specifically in Azure Blob storage. This capability is not available unless the database data & log files are stored in Azure Storage Blob. https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/file-snapshot-backups-for-database-files-in-azure
Any	SQL Native Backups to Local Disk Standard SQL Server native backup processes using BACKUP commands performed to local attached Standard or Premium storage. Then a separate process (not discussed here) backups up or copies the SQL backup files off server. This may sound less exotic than the other options mentioned in this blog post, but hey, nothing wrong with a strong, simple and proven backup process! https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-full-database-backup-sql-server
SQL 2008+	Microsoft Azure Backup Server (MABS) – to Recovery Vault Azure Backup allows you to back up the data for on-premises servers, virtual machines, virtualized workloads, SQL server, SharePoint server, and more. All Azure Backup components can be used to back up data to a Recovery Services vault in Azure. To perform backups of SQL Server on VM’s you must have Microsoft Azure Backup Server (MABS) deployed into your environment and the right components running on the VM’s to be backed up. https://docs.microsoft.com/en-us/azure/backup/backup-introduction-to-azure-backup https://docs.microsoft.com/en-us/azure/backup/backup-azure-sql-mabs https://docs.microsoft.com/en-us/azure/backup/backup-mabs-protection-matrix

And this, a late addition to the SQL Server backup family!

SQL 2012+

SQL Server Backup Direct to Recovery Vault

Extending on the above usage of Microsoft Azure Backup Server (MABS), a new feature has recently come out which allows databases deployed onto SQL Server VM’s to be directly backed up into the Azure Recovery Services Vault – thus bypassing the need to use MABS!

This makes life a bit easier, well, in respect to backups anyway.

https://docs.microsoft.com/en-us/azure/backup/backup-azure-sql-database

In addition to the above, there are also a myriad of other 3rd party backup tools which are available out in the market. Some I’ve used, and others not. For the purpose of keeping this post simple then I wont go into any of these in this blog post!

References

In addition to the above table, there are also a few solid references I can call out here…

SQL Backup and Restore Options Reference – https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-backup-recovery
SQL Backup Strategies for VLDB Databases – https://blogs.msdn.microsoft.com/igorpag/2015/07/28/multi-terabyte-sql-server-database-backup-strategies-for-azure-virtual-machines/

Summary

So there you have it, a pretty simple breakdown of the various SQL database backup options that are available at the Azure IaaS level.

So as usual, and as I always say, please test this out yourself with your own databases as your mileage may vary!

Disclaimer: all content on Mr. Fox SQL blog is subject to the disclaimer found here