CCNA Plan and configure a high availability and disaster recovery environment Questions

The most likely cause is that the secondary database is configured with the default 'Geo' secondary type, which is not readable and uses asynchronous replication with potentially high lag. To minimize lag and make the secondary readable for reporting, you must set the secondary type to 'Readable' (also called 'Named' secondary in some contexts). This ensures the secondary is kept in near-synchronous sync, though still asynchronous.

Option A is incorrect because the failover policy does not affect replication lag. Option B is incorrect because the secondary can be made readable by changing the secondary type. Option D is incorrect because Hyperscale does not support synchronous replication, and changing to sync would not address the lag issue.

The correct actions are C, D, and E. For a planned failover during a maintenance window, you should initiate a planned failover (no data loss) from the Azure portal or PowerShell (E) to ensure all transactions are synchronized before switching. After failover, verify that the new secondary (in West Europe) is configured as a readable secondary (C) to maintain disaster recovery capabilities.

Additionally, update the application connection strings to point to the new primary in North Europe (D). Option A is incorrect because a forced failover could cause data loss and is intended for unplanned outages. Option B is incorrect because seeding of the new secondary happens automatically via geo-replication.

Options A, B, and E are correct. A domain controller (B) is needed for Windows Server Failover Clustering. All VMs must be in the same availability set or availability zones (A) to ensure high availability.

An internal load balancer (E) is required for the availability group listener to support automatic failover. Option C is incorrect because SQL Server authentication is not a prerequisite; Windows Authentication is used for cluster communication. Option D is incorrect because VMs can be in different regions, but automatic failover across regions is not supported without additional configuration (e.g., Azure Site Recovery).

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.

Setting the failover group's secondary type to 'readable' enables read-only access to the secondary database for reporting, while preserving the failover group's DR capabilities. Option A is correct because configuring active geo-replication in addition to the failover group creates a separate readable secondary database that does not interfere with the existing failover group's DR, providing an alternative method for read-only workloads. Option C is incorrect because adding a zone-redundant replica (a high-availability feature across availability zones) does not affect the readability of the secondary in a failover group.

Option B is incorrect because read scale-out on the primary only allows read-only connections to the primary, not the secondary. Option D is incorrect because changing the secondary's service tier to Business Critical does not automatically make it readable; readability is controlled by the failover group's secondary type.

To meet the requirements of zero data loss (RPO=0) and RTO of 30 seconds across regions, a failover group with a readable secondary in a paired region using Business Critical service tier is the best option. Business Critical provides synchronous replication within a region but asynchronous geo-replication across regions; however, a planned failover ensures zero data loss by synchronizing all changes before failover. The readable secondary reduces read load on the primary.

Option A is incorrect because active geo-replication is asynchronous and cannot guarantee zero data loss. Option B is incorrect because it does not make the secondary readable, missing the read workload requirement. Option D is incorrect because zone redundancy protects within a region but not against regional disasters.

Auto-failover groups with a readable secondary in East Asia using Business Critical tier provide automatic failover meeting RTO of 30 seconds, and asynchronous geo-replication typically achieves RPO of 5 seconds for Azure SQL Database. The readable secondary can also handle read-only workloads. This configuration offers the lowest write latency since writes are acknowledged locally without waiting for cross-region synchronization.

Option A is incorrect because geo-restore has RPO of 1 hour and RTO of several hours. Option B is incorrect because zone redundancy is intra-region and Traffic Manager does not ensure RPO/RTO. Option D is incorrect because active geo-replication is asynchronous, not synchronous, and synchronous cross-region replication is not supported; it would increase write latency.

The correct cmdlet is New-AzSqlDatabaseSecondary (Option C). When the primary database is in an elastic pool, using the -SecondaryElasticPoolName parameter creates a new elastic pool on the secondary server in a different region if it does not already exist. This makes it suitable for creating the secondary pool as part of failover group setup.

Option A (Set-AzSqlDatabaseSecondary) modifies an existing geo-replication relationship, not create a new one. Option B (Add-AzSqlDatabaseToFailoverGroup) adds an existing database to a failover group, but the secondary pool must already be provisioned. Option D (New-AzSqlDatabaseFailoverGroup) creates the failover group itself, not the secondary elastic pool.

