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

Question 1

Refer to the exhibit. A PowerShell script is used to move an Azure SQL Database into an elastic pool. The script runs without error. Which condition must be true before the script runs?

Accepted Answer

The database must be in the same server as the elastic pool. Moving a database into an elastic pool requires the database to be in the same server as the pool. Option A is wrong because the elastic pool must be in the same server as the database, not a different server. Option C is wrong because the database can be any service tier (Basic, Standard, Premium, etc.) as long as it fits within the pool's resource limits. Option D is wrong because the database size can be any size up to the pool's max size limit, which is not necessarily less than 10 GB.

Answer

The elastic pool must be in a different server

Answer

The database must be in the Basic tier

Answer

The database size must be less than 10 GB

Question 2

You are managing an Azure SQL Database that requires automated index maintenance. You want to use a solution that minimizes administrative overhead and leverages built-in Azure capabilities. Which approach should you recommend?

Accepted Answer

Enable automatic tuning for the database and configure the 'CREATE INDEX' and 'DROP INDEX' recommendations.. Azure SQL Database's automatic tuning feature can automatically create and drop indexes based on workload patterns, minimizing administrative overhead and leveraging built-in Azure capabilities. Option B is incorrect because deploying a third-party solution would introduce additional complexity and cost, not leveraging built-in capabilities. Option C is incorrect because although Azure Automation can schedule scripts, it still requires custom scripting and maintenance, increasing overhead compared to fully automated built-in tuning. Option D is incorrect because scheduling a SQL Agent job requires manual setup and is not a built-in automated feature of Azure SQL Database; SQL Agent is available only in managed instances, not in single databases or elastic pools, and requires custom T-SQL scripting.

Answer

Deploy a third-party maintenance solution and connect it to the Azure SQL Database.

Answer

Create a custom PowerShell script and run it via Azure Automation Runbook on a schedule.

Answer

Schedule a SQL Agent job to rebuild indexes using a T-SQL script.

Question 3

Your company uses Azure SQL Managed Instance. You need to automate the execution of a stored procedure that processes sales data every night at 2 AM. The solution must use native capabilities and minimize latency. What should you do?

Accepted Answer

Create a SQL Agent job with a schedule to execute the stored procedure.. Azure SQL Managed Instance includes SQL Server Agent, which can natively schedule T-SQL jobs with minimal latency. Option A is incorrect because Elastic Database Jobs require a separate job database and are designed for multi-database scalability, adding overhead. Option B is incorrect because Azure Logic Apps introduce external orchestration, potentially increasing latency and cost. Option D is incorrect because Azure Functions require external connectivity and are not as tightly integrated as SQL Agent.

Answer

Create an Elastic Database Job to run the stored procedure on the target database.

Answer

Use Azure Logic Apps with a recurrence trigger to call the stored procedure via a connector.

Answer

Deploy an Azure Function with a timer trigger to invoke the stored procedure via the SQL binding.

Question 4

You need to automate the deployment of database schema changes across multiple Azure SQL Databases in a development environment. Which Azure service is designed for this purpose?

Accepted Answer

Azure DevOps using database projects and release pipelines.. Azure DevOps with database projects and release pipelines provides a CI/CD solution specifically designed for deploying schema changes to Azure SQL Databases. Option B is incorrect because SQL Agent jobs are used for scheduled administrative tasks within a single database, not for multi-database schema deployment automation. Option C is incorrect because Azure Automation Account with PowerShell runbooks is suitable for general automation tasks but lacks native CI/CD capabilities for database schema updates. Option D is incorrect because Azure Data Factory with copy activity focuses on data movement and transformation, not on executing schema changes.

Answer

SQL Agent jobs on each database.

Answer

Azure Automation Account with PowerShell runbooks.

Answer

Azure Data Factory with a copy activity.

Question 5

You are designing an automated backup strategy for Azure SQL Database. The compliance policy requires point-in-time restore (PITR) for the last 35 days, and long-term retention (LTR) for 7 years. What is the minimal number of LTR policies needed?

Accepted Answer

One LTR policy per database.. LTR policies are applied at the database level in Azure SQL Database. Each database requires its own LTR policy to retain backups beyond the PITR retention period. Option A is wrong because policies are per database, not per elastic pool. Option B is wrong because two policies per server (weekly and yearly) are not a minimal requirement; each database can have multiple LTR policies as needed. Option C is wrong because one LTR policy cannot cover all databases on a server; each database has its own retention requirements.

Answer

One LTR policy per elastic pool.

