DP-300 Practice Test 1 — 10 Questions

Question 1

A company uses Azure SQL Database and wants to automate the process of refreshing a development database from production backups weekly. Which Azure service should be used to orchestrate this process including restore and post-restore scripts?

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

Accepted Answer

Azure Automation with PowerShell runbooks. Azure Automation with PowerShell runbooks is designed for orchestrating scheduled administrative tasks across Azure resources, including database restore operations and post-restore T-SQL scripts. It supports credentials, schedules, and integration with Azure SQL via the SqlServer module, making it the right tool for weekly refresh automation.

Answer

Elastic Database Jobs

Answer

Azure Logic Apps

Answer

Azure Data Factory

Question 2

You need to audit all successful and failed login attempts to an Azure SQL Database. Which feature should you enable?

Accepted Answer

Azure SQL Auditing. Azure SQL Auditing is the correct feature because it tracks database events, including both successful and failed login attempts, and writes them to an audit log in your Azure Storage account, Log Analytics workspace, or Event Hubs. This allows you to monitor and review authentication activity for compliance and security analysis. Other features like Advanced Threat Protection, TDE, and Vulnerability Assessment do not capture login event logs.

Answer

Advanced Threat Protection

Answer

Transparent Data Encryption (TDE)

Answer

SQL Vulnerability Assessment

Question 3

You have an Azure SQL Database in the General Purpose tier. You notice that the log write throughput is consistently above the service tier limit, causing transaction throttling. You need to resolve this without moving to Business Critical. What should you do?

Accepted Answer

Batch transactions and reduce log writes.. Batching transactions reduces the number of log write operations, lowering the log write throughput below the service tier limit and avoiding throttling. Option A is incorrect because increasing the max log size does not affect the log write rate; it only provides more storage. Option C is incorrect because accelerated database recovery (ADR) improves recovery time and may reduce log I/O for crash recovery, but it does not directly reduce the sustained log write throughput from transaction processing. Option D is incorrect because the requirement explicitly states not to move to Business Critical.

Answer

Increase the max log size using ALTER DATABASE.

Answer

Enable accelerated database recovery to reduce log I/O.

Answer

Move to Business Critical tier.

Question 4

You are configuring Azure SQL Database for an e-commerce application that experiences variable traffic. You need to ensure that the database can automatically scale resources based on demand without manual intervention. The solution must also support scaling to zero compute when not in use to save costs. Which Azure SQL Database offering should you use?

Accepted Answer

Azure SQL Database serverless.. Azure SQL Database serverless is the correct choice because it automatically scales compute resources based on demand and supports pausing the database when idle, effectively scaling to zero compute to save costs. This aligns perfectly with the requirements of variable traffic and cost optimization without manual intervention.

Answer

Azure SQL Database elastic pool.

Answer

Azure SQL Database Hyperscale.

Answer

Azure SQL Database Business Critical tier.

Question 5

You need to monitor the storage space usage of an Azure SQL Database over time. Which tool should you use?

Accepted Answer

Azure SQL Analytics (Azure Monitor). Azure SQL Analytics in Azure Monitor provides historical storage metrics. Option A is wrong because Intelligent Insights is for proactive diagnostics, not storage monitoring. Option C is wrong because Query Store focuses on query performance. Option D is wrong because SSMS does not provide historical monitoring.

Answer

Intelligent Insights

Answer

Query Store

Answer

SQL Server Management Studio (SSMS)

Question 6

Drag and drop the steps to configure transparent data encryption (TDE) for an Azure SQL Database using a customer-managed key in Azure Key Vault in the correct order.

Accepted Answer

Create Azure Key Vault and key, then grant Key Vault permissions to the Azure SQL Database server, then enable TDE with customer-managed key, then select the key and save.. First set up Key Vault with a key, grant permissions, then enable TDE with customer-managed key, select the key, and save.

Answer

Grant Key Vault permissions to the Azure SQL Database server, then create Azure Key Vault and key, then enable TDE with customer-managed key, then select the key and save.

Answer

Enable TDE with customer-managed key, then create Azure Key Vault and key, then grant Key Vault permissions to the Azure SQL Database server, then select the key and save.

