Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 171:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

  After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have two Azure SQL Database servers named Server1 and Server2. Each server contains an Azure SQL database named Database1.

  You need to restore Database1 from Server1 to Server2. The solution must replace the existing Database1 on Server2.

  Solution: From Microsoft SQL Server Management Studio (SSMS), you rename Database1 on Server2 as Database2. From the Azure portal, you create a new database on Server2 by restoring the backup of Database1 from Server1, and then you delete Database2.

  Does this meet the goal?

  A. Yes

  B. No

  Correct Answer: B

  Instead restore Database1 from Server1 to the Server2 by using the RESTORE Transact-SQL command and the REPLACE option.

  Note: REPLACE should be used rarely and only after careful consideration. Restore normally prevents accidentally overwriting a database with a different database. If the database specified in a RESTORE statement already exists on the current server and the specified database family GUID differs from the database family GUID recorded in the backup set, the database is not restored. This is an important safeguard.

  Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql

• Question 172:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while

  others might not have a correct solution.

  After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have an Azure SQL database named Sales.

  You need to implement disaster recovery for Sales to meet the following requirements:

  1.

  During normal operations, provide at least two readable copies of Sales.

  2.

  Ensure that Sales remains available if a datacenter fails.

  Solution: You deploy an Azure SQL database that uses the General Purpose service tier and failover groups.

  Does this meet the goal?

  A. Yes

  B. No

  Correct Answer: B

  Instead deploy an Azure SQL database that uses the Business Critical service tier and Availability Zones. Note: Premium and Business Critical service tiers leverage the Premium availability model, which integrates compute resources (sqlservr.exe process) and storage (locally attached SSD) on a single node. High availability is achieved by replicating both compute and storage to additional nodes creating a three to four-node cluster.

  By default, the cluster of nodes for the premium availability model is created in the same datacenter. With the introduction of Azure Availability Zones, SQL Database can place different replicas of the Business Critical database to different availability zones in the same region. To eliminate a single point of failure, the control ring is also duplicated across multiple zones as three gateway rings (GW).

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla

• Question 173:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while

  others might not have a correct solution.

  After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have an Azure SQL database named Sales.

  You need to implement disaster recovery for Sales to meet the following requirements:

  1.

  During normal operations, provide at least two readable copies of Sales.

  2.

  Ensure that Sales remains available if a datacenter fails.

  Solution: You deploy an Azure SQL database that uses the General Purpose service tier and geo- replication.

  Does this meet the goal?

  A. Yes

  B. No

  Correct Answer: B

  Instead deploy an Azure SQL database that uses the Business Critical service tier and Availability Zones.

  Note: Premium and Business Critical service tiers leverage the Premium availability model, which integrates compute resources (sqlservr.exe process) and storage (locally attached SSD) on a single node. High availability is achieved by replicating both compute and storage to additional nodes creating a three to four-node cluster.

  By default, the cluster of nodes for the premium availability model is created in the same datacenter. With the introduction of Azure Availability Zones, SQL Database can place different replicas of the Business Critical database to different availability zones in the same region. To eliminate a single point of failure, the control ring is also duplicated across multiple zones as three gateway rings (GW).

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla

• Question 174:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

  After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. You have an Azure SQL database named Sales.

  You need to implement disaster recovery for Sales to meet the following requirements:

  1.

  During normal operations, provide at least two readable copies of Sales.

  2.

  Ensure that Sales remains available if a datacenter fails.

  Solution: You deploy an Azure SQL database that uses the Business Critical service tier and Availability Zones.

  Does this meet the goal?

  A. Yes

  B. No

  Correct Answer: A

  Premium and Business Critical service tiers leverage the Premium availability model, which integrates compute resources (sqlservr.exe process) and storage (locally attached SSD) on a single node. High availability is achieved by replicating both compute and storage to additional nodes creating a three to four-node cluster.

  By default, the cluster of nodes for the premium availability model is created in the same datacenter. With the introduction of Azure Availability Zones, SQL Database can place different replicas of the Business Critical database to different availability zones in the same region. To eliminate a single point of failure, the control ring is also duplicated across multiple zones as three gateway rings (GW).

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla

• Question 175:

  You have an Azure SQL database named DB1.

  You need to ensure that DB1 will support automatic failover without data loss if a datacenter fails. The solution must minimize costs.

  Which deployment option and pricing tier should you configure?

  A. Azure SQL Database Premium

  B. Azure SQL Database serverless

  C. Azure SQL Database managed instance Business Critical

  D. Azure SQL Database Standard

  Correct Answer: A

  By default, the cluster of nodes for the premium availability model is created in the same datacenter. With the introduction of Azure Availability Zones, SQL Database can place different replicas of the Business Critical database to different availability zones in the same region. To eliminate a single point of failure, the control ring is also duplicated across multiple zones as three gateway rings (GW). The routing to a specific gateway ring is controlled by Azure Traffic Manager (ATM). Because the zone redundant configuration in the Premium or Business Critical service tiers does not create additional database redundancy, you can enable it at no extra cost. By selecting a zone redundant configuration, you can make your Premium or Business Critical databases resilient to a much larger set of failures, including catastrophic datacenter outages, without any changes to the application logic. You can also convert any existing Premium or Business Critical databases or pools to the zone redundant configuration.

  Incorrect Answers:

  C. This feature is not available in SQL Managed Instance.

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla

