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

Question 1

You manage a fleet of Azure SQL Databases using elastic jobs. You need to automate a task that runs a T-SQL script against every database in a target group every night. The script must be stored centrally and updated without modifying the job agent. What should you use?

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

Accepted Answer

Store the T-SQL script in Azure Storage and reference it with a SAS token in the job step.. Elastic jobs allow job steps to reference a T-SQL script stored in Azure Storage via a SAS token. This centralizes the script, and updating the file in storage automatically applies to future job executions without changing the job definition. This is the intended method for reusable, centrally managed scripts.

Answer

Embed the T-SQL script directly in the job step definition and update the job whenever the script changes.

Answer

Use Azure Automation runbooks with the SqlServer module to iterate over databases and execute the script.

Answer

Create a stored procedure in the job agent database and call it from the job step.

Question 2

You manage an Azure SQL Database that experiences nightly batch processing. You need to automate the process of temporarily increasing the database's service objective to a higher tier before the batch starts and then scaling back down after it completes, all without manual intervention. The solution must minimize cost and administrative effort. What should you use?

Accepted Answer

Azure Automation runbooks with a schedule. The requirement is to automate scaling a database up and down on a schedule with minimal cost and effort. Azure Automation runbooks provide a scriptable, serverless way to execute Azure management operations on a schedule. They can start and stop scaling without manual intervention. Other options either do not support service objective changes, are designed for different purposes, or require more complex orchestration, making runbooks the most appropriate solution.

Answer

Azure Logic Apps with a recurrence trigger

Answer

SQL Database automatic tuning with the 'Create index' option

Answer

Elastic Database jobs with a step that runs ALTER DATABASE

Question 3

You are a database administrator for a healthcare company that uses Azure SQL Database. You need to automate the process of scaling the database up during business hours and down during off-hours to reduce costs. The scaling must occur on a schedule and must not cause downtime. Which Azure feature should you use?

Accepted Answer

Azure Automation runbooks with PowerShell runbooks that call the Set-AzSqlDatabase cmdlet.. Azure Automation runbooks with PowerShell are the correct choice because they can execute scheduled management operations against Azure SQL Database. The Set-AzSqlDatabase cmdlet allows changing the service objective, which scales the database up or down without downtime. This approach is flexible and can be scheduled to match business hours, achieving cost savings. Other options either do not support scaling or are not designed for scheduled management tasks.

Answer

Azure Logic Apps with a recurrence trigger that calls the Azure SQL Database REST API.

Answer

Azure Monitor autoscale rules that scale the database based on CPU usage.

Answer

Elastic Jobs that execute a T-SQL script to alter the database service objective.

Question 4

You manage an Azure SQL Database. You need to automatically create a copy of the database every day at 1:00 AM and retain the copy for 30 days. The solution must minimize cost and administrative effort. What should you use?

Accepted Answer

Azure SQL Database automatic backups with a long-term retention policy. Azure SQL Database automatic backups with long-term retention (LTR) allow you to retain backups for up to 10 years. You can configure a policy to keep daily backups for 30 days. This is a built-in, fully managed feature that requires no custom automation and minimizes both cost and administrative effort.

Answer

Azure Data Factory pipeline with a copy activity

Answer

Azure Automation runbook that runs a T-SQL CREATE DATABASE AS COPY OF statement

Answer

Elastic Database Jobs that run a T-SQL script to create a copy

Question 5

You manage an Azure SQL Managed Instance that hosts a mission-critical database. You need to automate the process of taking a copy-only backup of the database to a URL in Azure Blob Storage every 6 hours. The backup must not affect the differential backup chain. Which method should you use?

Accepted Answer

Create a SQL Server Agent job that executes BACKUP DATABASE ... TO URL WITH COPY_ONLY. SQL Server Agent on Azure SQL Managed Instance supports native BACKUP DATABASE TO URL with the COPY_ONLY option. This creates a backup that does not reset the differential base, so the existing differential backup chain remains intact. Scheduling it every 6 hours via an Agent job meets the frequency and non-disruptive requirements.

Answer

Use Elastic Database Jobs to run BACKUP DATABASE ... TO URL WITH COPY_ONLY against the Managed Instance

Answer

Configure automated backups with a custom retention policy of 6 hours

Answer

