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

Question 1

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. Azure SQL Managed Instance has built-in automated backups, but they run automatically and cannot be customized to run every 4 hours or target a specific storage account. Long-term retention (LTR) policies are for retaining backups beyond the automated backup period, not for scheduling. SQL Server Agent is available in Azure SQL Managed Instance and can be used to schedule jobs, but it does not provide a direct way to back up to an Azure storage account every 4 hours. Azure Automation with PowerShell runbooks can invoke backups using REST APIs or T-SQL, and schedule them precisely, making it the best choice for this requirement.

Answer

Built-in automated backups

Answer

SQL Agent jobs

Answer

Long-term retention (LTR) policies

Question 2

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

Question 3

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 4

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 Data Factory supports copy activity but not native restore. Azure Automation with PowerShell runbooks can orchestrate the entire workflow. Azure SQL Database elastic jobs are for T-SQL tasks across databases but not for restore operations. Azure Logic Apps can also orchestrate but is less suited for complex scripting.

Answer

Elastic Database Jobs

Answer

Azure Logic Apps

Answer

Azure Data Factory

Question 5

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 6

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 allow custom scheduling and execution of T-SQL scripts for index maintenance and statistics updates. Option A (Elastic Database Jobs) is designed for Azure SQL Database, not Managed Instance. Option C (Azure Logic Apps) can trigger but is not optimal for long-running maintenance tasks. Option D (SQL Agent job) is available in Azure SQL Managed Instance, but it is less flexible for automated scheduling during low usage windows and may impact performance; Azure Automation provides better orchestration and monitoring capabilities. Therefore, the best approach is to use Azure Automation runbook.

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 7

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 8

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 Automation runbooks can query fragmentation and send emails via SendGrid. Elastic Database Jobs can check fragmentation but cannot send emails directly. Azure Monitor alerts can trigger on metrics like DTU but not on index fragmentation. Azure Logic Apps can receive data but require custom setup.

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 9

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 to store metadata, a job agent to orchestrate, and target databases. SQL Agent is not required. Elastic pool is optional. Azure Automation is not required.

Answer

SQL Agent

Answer

Azure Automation account

Question 10

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. Azure Automation runbooks can execute T-SQL scripts and scale databases. They cannot manage on-premises SQL Server directly (requires hybrid worker). SQL Agent jobs cannot be created in Azure SQL Database.

Answer

Deploy Azure Resource Manager templates

Answer

Create SQL Agent jobs on Azure SQL Database

Answer

Manage on-premises SQL Server instances directly

Question 11

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.. Azure SQL Managed Instance supports configuring a long-term retention (LTR) backup policy that automatically stores backups in a geo-redundant storage account for up to 10 years, minimizing administrative overhead while supporting point-in-time restore within the retention period. Option B is incorrect because Azure Backup is designed for IaaS VMs and other workloads, not for SQL Managed Instance native backups. Option C is incorrect because scheduling BACPAC exports is not a backup solution — it does not support point-in-time restore and requires manual setup. Option D is incorrect because using SQL Agent jobs with BACKUP DATABASE TO URL adds complexity and does not integrate with Azure's automated backup management.

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 12

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.. Azure SQL Database auto-failover groups provide automated failover and failback with a grace period for data loss tolerance. Option B is wrong because it does not automate failover. Option C is wrong because manual failover does not provide automatic failback. Option D is wrong because ScheduledExecutorService is a manual workaround that does not integrate with Azure failover groups.

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 13

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 14

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 attempts' and 'Retry interval (minutes)' settings that allow you to configure automatic retries directly in the job step properties. This is the simplest and most native way to retry a failed job step. Option A is incorrect because a T-SQL loop checking job history is complex and not an automated built-in feature. Option B is incorrect because Power Automate polling is an external solution that adds unnecessary complexity. Option C is incorrect because Azure Monitor alerts can notify but do not automatically retry the job; they would require additional logic (e.g., webhook to PowerShell) which is not native or as reliable as the built-in retry settings.

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 15

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.. Azure SQL Database elastic jobs automatically store job execution history in a dedicated job database, which is specified when the job agent is created. This database serves as the central repository for job logs, making it the appropriate location for auditing and troubleshooting. Option A is incorrect because elastic jobs log to the job database, not Azure Table Storage. Option B is incorrect because SQL Server Audit is used for auditing database events, not for capturing job execution logs. Option D is incorrect because Azure Monitor diagnostic settings can be used for additional monitoring but are not the central logging mechanism for elastic jobs.

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.