DP-300 Plan and configure high availability and disaster recovery — Timed 20 Questions

Question 1

A company runs a critical Azure SQL Database in the West US region. To meet a Recovery Point Objective (RPO) of 5 seconds and a Recovery Time Objective (RTO) of 30 seconds during a regional outage, which deployment option should be used?

20 minute time limit — choose an answer to begin.

Accepted Answer

Use an Auto-Failover Group with a secondary in West US 2. For a regional outage, you need a secondary in a different Azure region. Auto-Failover Groups provide automatic failover with a configurable data loss threshold. The RPO of 5 seconds can be achieved by setting the grace period appropriately, and automatic failover can typically occur within seconds to meet the RTO of 30 seconds. The Business Critical tier with zone-redundant configuration only protects against availability zone failures within the same region, not a full regional outage. Other options either have longer RTO (point-in-time restore) or require manual failover (Active Geo-Replication with readable secondary without automatic failover).

Answer

Deploy the database in the Business Critical tier with zone-redundant configuration in West US

Answer

Enable geo-zone-redundant backup storage and perform point-in-time restore

Answer

Configure Active Geo-Replication with a readable secondary in East US

Question 2

A company has an Azure SQL Managed Instance in the East US region. They need to implement disaster recovery with automatic failover to a paired region. The solution must minimize data loss to less than 5 seconds. Which feature should they use?

Accepted Answer

Configure an Auto-Failover Group with a secondary instance in West US. Auto-Failover Groups for Azure SQL Managed Instance provide automatic failover to a paired region (West US) with a replication lag target of less than 5 seconds when using the Readable Secondary option. This feature uses synchronous replication at the commit level to minimize data loss, meeting the RPO requirement of <5 seconds.

Answer

Active Geo-Replication with a secondary in East US 2

Answer

Enable geo-redundant backup storage and restore to West US

Answer

Set up a Failover Group with manual failover to a secondary in West US

Question 3

A company uses Azure SQL Database Hyperscale tier for a large database. They need to perform a disaster recovery drill by failing over to a secondary region with minimal data loss. The secondary is in a paired region and is readable. Which approach should they use?

Accepted Answer

Use an Auto-Failover Group with the secondary in the paired region. Auto-Failover Groups with Azure SQL Database Hyperscale provide automated, orchestrated failover to a secondary region with minimal data loss by using synchronous replication for the log service. This meets the requirement for a disaster recovery drill with a readable secondary and minimal data loss, as the secondary is kept in sync and can be failed over manually or automatically.

Answer

Restore a geo-redundant backup to the secondary region

Answer

Configure Active Geo-Replication to the secondary region

Answer

Create a named replica in the secondary region and fail over manually

Question 4

Which THREE factors should be considered when choosing between Azure SQL Database active geo-replication and auto-failover groups for disaster recovery?

Accepted Answer

Automatic failover capability. Option A (Automatic failover capability) is correct because auto-failover groups provide automatic failover of the entire group when the primary region becomes unavailable, whereas active geo-replication requires manual failover initiation, making this a key differentiator when choosing between the two. Option C (Number of readable secondary replicas required) is correct because active geo-replication supports up to four readable secondaries per primary database, while auto-failover groups support only one readable secondary per group, so the required number of readable replicas directly influences the choice. Option E (Granular control over individual database failover) is correct because active geo-replication allows failover at the individual database level, while auto-failover groups fail over all databases in the group together, so the need for per-database granularity is a deciding factor. Option B is not a differentiator because both features provide an RPO of approximately 5 seconds (not 1 second), and Option D is not a distinguishing factor because both active geo-replication and auto-failover groups support SQL Server Authentication.

Answer

Recovery Point Objective (RPO) of 1 second

Answer

Support for SQL Server Authentication

Question 5

You are troubleshooting an Always On Availability Group named AG1. The exhibit shows the current state. The primary replica shows PENDING_FAILOVER. The secondary replica is DISCONNECTED with error 35202. What is the most likely cause of this issue?

Accepted Answer

