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

Question 1

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?

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

Accepted Answer

Create a scheduled Azure Logic App that uses the Azure SQL Database REST API to update the service objective.. Azure Logic Apps can be scheduled to run a workflow that uses the Azure SQL Database REST API (or Azure Resource Manager REST API) to update the service objective (DTU or vCore) of an Azure SQL Database. This enables automated scaling without custom infrastructure. Option A is incorrect because SQL Agent jobs run within the SQL Server context and cannot directly change the service objective; the ALTER DATABASE MODIFY (SERVICE_OBJECTIVE) command is not valid. Option C is incorrect because Elastic Job agents are designed for executing T-SQL scripts across multiple databases, not for changing service tiers. Option D is incorrect because Azure SQL Database does not support autoscale; scaling must be performed manually or programmatically.

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 2

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 3

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 automatically applies OS and SQL Server engine updates during the user-configured maintenance window, minimizing downtime by performing patching during off-peak hours. Option B is wrong because Elastic Job agents are used for scheduling T-SQL jobs across multiple databases, not for patching the instance or OS. Option C is wrong because manual patching via the Azure portal is not an automated solution and would require manual effort and cause downtime. Option D is wrong because Azure Update Manager is designed for Azure VMs and Arc-enabled servers, not for Azure SQL Managed Instance, which has its own built-in patching mechanism.

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 4

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 provide a declarative, Infrastructure-as-Code approach that is repeatable and can be version-controlled. Option B is wrong because while PowerShell can automate, it is procedural and not as easily version-controlled or idempotent as ARM templates. Option C is wrong because the Azure portal is manual and not repeatable. Option D is wrong because SSMS scripting is also manual and not suitable for automated deployment.

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 5

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 D and E are correct. Enabling SQL Insights (preview) provides intelligent performance monitoring with built-in anomaly detection, and creating an Azure Monitor alert rule on DTU/CPU and storage metrics enables automated notifications when thresholds are exceeded. Option A is incorrect because Power BI is a reporting tool, not designed for real-time alerting on performance anomalies. Option B is incorrect because Query Store tracks query performance but does not natively support email notifications. Option C is incorrect because SQL Agent alerts are for job scheduling events, not for monitoring performance counters like DTU or CPU.

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 6

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, B, and D are correct. Long-term retention (LTR) backup policy provides archival backups for compliance and point-in-time restore. Active geo-replication maintains a synchronized readable secondary database with a low RPO (typically less than 5 seconds). Auto-failover groups automate failover and enable a low RTO (typically less than 1 hour). Together, these three features meet the RPO of 5 minutes and RTO of 1 hour. Option C (Azure Backup service) is not used for Azure SQL Database; it is for Azure VMs and on-premises workloads. Option E (Point-in-time restore) can meet the RPO but does not provide automated failover and has a higher RTO, so it is insufficient alone.

Answer

Azure Backup service.

Answer

Point-in-time restore (PITR).

Question 7

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) and ARM templates (C) are both declarative Infrastructure as Code (IaC) tools for defining Azure resources, including Azure SQL Database, firewall rules, Entra ID admin, and vulnerability assessment baselines. An Azure DevOps release pipeline (D) can automate the deployment of these templates, ensuring idempotent and repeatable deployments. SQL Server Management Studio (A) is a management tool for SQL Server, not for IaC or automated deployment. The Azure portal (E) is a manual GUI and not suitable for repeatable automation.

Answer

SQL Server Management Studio.

Answer

Azure portal.

