DP-300 Practice Test 16 — 15 Questions

Question 1

Drag and drop the steps to configure a failover group for an Azure SQL Database in the correct order.

Accepted Answer

Step 1: Create a secondary Azure SQL Database server; Step 2: Create a failover group; Step 3: Add databases to the failover group; Step 4: Configure the failover policy; Step 5: Test failover.. Failover groups require a secondary server, then creating the group, adding databases, configuring policy, and testing.

Answer

Step 2: Create a failover group; Step 1: Create a secondary Azure SQL Database server; Step 3: Add databases to the failover group; Step 4: Configure the failover policy; Step 5: Test failover.

Answer

Step 1: Create a secondary Azure SQL Database server; Step 2: Create a failover group; Step 4: Configure the failover policy; Step 3: Add databases to the failover group; Step 5: Test failover.

Answer

Step 1: Create a secondary Azure SQL Database server; Step 3: Add databases to the failover group; Step 2: Create a failover group; Step 4: Configure the failover policy; Step 5: Test failover.

Question 2

You are managing an Azure SQL Database that runs a critical line-of-business application. Users report that a specific query is running slower than usual. You identify that the query is performing a clustered index scan on a large table with over 10 million rows. The table has a clustered index on an identity column and a nonclustered index on a frequently filtered column. You need to minimize the query execution time without adding additional indexes. What should you do?

Accepted Answer

Update all statistics on the table.. The query is performing a clustered index scan, which means SQL Server is reading all rows in the table. Outdated statistics can cause the optimizer to choose a scan instead of a more efficient seek. Updating all statistics on the table (option B) provides the optimizer with fresh distribution information, potentially allowing it to choose a better execution plan that avoids the scan, thereby reducing query execution time without adding indexes.

Answer

Increase the service tier of the Azure SQL Database to provide more resources.

Answer

Rebuild the clustered index to reduce fragmentation.

Answer

Update the statistics on the nonclustered index only.

Question 3

You need to automate the deployment of schema changes to an Azure SQL Database using Azure DevOps. Which THREE components are required? (Choose three.)

Accepted Answer

Build pipeline. Option A (Build pipeline) is correct because a build pipeline is needed to compile the database project (e.g., a SQL Server Database Project / DACPAC) and produce the deployment artifact that will be published to the Azure SQL Database. Option B (Release pipeline) is correct because the release pipeline consumes that artifact and executes the deployment task (such as Azure SQL Database deployment or SqlPackage) against the target Azure SQL Database. Option E (Variable group) is correct because it stores reusable, environment-specific values (server name, database name, credentials, connection strings) that the pipelines reference, keeping secrets and configuration out of the pipeline definition. Option C (Elastic job agent) is not required because it is used to run T-SQL jobs across a set of databases in a pool, not to deploy schema changes through Azure DevOps. Option D (Azure Automation runbook) is not required because runbooks automate operational tasks in Azure, but schema deployment in this scenario is handled by the Azure DevOps build and release pipelines.

Answer

Elastic job agent

Answer

Azure Automation runbook

Question 4

The query returns a list of query hashes with high average duration. You need to identify which queries are most likely causing CPU pressure. What additional metric should you include?

Accepted Answer

Include avg_cpu_time to measure CPU usage.. To identify queries causing CPU pressure, you need to measure CPU usage per query. The `avg_cpu_time` metric directly indicates how much CPU time each query consumes on average, making it the most relevant additional metric.

Answer

Include wait_stats to see blocking.

Answer

Include count_executions to see frequency.

Answer

Include avg_logical_reads to see I/O consumption.

Question 5

Which THREE components are required to configure an auto-failover group for Azure SQL Database? (Choose three.)

Accepted Answer

A failover group name. A failover group is a named resource, so option B (a failover group name) is required to create and reference it. Option C (a secondary logical server in a different region) is required because the failover group replicates databases to a partner server, and placing it in a different Azure region provides the geo-redundancy that failover groups are designed for. Option D (the primary logical server) is required because the failover group is defined between a primary and secondary server, with the primary hosting the read-write databases. Option A is not required because failover groups automatically manage the geo-replication relationship; you do not manually configure active geo-replication as a prerequisite. Option E is not applicable because an availability group listener is an Always On availability group concept for SQL Server, not an Azure SQL Database auto-failover group component.

Answer

Active geo-replication

Answer

An availability group listener

Question 6

You need to automate the execution of a T-SQL script against all user databases in an Azure SQL Database elastic pool. The script should run on a schedule and results should be logged to a table. Which feature should you use?

Accepted Answer

Elastic Database Jobs. Elastic Database Jobs are designed for executing T-SQL across many databases in a pool. SQL Agent is not available in Azure SQL Database. Azure Automation runbooks can run T-SQL but require more setup. Azure Data Factory is for data movement, not T-SQL execution.

Answer

SQL Agent jobs

Answer

Azure Data Factory

Answer

Azure Automation with PowerShell runbooks

Question 7

You are designing a security strategy for Azure SQL Managed Instance. The compliance team requires that all database backups be encrypted at rest using a customer-managed key. Which feature should you enable?

