DP-300 practice questions 71–80

1. Question 71 · 354 in bank

   HOTSPOT - You plan to deploy an instance of SQL Server on Linux Azure Virtual Machines. The instance will run Microsoft SQL Server 2022 and use the SQL Server IaaS Agent extension for Linux. Which Linux operating system should you deploy, and which benefit will the SQL Server IaaS Agent extension provide? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   >> Operating System: Ubuntu Linux https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/linux/sql-server-iaas-agent-extension-linux?view=azuresql&tabs=azure-powershell#limitations Limitations: The Linux SQL IaaS Agent extension has the following limitations: Only SQL Server VMs running on the Ubuntu Linux operating system are supported. Other Linux distributions are not currently supported. >> Benefit: Registration of a SQL virtual machine resource in Azure In below link, automated backups and patching are NOT present in the Benefits list for Linux: https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/linux/sql-server-iaas-agent-extension-linux?view=azuresql&tabs=azure-powershell

   Source: Adapted from community discussion — voodoo_sh

   References

   • Microsoft Learn
   • Microsoft Learn

   

   Next question

2. Question 72 · 354 in bank

   DRAG DROP - You have a burstable Azure virtual machine named VM1 that hosts an instance of Microsoft SQL Server. You need to attach an Azure ultra disk to VM1. The solution must minimize downtime on VM1. In which order should you perform the actions? To answer, move all actions from the list of actions to the answer area and arrange them in the correct order.

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   

   Next question

3. Question 73 · 354 in bank

   HOTSPOT - You have an Azure Synapse Analytics dedicated SQL pool named Pool1 and an Azure Data Lake Storage Gen2 account named Account1. You plan to access the files in Account1 by using an external table. You need to create a data source in Pool1 that you can reference when you create the external table. How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: dfs - For Azure Data Lake Store Gen 2 used the following syntax: http[s] <storage_account>.dfs.core.windows.net/<container>/subfolders Incorrect: Not blob: blob is used for Azure Blob Storage. Syntax: http[s] <storage_account>.blob.core.windows.net/<container>/subfolders Box 2: TYPE = HADOOP - Syntax for CREATE EXTERNAL DATA SOURCE. External data sources with TYPE=HADOOP are available only in dedicated SQL pools. CREATE EXTERNAL DATA SOURCE <data_source_name> WITH - ( LOCATION = '<prefix>://<path>' [, CREDENTIAL = <database scoped credential> ] , TYPE = HADOOP ) [;] Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables

   Source: Adapted from community discussion — U_C

   References

   • Microsoft Learn

   

   Next question

4. Question 74 · 354 in bank

   HOTSPOT - You plan to develop a dataset named Purchases by using Azure Databricks. Purchases will contain the following columns: ✑ ProductID ✑ ItemPrice ✑ LineTotal ✑ Quantity ✑ StoreID ✑ Minute ✑ Month ✑ Hour ✑ Year ✑ Day You need to store the data to support hourly incremental load pipelines that will vary for each StoreID. The solution must minimize storage costs. How should you complete the code? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: .partitionBy - Example: df.write.partitionBy("y","m","d") .mode(SaveMode.Append) .parquet("/data/hive/warehouse/db_name.db/" + tableName) Box 2: ("Year","Month","Day","Hour","StoreID") Box 3: .parquet("/Purchases") Reference: https://intellipaat.com/community/11744/how-to-partition-and-write-dataframe-in-spark-without-deleting-partitions-with-no-new-data

   Source: Adapted from community discussion — Backy

   

   Next question

