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

Question 1

Which TWO actions are required to automate the export of an Azure SQL Database to a BACPAC file on a monthly basis? (Choose two.)

Accepted Answer

Use Azure Automation or a scheduled Azure Function to call the Export-AzSqlDatabase cmdlet.. Option B is correct because automating a monthly BACPAC export requires a scheduling/orchestration mechanism, and Azure Automation runbooks or a timer-triggered Azure Function can invoke the Az PowerShell cmdlet Export-AzSqlDatabase (or the equivalent az sql db export CLI command) on a recurring schedule. Option E is correct because a BACPAC export must be written to a destination, and the Export-AzSqlDatabase cmdlet requires a target Azure Storage account and container (specified via -StorageAccountName/-StorageContainerName or a storage key/URI) to hold the resulting .bacpac file. Option A is not required because long-term retention (LTR) applies to automated full/differential/log backup copies for point-in-time restore, not to BACPAC logical exports. Option C is not required because the export is a platform service performed by Azure SQL Database; no SQL Server on an Azure VM is needed. Option D is not required because SSIS is an ETL tool and plays no role in generating a BACPAC export.

Answer

Configure long-term retention (LTR) policy for the database.

Answer

Deploy a SQL Server on Azure VM to run the export command.

Answer

Install SQL Server Integration Services (SSIS) on a virtual machine.

Question 2

Which TWO tools can be used to automate the deployment of database schema changes to Azure SQL Database as part of a CI/CD pipeline? (Choose two.)

Accepted Answer

GitHub Actions with the Azure SQL Database deployment action. Option B is correct because GitHub Actions provides the 'Azure SQL Database deployment' action (azure/sql-action), which executes scripts or DACPAC/BACPAC deployments against Azure SQL Database as an automated pipeline step. Option C is correct because an Azure DevOps release pipeline can use the 'Azure SQL Database deployment' task (SqlAzureDacpacDeployment) to deploy a SQL Server database project's DACPAC, applying schema changes automatically during CI/CD. Option A is not a pipeline automation tool; Azure Data Studio with SQLCMD mode is an interactive client for running scripts manually. Option D, the Import and Export Wizard, only moves data between sources and does not manage schema versioning or pipeline deployment. Option E, SSMS, is a manual GUI administration tool and does not itself automate CI/CD schema deployment.

Answer

Azure Data Studio with SQLCMD mode

Answer

SQL Server Import and Export Wizard

Answer

SQL Server Management Studio (SSMS)

Question 3

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.. When automatic tuning forces a plan that causes regression, the correct remediation is to create a plan guide that pins the previous (good) plan for that specific query, then disable the automatic tuning recommendation for that query so Azure SQL doesn't re-force the bad plan. This is a targeted fix that preserves automatic tuning for other queries.

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 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

Configure the auto-failover group to automatically fail over.. Auto-failover groups are designed to automatically fail over to the secondary region in the event of a disaster, providing built-in automation. Option C is incorrect because while an Azure Automation runbook could be used to initiate a failover manually, it is redundant since the auto-failover group already handles automatic failover. Options B and D are incorrect because elastic jobs are for management tasks like data consistency, and SQL Server Agent is not available in Azure SQL Database.

Answer

Schedule a failover using elastic jobs.

Answer

Create an Azure Automation runbook that initiates the failover.

Answer

Use a SQL Server Agent job to trigger failover.

Question 5

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 is the native monitoring and alerting service in Azure. It can collect metrics from Azure SQL Database, including storage usage, and trigger alerts based on thresholds like 80%. You can create an alert rule that sends notifications via email, SMS, or webhook to the operations team.

Answer

Azure Automation

Answer

Microsoft Sentinel

Answer

Azure Logic Apps

Question 6

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. Option A (Build pipeline) is correct because a build pipeline is needed to compile the database project (e.g., a SQL Server Database Project / DACPAC) and produce the deployment artifact that will be published to the Azure SQL Database. Option B (Release pipeline) is correct because the release pipeline consumes that artifact and executes the deployment task (such as Azure SQL Database deployment or SqlPackage) against the target Azure SQL Database. Option E (Variable group) is correct because it stores reusable, environment-specific values (server name, database name, credentials, connection strings) that the pipelines reference, keeping secrets and configuration out of the pipeline definition. Option C (Elastic job agent) is not required because it is used to run T-SQL jobs across a set of databases in a pool, not to deploy schema changes through Azure DevOps. Option D (Azure Automation runbook) is not required because runbooks automate operational tasks in Azure, but schema deployment in this scenario is handled by the Azure DevOps build and release pipelines.

Answer

Elastic job agent

Answer

Azure Automation runbook

Question 7

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.. Auto-pause functionality is only available for Azure SQL Database in the serverless compute tier. Without configuring the database as serverless (by setting the 'sku' property to include 'Serverless' tier), the auto-pause and auto-resume features will not work, regardless of other property settings. Option A is incorrect because the 'zoneRedundant' property does not affect auto-pause. Option C is incorrect because the minimum 'autoPauseDelay' for serverless is 60 minutes, not 360. Option D is incorrect because the API version used supports serverless features.

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 8

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.. In Azure Automation runbooks, there is no interactive desktop environment, so cmdlets that require a graphical user interface, such as Out-GridView, are not supported. When the script runs in a runbook, Out-GridView fails silently or does not produce any output, causing the subsequent alert logic (which likely depends on the output) to never trigger. Option A is incorrect because the -OutputAs parameter is used with Invoke-SqlCmd to specify the output format, not to return results for alerting. Option C is incorrect because Invoke-SqlCmd is fully compatible with Azure SQL Database when proper authentication is used. Option D is incorrect because the script runs successfully, indicating the query syntax is valid.

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 9

