DP-300 Configure and manage automation of tasks — Timed 20 Questions

Question 1

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?

20 minute time limit — choose an answer to begin.

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 2

A company uses Azure SQL Database for a critical application. They need to automate the process of exporting a database to a storage account every night, ensuring the export is consistent. The solution must minimize administrative overhead. What should they use?

Accepted Answer

Create an Azure Automation runbook that uses the Export-AzureRmSqlDatabase cmdlet and schedule it to run nightly.. Azure Automation runbooks can execute PowerShell cmdlets like Export-AzureRmSqlDatabase (or the newer Export-AzSqlDatabase) to perform a consistent export of an Azure SQL Database to a storage account. By scheduling the runbook to run nightly, you automate the export with minimal administrative overhead, as the export operation uses database snapshots to ensure consistency without requiring complex orchestration.

Answer

Create an Elastic Database Job that runs a T-SQL script to export the database.

Answer

Deploy an Azure Data Factory pipeline with a Copy activity to export the database.

Answer

Use an Azure Logic App with the SQL Server connector to export the database.

Question 3

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 4

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.

Question 5

Refer to the exhibit. You run this KQL query in Azure Monitor Log Analytics for an Azure SQL Database. What type of events does the query return?

Accepted Answer

Audit log modifications. Action_id_s == 'AUSC' corresponds to AUDIT_CHANGE events in SQL Server audit logs, which track modifications to audit configurations. Option A is wrong because 'AUSC' does not represent failed login events. Option B is wrong because 'AUSC' is not used for schema changes. Option D is wrong because 'AUSC' is not associated with data manipulation statements.

Answer

Failed logins

Answer

Schema changes

Answer

Data manipulation statements

Question 6

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 7

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 8

Which TWO services can be used to automate the backup of Azure SQL Database to a storage account? (Choose two.)

Accepted Answer

Azure Automation with PowerShell runbooks. Azure Automation with PowerShell runbooks can execute Export-AzSqlDatabase to create a BACPAC file in an Azure Storage account. Azure Data Factory supports exporting Azure SQL Database to a BACPAC file in blob storage via the Export activity. Azure Backup stores backups in a Recovery Services vault, not directly in a storage account, so it does not meet the requirement. SQL Agent jobs are not available for Azure SQL Database, and Elastic Database Jobs can only run T-SQL scripts, not backup operations.

Answer

Elastic Database Jobs

Answer

SQL Agent jobs

Answer

Azure Backup

Question 9

You are a database administrator for a retail company that uses Azure SQL Database with the Serverless compute tier. The database experiences unpredictable idle periods, and you want to minimize costs by automatically pausing the database when it is idle for more than 60 minutes and resuming it when a connection is attempted. However, you also need to ensure that a critical reporting job that runs every hour can connect even if the database is paused. What should you do?

Accepted Answer

Enable the serverless auto-pause feature with a delay of 60 minutes. No additional action is needed; the reporting job will automatically resume the database upon connection.. The Serverless tier has built-in auto-pause and auto-resume features. You can configure the auto-pause delay to 60 minutes. The reporting job will automatically resume the database when it connects, as the serverless tier supports auto-resume on connection. Option B is incorrect because disabling auto-pause and using Azure Automation to scale down the database would not automatically pause the database based on idle time, and Azure Automation cannot automatically resume the database on connection. Option C is incorrect because Elastic Jobs cannot resume a paused database; the database must be active for Elastic Jobs to execute. Option D is incorrect because setting the auto-pause delay to 0 would cause the database to pause immediately after any idle period, which would disrupt the reporting job and require manual or automated resume actions.

Answer

Disable auto-pause for the database and use Azure Automation to scale down the database during idle periods.

Answer

Use Elastic Database Jobs to keep the database active by running a lightweight query every 59 minutes.

Answer

Set the auto-pause delay to 0 minutes to minimize costs, and create an Azure Automation runbook to keep the database active during the reporting job.

Question 10

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?

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 11

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 12

You are responsible for automating backups of on-premises SQL Server databases to Azure Blob Storage. The solution must use the least administrative effort and provide point-in-time restore capability. What should you implement?

Accepted Answer

