DP-300 Plan and configure high availability and disaster recovery — All Questions With Answers

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?

Accepted Answer

Deploy the database in the Business Critical tier with zone-redundant configuration in West US. The Business Critical tier with zone-redundant configuration provides synchronous replication of data across three availability zones within the same Azure region, ensuring zero data loss (RPO=0) and automatic failover within seconds. This meets the stringent RPO of 5 seconds and RTO of 30 seconds during a regional outage because zone redundancy protects against zone-level failures, and the failover is automatic and fast. In contrast, other options either introduce asynchronous replication (which cannot guarantee an RPO of 5 seconds) or rely on cross-region failover that exceeds the required RTO.

Answer

Use an Auto-Failover Group with a secondary in West US 2

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. Option C is correct because 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 runs SQL Server 2019 on Azure Virtual Machines in an availability set. They need to achieve high availability for a critical database with automatic failover and no shared storage. The solution must minimize downtime during planned maintenance. What should they implement?

Accepted Answer

Create an Always On Availability Group with an availability group listener. An Always On Availability Group (AG) with a listener provides high availability with automatic failover at the database level without requiring shared storage. This solution meets the requirement for automatic failover and minimizes downtime during planned maintenance by allowing manual failover to a synchronized secondary replica with minimal disruption.

Answer

Configure Log Shipping to a secondary VM

Answer

Deploy a Failover Cluster Instance using Azure Shared Disks

Answer

Use Database Mirroring with automatic failover

Question 4

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 5

Which TWO options are required to configure a SQL Server Always On Availability Group on Azure Virtual Machines?

Accepted Answer

Internal Load Balancer. A is correct because an Internal Load Balancer is required to route traffic to the primary replica in a SQL Server Always On Availability Group (AG) deployed on Azure Virtual Machines. The listener uses the ILB's frontend IP and health probe to direct client connections to the current primary node, as the Windows Server Failover Cluster (WSFC) does not support the cluster IP address in Azure without a load balancer.

Answer

Azure Files share for witness

Answer

Azure SQL Database

Answer

VPN gateway between regions

Question 6

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 is correct because auto-failover groups provide automatic failover capability, which is essential for minimizing downtime during a disaster. Active geo-replication, on the other hand, requires manual or custom scripting to initiate failover, making it less suitable for scenarios where rapid, unattended recovery is needed.

Answer

Recovery Point Objective (RPO) of 1 second

Answer

Support for SQL Server Authentication

Question 7

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 8

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.. Option B is correct because 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 9

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 a failover group is the Azure feature that manages automatic failover for geo-replicated databases. It coordinates the failover of the primary and all secondaries in the group, providing a single endpoint and automatic failover policy. Without a failover group, active geo-replication only supports manual 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 10

You are the database administrator for a global e-commerce company. The company runs its production SQL Server on an Azure Virtual Machine (IaaS) in the West US region. The database is mission-critical and requires a Recovery Point Objective (RPO) of 5 minutes and a Recovery Time Objective (RTO) of 30 minutes in the event of a regional disaster. The VM uses premium SSDs and is backed up daily to a Recovery Services vault with geo-redundant storage. The current backup policy takes full backups weekly, differential backups daily, and transaction log backups every 15 minutes. The VM is in an availability set for high availability within the region. During a recent regional outage simulation, the database was unavailable for 4 hours because the backups needed to be restored to a different region, and the restore process took longer than expected. You need to recommend a solution to meet the RPO and RTO requirements. What should you do?

Accepted Answer

Configure a SQL Server Always On availability group with a synchronous-commit replica in a secondary Azure region.. Option C is correct because a SQL Server Always On availability group with a synchronous-commit replica in a secondary Azure region provides automatic failover with zero data loss (RPO of 0 seconds) and can meet the 30-minute RTO by enabling fast, automated failover to the secondary region. This solution eliminates the need for manual restore processes and ensures continuous data synchronization, directly addressing the 4-hour outage caused by slow geo-restore.

Answer

Implement Azure Site Recovery to replicate the VM to a secondary region.

Answer

Set up log shipping to a secondary SQL Server in a different region and perform manual failover.

Answer

Increase the frequency of transaction log backups to every 5 minutes and use geo-restore.

Question 11

Drag and drop the steps to configure automatic tuning for an Azure SQL Database in the correct order.

Question 12

Drag and drop the steps to configure a SQL Server Agent job in Azure SQL Managed Instance to run a maintenance task in the correct order.

Question 13

Match each Azure SQL Database security feature to its purpose.

Question 14

