Azure SQL: Backup, Geo-Replication, and Failover

Data protection and business continuity are critical for any production database. Azure SQL provides multiple layers of protection — from automatic backups and point-in-time restore to active geo-replication and auto-failover groups. This lesson covers how to protect your data and ensure availability across regions.

---

Automatic Backups

Azure SQL Database and SQL Managed Instance both take automated backups without any configuration:

Backup Schedule

Backup Type	Frequency	Purpose
Full	Weekly	Complete database copy
Differential	Every 12–24 hours	Changes since the last full backup
Transaction log	Every 5–10 minutes	All committed transactions

Backup Storage Redundancy

When you create a database, you choose the backup storage redundancy:

Option	Description	Use Case
Locally redundant (LRS)	3 copies in a single data centre	Development, non-critical workloads
Zone-redundant (ZRS)	3 copies across availability zones	Production with single-region DR
Geo-redundant (GRS)	6 copies: 3 local + 3 in a paired region	Production requiring cross-region DR

The default for new databases is geo-redundant storage (GRS), which protects against the loss of an entire Azure region.

---

Point-in-Time Restore (PITR)

PITR lets you restore a database to any second within the backup retention period:

az sql db restore \
  --resource-group rg-demo \
  --server my-sql-server \
  --name my-database-restored \
  --dest-name my-database-pitr \
  --time "2025-03-10T14:30:00Z"

Retention Periods

Tier	Default	Maximum
Basic (DTU)	7 days	7 days
Standard / General Purpose	7 days	35 days
Premium / Business Critical	7 days	35 days
Hyperscale	7 days	35 days

You can configure the retention period per database:

az sql db update \
  --resource-group rg-demo \
  --server my-sql-server \
  --name my-database \
  --backup-storage-redundancy Geo \
  --short-term-retention-days 14

How PITR Works

1. Azure continuously takes full, differential, and transaction log backups
2. When you request a restore, Azure restores the most recent full backup
3. Then applies the differential backup
4. Then replays transaction logs up to the requested point in time
5. The restore creates a new database — it does not overwrite the existing one

---

Long-Term Retention (LTR)

For compliance and regulatory requirements, you may need to keep backups for months or years:

Setting	Description	Example
Weekly	Keep one full backup per week	W=4 (keep 4 weekly backups)
Monthly	Keep one full backup per month	M=12 (keep 12 monthly backups)
Yearly	Keep one full backup per year	Y=5 (keep 5 yearly backups)
Week of year	Which week's backup to keep for the yearly copy	WeekOfYear=1 (first week of January)

az sql db ltr-policy set \
  --resource-group rg-demo \
  --server my-sql-server \
  --name my-database \
  --weekly-retention P4W \
  --monthly-retention P12M \
  --yearly-retention P5Y \
  --week-of-year 1

LTR backups are stored in Azure Blob Storage (read-access geo-redundant by default) and can be retained for up to 10 years.

---

Active Geo-Replication

Active geo-replication creates continuously synchronised read replicas of your database in different Azure regions:

Primary (UK South)
  |
  |-- Geo-Secondary (North Europe) — asynchronous replication
  |-- Geo-Secondary (East US) — asynchronous replication

Key Characteristics

Feature	Detail
Replication	Asynchronous (eventual consistency)
Max secondaries	Up to 4 readable secondaries
Regions	Any Azure region (including the same region)
Read access	Secondaries are readable for offloading queries
Failover	Manual — you initiate the failover
RPO	Less than 5 seconds (typical replication lag)
RTO	Less than 30 seconds

Creating a Geo-Secondary

az sql db replica create \
  --resource-group rg-demo \
  --server my-sql-server \
  --name my-database \
  --partner-server my-dr-server \
  --partner-resource-group rg-dr

Manual Failover