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

Question 1

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 run T-SQL scripts against a target group of databases, so option A is correct: a job can execute a T-SQL script that runs UPDATE STATISTICS across multiple databases in the target group. Option B is correct because jobs can run T-SQL that queries catalog views such as sys.databases and sys.dm_db_resource_stats and inserts the results into a table for centralized metadata collection. Option E is correct because index maintenance, including ALTER INDEX ... REBUILD, is a T-SQL operation that can be executed by a job against every database in an elastic pool used as the job target. Option C is not correct because creating a new Azure SQL Database is a control-plane operation performed through the Azure portal, PowerShell, CLI, or REST API, not through the T-SQL execution model of Elastic Database Jobs. Option D is not correct because changing the service tier objective (SLO) is also a management/control-plane action, not a T-SQL statement that Elastic Database Jobs can run against target databases.

Answer

Create a new Azure SQL Database.

Answer

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

Question 2

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 3

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.

Question 4

You are automating the creation of an Azure SQL database. You need to ensure that the deployment is idempotent using Azure Resource Manager (ARM) templates. Which deployment mode should you use?

Accepted Answer

Incremental. Incremental deployment mode is the default ARM template mode and is idempotent: resources declared in the template are created or updated, and existing resources not in the template are left untouched. This makes repeated deployments safe and consistent, which is exactly what idempotency requires. Complete mode, by contrast, deletes resources in the resource group that are not in the template, so it is not idempotent in the safe sense.

Answer

Complete

Answer

Automatic

Answer

Validate

Question 5

You have an Azure SQL Database that needs to be backed up daily using Azure Automation runbooks. The runbook must trigger an export of the database to a storage account. How should you configure the runbook to authenticate securely to Azure?

Accepted Answer

Enable a system-assigned managed identity for the Automation account. Managed Identity (system-assigned or user-assigned) is the recommended secure authentication method for Azure Automation runbooks, avoiding stored credentials. Option A uses credentials stored in the runbook, which is less secure. Option B uses automation account credentials, which still requires key management. Option D is not a valid type.

Answer

Use a shared access signature (SAS) token stored in the runbook

Answer

Use Automation Account credential assets

Answer

Store the SQL admin credentials as variables in the runbook

Question 6

You need to automate the scaling of an Azure SQL Database in response to CPU usage using Azure Automation. Which Azure service should you use to monitor CPU metrics and trigger the runbook?

Accepted Answer

Azure Monitor metric alerts. Azure Monitor metric alerts are used to monitor CPU metrics and trigger actions such as runbooks in Azure Automation. You can create an alert rule based on a metric like CPU percentage, and configure an action group that triggers a runbook. This is the standard way to automate scaling based on metrics.

Answer

Azure SQL Analytics

Answer

Azure Log Analytics

Answer

Kusto Query Language (KQL)

Question 7

You need to automate the deployment of Azure SQL Database logical servers and databases using Bicep. What is the best practice for storing the administrative password securely?

Accepted Answer

Reference the password from Azure Key Vault using the getSecret function. Azure Key Vault is the recommended secure storage for secrets like administrative passwords in Azure deployments. Using the `getSecret` function in Bicep allows you to reference a secret from Key Vault at deployment time without exposing the password in the Bicep file or deployment logs, aligning with Azure security best practices and the principle of least privilege.

Answer

Use the adminPassword property with a generated password

Answer

Use an environment variable in the deployment script

Answer

Store the password as a plain text parameter in the Bicep file

Question 8

You have an Azure SQL Database that must be automatically restarted every night to clear the procedure cache. You plan to use elastic jobs in Azure SQL Database. What should you create first?

Accepted Answer

An elastic job agent. To use elastic jobs in Azure SQL Database, you must first create an elastic job agent. The agent is the compute resource that runs the jobs. After creating the agent, you create a job database, target groups, credentials, and jobs. The agent is the foundational component.

Answer

A job database

Answer

A target group

Answer

A job credential

Question 9

Which THREE components are required to set up elastic jobs in Azure SQL Database?

Accepted Answer

Target groups. Target groups (C) are required because they define the set of databases (or elastic pools) against which an elastic job will execute. Without target groups, the job agent has no scope of execution. The elastic job agent (D) orchestrates job scheduling and execution, and job credentials (E) are needed to authenticate to the target databases for running T-SQL scripts.

Answer

Azure Automation account

Answer

SQL Server Agent

Question 10

Which TWO methods can be used to automate index maintenance in Azure SQL Database?

Accepted Answer

Azure Automation runbooks executing T-SQL scripts. Azure Automation runbooks can execute T-SQL scripts against Azure SQL Database using the Invoke-SqlCmd cmdlet or similar, enabling scheduled index maintenance tasks such as rebuilding or reorganizing indexes. This method is fully supported in Azure SQL Database, which lacks SQL Server Agent, and allows for flexible, cloud-native automation.

Answer

SQL Server Agent jobs

Answer

Azure Data Factory pipelines

Answer

Automatic tuning

Question 11

Refer to the exhibit. You are deploying an Azure SQL Database using this ARM template. After deployment, you need to automate the scaling of the database to a higher service tier when DTU consumption exceeds 80% for 5 minutes. Which Azure service should you use to trigger the scaling?

