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

Question 1

Your company has an Azure SQL Database that uses a failover group with a secondary in a different region. You need to ensure that read-only queries are directed to the secondary database to offload the primary. What should you configure?

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

Accepted Answer

Add ApplicationIntent=ReadOnly to the connection string and use the failover group listener.. Add ApplicationIntent=ReadOnly to the connection string and use the failover group listener. Azure SQL Database failover groups provide a read-only listener endpoint. When a client connects to the failover group listener with ApplicationIntent=ReadOnly in the connection string, the connection is automatically routed to the secondary database. This offloads read-only queries from the primary. Option B is incorrect because Traffic Manager is not needed; the failover group listener already handles routing. Option C is incorrect because read-scale is a feature for querying readable secondaries in a Business Critical tier, but it does not automatically route read-only queries to a geo-secondary. Option D is incorrect because using a separate connection string pointing directly to the secondary server defeats the purpose of automatic failover and redirection; the listener provides automatic routing and failover.

Answer

Use a Traffic Manager profile to route read traffic.

Answer

Enable read-scale for the database.

Answer

Configure a separate connection string pointing to the secondary server.

Question 2

You are troubleshooting a failover group for Azure SQL Database. The automatic failover is not triggering as expected during a regional outage. You verify that the grace period for data loss is set to 3600 seconds. The outage lasts 30 minutes. What is the most likely reason the automatic failover did not occur?

Accepted Answer

The outage duration is less than the grace period for data loss.. The grace period for data loss is set to 3600 seconds (1 hour), and the outage lasted only 30 minutes. Automatic failover for Azure SQL Database failover groups will not occur until the grace period expires, as the system waits for the outage to resolve within that time to avoid unnecessary failover and potential data loss. Option A is incorrect because the failover policy is not specified as manual; if it were manual, the automatic failover would not occur regardless of the grace period. Option C is incorrect because the grace period is actually longer than the outage duration, so it is not too short. Option D is incorrect because there is no indication that the secondary region is also experiencing an outage.

Answer

The failover policy is set to manual.

Answer

The grace period for data loss is too short.

Answer

The secondary region is also experiencing an outage.

Question 3

You need to create a disaster recovery plan for an Azure SQL Database that has an RPO of 1 hour and an RTO of 12 hours. Which solution should you use?

Accepted Answer

Geo-restore of automated backups.. Geo-restore of automated backups can achieve an RPO of 1 hour (backups are taken every 5-10 minutes, allowing point-in-time restore within that window) and an RTO of up to 12 hours, meeting the stated requirements. Option A is incorrect because failover groups with automatic failover provide much lower RPO (seconds to minutes) and RTO (minutes), which exceeds the required thresholds and typically incurs higher cost. Option C is incorrect because active geo-replication also offers very low RPO (seconds) and RTO (minutes), exceeding requirements. Option D is incorrect because zone-redundant configuration only protects against an availability zone failure within a region, not against a regional disaster.

Answer

Failover group with automatic failover.

Answer

Active geo-replication.

Answer

Zone-redundant configuration.

Question 4

Refer to the exhibit. You have an Azure SQL Database failover group configured with the ARM template snippet shown. You need to ensure that read-only queries are routed to the secondary region when the primary is healthy. What should you modify?

Accepted Answer

Set the readOnlyEndpoint failoverPolicy to Enabled.. The readOnlyEndpoint failoverPolicy must be set to Enabled to route read-only queries to the secondary region when the primary is healthy. Option A is incorrect because the readWriteEndpoint failoverPolicy being Manual does not affect read-only routing; it controls failover initiation. Option C is incorrect because adding databases to the partnerServers array is unrelated to read-only routing. Option D is incorrect because changing the failoverGracePeriodMinutes to 0 does not enable read-only endpoint routing.

Answer

Set the readWriteEndpoint failoverPolicy to Manual.

Answer

Add the database to the partnerServers array.

Answer

Change the failoverGracePeriodMinutes to 0.

Question 5

Refer to the exhibit. You are configuring a backup policy for an Azure SQL Database. The database is in the East US region. You need to ensure that the database can be restored to a different region in the event of a regional disaster. Which setting in the exhibit must be correctly configured?

Accepted Answer

