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

Question 1

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

Use the Query Performance Insight blade in the Azure portal and schedule an export to a storage account.. Option B is correct because Query Performance Insight is built into Azure SQL Database and provides query-level performance data. Option A is wrong because Azure Monitor Workbooks can visualize data but require a data source; they do not query Query Store directly. Option C is wrong because SQL Agent jobs can run queries but do not have built-in reporting capabilities. Option D is wrong because Power BI requires an export mechanism.

Answer

Configure a Power BI dashboard that connects directly to Azure SQL Database Query Store.

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.

Question 2

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.. Option A is correct because Managed Instance automatically handles patching during the configured maintenance window. Option B is wrong because Azure Update Manager is not applicable to Managed Instance. Option C is wrong because manual patching is not automated. Option D is wrong because elastic jobs are for database-level tasks, not patching.

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 3

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.. Option B is correct because ARM templates are declarative and version-controlled. Option A is wrong because the Azure portal is manual. Option C is wrong because SSMS is manual. Option D is wrong because PowerShell is procedural, not the best for version-controlled repeatable deployments.

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.

Question 4

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.. Options A and D are correct. Azure Monitor alerts can be configured on performance metrics, and SQL Insights provides intelligent monitoring. Option B is wrong because SQL Agent does not monitor metrics. Option C is wrong because email notifications are not native to Query Store. Option E is wrong because Power BI is for reporting, not real-time alerting.

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 5

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.. Options A, C, and D are correct. Active geo-replication provides automatic failover with low RPO, long-term retention for archival, and auto-failover groups for automated failover. Option B is wrong because point-in-time restore does not provide automated failover. Option E is wrong because Azure Backup service is not used for Azure SQL Database.

Answer

Azure Backup service.

Answer

Point-in-time restore (PITR).

Question 6

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.. Options A, B, and D are correct. ARM templates define infrastructure, Azure DevOps pipelines automate deployment, and Bicep is a declarative language for ARM. Option C is wrong because the Azure portal is manual. Option E is wrong because SQL Server Management Studio is not for infrastructure deployment.

Answer

SQL Server Management Studio.

Answer

Azure portal.

Question 7

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.. Option C is correct. SQL Injection alerts may be disabled if an alternative mechanism, such as Microsoft Defender for SQL, is used to detect SQL injection attacks. Option A is wrong because disabling alerts does not reduce cost; it reduces security. Option B is wrong because there is no conflicting policy. Option D is wrong because there is no known compatibility issue.

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.

Question 8

You are a database administrator for a large e-commerce company that uses Azure SQL Database for its transactional systems. The environment consists of 50 databases across 10 logical servers, each with a mix of General Purpose and Business Critical service tiers. The company has a strict requirement to automatically scale databases based on workload patterns to optimize cost without manual intervention. Specifically, during Black Friday sales, one of the Business Critical databases (DB-Sales) experiences a surge in transactions, and you need to temporarily upgrade it to a higher service objective (S9 instead of S6) for 48 hours. After the sale, it should automatically revert to S6. Additionally, you need to ensure that all databases have automated backups with a 35-day point-in-time restore retention and that backup storage costs are minimized by using geo-redundant storage only for critical databases. You have been asked to design an automation solution using Azure native services. Which approach should you recommend?

Accepted Answer

Use Azure Automation Runbooks scheduled to run before and after Black Friday to change the service objective, and set backup retention policies using Azure Policy.. Option B is correct because Azure Automation Runbooks can be scheduled to change the service objective of a database, and policies for backup retention are set at the server level but can be overridden per database using ARM templates or PowerShell. Option A is wrong because elastic jobs are for executing scripts across databases, not for scheduling scaling. Option C is wrong because autoscale is not available for Azure SQL Database. Option D is wrong because SQL Agent cannot change service objectives or set backup retention.

Answer

Use Elastic Job agents to run a script that alters the service objective and configure backup retention using the Azure portal.

Answer

Enable autoscale on the database to automatically adjust service objective based on CPU usage.

Answer

Create a SQL Server Agent job that runs ALTER DATABASE MODIFY (SERVICE_OBJECTIVE = 'S9') and configure backup retention using T-SQL.

Question 9

You are a senior database administrator for a financial services company that uses Azure SQL Managed Instance to host multiple customer databases. The company has a requirement to automatically execute a series of compliance scripts every Sunday at 2:00 AM against all databases in the instance. The scripts include checking for orphaned users, verifying data encryption, and auditing login attempts. The solution must log the execution results in a central table and send an email summary to the compliance team. You have been asked to implement this automation using built-in Azure features without relying on external tools like Power Automate or custom schedulers. What should you use?

Accepted Answer

Create a SQL Agent job that runs a T-SQL script using sp_foreachdb to execute the compliance scripts on each database, store results in a central database, and send an email using Database Mail.. Option D is correct because SQL Agent jobs in Managed Instance can execute T-SQL scripts across databases using sp_send_dbmail for email, and cross-database queries can log results to a central database. Option A is wrong because elastic jobs target Azure SQL Database, not Managed Instance. Option B is wrong because Azure Automation Runbooks require external connectivity and cannot directly execute T-SQL across databases. Option C is wrong because Logic Apps are external and require additional configuration.