Question 8

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 provides built-in SQL injection detection, which can supersede the need for separate Security Alert Policy rules. When Defender for SQL is enabled, it automatically monitors and alerts on SQL injection attempts, so duplicating with a custom alert policy would be redundant and could cause confusion. Option A is incorrect because disabling alerts does not save costs; it weakens security. Option B is incorrect because vulnerability assessment policies do not override alert rules. Option C is incorrect because there is no known incompatibility between SQL injection alerts and storage account endpoints.

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 9

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.. Azure Automation Runbooks can be scheduled to execute PowerShell scripts that change the service objective of an Azure SQL Database (e.g., Set-AzSqlDatabase with the -RequestedServiceObjectiveName parameter). This allows scheduling a scale-up before Black Friday and a scale-down after 48 hours, meeting the temporary upgrade requirement. Additionally, Azure Policy can enforce backup retention policies (e.g., 35-day PITR) and specify geo-redundant storage only for critical databases, ensuring compliance and cost minimization. Option A is incorrect because Elastic Job agents are for executing T-SQL scripts across many databases, not for scheduling one-off scaling actions, and backup retention via portal is not automated. Option B is incorrect because Azure SQL Database's autoscale feature is only available in the serverless compute tier, not for provisioned service objectives like S6/S9, and it adjusts based on workload automatically, not on a scheduled temporary upgrade basis. Option C is incorrect because SQL Server Agent is not available in Azure SQL Database (it's used in SQL Server on-premises or Azure SQL Managed Instance), and ALTER DATABASE MODIFY SERVICE_OBJECTIVE is not supported in Azure SQL Database; scaling requires Azure PowerShell or REST API.

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 10

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.. SQL Agent jobs in Azure SQL Managed Instance can execute T-SQL scripts across databases using sp_foreachdb (or a cursor-based loop) to run compliance scripts on each database. Results can be inserted into a central database using three-part names (or cross-database queries). Email notifications can be sent using Database Mail (sp_send_dbmail). Option A is incorrect because Logic Apps are external tools and not built-in Azure features for this purpose. Option B is incorrect because Azure Automation Runbooks require external connectivity and are not directly integrated with SQL Managed Instance for cross-database execution. Option D is incorrect because Elastic Jobs are designed for Azure SQL Database, not for SQL Managed Instance.

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 11

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

Use Elastic Database Jobs to run a script that rebuilds/reorganizes indexes.. Elastic Database Jobs can be used to run a T-SQL script on a schedule, checking fragmentation thresholds and performing rebuilds/reorganizations. This meets the requirement of weekly execution while checking thresholds (30% and 10%). Option D is incorrect because automatic index tuning in Azure SQL Database does not support a fixed schedule; it operates in real-time based on workload and cannot be set to run weekly. Options A and C are not suitable: A uses a SQL Agent job which is not available in Azure SQL Database (only in SQL Server on VMs), and C pertains to SQL Server on VMs, not Azure SQL Database.

Answer

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

Answer

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

Answer

Enable automatic index tuning in Azure SQL Database with the desired fragmentation thresholds.

Question 12

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).. The most efficient solution because Azure Monitor provides built-in metrics for transactional replication on Azure SQL Managed Instance, such as 'Log Send Queue Size'. By creating a metric alert with a threshold of 300000 KB (which corresponds to approximately 5 minutes of latency at typical throughput), you can be notified automatically when latency exceeds this threshold. This approach uses native Azure Monitoring capabilities without requiring custom jobs or scripts. Options A, B, and C are less efficient: A requires Elastic Database Jobs and a logic app; B uses a performance counter that is not directly exposed in Azure Monitor for SQL Managed Instance and is more complex to set up; C requires creating a SQL Agent job to check a DMV and send email, which is manual and not as scalable as Azure Monitor alerts.

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 13

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

Azure Automation runbook with PowerShell cmdlets to modify the service objective.. Azure SQL Database does not have built-in autoscale based on CPU thresholds to change service tiers. The correct approach is to use Azure Automation runbooks, which can be triggered by Azure Monitor alerts or schedules to execute PowerShell cmdlets like Set-AzSqlDatabase to modify the service objective. Option D is incorrect because autoscale settings in the Azure portal are not available for SQL Database. Option B is for running jobs across databases, not for scaling. Option C can also work but is less direct than Azure Automation for this automation scenario.

Answer

Elastic Database Jobs to run ALTER DATABASE MODIFY.

Answer

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

Answer

Configure autoscale settings in the Azure portal for the SQL database server.

Question 14

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.. The serverless compute tier in Azure SQL Database automatically pauses after a period of inactivity and resumes on the first connection. By setting the auto-pause delay to 10 hours (covering the 8 PM to 6 AM window), the database will pause during non-business hours and automatically resume when a connection is attempted. Options A and D are unnecessary because the serverless tier handles this natively, and Option B is incorrect because elastic jobs are not used for scaling to 0 vCores.

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 15

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