Microsoft Role-based DP-300 Questions & Answers

• Question 1:

  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 General Purpose

  D. Azure SQL Database Hyperscale

  Correct Answer: A

  Explanation:

  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:

  Note:

  There are several versions of this question with different incorrect answer options. Other incorrect answers you may see on the exam include:

  Azure SQL Database Hyperscale

  Azure SQL Database Basic

  Azure SQL Database managed instance General Purpose

  Reference:

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

• Question 2:

  You have an Azure subscription that contains two instances of SQL Server on Azure Virtual Machines named VM1 and VM2. Both instances run Microsoft SQL Server 2019 CU8.

  You need to deploy a failover cluster instance (FCI) to VM1 and VM2. The solution must eliminate the need for the following:

  A distributed network name (DNN) A load balancer

  What should you do?

  A. Deploy VM1 and VM2 to a single proximity placement group.

  B. Deploy VM1 and VM2 to different proximity placement groups in the same Azure region.

  C. Connect VM1 and VM2 to a single subnet.

  D. Connect VM1 and VM2 to different subnets on a single virtual network.

  Correct Answer: D

  Explanation: Failover cluster instances with SQL Server on Azure Virtual Machines Connectivity To match the on-premises experience for connecting to your failover cluster instance, deploy your SQL Server VMs to multiple subnets within the same virtual network. Having multiple subnets negates the need for the extra dependency on an Azure Load Balancer, or a distributed network name (DNN) to route your traffic to your FCI.

  Note: SQL Server on Azure VMs uses Windows Server Failover Clustering (WSFC) functionality to provide local high availability through redundancy at the server-instance level: a failover cluster instance. An FCI is a single instance of SQL Server that's installed across WSFC (or simply the cluster) nodes and, possibly, across multiple subnets. On the network, an FCI appears to be a single instance of SQL Server running on a single computer. But the FCI provides failover from one WSFC node to another if the current node becomes unavailable.

  Reference: https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/failover-cluster-instance-overview

• Question 3:

  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 Basic

  C. Azure SQL Database managed instance General Purpose

  D. Azure SQL Database Hyperscale

  Correct Answer: A

  Explanation:

  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:

  Note:

  There are several versions of this question with different incorrect answer options. Other incorrect answers you may see on the exam include:

  Azure SQL Database Hyperscale

  Azure SQL Database Basic

  Azure SQL Database managed instance General Purpose

  Reference:

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

• Question 4:

  You have an on-premises datacenter that contains a 14-TB Microsoft SQL Server database.

  You plan to create an Azure SQL managed instance and migrate the on-premises database to the new instance.

  Which three service tiers support the SQL managed instance? Each correct answer presents a complete solution.

  NOTE: Each correct selection is worth one point.

  A. General Purpose Standard

  B. Business Critical Memory Optimized Premium

  C. General Purpose Premium

  D. Business Critical Premium

  E. Business Critical Standard

  Correct Answer: ABC

  Reference: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/resource-limits

• Question 5:

  You have an Azure SQL managed instance named SQLMI1 that has the following settings: vCores: 4

  Service tier: General Purpose

  Hardware generation: Standard-series (Gen5)

  You discover that memory pressure on SQLMI1 is high.

  You need to reduce the memory pressure on SQLMI1. The solution must minimize costs.

  What should to do?

  A. Enable the Query Store.

  B. Change vCores to 8.

  C. Change Hardware generation to Premium-series.

  D. Change Service tier to Business Critical.

  Correct Answer: A

  The correct answer is (A).

  The Query Store option is a cost-effective way to reduce memory pressure on a managed Azure SQL instance. The Query Store stores temporary data used by queries in the cloud, reducing the memory requirements of the managed Azure

  SQL instance.