5. Question 75 · 354 in bank

   DRAG DROP - You need to implement statistics maintenance for SalesSQLDb1. The solution must meet the technical requirements. Which four 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:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Automating Azure SQL DB index and statistics maintenance using Azure Automation: 1. Create Azure automation account (Step 1) 2. Import SQLServer module (Step 2) 3. Add Credentials to access SQL DB This will use secure way to hold login name and password that will be used to access Azure SQL DB 4. Add a runbook to run the maintenance (Step 3) Steps: 1. Click on "runbooks" at the left panel and then click "add a runbook" 2. Choose "create a new runbook" and then give it a name and choose "Powershell" as the type of the runbook and then click on "create" 5. Schedule task (Step 4) Steps: 1. Click on Schedules 2. Click on "Add a schedule" and follow the instructions to choose existing schedule or create a new schedule. Reference: https://techcommunity.microsoft.com/t5/azure-database-support-blog/automating-azure-sql-db-index-and-statistics-maintenance-using/ba-p/368974

   Source: Adapted from community discussion — phmartins

   References

   • Microsoft Learn
   • Microsoft Learn

   

   Next question

6. Question 76 · 354 in bank

   You need to provide an implementation plan to configure data retention for ResearchDB1. The solution must meet the security and compliance requirements. What should you include in the plan?

   • AConfigure the Deleted databases settings for ResearchSrv01.
   • BDeploy and configure an Azure Backup server.
   • CConfigure the Advanced Data Security settings for ResearchDB1.
   • DConfigure the Manage Backups settings for ResearchSrv01.

   Show answer

   Answer: D

   Explanation

   D is correct. You can configure SQL Database to retain automated backups for a period longer than the retention period for your service tier. In the Azure portal, navigate to your server and then select Backups. Select the Retention policies tab to modify your backup retention settings.

   Source: Adapted from community discussion — eric0718

   Next question

7. Question 77 · 354 in bank

   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:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   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/

   Source: Adapted from community discussion — ramelas

   References

   • azure.microsoft.com documentation

   

   Next question

8. Question 78 · 354 in bank

   What should you do after a failover of SalesSQLDb1 to ensure that the database remains accessible to SalesSQLDb1App1?

   • AConfigure SalesSQLDb1 as writable.
   • BUpdate the connection strings of SalesSQLDb1App1.
   • CUpdate the firewall rules of SalesSQLDb1.
   • DUpdate the users in SalesSQLDb1.

   Show answer

   Answer: B

   Explanation

   I say update connection strings. It is a sort of given (as it's recommended by Microsoft) that firewall rules be the same for master & secondary. https://docs.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-overview#keeping-credentials-and-firewall-rules-in-sync

   Source: Adapted from community discussion — Raffer

   References

   • Microsoft Learn
   • azure.microsoft.com documentation
   • Microsoft Learn

   Next question

9. Question 79 · 354 in bank

   What should you implement to meet the disaster recovery requirements for the PaaS solution?

   • AAvailability Zones
   • Bfailover groups
   • CAlways On availability groups
   • Dgeo-replication

   Show answer

   Answer: B

   Explanation

   Availability zones (e.g. different datacenters in same region) will not help. They want the DB to be available in case there is a regional event.. it's a PaaS not IaaS so it can't be Always On Availability Group. Geo-replication would mean new connection strings so access would not be restored quickly. So it must be failover group.

   Source: Adapted from community discussion — jimmyzaas

   References

   • Microsoft Learn

   Next question

10. Question 80 · 354 in bank

    You need to implement authentication for ResearchDB1. The solution must meet the security and compliance requirements. What should you run as part of the implementation?

    • ACREATE LOGIN and the FROM WINDOWS clause
    • BCREATE USER and the FROM CERTIFICATE clause
    • CCREATE USER and the FROM LOGIN clause
    • DCREATE USER and the ASYMMETRIC KEY clause
    • ECREATE USER and the FROM EXTERNAL PROVIDER clause

    Show answer

    Answer: E

    Explanation

    Has to be "E", as it's Azure SQL Database and the requirements are "Authenticate database users by using Active Directory credentials.".

    Source: Adapted from community discussion — Raffer

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

DP-300 practice, question 71. 354 questions available.