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

Question 1

You are a database administrator for a financial services company that uses Azure SQL Managed Instance. The company needs to automate the execution of a PowerShell script that performs a custom backup verification every week. The script must run on a machine that has the SqlServer PowerShell module installed and must authenticate to the managed instance using Azure AD. You need to configure the automation with the least administrative effort. What should you use?

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

Accepted Answer

Azure Automation runbook that uses a managed identity and imports the SqlServer module from the PowerShell Gallery.. Azure Automation with a managed identity and imported SqlServer module offers a serverless way to run PowerShell scripts on a schedule. The managed identity authenticates to Azure SQL Managed Instance without storing credentials, and the module import provides the necessary cmdlets. This avoids managing virtual machines or packaging modules with functions, making it the lowest-effort solution.

Answer

Azure Automation runbook that uses the Azure Run As account and the Az.Sql module.

Answer

Azure Functions with a timer trigger and a PowerShell script that uses the SqlServer module.

Answer

Azure Automation runbook with a Hybrid Runbook Worker deployed on an Azure virtual machine.

Question 2

You are the database administrator for a healthcare company that uses Azure SQL Database. You need to automate the process of scaling the database between the General Purpose and Business Critical service tiers based on a schedule. The scaling must occur without downtime and must be triggered by a PowerShell script that runs in Azure Automation. Which cmdlet should you use?

Accepted Answer

Set-AzSqlDatabase -RequestedServiceObjectiveName. Set-AzSqlDatabase with the -RequestedServiceObjectiveName parameter is the correct cmdlet to change the service tier and performance level of an Azure SQL Database. It supports switching between General Purpose and Business Critical by specifying the appropriate service objective, and the operation is performed online with minimal downtime.

Answer

Set-AzSqlDatabase -ServiceObjectiveName

Answer

New-AzSqlDatabaseCopy

Answer

Set-AzSqlDatabase -Edition

Question 3

You need to automate the process of scaling an Azure SQL Database to a higher service tier during business hours and back to a lower tier after hours. The solution must minimize cost and administrative effort. What should you use?

Accepted Answer

Azure Automation runbook with a schedule. Azure Automation runbooks allow you to run scheduled PowerShell scripts that can change the service tier of an Azure SQL Database. This is a straightforward and cost-effective way to automate scaling up and down based on a schedule, minimizing both cost and administrative effort.

Answer

Azure SQL Database automatic tuning

Answer

Azure Monitor autoscale

Answer

Azure Logic Apps with a recurrence trigger

Question 4

You are using Azure Automation to run a runbook that executes a long-running T-SQL script against an Azure SQL Database. The script occasionally exceeds the default runbook execution time limit. You need to ensure the runbook can complete without timing out. What should you do?

Accepted Answer

Modify the runbook settings to increase the execution time limit. The execution time limit of an Azure Automation runbook can be increased in the runbook's settings. For cloud runbooks, the maximum is 24 hours; for Hybrid Runbook Workers, it can be unlimited. Increasing the limit allows long-running T-SQL scripts to complete. Other options either do not address the limit or introduce unnecessary complexity.

Answer

Configure the runbook to run on a Hybrid Runbook Worker

Answer

Split the T-SQL script into multiple smaller scripts and run them as separate runbooks

Answer

Use Azure Logic Apps to orchestrate the runbook execution in a loop

Question 5

You are a database administrator for a manufacturing company that uses Azure SQL Managed Instance. You need to automate the process of exporting a database to a BACPAC file and storing it in Azure Blob Storage every week. The solution must be fully automated and require minimal maintenance. What should you use?

Accepted Answer

SQL Agent job with a PowerShell step that uses SqlPackage.exe to export the BACPAC to Azure Blob Storage.. Azure SQL Managed Instance includes SQL Agent, which can schedule and run PowerShell steps. By using a SQL Agent job with a PowerShell step that calls SqlPackage.exe, you can automate the BACPAC export to Azure Blob Storage. This is a native, low-maintenance solution that meets the requirement for full automation.

Answer

Azure Logic App that triggers an Azure Function to run SqlPackage.exe and upload the BACPAC to Blob Storage.

Answer

Azure Automation runbook that uses the Az.Sql module to export the database to a BACPAC.

Answer

Elastic Jobs with a T-SQL step that runs BACKUP DATABASE to a URL.

Question 6

You are a database administrator for a company that uses Azure SQL Database. You need to automate the execution of a T-SQL script that performs index maintenance across multiple databases. The script must run on a schedule, and you want to minimize the need for external components. Which two features should you use together? (Choose two.)

Accepted Answer

Elastic Database Jobs. Elastic Database Jobs provide native scheduling and execution of T-SQL across a target group of Azure SQL databases. By defining a target group that includes the databases needing index maintenance, you can run the script on a schedule with minimal external dependencies. This combination meets the requirement efficiently.

Answer

Azure Functions with a timer trigger

Answer

Azure Automation runbooks

Answer

SQL Server Agent

Question 7

You are a database administrator for a company that uses Azure SQL Database. You need to automate the execution of T-SQL scripts across multiple databases. Which two Azure-native features can be used to schedule and run T-SQL scripts against Azure SQL Database? (Choose two.)

Accepted Answer

Elastic Jobs. Elastic Jobs and Azure Automation runbooks are both Azure-native services that can schedule and execute T-SQL against Azure SQL Database. Elastic Jobs are purpose-built for T-SQL automation across databases, while Azure Automation provides a general-purpose scripting environment. SQL Server Agent is not available for Azure SQL Database, and Data Factory and Logic Apps are not intended for this type of T-SQL scheduling.

Answer

Azure Data Factory

Answer

SQL Server Agent

Answer

Azure Logic Apps

Question 8

You are a database administrator for a media company that uses Azure SQL Database. The company has an elastic job that runs a T-SQL script to aggregate usage statistics every hour. The job has been failing intermittently with a timeout error. You need to ensure the job completes successfully without increasing the schedule interval. What should you do?

Accepted Answer

Optimize the T-SQL script to reduce execution time and ensure it uses efficient indexing.. Optimizing the T-SQL script directly addresses the cause of timeouts by reducing execution time. This might involve adding indexes, rewriting queries, or batching operations. It is the most effective way to ensure the job completes within the service timeout without altering the schedule or incurring unnecessary costs. Elastic jobs have a fixed timeout, so script efficiency is key.

Answer

Increase the DTU or vCore limit of the database to improve performance.

Answer

Configure the job to run on a secondary replica using read-only intent.

Answer

Increase the job's execution timeout setting in the elastic job agent.

Question 9

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 10

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 11

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 12

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 13

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 14

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 15

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.