Q: Your Tier-1 banking transactional database requires 100 TB of storage capacity, rapid scaling up to 80 vCores, and a strict DR requirement of RPO < 5 seconds and RTO < 2 minutes across paired Azure regions. How do you design and operate Azure SQL Hyperscale with Auto-Failover Groups?

Deploy tiered compute and storage architecture supporting up to 100 TB:

• Provision Hyperscale: Executed az sql db create -g rg-data -s sql-primary-eastus -n core-ledger --edition Hyperscale --family Gen5 --capacity 32 --ha-replicas 2 --zone-redundant true.
• Storage Architecture: Hyperscale separates compute nodes from storage page servers, enabling near-instant database scaling and snapshots regardless of data size.

Establish continuous asynchronous replication to a paired secondary region:

• Create Failover Group: Created failover group linking East US (primary) to Central US (secondary): az sql failover-group create -g rg-data --server sql-primary-eastus --name fg-ledger --partner-server sql-dr-centralus --failover-policy Automatic --grace-period 1.
• Failover Policy & Grace Period: Set grace period to 1 hour for automated failover, preventing premature split-brain during transient network blips.

Segregate transactional OLTP traffic from analytical reporting traffic:

• Read-Only Listener: Configured BI dashboards and analytics services to connect to fg-ledger.secondary.database.windows.net.
• Zero Resource Contention: Hyperscale secondary replicas utilize dedicated compute nodes, preventing analytical queries from consuming write transaction log throughput.

Validate RPO and RTO compliance under simulated primary region outage conditions:

• Planned Failover Drill: Executed az sql failover-group set-primary -g rg-data --server sql-dr-centralus --name fg-ledger.
• Metrics Verification: Confirmed zero data loss (RPO = 0s) and listener redirection completed in 34 seconds (RTO well below 2-minute SLA).