19 questions · Plan and configure high availability and disaster recovery · All types, answers revealed
Drag and drop the steps to configure a SQL Server Agent job in Azure SQL Managed Instance to run a maintenance task in the correct order.
Drag steps to the numbered slots on the right, or tap a step then tap a slot.
Why this order
Connect to the instance, create a job, define steps, schedule, then enable and start.
Which TWO configurations are required to enable automatic failover for an Azure SQL Database configured with active geo-replication?
Failover group is required to orchestrate automatic failover.
Why this answer
Option B is correct because a failover group is the Azure feature that manages automatic failover for geo-replicated databases. It coordinates the failover of the primary and all secondaries in the group, providing a single endpoint and automatic failover policy. Without a failover group, active geo-replication only supports manual failover.
Exam trap
The trap here is that candidates often confuse the prerequisites for automatic failover with the features of active geo-replication alone, forgetting that a failover group is the specific construct that enables automation.
A company has an Azure SQL Managed Instance in the East US region. They need to implement disaster recovery with automatic failover to a paired region. The solution must minimize data loss to less than 5 seconds. Which feature should they use?
Auto-failover groups for managed instance provide automatic failover and RPO of 5 seconds.
Why this answer
Option C is correct because Auto-Failover Groups for Azure SQL Managed Instance provide automatic failover to a paired region (West US) with a replication lag target of less than 5 seconds when using the Readable Secondary option. This feature uses synchronous replication at the commit level to minimize data loss, meeting the RPO requirement of <5 seconds.
Exam trap
The trap here is confusing Active Geo-Replication (which is for Azure SQL Database, not Managed Instance) with Auto-Failover Groups, leading candidates to select Option A even though it is unsupported for Managed Instance.
How to eliminate wrong answers
Option A is wrong because Active Geo-Replication is not supported for Azure SQL Managed Instance; it is only available for Azure SQL Database. Option B is wrong because geo-redundant backup storage (GRS) provides point-in-time restore to another region but does not offer automatic failover or a sub-5-second RPO; recovery time can be hours and data loss depends on backup frequency. Option D is wrong because a Failover Group with manual failover does not provide automatic failover; the requirement explicitly states 'automatic failover,' and manual failover requires human intervention, which can increase downtime.
Match each Azure SQL Database security feature to its purpose.
Drag a concept onto its matching description — or click a concept then click the description.
Encrypts data at rest
Encrypts sensitive data in transit and at rest
Limits exposure of sensitive data by masking it to non-privileged users
Restricts access to rows based on user characteristics
Why these pairings
These features help protect data in Azure SQL Database.
A company runs a critical Azure SQL Database in the West US region. To meet a Recovery Point Objective (RPO) of 5 seconds and a Recovery Time Objective (RTO) of 30 seconds during a regional outage, which deployment option should be used?
Zone-redundant Business Critical provides fast failover within seconds and RPO of 5 seconds.
Why this answer
The Business Critical tier with zone-redundant configuration provides synchronous replication of data across three availability zones within the same Azure region, ensuring zero data loss (RPO=0) and automatic failover within seconds. This meets the stringent RPO of 5 seconds and RTO of 30 seconds during a regional outage because zone redundancy protects against zone-level failures, and the failover is automatic and fast. In contrast, other options either introduce asynchronous replication (which cannot guarantee an RPO of 5 seconds) or rely on cross-region failover that exceeds the required RTO.
Exam trap
The trap here is that candidates often assume cross-region replication (Auto-Failover Groups or Active Geo-Replication) is required for regional outages, but the question specifies a regional outage within the same region (West US), where zone-redundant configuration within the same region can meet the aggressive RPO and RTO, whereas cross-region options introduce asynchronous replication and longer failover times.
How to eliminate wrong answers
Option A is wrong because Auto-Failover Groups use asynchronous replication (typically with a lag of 5–30 seconds or more), which cannot guarantee an RPO of 5 seconds, and failover across regions (West US to West US 2) may take longer than 30 seconds due to DNS propagation and transaction log catch-up. Option C is wrong because geo-zone-redundant backup storage and point-in-time restore are designed for data recovery from backups, not for high availability or automatic failover; the RTO would be measured in hours, not seconds, due to the time required to restore a database. Option D is wrong because Active Geo-Replication uses asynchronous replication (typically with an RPO of 5–30 seconds or more), and failover to a secondary in East US would introduce cross-region latency and DNS changes, making it impossible to meet a 30-second RTO.
Refer to the exhibit. An administrator wants to restore the primary database SalesDB to a point in time 2025-03-15T09:00:00Z. What is the impact on the geo-replication?
Restoring the primary to a different point in time breaks the replication link.
Why this answer
Restoring the primary database to an earlier point in time will break the geo-replication link. The secondary database will become a regular database, and replication must be reconfigured after the restore. The restore is possible because the requested time is within the restore window.
Refer to the exhibit. After a brief outage, the availability group recovered. However, SQL2 shows NOT_HEALTHY and DISCONNECTED. What is the most likely cause?
The connection timeout error and the DISCONNECTED state suggest network issues.
Why this answer
The error 35201 indicates a connection timeout. SQL2 is configured for automatic failover and synchronous commit. After the restart, SQL2 remains disconnected and not healthy.
This is likely because the network connectivity between SQL1 and SQL2 is still interrupted or there is a persistent issue. Since SQL3 is connected and healthy, the issue is specific to SQL2.
Drag and drop the steps to configure automatic tuning for an Azure SQL Database in the correct order.
Drag steps to the numbered slots on the right, or tap a step then tap a slot.
Why this order
Automatic tuning is configured via the portal by selecting the database, navigating to automatic tuning, enabling options, and saving.
A company uses Azure SQL Database Hyperscale tier for a large database. They need to perform a disaster recovery drill by failing over to a secondary region with minimal data loss. The secondary is in a paired region and is readable. Which approach should they use?
Auto-failover groups support Hyperscale and allow failover with minimal data loss.
Why this answer
Auto-Failover Groups with Azure SQL Database Hyperscale provide automated, orchestrated failover to a secondary region with minimal data loss by using synchronous replication for the log service. This meets the requirement for a disaster recovery drill with a readable secondary and minimal data loss, as the secondary is kept in sync and can be failed over manually or automatically.
Exam trap
The trap here is that candidates confuse Active Geo-Replication (unsupported on Hyperscale) with Auto-Failover Groups (the correct feature for Hyperscale), or assume named replicas can be used for cross-region failover when they are strictly intra-region read-only replicas.
How to eliminate wrong answers
Option A is wrong because restoring a geo-redundant backup to the secondary region involves point-in-time recovery from backups, which can result in significant data loss (up to the last backup interval) and does not provide a readable secondary for ongoing reads or a seamless failover drill. Option B is wrong because Active Geo-Replication is not supported on Azure SQL Database Hyperscale; it is only available for other service tiers (General Purpose, Business Critical). Option C is wrong because named replicas in Hyperscale are read-only replicas within the same region and cannot be used for cross-region failover; they do not support manual failover to a secondary region.
Which TWO options are required to configure a SQL Server Always On Availability Group on Azure Virtual Machines?
Required for the listener.
Why this answer
A is correct because an Internal Load Balancer is required to route traffic to the primary replica in a SQL Server Always On Availability Group (AG) deployed on Azure Virtual Machines. The listener uses the ILB's frontend IP and health probe to direct client connections to the current primary node, as the Windows Server Failover Cluster (WSFC) does not support the cluster IP address in Azure without a load balancer.
Exam trap
The trap here is that candidates often think a VPN gateway is required for cross-region AGs, but the question asks for required options to configure the AG, and the ILB and WSFC are the only mandatory components; the VPN gateway is optional and only relevant for specific network topologies.
Which THREE factors should be considered when choosing between Azure SQL Database active geo-replication and auto-failover groups for disaster recovery?
Auto-failover groups provide automatic failover; active geo-replication does not.
Why this answer
Option A is correct because auto-failover groups provide automatic failover capability, which is essential for minimizing downtime during a disaster. Active geo-replication, on the other hand, requires manual or custom scripting to initiate failover, making it less suitable for scenarios where rapid, unattended recovery is needed.
Exam trap
The trap here is that candidates often assume a lower RPO is always a deciding factor, but both technologies provide the same RPO, so the key differentiators are automatic failover, the number of readable secondaries, and granular failover control.
Match each Azure SQL Database error code to its meaning.
Drag a concept onto its matching description — or click a concept then click the description.
Service is busy; retry the request later
Database is not currently available
Cannot open server '%.*ls' requested by the login
Login failed for user
Why these pairings
These are common error codes encountered when working with Azure SQL Database.
You are troubleshooting an Always On Availability Group named AG1. The exhibit shows the current state. The primary replica shows PENDING_FAILOVER. The secondary replica is DISCONNECTED with error 35202. What is the most likely cause of this issue?
Error 35202 often relates to connectivity issues on the mirroring endpoint port.
Why this answer
The PENDING_FAILOVER state on the primary replica indicates that a failover was initiated but could not complete because the secondary replica is DISCONNECTED. Error 35202 specifically indicates that a connection attempt to the secondary replica failed, which in an Always On Availability Group (AG) is typically due to a network connectivity issue on the dedicated endpoint port (default 5022). Since the secondary is unreachable, the failover cannot finalize, leaving the primary in a pending state.
Exam trap
The trap here is that candidates often confuse the availability group listener port (default 1433) with the replica-to-replica synchronization endpoint port (default 5022), leading them to incorrectly attribute the issue to listener misconfiguration rather than a blocked endpoint port.
How to eliminate wrong answers
Option B is wrong because the availability group listener is used for client connections and routing, not for replica-to-replica synchronization; a misconfigured listener would not cause the secondary to be DISCONNECTED with error 35202. Option C is wrong because if the primary encountered a critical error and failed over automatically, the primary would no longer be in PENDING_FAILOVER—it would have transitioned to a resolving or offline state, and the secondary would have become the new primary. Option D is wrong because cluster quorum loss would affect the entire cluster's ability to manage resources, typically resulting in both replicas being in an unresolved state or the AG going offline entirely, not a specific PENDING_FAILOVER on the primary with a DISCONNECTED secondary.
Refer to the exhibit. An administrator creates a geo-replica for SalesDB. After 30 minutes, the replication state remains SEEDING. What is the most likely cause?
Zone redundancy must be enabled on the primary database for geo-replication to seed successfully.
Why this answer
The exhibit shows zoneRedundant is false. Geo-replication requires zone redundancy enabled on the primary database because Azure SQL Database geo-replication is only supported for zone-redundant databases.
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?
Always On AG provides automatic failover and no shared storage.
Why this answer
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.
Exam trap
The trap here is that candidates often confuse Failover Cluster Instances (which require shared storage) with Always On Availability Groups (which do not), or they overlook that Database Mirroring is deprecated and lacks the listener feature for seamless client redirection.
How to eliminate wrong answers
Option A is wrong because Log Shipping provides only manual failover and does not support automatic failover; it also typically results in longer downtime during failover. Option B is wrong because a Failover Cluster Instance using Azure Shared Disks requires shared storage, which is explicitly not allowed by the requirement for no shared storage. Option D is wrong because Database Mirroring with automatic failover requires a witness server and is deprecated in SQL Server 2019; it also does not support an availability group listener for client redirection, making it less suitable for minimizing downtime during planned maintenance.
Refer to the exhibit. An administrator wants to ensure that during a regional outage, the failover group automatically fails over without data loss if possible. What is the current configuration gap?
Geo-redundant backup storage (GRS) is needed to protect backups across regions.
Why this answer
Automatic failover with data loss grace period is set to 60 minutes. The backup redundancy is Local, which means backups are stored only in the primary region. If a regional disaster occurs, backups might be lost.
Geo-redundant backups would be needed to recover in the secondary region without data loss.
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?
Synchronous replication provides RPO of 0 (within 5 minutes) and automatic failover can achieve RTO in minutes.
Why this answer
Option C is correct because 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.
Exam trap
The trap here is that candidates confuse Azure Site Recovery (VM-level replication) with database-level replication, assuming it provides SQL Server transaction consistency, when in fact it only offers crash-consistent or app-consistent snapshots that may not meet strict RPO/RTO for SQL Server.
How to eliminate wrong answers
Option A is wrong because Azure Site Recovery replicates the entire VM at the hypervisor level, not the SQL Server database level, which can cause data inconsistency and does not guarantee SQL Server transaction-consistent failover, nor does it meet the 5-minute RPO without additional log shipping. Option B is wrong because log shipping requires manual failover and has a built-in delay (typically 15-60 minutes) between log backup and restore, making it impossible to achieve a 5-minute RPO, and manual failover cannot meet the 30-minute RTO reliably. Option D is wrong because increasing transaction log backup frequency to every 5 minutes still relies on geo-restore from a Recovery Services vault, which involves restoring from geo-redundant storage (GRS) that can take hours due to large data volumes and network latency, failing the 30-minute RTO.
Refer to the exhibit. An administrator tries to restore the database OrdersDB to a point in time 2025-03-14 10:00 UTC and receives error 406. What is the most likely reason?
With 12-hour backup frequency, only full/differential backups are taken; no transaction logs are available for PITR.
Why this answer
Error 406 in Azure SQL Database indicates that the requested restore point is not available because it is outside the backup retention period. The automated backup frequency is 12 hours, so backups are taken every 12 hours. The last backup before 2025-03-14 10:00 would be at 2025-03-14 02:00.
The next backup after that is at 2025-03-14 14:00. Since the restore point 10:00 falls between backups, the database cannot be restored to that exact time unless transaction log backups are available. However, the error suggests that the point is not within the retention window.
The retention is 7 days, so 2025-03-14 is within 7 days from 2025-03-15. But the backup frequency of 12 hours means that the minimum restore point granularity is 12 hours. The requested time is not aligned with any available backup or log backup.
The error 406 specifically means 'The database is not in a state that allows recovery to the specified point in time.' This can happen if the database is in a geo-replication relationship or if the point is not covered by log backups. In this case, the automated backup frequency is 12 hours, so there are no log backups for point-in-time restore. The only available restore points are the full/differential backups at 02:00 and 14:00.
Therefore, the requested time 10:00 is not available.
You are the database administrator for a global e-commerce company. They run a mission-critical application on Azure SQL Database in the Business Critical tier. The database is 2 TB and experiences high write throughput. The current setup uses an auto-failover group with a secondary in the same region (West US) for high availability. The client application uses the auto-failover group listener with ReadScale=1 to route read-only queries to the secondary. Recently, during a regional outage that affected West US, the failover to the secondary succeeded, but the application experienced significant performance degradation and many timeouts for read operations. Investigation reveals that the secondary replica was overwhelmed with read traffic after failover. The business requires an RTO of 30 seconds and RPO of 5 seconds. The application must be able to handle read-heavy workloads even during a failover. You need to recommend a solution to improve read scalability and disaster recovery without changing the application code. What should you do?
This provides additional read capacity and disaster recovery.
Why this answer
Option B is correct because deploying an additional readable secondary replica in a different Azure region via active geo-replication provides both disaster recovery and read scalability. After a regional failover, the geo-secondary can serve read traffic without overwhelming the primary, meeting the RTO of 30 seconds and RPO of 5 seconds. The application can use the geo-secondary for reads by updating the connection string, without changing application code, ensuring read-heavy workloads are handled during failover.
Exam trap
The trap here is that candidates may think adding more replicas in the same region (Option A) or scaling the secondary (Option C) solves the problem, but they fail to recognize that a regional outage requires a geographically separate replica to ensure read scalability and disaster recovery.
How to eliminate wrong answers
Option A is wrong because adding a second secondary replica in the same region does not protect against a regional outage; both replicas would be affected, and the read traffic would still overwhelm the single surviving replica. Option C is wrong because increasing the service objective of the secondary replica does not address the root cause of read traffic overwhelming a single replica after failover; it only provides more resources but still relies on a single replica for reads, which can still be overwhelmed. Option D is wrong because changing to the Hyperscale tier, while supporting multiple readable replicas, does not guarantee the same RTO/RPO as the Business Critical tier with auto-failover groups, and it requires significant architectural changes that may not meet the strict RTO of 30 seconds.
Ready to test yourself?
Try a timed practice session using only Plan and configure high availability and disaster recovery questions.