Answer

Two LTR policies per server (weekly and yearly).

Answer

One LTR policy per server.

Question 6

Your team uses GitHub Actions for CI/CD. You need to automatically deploy schema changes to an Azure SQL Database after a pull request merge. The solution must use a service principal with minimal permissions. Which authentication method should the workflow use?

Accepted Answer

Service principal with a client secret stored in GitHub Secrets.. It uses a service principal with a client secret stored in GitHub Secrets, which meets the requirement of using a service principal with minimal permissions. Using a service principal is the appropriate authentication method for an automated CI/CD deployment with Azure SQL Database. Option B is incorrect because a user-assigned managed identity is not a service principal; it is a different type of identity that does not satisfy the requirement for a service principal. Option C is incorrect because storing connection strings in environment variables is insecure and does not use a service principal. Option D is incorrect because SQL authentication does not involve a service principal and is less secure than Azure AD-based methods.

Answer

Azure CLI action using a user-assigned managed identity for the GitHub runner.

Answer

Environment variables containing the connection string.

Answer

SQL authentication using a user name and password stored in GitHub Secrets.

Question 7

You need to run a complex T-SQL script on an Azure SQL Database every hour. The script performs data transformations that must be logged for auditing. Which native Azure service should you use?

Accepted Answer

Elastic Database Jobs with a T-SQL job step.. Elastic Database Jobs (option B) is the correct native Azure service for running complex T-SQL scripts on Azure SQL Database on a schedule with logging. It is designed for scheduled execution of T-SQL scripts across one or more databases and provides job logging and history. Option A is incorrect because SQL Agent is not available in Azure SQL Database single databases (only in SQL Server on VMs or Managed Instances). Option C is incorrect because Azure Data Factory is primarily an ETL service, not optimized for simple scheduled script execution, though it can be used, it is not the native simple solution. Option D is incorrect because Azure Automation Runbooks execute PowerShell, not T-SQL directly; while Invoke-SqlCmd can be used, it is not the native T-SQL scheduling service.

Answer

SQL Agent job on the Azure SQL Database.

Answer

Azure Data Factory with a stored procedure activity.

Answer

Azure Automation Account with a PowerShell runbook that uses Invoke-SqlCmd.

Question 8

You have an Azure SQL Database that needs to be automatically scaled up during peak hours and scaled down during off-peak. The solution must use native Azure capabilities without custom code. What should you use?

Accepted Answer

Create Elastic Database Jobs that run ALTER DATABASE to change the service objective at scheduled times.. Elastic Database Jobs can be used to run ALTER DATABASE statements on a schedule, enabling automated scaling without custom code. Option A is wrong because it requires custom code (PowerShell) and is not a native Azure SQL capability. Option B is wrong because autoscale for elastic pools adjusts per-database DTUs, not the overall database service objective, and does not support scheduled scaling. Option C is wrong because autoscale settings are not available for Azure SQL Database at the server level.

Answer

Use Azure Functions with timer trigger and PowerShell to change the pricing tier.

Answer

Configure autoscale for the elastic pool that contains the database.

Answer

Configure autoscale settings on the Azure SQL Database server.

Question 9

Your organization uses Azure SQL Database and needs to automate email notifications when a database reaches 80% storage usage. Which native Azure feature can you use?

Accepted Answer

Create an Azure Monitor alert rule on the 'storage_percent' metric with an email action group.. Azure Alert Rules can monitor metrics like storage percent and trigger email actions. Option B is wrong because SQL Agent is not available in Azure SQL Database. Option C is wrong because Elastic Jobs execute T-SQL, not send email directly. Option D is wrong because Database Mail is not available in Azure SQL Database.

Answer

Create a SQL Agent alert that fires when the storage is above 80% and sends an email.

Answer

Configure Database Mail to send alerts automatically.

Answer

Create an Elastic Database Job that checks storage and sends email via sp_send_dbmail.

Question 10

Which TWO of the following are valid methods to automate backups for Azure SQL Managed Instance? (Select exactly two.)

Accepted Answer

Schedule T-SQL BACKUP DATABASE TO URL statements via SQL Agent jobs.. Options B and D are correct. Azure SQL Managed Instance supports automated backups through the service's built-in point-in-time restore (PITR) and long-term retention (LTR) policies (option D). Additionally, you can schedule manual copy-only backups to Azure Blob Storage using T-SQL BACKUP DATABASE TO URL statements via SQL Agent jobs (option B). Option A is incorrect because Azure Backup does not natively support Azure SQL Managed Instance. Option C is incorrect because Azure Site Recovery is a disaster recovery solution, not a backup service. Option E is incorrect because Azure VM backup is designed for IaaS virtual machines, not for PaaS managed instances.

