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

Question 1

Match each Azure SQL Database high availability feature to its description.

Accepted Answer

Zone-redundant availability: Protects against datacenter failures by replicating databases across three availability zones within a region.. These features provide different levels of availability and disaster recovery for Azure SQL Database.

Answer

Zone-redundant availability: Automatically fails over databases across different Azure regions.

Answer

Active geo-replication: Creates four replicas in the same datacenter to provide high availability.

Question 2

Match each Azure SQL Database command to its function.

Accepted Answer

CREATE DATABASE: Creates a new database. In Azure SQL Database, CREATE DATABASE initializes a new database, ALTER DATABASE changes its configuration, DROP DATABASE removes it, and BACKUP DATABASE creates a backup (though backups are often automatic). Common confusions include swapping CREATE with DROP and ALTER with BACKUP due to similar administrative contexts.

Answer

CREATE DATABASE: Deletes a database

Answer

ALTER DATABASE: Creates a backup

Question 3

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' mode processes the template as an incremental update to the resource group, making it idempotent. Only resources specified in the template are added or modified; existing resources not in the template are left unchanged. 'Complete' mode deletes resources not in the template, which is not idempotent. 'Validate' mode only validates without deploying, and 'Automatic' is not a valid ARM deployment mode.

Answer

Complete

Answer

Automatic

Answer

Validate

Question 4

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 5

Your company uses Azure SQL Managed Instance and needs to automate patching and maintenance. The compliance team requires that all maintenance windows be predefined and that no maintenance occurs outside these windows. What should you configure?

Accepted Answer

Assign a maintenance configuration to the SQL Managed Instance. Azure Maintenance Configurations allow you to schedule maintenance windows for Azure SQL Managed Instance, ensuring compliance. Option A is for Azure VMs, not SQL MI. Option B is for patching at the OS level, not applicable. Option C is for Azure SQL Database, not Managed Instance.

Answer

Configure a maintenance configuration for the virtual machine scale set

Answer

Set a maintenance window in the Azure SQL Database server settings

Answer

Use Azure Update Management to schedule patching

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 can be configured to trigger an Automation runbook when a metric (like CPU) crosses a threshold. Option A (Azure SQL Analytics) is for analyzing logs, not triggering actions. Option B (Azure Log Analytics) is for log analytics, not for direct metric alerts triggering runbooks. Option C (Kusto Query Language) is a query language, not a service.

Answer

Azure SQL Analytics

Answer

Azure Log Analytics

Answer

Kusto Query Language (KQL)

Question 7

You are designing an automated data movement solution using Azure Data Factory. Data must be copied from an on-premises SQL Server to Azure SQL Database. The solution must support incremental loads and handle schema changes. Which approach should you use?

Accepted Answer

Use Self-Hosted Integration Runtime with a Copy activity configured with incremental loading. A Self-Hosted Integration Runtime is required to access an on-premises SQL Server, and the Copy activity can be configured for incremental loading using a watermark column. This supports the requirement for incremental loads and schema changes (by using auto-create table). Option B is incorrect because Azure-SSIS IR is for SSIS package execution, not for data copy with incremental loading. Option C is incorrect because Azure IR cannot access on-premises networks without a self-hosted IR. Option D is incorrect because a Stored Procedure activity is used for executing stored procedures, not for copying data with incremental loading.

Answer

Use Azure-SSIS Integration Runtime with a Data Flow activity

Answer

Use Azure Integration Runtime with a Copy activity

Answer

Use a Stored Procedure activity in Data Factory

Question 8

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 9

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. An elastic job agent is the top-level resource that orchestrates jobs. Before creating any jobs, you must create the elastic job agent. The job database (A) stores job definitions but is created as part of agent setup or after. Target groups (C) and job credentials (D) are created after the agent exists.

Answer

A job database

Answer

A target group

Answer

A job credential

Question 10

Your company uses GitHub Actions to deploy changes to Azure SQL Database. You need to ensure that rollbacks can be performed automatically if a deployment fails. Which strategy should you implement?

Accepted Answer

Create a database copy or snapshot before each deployment. The correct strategy is to create a database copy or snapshot before each deployment. This allows you to revert the database to its pre-deployment state if the deployment fails, enabling automated rollback. Option A (Azure App Service deployment slots) is designed for app deployment, not databases. Option B (approval gates) controls when deployments proceed but does not automate rollback. Option D (point-in-time restore) can restore to a point in time but is not a deployment-specific rollback method and may not cover the exact previous state.

Answer

Use Azure App Service deployment slots for the database

Answer

Use GitHub Actions deployment environments with approval gates

Answer

Use Azure SQL Managed Instance point-in-time restore

Question 11

You need to automate the creation of an Azure SQL Database using Azure CLI in a CI/CD pipeline. The database name must be unique and include the build ID. How should you specify the database name in the Azure CLI command?

Accepted Answer

Use an environment variable $(Build.BuildId) set by the pipeline. In Azure DevOps pipelines, $(Build.BuildId) is a predefined variable that provides a unique build ID. This can be used in Azure CLI commands to create a unique database name, e.g., 'mydb-$(Build.BuildId)'. Option A ($RANDOM) is Bash-specific and not reliable across pipeline runs. Option B (uuid) is not a valid Azure CLI function. Option C (hardcoded name) fails on pipeline rebuilds due to name conflicts.

Answer

Use the $RANDOM variable in Bash

Answer

Use the $(uuid) function in Azure CLI

Answer

Use a hardcoded name like 'mydb'

Question 12

Which TWO actions should you perform to enable automatic tuning for an Azure SQL Database?

Accepted Answer

Enable automatic tuning at the Azure SQL Database server level. Automatic tuning in Azure SQL Database requires enabling it at both the server level (to set the inheritance policy) and the database level (to apply tuning options like FORCE_LAST_GOOD_PLAN or CREATE_INDEX). Option B is correct because enabling at the server level allows you to define a default tuning policy that can be inherited by all databases, which is a prerequisite for per-database tuning. Option C is correct because you must also explicitly enable automatic tuning at the database level to override or confirm the inherited settings for that specific database.

Answer

Set the database compatibility level to 150

Answer

Enable Query Store

Answer

Configure performance recommendations

Question 13

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 14

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 15

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