Plan and configure high availability and disaster recovery practice questions

Deploy the database in the Business Critical tier with zone-redundant configuration in West US

Why it fails: Incorrect. Zone-redundant configuration within the same region does not protect against a full regional outage.

Enable geo-zone-redundant backup storage and perform point-in-time restore

Why it fails: Incorrect. Geo-zone-redundant backup storage and point-in-time restore have much higher RTO (hours) and cannot meet 5-second RPO.

Configure Active Geo-Replication with a readable secondary in East US

Why it fails: Incorrect. Active Geo-Replication requires manual failover, which typically takes several minutes, failing the RTO of 30 seconds.

Active Geo-Replication with a secondary in East US 2

Why it fails: Active Geo-Replication is not supported for SQL Managed Instance.

Enable geo-redundant backup storage and restore to West US

Why it fails: Geo-restore has RPO of hours, not seconds.

Set up a Failover Group with manual failover to a secondary in West US

Why it fails: Manual failover is not automatic.

Restore a geo-redundant backup to the secondary region

Why it fails: Restoring a backup takes too long for a drill.

Configure Active Geo-Replication to the secondary region

Why it fails: Active Geo-Replication is not supported for Hyperscale.

Create a named replica in the secondary region and fail over manually

Why it fails: Named replicas are for read scale-out, not disaster recovery.

Recovery Point Objective (RPO) of 1 second

Why it fails: Both have RPO of 5 seconds.

Support for SQL Server Authentication

Why it fails: Both support SQL Server Authentication.

The availability group listener is not configured correctly

Why it fails: Listener issues do not cause replica disconnection.

The primary instance encountered a critical error and failed over automatically

Why it fails: PENDING_FAILOVER indicates a manual or forced failover, not automatic.

The cluster has lost quorum

Why it fails: Quorum loss would show different symptoms; both replicas would be affected.

Add a second secondary replica to the auto-failover group in the same region.

Why it fails: During a regional outage, all replicas in the same region are affected.

Increase the service objective of the secondary replica in the auto-failover group to a higher DTU or vCore to handle the read load.

Why it fails: Scaling up does not add additional read replicas; the secondary still has limited capacity.

Change the database to the Hyperscale tier, which supports multiple readable replicas and automatic failover.

Why it fails: Hyperscale does not support auto-failover groups with the same RTO/RPO and changes the architecture.

Enable read-scale out on the secondary database.

Why it fails: Read-scale out changes the secondary to read-only connections; it does not affect failover capability. It is tempting because it is a common companion setting for readable secondaries, but automatic failover requires the failover group configuration instead, which this setting does not provide.

Set the secondary database to have the same service tier as the primary.

Why it fails: Automatic failover requires the secondary to be in the same service tier and compute generation as the primary, but matching tiers alone does not enable it; the failover group must be created. Identical tiers matter for performance symmetry, not for triggering failover.

Configure zone redundancy on the primary database.

Why it fails: Zone redundancy spreads replicas across availability zones within a single region, protecting against datacentre failure but providing no cross-region replica to promote. It is tempting because zone redundancy genuinely raises availability for a standalone database, and would be the right choice when the requirement is resilience within one region rather than automatic failover elsewhere.

4060: Login failed for user 'username'.

Why it fails: Incorrect — login failure is error 18456, not 4060. Error 4060 is about database access.

18456: Cannot open database requested by the login.

Why it fails: Incorrect — database open failure is error 4060, not 18456. Error 18456 is login failure.

The recovery model is set to Full instead of Simple.

Why it fails: Incorrect. Azure SQL Database always uses the full recovery model and does not support changing it to simple. Recovery model does not affect geo-replication seeding.

The primary database SalesDB is not zone-redundant.

Why it fails: Incorrect. Zone redundancy is not a prerequisite for geo-replication. Geo-replication can be configured on databases without zone redundancy.

The secondary database is being used as a read-only replica.

Why it fails: Incorrect. Using the secondary as a read-only replica does not prevent seeding. The secondary is normally readable after seeding completes, but the seeding process itself is not affected by read-only usage.

The failover policy is set to Automatic, but the grace period is too long.

Why it fails: Grace period is acceptable; the issue is backup redundancy.

The secondary managed instance mi2 is in the same region as the primary.

Why it fails: Secondary is in westus, primary in eastus; different regions.

The read-only endpoint failover policy is Disabled, which prevents read-only traffic after failover.

Why it fails: Read-only failover policy is not required for automatic failover.

The long-term retention policy interferes with point-in-time restore.

Why it fails: Incorrect. Long-term retention policies do not interfere with point-in-time restore; they operate independently.

The requested restore point is outside the 7-day retention period.

Why it fails: Incorrect. The requested date is within the 7-day retention period, so this is not the cause of error 406.

The automated backup frequency is 12 hours, so point-in-time restore is not supported.

Why it fails: Incorrect. Azure SQL Database supports point-in-time restore regardless of full/differential backup frequency because transaction log backups are taken frequently (every 5-10 minutes).

The geo-replication will continue seamlessly after the restore.

