DP-300 Plan and configure high availability and disaster recovery Practice Test 3 — 15 Questions

Question 1

You manage a business-critical Azure SQL Managed Instance named MI1 in the West Europe region. The compliance team requires that the instance remains available even if an entire Azure region fails. You need to implement a disaster recovery solution that provides a readable secondary in a paired region and allows for manual failover. The solution must minimize administrative effort. What should you do?

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

Accepted Answer

Configure an auto-failover group between MI1 and a secondary managed instance in North Europe.. Auto-failover groups are the recommended solution for cross-region disaster recovery for Azure SQL Managed Instance. They provide a readable secondary in a paired region and support manual failover, which aligns with the compliance requirement. They also minimize administrative effort compared to custom replication or backup restore strategies. The other options either do not provide a readable secondary, require manual failover initiation, or are not supported.

Answer

Configure an availability group between MI1 and a secondary managed instance in North Europe.

Answer

Set up transactional replication from MI1 to a SQL Server on an Azure VM in North Europe.

Answer

Enable geo-redundant backups for MI1.

Question 2

You manage an Azure SQL Database in the General Purpose service tier. The database has a business-critical workload, and the CIO requires a disaster recovery plan that includes a secondary region. You need to configure a failover group that meets these requirements: (1) The secondary database must be readable for reporting. (2) The failover group must support automatic failover. (3) The solution must minimize data loss. Which two actions should you perform? (Choose two.)

Accepted Answer

Configure the failover policy to Automatic with a grace period of at least one hour.. To configure a failover group for Azure SQL Database, you must create a secondary server in the target region and add the primary database to the failover group, which automatically provisions the secondary database. You must also set the failover policy to Automatic with a grace period to enable automatic failover. These two actions together satisfy the requirements for a readable secondary, automatic failover, and minimal data loss.

Answer

Create a geo-replica of the database in the target region and then add it to the failover group.

Answer

Set the secondary database to read-only by using the ApplicationIntent=ReadOnly parameter.

Answer

Enable geo-redundant backup storage on the primary database.

Question 3

You manage an Azure SQL Managed Instance in the East US region. The instance hosts a mission-critical database that must be available in a secondary region in case of a regional outage. You need to configure a disaster recovery solution that provides a readable secondary and allows manual failover. What should you do?

Accepted Answer

Configure an auto-failover group between the primary managed instance and a secondary managed instance in West US.. Auto-failover groups are the only native cross-region disaster recovery solution for Azure SQL Managed Instance that provides a readable secondary and supports manual failover. They replicate databases asynchronously and allow the secondary to be used for read-only workloads. Failover can be initiated manually or automatically, depending on configuration.

Answer

Create a geo-secondary managed instance using the Azure portal and configure failover manually.

Answer

Configure transactional replication to a SQL Server on Azure VM in West US.

Answer

Use Azure SQL Managed Instance built-in backups and restore to a new instance in West US.

Question 4

You are designing a high-availability solution for SQL Server on Azure Virtual Machines. The solution must use an Always On availability group with synchronous-commit mode and automatic failover. The VMs are in the same Azure region. You need to configure the environment to meet these requirements. Which two actions should you perform? (Choose two.)

Accepted Answer

Create an internal load balancer and configure it as the listener for the availability group.. To configure an Always On availability group on Azure VMs with automatic failover, you must ensure the VMs are placed in an availability set or availability zones to provide fault tolerance. You also need an internal load balancer to serve as the listener, as Azure does not support floating IP addresses directly on VMs. These two actions are essential for the availability group to function and be accessible.

Answer

Configure a cloud witness as the quorum witness.

Answer

Enable Accelerated Networking on all VMs.

Answer

Configure the availability group to use asynchronous-commit mode.

Question 5

A company runs a mission-critical SQL Server 2019 on an Azure Virtual Machine. The database is part of an Always On Availability Group with two replicas in the same region. The company wants to implement a disaster recovery solution to a different Azure region with minimal data loss and automatic failover. The solution must minimize administrative effort. What should you implement?

Accepted Answer

Configure a distributed availability group between the primary availability group and a new availability group in the secondary region.. A distributed availability group is designed for cross-region disaster recovery for SQL Server. It allows automatic failover between two availability groups, providing minimal data loss and reduced administrative effort compared to manual solutions like log shipping. Log shipping lacks automatic failover, Azure Site Recovery operates at the VM level and does not ensure database consistency, and geo-replicas are not available for SQL Server on VMs.

Answer

Implement log shipping from the primary replica to a standby server in the secondary region.

Answer

Use Azure Site Recovery to replicate the SQL Server VMs to the secondary region and configure a recovery plan.

Answer

Create a geo-replica of the Azure SQL Database and configure auto-failover groups.

Question 6

You are the database administrator for a SQL Server 2019 Always On availability group on Azure Virtual Machines. The availability group has three synchronous replicas in the same region and one asynchronous replica in a remote region. You need to configure the availability group to automatically fail over to the remote region if the entire primary region fails, while minimizing data loss. What should you configure?

Accepted Answer

Create a distributed availability group between the primary availability group and a secondary availability group in the remote region.. A distributed availability group is the correct solution for cross-region disaster recovery with SQL Server Always On. It allows a primary availability group and a secondary availability group in different regions to be linked, with asynchronous replication to minimize latency impact. You can configure automatic failover for the distributed availability group if the remote availability group is set up with synchronous replicas internally. This provides regional DR while minimizing data loss.