Configure SQL Server Managed Backup to Microsoft Azure.. SQL Server Managed Backup to Microsoft Azure (also known as Managed Backup) is the correct choice because it provides automated, policy-based backup management with minimal administrative effort. It natively supports point-in-time restore by automatically scheduling full, differential, and transaction log backups to Azure Blob Storage, and it handles backup retention and recovery point management without requiring custom scripts or additional infrastructure.

Answer

Install Azure Backup Server on-premises and configure backup of SQL Server databases.

Answer

Use SQL Server Agent jobs to perform full, differential, and log backups to an Azure Blob Storage URL.

Answer

Use Azure Data Factory to copy database backups to Blob Storage.

Question 13

You need to automate the deployment of schema changes to multiple Azure SQL Databases in different regions. The solution must support rollback and version control. Which technology should you use?

Accepted Answer

Use Azure DevOps with a database project and release pipelines.. Azure DevOps with a database project and release pipelines is the correct choice because it provides source control for schema changes, automated deployment across multiple environments, and built-in rollback capabilities through pipeline versioning and deployment history. This approach aligns with infrastructure-as-code principles, enabling consistent, repeatable, and auditable schema deployments to Azure SQL Databases in different regions.

Answer

Use Azure Data Factory to run stored procedures for schema changes.

Answer

Use SQL Server Agent jobs to run deployment scripts on schedule.

Answer

Use Azure Automation with PowerShell scripts to execute T-SQL scripts.

Question 14

You are configuring automated backups for an Azure SQL Database. Which TWO settings can you configure?

Accepted Answer

Backup retention period (in days).. The backup retention period (in days) is a configurable setting for Azure SQL Database automated backups. You can set the retention period for point-in-time restore (PITR) backups between 1 and 35 days, and for long-term retention (LTR) backups up to 10 years. This directly controls how far back you can restore your database.

Answer

Backup compression.

Answer

Backup frequency (full, differential, log).

Answer

Point-in-time restore interval.

Question 15

You need to automate monitoring and alerting for an Azure SQL Database. Which THREE actions can you achieve using Azure Monitor and SQL Insights?

Accepted Answer

Stream diagnostic logs to a Log Analytics workspace.. Azure Monitor and SQL Insights allow you to configure diagnostic settings to stream SQL Database metrics and resource logs (e.g., query store runtime statistics, wait statistics) to a Log Analytics workspace. This enables centralized log analysis, custom KQL queries, and long-term retention for auditing and troubleshooting.

Answer

Automatically rebuild fragmented indexes.

Answer

Automatically scale the database to a higher service tier.

Question 16

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 17

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 18

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 19

You are tasked with automating index maintenance for an Azure SQL Database. Which Azure service should you use to run T-SQL scripts on a recurring schedule?

Accepted Answer

Elastic Database Jobs. Elastic Database Jobs (B) is the correct service for automating T-SQL script execution across Azure SQL Database on a recurring schedule. It is specifically designed for Azure SQL Database and Azure SQL Managed Instance, providing a job scheduler that can run T-SQL scripts against multiple databases, handle retries, and manage job history. SQL Server Agent is not available in Azure SQL Database (only in SQL Server on-premises or Azure SQL Managed Instance), making Elastic Database Jobs the appropriate choice for this PaaS scenario.

Answer

SQL Server Agent

Answer

Azure Automation Runbook

Answer

Azure Logic Apps

Question 20

You need to automate the creation of a new Azure SQL Database whenever a new customer signs up. The solution should use infrastructure as code and integrate with your CI/CD pipeline. What should you use?

Accepted Answer

Create an ARM template that defines the database and deploy it from your CI/CD pipeline.. B is correct because ARM (Azure Resource Manager) templates are the recommended infrastructure-as-code approach for defining and deploying Azure SQL Databases in a repeatable, declarative manner. Integrating ARM template deployment into a CI/CD pipeline ensures consistent, version-controlled database creation as part of automated workflows, aligning with DevOps best practices.

Answer

Create an Azure Automation runbook that calls New-AzureRmSqlDatabase and trigger it from your CI/CD pipeline.

Answer

Set up an Elastic Database Job that runs a CREATE DATABASE statement.

Answer

Configure a SQL Server Agent job on the logical server to run a CREATE DATABASE statement.