DP-300 Configure and manage automation of tasks Practice Test 8 — 15 Questions

Question 1

You have an Azure SQL Database that uses automatic tuning. You notice that a forced plan regression is causing performance degradation. You need to revert to the previous plan and prevent the automatic tuning from forcing the same plan again. What should you do?

Accepted Answer

Create a plan guide for the previous plan and then disable the automatic tuning recommendation for that query.. The correct approach is to manually create the plan guide for the previous plan and then disable automatic tuning for that specific query. Option A is wrong because disabling automatic tuning globally would affect all queries. Option B is wrong because the database engine cannot be configured to ignore a specific plan. Option D is wrong because reindexing may not address the plan regression.

Answer

Reindex the tables involved in the query.

Answer

Disable automatic tuning for the database.

Answer

Run DBCC FREEPROCCACHE to clear the plan cache.

Question 2

You are automating the creation of Azure SQL Databases using ARM templates. You need to ensure that the databases are created with the 'Hyperscale' service tier and with 'ZoneRedundant' enabled. Which ARM template property should you set?

Accepted Answer

Set 'sku' to 'HS_Gen5_2' and 'properties.zoneRedundant' to true.. The 'sku' property in ARM templates defines the service tier. For Hyperscale, the name is 'HS', and tier is 'Hyperscale'. The 'zoneRedundant' property is a separate property under 'properties'. Option B is wrong because 'edition' is not used in ARM templates for Azure SQL Database. Option C is wrong because 'requestedServiceObjectiveName' is for DTU-based tiers. Option D is wrong because 'maxSizeBytes' does not define the tier.

Answer

Set 'requestedServiceObjectiveName' to 'HS_Gen5_2' and 'zoneRedundant' to true.

Answer

Set 'edition' to 'Hyperscale' and 'zoneRedundant' to true.

Answer

Set 'maxSizeBytes' to a value and 'zoneRedundant' to true.

Question 3

You need to automate the execution of a T-SQL script against an Azure SQL Database every hour. Which Azure service should you use?

Accepted Answer

Azure Automation. Azure Automation with a runbook can run PowerShell or Python scripts that execute T-SQL against a database. Option A is wrong because Azure Logic Apps can also run SQL, but it is more suited for integration workflows. Option B is wrong because elastic jobs are for scheduling jobs across multiple databases, but they require a job agent. Option D is wrong because Azure Data Factory is for data movement and transformation, not simple T-SQL execution.

Answer

Azure Data Factory

Answer

Elastic Database Jobs

Answer

Azure Logic Apps

Question 4

You manage an Azure SQL Database that is part of a failover group. You need to automate the failover to the secondary region in the event of a disaster. Which approach should you use?

Accepted Answer

Create an Azure Automation runbook that initiates the failover.. Azure Automation with a runbook can monitor for disaster and initiate a failover using PowerShell or CLI. Option A is wrong because auto-failover groups handle automatic failover, but the question asks for automation in the event of disaster, which is already automatic. Option C is wrong because elastic jobs cannot initiate failover. Option D is wrong because SQL Server Agent is not available in Azure SQL Database.

Answer

Configure the auto-failover group to automatically fail over.

Answer

Schedule a failover using elastic jobs.

Answer

Use a SQL Server Agent job to trigger failover.

Question 5

You have an Azure SQL Database that uses SQL Server Agent for automation tasks. You migrated the database to Azure SQL Managed Instance. After migration, some SQL Server Agent jobs fail because they reference a linked server that no longer exists. You need to automate the removal of all linked server references from the jobs. What should you do?

Accepted Answer

Create a T-SQL script that updates the job steps to remove linked server references, and execute it via Azure Automation runbook.. PowerShell with the 'Get-AzSqlInstanceDatabase' cmdlet can list databases, but to modify jobs, you need to use T-SQL or the SQL Agent cmdlets. Option A is wrong because Azure Automation does not directly modify SQL Agent jobs. Option B is wrong because elastic jobs are separate from SQL Agent. Option D is wrong because Azure CLI does not have SQL Agent management cmdlets.

