Domain 5 of 5

Plan and configure a high availability and disaster recovery (HA/DR) environment

Domain · 22.5% of the DP-300 exam

Restore and failover are different recovery paths

Suppose an application bug deletes customer rows at 14:05 and the transaction commits: an Always On availability group (AG), a SQL Server feature that keeps a copy of each database on other SQL Server instances called replicas, protects a database by sending each of its transactions to a replica, so the replicas receive the deletion too, and Azure SQL Database answers data corruption or deletion caused by an application bug or human error with its automatic backups. Microsoft's disaster recovery guidance describes both ways back. A restore goes back to a backup, a copy of your data stored safely for a defined period of time, which recovers from disasters when automatic failover to another replica isn't possible, or when data corruption occurs. A failover moves the work to a target prepared in advance: an AG fails over to a secondary replica to make it the new primary replica, and a failover cluster instance (FCI), on Windows a single SQL Server instance installed across Windows Server Failover Cluster (WSFC) nodes, fails over from one node to another if the current node becomes unavailable; on Linux, Microsoft supports Pacemaker for FCI configurations with the mssql-server-ha package. Both paths are judged against the same two targets: the recovery point objective (RPO) is the maximum duration of acceptable data loss, and the recovery time objective (RTO) the maximum duration of acceptable downtime. The RPO has to align with the backup interval or with the data changes a secondary hasn't received yet, and the RTO has to cover the time a restore takes or the time a failover needs to detect the failed primary and switch to the secondary. A failover therefore never stands in for a backup: the SQL Server availability features don't replace the requirement to have a robust, well tested backup and restore strategy.

Plan an HA/DR strategy for database solutions, Plan and perform backup and restore of a database, Configure HA/DR for database solutions

Plan and configure a high availability and disaster recovery (HA/DR) environment carries 20–25% of the DP-300 exam, the same range as two other DP-300 domains, and the Microsoft DP-300 study guide lists its bullets under three headings. Plan an HA/DR strategy for database solutions lists 'Recommend HA/DR strategy based on Recovery Point Objective/Recovery Time Objective (RPO/RTO) requirements', 'Evaluate HA/DR for hybrid deployments', 'Evaluate Azure-specific HA/DR solutions' and 'Plan a testing procedure for an HA/DR solution' in the study guide: its bullets recommend, evaluate or plan, and none of them configures. Weighing a restore against a failover is that kind of recommendation: for Azure SQL Database, Microsoft gives a typical RTO of less than 60 seconds for active geo-replication or for failover groups with a customer-managed failover policy, against minutes or hours for geo-restore. Active geo-replication creates a continuously synchronized readable secondary database in any region; failover groups, an Azure SQL feature distinct from AGs, provide continuous synchronization between a primary and secondary database and also manage the replication and failover of some or all databases on a logical server to a secondary logical server in another region, and under the customer-managed policy customers perform the failover; geo-restore creates a new database on any existing server in any Azure region from geo-replicated backups. A test procedure covers restores as well as failovers: Microsoft's guidance is to test your backups and restoration processes to verify their integrity and understand how long the restoration process takes. Plan and perform backup and restore of a database lists seven bullets in the study guide, from 'Recommend a database backup and restore strategy' to 'Backup to and restore from cloud storage', among them 'Perform a database restore to a point in time' and 'Configure long-term backup retention', and each involves only backups and restores. In Azure SQL Database, point-in-time restore (PITR, a restore to a point within the retention period), long-term retention (LTR) restore and geo-restore each create a new database, and an LTR policy extends retention beyond the maximum supported period for point-in-time restore. Both headings hold a recommendation bullet: a choice among backups and restores alone fits the backup and restore heading, and a choice that includes a failover, such as geo-restore against a failover group, fits the RPO/RTO bullet of Plan an HA/DR strategy for database solutions. Configure HA/DR for database solutions lists 'Configure active geo-replication', 'Configure Always On availability groups on SQL Managed Instance and Azure virtual machines', 'Configure failover groups', 'Configure Always On Failover Cluster Instances on Azure virtual machines', 'Configure log shipping', 'Monitor an HA/DR solution' and 'Troubleshoot an HA/DR solution' in the study guide; the five features its Configure bullets name, active geo-replication, AGs, failover groups, FCIs and log shipping, are the HA/DR mechanisms, and each recovers by failover. Log shipping is the one built from backups: it is based on SQL Server's native backups, automatically generating transaction log backups, copying them to a warm standby and applying them there, yet recovery means bringing that secondary online, which a log shipping configuration doesn't do automatically. A requirement that weighs a protection including a failover, in an Azure-specific or a hybrid deployment, is what the Recommend and Evaluate bullets of Plan an HA/DR strategy for database solutions in the study guide describe.