Network connectivity between the replicas is blocked on port 5022. The PENDING_FAILOVER state on the primary replica indicates that a failover was initiated but could not complete because the secondary replica is DISCONNECTED. Error 35202 specifically indicates that a connection attempt to the secondary replica failed, which in an Always On Availability Group (AG) is typically due to a network connectivity issue on the dedicated endpoint port (default 5022). Since the secondary is unreachable, the failover cannot finalize, leaving the primary in a pending state.

Answer

The availability group listener is not configured correctly

Answer

The primary instance encountered a critical error and failed over automatically

Answer

The cluster has lost quorum

Question 6

You are the database administrator for a global e-commerce company. They run a mission-critical application on Azure SQL Database in the Business Critical tier. The database is 2 TB and experiences high write throughput. The current setup uses an auto-failover group with a secondary in the same region (West US) for high availability. The client application uses the auto-failover group listener with ReadScale=1 to route read-only queries to the secondary. Recently, during a regional outage that affected West US, the failover to the secondary succeeded, but the application experienced significant performance degradation and many timeouts for read operations. Investigation reveals that the secondary replica was overwhelmed with read traffic after failover. The business requires an RTO of 30 seconds and RPO of 5 seconds. The application must be able to handle read-heavy workloads even during a failover. You need to recommend a solution to improve read scalability and disaster recovery without changing the application code. What should you do?

Accepted Answer

Deploy an additional readable secondary replica in the Business Critical tier in a different Azure region and configure active geo-replication to that region. Update the connection string to use the geo-secondary for reads.. Deploying an additional readable secondary replica in a different Azure region via active geo-replication provides both disaster recovery and read scalability. After a regional failover, the geo-secondary can serve read traffic without overwhelming the primary, meeting the RTO of 30 seconds and RPO of 5 seconds. The application can use the geo-secondary for reads by updating the connection string, without changing application code, ensuring read-heavy workloads are handled during failover.

Answer

Add a second secondary replica to the auto-failover group in the same region.

Answer

Increase the service objective of the secondary replica in the auto-failover group to a higher DTU or vCore to handle the read load.

Answer

Change the database to the Hyperscale tier, which supports multiple readable replicas and automatic failover.

Question 7

Which TWO configurations are required to enable automatic failover for an Azure SQL Database configured with active geo-replication?

Accepted Answer

Configure a failover group that includes the primary and secondary databases.. Option B is correct because automatic failover for active geo-replication requires an auto-failover group, which wraps the primary and secondary databases (or servers) and defines the failover policy, grace period, and listener endpoints that Azure uses to trigger failover automatically. Option E is correct because active geo-replication's automatic failover scenario requires the secondary database to reside on a logical server in a different Azure region, ensuring regional isolation so the secondary can take over if the primary region fails. Option A is not required because read-scale out only affects read-only routing to replicas and is unrelated to failover automation. Option C is not required because the secondary can use a different service tier (though matching tiers is recommended for performance parity), and failover still works without it. Option D is not required because zone redundancy protects against datacenter-level failures within a region and is not a prerequisite for cross-region automatic failover.

Answer

Enable read-scale out on the secondary database.

Answer

Set the secondary database to have the same service tier as the primary.

Answer

Configure zone redundancy on the primary database.

Question 8

Match each Azure SQL Database error code to its meaning.

Accepted Answer

4060: Cannot open database requested by the login. Login failed.. Common Azure SQL Database error codes: 4060 (database not accessible), 18456 (login failure), 40197 (transient error), 40501 (throttling). Distractors swap the definitions between 4060 and 18456.

Answer

4060: Login failed for user 'username'.

Answer

18456: Cannot open database requested by the login.

Question 9

Refer to the exhibit. An administrator creates a geo-replica for SalesDB. After 30 minutes, the replication state remains SEEDING. What is the most likely cause?

Accepted Answer

The secondary server sqlsrv2 is in a different region than the primary.. Geo-replication seeds the database by copying data from primary to secondary. Being in a different region introduces network latency and higher transfer time, which can cause the SEEDING state to persist for an extended period. Zone redundancy is not a prerequisite for geo-replication.

Answer

The recovery model is set to Full instead of Simple.

Answer

The primary database SalesDB is not zone-redundant.

Answer

The secondary database is being used as a read-only replica.

Question 10

