High Availability and Disaster Recovery for Azure SQL Databases

Author by Concurrency Blog

Azure SQL Database is Microsoft's PaaS database offering. When you create an Azure SQL Database, you get to choose:
  • The server name and location
  • v11 or v12 (this determines the feature set)
  • Pricing Tier
  • Collation
What you don't get to choose is High Availability or Disaster Recovery strategies as you would on-premises, such as failover clustering or log shipping. Why is this? Microsoft has baked it right into the product - this is part of what you are selecting in the "Pricing Tier" option. The pricing tiers (as of April 2015) are Basic, Standard, and Premium. HA is part of each data center (or region). The DR options vary across tiers. 

High Availability 

Azure SQL Databases automatically have more than one copy created. There will be at least three copies of your data, and at least two of those are synchronous. The hardware they are on is on completely physically separate sub-systems. This way, if the hardware fails, your database will automatically and seamlessly fail over to the synchronous copy.  

Disaster Recovery 

There are multiple types of "disasters" that can befall a database. Some are user-generated, such as deleting all the rows in a table. There can also be a true disaster, and an entire region can become unavailable. Azure SQL Database has multiple ways to protect your data. 

Backups  

The foundation of DR is backups of the data, and the ability to restore them. Azure SQL Databases are backed up on a regular basis. A full backup is taken once per week, a differential backup once per day, and a log backup every five minutes. Unfortunately, you don't have the ability to configure this any more granularly, and you can't perform an ad-hoc backup.

Point in time Restore

This option allows you to revert back in time to a specific point. How specific? You can restore to any one of your five-minute-increment backups - for example, 7:05 am or 11:45 PM. The length of time backups are retained is controlled by the pricing tier.
  • Basic - 7 days
  • Standard - 14 days
  • Premium - 35 days
When you perform a restore, you'll get a new database on the same server. For example, I have a database named v12test. When I restored it to April 19, the database v12test_2015-04-19T12-00Z was created. This ensures that no necessary data is overwritten or lost. Point in time restores are a great way to recover when there is a user issue, such as a DELETE statement run without a WHERE clause.

Disaster Recovery

What happens if the issue isn't with a single piece of hardware under one of your databases, or it's not a user error? What if the entire region becomes unavailable? Your DR options are controlled by the pricing tier you select.
  • Basic - Geo-restore, restore to any Azure region; no secondaries
  • Standard - Standard geo-replication; offline secondary
  • Premium - Active geo-replication; up to 4 online, readable secondaries
What do those options include?

Geo-restore

When you create your database server, you decide which geographical region you want it in. What would you do if that region had a catastrophe and was no longer available? Behind the scenes, your backups are stored on geo-redundant storage. You'd perform a geo-restore, which means you would select a different region and restore the database to it. With this option, your data could be up to one hour behind.

Standard Geo-Replication

Here, a copy of your data is being constantly, asynchronously written to a secondary database on a server in another region. It's a non-readable copy, though, so you can't use it for queries or reporting. In the event of a disaster, however, you can fail over to the secondary.

Active Geo-Replication

Similar to Standard Geo-replication, your data is being asynchronously written - but here, it's on up to four secondary servers in different regions. These secondaries can be readable. For a premium price, you have the most copies of your data, and they can be used at any time. You can also fail over to a secondary in the event of disaster, although you then need to reconfigure the setup.

Your Data is Protected

If you've hesitated to move data into Azure because of a lack of HA and DR options, don't let that stop you any longer! Your data will be highly available and recoverable.
Author

Concurrency Blog

The latest about Concurrency