The backupStorageRedundancy is set to Geo.. BackupStorageRedundancy must be set to 'Geo' to enable geo-redundant backups that can be restored to a different region in case of a regional disaster. Option A is incorrect because the geoBackupPolicy state being Enabled simply enables the geo-backup policy, but it does not ensure geo-redundancy if the storage redundancy is not set to Geo. Option B is incorrect because the geoBackupPolicy location indicates where the geo-backup is stored, but the key setting for cross-region restore is the storage redundancy, not the location. Option D is incorrect because weeklyRetention defines how long weekly backups are retained, which does not affect the ability to restore to a different region.

Answer

The geoBackupPolicy state is Enabled.

Answer

The geoBackupPolicy location is set to eastus2.

Answer

The weeklyRetention is set to P4W.

Question 6

Refer to the exhibit. You have an Azure SQL Managed Instance configured with the failover group properties shown. The primary region experiences an outage. After 30 minutes, the failover group has not failed over. You need to force failover immediately. What should you do?

Accepted Answer

Use the Switch-AzSqlDatabaseFailoverGroup cmdlet with -AllowDataLoss.. When the failover group has an automatic failover policy and the primary region experiences an outage, the automatic failover will only occur after the grace period (typically 1 hour) expires. Since only 30 minutes have passed, the failover has not yet triggered automatically. To force an immediate failover, you can use the Switch-AzSqlDatabaseFailoverGroup cmdlet with the -AllowDataLoss parameter, which initiates a forced failover that may result in data loss. Option A is incorrect because changing the failover policy to Manual does not force a failover; it only changes the policy for future failovers. Option B is incorrect because setting secondaryType to Standby is not relevant to initiating a failover. Option D is incorrect because waiting for the grace period to expire would eventually result in automatic failover, but the requirement is to force failover immediately.

Answer

Change the failover policy to Manual and then fail over.

Answer

Set the secondaryType to Standby.

Answer

Wait for the grace period to expire.

Question 7

Which TWO of the following are benefits of using Azure SQL Database failover groups for high availability?

Accepted Answer

Automatic failover to a secondary region based on a grace period.. Failover groups provide automatic failover to a secondary region based on a grace period (option B), allowing for continuous availability during regional outages. They also support routing read-only queries to the secondary region (option C) to offload read workloads and improve performance. Option A is incorrect because synchronous replication across availability zones is achieved through zone-redundant configuration, not failover groups. Option D is incorrect because load balancing read-write traffic across replicas is not a feature of failover groups; that is handled by read-scale replicas or connection policies. Option E is incorrect because geo-redundant backup storage is a separate feature (geo-redundant storage), not a direct benefit of failover groups.

Answer

Synchronous replication of data across availability zones.

Answer

Load balancing read-write traffic across multiple replicas.

Answer

Geo-redundant backup storage for disaster recovery.

Question 8

Which THREE factors should you consider when choosing between active geo-replication and failover groups for an Azure SQL Database disaster recovery solution?

Accepted Answer

Failover groups require all databases in the group to fail over together.. The correct answers are A, C, and E. Option A is correct because failover groups fail over all databases in the group together. Option C is correct because failover groups provide a single readable/writable listener endpoint. Option E is correct because active geo-replication supports up to 4 readable secondaries. Option B is incorrect: active geo-replication is available in both the Premium and Business Critical tiers, not just Business Critical. Option D is incorrect: failover groups support readable secondaries.

Answer

Active geo-replication is only available in the Business Critical tier.

Answer

Failover groups do not support readable secondaries.

Question 9

Which TWO of the following are valid methods to recover an Azure SQL Database after a regional outage?

Accepted Answer

Fail over to a secondary region using a failover group.. Options B and E are correct. Failover groups with automatic failover allow recovery by failing over to a secondary region (B). Geo-restore from geo-redundant backups restores the database to any Azure region using the most recent geo-redundant backup (E). Option A is incorrect because log shipping is not supported for Azure SQL Database. Option C is incorrect because point-in-time restore can only restore to a point in time within the same region. Option D is incorrect because copying a database from a geo-redundant backup is not a direct method; you must perform a geo-restore.

Answer

Replicate the database from a backup to a secondary region using log shipping.

Answer

Point-in-time restore to a time before the outage.

Answer

Copy the database from a geo-redundant backup to a new server.

Question 10

You manage a critical SQL Server on Azure Virtual Machine running SQL Server 2019. The VM is in a single availability set. You need to ensure automatic failover in case of a zone-level failure. What should you implement?

Accepted Answer