Refer to the exhibit. An administrator wants to ensure that during a regional outage, the failover group automatically fails over without data loss if possible. What is the current configuration gap?

Accepted Answer

The backup storage redundancy is Local, which does not provide geo-redundancy for backups.. Automatic failover with data loss grace period is set to 60 minutes. The backup redundancy is Local, which means backups are stored only in the primary region. If a regional disaster occurs, backups might be lost. Geo-redundant backups would be needed to recover in the secondary region without data loss.

Answer

The failover policy is set to Automatic, but the grace period is too long.

Answer

The secondary managed instance mi2 is in the same region as the primary.

Answer

The read-only endpoint failover policy is Disabled, which prevents read-only traffic after failover.

Question 11

Refer to the exhibit. An administrator tries to restore the database OrdersDB to a point in time 2025-03-14 10:00 UTC and receives error 406. What is the most likely reason?

Accepted Answer

The database is currently in a geo-replication secondary role.. Error 406 in Azure SQL Database indicates that the database is not in a state that allows recovery to the specified point in time. This commonly occurs when the database is a secondary replica in a geo-replication relationship. Geo-secondary databases are read-only and cannot be restored to a point in time directly; instead, the primary database must be used for restore operations. Options B, C, and D are incorrect: long-term retention does not interfere with PITR, the requested time is within the 7-day retention window, and Azure SQL Database automatically takes transaction log backups every 5-10 minutes, enabling PITR regardless of full backup frequency.

Answer

The long-term retention policy interferes with point-in-time restore.

Answer

The requested restore point is outside the 7-day retention period.

Answer

The automated backup frequency is 12 hours, so point-in-time restore is not supported.

Question 12

Refer to the exhibit. An administrator wants to restore the primary database SalesDB to a point in time 2025-03-15T09:00:00Z. What is the impact on the geo-replication?

Accepted Answer

The geo-replication will be terminated, and the secondary will become a standalone database.. When you perform a point-in-time restore (PITR) on a primary database that is configured for geo-replication, the geo-replication link is broken. The secondary database becomes a standalone read-only database, and the primary is restored as a new database (typically with a name like SalesDB_restored or a specified name). This is because PITR creates a new database from backups, which cannot maintain the continuous replication stream required for geo-replication.

Answer

The geo-replication will continue seamlessly after the restore.

Answer

The restore will fail because the database is in a geo-replication relationship.

Answer

The secondary database will automatically be restored to the same point in time.

Question 13

Refer to the exhibit. An administrator is configuring geo-replication for an Azure SQL Database. The admin runs the commands and sees the output shown. What is the current status of the geo-replication link?

Accepted Answer

The secondary database is fully synchronized and ready for failover.. The exhibit (not shown here) indicates the geo-replication link is in a healthy, synchronized state, meaning the secondary database is fully synchronized and ready for failover. In Azure SQL Database, a 'Seeding' or 'Catchup' state would indicate incomplete sync, but the correct answer reflects a 'Synchronized' or 'Healthy' status. Therefore the secondary is ready for failover.

Answer

The secondary database is lagging behind the primary by a significant amount.

Answer

The secondary database is in the process of initial seeding and is not yet fully synchronized.

Answer

The replication link is broken and needs to be re-created.

Question 14

Refer to the exhibit. A backup administrator checks the protection status of a VM and receives an error. What is the most likely cause?

Accepted Answer

The VM has been deleted from Azure.. The error code 'UserErrorVMNotFound' and message indicate that the VM 'myVM' has been deleted. The backup item exists but the underlying VM is gone, so backups can no longer be performed.

Answer

The backup vault is in a different region than the VM.

Answer

The backup policy has been deleted, causing the VM to be unprotected.

Answer

The VM has been moved to a different resource group.

Question 15

Refer to the exhibit. An administrator configures an auto-failover group for Azure SQL Database. The primary server is in East US and the secondary in West US. What will happen if the primary region becomes unavailable?

Accepted Answer

The failover will occur automatically after 60 minutes.. Auto-failover groups in Azure SQL Database support automatic failover based on a configurable grace period, with the default being 1 hour (60 minutes). When the primary region becomes unavailable, Azure detects the outage and, after the grace period expires, automatically promotes the secondary to primary without manual intervention. This ensures high availability and disaster recovery across regions while preventing unnecessary failovers due to transient issues.