Answer

Use Azure Backup to schedule full backups.

Answer

Use Azure Site Recovery to replicate the instance.

Answer

Use Azure VM backup by installing the backup extension.

Question 11

Which THREE of the following are required to automate schema deployments to Azure SQL Database using Azure DevOps? (Select exactly three.)

Accepted Answer

A release pipeline with a 'Azure SQL Database deployment' task.. Options A, B, and C are correct. A release pipeline with the 'Azure SQL Database deployment' task automates the deployment. A SQL database project (.sqlproj) defines the schema to deploy. A service connection to Azure with appropriate permissions is needed for authentication and authorization. Option D is incorrect because while a schema compare tool can be used to generate scripts, it is not a strict requirement; the deployment task can handle the deployment directly from the project. Option E is incorrect because a self-hosted build agent is not required; Microsoft-hosted agents can be used.

Answer

A schema compare tool to generate deployment scripts.

Answer

A self-hosted build agent with SQL tools installed.

Question 12

Which TWO of the following are native options to automate index maintenance on Azure SQL Database? (Select exactly two.)

Accepted Answer

Create Elastic Database Jobs that run index maintenance T-SQL scripts.. Options A and C are correct. Elastic Database Jobs (Option A) allow running custom T-SQL scripts for index maintenance across databases. Automatic tuning (Option C) can automatically create and drop indexes based on workload patterns, which is a form of index maintenance. Option B is incorrect because Azure Automation PowerShell runbooks are not a native option for Azure SQL Database index maintenance; they would require additional setup and do not directly execute T-SQL. Option D is incorrect because SQL Agent jobs are not available in Azure SQL Database (they are available in SQL Server on-premises or on Azure VMs). Option E is incorrect because Azure Data Factory is primarily for data movement and orchestration, not for direct index maintenance.

Answer

Use Azure Automation PowerShell runbooks to invoke index rebuilds.

Answer

Schedule SQL Agent jobs with ALTER INDEX statements.

Answer

Use Azure Data Factory to copy data and rebuild indexes.

Question 13

Refer to the exhibit. You are reviewing an Elastic Database Job definition for Azure SQL Database. The job is scheduled to run once on January 15, 2026. Which statement about the job's target is correct?

Accepted Answer

The job will target all databases in the server except 'db1'.. The target group uses membershipType 'Exclude' with a members list containing only 'db1'. This means that the job will target all databases in the logical server except 'db1'. Option A is incorrect because the membership type is 'Exclude', not 'Include', so it does not target only 'db1'. Option C is incorrect because the target group is at the database level within a server, not the entire server itself. Option D is incorrect because the target group excludes 'db1', so it targets all databases on the server, but not the server as a whole.

Answer

The job will target only the database named 'db1'.

Answer

The job will target the entire logical server.

Answer

The job will target the server and all its databases.

Question 14

Refer to the exhibit. You are reviewing an Azure Automation runbook configuration that performs backups. What is the most likely issue with this configuration?

Accepted Answer

The retention period (7 days) equals the backup interval (7 days), which could cause data loss if a backup fails.. The retention period of 7 days matches the backup interval of 7 days, meaning each backup is retained exactly until the next backup. If a backup fails, there is no overlap and the previous backup may have already been deleted, leading to data loss. Option A is wrong because the runbook is enabled (isEnabled: true). Option B is wrong because Standard_LRS is suitable for backups. Option C is wrong because the description is not the issue.

Answer

The runbook is disabled (isEnabled: false).

Answer

The storage account type Standard_LRS is not suitable for backups.

Answer

The backup description is missing required information.

Question 15

Refer to the exhibit. An administrator runs this Azure CLI command. What is the immediate effect?

Accepted Answer

The OrdersDB database's service objective is updated to S2.. The 'az sql db update' command updates an existing database's service objective to S2. This immediate effect changes the performance level of the OrdersDB database. Option A is incorrect because the command targets the database, not the server. Option C is incorrect because the command does not specify scaling direction; it simply sets the service objective, which could be an upgrade or downgrade depending on the current setting. Option D is incorrect because the update command does not create a new database; it modifies the existing one.

Answer

The server's performance tier is changed to S2.

Answer

The database is scaled down from a higher tier to S2.

Answer

A new database named OrdersDB is created with S2 tier.