Plan and configure a high availability and disaster recovery environment

Planning an HA/DR strategy

• High availability (HA) keeps the service running through local failures (node, rack, zone). Disaster recovery (DR) restores service after a larger loss (region). Backup protects against data damage, accidental deletes and corruption, which replication faithfully copies.
• RPO (recovery point objective): maximum acceptable data loss, measured in time. RTO (recovery time objective): maximum acceptable downtime.
• SLA figures: SQL Database and MI offer up to 99.99%, with zone-redundant configurations improving resilience to zone failure. Check the SLA documentation for exact tiers.

Built-in HA by platform

Typical choices by RPO/RTO

Hybrid and Azure-specific options

• Hybrid DR: use Azure as a DR site for on-premises SQL Server: distributed or stretched AG to Azure VMs, log shipping to Azure, Managed Instance link (on-premises SQL Server 2016+ to MI, includes failover to MI, with 2022+ failing back), backup to URL and restore, and Azure Site Recovery for VM-level recovery (ASR is not transaction-aware: not recommended as the sole database DR). Connectivity: VPN/ExpressRoute, latency matters for synchronous replicas.
• Azure-specific: availability zones and sets, paired regions, geo-redundant backup storage, failover groups, geo-replication.
• Plan a testing procedure: define scenarios (planned failover, forced failover, region loss, restore from backup, corruption), a runbook, success criteria (measured RTO/RPO), schedule drills, include applications (connection strings, DNS, firewall, logins, jobs, certificates), test failback, document results. Use planned failover (no data loss) for drills and forced failover only for real outages. Test restores regularly because an untested backup isn’t a backup.

Backup and restore

Automatic backups in PaaS

SQL Database and MI take backups automatically: full weekly, differential every 12 or 24 hours, transaction log about every 10 minutes (frequency subject to activity). Hyperscale uses storage snapshots (near-instant, no full/diff chain). No manual backup is needed or allowed in SQL Database (no BACKUP DATABASE).

• Backup storage redundancy: LRS, ZRS, GRS (default, read-access geo-redundant, RA-GRS), GZRS. Choose at creation; changing later may require a new database/copy except where supported. GRS backups allow geo-restore to any region (RPO about 1 hour).
• PITR retention: 1-35 days (default 7). Restoring creates a new database (it doesn’t overwrite). On MI you can restore to the same or another instance.
• Deleted database restore: possible within retention, to the deletion time or earlier.

Restore options in SQL Database

After restore, rename or swap: restore as a new name, then rename or redirect the app. Restore time depends on size and log activity (Hyperscale is size-independent).

Long-term retention (LTR)

LTR copies full backups to separate Blob storage up to 10 years. Policy parameters: W (weekly), M (monthly), Y (yearly) retention and week of year for yearly. Example: --weekly-retention P12W --monthly-retention P12M --yearly-retention P5Y --week-of-year 1. LTR works on SQL Database, MI and Hyperscale (newer). Restore creates a new database. LTR backups can be copied across regions with GRS-type storage and are kept after database deletion (until expiry; the policy can’t be changed after the database is deleted, but individual backups can be removed). Legal hold/immutability via Blob immutability policy in some setups.

Native backup and restore on VMs, MI

• SQL Server on VM: full, differential, transaction log, filegroup, copy-only (doesn’t break the differential base or log chain), tail-log backup. Requires the FULL recovery model for log backups and point-in-time restore; SIMPLE model reuses the log, no log backups; BULK_LOGGED minimally logs bulk operations.
• Automated Backup (SQL IaaS Agent extension): schedule backups to Azure storage (automatic or manual schedule), retention 1-90 days, encryption option.
• Azure Backup for SQL Server in Azure VMs: workload-aware, central policy, 15-minute log backups, restore to point in time, to alternate location, long-term retention, Recovery Services vault.
• Managed Instance: platform handles automatic backups for PITR and LTR; you can take COPY_ONLY full backups to URL only. You can restore native backups from URL into MI.

T-SQL examples

-- full, differential, log
BACKUP DATABASE Sales TO URL = 'https://acct.blob.core.windows.net/bkp/Sales_full.bak' WITH COMPRESSION, CHECKSUM, STATS = 10;
BACKUP DATABASE Sales TO URL = '.../Sales_diff.bak' WITH DIFFERENTIAL, COMPRESSION;
BACKUP LOG Sales TO URL = '.../Sales_log.trn';