A company uses Azure SQL Database and wants to automate the process of refreshing a development database from production backups weekly. Which Azure service should be used to orchestrate this process including restore and post-restore scripts?

Accepted Answer

Azure Automation with PowerShell runbooks. Azure Automation with PowerShell runbooks is designed for orchestrating scheduled administrative tasks across Azure resources, including database restore operations and post-restore T-SQL scripts. It supports credentials, schedules, and integration with Azure SQL via the SqlServer module, making it the right tool for weekly refresh automation.

Answer

Elastic Database Jobs

Answer

Azure Logic Apps

Answer

Azure Data Factory

Question 10

You need to automate the execution of a T-SQL script against all user databases in an Azure SQL Database elastic pool. The script should run on a schedule and results should be logged to a table. Which feature should you use?

Accepted Answer

Elastic Database Jobs. Elastic Database Jobs are designed for executing T-SQL across many databases in a pool. SQL Agent is not available in Azure SQL Database. Azure Automation runbooks can run T-SQL but require more setup. Azure Data Factory is for data movement, not T-SQL execution.

Answer

SQL Agent jobs

Answer

Azure Data Factory

Answer

Azure Automation with PowerShell runbooks

Question 11

You need to automate the deployment of an Azure SQL Database using Infrastructure as Code. The deployment should include the database, firewall rules, and threat detection settings. Which tool should you use?

Accepted Answer

Azure Resource Manager templates. Azure Resource Manager (ARM) templates are the native IaC for Azure. Azure Automation runbooks can deploy but are not declarative. Azure CLI can script deployments but is imperative. Azure Policy is for governance, not deployment.

Answer

Azure CLI scripts

Answer

Azure Automation runbooks

Answer

Azure Policy

Question 12

A company uses Azure SQL Database and wants to automatically send an email notification when an index fragmentation exceeds 30% for any database. Which solution should they implement?

Accepted Answer

Use Azure Automation runbook to query sys.dm_db_index_physical_stats and send email. Azure SQL Database does not expose SQL Server Agent, and index fragmentation is not a native Azure Monitor metric, so the correct approach is an Azure Automation runbook that connects to the database, queries sys.dm_db_index_physical_stats, evaluates fragmentation, and sends email via SendGrid, Office 365, or SMTP. This is the standard pattern for custom T-SQL-based monitoring and alerting in Azure SQL Database.

Answer

Create an Azure Monitor alert based on fragmentation

Answer

Use Elastic Database Jobs to check fragmentation and send email

Answer

Configure SQL Agent job to send email

Question 13

Which THREE components are required to run Elastic Database Jobs for Azure SQL Database? (Choose three.)

Accepted Answer

A job database. Elastic Database Jobs require a job database (A), which stores job definitions, execution history, and job step metadata, and it must reside in the same Azure SQL Database server as the job agent. They also require a job agent (C), the Azure resource that executes T-SQL against the target databases and is created in the job database's server. Target databases (B) are required because they are the members of the job group against which the job steps run. SQL Agent (D) is not used, since Elastic Database Jobs are an Azure SQL Database-native feature rather than an on-premises SQL Server Agent component. Azure Automation account (E) is also unnecessary, as scheduling and execution are handled by the job agent and job database, not by Azure Automation.

Answer

SQL Agent

Answer

Azure Automation account

Question 14

You manage an Azure SQL Managed Instance that hosts several databases. You need to automate the process of patching the operating system and SQL Server engine with minimal downtime. What should you use?

Accepted Answer

Configure the maintenance window for the Managed Instance.. Azure SQL Managed Instance provides a built-in maintenance window feature that allows you to schedule patching of the underlying OS and SQL Server engine with minimal downtime. Configuring the maintenance window ensures updates are applied during a specified time, reducing impact on production workloads.

Answer

Use an Elastic Job agent to run a script that applies updates.

Answer

Schedule a manual patching using the Azure portal.

Answer

Use Azure Update Manager to schedule patching.

Question 15

You need to automate the creation of an Azure SQL Database and a corresponding server-level firewall rule to allow access from a specific IP address. The deployment must be repeatable and version-controlled. What should you use?

Accepted Answer

Create an ARM template that defines both the server firewall rule and the database.. ARM templates are declarative JSON files that define the desired state of Azure resources, including Azure SQL Database and server-level firewall rules. They support idempotent deployments, meaning the same template can be run repeatedly to ensure the environment matches the definition, and they can be version-controlled in source control. This makes them ideal for repeatable, automated deployments. The template can include both the Microsoft.Sql/servers/firewallRules and Microsoft.Sql/servers/databases resources, ensuring they are created together.

Answer

Write a PowerShell script that uses New-AzSqlDatabase and New-AzSqlServerFirewallRule.

Answer

Use the Azure portal to create the database and firewall rule.

Answer

Use SQL Server Management Studio to script the creation.