The policy 'if' condition requires allOf: type is 'Microsoft.Sql/servers', and anyOf: encryption protector does not exist OR encryption protector is 'ServiceManaged'. This audits SQL servers that either use service-managed TDE or have no encryption protector configured. Option B is incorrect because the policy does not trigger when a customer-managed key is used; it specifically targets those without a customer-managed key.

Option C is incorrect because the condition checks for absence of encryption protector or service-managed, not the absence of customer-managed key. Option D is incorrect because version 12.0 is checked but it is not the trigger; the trigger is based on encryption protector type.

Configuring manual failover allows the database administrator to initiate failover immediately without waiting for a grace period. This reduces the overall failover time, as the 20-minute failover observed likely included time waiting for automatic failover to trigger after the grace period. With manual failover, the switch can be performed in less than 15 minutes.

Option B is incorrect because setting the grace period to 1 hour would actually increase the time before automatic failover occurs, and enabling read-scale failover does not reduce failover time. Option C increases cost and does not directly affect failover time. Option A does not significantly reduce failover time.

After a manual failover in an Azure SQL Managed Instance failover group, the original primary becomes a secondary. To restore it to the failover group, you must first remove the original primary from the group if it is still listed, then add it back as a secondary. This allows the original primary to rejoin as a readable secondary and resync data.

Option B is incorrect because a planned failover can only be initiated when the primary is healthy, which it may not be after an outage. Option C is incorrect because creating a new instance is unnecessary and wasteful. Option D is incorrect because a forced failover is not appropriate for restoring a previously failed instance; forced failover is used for urgent failover scenarios and may cause data loss.

Auto-failover groups with a secondary server in a different region provide cross-region disaster recovery with minimal data loss. Azure SQL Database supports configuring an auto-failover group that includes a primary and secondary server in different regions. When using the Business Critical service tier, synchronous replication is used within the primary region, but for cross-region failover, asynchronous replication is used, resulting in minimal data loss (typically a few seconds).

Option A is wrong because zone-redundant replicas provide high availability within a single region, not cross-region DR. Option C is wrong because read scale-out allows offloading read workloads to a replica, but does not provide failover capability. Option D is wrong because geo-redundant backup storage and geo-restore can restore the database in a different region, but it involves longer recovery time (hours) and potential data loss, and is not a failover mechanism.

After a forced failover, the original primary is in a failed state. To bring it back as a secondary without data loss, you must remove it from the failover group, drop the original database, and then add a new secondary database (which will be seeded from the current primary). This is the only method that ensures no data loss.

Option A is incorrect because there is no 'az sql db failover-group re-sync' command. Option B is incorrect because active geo-replication is not required; failover groups automatically handle replication. Option C is incorrect because restoring from backup would not reflect changes made after the failover, resulting in data loss.

A secondary database in a paired region with data synchronization ensures that data is replicated to another region, enabling failover with minimal data loss. Option C is correct: The failover group listener endpoint provides a constant connection string that automatically routes to the current primary database, ensuring application connectivity after failover. Option E is correct: The failover group configured between primary and secondary servers orchestrates the replication and automatic failover process.

Option A is incorrect: Geo-redundant backup storage is for point-in-time restore, not for failover. Option D is incorrect: Azure Traffic Manager with priority routing is not needed when using failover groups; the failover group listener handles routing.

Options B and D are correct. Zone-redundant configuration can be enabled during database creation or by updating an existing database (B), and it provides resilience within a single Azure region across multiple availability zones (D). Option A is incorrect because zone-redundant configuration does not require a failover group; failover groups are used for geo-replication, not for zone redundancy.

Option C is incorrect because zone-redundant configuration is available in both the General Purpose and Business Critical service tiers. Option E is incorrect because the cost impact varies by tier: General Purpose tier does not incur additional cost for zone redundancy, while Business Critical does; thus, the blanket statement that it always incurs additional cost is false.

A failover group between two General Purpose instances in different regions provides geo-replication with an RPO of seconds to minutes and an RTO of about 1 hour, meeting the requirements at minimal cost. Option A is incorrect because zone-redundant redundancy only protects against zonal failures within the same region, not regional disasters. Option B is incorrect because deploying a second instance with zone redundancy in the same region does not provide cross-region DR.

Option D is incorrect because using Business Critical instances is more expensive and not required for the specified RPO/RTO.

