The DP-300 exam focuses heavily on choosing the right option for each high availability and disaster recovery scenario. Azure SQL Database and SQL Managed Instance offer backup, replication, and failover mechanisms that each address a distinct problem: backups handle logical corruption, replication addresses physical disasters, and automatic failover ensures continuous service.
PITR — A Time Machine for Undoing Mistakes
Imagine a developer runs a flawed UPDATE statement over a weekend and overwrites thousands of critical records. There is no rollback script, and the only information available is the exact timestamp of the error. Azure SQL Database offers Point-in-Time Restore (PITR) precisely for this kind of logical data corruption.
PITR works by combining three automated backup types: full backups weekly, differential backups every 12 hours, and transaction log backups every 5 to 10 minutes. Together they allow restoration to any minute within the retention window. The default retention period is 7 days, configurable up to 35 days.
Restoration always produces a new database — the original is never overwritten in place. After the restore completes, a DBA validates the data and manually redirects application connections to the new database. This step is part of the recovery time and must be factored into RTO planning.
LTR — A Vault for Decade-Long Records
A tax authority requires seven years of transaction history. A healthcare regulation mandates patient data be kept for ten years. PITR's maximum 35-day retention cannot meet these requirements. Long-Term Retention (LTR) was designed specifically for this kind of regulatory compliance.
LTR stores full backups in Azure Blob Storage for up to 10 years. Weekly, monthly, and yearly retention policies can be configured independently — for example, weekly backups for 12 weeks, monthly backups for 36 months, and yearly backups for 5 years simultaneously.
LTR and PITR serve different purposes. PITR is for precise point-in-time recovery after a bad query. LTR is for long-term archival — preserving a snapshot of January 1st from last year for an annual audit. Both features can be active on the same database simultaneously.
!PITR versus LTR
Active Geo-Replication — A Standby Twin Across the Country
When a logistics hub goes down in a storm, having an equivalent backup hub in another city means deliveries can continue with minimal disruption. Active Geo-Replication provides exactly this structure for Azure SQL Database.
Active Geo-Replication creates up to four readable secondary replicas in different regions for a single database. Replication is asynchronous, and secondary replicas can simultaneously serve read-only workloads such as reports and analytics queries, offloading pressure from the primary.
When a failure occurs, a manual failover promotes a secondary replica to primary. This is the key distinction from Auto-Failover Group — there is no automatic switching, someone must initiate it. Active Geo-Replication is not supported for SQL Managed Instance.
Auto-Failover Group — An Automatic Sprinkler System for Your Database
When a fire alarm goes off, should someone run to grab a fire extinguisher, or should the sprinklers activate automatically? Auto-Failover Group is the sprinkler system for database failures — it triggers without human intervention.
Auto-Failover Group supports automatic failover for a group of databases or SQL Managed Instances. When the primary region fails, the system switches to the secondary region based on a configured policy. Applications connect using a read-write listener endpoint, so no connection string changes are needed after failover.
Unlike Active Geo-Replication, Auto-Failover Group supports both SQL Database and SQL Managed Instance. It also manages multiple databases as a single group, making it suited for applications where many databases must fail over together to maintain consistency.
Geo-Replication vs Auto-Failover Group — How to Choose
| Feature | Active Geo-Replication | Auto-Failover Group | |:--|:--|:--| | Scope | Single database | Group of databases or MI | | Supported products | SQL Database only | SQL Database + SQL MI | | Failover type | Manual | Automatic (policy-based) | | Readable secondaries | Up to 4 | Yes (read-only listener) | | Listener endpoint | No | Yes | | Multi-database management | No | Yes |
Choose Active Geo-Replication when you need a single database with readable secondaries and want manual control over failover timing. Choose Auto-Failover Group when multiple databases must fail over as a unit, automatic cutover is required, and a single listener endpoint must remain stable after switchover.
Always On AG and FCI — HA for IaaS (SQL on VM)
When SQL Server runs on Azure VMs rather than a PaaS service, high availability is configured differently. Always On Availability Groups (AG) and Failover Cluster Instance (FCI) are the primary options in this IaaS environment.
Always On AG supports multi-node configurations with synchronous or asynchronous replication. A listener always routes clients to the primary node. On failure, another node takes over automatically or manually. Synchronous replication brings RPO close to zero but adds write latency since secondaries must acknowledge each write.
FCI provides instance-level availability using shared storage. If one node fails, another picks up the same SQL Server instance. For SQL Managed Instance, built-in instance-level availability resembles FCI and is handled by the platform automatically.
Common Exam Traps — Concepts That Look Similar But Are Not
The most frequent confusion is treating PITR and Geo-Replication as if they address the same problem. PITR undoes logical corruption — a bad UPDATE. Geo-Replication keeps a live copy in another region for physical disaster recovery. Mixing them up on an exam question almost always leads to a wrong answer.
Active Geo-Replication does not support SQL Managed Instance. Whenever a question combines SQL MI with readable secondaries or cross-region replication, Auto-Failover Group is the answer.
Geo-restore is not the same as Geo-replication. Geo-restore recovers from a geo-redundant backup and can have an RPO of several hours — it is not a real-time HA solution. If a scenario requires minimal data loss with continuous replication, Active Geo-Replication or Auto-Failover Group is the correct choice.
Exam Key Takeaways
"Logical corruption, wrong query recovery" -- PITR (Point-in-Time Restore) "7-year or 10-year regulatory retention" -- LTR (Long-Term Retention) "Default PITR retention period" -- 7 days (up to 35 days) "Maximum LTR retention period" -- 10 years "Single database, readable secondaries, manual failover" -- Active Geo-Replication "Group of databases, automatic failover, listener endpoint" -- Auto-Failover Group "SQL MI + cross-region replication" -- Auto-Failover Group (Geo-Replication not supported on MI) "IaaS SQL on VM, sync/async replication, multi-node" -- Always On Availability Groups "IaaS SQL on VM, instance-level HA, shared storage" -- Failover Cluster Instance "No connection string change needed after failover" -- Auto-Failover Group listener endpoint "Entire primary region failure, restore from backup" -- Geo-redundant backup + Geo-restore
PITR = logical corruption short-term recovery, LTR = regulatory long-term archival, Active Geo-Replication = single database manual cutover, Auto-Failover Group = group automatic cutover