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

Question 1

You are tasked with automating the creation of user databases in an Azure SQL Managed Instance after deployment. Which tool should you use?

Accepted Answer

Azure Automation with PowerShell runbooks. Azure Automation with PowerShell runbooks is ideal for automating post-deployment tasks such as creating user databases. Azure Resource Manager templates deploy infrastructure but not subsequent database operations. SQL Agent jobs are available in Azure SQL Managed Instance, but Azure Automation provides more orchestration flexibility. Elastic Database Jobs are supported in Azure SQL Managed Instance (in preview), but they are primarily designed for cross-database query scheduling and management, making Azure Automation a more appropriate choice for database creation automation.

Answer

Elastic Database Jobs

Answer

Azure Resource Manager templates

Answer

SQL Agent jobs

Question 2

A company uses Azure SQL Managed Instance and needs to automatically rebuild fragmented indexes weekly during low usage. They also need to update statistics. Which approach should they take?

Accepted Answer

Use Azure Automation runbook with Invoke-SqlCmd to run maintenance scripts. Azure Automation runbooks with Invoke-SqlCmd are the recommended approach for scheduled maintenance tasks on Azure SQL Managed Instance because they can authenticate to the instance and execute T-SQL scripts on a schedule. This provides the flexibility to run index rebuild and UPDATE STATISTICS commands weekly during low-usage windows without relying on features that aren't fully supported or require additional infrastructure.

Answer

Use Elastic Database Jobs to run index maintenance

Answer

Use Azure Logic Apps with SQL connector

Answer

Create a SQL Agent job on the managed instance

Question 3

Which TWO services can be used to automate the backup of Azure SQL Database to a storage account? (Choose two.)

Accepted Answer

Azure Automation with PowerShell runbooks. Azure Automation with PowerShell runbooks can execute Export-AzSqlDatabase to create a BACPAC file in an Azure Storage account. Azure Data Factory supports exporting Azure SQL Database to a BACPAC file in blob storage via the Export activity. Azure Backup stores backups in a Recovery Services vault, not directly in a storage account, so it does not meet the requirement. SQL Agent jobs are not available for Azure SQL Database, and Elastic Database Jobs can only run T-SQL scripts, not backup operations.

Answer

Elastic Database Jobs

Answer

SQL Agent jobs

Answer

Azure Backup

Question 4

Which TWO actions can be performed using Azure Automation runbooks for Azure SQL Database? (Choose two.)

Accepted Answer

Execute T-SQL scripts against Azure SQL Database. Option B is correct because Azure Automation runbooks (PowerShell or Python) can use the Invoke-Sqlcmd cmdlet or the SqlServer module to connect to Azure SQL Database and execute T-SQL scripts against it. Option D is correct because runbooks can call Azure PowerShell cmdlets such as Set-AzSqlDatabase to change the service tier/DTU or vCore settings, effectively scaling the database up or down. Option A is not the intended answer here because, while runbooks can technically deploy ARM templates via New-AzResourceGroupDeployment, the question targets Azure SQL Database-specific actions and this is a generic Azure capability. Option C is incorrect because Azure SQL Database does not include SQL Server Agent; SQL Agent jobs are only available in Azure SQL Managed Instance and on-premises SQL Server. Option E is incorrect because Azure Automation runbooks cannot directly manage on-premises SQL Server instances unless a Hybrid Runbook Worker is deployed on-premises, which is outside the scope of this scenario.

Answer

Deploy Azure Resource Manager templates

Answer

Create SQL Agent jobs on Azure SQL Database

Answer

Manage on-premises SQL Server instances directly

Question 5

You are managing an Azure SQL Managed Instance that hosts a critical database. You need to automate the export of daily backups to a storage account for long-term retention. The solution must minimize administrative overhead and support point-in-time restore within the retention period. What should you use?

Accepted Answer

Configure a long-term retention (LTR) backup policy for the Managed Instance.. Configuring a long-term retention (LTR) backup policy for the Azure SQL Managed Instance is the correct solution because LTR automatically copies full backups to Azure Blob Storage for up to 10 years, with minimal administrative overhead. It supports point-in-time restore within the retention period and is a native, built-in feature that requires only policy configuration. This meets the requirements of automation, minimal overhead, and PITR.

Answer

Configure an Azure Backup policy for the Managed Instance.

Answer

Schedule a BACPAC export of the database to the storage account.

Answer

Create a SQL Agent job to perform backups using BACKUP DATABASE TO URL.

Question 6

You have an Azure SQL Database configured with active geo-replication. You need to automate the failover process in the event of a regional outage, ensuring minimal data loss and automatic failback when the primary region recovers. What should you implement?

Accepted Answer

