DP-300 Configure and manage automation of tasks — Timed 15 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?

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