Deploy the VM in an availability zone and configure an Always On availability group with a secondary replica in another zone.. Deploying the VM in an availability zone and configuring an Always On availability group with a secondary replica in another zone ensures automatic failover if the primary zone fails. Option B is incorrect because an availability set only protects against failures within a datacenter, not zone-level failures. Option C is incorrect because geo-replication is designed for regional failure recovery, not zonal failover. Option D is incorrect because automated backups with long-term retention do not provide failover capabilities.

Answer

Place the VM in an availability set.

Answer

Enable geo-replication to another Azure region.

Answer

Configure automated backups with long-term retention.

Question 11

Your company uses Azure SQL Database in a General Purpose service tier. You need to minimize downtime during a planned patching event. Which feature should you enable?

Accepted Answer

Zone-redundant configuration. Zone-redundant configuration for Azure SQL Database provides high availability by replicating across zones, minimizing downtime during planned patching events. Option A is incorrect because auto-failover groups are primarily for disaster recovery across regions. Option B is incorrect because read scale-out only offloads read workloads to a readable secondary replica, which does not reduce downtime during patching of the primary. Option C is incorrect because active geo-replication is for disaster recovery, not for reducing downtime during patching within a region.

Answer

Auto-failover group

Answer

Read scale-out

Answer

Active geo-replication

Question 12

You have a SQL Server 2022 on Azure VM configured with a Basic availability group for a line-of-business application. The application requires automatic failover without manual intervention. What additional component is needed?

Accepted Answer

Windows Server Failover Cluster (WSFC). A Basic availability group on SQL Server 2022 Azure VM requires a Windows Server Failover Cluster (WSFC) for automatic failover. Without WSFC, automatic failover is not possible. Azure Load Balancer is used for client connectivity via the listener, but does not provide automatic failover. Virtual network gateway is for site-to-site connectivity. Availability group listener requires WSFC to function and does not itself enable automatic failover.

Answer

Azure Load Balancer

Answer

Virtual network gateway

Answer

Availability group listener

Question 13

You have an Azure SQL Database configured with active geo-replication to a secondary region. The primary region experiences a full outage. You need to fail over with minimal data loss. What should you do?

Accepted Answer

Initiate an unplanned failover from the primary to the secondary.. During a full outage, the primary is offline, so a planned failover (which requires connectivity to the primary to synchronize) is not possible. The only option to fail over with minimal data loss is to initiate an unplanned (forced) failover, which will make the secondary the new primary. Although there may be some data loss from transactions not yet replicated, this is the best available option. Deleting the secondary or creating a new secondary does not fail over.

Answer

Delete the secondary database and create a new one in the primary region.

Answer

Initiate a planned failover from the primary to the secondary.

Answer

Create a new secondary database in the same region as the primary.

Question 14

You need to design a disaster recovery plan for an Azure SQL Managed Instance. The recovery time objective (RTO) is 1 hour, and the recovery point objective (RPO) is 15 minutes. What is the most cost-effective solution?

Accepted Answer

Create an auto-failover group with a secondary instance in a paired region.. Auto-failover groups (Option C) are the most cost-effective solution for Azure SQL Managed Instance with an RTO of 1 hour and RPO of 15 minutes. They provide an RTO of ~1 hour and an RPO of 5 seconds to 5 minutes, meeting the requirements at low cost. Option A is incorrect because active geo-replication is not supported for SQL Managed Instance. Option B is incorrect because log shipping is not natively supported for Managed Instance. Option D is incorrect because point-in-time restore has a longer RTO and RPO, typically not meeting the 15-minute RPO requirement.

Answer

Configure active geo-replication between instances in different regions.

Answer

Set up log shipping to a secondary instance.

Answer

Use point-in-time restore to a secondary region.

Question 15

You have a SQL Server on Azure VM running a mission-critical database. The VM is configured with Azure Site Recovery (ASR) for disaster recovery. During a disaster recovery drill, you notice that the recovered database is not consistent. What is the most likely cause?

Accepted Answer

Application-consistent snapshots are not enabled in the ASR replication policy.. Azure Site Recovery (ASR) replicates at the VM level, and without application-consistent snapshots enabled in the replication policy, the recovered database may not be transactionally consistent. Option A is incorrect because ASR supports cross-region replication to a secondary region that may be in a different geo. Option C is incorrect because backup retention period does not affect ASR replication consistency. Option D is incorrect because ASR does not require the database to be part of an Always On availability group.

Answer

The secondary region is not in the same geo as the primary.

Answer

The backup retention period is too short.

Answer

The database is not part of an Always On availability group.