As a database administrator, I am very conscious of how data is being backed up. Up until SQL Server 2016, you were limited to backing up your databases to a physical device. That device could be a tape, local disk, or network storage. After successfully creating a backup, if you’re doing it right, the backups then had to be moved off-site for disaster recovery purposes.
Today, with SQL Server 2016 or higher, you can now backup directly to a URL. This URL is an endpoint into a cloud storage account, in this case, for Microsoft Azure. Furthermore, with Azure Availability Zones now available, certain regions give you extra redundancy and will be replicated to another region for a minimal cost. Not only does this solution offer you the ability to back up your databases off-site, but you get the added benefit of having them replicated for you.
Azure storage that can be utilized for backups comes in two different flavors, block blobs or page blobs along with three different tiers. These tiers are hot, cool, and archive. I’ll explain more about the tiers later. Microsoft recommends using block blobs for backups rather than page blobs simply because it is more secure, it’s cheaper, and you can get better performance.
Block blob storage is used for objects like files. You can think of this storage like looking at files on your local operating system. Since we are using this storage for backup purposes, this is a good choice. The maximum size for a single block blob is ~4.75TB.
Page blob storage is for random read/write storage like virtual hard drives. Azure virtual machines use page blob storage for disks under the hood. The I/O for these disks are random so page blogs work well for them. The maximum size for a single page blog is 8TB.
Microsoft has a good article that explains the differences in storage types in further detail.
As I mentioned, both block and page blobs are available in three different tiers for general availability, namely Hot, Cool, Archive. There is a fourth tier, Premium, but it is in Preview at the moment. You can use these tiers to help reduce costs (the Archive is the cheapest) and improve performance. Hot is going to perform better than the other two tiers however it is also going to be more expensive. Here’s a breakdown of the storage that is currently available:
- Hot – Storage that is optimized for data that is accessed frequently. You could put today’s backup of your databases here so that in the event of a restore, it would be easily accessible.
- Cool – Optimized for data that is kept for at least 30 days and is access infrequently. As the age of the backup file increases, you could move them from Hot to Cool storage, saving on costs.
- Note: You can incur a penalty if data is removed from cool storage prior to 30 days
- Archive – Data is rarely accessed and stored for a minimum of 180 days. This storge would be used for long-term archival of backups.
- Note: With archive storage, the latency in data retrieve will be higher than Hot/Cool. Like cool storage, you can incur a penalty if data is removed prior to 180 days.
- Premium – Still in Preview, this storage provides high performance for data that is accessed often. You’ll get better performance along with a higher cost.
Microsoft provides a good comparison chart. Click on the image below to go to the documentation
In a nutshell, if you do not plan on restoring the data often from Azure storage, the Cool storage offers a good solution to allow you to back up things off-site as well as for a minimal cost. As an example, I priced out 5TB of storage for a client, including zone redundant storage, for less than $100/month. That is a cheap cost, in my opinion, to get their backups off-site and redundant for disaster recovery.
You can use the Azure Pricing Calculator to estimate the monthly cost depending on regions, tiers, and the amount of storage space you want. Once you have chosen the appropriate options for your needs, you can start to use it to back up your databases. I’ll show how to do this in an upcoming post.
© 2018, John Morehouse. All rights reserved.