Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 281:

  HOTSPOT

  You need to recommend a configuration for ManufacturingSQLDb1 after the migration to Azure. The solution must meet the business requirements.

  What should you include in the recommendation? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Node majority with witness

  As a general rule when you configure a quorum, the voting elements in the cluster should be an odd number. Therefore, if the cluster contains an even number of voting nodes, you should configure a disk witness or a file share witness.

  Note: Mode: Node majority with witness (disk or file share)

  Nodes have votes. In addition, a quorum witness has a vote. The cluster quorum is the majority of voting nodes in the active cluster membership plus a witness vote. A quorum witness can be a designated disk witness or a designated file

  share witness.

  Box 2: Azure Standard Load Balancer

  Microsoft guarantees that a Load Balanced Endpoint using Azure Standard Load Balancer, serving two or more Healthy Virtual Machine Instances, will be available 99.99% of the time.

  Scenario: Business Requirements

  Litware identifies business requirements include: meet an SLA of 99.99% availability for all Azure deployments.

  Incorrect Aswers:

  Basic Balancer: No SLA is provided for Basic Load Balancer.

  Note: There are two main options for setting up your listener: external (public) or internal. The external (public) listener uses an internet facing load balancer and is associated with a public Virtual IP (VIP) that is accessible over the internet. An

  internal listener uses an internal load balancer and only supports clients within the same Virtual Network.

  Reference:

  https://technet.microsoft.com/windows-server-docs/failover-clustering/deploy-cloud-witness

  https://azure.microsoft.com/en-us/support/legal/sla/load-balancer/v1_0/

• Question 282:

  HOTSPOT

  You are planning the migration of the SERVER1 databases. The solution must meet the business requirements.

  What should you include in the migration plan? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Azure Database Migration service

  Box 1: Premium 4-VCore

  Scenario: Migrate the SERVER1 databases to the Azure SQL Database platform.

  Minimize downtime during the migration of the SERVER1 databases.

  Premimum 4-vCore is for large or business critical workloads. It supports online migrations, offline migrations, and faster migration speeds.

  Incorrect Answers:

  The Standard pricing tier suits most small- to medium- business workloads, but it supports offline migration only.

  Box 2: A VPN gateway

  You need to create a Microsoft Azure Virtual Network for the Azure Database Migration Service by using the Azure Resource Manager deployment model, which provides site-to-site connectivity to your on-premises source servers by using

  either ExpressRoute or VPN.

  Reference:

  https://azure.microsoft.com/pricing/details/database-migration/

  https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-azure-sql-online

• Question 283:

  HOTSPOT

  You need to recommend a configuration for ManufacturingSQLDb1 after the migration to Azure. The solution must meet the business requirements.

  What should you include in the recommendation? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Scenario: Business Requirements

  Litware identifies business requirements include: meet an SLA of 99.99% availability for all Azure deployments.

  Box 1: Cloud witness

  If you have a Failover Cluster deployment, where all nodes can reach the internet (by extension of Azure), it is recommended that you configure a Cloud Witness as your quorum witness resource.

  Box 2: Azure Basic Load Balancer

  Microsoft guarantees that a Load Balanced Endpoint using Azure Standard Load Balancer, serving two or more Healthy Virtual Machine Instances, will be available 99.99% of the time.

  Note: There are two main options for setting up your listener: external (public) or internal. The external (public) listener uses an internet facing load balancer and is associated with a public Virtual IP (VIP) that is accessible over the internet. An

  internal listener uses an internal load balancer and only supports clients within the same Virtual Network.

  Reference:

  https://technet.microsoft.com/windows-server-docs/failover-clustering/deploy-cloud-witness

  https://azure.microsoft.com/en-us/support/legal/sla/load-balancer/v1_0/

• Question 284:

  HOTSPOT

  You need to implement the monitoring of SalesSQLDb1. The solution must meet the technical requirements.

  How should you collect and stream metrics? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: The server, the elastic pool, and the database

  Senario:

  SalesSQLDb1 is in an elastic pool named SalesSQLDb1Pool.

  Litware technical requirements include: all SQL Server and Azure SQL Database metrics related to CPU and storage usage and limits must be analyzed by using Azure built-in functionality.

  Box 2: Azure Event hubs

  Scenario: Migrate ManufacturingSQLDb1 to the Azure virtual machine platform.

  Event hubs are able to handle custom metrics.

  Incorrect Answers:

  Azure Log Analytics

  Azure metric and log data are sent to Azure Monitor Logs, previously known as Azure Log Analytics, directly by Azure. Azure SQL Analytics is a cloud only monitoring solution supporting streaming of diagnostics telemetry for all of your Azure

  SQL databases.

  However, because Azure SQL Analytics does not use agents to connect to Azure Monitor, it does not support monitoring of SQL Server hosted on-premises or in virtual machines.