Match each Azure SQL Database error code to its meaning.

Question 15

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 primary database SalesDB is not zone-redundant.. The exhibit shows zoneRedundant is false. Geo-replication requires zone redundancy enabled on the primary database because Azure SQL Database geo-replication is only supported for zone-redundant databases.

Answer

The secondary server sqlsrv2 is in a different region than the primary.

Answer

The recovery model is set to Full instead of Simple.

Answer

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

Question 16

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 17

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 automated backup frequency is 12 hours, so point-in-time restore is not supported.. Error 406 in Azure SQL Database indicates that the requested restore point is not available because it is outside the backup retention period. The automated backup frequency is 12 hours, so backups are taken every 12 hours. The last backup before 2025-03-14 10:00 would be at 2025-03-14 02:00. The next backup after that is at 2025-03-14 14:00. Since the restore point 10:00 falls between backups, the database cannot be restored to that exact time unless transaction log backups are available. However, the error suggests that the point is not within the retention window. The retention is 7 days, so 2025-03-14 is within 7 days from 2025-03-15. But the backup frequency of 12 hours means that the minimum restore point granularity is 12 hours. The requested time is not aligned with any available backup or log backup. The error 406 specifically means 'The database is not in a state that allows recovery to the specified point in time.' This can happen if the database is in a geo-replication relationship or if the point is not covered by log backups. In this case, the automated backup frequency is 12 hours, so there are no log backups for point-in-time restore. The only available restore points are the full/differential backups at 02:00 and 14:00. Therefore, the requested time 10:00 is not available.

Answer

The database is currently in a geo-replication secondary role.

Answer

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

Answer

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

Question 18

Refer to the exhibit. After a brief outage, the availability group recovered. However, SQL2 shows NOT_HEALTHY and DISCONNECTED. What is the most likely cause?

Accepted Answer

The network connection between SQL1 and SQL2 is still down after the outage.. The error 35201 indicates a connection timeout. SQL2 is configured for automatic failover and synchronous commit. After the restart, SQL2 remains disconnected and not healthy. This is likely because the network connectivity between SQL1 and SQL2 is still interrupted or there is a persistent issue. Since SQL3 is connected and healthy, the issue is specific to SQL2.

Answer

The automatic failover policy requires a quorum that is not met.

Answer

The listener is not configured correctly for SQL2.

Answer

The secondary replica SQL2 has an incompatible database version.

Question 19

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.. Restoring the primary database to an earlier point in time will break the geo-replication link. The secondary database will become a regular database, and replication must be reconfigured after the restore. The restore is possible because the requested time is within the restore window.

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.

DP-300 Plan and configure high availability and disaster recovery — All Questions With Answers

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?

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?

A company runs SQL Server 2019 on Azure Virtual Machines in an availability set. They need to achieve high availability for a critical database with automatic failover and no shared storage. The solution must minimize downtime during planned maintenance. What should they implement?

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?

Which TWO options are required to configure a SQL Server Always On Availability Group on Azure Virtual Machines?

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

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?

Exhibit

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

Drag and drop the steps to configure automatic tuning for an Azure SQL Database in the correct order.

Drag and drop the steps to configure a SQL Server Agent job in Azure SQL Managed Instance to run a maintenance task in the correct order.

Match each Azure SQL Database security feature to its purpose.

Match each Azure SQL Database error code to its meaning.

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?

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?

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?

Exhibit

Refer to the exhibit. After a brief outage, the availability group recovered. However, SQL2 shows NOT_HEALTHY and DISCONNECTED. What is the most likely cause?

Exhibit

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?

DP-300 Plan and configure high availability and disaster recovery — All Questions With Answers

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?

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?

A company runs SQL Server 2019 on Azure Virtual Machines in an availability set. They need to achieve high availability for a critical database with automatic failover and no shared storage. The solution must minimize downtime during planned maintenance. What should they implement?

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?

Which TWO options are required to configure a SQL Server Always On Availability Group on Azure Virtual Machines?

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

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?

Exhibit

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

Drag and drop the steps to configure automatic tuning for an Azure SQL Database in the correct order.

Drag and drop the steps to configure a SQL Server Agent job in Azure SQL Managed Instance to run a maintenance task in the correct order.

Match each Azure SQL Database security feature to its purpose.

Match each Azure SQL Database error code to its meaning.

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?

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?

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?

Exhibit

Refer to the exhibit. After a brief outage, the availability group recovered. However, SQL2 shows NOT_HEALTHY and DISCONNECTED. What is the most likely cause?

Exhibit

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?