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

Question 1

A company uses Azure SQL Managed Instance. They need to automate index maintenance for all databases in the instance. The solution must minimize administrative overhead and use built-in Azure features. What should you do?

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

Accepted Answer

Create an elastic job agent with a T-SQL script for index maintenance targeting all databases.. An elastic job agent is a built-in Azure feature designed specifically for automating administrative tasks across multiple databases in Azure SQL Managed Instance. It allows you to create a T-SQL script for index maintenance and target all databases in the instance with minimal overhead, as it manages scheduling, retries, and target group membership natively.

Answer

Use Azure Automation with a PowerShell runbook that connects to each database and runs index maintenance.

Answer

Configure a SQL Agent job on the instance to run index maintenance on the master database.

Answer

Use Azure Data Factory to schedule a stored procedure execution for each database.

Question 2

You need to automatically scale an Azure SQL Database based on workload patterns. The solution must use built-in Azure features and minimize manual intervention. Which feature should you configure?

Accepted Answer

Configure autoscale settings for the Azure SQL Database.. Azure SQL Database supports built-in autoscale through the 'Autoscale' feature (serverless compute tier or DTU-based scaling policies), which automatically adjusts resources based on workload patterns without manual intervention. This is the only option that leverages a native Azure feature for dynamic, reactive scaling rather than scheduled or manual actions.

Answer

Use Azure Data Factory to scale the database based on pipeline triggers.

Answer

Create an Azure Automation runbook that scales the database on a schedule.

Answer

Use an elastic pool and manually adjust eDTUs.

Question 3

You manage an Azure SQL Database that supports a critical application. You need to automate the process of rebuilding indexes that have fragmentation above 30% on a weekly basis. The solution must use built-in database features and minimize performance impact. What should you do?

Accepted Answer

Schedule a weekly job using elastic jobs to reorganize all indexes.. Elastic Jobs in Azure SQL Database allow you to schedule T-SQL scripts across multiple databases. You can create a job that runs a script to identify indexes with fragmentation above 30% and rebuild them. This uses built-in database features (Elastic Jobs are part of Azure SQL Database) and minimizes performance impact by allowing you to schedule the job during off-peak hours. Option A is not available in Azure SQL Database (single database) as SQL Agent is only supported in Azure SQL Managed Instance. Option B uses Azure Automation, which is an external service and not a built-in database feature. Option C is incorrect because automatic tuning's 'Create index' option creates new indexes, not rebuilds existing ones, and 'Force plan' stabilizes query plans but does not address fragmentation.

Answer

Create a SQL Agent job that runs ALTER INDEX REBUILD on all indexes with fragmentation >30%.

Answer

Use Azure Automation to run a PowerShell script that checks fragmentation and rebuilds indexes.

Answer

Enable automatic tuning and set the 'Force plan' and 'Create index' options.

Question 4

Refer to the exhibit. You are reviewing the JSON configuration for an elastic job. The job is expected to run every week on Sunday at midnight UTC. However, the job is not executing as scheduled. What is the most likely cause?

Accepted Answer

The interval and frequency properties are conflicting; interval should be removed for weekly frequency.. The job configuration includes both an 'interval' property (e.g., 'PT1H' for every hour) and a 'frequency' property set to 'Weekly'. In Azure Elastic Jobs, when 'frequency' is specified, the 'interval' property is ignored or causes a conflict because the schedule is defined by the cron-like recurrence pattern. Removing the 'interval' property resolves the conflict and allows the weekly schedule to execute correctly.

Answer

The retention policy is set too low, causing the job to be pruned before execution.

Answer

The startTime is in the past; the job should have a future start time.

Answer

The job definition is missing the 'scheduleType' property.

Question 5

Refer to the exhibit. You are monitoring index fragmentation in an Azure SQL Database. You need to automate the rebuild of this index when fragmentation exceeds 50%. The rebuild must be online to minimize downtime. Which T-SQL statement should you include in your automated maintenance job?

Accepted Answer

ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = ON);. It uses the `ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = ON)` statement, which rebuilds all indexes on the table online, allowing concurrent user access and minimizing downtime. This meets the requirement to automate the rebuild when fragmentation exceeds 50%, as online rebuilds are supported in Azure SQL Database for this purpose.

Answer

ALTER INDEX Orders ON Orders REBUILD;

Answer

ALTER INDEX ALL ON Orders REORGANIZE;

Answer

ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = OFF);

Question 6

Your company has a critical Azure SQL Database named SalesDB in the West US region. To meet disaster recovery requirements, you configured active geo-replication with a readable secondary replica in East US. You need to automate failover in the event of a regional outage. The solution must ensure that the failover is initiated automatically based on health metrics and that the application connection string is updated to point to the new primary. Currently, the application uses a connection string with the primary server name. You have the following requirements: minimize manual intervention, use built-in Azure features, and ensure that failover is tested regularly. You consider the following options: A) Use Azure Traffic Manager with endpoint monitoring to route traffic to the primary and automatically failover. B) Configure auto-failover group with read-write failover policy and grace period. C) Create an Azure Logic App that queries the database health and runs a PowerShell script to perform failover and update DNS. D) Use Azure Automation with a runbook that checks the database status and initiates failover via REST API. Which option best meets the requirements?