• Question 176:

  You have SQL Server on Azure virtual machines in an availability group.

  You have a database named DB1 that is NOT in the availability group.

  You create a full database backup of DB1.

  You need to add DB1 to the availability group.

  Which restore option should you use on the secondary replica?

  A. Restore with Recovery

  B. Restore with Norecovery

  C. Restore with Standby

  Correct Answer: B

  Prepare a secondary database for an Always On availability group requires two steps:

  1.

  Restore a recent database backup of the primary database and subsequent log backups onto each server instance that hosts the secondary replica, using RESTORE WITH NORECOVERY

  2.

  Join the restored database to the availability group.

  Reference:

  https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/manually-prepare-a- secondary-database-for-an-availability-group-sql-server

• Question 177:

  You are planning disaster recovery for the failover group of an Azure SQL Database managed instance.

  Your company's SLA requires that the database in the failover group become available as quickly as possible if a major outage occurs.

  You set the Read/Write failover policy to Automatic.

  What are two results of the configuration? Each correct answer presents a complete solution.

  NOTE: Each correct selection is worth one point.

  A. In the event of a datacenter or Azure regional outage, the databases will fail over automatically.

  B. In the event of an outage, the databases in the primary instance will fail over immediately.

  C. In the event of an outage, you can selectively fail over individual databases.

  D. In the event of an outage, you can set a different grace period to fail over each database.

  E. In the event of an outage, the minimum delay for the databases to fail over in the primary instance will be one hour.

  Correct Answer: AE

  A: Auto-failover groups allow you to manage replication and failover of a group of databases on a server or all databases in a managed instance to another region.

  E: Because verification of the scale of the outage and how quickly it can be mitigated involves human actions by the operations team, the grace period cannot be set below one hour. This limitation applies to all databases in the failover group regardless of their data synchronization state.

  Incorrect Answers:

  C: individual SQL Managed Instance databases cannot be added to or removed from a failover group.

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/auto-failover-group-overview

• Question 178:

  You have 10 Azure virtual machines that have SQL Server installed.

  You need to implement a backup strategy to ensure that you can restore specific databases to other SQL Server instances. The solution must provide centralized management of the backups.

  What should you include in the backup strategy?

  A. Automated Backup in the SQL virtual machine settings

  B. Azure Backup

  C. Azure Site Recovery

  D. SQL Server Agent jobs

  Correct Answer: B

  Azure Backup provides an Enterprise class backup capability for SQL Server on Azure VMs. All backups are stored and managed in a Recovery Services vault. There are several advantages that this solution provides, especially for Enterprises.

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/backup-restore#azbackup

• Question 179:

  You need to recommend an availability strategy for an Azure SQL database. The strategy must meet the following requirements:

  1.

  Support failovers that do not require client applications to change their connection strings.

  2.

  Replicate the database to a secondary Azure region.

  3.

  Support failover to the secondary region. What should you include in the recommendation?

  A. failover groups

  B. transactional replication

  C. Availability Zones

  D. geo-replication

  Correct Answer: A

  Active geo-replication is an Azure SQL Database feature that allows you to create readable secondary databases of individual databases on a server in the same or different data center (region).

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-overview

• Question 180:

  You are building a database backup solution for a SQL Server database hosted on an Azure virtual machine.

  In the event of an Azure regional outage, you need to be able to restore the database backups. The solution must minimize costs. Which type of storage accounts should you use for the backups?

  A. locally-redundant storage (LRS)

  B. read-access geo-redundant storage (RA-GRS)

  C. zone-redundant storage (ZRS)

  D. geo-redundant storage

  Correct Answer: B

  Geo-redundant storage (with GRS or GZRS) replicates your data to another physical location in the secondary region to protect against regional outages. However, that data is available to be read only if the customer or Microsoft initiates a failover from the primary to secondary region. When you enable read access to the secondary region, your data is available to be read if the primary region becomes unavailable. For read access to the secondary region, enable read-access geo-redundant storage (RA- GRS) or read-access geo-zone-redundant storage (RA-GZRS).

  Incorrect Answers:

  A: Locally redundant storage (LRS) copies your data synchronously three times within a single physical location in the primary region. LRS is the least expensive replication option, but is not recommended for applications requiring high availability.

  C: Zone-redundant storage (ZRS) copies your data synchronously across three Azure availability zones in the primary region.

  D: Geo-redundant storage (with GRS or GZRS) replicates your data to another physical location in the secondary region to protect against regional outages. However, that data is available to be read only if the customer or Microsoft initiates a failover from the primary to secondary region.

  Reference: https://docs.microsoft.com/en-us/azure/storage/common/storage-redundancy