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

Question 1

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?

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

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

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 4

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.

Question 5

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

Accepted Answer

Azure Automation. Azure Automation is correct because it provides a cloud-based automation and configuration service that supports scheduling PowerShell or Python runbooks to execute tasks against Azure SQL Database, such as running T-SQL scripts or performing maintenance operations. It integrates natively with Azure SQL via the Az module and can be triggered on a recurring schedule, making it a valid service for scheduling automated tasks.

Answer

SQL Server Agent

Answer

Azure Functions

Answer

Azure Logic Apps

Question 6

Which THREE actions can be performed by using Elastic Database Jobs in Azure SQL Database? (Choose three.)

Accepted Answer

Run a T-SQL script to update statistics across multiple databases.. Elastic Database Jobs in Azure SQL Database are designed to automate administrative tasks across multiple databases. Running a T-SQL script to update statistics is a common maintenance operation that can be executed in parallel across a target group of databases, making option A correct.

Answer

Create a new Azure SQL Database.

Answer

Change the service tier objective (SLO) of a database.

Question 7

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 8

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 9

You are configuring automated backup retention for Azure SQL Managed Instance. The compliance policy requires that you be able to restore a database to any point within the last 90 days, and that you keep backups for a minimum of 7 years for auditing purposes. Which backup retention policy should you configure?

Accepted Answer

Set PITR retention to 35 days and configure a long-term retention (LTR) policy to keep weekly backups for 7 years.. Azure SQL Managed Instance's point-in-time restore (PITR) retention is capped at 35 days, so to meet the 90-day restore requirement, you must combine a 35-day PITR policy with a long-term retention (LTR) policy that keeps weekly full backups for 7 years. LTR allows you to restore to any point within the LTR window by using full, differential, and log backups, satisfying both the 90-day point-in-time restore and the 7-year auditing compliance.

Answer

Set point-in-time restore (PITR) retention to 90 days.

Answer

Set PITR retention to 90 days and configure geo-replication to achieve the 7-year retention.

Answer

Configure geo-redundant backup storage with a retention of 90 days.

Question 10

You are designing an automated backup strategy for Azure SQL Database. Which TWO actions satisfy a requirement to maintain daily backups for 30 days and ensure recoverability if the Azure region becomes unavailable?

Accepted Answer

Enable geo-redundant backup storage for the server.. Enabling geo-redundant backup storage (RA-GRS) for Azure SQL Database ensures that automated backups are replicated to a paired Azure region. This satisfies the requirement for recoverability if the primary region becomes unavailable, as backups can be restored from the secondary region. Combined with setting point-in-time restore retention to 30 days (Option C), you maintain daily backups for the full 30-day retention period, meeting both requirements.

Answer

Deploy a read-scale replica in a different Azure region.

Answer

Set up a long-term retention policy with a retention of 30 days.

Answer

Configure active geo-replication to a secondary region.

Question 11

You are managing an Azure SQL Database that runs a critical business application. The database experiences a predictable surge in read-only queries every night at 2:00 AM. You need to configure automatic scaling to handle this surge without manual intervention. What should you do?

Accepted Answer

Configure autoscale settings on the Azure SQL Database using Azure Automation runbooks triggered by a metric alert.. Azure SQL Database does not natively support automatic scaling based on load. To achieve this, you must use Azure Automation runbooks triggered by a metric alert (e.g., DTU or CPU percentage) to programmatically scale the database's service tier up or down. This approach allows you to handle the predictable nightly surge without manual intervention, as the runbook can be scheduled or triggered by a threshold alert.

Answer

Create a read replica and redirect read queries to it during the surge.

Answer

Manually scale up the database service tier before 2:00 AM each day.

Answer

Move the database to an Elastic Database Pool and rely on its built-in autoscaling.

Question 12

You are automating index maintenance for an Azure SQL Database. Which THREE of the following should you consider to minimize performance impact?

Accepted Answer

Use the ONLINE = ON option when rebuilding indexes.. Using ONLINE = ON when rebuilding indexes in Azure SQL Database allows concurrent user access to the underlying table during the index rebuild operation. This minimizes blocking and performance impact on production workloads, as the index rebuild is performed as a series of short-term locks rather than a single long-term schema modification lock.

Answer

Always use SORT_IN_TEMPDB = ON to reduce tempdb contention.

Answer

Set MAXDOP to 0 to use all CPUs for faster execution.

Question 13

You are reviewing an Azure SQL Database automated backup retention policy. The exhibit shows the current configuration. You need to ensure that the database can be restored to a point in time within the last 7 days at a granularity of 1 minute. Which of the following is a limitation of the current configuration?

Accepted Answer

The retention period is too short to support point-in-time restore.. The current configuration shows a backup retention period of only 5 days, which is less than the required 7 days for point-in-time restore. With a retention period shorter than 7 days, you cannot restore to a point within the last 7 days. The backup interval of 12 hours is for full backups and does not affect point-in-time restore granularity, as transaction log backups are taken every 5-10 minutes, enabling 1-minute granularity.

Answer

The backup interval of 12 hours does not allow 1-minute granularity for point-in-time restore.

Answer

The policy must specify a minimum retention of 30 days for point-in-time restore.

Answer

Geo-redundant backup must be disabled for point-in-time restore to work.

Question 14

Drag and drop the steps to migrate an on-premises SQL Server database to Azure SQL Database using the Data Migration Assistant (DMA) in the correct order.

Accepted Answer

Assess database compatibility, then fix compatibility issues, then deploy Azure SQL Database, then migrate schema and data. First assess and fix issues, then deploy the target, then migrate schema, then data.

Answer

Deploy Azure SQL Database, then assess database compatibility, then fix compatibility issues, then migrate schema and data

Answer

Assess database compatibility, then deploy Azure SQL Database, then fix compatibility issues, then migrate schema and data

Answer

Migrate schema and data, then assess database compatibility, then fix compatibility issues, then deploy Azure SQL Database

Question 15

Drag and drop the steps to configure an Azure SQL Database elastic pool in the correct order.

Accepted Answer

Create the elastic pool, then add databases to the pool, then configure per-database limits, then monitor pool performance, then scale the pool as needed.. First create the pool, then add databases, configure per-database limits, monitor, and scale.

Answer

Add databases to an existing pool first, then create the elastic pool, then configure per-database limits, then monitor, then scale.

Answer

Create the elastic pool, then configure per-database limits, then add databases, then monitor, then scale.

Answer

Create the elastic pool, then add databases, then monitor, then configure per-database limits, then scale.