Why it fails: Geo-replication is disrupted when the primary database is restored to a different point.

The restore will fail because the database is in a geo-replication relationship.

Why it fails: Restore is allowed but will break replication.

The secondary database will automatically be restored to the same point in time.

Why it fails: The secondary is not automatically restored.

The secondary database is lagging behind the primary by a significant amount.

Why it fails: Lagging would show PENDING or less than 100% complete.

The secondary database is in the process of initial seeding and is not yet fully synchronized.

Why it fails: Initial seeding would show a replication state of SEEDING and percentComplete less than 100.

The replication link is broken and needs to be re-created.

Why it fails: A broken link would show a state like SUSPENDED or ERROR.

The backup vault is in a different region than the VM.

Why it fails: Cross-region backup is supported; this would not cause VM not found error.

The backup policy has been deleted, causing the VM to be unprotected.

Why it fails: The error is about VM not found, not policy.

The VM has been moved to a different resource group.

Why it fails: Moving would cause a different error, but the VM resource ID would change and backup might still work if updated.

The administrator must manually trigger the failover.

Why it fails: The policy is Automatic, so manual intervention is not required.

The databases will become read-only until the primary recovers.

Why it fails: The read-only endpoint is disabled, so no read access either. The databases will be unavailable until failover.

The secondary will immediately take over as the primary.

Why it fails: Automatic failover has a grace period; it's not immediate.

The database is being upgraded to a new service tier.

Why it fails: Upgrade would show 'Upgrading' or 'Scaling' status.

The database is corrupted and Azure is automatically repairing it.

Why it fails: Corruption would show 'Suspect' or 'Recovery Pending', not 'Restoring'.

The database is in the process of replication to a secondary region.

Why it fails: Replication status would show 'Replicating' or 'Synchronizing', not 'Restoring'.

The zone redundancy is enabled but geo-replication is not configured.

Why it fails: The 'replicas' array indicates geo-replication is configured, even if not fully ready.

The geo-replication failed because the SKU does not support it.

Why it fails: Premium SKU supports geo-replication.

The geo-replication is fully functional and ready for failover.

Why it fails: One replica is still provisioning, so not fully functional.

A read-scale out replica in West US and an Azure Traffic Manager profile.

Why it fails: Read-scale out replicas are for offloading read workloads within the same region and are not a disaster recovery mechanism across regions. Traffic Manager can route connections but does not replicate data or promote a secondary database. This approach cannot provide a writable copy in West US during a regional failure.

A geo-secondary in West US with auto-failover enabled on the database.

Why it fails: Azure SQL Database does not expose an auto-failover setting directly on a geo-secondary database. Automatic failover is a property of a failover group, not of the geo-replication link itself. Enabling auto-failover on the database is not a supported configuration, so this option cannot meet the requirement.

An active geo-replication secondary in West US and a manual failover runbook.

Why it fails: Active geo-replication creates a readable secondary, but failover to it is a manual operation and the application connection string must be updated to point to the new primary server. It does not provide automatic failover or transparent redirection, so it fails the automatic failover and no connection string change requirements.

Change the quorum configuration to use a cloud witness.

Why it fails: A cloud witness can be used for quorum in a cluster that spans multiple sites or to avoid a third site. However, in this scenario, both replicas are in the same region and a disk witness is already configured. Changing to a cloud witness does not directly enable automatic failover; the failover mode of the availability group must be set to automatic. The quorum type is not the limiting factor here.

Configure the secondary replica to asynchronous commit mode.

Why it fails: Asynchronous commit mode does not support automatic failover because the secondary may not have all the data. Automatic failover requires synchronous commit mode to ensure no data loss. Setting the secondary to asynchronous commit would actually prevent automatic failover, so it is not the correct action.

Add a third replica in a different region to the availability group.

Why it fails: Adding a third replica in a different region could provide additional disaster recovery, but it is not required for automatic failover between two replicas in the same region. The existing two replicas can support automatic failover if configured correctly. Adding a third replica increases complexity and cost without addressing the immediate need.

Enable active geo-replication and configure a failover policy with automatic failover.

Why it fails: Active geo-replication does not support automatic failover policies; failover must be initiated manually. It can be used for read-scale out and manual disaster recovery, but it does not meet the automatic failover requirement. Auto-failover groups are built on geo-replication and add automatic failover capabilities.

Use Azure SQL Database built-in backup and restore to a different region.

Why it fails: Built-in backups are stored in geo-redundant storage by default, but restoring to another region involves creating a new database from a geo-restored backup. This process can take longer than 1 hour for large databases and does not provide automatic failover. It also requires manual steps, increasing RTO and administrative effort.

Create a geo-replica in West US and manually initiate a failover when needed.

Why it fails: Geo-replicas provide asynchronous replication, but failover is manual. While the RPO can be low, the RTO depends on manual intervention, which may exceed 1 hour and requires administrative effort. Auto-failover groups are preferred for automatic failover and lower RTO. Additionally, connection strings must be updated after failover, increasing complexity.