Failover groups are the native disaster recovery feature for Azure SQL Managed Instance, providing automatic failover, a readable secondary (via read-only intent), and minimal data loss with a synchronization mode that prioritizes data durability. Option A (auto-failover group with active geo-replication) is incorrect because active geo-replication is a separate feature for single databases and elastic pools, not managed instances; failover groups themselves include the auto-failover capability. Option C (log shipping) is not a built-in managed instance feature, does not provide automatic failover, and the secondary is not readable during normal operation.

Option D (active geo-replication) is for single databases and elastic pools, not managed instances, and does not provide automatic failover.

Business Critical tier with zone redundancy provides automatic failover within seconds and zero data loss, meeting the RPO of 5 seconds and RTO of 1 minute. Option A is incorrect because Hyperscale tier with geo-replication and auto-failover group typically has a higher RTO (often up to 1 hour) and RPO may exceed 5 seconds. Option B is incorrect because Hyperscale tier with zone redundancy does not provide automatic failover on its own; it only offers local resilience.

Option C is incorrect because General Purpose tier with active geo-replication has a higher RTO (up to 1 hour) and does not guarantee an RPO of 5 seconds.

The Premium tier supports zone-redundant configuration, which replicates data across availability zones within the same region, providing high availability and minimizing downtime during planned maintenance or unplanned failures. Option A is wrong: the Basic tier does not support failover groups or zone redundancy. Option B is wrong: active geo-replication is designed for disaster recovery across regions, not for high availability within a region, and incurs higher cost and complexity.

Option D is wrong: the Standard tier does not support zone redundancy; zone redundancy is only available in the Premium or Business Critical tiers.

A SQL Server Failover Cluster Instance (FCI) with shared storage provides automatic failover within seconds and zero data loss, as the shared storage ensures no data is lost during failover. Option A (Backup and restore to another region) does not provide automatic failover and can have data loss up to the last backup. Option C (Always On Availability Group with synchronous commit) can achieve zero data loss and automatic failover if properly configured, but it requires a listener and may have more complex setup; however, FCI is typically the preferred solution for VM-level HA with zero data loss.

Option D (Log shipping) involves manual failover and potential data loss, so it does not meet the requirements.

Using ApplicationIntent=ReadOnly in connection strings directs read-only queries to the secondary replica. To ensure consistent data during replication lag, you must verify the secondary is up-to-date before querying, for example by checking the replication lag via DMVs or using the 'latest' hint. This ensures reporting queries see consistent data.

Option B is incorrect because read scale-out is specific to Hyperscale databases, not active geo-replication. Option C is incorrect because an auto-failover group with read-write endpoint does not guarantee read consistency for reporting queries. Option D is incorrect because a failover group with read-only endpoint does not enforce consistency checks.

In the Business Critical tier of Azure SQL Database, there is a fixed set of four replicas: one primary and three secondary replicas. All secondary replicas are readable and can serve read-only queries. Therefore, the maximum number of readable replicas available for read-only queries is 3.

Option C (4) is incorrect because it would include the primary replica, which is not typically used for read-only queries unless read-scale is enabled, but even then the maximum readable replicas (including primary) is 4, but the question asks specifically for 'readable replicas available for read-only queries', which are the secondaries. The highAvailabilityReplicaCount property shown in the exhibit is not configurable for the Business Critical tier; it is only applicable to the Hyperscale tier. In Business Critical, the replica count is fixed at 4 total, with 3 readable secondaries.

Business Critical tier with zone redundancy provides high availability within a region with an RPO of 0 and RTO of approximately 30 seconds for planned maintenance. Configuring a failover group to a secondary region provides disaster recovery with an RPO of 5 minutes and RTO of 1 hour. Option A is incorrect because active geo-replication is not supported for Azure SQL Managed Instance; failover groups are used instead.

Option B is incorrect because General Purpose tier does not provide an RTO of 30 seconds for planned maintenance, and geo-redundant backup does not meet the required RPO and RTO for planned maintenance. Option C is incorrect because a failover group alone does not provide the sub-minute RTO needed for planned maintenance within the region.

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.

Options C and D are correct. To achieve an RPO of 0 seconds and an RTO of 30 seconds for Azure SQL Managed Instance, you need configurations that provide synchronous replication and rapid failover. Option C combines zone redundancy (for local high availability with zero data loss within the region) and a failover group to a secondary instance in a paired region.

The failover group, when used with the Business Critical service tier and a planned failover, can synchronize all data before failing over, meeting the zero RPO requirement. Option D directly uses a failover group with the 'planned' failover option, which ensures all committed transactions are replicated before failover, achieving zero data loss and a fast RTO. Option A is incorrect because active geo-replication is not supported for SQL Managed Instance and is asynchronous.

