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

Question 1

You are designing an automated backup strategy for Azure SQL Database. The compliance policy requires point-in-time restore (PITR) for the last 35 days, and long-term retention (LTR) for 7 years. What is the minimal number of LTR policies needed?

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

Accepted Answer

One LTR policy per database.. LTR policies are applied at the database level in Azure SQL Database. Each database requires its own LTR policy to retain backups beyond the PITR retention period. Option A is wrong because policies are per database, not per elastic pool. Option B is wrong because two policies per server (weekly and yearly) are not a minimal requirement; each database can have multiple LTR policies as needed. Option C is wrong because one LTR policy cannot cover all databases on a server; each database has its own retention requirements.

Answer

One LTR policy per elastic pool.

Answer

Two LTR policies per server (weekly and yearly).

Answer

One LTR policy per server.

Question 2

Your team uses GitHub Actions for CI/CD. You need to automatically deploy schema changes to an Azure SQL Database after a pull request merge. The solution must use a service principal with minimal permissions. Which authentication method should the workflow use?

Accepted Answer

Service principal with a client secret stored in GitHub Secrets.. Using a service principal with a client secret stored in GitHub Secrets is a standard method for authenticating to Azure from GitHub Actions with minimal permissions. The service principal can be assigned only the necessary roles, such as db_owner on the database. Managed identity is not directly supported for GitHub-hosted runners. SQL authentication is less secure and not minimal. Connection strings in environment variables are not recommended.

Answer

Azure CLI action using a user-assigned managed identity for the GitHub runner.

Answer

Environment variables containing the connection string.

Answer

SQL authentication using a user name and password stored in GitHub Secrets.

Question 3

You need to run a complex T-SQL script on an Azure SQL Database every hour. The script performs data transformations that must be logged for auditing. Which native Azure service should you use?

Accepted Answer

Elastic Database Jobs with a T-SQL job step.. Elastic Database Jobs is a native Azure SQL Database feature that allows running T-SQL scripts across multiple databases on a schedule, with logging capabilities. It is designed for recurring administrative tasks like complex transformations and auditing, making it the correct choice.

Answer

SQL Agent job on the Azure SQL Database.

Answer

Azure Data Factory with a stored procedure activity.

Answer

Azure Automation Account with a PowerShell runbook that uses Invoke-SqlCmd.

Question 4

You have an Azure SQL Database that needs to be automatically scaled up during peak hours and scaled down during off-peak. The solution must use native Azure capabilities without custom code. What should you use?

Accepted Answer

Create Elastic Database Jobs that run ALTER DATABASE to change the service objective at scheduled times.. Elastic Database Jobs can be used to run ALTER DATABASE statements on a schedule, enabling automated scaling without custom code. Option A is wrong because it requires custom code (PowerShell) and is not a native Azure SQL capability. Option B is wrong because autoscale for elastic pools adjusts per-database DTUs, not the overall database service objective, and does not support scheduled scaling. Option C is wrong because autoscale settings are not available for Azure SQL Database at the server level.

Answer

Use Azure Functions with timer trigger and PowerShell to change the pricing tier.

Answer

Configure autoscale for the elastic pool that contains the database.

Answer

Configure autoscale settings on the Azure SQL Database server.

Question 5

You are responsible for automating database maintenance on multiple Azure SQL Databases in an elastic pool. You need to rebuild fragmented indexes weekly. The solution should minimize resource contention. What should you implement?

Accepted Answer

Use Elastic Database Jobs with a T-SQL script that rebuilds indexes, and schedule it weekly.. Elastic Database Jobs enable you to schedule T-SQL scripts that run against multiple databases in an elastic pool with built-in concurrency control, minimizing resource contention. Option A is incorrect because automatic tuning does not include an INDEX_REBUILD option; it focuses on automatic index management and plan correction, not scheduled index rebuilds. Option B is incorrect because SQL Agent is not available in Azure SQL Database (it is only available in SQL Managed Instance or SQL Server on Azure VMs). Option D is incorrect because running a PowerShell script sequentially against each database would likely increase resource contention and lacks the scheduling and concurrency management provided by Elastic Database Jobs.

Answer

Enable automatic tuning and set 'INDEX_REBUILD' option.

Answer

Create a SQL Agent job on each database to rebuild indexes during off-peak.

Answer

Write a PowerShell script that connects to each database sequentially and rebuilds indexes.

Question 6

Which TWO of the following are valid methods to automate backups for Azure SQL Managed Instance? (Select exactly two.)

Accepted Answer

