There are many elements and offerings for implementing high availability disaster recovery with Azure SQL, but broadly speaking they fall into two categories. Firstly, there is the fully synchronized always-on availability groups functionality that was originally developed with SQL Server and has been migrated to the cloud with the premium and business-critical service tiers. Secondly, we have the Azure SQL and Managed Instance DR solutions such as Active Geo-replication and Failover groups.

If you want a solution that meets no compromise RTO and RPO goals, that will involve some form of the always-on availability groups, whether that’s on-premise, on an Azure virtual machine, or using premium service tiers. Having said that the reasonably new hyperscale architecture shows an exciting departure from the traditional cluster technology, by separating functionality out into storage engines that in turn access separately stored files where backups make use of file snapshots that backup the data incredibly quickly.

In any case, there is no getting away from distance and physics. Having fully synchronized replication across Azure regions or vast distances is always going to be a challenge. This is borne out by the fact that as we move down the service tiers what passes for automatic failover is not that helpful when the minimum grace period is 60 minutes.

As the number of data centers increase and regions that support availability zones also increase the cost of redundancy and replication will decrease. It is only a major issue if a complete Azure region goes off-line, and while not an impossible event would be a very rare one. 

By default, database backups are automatically replicated to a paired region, so every service tier has geographic redundancy is built-in at some level. Both Azure SQL and SQL Managed Instances do allow you to replicate data across regions but it is asynchronous, and while the latency can be very low there is no guarantee that data will not be lost in the event of a catastrophic failure.

On the plus side, all of these high availability disaster recovery solutions give you the benefit of read-only replicas that are accessible during normal operation. This allows you to offload read operations like reporting and backups from the primary database, so there may actually be something as a free lunch.

If we were to put all the different technologies onto a high availability disaster recovery spectrum, at one end we would have the low-cost but time and labor expensive plain backup and restore. And at the other end always-on availability group and failover cluster type technology, which is orders of magnitude more expensive, but offering automatic and near-instantaneous recovery. In the middle we have the compromise, both active geo-replication, and failover groups rely on manual intervention in the case of a failure but only a limited amount of data could be potentially lost.

Even though automatic failover is an option, both would require manual intervention to failover in a timely fashion. Failover groups have the advantage of being supported by SQL Managed Instances and application endpoints will not have to be changed as they point to the failover group URL and will automatically connect to the new primary server.

If system downtime is not a major priority but you still want to minimize loss of data in the event of a database outage, you can achieve this with traditional backup and restore. We saw at the most minimal level of automation how you can create scheduled jobs to do full, differential, and transaction log backups with SQL agent jobs. The frequency of backups, especially transaction log backups can help meet a quite stringent recovery point objective.

Depending on database size and compute resources, that is the amount of effort it requires to do backups, full backups can be done weekly or daily. Depending on the full backup schedule differentials can range from once or twice a day to every three or four hours, and again dependent on differential backup frequency and the amount of data change, transaction log backup frequency can range from once every 60 to perhaps once a minute. It really depends on RPO requirements. SQL Server management studio has a useful wizard for conducting a point in time restore using a combination of the aforementioned backups.

In Azure SQL and SQL Managed Instance, database backups are automatically implemented and managed. There is a point in time restore function that hides the complexity of backup selection. The use of active geo-replication or failover groups will go a long way to minimize the amount of data loss in the event of a region-wide failure. 

High availability disaster recovery is a bit like insurance if you want to minimize excess, that is reduce time to recovery and loss of data, you will have to pay higher premiums, so increase service level tier. We have looked at many options, some of which can be combined and others not, but all will meet your requirements once you have determined what they are.

Lectures

Course Introduction - Overview - High Availability and Disaster Recovery Concepts - Hyperscale - Combining On-Premises with the Cloud - Always On Availability Groups - Failover in the Cloud - Database Backups