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

Question 1

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?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

The database is being moved into an elastic pool, which overrides the edition and SLO. When the -ElasticPoolName parameter is specified, the database is moved into the elastic pool, and the -Edition and -RequestedServiceObjectiveName parameters are ignored because the elastic pool's service tier and performance level determine the database's settings. The script runs without errors, but the database remains at Standard S2 because the pool's tier overrides the specified edition and SLO. Option A is incorrect because the command succeeded, indicating the pool exists. Option B is incorrect because the parameter is correctly spelled. Option D is incorrect because the script succeeded without privilege errors.

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 2

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

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.. The most appropriate solution because Azure Logic Apps provides a recurrence trigger that can be scheduled to run at specific times (e.g., every Sunday at 2:00 AM UTC) and includes built-in retry policies to handle failures, ensuring resilience without custom code. Option B (Azure Automation Runbook) can be scheduled and can include custom error handling for retries, but it requires more manual implementation and is less integrated for simple retry logic. Option C (T-SQL Agent job) is not available in Azure SQL Database; Azure SQL Database does not support SQL Agent jobs natively. Option D (Azure Function with timer trigger) requires custom retry logic in code, which is more complex than using Logic Apps' built-in retry policy.

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 4

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

Deploy Ola Hallengren’s IndexOptimize stored procedure in each database and schedule it via SQL Server Agent.. Azure SQL Managed Instance fully supports SQL Server Agent, which can schedule Ola Hallengren's IndexOptimize stored procedure for index maintenance across all databases. This approach allows centralized management, scheduling during off-peak hours, and granular control over parallelism and throttling to minimize performance impact. Option A is incorrect because Elastic Database Jobs are not available on Azure SQL Managed Instance; they are designed for Azure SQL Database. Option B is incorrect because Azure Logic Apps are not suited for executing T-SQL scripts across multiple databases with performance-sensitive throttling. Option C is incorrect because Azure Automation Runbooks would require complex orchestration and lack native scheduling and throttling capabilities compared to SQL Agent.

Answer

Use Elastic Database Jobs to run index maintenance scripts on all databases during off-peak hours, with parallel execution throttled.

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.

Question 5

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 Suspend-AzSqlDatabase cmdlet, scheduled via Automation schedule.. Azure Automation Runbooks with the Suspend-AzSqlDatabase cmdlet provide a native, straightforward way to stop an Azure SQL Database on a schedule. Option B is incorrect because Azure Logic Apps with the SQL connector cannot execute ALTER DATABASE SET SUSPEND; suspension is not a T-SQL operation for Azure SQL Database. Option C is incorrect because Azure Functions can achieve this but require more custom code and setup compared to the simpler Automation Runbook approach. Option D is incorrect because Elastic Database Jobs are designed for executing T-SQL scripts across multiple databases, not for stopping or starting databases.

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.

Question 6

Your team uses Azure DevOps to deploy database changes to Azure SQL Database. You need to automate the generation and deployment of database schema changes based on a Git branch merge. Which Azure service should you integrate?

Accepted Answer

Azure Pipelines with a release pipeline that uses the Azure SQL Database deployment task.. Azure Pipelines (part of Azure DevOps) can be configured to trigger on branch merges and includes a built-in Azure SQL Database deployment task for automating schema changes. Option A is wrong because Azure Automation Runbooks are not designed for CI/CD workflows. Option C is wrong because GitHub Actions would require moving outside the existing Azure DevOps ecosystem. Option D is wrong because Azure Logic Apps are meant for integration workflows, not CI/CD pipeline automation.

Answer

Azure Automation Runbook triggered by a webhook from Azure DevOps.

Answer

GitHub Actions with a workflow that runs on pull request merge, using the Azure SQL action.

Answer

Azure Logic Apps with an HTTP trigger from Azure DevOps, then use SQL connector to run scripts.

Question 7

You are designing an automated backup strategy for Azure SQL Managed Instance. The solution must ensure point-in-time restore (PITR) within 2 hours for the last 7 days and long-term retention (LTR) for 5 years. Which configuration should you use?

Accepted Answer

