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

Question 1

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 2

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 3

You need to configure a backup policy for Azure SQL Database that allows restoring to any point within the last 7 days. What is the minimum point-in-time restore retention period you should set?

Accepted Answer

7 days. Point-in-time restore retention can be set from 1 to 35 days. To restore to any point within 7 days, you need at least 7 days retention. Option A (1 day) is insufficient. Option C (14 days) is more than needed. Option D (3 days) is also insufficient.

Answer

1 day

Answer

14 days

Answer

3 days

Question 4

Which TWO of the following are benefits of using Azure SQL Database failover groups compared to active geo-replication? (Choose Two.)

Accepted Answer

Failover groups allow you to fail over multiple databases simultaneously.. Options A and B are correct. Failover groups allow multiple databases to fail over together as a unit (A), which is not possible with active geo-replication where each database must be failed over individually. Failover groups also provide a read-write listener endpoint that remains unchanged after a failover (B), simplifying application connection strings. Option C is incorrect because failover groups can be configured for automatic failover but do not automatically fail over without any manual intervention or a defined auto-failover policy. Option D is incorrect because both failover groups and active geo-replication provide readable secondary replicas, so this is not a distinct benefit. Option E is incorrect because failover groups still rely on geo-redundant storage; they do not eliminate the need for it.

Answer

Failover groups automatically fail over without any manual intervention.

Answer

Failover groups provide a readable secondary replica for read-only workloads.

Answer

Failover groups eliminate the need for geo-redundant storage.

Question 5

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.. Options A, B, and E are correct. A domain controller (B) is needed for Windows Server Failover Clustering. All VMs must be in the same availability set or availability zones (A) to ensure high availability. An internal load balancer (E) is required for the availability group listener to support automatic failover. Option C is incorrect because SQL Server authentication is not a prerequisite; Windows Authentication is used for cluster communication. Option D is incorrect because VMs can be in different regions, but automatic failover across regions is not supported without additional configuration (e.g., Azure Site Recovery).

Answer

All VMs must use SQL Server authentication.

Answer

All VMs must be in the same Azure region.

Question 6

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.. Zone-redundant configuration for Azure SQL Database replicates databases across multiple availability zones within the same region, providing protection against a single zone failure (option C) and offering higher availability than a single-zone deployment (option E). Option A is incorrect because zone redundancy does not reduce read latency; that is achieved by geo-replication or read scale-out. Option B is incorrect because automatic failover to a paired region is a feature of geo-redundant disaster recovery, not zone redundancy. Option D is incorrect because zone redundancy does not protect against a full regional disaster; it only protects within a region.

Answer

Reduce read latency for globally distributed users.

Answer

Automatic failover to a paired region.

Answer

Protection against a regional disaster.

Question 7

You are reviewing the ARM template snippet for an Azure SQL Database failover group. The primary server is in East US. The secondary is in West Europe. The readWriteEndpoint has automatic failover with a grace period of 60 minutes. The readOnlyEndpoint is disabled. After a complete outage in East US, what will happen?

Accepted Answer

The failover group will wait 60 minutes, then automatically fail over to West Europe.. With automatic failover policy and a grace period, the system will wait for 60 minutes before automatically failing over to West Europe. The read-only endpoint is disabled, so no read-only routing is available. Option A is incorrect because the grace period is 60 minutes, not zero. Option C is incorrect because automatic failover is enabled. Option D is incorrect because the failover will happen within 60 minutes, not immediately.

Answer

The failover group will not fail over automatically because the grace period is too long.

Answer

The failover group will immediately fail over to West Europe.

Answer

The failover group requires manual failover because readOnlyEndpoint is disabled.

Question 8

You query the sys.dm_geo_replication_link_status dynamic management view for an Azure SQL Database configured with active geo-replication. The exhibit shows the output. What does this indicate about the replication health?

Accepted Answer

The secondary is fully synchronized with minimal lag.. The replicationLag of 5 seconds is minimal and normal for active geo-replication, indicating the secondary is nearly synchronized. The replicationState 'CATCH_UP' confirms that the secondary is fully caught up with the primary. Options B, C, and D are incorrect: B is wrong because a secondary role is expected in geo-replication; C is wrong because 5 seconds lag is not a problem; D is wrong because the state is CATCH_UP, not SEEDING.

Answer

The secondary role indicates a failover has occurred.

Answer

The secondary is 5 seconds behind, indicating a problem.

Answer

The replication is in the seeding phase.

Question 9

You are preparing a disaster recovery runbook. You plan to use the PowerShell command shown in the exhibit to restore a database to a different region. What must be true for this command to succeed?

Accepted Answer

The source database must have geo-redundant backup storage configured.. The Restore-AzSqlDatabase cmdlet with the -FromGeoBackup parameter requires the source database to have geo-redundant backup storage (also known as geo-redundant storage) enabled. This allows restoration from geographically replicated backups. Option B is incorrect because point-in-time restore is not required for a geo-restore; geo-restore uses the geo-redundant backup independently. Option C is incorrect because the source database does not need to be online at the time of restoration; geo-backups are stored in Azure storage and are available even if the source database is offline. Option D is incorrect because the purpose of geo-restore is to restore to a different region; therefore, the target server can be in a different region than the source.

Answer

The source database must have point-in-time restore enabled.

Answer

The source database must be online at the time of restoration.

Answer

The target server must be in the same region as the source server.

Question 10

Your company runs a mission-critical Azure SQL Database in the East US region. To meet an RPO of 5 seconds and an RTO of 30 minutes in the event of a regional outage, which deployment option should you choose?

Accepted Answer

Failover groups with auto-failover policy. Failover groups with auto-failover policy provide automatic failover with an RPO of 5 seconds (default) and RTO of 30 minutes. Active geo-replication requires manual failover, which does not meet the RTO. Zone-redundant configuration only protects within a region. Backup restore has much higher RPO/RTO.

Answer

Zone-redundant configuration

Answer

Point-in-time restore

Answer

Active geo-replication with manual failover

Question 11

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. During a disaster where the primary region is unavailable, the correct action is to force a failover with potential data loss. The Azure CLI command 'az sql mi failover-group force-failover-allow-data-loss' (Option A) performs this forced failover, accepting the risk of data loss to restore availability. Option B is incorrect because adding to an elastic pool is unrelated to failover groups. Option C is incorrect because 'az sql mi failover-group failover' is not a valid command; the correct command includes 'force-failover-allow-data-loss'. Option D is incorrect because the Azure portal does not directly support geo-failover for managed instances; the CLI or PowerShell must be used for forced failover.

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 12

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 13

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?

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

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 14

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. For Azure SQL Managed Instance, failover groups provide automatic failover to a secondary region and allow read-only workloads to use the secondary replica, minimizing data loss. Option B is incorrect because availability groups are not supported for Azure SQL Managed Instance; they are for SQL Server on Azure VMs. Option C is essentially the same as A but less precise; the standard solution is to deploy a second managed instance in the secondary region and configure a failover group. Option D is incorrect because active geo-replication is a feature of Azure SQL Database, not Azure SQL 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 15

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 policy 'if' condition requires allOf: type is 'Microsoft.Sql/servers', and anyOf: encryption protector does not exist OR encryption protector is 'ServiceManaged'. This audits SQL servers that either use service-managed TDE or have no encryption protector configured. Option B is incorrect because the policy does not trigger when a customer-managed key is used; it specifically targets those without a customer-managed key. Option C is incorrect because the condition checks for absence of encryption protector or service-managed, not the absence of customer-managed key. Option D is incorrect because version 12.0 is checked but it is not the trigger; the trigger is based on encryption protector type.

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