Answer

Use elastic jobs to remove the linked server references.

Answer

Use Azure CLI to remove the linked server references.

Answer

Use Azure Automation to run a PowerShell script that removes the linked server references.

Question 6

You need to automatically notify the operations team when an Azure SQL Database reaches 80% storage usage. Which Azure service should you use to create the alert?

Accepted Answer

Azure Monitor. Azure Monitor alerts can be configured to trigger based on metrics like storage percent. Option B is wrong because Azure Automation is for runbooks, not alerting. Option C is wrong because Microsoft Sentinel is a SIEM, not for simple metric alerts. Option D is wrong because Azure Logic Apps can be triggered by alerts, but the alert itself is created in Azure Monitor.

Answer

Azure Automation

Answer

Microsoft Sentinel

Answer

Azure Logic Apps

Question 7

You are designing an automation strategy for an Azure SQL Database that requires the following: 1) Automatically scale up the service tier when CPU usage exceeds 90% for 5 minutes. 2) Automatically scale down when CPU usage drops below 10% for 15 minutes. 3) The solution must be cost-effective and use built-in Azure features. Which TWO options should you combine? (Choose two.)

Accepted Answer

Azure Logic Apps with a metric trigger. Azure Logic Apps can trigger scaling actions based on metrics, and Azure Automation runbooks can execute the scaling commands. Option B is wrong because elastic jobs are for scheduling database tasks, not scaling. Option D is wrong because autoscale rules are not directly available for Azure SQL Database; you need custom automation. Option E is wrong because Azure Functions can also be used, but Logic Apps and Automation are the recommended combination.

Answer

Elastic Database Jobs

Answer

Azure Monitor autoscale

Answer

Azure Functions

Question 8

You need to automate the deployment of schema changes to an Azure SQL Database using Azure DevOps. Which THREE components are required? (Choose three.)

Accepted Answer

Build pipeline. A release pipeline defines the deployment steps, a build pipeline produces the artifacts (e.g., .dacpac), and a variable group stores configuration values like connection strings. Option B is wrong because an Azure Automation runbook is not part of DevOps CI/CD. Option D is wrong because elastic jobs are not used for schema deployment.

Answer

Elastic job agent

Answer

Azure Automation runbook

Question 9

You are implementing automation for an Azure SQL Database. Which TWO scenarios are appropriate for using elastic database jobs? (Choose two.)

Accepted Answer

Checking data consistency across multiple databases. Elastic jobs are designed for executing administrative tasks across multiple databases. Index maintenance and data consistency checks are common use cases. Option C is wrong because scaling is better done with other automation. Option D is wrong because backup is automatically managed. Option E is wrong because user management across databases is better done with contained users or Entra ID.

Answer

Scaling up a database when CPU usage is high

Answer

Creating logins and users across multiple databases

Answer

Performing a full database backup

Question 10

You are reviewing an ARM template snippet for an Azure SQL Database. The database should be configured to automatically pause after 60 minutes of inactivity and resume with a minimum capacity of 0.5 vCores. However, the database is not pausing as expected. What is the most likely cause?

Accepted Answer

The database is not configured with the 'Serverless' compute tier.. The 'autoPauseDelay' property is used for serverless databases. For serverless, 'minCapacity' is also required. The snippet is missing the 'sku' property with 'Serverless' tier. Without the serverless tier, auto-pause is not available. Option A is wrong because zone redundancy does not affect auto-pause. Option B is wrong because the properties are correct but the tier is missing. Option D is wrong because the API version supports serverless.

Answer

The 'zoneRedundant' property is set to false, which prevents auto-pause.

Answer

The 'autoPauseDelay' value is too low; it must be at least 360 minutes.

Answer

The API version does not support auto-pause.

Question 11