Answer

Use Azure Site Recovery to replicate the entire virtual machine to the remote region and perform a failover.

Answer

Add the remote replica to the same availability group and set it to synchronous commit with automatic failover.

Answer

Configure the remote replica as a log shipping secondary and automate failover by using SQL Server Agent jobs.

Question 7

You are configuring an Azure SQL Database for high availability. The database is in the General Purpose service tier. You need to ensure that the database remains available during a planned maintenance event. What should you do?

Accepted Answer

Enable zone redundancy.. Zone redundancy for Azure SQL Database replicates the database across multiple availability zones within a region, providing high availability and automatic failover during planned maintenance or unplanned outages. It is the recommended feature to ensure availability without manual intervention.

Answer

Create a geo-replica in another region.

Answer

Configure an auto-failover group.

Answer

Use read-scale out with a secondary replica.

Question 8

You manage an Azure SQL Database named InventoryDB in the North Europe region. The database is business-critical, and the company wants to ensure that it can be recovered to any point in time within the last 35 days. The company also wants to minimize costs. What should you configure?

Accepted Answer

Set the point-in-time restore retention period to 35 days.. The requirement is to recover the database to any point in time within the last 35 days. Azure SQL Database's built-in point-in-time restore retention can be configured up to 35 days. Configuring this setting directly addresses the requirement without additional cost. Other options either do not provide point-in-time restore, increase cost unnecessarily, or are for different purposes.

Answer

Set the backup storage redundancy to geo-redundant.

Answer

Configure a long-term retention policy to retain backups for 35 days.

Answer

Enable active geo-replication to a secondary database in West Europe.

Question 9

You are configuring a high availability solution for an Azure SQL Managed Instance. The instance must remain available if a single availability zone fails. You need to enable zone redundancy. Which two actions must you perform? (Choose two.)

Accepted Answer

Configure the managed instance with the Business Critical service tier.. To enable zone redundancy for Azure SQL Managed Instance, you must deploy the instance in a region that supports availability zones and use the Business Critical service tier. These two conditions are prerequisites. Zone redundancy is then automatically configured, providing a secondary replica in a different zone. Auto-failover groups and read-scale out are not part of this configuration.

Answer

Configure a read-scale out with a readable secondary replica in a different zone.

Answer

Create an auto-failover group with a secondary managed instance in another region.

Answer

Enable the zone redundancy option during the managed instance creation or update.

Question 10

You are planning a disaster recovery strategy for a SQL Server 2019 Always On availability group on Azure Virtual Machines. The availability group spans two Azure regions. You need to ensure that the cluster can automatically fail over to the secondary region if the primary region fails. Which two actions should you perform? (Choose two.)

Accepted Answer

Configure the availability group to use synchronous-commit mode with automatic failover.. To enable automatic failover for a cross-region Always On availability group, you must configure a cloud witness for quorum and set the availability group to synchronous-commit mode with automatic failover. These two actions ensure that the cluster maintains quorum and that the secondary replica is synchronized, allowing automatic failover without data loss.

Answer

Set the availability group to asynchronous-commit mode.

Answer

Enable Azure Site Recovery for the SQL Server VMs.

Answer

Create a load balancer for the availability group listener.

Question 11

A company runs SQL Server 2019 on an Azure Virtual Machine. They have configured an Always On Availability Group with two replicas in the same region. They now need to add a disaster recovery replica in a different Azure region. The solution must support automatic failover and minimize data loss. What should you do?

Accepted Answer

Create a new availability group in the secondary region and configure a distributed availability group between the two availability groups.. A distributed availability group is the correct solution for cross-region disaster recovery for SQL Server on Azure VMs. It connects two separate availability groups, supports automatic failover, and uses asynchronous replication to minimize data loss. Adding a replica to the existing group is not supported for automatic failover, log shipping lacks automatic failover, and Azure Site Recovery operates at the VM level without database-level failover automation.

Answer

Use Azure Site Recovery to replicate the SQL Server VMs to the secondary region and configure a recovery plan for automatic failover.

Answer

Add a new replica to the existing availability group in the secondary region and configure it for automatic failover.

Answer

Implement log shipping to the secondary region and configure a monitor server to automate failover.

Question 12

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 13

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

Accepted Answer

Internal Load Balancer. Option A (Internal Load Balancer) is required because in Azure, the availability group listener needs an Azure Internal Load Balancer to route client connections to the current primary replica, since Azure networking does not support the traditional floating IP/ARP method used on-premises. Option C (Windows Server Failover Cluster) is required because Always On Availability Groups are built on top of a WSFC, which provides the cluster quorum, health detection, and failover orchestration for the SQL Server replicas on the Azure VMs. Option B is not required because a cloud witness (an Azure Storage account blob) is the typical witness for a WSFC in Azure, not an Azure Files share. Option D is incorrect because Azure SQL Database is a separate PaaS offering and cannot host the SQL Server instances that form an Always On Availability Group on VMs. Option E is not required because a VPN gateway is only needed for cross-region or hybrid connectivity, not for the core configuration of an availability group within Azure.

Answer

Azure Files share for witness

Answer

Azure SQL Database

Answer

VPN gateway between regions

Question 14

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 15

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.