Plan and configure a high availability and disaster recovery (HA/DR) environment

You manage a two-node Always On availability group on SQL Server 2022 VMs in Azure hosting a 900 GB database. You must add a third secondary replica in a different region. The corporate WAN link to that region is reliable but you want to avoid manually backing up the database to a file share, copying it across the WAN, and restoring it on the new replica. You need the new replica to be initialized with minimal administrative steps directly over the AG's data movement channel. Which method should you configure when adding the secondary replica?

You manage a two-node Always On availability group on SQL Server 2022 VMs in Azure, with both replicas in the same region and Availability Zone-aligned. The business requires zero data loss (RPO = 0) on automatic failover, but a recent audit shows transaction latency increased after the AG was deployed. The primary replica currently uses asynchronous-commit mode with manual failover. Which configuration change best meets the RPO = 0 automatic failover requirement?

You manage a two-node Always On availability group spread across two Azure regions using SQL Server on Azure VMs. The primary is in East US and the secondary is in West US. During a recent East US zone disruption, the Windows Server Failover Cluster lost quorum and both nodes went offline even though the West US node was healthy. You need to choose a quorum configuration that keeps the cluster online when a single region or node is lost, while minimizing cost and administrative overhead. What should you configure?

You administer an Always On availability group hosted on two SQL Server on Azure VMs deployed across two Azure regions, placing the replicas in different subnets. The availability group listener has two IP addresses, one in each subnet. After a failover to the secondary region, a .NET application experiences long connection delays (roughly 20 seconds) before it eventually connects to the listener. You must minimize these connection times without changing the network topology. What should you do?

You manage a three-node Always On availability group on Azure SQL VMs across a single region. The configuration uses Node and File Share Majority quorum with the file share witness hosted on the primary node's local disk. During a planned reboot of the primary node for OS patching, the availability group unexpectedly goes offline and no automatic failover occurs to either secondary replica. What is the root cause?

You manage a three-node Always On availability group on Azure SQL Server VMs. The primary replica hosts a high-throughput OLTP workload, and business users complain that nightly full backups cause CPU and I/O contention on the primary. You want to offload backup operations away from the primary while still meeting your recovery requirements. The availability group has two synchronous secondary replicas configured for automatic failover. What should you configure?

You manage a SQL Server 2022 Standard Edition instance running on an Azure VM. The business wants automatic failover for a single 200 GB production database to a second VM in the same region, with a synchronous secondary. Management refuses to pay for Enterprise Edition. Which high availability solution can you configure to meet these requirements while staying on Standard Edition?

You manage an Azure SQL Database with a business application deployed across two regions. You have configured an auto-failover group between the primary server (East US) and secondary server (West US). The application's connection string currently points directly to the primary server's fully qualified server name. After a recent regional outage triggered an automatic failover, the application could not reconnect to the database even though the secondary was successfully promoted. What change should you make to ensure the application automatically follows the primary during future failovers?

You manage a mission-critical SQL Server 2022 database on an Azure VM. Compliance requires that every full backup written to an Azure Blob Storage container be validated so that any media corruption is detected before the backup is ever needed for a disaster recovery restore. You need to configure the backup process to detect page-level corruption during the backup operation and then confirm that the backup set on the URL is complete and readable, without actually restoring the database. Which combination of actions should you implement?

You manage a SQL Server 2022 instance hosting a production database that uses the FULL recovery model. A nightly full backup and 15-minute transaction log backups are scheduled through a well-established maintenance plan, and these backups form the basis of your point-in-time restore strategy. A developer asks you to take an ad hoc full backup right now so they can refresh a test environment. You must ensure this manual backup does NOT interfere with the existing restore chain used for disaster recovery. Which backup approach should you use?

You manage a 2 TB database on a SQL Server 2022 instance running on an Azure VM. The database uses the FULL recovery model. A full backup is taken every Sunday at 01:00, and transaction log backups run every 15 minutes. Restoring 6 days of transaction log backups after a failure late in the week takes several hours, exceeding the agreed RTO. You must reduce restore time without increasing the full backup frequency. What should you add to the backup strategy?

You manage an Azure SQL Database (Business Critical) that uses active geo-replication with a readable secondary in a paired region. Compliance requires an annual disaster recovery drill that validates the application can operate fully against the secondary region, including write operations, while producing an auditable record of the exercise. The production database must return to its original primary region afterward with no data loss. Which approach should you use for the drill?

You manage a set of Azure SQL databases protected by an auto-failover group with automatic failover enabled. The application requires that regional failover happen automatically during a prolonged outage, but the business will not tolerate silent data loss during transient network blips. You configure the failover group's read/write failover policy to Automatic and set GracePeriodWithDataLossHours to 4. During a real regional outage where the primary region is unreachable and replication cannot catch up, what behavior will occur?

A retail company runs an application backed by four related Azure SQL databases on a single logical server in the East US region. Leadership requires that, in a regional outage, all four databases fail over together to West US as a group, and the application's connection strings must not need to change after failover. Which HA/DR configuration best meets these requirements?

You are designing high availability for a mission-critical SQL Server workload on Azure VMs. The application requires instance-level protection (SQL Agent jobs, linked servers, logins, and the entire instance must fail over together), and the business mandates automatic failover with no data loss within a single Azure region. The application vendor does not support databases in an availability group. Which solution should you recommend?

A retail company runs a mission-critical order-processing database on Azure SQL Database (General Purpose). The business defines an RTO of 5 minutes and an RPO of 5 seconds for a full regional outage. The current configuration relies only on the built-in geo-redundant backups, and during a recent DR drill the team measured a recovery time of over an hour using geo-restore. Which configuration change should you implement to meet both the RTO and RPO requirements?

You manage a SQL Server 2022 instance hosting a 2 TB reporting-source database on an on-premises server. Management wants a low-cost disaster recovery copy on a second on-premises server in a different building, and they also want to run occasional read-only reporting queries against that copy between restore cycles. The RPO is 15 minutes and there is no requirement for automatic failover. Which HA/DR feature should you configure?

You maintain a log shipping configuration between a primary SQL Server instance and a secondary instance for disaster recovery. The primary server is still online and healthy, but you must perform a planned, controlled failover to the secondary as part of a datacenter migration with zero data loss. Which sequence of actions correctly performs the role change?

You administer a SQL Server 2019 instance on-premises that uses log shipping to a secondary server in a remote datacenter for disaster recovery. Recently, users reporting on the standby secondary complain that data is unusually stale. You need to determine, using the built-in log shipping monitoring, which specific step is falling behind so you can target your investigation. Which log shipping job and its associated monitoring metric should you examine to confirm that the transaction logs are being applied to the secondary too slowly?

A financial services company runs a production Azure SQL Database. Regulatory requirements mandate that a backup taken on the first day of each month be retained for 7 years, while the standard automated backups only cover a 35-day window. The DBA must implement this without deploying additional infrastructure or custom scripts. What should the DBA configure?