DP-300 Practice Question: Plan and configure high availability and disaster recovery
You manage a SQL Server 2019 availability group on Azure Virtual Machines. The availability group has three replicas: one primary and two synchronous secondary replicas in the same region. A fourth asynchronous replica is in a different Azure region for disaster recovery. During a planned maintenance window, you need to perform a manual failover to one of the synchronous secondary replicas without data loss. Which Transact-SQL command should you run on the target secondary replica?
⚠ Common exam trap
Many candidates confuse the command for a planned failover with the one for a forced failover that allows data loss.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
ALTER AVAILABILITY GROUP [AG1] FAILOVER;
To perform a planned manual failover without data loss in an availability group, you run ALTER AVAILABILITY GROUP ... FAILOVER on the target secondary replica. This command requires the secondary to be synchronized with the primary and ensures no data loss. The FORCE_FAILOVER_ALLOW_DATA_LOSS option is for unplanned failover with potential data loss, while the other options are configuration commands that do not initiate a failover.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON 'SQL2' WITH (FAILOVER_MODE = MANUAL);
Why it's wrong here
MODIFY REPLICA changes the failover mode of a replica (e.g., from automatic to manual) but does not trigger a failover. It is a configuration change, not an action to move the primary role. After setting the failover mode, you would still need to run a failover command. This option does not accomplish the required failover.
- ✓
ALTER AVAILABILITY GROUP [AG1] FAILOVER;
Why this is correct
The ALTER AVAILABILITY GROUP ... FAILOVER command performs a manual failover without data loss when the target secondary is synchronized. It is the correct command to use on the secondary replica that you want to become the new primary, ensuring no data loss because the secondary is synchronous and up to date. This meets the requirement for a planned failover without data loss.
- ✗
ALTER AVAILABILITY GROUP [AG1] SET (ROLE = SECONDARY);
Why it's wrong here
The SET (ROLE = SECONDARY) option is used in a failover cluster instance context to change the role of an availability group, not to perform a failover to a specific secondary replica. It does not initiate a failover from the primary to a secondary. This command is not the correct way to manually fail over an availability group.
- ✗
ALTER AVAILABILITY GROUP [AG1] FORCE_FAILOVER_ALLOW_DATA_LOSS;
Why it's wrong here
FORCE_FAILOVER_ALLOW_DATA_LOSS is used for forced failover when the primary is unavailable and you are willing to accept possible data loss. Using it during a planned maintenance window would unnecessarily risk data loss and is not the correct choice when a synchronized secondary is available. This command is for disaster recovery, not planned failover.
Go deeper
Related to this question
About these practice questions
This DP-300 question is part of Courseiva's 574-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Microsoft exam blueprint
This DP-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-300 exam.