DP-300 Plan and configure a high availability and disaster recovery environment Practice Test 4 — 15 Questions

Question 1

Your company has an Azure SQL Database in the East US region. You need to configure a disaster recovery solution that provides a readable secondary in the West US region with an RPO of 5 seconds. The solution must support automatic failover from the application. What should you configure?

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

Accepted Answer

Create an auto-failover group with a secondary server in West US using synchronous replication.. An auto-failover group in Azure SQL Database provides a readable secondary in a paired or chosen region, uses asynchronous replication with an RPO typically under 5 seconds, and includes a read-write and read-only listener endpoint that enables automatic application failover. This matches the requirement for a readable secondary in West US, 5-second RPO, and automatic failover.

Answer

Enable zone-redundant replicas in the same region.

Answer

Enable geo-redundant backup storage and perform geo-restore.

Answer

Configure active geo-replication to a secondary server in West US.

Question 2

You manage a critical database in Azure SQL Database that must recover within 5 minutes in case of a regional outage. You configure geo-replication. Which SKU guarantees the fastest failover time with the least data loss?

Accepted Answer

Business Critical (BC). Business Critical (BC) is the Azure SQL Database service tier that provides the lowest latency and fastest failover for geo-replication because it uses local SSD storage and leverages Always On availability group technology with synchronous replication within the region and asynchronous replication across regions. For a 5-minute RTO with minimal data loss, BC offers the best performance and lowest RPO among the listed SKUs.

Answer

General Purpose (GP)

Answer

Hyperscale (HS)

Answer

Serverless

Question 3

Your Azure SQL Database is configured with active geo-replication to a secondary region. The primary region experiences a complete outage. You initiate a manual failover to the secondary. Users report that some client applications are failing to connect. What is the most likely cause?

Accepted Answer