Answer

The administrator must manually trigger the failover.

Answer

The databases will become read-only until the primary recovers.

Answer

The secondary will immediately take over as the primary.

Question 16

Refer to the exhibit. An administrator is trying to perform a point-in-time restore for a managed instance database. The output shows the state of the instance and databases. What is the most likely reason db1 shows 'Restoring' status?

Accepted Answer

The database is being restored from a geo-redundant backup.. The 'Restoring' status on db1 indicates that a restore operation is currently in progress on that database. The geo-backup list shows an available backup, which might be used for a geo-restore or point-in-time restore that is ongoing.

Answer

The database is being upgraded to a new service tier.

Answer

The database is corrupted and Azure is automatically repairing it.

Answer

The database is in the process of replication to a secondary region.

Question 17

Refer to the exhibit. An administrator configures an Azure Cache for Redis instance with zone redundancy and a replica in a different region. What is the current state of the geo-replication setup?

Accepted Answer

The geo-replication is partially configured; the East US replica is still being provisioned.. The exhibit shows that zone redundancy is enabled on the primary cache, but the geo-replication link to the East US replica is still in the provisioning state, meaning the secondary cache has not finished syncing. Until provisioning completes, the geo-replication is only partially configured and failover is not yet possible.

Answer

The zone redundancy is enabled but geo-replication is not configured.

Answer

The geo-replication failed because the SKU does not support it.

Answer

The geo-replication is fully functional and ready for failover.

Question 18

You administer an Azure SQL Database named SalesDB in the East US region. The business requires a secondary read-only copy in West US for disaster recovery. You need the secondary to become writable automatically if East US fails, with no application connection string changes. The solution must minimize cost. What should you configure?

Accepted Answer

A failover group containing SalesDB with the West US server as the secondary.. A failover group is the only option that combines cross-region replication, automatic failover, and stable listener endpoints. Because applications use the listener, no connection string changes are needed after a failover. Active geo-replication requires manual failover and connection string updates, and read-scale out replicas are not a DR solution.

Answer

A read-scale out replica in West US and an Azure Traffic Manager profile.

Answer

A geo-secondary in West US with auto-failover enabled on the database.

Answer

An active geo-replication secondary in West US and a manual failover runbook.

Question 19

You manage a SQL Server 2019 instance on an Azure Virtual Machine named SQLVM1. The instance hosts a database that must be highly available. You have configured an Always On availability group with a primary replica on SQLVM1 and a secondary replica on SQLVM2 in the same region. You need to ensure that the availability group automatically fails over to SQLVM2 if SQLVM1 becomes unresponsive. The cluster quorum is currently configured with a disk witness. What should you do?

Accepted Answer

Set the availability group to automatic failover mode.. To enable automatic failover, the availability group must be set to automatic failover mode, and the secondary replica must be configured for synchronous commit. The disk witness provides quorum, so no quorum change is needed. Asynchronous commit would prevent automatic failover, and adding a third replica is unnecessary for this requirement.

Answer

Change the quorum configuration to use a cloud witness.

Answer

Configure the secondary replica to asynchronous commit mode.

Answer

Add a third replica in a different region to the availability group.

Question 20

You manage an Azure SQL Database in the East US region using the Business Critical service tier. The database is critical and must be recoverable in a different Azure region with an RPO of less than 5 minutes and an RTO of less than 1 hour. You need to configure a disaster recovery solution that meets these requirements with minimal administrative effort. What should you do?

Accepted Answer

Configure an auto-failover group that includes the primary server and a secondary server in West US.. Auto-failover groups are designed for cross-region disaster recovery with automatic failover, low RPO, and minimal administrative effort. They allow the application to continue using the same connection string, and failover can be automatic based on policies. Geo-replicas and active geo-replication require manual failover, and backup/restore is not automatic and may not meet the RTO.

Answer

Enable active geo-replication and configure a failover policy with automatic failover.

Answer

Use Azure SQL Database built-in backup and restore to a different region.

Answer

Create a geo-replica in West US and manually initiate a failover when needed.