-- restore chain to a point in time
RESTORE DATABASE Sales FROM URL = '.../Sales_full.bak' WITH NORECOVERY;
RESTORE DATABASE Sales FROM URL = '.../Sales_diff.bak' WITH NORECOVERY;
RESTORE LOG Sales FROM URL = '.../Sales_log.trn' WITH STOPAT = '2026-11-09 10:30:00', RECOVERY;

• NORECOVERY leaves the database restoring so further backups apply; RECOVERY brings it online. STANDBY keeps it read-only between restores. STOPAT and STOPATMARK give the point-in-time and marked-transaction recovery. RESTORE VERIFYONLY checks a backup file is readable (not a full proof). WITH CHECKSUM detects corruption.
• Restore order: full → latest differential → each subsequent log in sequence. Take a tail-log backup first (WITH NORECOVERY) to avoid losing recent transactions.
• To rebuild after losing the system: restore master rarely; more often, restore user databases and re-create logins and jobs.

Backup to and from cloud storage (URL)

• Back up to Azure Blob storage as block blobs (recommended; BACKUP ... TO URL). Old page-blob backups with a storage account key are legacy.
• Authenticate with a CREDENTIAL containing a Shared Access Signature (SAS): CREATE CREDENTIAL [https://acct.blob.core.windows.net/bkp] WITH IDENTITY='SHARED ACCESS SIGNATURE', SECRET='<sas>'. SQL Server 2022 and MI also support managed identity. Credential name must match the container URL when using SAS.
• Striping across up to 64 URLs for performance, MAXTRANSFERSIZE = 4194304, BLOCKSIZE. A block blob max is about 200 GB per file, so stripe big backups.
• Restore from URL with RESTORE DATABASE ... FROM URL. On MI, native restore of a database from URL is the standard offline-migration path.
• Managed backup to Azure (SQL Server 2016+ Managed Backup) automates scheduling.

Configuring HA/DR

Active geo-replication (SQL Database)

• Asynchronous, readable secondary databases in the same or other regions, up to 4 secondaries, per database. Replication is built on Always On technology.
• Failover is manual per database (planned failover: no data loss; forced: possible data loss). After failover the connection string must change (server name differs) unless using a failover group. Secondary can be elastic pool or standalone, same or different tier.
• Setup: portal “Geo-replication”, Add-AzSqlDatabaseSecondary, az sql db replica create, or ALTER DATABASE ... ADD SECONDARY ON SERVER. Firewall rules and logins must exist on the secondary server. Hyperscale uses geo replicas too (and named replicas for read scale-out in the same region).
• Monitor with sys.dm_geo_replication_link_status (replication_lag_sec, last_replication).
• Termination (stop replication) makes the secondary a standalone read-write database.

Auto-failover groups

Groups of databases (SQL Database) or all user databases on an instance (MI) with a primary and secondary server/instance in different regions.

• Provide read-write listener (<fog>.database.windows.net / <fog>.<dnszone>.database.windows.net) and read-only listener (<fog>.secondary.database.windows.net) endpoints that stay constant, so no connection-string change after failover. Use them in apps.
• Failover policy: Automatic (service fails over when it detects an outage, after a grace period with data loss, minimum 1 hour recommended in portal default) or Manual. Planned failover is fully synchronised with no data loss. Forced failover can lose data (the grace period lets you trade RPO for RTO).
• SQL Database: pick which databases to include; each is geo-replicated to the secondary server. A server can hold several groups. MI: one failover group per instance pair, with instances in different regions, VNets connected by global VNet peering or VPN gateways, non-overlapping address ranges, and the secondary created in the same DNS zone as the primary (--dns-zone-partner).
• Prerequisites: both servers exist, logins/firewall/TDE keys aligned, SQL logins with same SIDs in master on the secondary (or use contained users/Entra).
• Failover group vs geo-replication: groups for application-level failover, many databases, constant endpoints; geo-replication for single database control.
• Failover cmdlets: Switch-AzSqlDatabaseFailoverGroup (planned), -AllowDataLoss (forced). Secondary servers are billed; use read-only listener for reporting.

Always On availability groups

Availability group (AG) replicates a set of databases from a primary replica to up to 8 secondary replicas (SQL Server 2019+; up to 5 synchronous-commit replicas including the primary, with automatic failover across up to 3 of them). Modes: synchronous commit (no data loss, adds latency, supports automatic failover) and asynchronous commit (low latency, possible data loss, manual or forced failover). Secondary can be readable and used for backups (not full non-copy-only on secondaries), and DBCC CHECKDB offload.

On Azure VMs:

• Needs a Windows Server Failover Cluster (WSFC) (Linux uses Pacemaker), nodes in an availability set or different availability zones for resilience, a domain (or domain-independent AG with certificates), and a quorum witness: cloud witness (Azure storage account), disk or file share witness.
• Listener needs a way to route traffic: Azure load balancer with a health probe and floating IP (frontend IP = listener IP), or Distributed Network Name (DNN) listener (SQL Server 2019 CU8+, Windows Server 2016+; no load balancer needed, simpler with multi-subnet). Multi-subnet failover setting MultiSubnetFailover=True in connection strings.
• Create through the portal’s SQL virtual machines resource (Azure quickstart templates or “High availability” blade with the SQL IaaS Agent extension in the Full management mode).
• Connection mode: ApplicationIntent=ReadOnly with read-only routing to readable secondaries.

On Managed Instance: AG is not user-managed, but Business Critical includes a built-in readable replica, and you use Managed Instance link (distributed availability group between SQL Server and MI) for hybrid DR/migration/read-offload. Failover groups handle regional DR.

Distributed availability groups link two AGs (for example across regions or from on-premises to Azure) with async replication.

Always On failover cluster instances (FCI)

An FCI is one SQL Server instance installed across WSFC nodes, using shared storage, with a virtual network name. Failover moves the instance; one copy of data so storage is a single point of failure unless replicated. Protects against node failure but not storage loss. On Azure VMs, shared storage options:

SQL Server on VMs with the IaaS extension supports the FCI registered through the extension. Compared with AG: FCI needs shared storage, protects the whole instance (logins, jobs), no readable secondary, no per-database failover, Standard Edition supports FCI (AG basic in Standard has limits: Basic Availability Groups only one database, two replicas, no read access). For a failover cluster of two nodes without data duplication choose FCI; for readable secondaries and database-level choose AG.

Log shipping

Log shipping automatically backs up the transaction log on the primary, copies it to a share (or Azure Blob storage), and restores it on one or more secondaries (warm standby), in NORECOVERY or STANDBY (readable between restores, interrupts when restoring). Jobs: backup, copy, restore, plus an optional monitor server that raises alerts when the backup/restore threshold (out of sync) is exceeded.

• RPO is at least the backup interval (typically minutes), manual failover (role change, redirect clients, bring online with RESTORE ... WITH RECOVERY), logins and jobs need manual sync. Supports cross-region and on-premises to Azure DR and can also be used on VMs only, not natively supported by SQL Database. For MI migration, use the Log Replay Service.
• Works with FULL or BULK_LOGGED recovery models, SQL Server Standard and higher, and can coexist with AG and FCI.

Comparison

Monitor and troubleshoot HA/DR

• Geo-replication/failover groups: sys.dm_geo_replication_link_status, portal replication status, Get-AzSqlDatabaseFailoverGroup, Azure Monitor metrics and Resource Health, Service Health alerts. Common problems: logins missing on the secondary, firewall differences, TDE key not accessible, tier too small for the primary’s log rate (lag growing), replication broken after tier change, networking gaps for MI (NSG, peering, DNS zone).
• AG: AG dashboard in SSMS, sys.dm_hadr_availability_replica_states, sys.dm_hadr_database_replica_states (synchronization_state_desc, log_send_queue_size, redo_queue_size), Failover Cluster Manager events, Extended Events AlwaysOn_health. Typical causes: quorum loss, listener connectivity (load balancer probe, NSG, MultiSubnetFailover), network latency in synchronous mode, log growth because a secondary is behind or suspended, HADR waits (HADR_SYNC_COMMIT).
• Log shipping: monitor job history, msdb.dbo.log_shipping_monitor_* tables, thresholds and alerts, broken LSN chain (log backup taken outside log shipping).
• FCI: cluster validation, shared disk health, DNS/virtual name, quorum.
• Alert on replication lag, failed backups, and backup storage use. For restore, verify with RESTORE HEADERONLY and RESTORE FILELISTONLY, and run test restores.