• Question 285:

  HOTSPOT

  You need to recommend the appropriate purchasing model and deployment option for the 30 new databases. The solution must meet the technical requirements and the business requirements.

  What should you recommend? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: DTU Scenario:

  1.

  The 30 new databases must scale automatically.

  2.

  Once all requirements are met, minimize costs whenever possible.

  You can configure resources for the pool based either on the DTU-based purchasing model or the vCore-based purchasing model.

  In short, for simplicity, the DTU model has an advantage. Plus, if you’re just getting started with Azure SQL Database, the DTU model offers more options at the lower end of performance, so you can get started at a lower price point than with

  vCore.

  Box 2: An Azure SQL database elastic pool

  Azure SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. The databases in an elastic pool are on a single server and share a set

  number of resources at a set price. Elastic pools in Azure SQL Database enable SaaS developers to optimize the price performance for a group of databases within a prescribed budget while delivering performance elasticity for each

  database.

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-overview https://docs.microsoft.com/en-us/azure/azure-sql/database/reserved-capacity-overview

• Question 286:

  HOTSPOT

  You are evaluating the role assignments.

  For each of the following statements, select Yes if the statement is true. Otherwise, select No.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Yes

  DBAGroup1 is member of the Contributor role.

  The Contributor role grants full access to manage all resources, but does not allow you to assign roles in Azure RBAC, manage assignments in Azure Blueprints, or share image galleries.

  Box 2: No

  Box 3: Yes

  DBAGroup2 is member of the SQL DB Contributor role.

  The SQL DB Contributor role lets you manage SQL databases, but not access to them. Also, you can't manage their security-related policies or their parent SQL servers. As a member of this role you can create and manage SQL databases.

  Reference:

  https://docs.microsoft.com/en-us/azure/role-based-access-control/built-in-roles

• Question 287:

  HOTSPOT

  You need to design an analytical storage solution for the transactional data. The solution must meet the sales transaction dataset requirements.

  What should you include in the solution? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Hash

  Scenario:

  Ensure that queries joining and filtering sales transaction records based on product ID complete as quickly as possible.

  A hash distributed table can deliver the highest query performance for joins and aggregations on large tables.

  Box 2: Round-robin

  Scenario:

  You plan to create a promotional table that will contain a promotion ID. The promotion ID will be associated to a specific product. The product will be identified by a product ID. The table will be approximately 5 GB.

  A round-robin table is the most straightforward table to create and delivers fast performance when used as a staging table for loads. These are some scenarios where you should choose Round robin distribution:

  When you cannot identify a single key to distribute your data.

  If your data doesn't frequently join with data from other tables.

  When there are no obvious keys to join.

  Incorrect Answers:

  Replicated: Replicated tables eliminate the need to transfer data across compute nodes by replicating a full copy of the data of the specified table to each compute node. The best candidates for replicated tables are tables with sizes less than

  2 GB compressed and small dimension tables.

  Reference: https://rajanieshkaushikk.com/2020/09/09/how-to-choose-right-data-distribution-strategy-for-azure-synapse/

• Question 288:

  DRAG DROP

  You have SQL Server on an Azure virtual machine that contains a database named DB1. DB1 is 30 TB and has a 1-GB daily rate of change.

  You back up the database by using a Microsoft SQL Server Agent job that runs Transact-SQL commands. You perform a weekly full backup on Sunday, daily differential backups at 01:00, and transaction log backups every five minutes.

  The database fails on Wednesday at 10:00.

  Which three backups should you restore in sequence? To answer, move the appropriate backups from the list of backups to the answer area and arrange them in the correct order.

  Select and Place:

  

  Correct Answer:

  

• Question 289:

  DRAG DROP

  You need to apply 20 built-in Azure Policy definitions to all new and existing Azure SQL Database deployments in an Azure subscription. The solution must minimize administrative effort.

  Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

  Select and Place:

  

  Correct Answer:

  

  Step 1: Create an Azure Policy Initiative

  The first step in enforcing compliance with Azure Policy is to assign a policy definition. A policy definition defines under what condition a policy is enforced and what effect to take.

  With an initiative definition, you can group several policy definitions to achieve one overarching goal. An initiative evaluates resources within scope of the assignment for compliance to the included policies.

  Step 2: Create an Azure Policy Initiative assignment

  Assign the initiative definition you created in the previous step.

  Step 3: Run Azure Policy remediation tasks

  To apply the Policy Initiative to the existing SQL databases.

  Reference:

  https://docs.microsoft.com/en-us/azure/governance/policy/tutorials/create-and-manage

• Question 290:

  DRAG DROP

  You have SQL Server on an Azure virtual machine named SQL1.

  SQL1 has an agent job to back up all databases.

  You add a user named dbadmin1 as a SQL Server Agent operator.

  You need to ensure that dbadmin1 receives an email alert if a job fails.

  Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

  Select and Place:

  

  Correct Answer:

  

  Step 1: Enable the email settings for the SQL Server Agent.

  To send a notification in response to an alert, you must first configure SQL Server Agent to send mail.

  Step 2: Create a job alert

  Step 3: Create a job notification

  Example:

  -- adds an e-mail notification for the specified alert (Test Alert)

  -- This example assumes that Test Alert already exists

  -- and that François Ajenstat is a valid operator name.

  USE msdb ;

  GO

  EXEC dbo.sp_add_notification

  @alert_name = N'Test Alert',

  @operator_name = N'François Ajenstat',

  @notification_method = 1 ;

  GO

  Reference:

  https://docs.microsoft.com/en-us/sql/ssms/agent/notify-an-operator-of-job-status

  https://docs.microsoft.com/en-us/sql/ssms/agent/assign-alerts-to-an-operator