Q01 - Question
You deploy two SQL Server virtual machines in an Azure region that supports Availability Zones. The solution must tolerate the loss of a single data center in the region. It must also tolerate a SQL Server crash inside a virtual machine. Which design should you use?
Domain: Plan and configure a high availability and disaster recovery (HA/DR) environment (20–25%) Type: Single choice
- A. Place both virtual machines in one availability set and rely on fault domains for all failure types.
- B. Use Azure Site Recovery alone to replicate the virtual machines within the region.
- C. Place the virtual machines in an availability set and in different Availability Zones at the same time.
- D. Deploy the virtual machines into different Availability Zones and configure an Always On availability group.
D is correct.
Explanation: Availability Zones account for data center-level failure because each zone is a unique physical location in a region. Zones do not protect against in-guest failures such as a SQL Server crash. An availability group (AG) or failover cluster instance (FCI) is also needed to meet recovery objectives. Test the latency between the zones to verify that it meets performance targets.
A is incorrect: Availability sets protect against Azure maintenance and single points of failure in a single data center. They do not protect against a data center failure or an in-guest failure.
B is incorrect: Azure Site Recovery replicates a virtual machine from one Azure region to another. It does not know that SQL Server is running in the virtual machine, so it may not meet the recovery point objective (RPO).
C is incorrect: Availability sets and Availability Zones cannot be combined. You must choose one.
Q02 - Question
Two availability groups run on the same two Azure virtual machines. Each availability group has its own listener. You configure one internal load balancer with a front-end IP address and port for each listener. You need the listeners to work for applications that connect from outside the virtual machine that hosts the primary replica. What should you configure?
Domain: Plan and configure a high availability and disaster recovery (HA/DR) environment (20–25%) Type: Single choice
- A. One shared probe port that you set on both listener IP address resources.
- B. A reserved IP address in Azure for each listener so that no other resource can use it.
- C. A distributed network name for each availability group listener name.
- D. A unique probe port for each listener IP address, set on the listener IP address resource in the WSFC.
D is correct.
Explanation: Creating the load balancer is not enough. Each IP address that uses the load balancer requires its own unique probe port. Two listeners require two probe ports. You set each probe port on the cluster IP address resource for the listener by using Set-ClusterParameter. Setting the probe port stops and starts the listener IP address and temporarily brings the AG offline, so configure it before you deploy to production.
A is incorrect: Each IP address that uses the load balancer requires a unique probe port, so two listeners can't share one probe port.
B is incorrect: You can't reserve the listener IP address at the Azure level. Instead, verify that no other resource claims the address to avoid network conflicts.
C is incorrect: A distributed network name is for the WSFC name only. You can't use it with the name of an availability group or an FCI.
Q03 - Question
You plan high availability for SQL Server on Azure virtual machines. After a failover, the secondary server must have the logins, SQL Server Agent jobs, and databases already in place. You do not want to re-create instance-level objects manually. You can plan the installation of SQL Server from the start. Which feature should you use?
Domain: Plan and configure a high availability and disaster recovery (HA/DR) environment (20–25%) Type: Single choice
- A. Always On Failover Cluster Instance (FCI)
- B. Always On availability group (AG)
- C. Log shipping
- D. An availability group that uses only Windows authentication
A is correct.
Explanation: An FCI provides instance-level protection. The entire instance restarts on another node, so logins and SQL Server Agent jobs are already in place. An FCI is configured when SQL Server is installed. A standalone instance cannot be converted to an FCI. An FCI also requires shared storage, Active Directory Domain Services (AD DS), and Domain Name Services (DNS).
B is incorrect: An AG provides database-level protection. You must manually create SQL Server Agent jobs, instance-level logins, and linked servers on a new primary replica.
C is incorrect: Log shipping provides database-level protection. You must account for objects outside the database yourself.
D is incorrect: Windows authentication can simplify access for an AG. The AG still provides only database-level protection, and SQL Server Agent jobs and linked servers must be created manually.
Q04 - Question
An application uses Azure SQL Database. A node-level hardware failure occurs, and the service creates a new node and attaches the data storage. Users report dropped connections and lost in-flight transactions. You need to reduce the effect of this event on the application. What should you do?
Domain: Plan and configure a high availability and disaster recovery (HA/DR) environment (20–25%) Type: Single choice
- A. Replace the database with a failover cluster instance so that you control the failover process.
- B. Add retry logic to the application so that it handles dropped connections and transient failures.
- C. Turn off Accelerated Database Recovery (ADR) to reduce recovery work on the new node.
- D. Set the database to the EMERGENCY state to repair it during the failover.
B is correct.
Explanation: In Azure SQL Database, all connections are dropped when a new node starts, and in-flight transactions are lost. The application must include retry logic. Cloud applications should be designed to handle transient failures.
A is incorrect: With PaaS, you can configure only the options that Azure provides. A failover cluster instance is a SQL Server feature for IaaS deployments.
C is incorrect: ADR is on by default in Azure SQL Database and cannot be turned off. It also contributes to database availability.
D is incorrect: You cannot set a database to the OFFLINE or EMERGENCY state in Azure SQL Database. Azure manages and maintains the service.
Q05 - Question
A primary Azure SQL Database must have a readable secondary replica in another region. The secondary must be in a different Azure subscription than the primary. You need to choose how to create the replica. Which approach should you use?
Domain: Plan and configure a high availability and disaster recovery (HA/DR) environment (20–25%) Type: Single choice
- A. Use the Replicas page in the Azure portal and select a target server in the other subscription.
- B. Create the geo-secondary with a lower service tier than the primary to reduce cost.
- C. Create an active geo-replication secondary on an Azure SQL Managed Instance in the other subscription.
- D. Configure cross-subscription geo-replication programmatically and use the same service tier for the secondary.
D is correct.
Explanation: Cross-subscription geo-replication lets you place a secondary replica in a different subscription than the primary database. It is available only programmatically. All databases in a geo-replication relationship must also use the same service tier.
A is incorrect: The portal Replicas page is the manual method for configuring geo-replication, but cross-subscription geo-replication is available only programmatically.
B is incorrect: All databases in a geo-replication relationship are required to have the same service tier. A lower tier for the secondary isn't allowed.
C is incorrect: Azure SQL Managed Instance doesn't support active geo-replication. It uses auto-failover groups instead.
Q06 - Question
You manage SQL Server on an Azure virtual machine. Automated backups through the SQL Server resource provider are configured with transaction log backups to a storage account. A colleague also plans to add transaction log backup jobs inside the virtual machine at the instance level. You need to protect the ability to restore databases from the log chain. What should you do?
Domain: Plan and configure a high availability and disaster recovery (HA/DR) environment (20–25%) Type: Single choice
- A. Use one backup method for transaction log backups and don't configure the other.
- B. Keep both transaction log backup methods so that each backup has a duplicate.
- C. Keep both methods and rely on automated restores to combine the log backups.
- D. Disable the automated backups and store log backups on the ephemeral storage of the virtual machine.
A is correct.
Explanation: Each transaction log backup clears the log. A log restore requires an entire unbroken chain of log backups. Using one method keeps the chain in one coordinated place.
B is incorrect: Two uncoordinated log backup methods each clear the log. You might need to piece together backups from both locations to restore a database.
C is incorrect: Restores can't be automated. You must use the restore from URL functionality in SQL Server, and combining two log backup sets doesn't prevent a fragmented chain.
D is incorrect: Ephemeral storage is erased on shutdown or restart, so backups written there can be lost.
Q07 - Question
An administrator ran DROP DATABASE on a database in Azure SQL Database. The logical server that hosted the database still exists. You need to recover the data to the last point in time available before the deletion. What should you do?
Domain: Plan and configure a high availability and disaster recovery (HA/DR) environment (20–25%) Type: Single choice
- A. Restore the backup in place over the existing database name.
- B. Open the server's overview page, select Deleted databases, and restore the database to a new database name.
- C. Delete the server and recreate it to recover the automated backups.
- D. Restore a SQL Managed Instance backup to the SQL Database server.
B is correct.
Explanation: When the server isn't deleted but the database is, you can restore the database normally. On the Deleted databases page, select the database and enter a name for the new database that is created from the backup.
A is incorrect: Restore in place isn't supported on SQL Database. The target database must not exist before you restore.
C is incorrect: When a server is deleted, all of its backups are deleted at the same time and can't be recovered.
D is incorrect: You can't restore SQL Managed Instance backups on SQL Database.
Q08 - Question
Your team runs Azure SQL Database workloads. You need to receive email or SMS notifications about service problems, planned maintenance events, and health advisories that might affect availability in your Azure regions. What should you use?
Domain: Plan and configure a high availability and disaster recovery (HA/DR) environment (20–25%) Type: Single choice
- A. Azure Service Health alerts
- B. The sys.dm_database_replica_states DMV
- C. The az sql db list command
- D. The long-term backup retention history in the Azure portal
A is correct.
Explanation: Azure Service Health provides information about service problems, planned maintenance events, health advisories, and health history. You can set up alerts that notify you through email or SMS of any event that might affect availability.
B is incorrect: The sys.dm_database_replica_states DMV shows the status of replicas. It doesn't send notifications about regional service events or planned maintenance.
C is incorrect: The az sql db list command lists Azure SQL databases and their status when you run it. It doesn't send notifications about service events.
D is incorrect: Long-term backup retention history shows backup information. It doesn't report service problems or planned maintenance events.
Q09 - Question
An organization runs SQL Server in an on-premises data center. You plan to add a secondary replica of an availability group (AG) in Azure virtual machines to provide disaster recovery. Which two actions should you include in the plan?
Domain: Plan and configure a high availability and disaster recovery (HA/DR) environment (20–25%) Type: Multi-select
- A. Use ExpressRoute or a secure site-to-site VPN, and verify that the bandwidth supports the RTO and RPO.
- B. Expose the Azure virtual machines directly to the public internet so that the on-premises replica can connect.
- C. Make sure that the associated infrastructure, such as AD DS and DNS, exists in Azure.
- D. Use Azure SQL Managed Instance as the Azure secondary replica of the on-premises AG.
A and C are correct.
Explanation: Networking is the most important consideration for a hybrid solution that extends to Azure. Insufficient bandwidth can cause you to miss the RTO and RPO. Use ExpressRoute, or configure a secure site-to-site VPN if ExpressRoute is not available. A secondary AG replica in Azure also requires the associated infrastructure, such as AD DS and DNS, to exist in Azure.
B is incorrect: Exposing IaaS virtual machines directly to the public internet is discouraged. Use a site-to-site VPN or ExpressRoute instead.
D is incorrect: PaaS solutions are not designed for traditional hybrid solutions. A hybrid HADR solution is IaaS-based and relies on traditional infrastructure, such as virtual machines that host SQL Server.
Q10 - Question
A business defines a recovery time objective (RTO) of 15 minutes for a web application and its SQL Server backend. In a test, SQL Server recovers in 5 minutes. The application servers recover in 20 minutes. You need to determine whether the application architecture meets the RTO. What should you conclude?
Domain: Plan and configure a high availability and disaster recovery (HA/DR) environment (20–25%) Type: Single choice
- A. The RTO is met because SQL Server, the most important component, recovers in 5 minutes.
- B. The RTO is met because the average recovery time of the two tiers is 12.5 minutes.
- C. The RTO is not met because the slowest component determines the overall RTO, which is 20 minutes.
- D. The RTO is met because an RTO applies only to the database tier of an application.
C is correct.
Explanation: An RTO can be defined for the whole solution and for individual components. The overall RTO is determined by the slowest component to recover. The application servers take 20 minutes, so the overall recovery time is 20 minutes and exceeds the 15-minute RTO. You must adjust the objective or the design.
A is incorrect: A fast database recovery does not shorten the overall recovery. The application is not online until the application servers recover.
B is incorrect: Recovery times of components are not averaged. The slowest component sets the overall RTO.
D is incorrect: An RTO can be defined at the component level and for the entire application architecture. This requirement is stated for the whole application.