DP-300 practice questions 241–250

1. Question 241 · 354 in bank

   HOTSPOT - You have an Azure Data Factory instance named ADF1 and two Azure Synapse Analytics workspaces named WS1 and WS2. ADF1 contains the following pipelines: ✑ P1: Uses a copy activity to copy data from a nonpartitioned table in a dedicated SQL pool of WS1 to an Azure Data Lake Storage Gen2 account ✑ P2: Uses a copy activity to copy data from text-delimited files in an Azure Data Lake Storage Gen2 account to a nonpartitioned table in a dedicated SQL pool of WS2 You need to configure P1 and P2 to maximize parallelism and performance. Which dataset settings should you configure for the copy activity of each pipeline? To answer, select the appropriate options in the answer area. 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

   P1: Set the Partition option to Dynamic Range. The SQL Server connector in copy activity provides built-in data partitioning to copy data in parallel. P2: Set the Copy method to PolyBase Polybase is the most efficient way to move data into Azure Synapse Analytics. Use the staging blob feature to achieve high load speeds from all types of data stores, including Azure Blob storage and Data Lake Store. (Polybase supports Azure Blob storage and Azure Data Lake Store by default.) Reference: https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-sql-data-warehouse https://docs.microsoft.com/en-us/azure/data-factory/load-azure-sql-data-warehouse

   

   Next question

2. Question 242 · 354 in bank

   DRAG DROP - You have an Azure subscription that contains an Azure SQL managed instance named SQLMi1 and a SQL Agent job named Backupdb. Backupdb performs a daily backup of the databases hosted on SQLMi1. You need to be notified by email if the 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. NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select. 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

   Step 1: Enable Database Mail - If it isn't already enabled, first you would need to configure the Database Mail feature on SQL Managed Instance. Box 2: Create an operator. You can notify the operator that something happened with your SQL Agent jobs. An operator defines contact information for an individual responsible for the maintenance of one or more instances in SQL Managed Instance. Box 3: Add a failure notification to the job, You can then modify any SQL Agent job and assign operators that will be notified via email if the job completes, fails, or succeeds using SSMS or the following T- SQL script: EXEC msdb.dbo.sp_update_job @job_name=N'Load data using SSIS', @notify_level_email=3, -- Options are: 1 on succeed, 2 on failure, 3 on complete @notify_email_operator_name=N'AzureSQLTeam'; Reference: https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/job-automation-managed-instance

   Source: Adapted from community discussion — lukelin08

   References

   • Microsoft Learn

   

   Next question

3. Question 243 · 354 in bank

   DRAG DROP - You have SQL Server on an Azure virtual machine. You need to use Policy-Based Management in Microsoft SQL Server to identify stored procedures that do not comply with your naming conventions. 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:

   

   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

   First create a condition, then a custom policy based on the condition, finally run a policy evaluation. Reference: https://www.mssqltips.com/sqlservertip/2298/enforce-sql-server-database-naming-conventions-using-policy-based-management/

   Source: Adapted from community discussion — reachmymind

   References

   • Microsoft Learn
   • Microsoft Learn

   

   Next question

4. Question 244 · 354 in bank

   You have an Azure SQL managed instance named SQLMI1 that hosts 10 databases. You need to implement alerts by using Azure Monitor. The solution must meet the following requirements: ✑ Minimize costs. ✑ Aggregate Intelligent Insights telemetry from each database. What should you do?

   • AFrom the Diagnostic settings of each database, select Send to Log Analytics.
   • BFrom the Diagnostic settings of each database, select Stream to an event hub.
   • CFrom the Diagnostic settings of SQLMI1, select Send to Log Analytics.
   • DFrom the Diagnostic settings of SQLMI1, select Stream to an event hub.

   Show answer

   Answer: A

   Explanation

   Databases in Azure SQL Managed Instance You can set up an instance database resource to collect the following diagnostic telemetry: To enable streaming of diagnostic telemetry for an instance database, follow these steps: 1. Go to instance database resource within managed instance. 2. Select Diagnostics settings. 3. Select Turn on diagnostics if no previous settings exist, or select Edit setting to edit a previous setting. 4. Etc. 5. Repeat the above steps for each instance database you want to monitor. Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/metrics-diagnostic-telemetry-logging-streaming-export-configure?tabs=azure-portal#configure-the- streaming-export-of-diagnostic-telemetry

   Source: Adapted from community discussion — OBIJUAN88

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   Next question