Schedule T-SQL BACKUP DATABASE TO URL statements via SQL Agent jobs.. Option B is correct because Azure SQL Managed Instance supports native T-SQL BACKUP DATABASE ... TO URL statements, which can be scheduled through SQL Server Agent jobs to write full, differential, and log backups directly to Azure Blob Storage. Option D is correct because configuring long-term retention (LTR) policies on the managed instance lets you automatically copy the built-in automated backups to Azure Blob Storage for up to 10 years, satisfying automated backup retention requirements. Option A is incorrect because Azure Backup does not back up Azure SQL Managed Instance; it targets Azure VMs, file shares, and other workloads, not the managed instance's SQL engine. Option C is incorrect because Azure Site Recovery provides disaster recovery replication for VMs and physical servers, not for Azure SQL Managed Instance databases. Option E is incorrect because Azure VM backup with the backup extension applies to IaaS virtual machines, whereas Azure SQL Managed Instance is a PaaS service that does not expose the VM for extension-based backup.

Answer

Use Azure Backup to schedule full backups.

Answer

Use Azure Site Recovery to replicate the instance.

Answer

Use Azure VM backup by installing the backup extension.

Question 7

Refer to the exhibit. You are reviewing an Elastic Database Job definition for Azure SQL Database. The job is scheduled to run once on January 15, 2026. Which statement about the job's target is correct?

Accepted Answer

The job will target all databases in the server except 'db1'.. In an Elastic Database Job for Azure SQL Database, the target can be specified as a set of databases, and the 'exclude' option allows excluding specific databases. If the job definition includes a target with 'exclude' containing 'db1', the job will run against all databases in the server except 'db1'. This is a common pattern for applying maintenance tasks to all databases except a specific one.

Answer

The job will target only the database named 'db1'.

Answer

The job will target the entire logical server.

Answer

The job will target the server and all its databases.

Question 8

Refer to the exhibit. You are reviewing an Azure Automation runbook configuration that performs backups. What is the most likely issue with this configuration?

Accepted Answer

The retention period (7 days) equals the backup interval (7 days), which could cause data loss if a backup fails.. The most likely issue is that the retention period (7 days) equals the backup interval (7 days). If a backup fails, the previous backup might be deleted before a new successful backup is created, leading to data loss. Best practice is to set retention longer than the interval to ensure at least one backup is always available.

Answer

The runbook is disabled (isEnabled: false).

Answer

The storage account type Standard_LRS is not suitable for backups.

Answer

The backup description is missing required information.

Question 9

You manage a critical Azure SQL Database that requires automated index maintenance. You need to ensure that index rebuilds only occur during low-usage periods defined in a schedule, while index reorganizations run more frequently. What is the most efficient way to implement this using Azure Automation?

Accepted Answer

Create an Elastic Database Job that runs a T-SQL script to rebuild and reorganize indexes based on fragmentation thresholds, scheduled via job schedule.. The question specifies 'using Azure Automation', but Elastic Database Jobs are the most efficient service for scheduling T-SQL index maintenance tasks on Azure SQL Database. They are purpose-built for executing T-SQL scripts against a database (or group of databases) on a schedule, with built-in retry logic and monitoring. While Azure Automation runbooks (option C) can also execute T-SQL, they are not designed specifically for database jobs and require more manual setup for scheduling, error handling, and scaling. Therefore, Elastic Database Jobs (option A) are the recommended approach, even though they are not part of the Azure Automation service. The key is to recognize that for automated index maintenance on Azure SQL Database, Elastic Database Jobs provide the most efficient and effective solution.

Answer

Configure SQL Agent on the Azure SQL Database logical server to run index maintenance jobs.

Answer

Use Azure Automation runbooks with the Invoke-Sqlcmd cmdlet to run index maintenance scripts on a schedule.

Answer

Deploy an Azure Function with a timer trigger that connects to the database and runs index maintenance commands.

Question 10

Your company uses Azure SQL Managed Instance and wants to automate the creation of new databases for each development sprint. Each database must be a copy of a template database with specific schema and data. You need to recommend an automation solution that minimizes manual steps and integrates with your CI/CD pipeline. What should you use?

Accepted Answer

Create an Elastic Database Job that runs CREATE DATABASE AS COPY OF.... For Azure SQL Managed Instance, automating database creation with CI/CD integration can be achieved using Elastic Database Jobs (now Elastic Jobs). These jobs can run T-SQL commands such as 'CREATE DATABASE AS COPY OF' to create copies of a template database. Elastic Jobs can be triggered programmatically via REST API or Azure DevOps tasks, enabling seamless integration into a CI/CD pipeline. Option A (Logic App) does not support database copy operations. Option B (portal feature) requires manual steps. Option D uses PowerShell cmdlets ('az sql db copy' or 'Restore-AzSqlDatabase') that are specific to Azure SQL Database, not Managed Instance; the correct cmdlets for Managed Instance would be 'Restore-AzSqlInstanceDatabase' or T-SQL commands. Therefore, option C is the most appropriate recommendation.

Answer

Deploy an Azure Logic App with a SQL connector that triggers on a schedule.

Answer

Use the Azure portal's 'Create database from backup' feature.

Answer

