DP-300 Plan and configure high availability and disaster recovery — 15 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?

Choose an answer to begin — your selection is scored in the full session.

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

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.. 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 3

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 4

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 5

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 6

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 7

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 8

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. 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 9

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 10

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 11

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.. 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 12

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

Accepted Answer

Select the Azure SQL Database, then navigate to Automatic tuning settings, then enable the desired options, then save changes.. Automatic tuning is configured via the portal by selecting the database, navigating to automatic tuning, enabling options, and saving.

Answer

Navigate to Automatic tuning settings, then select the Azure SQL Database, then enable the desired options, then save changes.

Answer

Select the Azure SQL Database, then enable the desired options, then navigate to Automatic tuning settings, then save changes.

Answer

Enable the desired options, then select the Azure SQL Database, then navigate to Automatic tuning settings, then save changes.

Question 13

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.

Accepted Answer

Connect to the instance, create a job, define steps, schedule, then enable and start.. Connect to the instance, create a job, define steps, schedule, then enable and start.

Answer

Create a job, define steps, schedule, connect to the instance, then enable and start.

Answer

Connect to the instance, define steps, create a job, schedule, then enable and start.

Answer

Connect to the instance, create a job, schedule, define steps, then enable and start.

Question 14

Match each Azure SQL Database security feature to its purpose.

Accepted Answer

Transparent Data Encryption (TDE): Encrypts data at rest. TDE protects data at rest by encrypting the database files. Always Encrypted protects data in use by encrypting on the client side. Dynamic Data Masking hides sensitive data in query results from unauthorized users. Common confusions involve mixing up TDE and Always Encrypted.

Answer

Transparent Data Encryption (TDE): Encrypts data in use on client side

Answer

Always Encrypted: Encrypts data at rest

Question 15

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.