Answer

Create Azure Key Vault and key, then enable TDE with customer-managed key, then grant Key Vault permissions to the Azure SQL Database server, then select the key and save.

Question 7

You run the query in the exhibit on an Azure SQL Database. The result shows high wait_time_ms for PAGEIOLATCH_SH waits. What does this indicate?

Accepted Answer

I/O subsystem bottleneck for read operations. PAGEIOLATCH_SH waits indicate that a query is waiting for a data page to be read from disk into the buffer pool, which is an I/O operation. High wait_time_ms for this wait type typically points to an I/O subsystem bottleneck for read operations, making option A correct. Option B (CPU bottleneck) is incorrect because PAGEIOLATCH_SH is related to I/O, not CPU. Option C (blocking) is incorrect because blocking is associated with LOCK waits, not PAGEIOLATCH_SH. Option D (memory pressure) is incorrect; while memory pressure can increase physical I/O, the wait type itself specifically indicates I/O latency for reading pages from disk.

Answer

CPU bottleneck

Answer

Blocking between concurrent transactions

Answer

Memory pressure

Question 8

Your Azure SQL Database is experiencing a sudden increase in wait time due to PAGEIOLATCH_SH waits. What should you do to reduce these waits?

Accepted Answer

Add appropriate indexes to reduce table scans. PAGEIOLATCH_SH waits indicate I/O bottlenecks caused by excessive page reads from disk. Adding appropriate indexes reduces the number of pages read by enabling more efficient data access (e.g., index seeks instead of table scans), directly reducing I/O. Option A is incorrect because increasing max memory does not address the underlying query inefficiency driving I/O. Option C is incorrect because page compression reduces storage but may increase CPU and does not primarily reduce I/O waits. Option D is incorrect because forcing parameterization improves plan reuse but does not target I/O reduction.

Answer

Increase the database max memory

Answer

Enable page compression on large tables

Answer

Force parameterization of queries

Question 9

You are a database administrator for a hospital that uses Azure SQL Database to store patient records. The hospital's security policy requires that all database access be authenticated using Microsoft Entra ID (formerly Azure AD). You have already created a Microsoft Entra ID user for yourself and granted you the 'db_owner' role. You now need to create a new Microsoft Entra ID user for a nurse who needs read-only access to the database. What should you do first?

Accepted Answer

Connect to the database using your Microsoft Entra account and run 'CREATE USER [nurse@hospital.onmicrosoft.com] FROM EXTERNAL PROVIDER'. The nurse must be created as a contained database user mapped to Microsoft Entra ID. Since the hospital uses Azure SQL Database and requires Microsoft Entra authentication, you must connect to the user database (not master) using your Microsoft Entra account (which has db_owner privileges) and run 'CREATE USER [nurse@hospital.onmicrosoft.com] FROM EXTERNAL PROVIDER'. This creates a database user that authenticates via Microsoft Entra ID without requiring a server-level login, aligning with the security policy.

Answer

In the Azure portal, add the nurse as a server-level Microsoft Entra admin

Answer

Create a SQL login for the nurse on the logical server and then create a user in the database mapped to that login

Answer

Connect to the master database using SQL authentication and run 'CREATE USER [nurse@hospital.onmicrosoft.com] FROM EXTERNAL PROVIDER'

Question 10

You are configuring security for an Azure SQL Managed Instance. The instance will host a critical application that requires always encrypted with secure enclaves. Which TWO actions must you take to support this feature? (Choose two.)

Accepted Answer

Select the Intel Software Guard Extensions (Intel SGX) enclave type.. Always Encrypted with secure enclaves on Azure SQL Managed Instance requires the Intel Software Guard Extensions (Intel SGX) enclave type. Intel SGX is the only supported enclave technology for this feature on managed instances, providing a trusted execution environment that protects sensitive data in memory during cryptographic operations.

Answer

Configure the column master key to be stored in Azure Key Vault.

Answer

Enable the enclave attestation policy on the managed instance.

Answer

Enable Virtualization-Based Security (VBS) enclave type.