Set PITR retention to 7 days (default) and configure LTR backup policy with yearly backups for 5 years.. Azure SQL Managed Instance supports point-in-time restore (PITR) with a configurable retention period from 1 to 35 days; the default is 7 days, which satisfies the requirement to restore within 2 hours for the last 7 days (the 2-hour target is a recovery point objective, not retention). For long-term retention (LTR) of 5 years, Managed Instance supports LTR backup policies with yearly backups that can retain backups for up to 10 years. Therefore, setting PITR retention to 7 days (default) and configuring an LTR policy with yearly backups for 5 years meets both requirements. Option A is wrong because Azure Backup for SQL Server in Azure VM is for SQL Server on Azure VMs, not for Managed Instance. Option B is wrong because "geo-redundant backup storage for LTR" is a storage redundancy option, not an LTR retention policy; LTR requires explicit backup frequency and retention configuration. Option C is wrong because PITR retention cannot be set to 2 hours (minimum is 1 day) and custom backup jobs using Elastic Database Jobs are unnecessary; Managed Instance automates backups natively.

Answer

Use Azure Backup for SQL Server in Azure VM to back up the managed instance.

Answer

Set PITR retention to 7 days and use geo-redundant backup storage for LTR.

Answer

Set PITR retention to 2 hours and configure a custom backup job using Elastic Database Jobs.

Question 8

You need to automate the process of scaling an Azure SQL Database to a higher service tier when CPU usage exceeds 80% for 5 consecutive minutes. Which Azure service is best suited for this automation?

Accepted Answer

Azure Logic App triggered by an Azure Monitor metric alert, calling the Azure SQL Database REST API to update the tier.. Azure Logic Apps can be easily triggered by an Azure Monitor metric alert and can call the Azure SQL Database REST API to update the service tier. This provides a simple, low-code solution for automating scaling based on CPU thresholds. Option B is incorrect because Elastic Database Jobs are designed for executing T-SQL scripts across multiple databases, not for scaling operations. Option C is incorrect: although Azure Automation Runbooks can be triggered by alerts and use PowerShell, they require more setup and overhead compared to Logic Apps. Option D is also less optimal: Azure Functions can work but Logic Apps offer a more straightforward integration with Azure Monitor alerts and REST APIs for this specific use case.

Answer

Elastic Database Job that monitors sys.dm_db_resource_stats and executes ALTER DATABASE.

Answer

Azure Automation Runbook triggered by an Azure Monitor alert, using PowerShell to scale the database.

Answer

Azure Function triggered by an Azure Monitor alert, using the Azure SDK to scale.

Question 9

Your Azure SQL Database uses a failover group for disaster recovery. You need to automate a planned failover for disaster recovery testing without data loss. What should you use?

Accepted Answer

Create an Azure Automation Runbook that uses the Start-AzSqlDatabaseFailover command with the -AllowDataLoss parameter set to false.. Azure Automation Runbooks can execute the Start-AzSqlDatabaseFailover cmdlet with -AllowDataLoss:$false to perform a planned failover without data loss. Option A (Logic App with failover trigger) is designed for reacting to Azure Monitor alerts, not for scheduled failsafe testing. Option B (configuring automatic failover policy) is for unplanned failovers and would result in data loss if used for testing because automatic failover is asynchronous. Option C (T-SQL ALTER DATABASE FAILOVER scheduled via SQL Server Agent) is invalid because SQL Server Agent is not available in Azure SQL Database (single database or elastic pool); it's only available in Azure SQL Managed Instance. Therefore, D is the correct choice for automated planned failover.

Answer

Set up an Azure Logic App with a failover trigger from Azure Monitor.

Answer

Configure automatic failover policy in the failover group.

Answer

Use a T-SQL script with ALTER DATABASE FAILOVER scheduled via SQL Server Agent.

Question 10

You have an Azure SQL Database that stores sensitive customer data. You need to automate the masking of a specific column for non-admin users. Which feature should you use?

Accepted Answer

Dynamic Data Masking with a masking function configured on the column.. Dynamic Data Masking (DDM) is the correct feature for masking a specific column for non-admin users. DDM can be configured on a column using a masking function (e.g., default, email, custom) and automatically obfuscates the data in query results for users without the UNMASK permission. Option A (Always Encrypted) encrypts the column but does not mask it selectively; non-admin users cannot see the data at all unless they have the key. Option B (Row-Level Security) filters entire rows based on a predicate, not columns. Option D (Azure Policy) applies governance rules at the subscription/resource level and cannot mask database columns.

Answer

Always Encrypted with a column encryption key.

Answer

Row-Level Security to filter rows for non-admin users.

Answer

Azure Policy to enforce masking rules on the database.

Question 11

You need to automatically send a weekly email report summarizing the performance metrics (DTU consumption, storage used) of all Azure SQL Databases in a subscription. Which Azure service should you use?

Accepted Answer