Accepted Answer

Azure Monitor metric alert. Azure Monitor metric alerts can be configured to trigger an Azure Automation runbook that scales the Azure SQL Database when DTU consumption exceeds 80% for 5 minutes. This is a common pattern for autoscaling based on metrics. Option A (Azure SQL Analytics) is a monitoring solution that does not trigger actions. Option B (Azure Automation Update Management) is for managing updates on virtual machines, not for scaling databases. Option C (SQL Server Agent) is not available in Azure SQL Database; it is used for on-premises SQL Server instances.

Answer

Azure SQL Analytics

Answer

Azure Automation Update Management

Answer

SQL Server Agent

Question 12

You are designing an automated backup strategy for Azure SQL Managed Instance. The solution must ensure point-in-time restore (PITR) within 2 hours for the last 7 days and long-term retention (LTR) for 5 years. Which configuration should you use?

Accepted Answer

Set PITR retention to 7 days (default) and configure LTR backup policy with yearly backups for 5 years.. Azure SQL Managed Instance supports point-in-time restore (PITR) with a configurable retention period from 1 to 35 days; the default is 7 days, which satisfies the requirement to restore within 2 hours for the last 7 days (the 2-hour target is a recovery point objective, not retention). For long-term retention (LTR) of 5 years, Managed Instance supports LTR backup policies with yearly backups that can retain backups for up to 10 years. Therefore, setting PITR retention to 7 days (default) and configuring an LTR policy with yearly backups for 5 years meets both requirements. Option A is wrong because Azure Backup for SQL Server in Azure VM is for SQL Server on Azure VMs, not for Managed Instance. Option B is wrong because "geo-redundant backup storage for LTR" is a storage redundancy option, not an LTR retention policy; LTR requires explicit backup frequency and retention configuration. Option C is wrong because PITR retention cannot be set to 2 hours (minimum is 1 day) and custom backup jobs using Elastic Database Jobs are unnecessary; Managed Instance automates backups natively.

Answer

Use Azure Backup for SQL Server in Azure VM to back up the managed instance.

Answer

Set PITR retention to 7 days and use geo-redundant backup storage for LTR.

Answer

Set PITR retention to 2 hours and configure a custom backup job using Elastic Database Jobs.

Question 13

Your Azure SQL Database uses a failover group for disaster recovery. You need to automate a planned failover for disaster recovery testing without data loss. What should you use?

Accepted Answer

Create an Azure Automation Runbook that uses the Start-AzSqlDatabaseFailover command with the -AllowDataLoss parameter set to false.. Azure Automation Runbooks can execute the Start-AzSqlDatabaseFailover cmdlet with -AllowDataLoss:$false to perform a planned failover without data loss. Option A (Logic App with failover trigger) is designed for reacting to Azure Monitor alerts, not for scheduled failsafe testing. Option B (configuring automatic failover policy) is for unplanned failovers and would result in data loss if used for testing because automatic failover is asynchronous. Option C (T-SQL ALTER DATABASE FAILOVER scheduled via SQL Server Agent) is invalid because SQL Server Agent is not available in Azure SQL Database (single database or elastic pool); it's only available in Azure SQL Managed Instance. Therefore, D is the correct choice for automated planned failover.

Answer

Set up an Azure Logic App with a failover trigger from Azure Monitor.

Answer

Configure automatic failover policy in the failover group.

Answer

Use a T-SQL script with ALTER DATABASE FAILOVER scheduled via SQL Server Agent.

Question 14

You have an Azure SQL Database that stores sensitive customer data. You need to automate the masking of a specific column for non-admin users. Which feature should you use?

Accepted Answer

Dynamic Data Masking with a masking function configured on the column.. Dynamic Data Masking (DDM) is the correct feature because it masks sensitive data in query results for non-privileged users without altering the actual data at rest. You configure a masking rule (e.g., default(), email(), partial()) on the specific column, and users without UNMASK permission see masked values while admins see the real data. This matches the requirement to 'automate masking of a specific column for non-admin users.'

Answer

Always Encrypted with a column encryption key.

Answer

Row-Level Security to filter rows for non-admin users.

Answer

Azure Policy to enforce masking rules on the database.

Question 15

Which TWO actions can you perform using Elastic Database Jobs in Azure SQL Database?

Accepted Answer

Schedule a job to rebuild indexes on a set of databases.. Options B and E are correct. Elastic Database Jobs in Azure SQL Database allow you to schedule and run T-SQL scripts across multiple databases, including those in an elastic pool. You can use them to schedule index rebuilding (B) and to update statistics via T-SQL (E). Option A is incorrect because firewall rules are managed through Azure SQL Server firewall settings, not Elastic Database Jobs. Option C is incorrect because creating users in Microsoft Entra ID (formerly Azure AD) is done through Active Directory, not Elastic Database Jobs. Option D is incorrect because scaling service tiers is an administrative operation handled by Azure SQL Database scaling commands or automation, not by Elastic Database Jobs.

Answer

Add a firewall rule to allow access from a specific IP address.

Answer

Create users in Microsoft Entra ID for database access.

Answer

Automatically scale the service tier of a database based on CPU usage.