Configure an auto-failover group with a grace period of 1 hour.. Auto-failover groups are the Azure SQL feature purpose-built for automatic failover across regions. They wrap one or more databases, define a read-write and secondary listener endpoint, and support a configurable grace period (minimum 1 hour) before Azure automatically promotes the secondary after a primary outage. Because the group handles both the failover policy and the listener redirection, applications reconnect without connection-string changes, and when the primary region recovers, Azure can re-establish the original topology (automatic failback) once the grace period logic and replication state allow it.

Answer

Set up a PowerShell script that checks primary database health and initiates failover.

Answer

Use a ScheduledExecutorService in a Java application to monitor and failover.

Answer

Enable geo-replication and manually trigger failover when needed.

Question 7

You need to automate the deployment of a new Azure SQL Database with a specific performance tier, collation, and firewall rules using infrastructure as code. Which technology should you use?

Accepted Answer

ARM template with Microsoft.Sql/servers/databases resource definition.. Azure Resource Manager (ARM) templates are the standard infrastructure-as-code tool for Azure, allowing declarative deployment of Azure SQL Database with specific settings like performance tier, collation, and firewall rules. Option A (Azure CLI) and Option C (PowerShell) are procedural scripts that can be used but do not provide true declarative IaC. Option B (SSMS) is a GUI tool, not suitable for automation.

Answer

Azure CLI script with az sql db create command.

Answer

SQL Server Management Studio (SSMS) with a deployment script.

Answer

PowerShell script with New-AzSqlDatabase cmdlet.

Question 8

You have an Azure SQL Database that uses a SQL Agent job to run a critical ETL process every night. The job recently started failing intermittently. You need to automate the monitoring and alerting of job failures, and automatically retry the job twice with a 10-minute interval between retries. What should you configure?

Accepted Answer

Configure the job step's 'Retry attempts' and 'Retry interval (minutes)' settings in the SQL Agent job step properties.. SQL Agent job steps have built-in retry settings: 'Retry attempts' and 'Retry interval (minutes)'. Configuring these settings will automatically retry the job step on failure with the specified interval, meeting the requirement without additional automation. Therefore, this is the correct answer.

Answer

Modify the job to use a T-SQL loop that checks job history and re-runs the job step.

Answer

Use Microsoft Power Automate to poll the job history and re-run the job if failed.

Answer

Create an Azure Monitor alert on the job failure event and use a webhook to trigger a PowerShell script that retries the job.

Question 9

You manage an Azure SQL Database that uses Azure SQL Database elastic jobs to execute maintenance scripts across multiple databases. You need to ensure that job execution is logged in a central database for auditing and troubleshooting. What should you configure?

Accepted Answer

Ensure the Elastic Job agent uses a dedicated job database to store job execution logs.. Elastic jobs in Azure SQL Database require a dedicated job database (the 'job database' hosted on the Elastic Job agent) that stores job definitions, execution history, and step-level logs. This central database is the built-in mechanism for auditing and troubleshooting job execution across all target databases. Configuring the agent with its own job database ensures all execution metadata and logs are persisted centrally without touching target databases.

Answer

Configure the job to write logs to Azure Table Storage using a custom output.

Answer

Enable SQL Server Audit on the target databases to capture job execution.

Answer

Set up Azure Monitor diagnostic settings for the elastic job agent.

Question 10

You need to automate the process of scaling an Azure SQL Database up during peak hours and down during off-peak hours to optimize cost. The solution must be serverless and not require any custom infrastructure. What should you use?

Accepted Answer

Create a scheduled Azure Logic App that uses the Azure SQL Database REST API to update the service objective.. A scheduled Logic App is a serverless Azure resource that can call the Azure SQL Database REST API (specifically the PATCH on databases to update the requestedServiceObjectiveName) on a recurrence trigger, scaling up and down without any custom infrastructure. This meets both the serverless and no-custom-infrastructure requirements.

Answer

Create a SQL Agent job that runs ALTER DATABASE MODIFY (SERVICE_OBJECTIVE = ...).

Answer

Use an Elastic Job agent to run a script that changes the service objective.

Answer

Enable autoscale on the Azure SQL Database.

Question 11

Your company has a policy that all Azure SQL Databases must have their performance data (DTU/CPU, memory, IO) monitored and analyzed weekly. You need to automate the generation of a weekly report summarizing the top 10 queries by average CPU time. What should you use?

Accepted Answer

Configure a Power BI dashboard that connects directly to Azure SQL Database Query Store.. SQL Agent is not available in Azure SQL Database, making Option C incorrect. Option A is correct because Power BI can connect directly to Azure SQL Database's Query Store, and with a Power BI Pro license, you can schedule email subscriptions of the report, automating the weekly generation and distribution of the top 10 queries report.

Answer

Create an Azure Monitor Workbook that queries the Query Store.

Answer

Set up a SQL Agent job to run queries against sys.dm_exec_query_stats and email the results.

Answer

Use the Query Performance Insight blade in the Azure portal and schedule an export to a storage account.