Accepted Answer

Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault.. Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault is the correct feature because it encrypts the database at rest, including all backup files, using a key that the customer controls. This satisfies the compliance requirement for customer-managed key encryption of backups, as TDE automatically encrypts backups when the database is encrypted.

Answer

Always Encrypted with a column master key in Azure Key Vault.

Answer

Transparent Data Encryption (TDE) with a service-managed key.

Answer

Row-Level Security (RLS) with a custom authorization function.

Question 8

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 9

Your company uses Azure SQL Database and needs to restrict access to a specific column containing credit card numbers. Only users with the 'CreditCardViewer' role should see the full number; others should see only the last four digits. Which feature should you implement?

Accepted Answer

Dynamic Data Masking. Dynamic Data Masking (DDM) is the correct choice because it allows you to obfuscate sensitive data in query results without changing the underlying database. You can define a mask on the credit card column that shows only the last four digits to users without the 'CreditCardViewer' role, while users with that role can be granted the UNMASK permission to see the full value.

Answer

Always Encrypted

Answer

Row-Level Security

Answer

Column-level security with GRANT

Question 10

Refer to the exhibit. You are deploying an Azure SQL Database audit policy using an ARM template. What is the MOST significant security concern with the configuration shown?

Accepted Answer

The storage account access key is exposed in the template. The ARM template exposes the storage account access key in plaintext as a parameter value. This is a critical security concern because anyone with access to the template (e.g., in source control or deployment logs) can retrieve the key and gain unrestricted access to the storage account, including reading, modifying, or deleting audit logs. Azure SQL Database audit policies should use managed identities or Microsoft Entra ID authentication to avoid embedding secrets.

Answer

Enabling Azure Monitor target could allow unauthorized access to logs

Answer

Retention of 90 days may be too short for compliance

Answer

Including successful authentication events may expose sensitive login activity

Question 11

Which TWO disaster recovery options are available for Azure SQL Database? (Choose two.)

Accepted Answer

Auto-failover groups.. Active geo-replication and auto-failover groups are the two primary disaster recovery options for Azure SQL Database. Active geo-replication provides asynchronous replication of a database to a secondary region, while auto-failover groups extend this by allowing failover of multiple databases and providing read-only endpoints. Options A (database mirroring) and D (log shipping) are not supported for Azure SQL Database. Option C (Always On availability groups) is a feature for SQL Server on Azure VMs, not for Azure SQL Database.

Answer

Database mirroring.

Answer

Always On availability groups.

Answer

Log shipping.

Question 12

You are monitoring an Azure SQL Database that is experiencing high DTU consumption. You need to identify the queries that are causing high resource usage. Which two data sources can you use? (Choose two.)

Accepted Answer

Query Store. Query Store (Option B) captures a history of query execution plans and runtime statistics, allowing you to identify queries with high CPU, I/O, or duration. sys.dm_exec_query_stats (Option C) returns aggregate performance statistics for cached query plans, including total CPU time and logical reads, which directly points to resource-intensive queries. Both are valid sources for diagnosing high DTU consumption in Azure SQL Database.

Answer

sys.dm_os_wait_stats

Answer

sys.dm_db_index_usage_stats

Answer

sys.dm_io_virtual_file_stats

Question 13

You are reviewing the long-term retention (LTR) policy for an Azure SQL Database. The exhibit shows the current policy. You need to ensure that backups are retained for at least 10 years for compliance. What should you do?

Accepted Answer

Increase the yearly retention to P10Y.. The long-term retention (LTR) policy in Azure SQL Database uses ISO 8601 duration formats for each retention tier: weekly (P#W), monthly (P#M), and yearly (P#Y). To retain backups for at least 10 years, the yearly retention period must be set to P10Y, which represents 10 years. Only the yearly retention option supports multi-year durations, making it the correct choice for a 10-year compliance requirement.

Answer

Change the weekOfYear to 10.

Answer

Increase the monthly retention to P120M.

Answer

Increase the weekly retention to P10W.

Question 14

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.. SQL Agent is a native component of Azure SQL Managed Instance and supports scheduled job execution with T-SQL steps, making it the correct choice for running a stored procedure nightly at 2 AM. It minimizes latency because the job runs directly on the instance without external orchestration or network hops.

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 15

Which THREE components are required to run Elastic Database Jobs for Azure SQL Database? (Choose three.)

Accepted Answer

A job database. Elastic Database Jobs require a job database (A), which stores job definitions, execution history, and job step metadata, and it must reside in the same Azure SQL Database server as the job agent. They also require a job agent (C), the Azure resource that executes T-SQL against the target databases and is created in the job database's server. Target databases (B) are required because they are the members of the job group against which the job steps run. SQL Agent (D) is not used, since Elastic Database Jobs are an Azure SQL Database-native feature rather than an on-premises SQL Server Agent component. Azure Automation account (E) is also unnecessary, as scheduling and execution are handled by the job agent and job database, not by Azure Automation.

Answer

SQL Agent

Answer

Azure Automation account