The platform decides who runs the backups and which HA/DR mechanisms exist

With Azure SQL Database and Azure SQL Managed Instance you don't have to manage upgrades, high availability, or backups: in Azure SQL Database the service determines the exact timing of all database backups, and you can't change the schedule of backup jobs or disable them, and in Managed Instance database engine backups are automatically managed by Microsoft. Azure SQL Database backups are restored with tools such as the Azure portal, PowerShell, or the REST API, not Transact-SQL. Decisions remain: Azure SQL Database offers a configurable point-in-time restore retention period of up to 35 days on all service tiers except Basic, and active geo-replication and failover groups are disaster recovery capabilities you enable. The platform also limits which of the five HA/DR mechanisms are available. T-SQL statements related to availability groups aren't supported by SQL Database, and its unsupported high-availability syntax includes log shipping. Managed Instance fails over between regions with failover groups that continuously synchronize a primary and a secondary instance; it doesn't support CREATE AVAILABILITY GROUP, so the AGs a Configure bullet names for it are those of the Managed Instance link, which uses distributed availability groups to replicate data between SQL Server and Managed Instance, each an AG that spans two separate AGs; with SQL Server 2022 and later, the link enables online disaster recovery with fail back to SQL Server. On SQL Server on Azure VMs the backups are customer-run, for instance: you schedule Automated Backup or, backing up manually, are responsible for how your databases are backed up, for example directly to Blob storage in a different datacenter for disaster recovery; its business continuity features include AGs, FCIs, log shipping, backup and restore with Azure Blob storage, and Azure Site Recovery, which replicates VMs from a primary site to a secondary site. Azure Site Recovery is not one of the five HA/DR mechanisms; it is weighed under the Evaluate bullets of Plan an HA/DR strategy for database solutions.

Restore and failover across the three HA/DR headings of the Microsoft DP-300 study guide

Heading in the Microsoft DP-300 study guideRecovery pathWhat a requirement under it asksFeatures involved
Plan an HA/DR strategy for database solutionsRestore and failover, weighed against each otherWhich protection meets the RPO and RTO, in Azure-specific or hybrid deployments, and how to test itSuch as failover groups against geo-restore, AGs against FCIs, and failover and restore tests
Plan and perform backup and restore of a databaseRestoreHow backups are taken, retained and restored, including to a point in timeSuch as native backups, service-managed backups, PITR, LTR and geo-restore
Configure HA/DR for database solutionsFailoverHow an HA/DR mechanism is configured, monitored and troubleshotThe five HA/DR mechanisms its Configure bullets name: active geo-replication, AGs, failover groups, FCIs and log shipping

Decision tree

An HA/DR requirementPlanning how totest the HA/DRsolution?YesPlan an HA/DRstrategy fordatabase solutionsNoConfiguring,monitoring ortroubleshooting oneof the five HA/DRmechanisms?YesConfigure HA/DRfor databasesolutionsNoDoes the requirementinclude a failover?YesPlan an HA/DRstrategy fordatabase solutionsNoPlan and performbackup and restoreof a database

Subtopics in this domain