Create an Azure Automation runbook that runs a PowerShell script to export the database using Export-AzSqlInstanceDatabase

Question 6

You are a database administrator for a manufacturing company that uses Azure SQL Database. The company wants to automate the deployment of database schema changes across multiple databases in a development environment. You need to implement a solution that uses Azure DevOps pipelines. Which two actions should you perform? (Choose two.)

Accepted Answer

Use the Azure CLI task to run sqlcmd with a T-SQL script.. Azure DevOps pipelines can deploy schema changes to Azure SQL Database using either the SqlAzureDacpacDeployment task with a DACPAC or the Azure CLI task with sqlcmd. Both are supported and common methods. The other options either rely on features not available in Azure SQL Database or use less integrated approaches.

Answer

Create a runbook in Azure Automation to deploy the schema changes.

Answer

Use the Azure PowerShell task to invoke the Invoke-Sqlcmd cmdlet.

Answer

Configure a SQL Server Agent job to run the deployment script.

Question 7

You manage an Azure SQL Database that must be scaled to a higher service tier automatically when CPU usage exceeds 80% for 10 minutes. You want to use an Azure-native solution that requires minimal code. What should you configure?

Accepted Answer

Azure SQL Database serverless compute tier with auto-scaling enabled by default.. The serverless compute tier is designed for automatic scaling based on workload, including CPU usage. It adjusts compute within a specified range and can pause when idle, providing a native, code-free way to handle spikes. Other options either do not scale tiers, require custom automation, or are not applicable to a single database's service tier.

Answer

Azure SQL Database automatic tuning with the 'Create index' option enabled.

Answer

Azure Monitor autoscale with a metric alert rule that triggers an Azure Automation runbook to change the service tier.

Answer

Elastic pool with a maximum eDTU or vCore limit and automatic scaling enabled.

Question 8

You are designing an automation solution for an Azure SQL Database. You need to schedule a T-SQL script to run every night and also send an email notification if the script fails. Which two actions should you perform? (Choose two.)

Accepted Answer

Configure an alert rule in Azure Monitor that triggers when the job fails, and set an action group to send an email.. Elastic jobs are the native way to schedule T-SQL against Azure SQL Database. To get notified on failure, you can use Azure Monitor alerts on the job execution status, with an action group that sends email. Together, these two actions provide scheduled execution and failure notification without external orchestration.

Answer

Enable Advanced Threat Protection on the Azure SQL Database to detect script failures.

Answer

Create a SQL Server Agent job on the Azure SQL Database logical server.

Answer

Create an Azure Automation runbook that runs the T-SQL script and sends an email on failure.

Question 9

You are a database administrator for a financial services company that uses Azure SQL Database. You need to automate the detection of blocking chains that last longer than 30 seconds. When such a chain is detected, you want to automatically capture the blocking session IDs and the SQL text of the blocking statement into a table for later analysis. What should you use?

Accepted Answer

Elastic Database Jobs with a T-SQL script that queries sys.dm_exec_requests and logs blocking information. Elastic Database Jobs are designed to run T-SQL on a schedule against Azure SQL Database. A job step can query sys.dm_exec_requests to find blocking chains older than 30 seconds and insert the blocking session IDs and SQL text into a table. This is a native, low-maintenance solution that meets the requirement without external orchestration.

Answer

Azure Automation runbook that runs a PowerShell script to query the database and send an email

Answer

SQL Server Agent job on the Azure SQL Database logical server

Answer

Azure Monitor alerts with a custom log search query on the Azure SQL Database diagnostic logs

Question 10

You are a database administrator for a media company using Azure SQL Database. A nightly Azure Automation runbook performs index maintenance. The runbook currently runs as the Automation account's managed identity, but it intermittently fails with a login error when connecting to the database. You need to resolve the failure with the least administrative effort. What should you do?

Accepted Answer

Create a contained database user for the managed identity in the target database and grant the required permissions.. An intermittent login error when a managed identity connects usually means the contained database user is missing or its SID no longer matches the identity. Recreating the user with CREATE USER FROM EXTERNAL PROVIDER and granting the necessary permissions restores access with the least effort. Firewall changes, identity regeneration, and master-database role grants do not fix an authentication failure in the target database.

Answer

Enable public network access on the logical server and add a firewall rule for the Automation account.

Answer