Azure Logic App with a recurrence trigger, using Azure Monitor connector to get metrics and Office 365 Outlook connector to send email.. Azure Logic Apps provide a no-code/low-code solution with a recurrence trigger to run weekly, the Azure Monitor connector to retrieve metrics (DTU consumption, storage) from Azure SQL Databases, and the Office 365 Outlook connector to send the email report. Option A is less ideal because Azure Automation Runbooks require custom scripting for email and are more complex. Option C is incorrect because Elastic Database Jobs are designed for T-SQL administration tasks across databases, not for gathering metrics via Azure Monitor. Option D is incorrect because Azure Data Factory focuses on data movement and transformation, not on scheduling email reports with live metrics.

Answer

Azure Automation Runbook with PowerShell to collect metrics and send email via Send-MailMessage.

Answer

Elastic Database Job that queries sys.dm_db_resource_stats and sends email via sp_send_dbmail.

Answer

Azure Data Factory pipeline that runs weekly and sends an email with the report.

Question 12

Which TWO actions can you perform using Elastic Database Jobs in Azure SQL Database?

Accepted Answer

Schedule a job to rebuild indexes on a set of databases.. Options B and E are correct. Elastic Database Jobs in Azure SQL Database allow you to schedule and run T-SQL scripts across multiple databases, including those in an elastic pool. You can use them to schedule index rebuilding (B) and to update statistics via T-SQL (E). Option A is incorrect because firewall rules are managed through Azure SQL Server firewall settings, not Elastic Database Jobs. Option C is incorrect because creating users in Microsoft Entra ID (formerly Azure AD) is done through Active Directory, not Elastic Database Jobs. Option D is incorrect because scaling service tiers is an administrative operation handled by Azure SQL Database scaling commands or automation, not by Elastic Database Jobs.

Answer

Add a firewall rule to allow access from a specific IP address.

Answer

Create users in Microsoft Entra ID for database access.

Answer

Automatically scale the service tier of a database based on CPU usage.

Question 13

Which THREE configurations are required to automate the deployment of database schema changes from a Git repository to Azure SQL Database using Azure Pipelines?

Accepted Answer

An Azure SQL Database deployment task in the pipeline.. Options B, C, and E are correct. An Azure SQL Database deployment task (e.g., SqlAzureSqlDatabaseDeployment task) executes the schema change scripts. An Azure Resource Manager service connection to the SQL Database is required for authentication and permissions. A YAML pipeline file defines the build and release stages including the deployment steps. Option A is incorrect because an Elastic Database Job agent is used for multi-database management, not for automated deployment from Git for a single database. Option D is incorrect because an Azure Automation Runbook is not part of Azure Pipelines; it is used for process automation outside of CI/CD.

Answer

An Elastic Database Job agent to run the deployment scripts.

Answer

An Azure Automation Runbook to execute the deployment.

Question 14

Which TWO Azure services can be used to automate the execution of T-SQL scripts on a schedule against Azure SQL Database?

Accepted Answer

Azure Automation Hybrid Runbook Worker with PowerShell Invoke-SqlCmd.. Options B and D are correct. Azure Automation Hybrid Runbook Worker can execute T-SQL scripts using PowerShell Invoke-SqlCmd on a schedule, making it suitable for automating script execution against Azure SQL Database. Elastic Database Jobs are specifically designed to run T-SQL scripts on a schedule across one or more Azure SQL databases. Option A is incorrect because Azure Logic Apps with SQL connector is better suited for workflows and not primarily for scheduled T-SQL script execution. Option C is incorrect because Azure Functions, while capable of running code with timer triggers, is not the optimal service for scheduled T-SQL scripts; Elastic Database Jobs is the dedicated solution. Option E is incorrect because Azure Data Factory is focused on data movement and orchestration, not direct scheduled execution of T-SQL scripts.

Answer

Azure Logic Apps with SQL connector to execute stored procedures.

Answer

Azure Functions with timer trigger and SqlConnection.

Answer

Azure Data Factory with a SQL Server Stored Procedure activity.

Question 15

Refer to the exhibit. An Azure SQL Database has the above ARM template for long-term retention (LTR) backup policy. Which statement is true about the retention duration?

Accepted Answer

Yearly backups are retained for 5 years.. In the ARM template for LTR backup policy, the yearlyRetention is set to P5Y, which means 5 years. WeeklyRetention is P2W (2 weeks), and monthlyRetention is P6M (6 months). Therefore, only option C is accurate. Option A is incorrect because monthly backups are retained for 6 months, not 12. Option B is incorrect because yearly backups are retained for 5 years, not 10. Option D is incorrect because weekly backups are retained for 2 weeks, not 1 week.

Answer

Monthly backups are retained for 12 months.

Answer

Yearly backups are retained for 10 years.

Answer

Weekly backups are retained for 1 week.