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

Question 1

You need to automate the scaling of an Azure SQL Database in response to CPU usage using Azure Automation. Which Azure service should you use to monitor CPU metrics and trigger the runbook?

Accepted Answer

Azure Monitor metric alerts. Option B is correct because Azure Monitor alerts can be configured to trigger an Automation runbook when a metric (like CPU) crosses a threshold. Option A is for analyzing logs, not triggering actions. Option C is for server monitoring, not directly triggering runbooks. Option D is a query language, not a service.

Answer

Azure SQL Analytics

Answer

Azure Log Analytics

Answer

Kusto Query Language (KQL)

Question 2

You are designing an automated data movement solution using Azure Data Factory. Data must be copied from an on-premises SQL Server to Azure SQL Database. The solution must support incremental loads and handle schema changes. Which approach should you use?

Accepted Answer

Use Self-Hosted Integration Runtime with a Copy activity configured with incremental loading. Option C is correct because a self-hosted integration runtime is required for on-premises access, and the copy activity can be configured for incremental loads using watermarks. Option A lacks incremental loading. Option B uses the wrong IR (Azure IR cannot access on-premises). Option D is not a Data Factory activity.

Answer

Use Azure-SSIS Integration Runtime with a Data Flow activity

Answer

Use Azure Integration Runtime with a Copy activity

Answer

Use a Stored Procedure activity in Data Factory

Question 3

You need to automate the deployment of Azure SQL Database logical servers and databases using Bicep. What is the best practice for storing the administrative password securely?

Accepted Answer

Reference the password from Azure Key Vault using the getSecret function. Option A is correct because Azure Key Vault is the recommended secure storage for secrets like administrative passwords in Azure deployments. Using the `getSecret` function in Bicep allows you to reference a secret from Key Vault at deployment time without exposing the password in the Bicep file or deployment logs, aligning with Azure security best practices and the principle of least privilege.

Answer

Use the adminPassword property with a generated password

Answer

Use an environment variable in the deployment script

Answer

Store the password as a plain text parameter in the Bicep file

Question 4

You have an Azure SQL Database that must be automatically restarted every night to clear the procedure cache. You plan to use elastic jobs in Azure SQL Database. What should you create first?

Accepted Answer

An elastic job agent. Option A is correct because an elastic job agent is the top-level resource that orchestrates jobs. The job database stores job definitions and history. Option B is created after the agent. Option C is a target group, not first. Option D is a credential, but the agent must exist first.

Answer

A job database

Answer

A target group

Answer

A job credential

Question 5

Your company uses GitHub Actions to deploy changes to Azure SQL Database. You need to ensure that rollbacks can be performed automatically if a deployment fails. Which strategy should you implement?

Accepted Answer

Create a database copy or snapshot before each deployment. Option B is correct because deploying database snapshots before changes allows point-in-time recovery and rollback. Option A is for Azure SQL Managed Instance, not Database. Option C is not a standard rollback method. Option D is for code deployment, not database state.

Answer

Use Azure App Service deployment slots for the database

Answer

Use GitHub Actions deployment environments with approval gates

Answer

Use Azure SQL Managed Instance point-in-time restore

Question 6

You need to automate the creation of an Azure SQL Database using Azure CLI in a CI/CD pipeline. The database name must be unique and include the build ID. How should you specify the database name in the Azure CLI command?

Accepted Answer

Use an environment variable $(Build.BuildId) set by the pipeline. Option C is correct because Azure CLI can use environment variables set by the pipeline, combined with a static prefix. Option A uses a fixed name, which fails on rebuilds. Option B is not standard. Option D is for Bash, not cross-platform.

Answer

Use the $RANDOM variable in Bash

Answer

Use the $(uuid) function in Azure CLI

Answer

Use a hardcoded name like 'mydb'

Question 7

Which TWO actions should you perform to enable automatic tuning for an Azure SQL Database?

Accepted Answer

Enable automatic tuning at the Azure SQL Database server level. Automatic tuning in Azure SQL Database requires enabling it at both the server level (to set the inheritance policy) and the database level (to apply tuning options like FORCE_LAST_GOOD_PLAN or CREATE_INDEX). Option B is correct because enabling at the server level allows you to define a default tuning policy that can be inherited by all databases, which is a prerequisite for per-database tuning. Option C is correct because you must also explicitly enable automatic tuning at the database level to override or confirm the inherited settings for that specific database.

Answer

Set the database compatibility level to 150

Answer

Enable Query Store

Answer

Configure performance recommendations

Question 8

Which THREE components are required to set up elastic jobs in Azure SQL Database?

Accepted Answer

Target groups. Target groups (C) are required because they define the set of databases (or elastic pools) against which an elastic job will execute. Without target groups, the job agent has no scope of execution. The elastic job agent (D) orchestrates job scheduling and execution, and job credentials (E) are needed to authenticate to the target databases for running T-SQL scripts.

Answer

Azure Automation account

Answer

SQL Server Agent

Question 9

Which TWO methods can be used to automate index maintenance in Azure SQL Database?

Accepted Answer

Azure Automation runbooks executing T-SQL scripts. Option A is correct because Azure Automation runbooks can execute T-SQL scripts against Azure SQL Database using the Invoke-SqlCmd cmdlet or similar, enabling scheduled index maintenance tasks such as rebuilding or reorganizing indexes. This method is fully supported in Azure SQL Database, which lacks SQL Server Agent, and allows for flexible, cloud-native automation.