Option B is incorrect because zone redundancy alone provides high availability within a single region but does not address cross-region disaster recovery. Option E is incorrect because manual restore from backup cannot meet the 30-second RTO.

To maintain high availability during a planned patching event on a Business Critical tier with zone redundancy enabled, you should first verify that zone redundancy is enabled (B) to confirm the configuration. Perform a planned failover test (D) to ensure the failover mechanism works correctly. Ensure the database has a read-scale replica configured (E) to offload read workloads and maintain performance.

Option A (enable active geo-replication) is not required for high availability during patching; it is for disaster recovery. Option C (scale to lower tier) would reduce availability and is unnecessary.

After an automatic failover, the secondary region becomes the primary. To restore the original primary and re-establish the failover relationship, you must perform a planned failover (also called a graceful failover) from the current primary (the former secondary) to the original primary. This switches roles back without data loss.

Option A is incorrect because deleting and recreating the failover group is unnecessary and disruptive. Option B is incorrect because you cannot add the original primary as a new secondary if it is already part of the failover group—it must be removed first. Option D is incorrect because removing and re-adding the original primary would break the geo-replication and is not the correct procedure.

To achieve zero data loss during a planned failover under heavy write load, you should set the primary database to read-only to block any new writes. This allows the geo-replication to catch up and synchronize all committed transactions to the secondary. Then perform the planned failover (which switches the secondary to become the new primary).

After failover, set the new primary to read-write mode to resume writes. Option B is incorrect: switching the secondary to read-write only enables read workloads on the secondary; it does not trigger a failover. The failover must be explicitly initiated, and the write-heavy primary still has pending transactions.

Option C is incorrect: a full backup and restore would cause data loss because any transactions after the backup would be lost. Geo-replication provides near-real-time synchronization. Option D is incorrect: scaling up the primary does not affect the failover process or guarantee zero data loss.

The only way to ensure zero data loss is to prevent writes and synchronize before failover.

To achieve a 99.99% SLA for SQL Server on Azure VM, you need to ensure high availability. Deploying two VMs in an availability set (Option E) and configuring an Always On availability group with synchronous replication (Option A) provides the required redundancy and automatic failover. Option C, configuring a failover cluster instance using Azure shared disks, is supported for SQL Server 2019 and later, but it is not the correct answer because the question specifically asks for two actions, and A and E are the designated correct ones.

Option B (geo-redundant backup) does not affect availability. Option D (single VM with premium SSD) does not achieve 99.99% because it lacks redundancy.

The secondary database in a failover group for Premium-tier Azure SQL Database is readable. By setting ApplicationIntent=ReadOnly in the connection string, read-only queries are routed to this secondary replica, offloading read workloads without modifying the failover group configuration. Option B is incorrect because active geo-replication would create a separate secondary outside the failover group, which is unnecessary and adds complexity.

Option C is incorrect because read scale-out uses local replicas within the same region, not the failover group secondary. Option D is incorrect because scaling up the primary does not offload read queries; it only increases resources for all workloads.

The correct answers are B and E. In the Business Critical service tier, multiple synchronous replicas (at least 4) provide local high availability within a region. Additionally, enabling zone-redundant configuration distributes these replicas across availability zones within the same region, further improving availability.

Options A and D (Active geo-replication and Auto-failover groups) are cross-region disaster recovery features and do not contribute to intra-region high availability. Option C (Always On availability groups) is the underlying technology for the replicas but is not a separate feature; the question asks for features that contribute to high availability, and the replicas themselves are the feature.

After a planned failover to a secondary region, the secondary region becomes the primary. To fail back to the original primary region, you perform another planned failover from the current primary (the former secondary) to the original primary. This is the only way to switch roles back without data loss.

Option B correctly identifies this process. Option A is wrong because you cannot add a new secondary replica while failover groups are already in place; failover groups manage replicas. Option C is wrong because geo-replication is not used with managed instance failover groups; failover groups handle replication.

Option D is wrong because you cannot directly configure the original primary as a secondary; you must perform a failover.

After a manual failover in active geo-replication, the secondary becomes the new primary. Client applications that were connecting to the original primary server will have connection strings pointing to that original server's endpoint. Unless the clients update their connection strings to point to the new primary server (the former secondary), they will fail to connect.

