Configuring Disaster Recovery for Azure SQL

Disaster recovery (DR) is the set of mechanisms you put in place to ensure your database survives a catastrophic failure. A 'catastrophic failure' is not a crashed hard drive — that is a hardware fault. A disaster is something that takes out an entire Azure region: a major earthquake, a flood, a power grid failure, or even a large-scale cyberattack. When a whole region goes down, every database in that region becomes unavailable. Your job is to have a copy of your data running somewhere else, ready to serve traffic immediately or be brought online quickly.

Azure SQL Database and SQL Managed Instance both offer several ways to configure this. The first and most common is geo-redundant backup storage. When you create a database, Azure automatically backs it up every few minutes, and copies those backups to a paired region hundreds of kilometres away. If the primary region fails, you can restore from those backups — this is called geo-restore. It is free (the backups are included in your service cost), but it has a downside: the restore can take hours, especially if the disaster is widespread and many customers are trying to restore at once. The data loss window is also up to one hour, meaning you could lose the last hour of transactions.

For businesses that cannot afford an hour of lost data or hours of downtime, there is active geo-replication. This creates a second, readable copy of your database in a different Azure region. Your application can read from the secondary copy at any time, and if the primary fails, you perform a manual failover to promote the secondary to become the new primary. The failover happens within seconds. The catch is that you pay for the secondary database separately — it is a full, running database, not just a backup. The replication is asynchronous, which means changes on the primary are sent to the secondary with a small delay. If a disaster happens during that delay, a few transactions might be lost.

The next level is failover groups. A failover group is a management layer on top of active geo-replication. It lets you group one or more databases and define a single endpoint — a connection string — that your application always uses. The failover group handles the switching of endpoints automatically. You can configure automatic failover policies: if Azure detects that the primary region is down for a certain period (your grace period, typically one hour), it triggers the failover automatically. The application does not need to know which database is primary; it just connects to the same endpoint. Failover groups also support readable secondaries, so you can offload read traffic to the secondary database to distribute load.

For SQL Managed Instance, the equivalent is auto-failover groups. They work the same way but are designed for the managed instance architecture. You create a secondary instance in a different region, and the group manages replication and failover between the two. The key difference is that managed instances require a VPN gateway or Azure ExpressRoute connection between the two regions for replication traffic to flow.

There is also a cheaper, less capable option called a zone-redundant configuration. This does not protect against a full region failure, only against a failure of a single Azure data centre within that region. It replicates data across multiple 'availability zones' inside the same region. If a data centre burns down, your database stays up. But if the entire region suffers a disaster, zone redundancy does not help.

Each option makes a trade-off between the Recovery Point Objective (RPO) and the Recovery Time Objective (RTO). RPO is how much data you are willing to lose — measured in time. If the RPO is 5 seconds, you can lose at most 5 seconds of new data. RTO is how long it takes to get the database back online. Geo-restore has an RTO of hours and an RPO of up to one hour. Failover groups can achieve an RTO of under a minute and an RPO of a few seconds. You pick the combination that matches your business's tolerance for data loss and downtime.

All of these configurations are configured through the Azure portal, Azure CLI, or PowerShell. You must also consider networking: for failover groups, you need to configure firewall rules and virtual network settings so that the secondary database accepts connections from the same clients. And you must test your failover process regularly. There is no point having a DR system if nobody has ever practised failing over, or if the application's connection string is hardcoded to the old primary server.

First, you go to the Azure portal and locate your PayFast database server in West Europe. You open the failover groups blade and create a new failover group. You give it a name like 'payfast-dr-group'. You select the secondary server: you need a server already created in North Europe. If one does not exist, you create it. The portal asks you to choose a failover policy. You choose 'Automatic' and set a grace period of 1 hour. Why 1 hour? Because if there is a brief connectivity blip, you do not want automatic failover to trigger unnecessarily. If West Europe truly is down for an hour, the failover will happen automatically.

Next, you add the PayFast database to the failover group. The portal will start the initial seeding — copying the full database from West Europe to North Europe. This can take hours for large databases, so you schedule it during low traffic. While seeding is running, the secondary database is not usable. Once seeding finishes, continuous asynchronous replication begins. Every few seconds, changes on the primary are sent to the secondary. The RPO will typically be under 10 seconds, well within the 30-second business requirement.

You then update all application connection strings to point to the failover group listener endpoint — something like 'payfast-dr-group.database.windows.net'. The listener is a DNS name that always resolves to the current primary database. If a failover occurs, Azure updates the DNS record so the listener points to the new primary in Ireland. The application does not need to change its connection string.

Finally, you simulate a disaster. You trigger a manual failover during a maintenance window to test the RTO. You measure: from the moment you initiate the failover, how long until the application can successfully connect to the new primary and process a test payment? You find it takes 45 seconds. The RTO of 5 minutes is easily met. You repeat the test every quarter. The operations team documents the test results, and you sleep better knowing that the business is protected. This is the real-world workflow for configuring disaster recovery for Azure SQL.