Answer

SQL Server Agent jobs

Answer

Azure Data Factory pipelines

Answer

Automatic tuning

Question 10

Refer to the exhibit. You are deploying an Azure SQL Database using this ARM template. After deployment, you need to automate the scaling of the database to a higher service tier when DTU consumption exceeds 80% for 5 minutes. Which Azure service should you use to trigger the scaling?

Accepted Answer

Azure Monitor metric alert. Option B is correct because Azure Monitor metric alerts can trigger an Automation runbook that scales the database. The exhibit shows a General Purpose database, which supports scaling. Option A is for SQL Agent. Option C is for on-premises. Option D is for performance data, not triggering actions.

Answer

Azure SQL Analytics

Answer

Azure Automation Update Management

Answer

SQL Server Agent

Question 11

Refer to the exhibit. You execute this PowerShell script to automate database configuration. The script runs without errors, but the database remains in the 'Standard' edition with S2 performance level. What is the most likely reason?

Accepted Answer

The database is being moved into an elastic pool, which overrides the edition and SLO. Option D is correct because when ElasticPoolName is specified, the database is moved to the elastic pool and the edition and service objective are ignored; the pool's tier determines the performance. Option A is wrong because the command succeeded. Option B is wrong because the parameters are correct syntactically. Option C is wrong because the pool already exists.

Answer

The elastic pool does not exist

Answer

The -RequestedServiceObjectiveName parameter is misspelled

Answer

The script requires administrative privileges that the user does not have

Question 12

Refer to the exhibit. You are configuring an Azure Automation schedule for a runbook that backs up Azure SQL Databases. The runbook should run daily at midnight. However, the runbook runs twice a day. What is the most likely cause?

Accepted Answer

Both interval and schedule.interval are defined, causing two triggers per day. Option C is correct because the configuration has both 'interval' (1440 minutes = 1 day) and 'schedule.interval' (P1D = 1 day), causing the runbook to trigger twice daily. Option A is wrong because time zone doesn't cause double firing. Option B is wrong because the start time is set. Option D is wrong because the interval unit is minutes, not days.

Answer

The startTime is set to a past date

Answer

The schedule does not specify a time zone

Answer

The intervalUnit is set to Minutes instead of Days

Question 13

You manage an Azure SQL Database that must run a maintenance task every Sunday at 2:00 AM UTC. The task must be resilient to failures and automatically retry if it fails. You need to configure this using Azure automation. What is the most appropriate solution?

Accepted Answer

Create an Azure Logic App with a recurrence trigger scheduled for Sunday at 2:00 AM UTC and configure a retry policy.. Option C is correct because Azure Logic Apps provides built-in retry policies and can be scheduled to run at specific times. Option A is wrong because Azure Automation Runbooks can be scheduled but require more custom error handling for retries. Option B is wrong because Azure Functions can be triggered by timer, but retry logic requires custom implementation. Option D is wrong because T-SQL Agent jobs in Azure SQL Database are limited and do not support retry policies natively.

Answer

Create an Azure Automation Runbook and schedule it to run weekly. Add custom error handling for retries.

Answer

Use T-SQL Agent job in Azure SQL Database with a schedule and set up retry via Transact-SQL.

Answer

Create an Azure Function with a timer trigger and implement retry logic in code.

Question 14

Your company uses Azure SQL Managed Instance for a critical OLTP workload. You need to automate index maintenance for all databases in the instance without downtime. The solution must minimize performance impact during business hours. Which approach should you use?

Accepted Answer

Use Elastic Database Jobs to run index maintenance scripts on all databases during off-peak hours, with parallel execution throttled.. Option A is correct because Elastic Database Jobs can run index maintenance scripts across all databases with configurable schedules and throttling. Option B is wrong because Azure Automation Runbooks can be used but require more setup for multi-database execution and may not have built-in throttling. Option C is wrong because Ola Hallengren scripts are a best practice, but they need a scheduling mechanism; Elastic Database Jobs provide that. Option D is wrong because Azure Logic Apps are not designed for running T-SQL scripts across multiple databases.

Answer

Use Azure Logic Apps with a SQL connector to run index maintenance on each database, with retry policies.

Answer

Create an Azure Automation Runbook that connects to each database and runs index maintenance sequentially.

Answer

Deploy Ola Hallengren’s IndexOptimize stored procedure in each database and schedule it via SQL Server Agent.

Question 15

You have an Azure SQL Database that must be automatically stopped during weekends to save costs. Which combination of Azure services can achieve this automation?

Accepted Answer

Azure Automation Runbook with PowerShell script using Stop-AzSqlDatabase cmdlet, scheduled via Automation schedule.. Option B is correct because Azure Automation with PowerShell can stop and start Azure SQL Database using the Stop-AzSqlDatabase cmdlet, scheduled for weekends. Option A is wrong because Azure Logic Apps can stop/start via REST API but require more configuration; Automation Runbooks are simpler. Option C is wrong because Elastic Database Jobs cannot stop databases. Option D is wrong because Azure Functions can do it but require more custom setup.

Answer

Azure Logic Apps with a recurrence trigger and the SQL connector to execute ALTER DATABASE SET SUSPEND.

Answer

Azure Functions with a timer trigger and the Azure SDK to stop the database.

Answer

Elastic Database Jobs with a T-SQL script to ALTER DATABASE SET SUSPEND.