5. Question 245 · 354 in bank

   You have an Azure SQL managed instance that hosts multiple databases. You need to configure alerts for each database based on the diagnostics telemetry of the database. What should you use?

   • AAzure SQL Analytics alerts based on metrics
   • BSQL Health Check alerts based on diagnostics logs
   • CSQL Health Check alerts based on metrics
   • DAzure SQL Analytics alerts based on diagnostics logs

   Show answer

   Answer: D

   Explanation

   You can use Azure SQL Analytics for monitoring and alerting. You can easily create alerts with the data coming from Azure SQL Database resources. Here are some useful log queries that you can use with a log alert: Example, HIGH CPU: AzureMetrics - | where ResourceProvider=="MICROSOFT.SQL" | where ResourceId contains "/DATABASES/" | where MetricName=="cpu_percent" | summarize AggregatedValue = max(Maximum) by bin(TimeGenerated, 5m) | render timechart Note: Azure Monitor Logs is based on Azure Data Explorer, and log queries are written using the same Kusto query language (KQL). Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/metrics-diagnostic-telemetry-logging-streaming-export-configure?tabs=azure-portal#configure-the- streaming-export-of-diagnostic-telemetry

   References

   • Microsoft Learn

   Next question

6. Question 246 · 354 in bank

   You have an Azure SQL managed instance. You need to enable SQL Agent Job email notifications. What should you do?

   • AUse the Agent XPs option.
   • BEnable the SQL Server Agent.
   • CRun the sp_configure command.
   • DRun the sp_set_agent_properties command.

   Show answer

   Answer: C

   Explanation

   If it isn't already enabled, first you would need to configure the Database Mail feature on SQL Managed Instance: SQL Copy GO EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'Database Mail XPs', 1; GO RECONFIGURE https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/job-automation-managed-instance?view=azuresql

   Source: Adapted from community discussion — OBIJUAN88

   References

   • Microsoft Learn
   • Microsoft Learn

   Next question

7. Question 247 · 354 in bank

   You have four Azure subscriptions. Each subscription contains multiple Azure SQL databases. You need to update the column and index statistics for the databases. What should you use?

   • Aan Azure Automation runbook
   • Ba SQL Agent job
   • CAzure SQL Analytics
   • Dautomatic tuning in Azure SQL Database

   Show answer

   Answer: A

   Explanation

   You can create a runbook for index maintenance in an Azure SQL database. Reference: https://www.sqlshack.com/automate-azure-sql-database-indexes-and-statistics-maintenance/

   Source: Adapted from community discussion — HSQL

   Next question

8. Question 248 · 354 in bank

   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:

   

   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

   Step 1: Enable Database Mail - Database Mail must be enabled. Step 2: 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 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

   Source: Adapted from community discussion — wyxh

   

   Next question

9. Question 249 · 354 in bank

   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:

   

   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

   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

   

   Next question

10. Question 250 · 354 in bank

    You have an Azure SQL Database managed instance named SQLMI1. A Microsoft SQL Server Agent job runs on SQLMI1. You need to ensure that an automatic email notification is sent once the job completes. What should you include in the solution?

    • AFrom SQL Server Configuration Manager (SSCM), enable SQL Server Agent
    • BFrom SQL Server Management Studio (SSMS), run sp_set_sqlagent_properties
    • CFrom SQL Server Management Studio (SSMS), create a Database Mail profile
    • DFrom the Azure portal, create an Azure Monitor action group that has an Email/SMS/Push/Voice action

    Show answer

    Answer: C

    Explanation

    Answer is correct. You have to create Database Mail Profile. Luke97 is referring to the profile name. -- Create a Database Mail profile EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'AzureManagedInstance_dbmail_profile', @description = '...' ; Reference - https://techcommunity.microsoft.com/t5/azure-sql/sending-emails-in-azure-sql-managed-instance/ba-p/386235

    Source: Adapted from community discussion — ukp72

    References

    • Microsoft Learn
    • Microsoft Learn

    Next question

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

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