Use an Azure DevOps pipeline with a PowerShell task that runs the 'az sql db copy' command or the Restore-AzSqlDatabase cmdlet.

Question 11

You are troubleshooting a failed automated backup for an Azure SQL Database. The backup policy is configured for geo-redundant storage (RA-GRS). You notice that the last successful backup was 48 hours ago. The database is still online and accessible. What is the most likely cause of the backup failure?

Accepted Answer

The database is experiencing high transaction log generation, exceeding the backup throughput limit.. Azure SQL Database automated backups can fail if the database generates a high volume of transaction logs, causing the backup process to exceed its throughput limit or timeout. This can happen even if the database is online and accessible. Option A (storage account issues) would likely cause persistent failures for all backups, not just a single one. Option C (geo-replication link) is unrelated to backup failure. Option D (paused state) would prevent backups entirely. Therefore, B is the correct answer.

Answer

The backup storage account has been deleted or has incorrect firewall rules.

Answer

The geo-replication link is broken, causing backup failures.

Answer

The database is in a paused state due to serverless compute.

Question 12

Your Azure SQL Database is configured with active geo-replication. You need to automate the failover process in case of a regional outage. The solution should ensure minimal data loss and support testing without affecting the production environment. What should you use?

Accepted Answer

Create an Azure Automation runbook that monitors health and executes a manual failover using PowerShell cmdlet Start-AzSqlDatabaseFailover.. Azure SQL Database active geo-replication supports manual failover only; automatic failover is not available. To automate the process, you can use Azure Automation runbooks that invoke the Start-AzSqlDatabaseFailover cmdlet. This allows for scheduled or monitored failover, supports testing by targeting the secondary replica, and minimizes data loss by ensuring the primary is synchronized before failover. Option A is incorrect because Azure Traffic Manager performs DNS-level routing, not database failover. Option B is incorrect because Azure Load Balancer is for network traffic distribution, not SQL database failover. Option D is incorrect because geo-replication does not provide automatic failover; failover must be initiated manually or via automation.

Answer

Configure an Azure Traffic Manager profile with endpoint monitoring and failover.

Answer

Use Azure Load Balancer with a health probe to redirect traffic.

Answer

Enable automatic failover for the geo-replication group.

Question 13

You are automating the scaling of an Azure SQL Database based on workload patterns. The database uses the DTU purchasing model. You need to ensure that scaling actions do not cause connection drops. Which scaling method should you use?

Accepted Answer

Use the Azure portal or PowerShell to change the service tier within the same DTU tier (e.g., S2 to S3) to avoid downtime.. Changing the service tier within the same DTU tier (e.g., S2 to S3) is an online operation that does not cause connection drops. Azure SQL Database performs this scaling by adjusting resources while keeping the database online, so existing connections remain active. This is the recommended method for avoiding downtime when scaling DTU-based databases.

Answer

Create a new database at the target size and use point-in-time restore to copy data.

Answer

Scale using the ALTER DATABASE T-SQL command with the ONLINE option.

Answer

Use Elastic Database Jobs to schedule scaling during maintenance windows.

Question 14

You need to automate the generation of a weekly report that shows the top 10 queries by CPU consumption in an Azure SQL Database. The report should be emailed to the DBA team. Which service should you use to gather the query performance data?

Accepted Answer

Query Performance Insight. Query Performance Insight in Azure SQL Database provides query-level performance metrics including CPU, duration, and execution count. It can be used to identify the top 10 queries by CPU consumption and automate report generation. Option D is correct. Option A (SQL Server Profiler) is a traditional tool for capturing SQL Server events, not for automated reporting in Azure. Option B (Database Engine Tuning Advisor) focuses on index recommendations, not report generation. Option C (Azure Monitor Metrics) provides platform-level metrics but lacks query-level granularity.

Answer

SQL Server Profiler

Answer

Database Engine Tuning Advisor

Answer

Azure Monitor Metrics

Question 15

Which THREE components are part of an Azure SQL Managed Instance automated maintenance window configuration? (Choose three.)

Accepted Answer

Start time (e.g., 2:00 AM local time). The automated maintenance window for Azure SQL Managed Instance is configured with three specific settings: a start time (Option A), such as 2:00 AM local time, which defines when the maintenance window begins; a day of week (Option B), such as Sunday, which specifies which day the recurring maintenance window occurs; and a notification email (Option E) used to alert administrators ahead of upcoming maintenance events. These three values are exactly what the maintenance window configuration exposes in the Azure portal and via PowerShell/CLI, and they apply at the managed instance level rather than per database. Option C is incorrect because maintenance scheduling is defined at the managed instance level, not as a database-level maintenance schedule. Option D is incorrect because there is no setting for a maximum number of concurrent databases in the maintenance window configuration; concurrency is not a user-configurable parameter for this feature.

Answer

Database-level maintenance schedule

Answer

Maximum number of concurrent databases