Answer

Schedule a Logic App that connects to each database via the SQL connector and runs the scripts.

Answer

Use Azure Automation Runbooks with the PowerShell module for SQL Server to execute scripts on each database.

Answer

Create an Elastic Job agent with a job that runs the scripts against each database and logs results to a central database.

Question 10

You are responsible for automating index maintenance in Azure SQL Database. You need to ensure that index rebuilds and reorganizations are performed only when fragmentation exceeds 30% and 10%, respectively, and that the job runs weekly. Which approach should you use?

Accepted Answer

Enable automatic index tuning in Azure SQL Database with the desired fragmentation thresholds.. Option C is correct because Azure SQL Database's built-in automatic index tuning can be configured to handle index maintenance based on fragmentation thresholds without custom scripting. Options A and B manage indexes at the database level but require manual job creation. Option D is for on-premises or SQL Server on VM, not Azure SQL Database managed instance or single database.

Answer

Create a SQL Agent job using T-SQL script with sys.dm_db_index_physical_stats.

Answer

Use Elastic Database Jobs to run a script that rebuilds/reorganizes indexes.

Answer

Schedule a maintenance task using SQL Server Agent in a VM running SQL Server.

Question 11

Your company uses Azure SQL Managed Instance with transactional replication. You need to automate the monitoring of replication latency and send an alert if latency exceeds 5 minutes. You want to use Azure Monitoring capabilities. What is the most efficient solution?

Accepted Answer

Create an Azure Monitor metric alert on the 'Log Send Queue Size' metric with a threshold of 300000 KB (approximately 5 minutes).. Option A is correct because Azure SQL Managed Instance exposes replication metrics like Log Send Queue Size and Transaction Delay in Azure Monitor, which can be used to create alerts. Options B and C require custom scripting and are less efficient. Option D uses a deprecated metric.

Answer

Use Elastic Database Jobs to run a query on the distributor and log results to a table, then set up a logic app to check the table.

Answer

Monitor the 'Replication: Dist Delivery Latency' performance counter.

Answer

Create a SQL Agent job on the publisher that checks sys.dm_repl_sync_status and sends an email.

Question 12

You need to automatically scale up an Azure SQL Database to the next service tier when CPU usage exceeds 80% for 10 minutes, then scale back down when CPU drops below 30% for 30 minutes. Which Azure feature should you use?

Accepted Answer

Configure autoscale settings in the Azure portal for the SQL database server.. Option D is correct because autoscale settings in Azure SQL Database can be configured to scale based on metrics like CPU percentage. Options A and B are for different purposes. Option C is not a built-in feature.

Answer

Azure Automation runbook with PowerShell cmdlets to modify the service objective.

Answer

Elastic Database Jobs to run ALTER DATABASE MODIFY.

Answer

Azure Logic Apps with a recurrence trigger and SQL DB REST API.

Question 13

You have an Azure SQL Database that uses a serverless compute tier. You want to automate the process of pausing the database during non-business hours (8 PM to 6 AM) to save costs. The database should automatically resume when the first connection is attempted. What should you configure?

Accepted Answer

Set the auto-pause delay to 10 hours in the serverless configuration.. Option A is correct because serverless databases automatically pause after a period of inactivity and resume on first connection; you can set the auto-pause delay to 10 hours (from 8 PM to 6 AM). Options B and C are not applicable. Option D is for elastic pools.

Answer

Create a SQL Agent job to run ALTER DATABASE PAUSE and ALTER DATABASE RESUME.

Answer

Configure an elastic job to scale down to 0 vCores during off-hours.

Answer

Use Azure Automation to run a script that calls the REST API to pause/resume the database.

Question 14

You are designing an automation strategy for deploying schema changes to 50 Azure SQL Databases in a elastic pool. You need to ensure that changes are applied in a consistent order and rolled back if any database fails. Which approach should you use?

Accepted Answer

Create an Elastic Job that executes the schema change script within a transaction and uses target group error handling to roll back on failure.. Option B is correct because Azure SQL Database Elastic Jobs can run T-SQL scripts across multiple databases with transactional control and error handling. Options A and C do not provide rollback capability. Option D is for infrastructure deployment, not schema changes.

Answer

Use Azure Data Studio with the Schema Compare extension and apply changes manually.

Answer

Deploy the schema changes using Azure Resource Manager templates with SQL extensions.

Answer

Use PowerShell to loop through each database and execute the script, catching exceptions.

Question 15

You need to automate the backup of an on-premises SQL Server database to Azure Blob Storage using the latest Microsoft recommended method. Which approach should you use?

Accepted Answer

Use SQL Server Management Studio to create a maintenance plan that uses the 'Back Up Database' task with destination = URL.. Option A is correct because SQL Server Backup to URL using the BACKUP TO URL command with a shared access signature is the recommended method for backing up to Azure Blob Storage. Options B and C are outdated or less secure. Option D requires additional software.

Answer

Use PowerShell with the Backup-SqlDatabase cmdlet and the -BackupContainer parameter.

Answer

Use Azure Backup for SQL Server in Azure VMs.

Answer

Use the 'Backup to Windows Azure' feature in SQL Server 2012.