Option A is incorrect because geo-replication does not require VPN; the secondary database is accessible over the internet. Option B is incorrect because the secondary database is already provisioned and kept in sync, ready for failover. Option C is incorrect because the server name does not automatically change; the secondary server has its own name, and after failover, the connection string must be updated to that name.

Point-in-time restore (PITR) allows restoring a database to any point within the retention period. By configuring a retention period that covers at least the last 5 minutes, you can recover from user errors within 5 minutes. The default retention is 7 days, but you can configure it up to 35 days, so 5 minutes is easily achievable.

Option B (zone-redundant configuration) provides high availability within a region but not point-in-time recovery. Option C (active geo-replication) is for disaster recovery to another region. Option D (long-term backup retention) is for backups beyond the PITR retention period, not for short-term recovery.

To perform a point-in-time restore in a different region for disaster recovery, you must set 'createMode' to 'GeoRestore' and specify the target server in the desired region. The template currently uses 'PointInTimeRestore', which only restores to the same region. Option A is incorrect because adding a 'location' property alone does not enable cross-region restore—the restore mode must be changed.

Option B is incorrect because 'zoneRedundant' refers to high availability within a single region, not disaster recovery across regions. Option C is incorrect because 'GeoZone' backup redundancy still does not support cross-region point-in-time restore; geo-restore uses geo-redundant backups automatically.

None of the provided options fully satisfy both requirements: zero data loss (RPO=0) and failover time under 30 seconds across regions. Auto-failover groups (Option C) use asynchronous replication across regions, so RPO is not zero. General Purpose tier (Option B) also uses async replication.

Increasing replicas (Option A) does not affect failover time. Hyperscale with named replicas (Option D) does not support cross-region failover. To achieve zero data loss across regions, an alternative solution such as SQL Server on Azure VMs with an availability group or Azure SQL Managed Instance with a distributed availability group would be required.

A failover group between two Azure SQL Managed Instances in different regions provides automatic failover and a readable secondary. The secondary must be in the Business Critical service tier, matching the primary's tier, but you can use a smaller compute size (fewer vCores) to reduce costs while still supporting read-only workloads. Option A is incorrect because you cannot change the secondary's service tier to General Purpose in a failover group; it must match the primary.

Option C is incorrect because the secondary must be in a different region for disaster recovery. Option D is incorrect because active geo-replication is not supported for SQL Managed Instance; failover groups are the appropriate DR solution.

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.

During a disaster where the primary region is unavailable, the correct action is to force a failover with potential data loss. The Azure CLI command 'az sql mi failover-group force-failover-allow-data-loss' (Option A) performs this forced failover, accepting the risk of data loss to restore availability. Option B is incorrect because adding to an elastic pool is unrelated to failover groups.

Option C is incorrect because 'az sql mi failover-group failover' is not a valid command; the correct command includes 'force-failover-allow-data-loss'. Option D is incorrect because the Azure portal does not directly support geo-failover for managed instances; the CLI or PowerShell must be used for forced failover.

Zone-redundant configuration for Azure SQL Database replicates databases across multiple availability zones within the same region, providing protection against a single zone failure (option C) and offering higher availability than a single-zone deployment (option E). Option A is incorrect because zone redundancy does not reduce read latency; that is achieved by geo-replication or read scale-out. Option B is incorrect because automatic failover to a paired region is a feature of geo-redundant disaster recovery, not zone redundancy.

Option D is incorrect because zone redundancy does not protect against a full regional disaster; it only protects within a region.

For a planned failover of an Azure SQL Database failover group, you should ensure the failover is manual (C) and then initiate a planned failover (E). This approach gives you control over the timing and ensures no data loss because the secondary is fully synchronized before the switch. Option A (scaling up) does not reduce downtime during failover; it affects performance but not failover behavior.

Option B (terminating connections) is unnecessary because the failover process handles connection redirection. Option D (disabling automatic failover) is not required for a planned failover and could actually increase risk if an unplanned event occurs.

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.

A forced failover (also called an unplanned failover) is used when the primary database is completely unavailable, and you accept potential data loss. Option A is incorrect because a planned failover requires both the primary and secondary to be online to ensure zero data loss. Option B is incorrect because waiting for the primary to come back online is not an appropriate action when immediate failover is required due to catastrophic failure.

Option C is incorrect because you cannot remove the primary database from a failover group when it is down; the failover group must be failed over as a whole.