Client applications are using the old connection string pointing to the primary server.. With Azure SQL active geo-replication, the secondary database is accessed via a different server name (the secondary server's FQDN). After a manual failover, the secondary becomes primary, but client applications still using the original primary server's connection string will fail because that server is unavailable. Applications must be updated to point to the new primary server.

Answer

The secondary server is behind a VPN that clients cannot access.

Answer

The secondary database is not fully provisioned yet.

Answer

The server name changed after failover and DNS is not updated.

Question 4

You need to implement a disaster recovery solution for a SQL Server on Azure Virtual Machine using SQL Server Always On Availability Groups. The secondary replica must be in a different Azure region. What is the minimum number of VMs required to achieve automatic failover?

Accepted Answer

2. To achieve automatic failover with SQL Server Always On Availability Groups, you need a quorum-based cluster. With only two replicas (one primary, one secondary), a third vote is required to break ties and avoid split-brain. In Azure, this third vote is typically provided by a cloud witness (an Azure Storage account) rather than a third VM. Therefore, the minimum number of VMs is two: one primary and one secondary in different regions, plus a cloud witness. The cloud witness acts as the tie-breaker, enabling automatic failover without a third VM.

Answer

1

Answer

3

Answer

4

Question 5

You have an Azure SQL Managed Instance configured with failover groups to a secondary region. During a disaster, you perform a planned failover to the secondary region. After the primary region recovers, you want to fail back. What should you do first?

Accepted Answer

Perform a planned failover from the current primary to the original primary.. After a planned failover to a secondary region, the secondary region becomes the primary. To fail back to the original primary region, you perform another planned failover from the current primary (the former secondary) to the original primary. This is the only way to switch roles back without data loss. Option B correctly identifies this process. Option A is wrong because you cannot add a new secondary replica while failover groups are already in place; failover groups manage replicas. Option C is wrong because geo-replication is not used with managed instance failover groups; failover groups handle replication. Option D is wrong because you cannot directly configure the original primary as a secondary; you must perform a failover.

Answer

Add a new secondary replica in the original region.

Answer

Start geo-replication from the current primary to the original region.

Answer

Configure the original primary as a secondary replica.

Question 6

You are designing a disaster recovery plan for a SQL Server 2022 on Azure VM. The solution must provide automatic failover within seconds and zero data loss. Which SQL Server feature should you use?

Accepted Answer

Failover cluster instance (FCI) with shared storage. A SQL Server Failover Cluster Instance (FCI) with shared storage provides automatic failover within seconds and zero data loss, as the shared storage ensures no data is lost during failover. Option A (Backup and restore to another region) does not provide automatic failover and can have data loss up to the last backup. Option C (Always On Availability Group with synchronous commit) can achieve zero data loss and automatic failover if properly configured, but it requires a listener and may have more complex setup; however, FCI is typically the preferred solution for VM-level HA with zero data loss. Option D (Log shipping) involves manual failover and potential data loss, so it does not meet the requirements.

Answer

Backup and restore to another region

Answer

Always On Availability Group with synchronous commit

Answer

Log shipping

Question 7

You are using Azure SQL Managed Instance with auto-failover groups. The primary region is East US, secondary is West US. The primary region experiences a full outage. How does the failover group behave?

Accepted Answer

The failover group automatically fails over to West US and the read-write endpoint now points to West US.. Auto-failover groups automatically fail over to the secondary region with automatic read-write endpoint switch. Option B is incorrect because automatic failover does not require manual initiation; it occurs after the grace period. Option C is incorrect because the read-only listener is also updated to point to the new secondary (formerly primary) after failover. Option D is incorrect because the failover group will fail over automatically, not remain in the primary region.

Answer

You must manually initiate failover via the portal.

Answer

The read-only listener continues to point to West US.

Answer

The failover group remains in primary region and does not fail over.

Question 8

Which THREE of the following are prerequisites for configuring an Always On Availability Group on Azure VMs with automatic failover? (Choose Three.)

Accepted Answer

All VMs must be in the same availability set or availability zones.. Option A is correct because automatic failover requires the WSFC nodes to have reliable, low-latency connectivity and consistent fault/update domain placement, so the SQL Server VMs must be deployed in the same availability set or across availability zones within the same region. Option B is correct because Always On Availability Groups depend on Windows Server Failover Clustering, which requires the cluster nodes to be joined to an Active Directory domain, so a domain controller must be reachable in the same virtual network (or a connected one). Option E is correct because in Azure, the availability group listener needs an internal load balancer with a floating IP (direct server return) to route client connections to the current primary replica, since Azure networking does not support the traditional cluster IP address. Option C is not required because Always On supports Windows authentication as well as SQL Server authentication, and domain-based Windows authentication is in fact typical. Option D is not a separate prerequisite because placing all VMs in the same Azure region is already implied by using a single availability set or availability zones, and cross-region configurations would not support this automatic failover scenario.

Answer

All VMs must use SQL Server authentication.

Answer

All VMs must be in the same Azure region.

Question 9

Which TWO of the following are valid reasons to use zone-redundant configuration for Azure SQL Database? (Choose Two.)

Accepted Answer

Protection against a single availability zone failure within a region.. Option C is correct because zone-redundant configuration replicates Azure SQL Database across multiple availability zones within a single region, so if one availability zone fails, the database remains available via another zone. Option E is correct because spreading replicas across independent availability zones eliminates the single-zone single point of failure, yielding a higher availability SLA than a single-zone deployment. Option A is incorrect because reducing read latency for globally distributed users requires geo-replication or read replicas in other regions, not zone redundancy within one region. Option B is incorrect because automatic failover to a paired region is a feature of geo-replication/auto-failover groups, not zone redundancy. Option D is incorrect because protection against a regional disaster requires cross-region replication such as active geo-replication or failover groups, whereas zone redundancy only protects against zone-level failures inside one region.

Answer

Reduce read latency for globally distributed users.

Answer

Automatic failover to a paired region.

Answer

Protection against a regional disaster.

Question 10

You have an Azure SQL Managed Instance configured with a failover group between two regions. The primary region becomes unavailable. You need to fail over to the secondary region with minimal data loss. What should you do?

Accepted Answer

Run 'az sql mi failover-group force-failover-allow-data-loss' from the Azure CLI. When the primary region is unavailable, a forced failover is required because the normal failover command waits for the primary to be reachable to synchronize data. The 'force-failover-allow-data-loss' command promotes the secondary with potential data loss, which is the only viable option when the primary is down and minimal (not zero) data loss is acceptable.

Answer

Add the secondary instance to an elastic pool and initiate failover

Answer

Run 'az sql mi failover-group failover' from the Azure CLI

Answer

Use Azure portal to initiate geo-failover for the managed instance

Question 11

A manufacturing company uses Azure SQL Database Hyperscale tier for its IoT telemetry data. The database is in West Europe and must be readable in East US for reporting with a maximum lag of 10 seconds. You configure a failover group with East US as the secondary region. After setup, reporting queries in East US show data that is 5 minutes old. What is the most likely cause?

Accepted Answer

The secondary database is configured with 'Geo' secondary type; you need to set it to 'Readable' to minimize lag. The most likely cause is that the secondary database is configured with the default 'Geo' secondary type, which is not readable and uses asynchronous replication with potentially high lag. To minimize lag and make the secondary readable for reporting, you must set the secondary type to 'Readable' (also called 'Named' secondary in some contexts). This ensures the secondary is kept in near-synchronous sync, though still asynchronous. Option A is incorrect because the failover policy does not affect replication lag. Option B is incorrect because the secondary can be made readable by changing the secondary type. Option D is incorrect because Hyperscale does not support synchronous replication, and changing to sync would not address the lag issue.

Answer

The failover group has 'Read/Write failover policy' set to 'Automatic' causing the lag

Answer

The secondary database is not readable; it is only for failover

Answer

The replication mode is set to 'Async' and should be changed to 'Sync'

Question 12

You are designing a disaster recovery plan for an Azure SQL Database that supports an e-commerce application. The application requires an RPO of 15 seconds and an RTO of 1 hour during a regional outage. Which solution should you recommend?

Accepted Answer

Deploy active geo-replication to a secondary region and automate failover. Deploy active geo-replication to a secondary region and automate failover. Active geo-replication provides an asynchronous secondary replica in a different region with an RPO of up to 5 seconds, meeting the 15-second requirement. By automating failover (e.g., via Azure PowerShell, CLI, or Azure Automation), the RTO can be minimized to under 1 hour. Option A, creating a failover group with automatic failover, also uses geo-replication but the automatic failover policy may introduce additional detection time, making it harder to guarantee an RTO of exactly 1 hour. Additionally, failover groups are designed for managing multiple databases, adding unnecessary complexity for a single database. Option C, long-term backup retention and restore, has an RPO of hours, not seconds. Option D, zone-redundant availability, protects against zone failures within a single region, not a regional outage.

Answer

Create a failover group with automatic failover policy

Answer

Use long-term backup retention and restore in another region

Answer

Configure zone-redundant availability for the database

Question 13

Your company has an Azure SQL Managed Instance in the UK South region. You need to implement a disaster recovery solution that provides automatic failover to a secondary region and allows read-only workloads to use the secondary replica. The solution must minimize data loss. What should you configure?

Accepted Answer

Deploy a second managed instance and use auto-failover groups. Auto-failover groups for Azure SQL Managed Instance provide automatic failover to a secondary region and allow the secondary instance to be used for read-only workloads. They also support a configurable grace period and ensure minimal data loss by using asynchronous replication with a potential data loss window. This is the only option that combines automatic regional failover with read-scale capability for a Managed Instance.

Answer

Create an availability group in the secondary region and configure automatic failover

Answer

Configure a failover group between two managed instances

Answer

Enable active geo-replication and configure auto-failover using PowerShell

Question 14

You are reviewing an Azure Policy definition that audits SQL Servers. Based on the exhibit, which condition triggers the audit effect?

Accepted Answer

The SQL Server uses service-managed TDE or has no encryption protector configured. The Azure Policy definition for auditing SQL Servers typically checks for the absence of a customer-managed key (CMK) encryption protector or the use of service-managed TDE. The audit effect triggers when the SQL Server uses service-managed TDE or has no encryption protector configured, indicating non-compliance with the requirement to use CMK. This matches option A.

Answer

The SQL Server uses a customer-managed key from Azure Key Vault

Answer

The SQL Server does not have an encryption protector set to a customer-managed key

Answer

The SQL Server is version 12.0 and uses a customer-managed key

Question 15

You manage an Azure SQL Database that uses active geo-replication with a secondary in a paired region. During a planned maintenance, you need to fail over to the secondary with zero data loss. What should you do?

Accepted Answer

Execute a planned failover (graceful failover) using Transact-SQL or PowerShell. A planned failover (graceful failover) synchronizes all transactions between the primary and secondary before switching, ensuring zero data loss. Option A uses a standard failover that may allow data loss if the secondary is not fully synchronized. Option B causes data loss by taking the primary offline abruptly. Option D explicitly allows data loss, which is not acceptable for this requirement.

Answer

Use Azure portal to 'Failover' without specifying data loss tolerance

Answer

Take the primary offline, then promote the secondary

Answer

Execute a forced failover allowing data loss