Accepted Answer

Configure auto-failover group with read-write failover policy and grace period.. Auto-failover groups are the built-in Azure feature designed specifically for automating failover of Azure SQL Database geo-replication. They support a read-write failover policy with a configurable grace period that automatically triggers failover based on health metrics, and they automatically update the connection string endpoint for applications using the failover group listener name, not the individual server name. This minimizes manual intervention and allows regular testing via manual failover without affecting the production environment.

Answer

Use Azure Traffic Manager with endpoint monitoring to route traffic to the primary and automatically failover.

Answer

Create an Azure Logic App that queries the database health and runs a PowerShell script to perform failover and update DNS.

Answer

Use Azure Automation with a runbook that checks the database status and initiates failover via REST API.

Question 7

A company uses Azure SQL Managed Instance. They want to automate backups to a storage account for long-term retention beyond the default 35 days. What should they use?

Accepted Answer

Create an Azure Automation runbook that uses the Backup-SqlDatabase cmdlet with -BackupAction 'Database' -CopyOnly and schedule it.. It uses an Azure Automation runbook to schedule the Backup-SqlDatabase cmdlet with -CopyOnly, creating copy-only backups to a storage account. This provides long-term retention beyond the default 35 days by automating the backup process to Azure Blob Storage. While Azure SQL Managed Instance also supports native long-term retention policies via Azure CLI or PowerShell (Set-AzSqlInstanceDatabaseBackupLongTermRetentionPolicy), that approach is not listed among the options. Option D is the only option that offers automated backup to a storage account for retention beyond 35 days.

Answer

Configure a SQL Server Agent job on the Managed Instance to run a T-SQL BACKUP DATABASE TO URL statement.

Answer

Use Azure Backup to configure a backup policy for the Managed Instance.

Answer

Create an Elastic Database Job that runs a T-SQL BACKUP DATABASE TO URL statement.

Question 8

Which TWO options are valid services for scheduling automated tasks for Azure SQL Database? (Choose two.)

Accepted Answer

Azure Automation. Azure Automation (C) is a valid service for scheduling automated tasks against Azure SQL Database because it provides runbooks with schedules and, for T-SQL execution, the Azure Automation Run As account or managed identity can authenticate to the database. Elastic Database Jobs (E) is also valid because it is the Azure SQL Database-native job scheduling feature that runs T-SQL scripts across databases in a pool or server on a defined schedule. SQL Server Agent (A) is not available inside Azure SQL Database (it exists only in SQL Server and Azure SQL Managed Instance), so it cannot be used here. Azure Functions (B) and Azure Logic Apps (D) can trigger workflows and run code on timers, but they are general-purpose compute/integration services rather than dedicated task-scheduling services for Azure SQL Database, so they are not the intended answers.

Answer

SQL Server Agent

Answer

Azure Functions

Answer

Azure Logic Apps

Question 9

Refer to the exhibit. You are reviewing an ARM template snippet for an Azure SQL Database backup long-term retention policy. The template is deployed successfully. Which statement is true about the resulting backup retention?

Accepted Answer

Weekly backups are retained for 4 weeks.. The ARM template snippet defines a backup long-term retention policy for Azure SQL Database. The `weeklyRetention` property is set to `P4W`, which in ISO 8601 duration format means a period of 4 weeks. Therefore, weekly backups are retained for exactly 4 weeks. Option D correctly reflects this configuration.

Answer

Monthly backups are retained for 24 months.

Answer

Weekly backups are retained for 8 weeks.

Answer

Yearly backups are retained for 1 year.

Question 10

You are the database administrator for a large e-commerce company. The company uses Azure SQL Database for its product catalog, which is updated frequently during business hours. To maintain query performance, you have automated index maintenance using Elastic Database Jobs. The job runs every night at 2:00 AM and rebuilds indexes with a fill factor of 80. However, you notice that the job is taking longer than expected, often running until 6:00 AM, and sometimes it fails due to timeouts. You also observe that index fragmentation is still high after the job completes. The database is in the General Purpose service tier with 100 DTUs. Which action should you take to improve the situation?

Accepted Answer

Modify the job to reorganize indexes instead of rebuilding them, and use a lower degree of parallelism.. Reorganizing indexes is a less resource-intensive operation than rebuilding, which reduces the job duration and avoids timeouts. Additionally, using a lower degree of parallelism (MAXDOP) prevents excessive resource contention, which is critical for a General Purpose database with only 100 DTUs. Reorganizing also addresses fragmentation without the overhead of a full rebuild, making it more suitable for the limited resources and nightly maintenance window.

Answer

Create a second Elastic Database Job to run at 4:00 AM to rebuild indexes that failed.

Answer

Change the fill factor to 70 to reduce future fragmentation.

Answer

Increase the DTU level of the database to 200 DTUs to speed up the index rebuild.