• Question 6:

  You have an Azure subscription.

  You need to deploy an instance of SQL Server on Azure Virtual Machines. The solution must meet the following requirements:

  Custom performance configuration, such as IOPS, capacity, and throughout, must be supported.

  Costs must be minimized.

  Which type of disk should you include in the solution?

  A. Premium SSD v2

  B. Premium SSD

  C. Standard SSD

  D. Ultra SSD

  Correct Answer: A

  Explanation: Differences between Premium SSD and Premium SSD v2 Unlike Premium SSDs, Premium SSD v2 doesn't have dedicated sizes. You can set a Premium SSD v2 to any supported size you prefer, and make granular adjustments to the performance without downtime. Premium SSD v2 doesn't support host caching but, benefits significantly from lower latency, which addresses some of the same core problems host caching addresses. The ability to adjust IOPS, throughput, and size at any time also means you can avoid the maintenance overhead of having to stripe disks to meet your needs.

  Reference: https://learn.microsoft.com/en-us/azure/virtual-machines/disks-types

• Question 7:

  You have an Azure SQL database.

  You need to implement a disaster recovery solution that meets the following requirements:

  1.

  Minimizes how long it takes to recover the database if a datacenter fails

  2.

  Minimizes administrative effort What should you include in the solution?

  A. Azure Site Recovery

  B. active geo-replication

  C. auto-failover groups

  D. Azure Backup

  Correct Answer: C

  Overview of business continuity with Azure SQL Database and Azure SQL Managed Instance auto-failover groups Auto-failover group allows the application to automatically recover in case of a datacenter outage. Compare geo-replication with failover groups Incorrect:

  *

  active geo-replication

  Active geo-replication enables you to create readable replicas and manually failover to any replica in case of a datacenter outage or application upgrade.

  *

  Site Recovery

  Site Recovery replicates workloads running on physical and virtual machines (VMs) from a primary site to a secondary location. When an outage occurs at your primary site, you fail over to a secondary location, and access apps from there.

  After the primary location is running again, you can fail back to it.

  *

  Azure backup

  Slow recovery.

  Reference:

  https://learn.microsoft.com/en-us/azure/azure-sql/database/business-continuity-high-availability-disaster-recover-hadr-overview

• Question 8:

  You have an Azure subscription that contains the resources shown in the following table.

  

  You need to configure a connection between VM1 and MI1. The solution must meet the following requirements:

  1.

  The connection must be encrypted.

  2.

  Network latency must be minimized. What should you implement?

  A. a site-to-site VPN

  B. virtual network peering

  C. private endpoints

  D. service endpoints

  Correct Answer: C

• Question 9:

  You have an Azure virtual machine named VM1 that runs Windows Server 2022 and hosts a Microsoft SQL Server 2019 instance named SQL1.

  You need to configure SQL1 to use mixed mode authentication.

  Which procedure should you run?

  A. sp_addremotelogin

  B. xp_instance_regwrite

  C. sp_change_users_login

  D. xp_grant_login

  Correct Answer: B

  Change authentication mode (Transact-SQL)

  The following example changes Server Authentication from mixed mode (Windows and SQL) to Windows only.

  USE [master]

  GO

  EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',

  N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 1; GO

  Reference: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode

• Question 10:

  You have an Azure SQL database named SQL1.

  You need to implement a disaster recovery solution for SQL1. The solution must minimize the following:

  1.

  The recovery point objective (RPO)

  2.

  The recovery time objective (RTO)

  3.

  Administrative effort

  What should you include in the solution?

  A. Azure Site Recovery

  B. active geo-replication

  C. availability groups

  D. auto-failover groups

  Correct Answer: D

  Overview of business continuity with Azure SQL Database and Azure SQL Managed Instance auto-failover groups Auto-failover group allows the application to automatically recover in case of a datacenter outage. Compare geo-replication with failover groups Incorrect:

  *

  active geo-replication

  Active geo-replication enables you to create readable replicas and manually failover to any replica in case of a datacenter outage or application upgrade.

  *

  Site Recovery

  Site Recovery replicates workloads running on physical and virtual machines (VMs) from a primary site to a secondary location. When an outage occurs at your primary site, you fail over to a secondary location, and access apps from there.

  After the primary location is running again, you can fail back to it.

  *

  availability groups

  An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to eight sets of

  corresponding secondary databases.

  Reference:

  https://learn.microsoft.com/en-us/azure/azure-sql/database/business-continuity-high-availability-disaster-recover-hadr-overview