What Does Your SQL Backup Plan Look Like?

Since SQL Server 2012 SP1 CU2 (released 1/2013), Microsoft has included the ability to backup directly to Azure. And starting with their 2014 version, they have included a Managed Backup capability that offers the simplest way to get started backing up to the cloud. In addition, in 2014 they released an external tool that enables automatic copying of older SQL Server versions’ backups to Azure. However, while these capabilities offer a welcome alternative to on-premises drive and/or tape backups, there are many things to consider and configure in order to create a robust and fully capable SQL Disaster Recovery (DR) solution in Azure. Which is to say, there are always many things to consider and configure in order to create a robust and fully capable SQL DR solution, and the Azure capabilities do not, in most cases, significantly simplify the planning, implementation, and assurance of your DR capabilities. Backing up to the cloud, however, offers a robust alternative to traditional on-premises approaches in that it is immediately geographically isolated, relatively cost efficient, and supports substantial DR advantages such as in-cloud recovery and failover.

The Managed Backup tools are a good place to start looking at both the capabilities and limits of the SQL Server offering. In this version, going from no DR plan to having regular backups in the Azure cloud is a relatively straightforward process (assuming you’re comfortable with all the steps outlined here). However, there are a number of limitations with the 2014 implementation:

• Only User databases can be backed up (!) and they cannot be in Simple recovery model mode.
• The interval/schedule is automatic and not configurable. Convenient, but you get Microsoft’s assumed Recovery Point Objectives (RPO), not yours. In practice, this can leave you with 2-hours of lost data in a DR scenario.
• The total backup size is limited to 1TB.
• Backup retention is limited to 30-days.
• If you are using Log Shipping, you cannot use Managed Backups.
• Only Full and Log backups are taken. No Differentials.

If you have 2016 or later, a few of the above limitations are reduced:

• Supports both System and Simple recover model.
• The schedule can be customized to meet RPO.
• Total backup size is virtually unlimited.
• The other limitations from 2014 remain.

If the limitations for your version of SQL Server prohibit the use of Managed backups, you can still backup to Azure, but may have to build your own scheduling for both backups and retention. In general, prior to 2016, Managed Backups are suitable for only the most basic and casual SQL DR plan. Even with 2016, here are a number of additional things to consider:

How and where will you restore in case of a disaster?

If restoring on-premises, or other non-Azure location, be sure to consider the time it may take to download the backup files. If using Managed Backup, you will need to download the latest full backup, and every log backup taken since then. In SQL 2014, that could mean downloading around 80-files per database. In 2016, this number could be improved by taking a full backup more than weekly, but since differentials are not supported, the options are limited. If differentials are required, Managed Backups are not the solution.

On the other hand, consider the power and flexibility of restoring directly to Azure. If you’ve planned ahead, you could have a server already built and connected to a VPN or other mechanism to provide a reasonable and predictable Recovery Time Objective (RTO). Depending on your needs, you could have related application servers on warm standby as well. Consider that in Azure, while you pay for storage, you do not pay for servers that have been built, but are shut down. Keeping them in this built-but-idle state can have a very attractive run rate for a robust geographically isolated recovery option.

How do you test and validate your backups?

Here, I’d repeat just about everything from the prior section, and then highlight the incredible advantages possible by automating regular DR exercises in Azure using the same virtual servers described above. Though this section has the fewest words, it is the capability that gets me the most fired up about the value proposition of backing up to Azure. Imagine if your RTO promises were based on regularly recurring real tests on the exact infrastructure you’d use in an actual DR scenario.

How are you monitoring Azure backups?

With any of the backup methods mentioned so far, it is possible to build in alerts that will let you know when a backup task fails. However, if the entire backup scheduler fails, no alerts will fire. In addition, if using the Azure tool for versions prior to 2012 SP1 CU2, the scheduler only knows what succeeded to the point of writing the file locally; there is no monitor for the part that moves those files to Azure. In either case, it would be possible to add additional monitoring, using external tools, to verify that the scheduler is running or that the files are getting copied.

However, a better solution is to monitor the Azure file containers to make sure files are appearing, and being completely written at the expected interval. This can be done using Azure automation tools such as the hosted PowerShell capabilities in Azure Automation Services.

Is 30-days retention really enough? What about long-term retention for compliance, audit, and other requirements?

If you don’t use Managed Backups, you’ll have to create your own schedule, and also your own retention/deletion schedule. However, even if you do use Managed Backups, the automatic retention settings are limited to 30-days. In many environments, it is desirable to retain at least a subset (say a monthly full backup) for much longer than 30-days. In fact, most compliance regimes require retaining at least some key data for years. Again, using automation capabilities, this can be accomplished, and monitored in Azure.

The upshot is that backing up to Azure is a great option that offers significant advantages, especially if your DR plan includes restoring to Azure as well. While the newer Managed Backup capabilities are improving, and as of 2016, viable as a baseline for many DR plans, backing up to the cloud does not reduce the overall complexity and careful planning that should go into a responsible DR strategy for most businesses. To learn more about Azure, and SQL managed backups contact Thrive today!