Database Backup Options for SQL on Azure IaaS

https://mrfoxsql.wordpress.com/2018/01/29/database-backup-options-for-sql-on-azure-iaas/

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+ Azure Backup Server

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.

https://docs.microsoft.com/en-us/azure/backup/backup-introduction-to-azure-backup

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…

 

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

Advertisements

2 thoughts on “Database Backup Options for SQL on Azure IaaS

  1. Kevin3NF 4 February 2018 / 5:48 AM

    Video showing the SQL backups to Azure storage blog steps described above:

    Liked by 1 person

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 )

w

Connecting to %s