Question 12

You have an Azure SQL Database that runs a critical workload. You need to automate the monitoring of performance anomalies and receive notifications when certain thresholds are exceeded. Which TWO actions should you implement? (Choose two.)

Accepted Answer

Enable SQL Insights (preview) for intelligent performance monitoring.. Option D is correct because SQL Insights (preview) is an Azure Monitor-based solution that provides intelligent, out-of-the-box monitoring of Azure SQL Database performance, automatically surfacing anomalies and health metrics without manual configuration. Option E is correct because Azure Monitor alert rules can be created on platform metrics such as DTU/CPU percentage and storage usage, and they trigger notifications (email, SMS, webhook, action groups) when thresholds are exceeded, directly satisfying the requirement to automate monitoring and notification. Option A is not appropriate because a Power BI report is a visualization tool, not an alerting mechanism, and a one-minute refresh does not provide threshold-based notifications. Option B is not appropriate because Query Store captures query execution statistics for troubleshooting but does not natively send email notifications for high query duration. Option C is not appropriate because SQL Agent alerts on performance counters apply to SQL Server (IaaS) instances, not to the PaaS Azure SQL Database service, which does not expose SQL Agent.

Answer

Create a Power BI report that refreshes every minute.

Answer

Enable Query Store and set up email notifications for high query duration.

Answer

Configure a SQL Agent alert on performance counters.

Question 13

You need to automate the backup strategy for an Azure SQL Database to meet a recovery point objective (RPO) of 5 minutes and a recovery time objective (RTO) of 1 hour. Which THREE features should you combine? (Choose three.)

Accepted Answer

Long-term retention (LTR) backup policy.. Long-term retention (LTR) backup policy (A) is correct because it lets you retain full backups for up to 10 years and satisfies long-term compliance/retention needs beyond the default PITR window, which is essential in a complete automated backup strategy. Active geo-replication (B) is correct because it continuously replicates the database to a secondary region with an RPO typically under 5 seconds, easily meeting the 5-minute RPO and enabling fast read-only access or failover. Auto-failover group (D) is correct because it builds on active geo-replication and provides automatic, policy-driven failover with a listener endpoint, allowing recovery well within the 1-hour RTO. Azure Backup service (C) is not the right fit here because for Azure SQL Database the native automated backups and PITR already provide the short-term recovery capability, and Azure Backup is primarily used for IaaS VMs and on-premises workloads. Point-in-time restore (E) is not selected because although it supports restoring to any point within the retention period (default 7-35 days), its restore operation can take longer than the 1-hour RTO and it does not by itself provide cross-region replication or long-term retention.

Answer

Azure Backup service.

Answer

Point-in-time restore (PITR).

Question 14

You are responsible for automating the deployment of an Azure SQL Database with associated firewall rules, a Microsoft Entra ID admin, and a vulnerability assessment baseline. The deployment must be idempotent and repeatable. Which THREE tools or approaches should you use? (Choose three.)

Accepted Answer

Bicep template.. Bicep templates (B) are declarative infrastructure-as-code files that define Azure SQL Database, firewall rules, Entra ID admin, and vulnerability assessment settings, and because ARM redeploys them idempotently, they satisfy the repeatable deployment requirement. ARM templates (C) are the underlying JSON declarative format that also supports idempotent, repeatable deployments of the same resources and configurations. An Azure DevOps release pipeline (D) provides the automation orchestration that can deploy the Bicep/ARM templates repeatedly and consistently across environments. SQL Server Management Studio (A) is a manual GUI tool for managing SQL Server/Azure SQL instances and does not provide declarative, idempotent infrastructure deployment. The Azure portal (E) is a manual, click-based interface that is not repeatable or idempotent for automated deployments.

Answer

SQL Server Management Studio.

Answer

Azure portal.

Question 15

You are reviewing an ARM template snippet that configures a Security Alert Policy for an Azure SQL Database. The policy is enabled, and email notifications are sent to the account admin and admin@contoso.com. However, you notice that SQL Injection alerts are disabled. What is the most likely reason for disabling SQL Injection alerts?

Accepted Answer

Because SQL injection detection is already handled by Microsoft Defender for SQL.. Microsoft Defender for SQL (formerly Advanced Threat Protection) provides SQL injection detection and alerting as part of its advanced threat protection capabilities. When Defender for SQL is enabled on the database or server, it supersedes the legacy 'SQL Injection' alert in the classic Security Alert Policy, so administrators commonly disable the redundant SQL Injection alert to avoid duplicate notifications. This is why the ARM template shows SQL Injection alerts disabled while other alerts remain enabled.

Answer

To reduce the number of false positives and save costs on alert processing.

Answer

Because the database is configured with a conflicting vulnerability assessment policy that overrides SQL injection detection.

Answer

Because SQL injection alerts are incompatible with the chosen storage account endpoint.