Regenerate the Automation account's managed identity and update the runbook connection string.

Answer

Add the managed identity to the db_owner role in the master database.

Question 11

You are configuring an Elastic Database Job in Azure SQL Database to run a T-SQL script against all databases in an elastic pool. You need to ensure that the job can authenticate to the target databases and that the script runs on the correct databases. Which two actions should you perform? (Choose two.)

Accepted Answer

Define a target group that includes all databases in the elastic pool. Elastic Database Jobs authenticate to target databases using a credential stored in the job database. You must create this credential with the username and password of a login that has permissions on the targets. You also need to define a target group that includes the databases to run against, such as all databases in an elastic pool. These two actions ensure the job can connect and execute on the correct databases.

Answer

Create a job credential that uses a database-scoped credential in the job database

Answer

Define a target group that includes the master database of the server

Answer

Grant the job credential the db_owner role in the master database

Question 12

You are a database administrator for a retail company that uses Azure SQL Database. The company wants to automate the process of copying a production database to a development environment every week. You need to implement a solution that uses Azure Data Factory. Which activity should you use?

Accepted Answer

Stored Procedure activity. To copy a database in Azure SQL Database, you can use the CREATE DATABASE ... AS COPY OF T-SQL statement. Azure Data Factory's Stored Procedure activity can execute a stored procedure that runs this statement. This provides a native, code-light way to automate the weekly copy without additional services.

Answer

Copy Data activity

Answer

Azure Function activity

Answer

Web activity

Question 13

You are a database administrator for a financial services company that uses Azure SQL Managed Instance. You need to automate the process of copying a production database to a development instance every Sunday at 1:00 AM. The copy must be transactionally consistent and use minimal administrative effort. What should you use?

Accepted Answer

Create a SQL Server Agent job on the managed instance that runs a T-SQL script using BACKUP DATABASE and RESTORE DATABASE with a shared access signature (SAS) credential.. SQL Server Agent is available on Azure SQL Managed Instance and can schedule T-SQL jobs. Using BACKUP DATABASE to URL with a SAS credential and RESTORE DATABASE from URL provides a transactionally consistent copy with minimal external tooling. This native approach leverages existing features and meets the scheduling requirement directly.

Answer

Deploy an Azure Automation runbook that uses PowerShell to invoke the Copy-AzSqlInstanceDatabase cmdlet on a schedule.

Answer

Use Azure Data Factory with a copy activity that uses the SQL Server connector to copy the database.

Answer

Configure an Elastic Job agent to run a T-SQL script that performs a cross-instance database copy.

Question 14

You are a database administrator for a healthcare company that uses Azure SQL Database. You need to ensure that a critical stored procedure runs every hour to update patient statistics. The solution should be simple to configure and maintain, and should not require external compute resources. What should you use?

Accepted Answer

Elastic Jobs with a recurring schedule. Elastic Jobs provide a native, serverless way to schedule and execute T-SQL tasks within Azure SQL Database. They require no external compute and are simple to set up and maintain. By creating a job with a recurring schedule, you can ensure the stored procedure runs hourly. Other options either are not supported on Azure SQL Database (SQL Agent) or require external compute and additional management (Azure Automation, Azure Functions).

Answer

SQL Agent job on the Azure SQL Database

Answer

Azure Functions with a timer trigger that executes the stored procedure

Answer

Azure Automation runbook with a hourly schedule

Question 15

You are a database administrator for a media company that uses Azure SQL Database. You need to automate the deployment of schema changes to multiple databases in an elastic pool. The changes must be applied in a specific order, and you need to track which databases have been updated. What should you use?

Accepted Answer

Azure Pipelines with a release pipeline that runs a series of SQL scripts against each database.. Azure Pipelines is the best choice because it offers orchestration, ordering, and tracking. You can define a release pipeline with stages that deploy to each database in sequence, and use logging or a tracking table to record progress. It integrates with source control and provides audit trails. The other options lack the required orchestration and tracking capabilities or would require significant custom development.

Answer

SQL Server Data Tools (SSDT) with a DACPAC deployed via SqlPackage.exe to each database.

Answer

Azure Automation runbook that iterates through databases and applies scripts using Invoke-Sqlcmd.

Answer

Elastic Jobs that run a single T-SQL script against all databases in the pool.