Author – Amit Singh, Cloud Engineer
Business continuity and disaster recovery (BCDR or BC/DR) is a set of processes and techniques used to help an organization recover from a disaster and continue or resume routine business operations. It is a broad term that combines the roles and functions of IT and business in the aftermath of a disaster
Azure SQL Database offers the following capabilities for recovering from an outage:
- Active Geo-replication
- Auto Failover-Groups
- Geo-Restore
- Zone-redundant databases
Active Geo-Replication: –
Active Geo-replication is Azure SQL Database feature that allows you to create readable secondary databases of individual databases on a SQL Database server in the same or different data centre (region).
Active geo-replication is designed as a business continuity solution that allows the application to perform quick disaster recovery of individual databases in case of a regional disaster or large-scale outage. If geo-replication is enabled, the application can initiate failover to a secondary database in a different Azure region. Up to four secondaries are supported in the same or different regions, and the secondaries can also be used for read-only access queries. The failover must be initiated manually by the application or the user. After failover, the new primary has a different connection end point. The following diagram illustrates a typical configuration of a geo-redundant cloud application using Active geo-replication.
If for any reason your primary database fails, or simply needs to be taken offline, you can initiate failover to any of your secondary databases. When failover is activated to one of the secondary databases, all other secondaries are automatically linked to the new primary.
Active geo-replication is not supported by managed instance. For geographic failover of managed instances, use Auto Failure Groups.
You can manage replication and failover of an individual database or a set of databases on a server or in an elastic pool using active geo-replication.
Active geo-replication leverages the Always On technology of SQL Server to asynchronously replicate committed transactions on the primary database to a secondary database using snapshot isolation.
Benefits of Active Geo-Replication
- One of the primary benefits of Active Geo-Replication is that it provides a database-level disaster recovery solution. Using Active Geo-Replication, you can configure a user database in the Premium service tier to replicate transactions to databases on different Microsoft Azure SQL Database servers within the same or different regions. Cross-region redundancy enables applications to recover from a permanent loss of a datacenter caused by natural disasters, catastrophic human errors, or malicious acts.
- Another key benefit is that the online secondary databases are readable. Therefore, an online secondary can act as a load balancer for read workloads such as reporting. While you can create an online secondary in a different region for disaster recovery, you could also have an online secondary in the same region on a different server. Both online secondary databases can be used to balance read only workloads serving clients distributed across several regions.
Fig 1.1
The Active Geo-Replication feature provides the following essential capabilities:
- Automatic Asynchronous Replication
- Multiple online secondary databases
- Readable online secondary databases
- User-controlled termination for failover
Auto Failover groups.
Auto-failover group uses the same underlying technology as geo-replication. The following are some of the differences between auto-failover groups and active geo-replication.
Auto-failover groups is a SQL Database feature that allows you to manage replication and failover of a group of databases on a SQL Database server or all databases in a Managed Instance to another region (currently in public preview for Managed Instance). It uses the same underlying technology as active geo-replication.
Fig 1.2
- When you are using auto-failover groups with automatic failover policy, any outage that impacts one or several of the databases in the group results in automatic failover. Typically these are incidents that cannot be self-mitigated by the built-in automatic high availability operations. The examples of failover triggers include an incident caused by a SQL tenant ring or control ring being down due to an OS kernel memory leak on several compute nodes, or an incident caused by one or more tenant rings being down because a wrong network cable was cut during routine hardware decommissioning.
- In addition, auto-failover groups provide read-write and read-only listener end-points that remain unchanged during failovers.
- Whether you use manual or automatic failover activation, failover switches all secondary databases in the group to primary. After the database failover is completed, the DNS record is automatically updated to redirect the endpoints to the new region. For the specific RPO and RTO data.
- When you are using auto-failover groups with automatic failover policy, any outage that impacts databases in the SQL Database server or managed instance results in automatic failover. You can manage auto-failover group using:
- Azure Portal
- Azure CLI
- PowerShell
- Rest API
- After failover, ensure the authentication requirements for your server and database are configured on the new primary.
- Geo-replication replicates a single database to the secondary whereas auto-failover groups replicate a group of databases that are added to the failover group
- Auto-failover supports managed instances whereas geo-replication does not
- Both manual and auto-failover are available in auto-failover groups whereas only manual failover is possible in Azure SQL active geo-replication
- Auto-failover supports only one secondary server. If you need multiple secondary databases, consider using active geo-replication.
Geo-restore:-
- Geo-restore enables you to restore a SQL database from the most recent daily backup, and is automatically enabled for all service tiers at no extra cost. Geo-restore uses a geo-redundant backup as its source and can be used to recover a database even if the database or datacenter is inaccessible due to an outage.
- Geo-restore provides the ability to restore a database from a geo-redundant backup to create a new database. The database can be created on any server in any Azure region. Because it uses a geo-redundant backup as its source it can be used to recover a database even if the database is inaccessible due to an outage. Geo-restore is automatically enabled for all service tiers at no extra cost.
- Geo-restore uses the same technology as point in time restore with one important difference. It restores the database from a copy of the most recent daily backup in geo-replicated blob storage.
- For each active database, the service maintains a backup chain that includes a weekly full backup, multiple daily differential backups, and transaction logs saved every 5 minutes. These blobs are geo-replicated this guarantees that daily backups are available even after a massive failure in the primary region.
Fig 1.3
Zone-redundant databases:-
- Zone-redundant SQL single databases and elastic pools are now generally available in select regions.
- The zone-redundant configuration is available to SQL single databases and elastic pools in the Premium service tier at no extra cost. Enabling this option results in moving individual database replicas to different availability zones in the same region. It makes databases and elastic pools resilient to a much larger set of failures, including catastrophic datacentre outages. The supported regions now include France Central and Central US.
- Azure SQL Database Premium tier supports multiple redundant replicas for each database that are automatically provisioned in the same datacenter within a region.
- This design leverages the SQL Server AlwaysON technology and provides resilience to server failures with 99.99% availability SLA and RPO=0. With the introduction of Azure Availability Zones
- SQL Database now offers built-in support of Availability Zones in its Premium service tier. By placing individual database replicas in different availability zones
- To take advantage of this capability, you simply select the zone redundant option during database or elastic pool creation. You can also enable it for existing databases or pools. Once the zone redundant option is enabled, Azure SQL Database will automatically reconfigure the database or pool without any downtime.
Interested in Microsoft Azure, Let’s CONNECT!