You are reviewing a PowerShell script that is part of an Azure Automation runbook. The script is intended to monitor resource usage of an Azure SQL Database and trigger an alert if DTU usage exceeds 80%. The script runs successfully but does not trigger the alert. What is the most likely reason?

Accepted Answer

The script uses Out-GridView, which is not supported in Azure Automation.. The script uses 'Out-GridView', which displays results in a GUI window. In Azure Automation, there is no interactive desktop, so the command fails silently or does not output anything. The alert logic likely depends on the output. Option B is wrong because 'Invoke-SqlCmd' works with Azure SQL Database if the credential has access. Option C is wrong because the 'Query' is valid. Option D is wrong because 'Out-GridView' does not block execution but prevents output from being captured.

Answer

The script must use the -OutputAs parameter to return results.

Answer

The Invoke-SqlCmd cmdlet is not compatible with Azure SQL Database.

Answer

The query syntax is incorrect for Azure SQL Database.

Question 12

You are reviewing an Azure CLI command that creates an elastic job step. The job step is intended to rebuild all indexes on the Sales.Orders table, but the job fails. What is the error in the JSON configuration?

Accepted Answer

The command is missing a semicolon at the end.. The 'ALTER INDEX ALL' command should have a semicolon at the end. While not strictly required, the more likely issue is that the command is missing the semicolon. However, the real error is that the 'command' property should be a T-SQL script, and 'ALTER INDEX ALL ON Sales.Orders REBUILD' is missing the semicolon. Option A is wrong because the syntax is mostly correct. Option C is wrong because the command is correct syntactically. Option D is wrong because the job agent does not require a specific version.

Answer

The target group should specify 'SqlServer' type instead of 'SqlDatabase'.

Answer

The command should be 'REBUILD INDEX' instead of 'ALTER INDEX'.

Answer

The job agent version is incompatible with the command.

Question 13

A company has multiple Azure SQL Databases and wants to automate the deployment of schema changes using a CI/CD pipeline. They need to ensure that changes are automatically rolled back if any step fails. Which Azure service should they use?

Accepted Answer

Azure DevOps with Azure SQL Database deployment task. Azure SQL Database elastic jobs are designed for automating administrative tasks, not CI/CD. Azure Automation with PowerShell runbooks can orchestrate rollbacks but requires custom scripting. Azure DevOps with Azure SQL Deployment task supports transactional deployments with built-in rollback on failure. Azure Machine Learning is irrelevant.

Answer

Azure Automation with PowerShell runbooks

Answer

Azure SQL Database elastic jobs

Answer

Azure Machine Learning

Question 14

You need to automate the backup of an Azure SQL Managed Instance to a storage account every 4 hours. Which feature should you use?

Accepted Answer

Azure Automation with PowerShell runbooks. Long-term retention (LTR) backups are for retaining backups beyond the automated backup period, not scheduling. Azure SQL Managed Instance automated backups are built-in and cannot be customized for frequency. Azure Automation with PowerShell runbooks can invoke backups using T-SQL or REST APIs. SQL Agent jobs cannot run on managed instance as they are not available.

Answer

Built-in automated backups

Answer

SQL Agent jobs

Answer

Long-term retention (LTR) policies

Question 15

A company uses Azure SQL Database with elastic pools. They need to automatically scale up the pool DTU when CPU usage exceeds 80% for 5 minutes and scale down when below 20% for 10 minutes. Which solution should they implement?

Accepted Answer

Use Azure Automation runbook with PowerShell to check metrics and scale. Azure SQL Database autoscale is not natively supported; scaling must be done programmatically. Azure Automation with PowerShell runbooks can query metrics and trigger scaling. SQL Agent jobs are not available in Azure SQL Database. Elastic Database Transactions are for distributed transactions, not scaling. Azure Logic Apps can also be used but are more complex for this scenario.

Answer

Use Elastic Database Transactions to handle scaling

Answer

Configure autoscale settings on the elastic pool in the Azure portal

Answer

Create a SQL Agent job to monitor and alter the pool