This Blog is part 1 of a 2 blog series concerning backing up your SQL Server databases to Azure Cloud Storage. This first blog will provide a summary of the options currently available to you.
The reason to leverage Azure for your SQL Server backups is obvious. It is a far better alternative to tape backups. It eliminates the need for offsite transportation and makes high availability of the backups easier to obtain. In addition to this there is no hardware management overhead. Other things that should be factored are the cost (only pay for what you use) and the increased accessibility of the backups themselves.
When 2016 is released, there will be 3 supported methods of storing SQL Server backups in Azure Blob storage. The first two below can be done from on premise SQL Server instances but the third requires the instance to be running in an Azure VM:
Backup to URL
Done in the same way as backups have been done before to disk (or tape). This became available in SQL Server 2012 SP1 CU2. From 2016 onwards you will be able to use block blobs instead of page blobs. This method can be leveraged using the SSIS backup task.
SQL Server Managed Backups
A way to let SQL Server manage backups, negating the need to form your own strategies. This feature also allows you to set retention periods and works at a database level or at instance level.
Snapshot Backups for Database Files
Coming with SQL Server 2016 and provides near instantaneous backups and restores with point in time restore. The database files themselves need to be directly in Azure Storage as well as the instance being in an Azure VM.
Here are a few positives and negatives about each option:
Backup to URL
- + Can be done during an SSIS load
- + Can be executed whenever you wish
- – Currently no way to maintain the backups from within SSIS natively
SQL Server Managed Backups
- + Fully managed by SQL Server
- + Can have custom schedules
- + Maintains backups (retention period)
- – Always invoked as part of a schedule or under certain conditions
Snapshot Backups
- + Very fast
- – Requires database files to be situated in Azure Blobs
- – Requires instance to be on an Azure VM – not an on-prem solution
Your options I will explore in parts 2 . However I will not go into more detail about database file snapshot backups as I will stick to on premise solutions.