DP-300 Practice 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?

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.

Question 11

You administer a SQL Managed Instance in the West Europe region. You need to create a disaster recovery replica in North Europe with automated failover. The replica must be readable and support backups. What should you configure?

Accepted Answer

Create a failover group with the secondary instance in North Europe.. SQL Managed Instance supports failover groups for automated failover between regions, and the secondary instance in a failover group is readable and supports backups. Option A is incorrect because log shipping is not supported for SQL Managed Instance. Option B is incorrect because active geo-replication is not supported for SQL Managed Instance; failover groups are the appropriate solution. Option C is incorrect because the secondary instance in a failover group is readable and can be used for read-only workloads.

Answer

Set up log shipping from West Europe to North Europe.

Answer

Configure active geo-replication between the instances.

Answer

Create a failover group, but note the secondary is not readable.

Question 12

You are reviewing the ARM template snippet for an Azure SQL Database failover group. The primary server is in East US. The secondary is in West Europe. The readWriteEndpoint has automatic failover with a grace period of 60 minutes. The readOnlyEndpoint is disabled. After a complete outage in East US, what will happen?

Accepted Answer

The failover group will wait 60 minutes, then automatically fail over to West Europe.. With automatic failover enabled on the read/write endpoint and a 60-minute grace period, Azure SQL failover groups wait the full grace period after detecting the primary outage before promoting the secondary. Only after that window elapses does the geo-secondary in West Europe become the new primary. The readOnlyEndpoint setting is irrelevant to read/write failover behavior.

Answer

The failover group will not fail over automatically because the grace period is too long.

Answer

The failover group will immediately fail over to West Europe.

Answer

The failover group requires manual failover because readOnlyEndpoint is disabled.

Question 13

Which THREE of the following are best practices for managing keys in Azure Key Vault for use with Azure SQL Database TDE?

Accepted Answer

Enable soft-delete and purge protection on the Key Vault.. Enabling soft-delete and purge protection on the Key Vault is a best practice because soft-delete retains deleted keys for a configurable retention period (default 90 days), allowing recovery if a key is accidentally deleted. Purge protection prevents permanent deletion of keys even after the soft-delete retention period expires, which is critical for TDE because if the key is permanently lost, the encrypted database becomes inaccessible. Together, these features ensure that the TDE protector key is never irrevocably lost, maintaining database recoverability and compliance.

Answer

Store the Key Vault in the same resource group as the SQL server.

Answer

Disable Key Vault auditing to reduce costs.

Question 14

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database. The template configures backup retention. What is the effect of this configuration?

Accepted Answer

Point-in-time restore (PITR) backups are retained for 7 days, and differential backups occur every 12 hours.. The ARM template configures the backup retention settings for Azure SQL Database. By default, Azure SQL Database automatically performs full backups every week, differential backups every 12 hours, and transaction log backups every 5–10 minutes. The configuration shown sets the point-in-time restore (PITR) retention period to 7 days, meaning you can restore the database to any point within the last 7 days. Differential backups occur every 12 hours to support efficient PITR, but the retention setting directly controls how far back you can perform a point-in-time restore.

Answer

Full backups are taken every 12 hours and retained for 7 days.

Answer

Long-term retention (LTR) is set to 7 days.

Answer

Transaction log backups are taken every 12 hours.

Question 15

You are configuring managed backup for an Azure SQL Managed Instance as shown in the exhibit. What is the purpose of this configuration?

Accepted Answer

To configure automated backups of the managed instance to Azure Blob Storage.. The configuration shown in the exhibit is for managed backup, which automatically schedules and stores full, differential, and transaction log backups of the Azure SQL Managed Instance to Azure Blob Storage. This ensures that backups are retained and available for restore operations without manual intervention, fulfilling the core purpose of automated backups to Azure Blob Storage.

Answer

To enable geo-replication for the managed instance.

Answer

To set up disaster recovery across Azure regions.

Answer

To configure point-in-time restore for the managed instance.

Microsoft Azure Database Administrator Associate DP-300 practice test

Three ways to study

Study DP-300 by domain

Study DP-300 by topic

Configure and manage automation of tasks practice questions

Plan and configure high availability and disaster recovery practice questions

Implement a secure environment practice questions

Plan and configure a high availability and disaster recovery environment practice questions

Plan and implement data platform resources practice questions

Monitor, configure, and optimize database resources practice questions

DP-300 fundamentals practice questions

DP-300 scenario practice questions

DP-300 troubleshooting practice questions

Top DP-300 questions

Microsoft Azure Database Administrator Associate DP-300 practice questions