DP-300 Practice Questions

Question 1

You are responsible for a set of Azure SQL Databases that are used by different departments in your organization. The databases are deployed in an elastic pool with Standard tier (eDTU 200). Usage patterns show that the marketing database uses high CPU during the day, while the sales database uses high IO at night. You want to optimize costs while ensuring each database gets the resources it needs. What should you do?

Accepted Answer

Configure minimum and maximum eDTU per database in the pool. Configuring minimum and maximum eDTU per database within the elastic pool guarantees that each database gets the resources it needs during peak usage (e.g., high CPU for marketing during the day, high IO for sales at night) while still sharing the pool's overall eDTU, optimizing costs. Option B is incorrect because moving to a vCore-based pool does not inherently address resource contention; it's a different purchasing model. Option C is incorrect because adding more databases to the pool increases contention and may degrade performance. Option D is incorrect because moving each database to a standalone DTU tier would likely increase costs and eliminate the benefits of pooling.

Answer

Migrate the pool to a vCore-based elastic pool

Answer

Add more databases to the pool to spread the load

Answer

Move each database to a standalone DTU tier

Question 2

You are designing high availability for a critical Azure SQL Database. The database is 1 TB in size and requires an RPO of 0 (zero data loss) and an RTO of less than 30 seconds. Which TWO configurations can meet these requirements?

Accepted Answer

Business Critical service tier with zone redundancy enabled.. Options A and C are correct. For zero data loss (RPO=0), synchronous replication is required. Business Critical (option A) with zone redundancy provides synchronous replicas within the same region and automatic failover, meeting both RPO=0 and RTO<30 seconds. Hyperscale (option C) also offers zone redundancy with synchronous log replication, enabling rapid failover without data loss. Option B (auto-failover group with same-region secondary) uses asynchronous replication, so data loss can occur. Option D (General Purpose) uses asynchronous replication as well and cannot guarantee zero data loss. Option E (active geo-replication) is also asynchronous, so it does not meet the RPO requirement.

Answer

Auto-failover group with a secondary in the same region.

Answer

General Purpose service tier with zone redundancy enabled.

Answer

Active geo-replication to a secondary in a different region.

Question 3

You are migrating an on-premises SQL Server database to Azure SQL Managed Instance. The source database uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. Which step is required to enable TDE in the target Managed Instance?

Accepted Answer

Export the certificate to a PFX file, import it to Azure Key Vault, and configure TDE with a key vault key. Azure SQL Managed Instance does not support importing user-provided certificates for TDE. Instead, it relies on Azure Key Vault as the external key store. Therefore, you must export the on-premises TDE certificate to a PFX file, import it into Azure Key Vault, and then configure the Managed Instance to use that key vault key for TDE. This ensures the database encryption key can be unwrapped and the database remains encrypted during and after migration.

Answer

Create a self-signed certificate in the Managed Instance and restore the database with KEEP_TDE

Answer

Use the same certificate file by uploading it to the Managed Instance via SQL Server Management Studio

Answer

Disable TDE on the source database before migration, then enable it after migration

Question 4

You are a database administrator for an Azure SQL Managed Instance. You need to ensure that all connections to the instance use encrypted connections. What should you configure?

Accepted Answer

Set the 'Force Encryption' option to Yes on the server properties.. Setting 'Force Encryption' to Yes on the Azure SQL Managed Instance server properties enforces the use of TLS (Transport Layer Security) for all client connections. This configuration ensures that any client attempting to connect without encryption will be rejected, thereby meeting the requirement that all connections use encrypted connections. The setting is applied at the instance level and overrides client-side encryption preferences.

Answer

Enable Transparent Data Encryption (TDE).

Answer

Enable Always Encrypted for sensitive columns.

Answer

Configure a firewall rule to allow only specific IP addresses.

Question 5

You need to configure a long-term retention policy for backups of an Azure SQL Database that must retain weekly full backups for 5 years and monthly full backups for 10 years. Which backup retention feature should you use?

Accepted Answer

Long-Term Retention (LTR) policy. Long-Term Retention (LTR) policy allows you to retain backups beyond the default retention period, with weekly, monthly, and yearly options. Option A is wrong because geo-restore is for disaster recovery, not long-term retention. Option C is wrong because automated backups have a maximum retention of 35 days. Option D is wrong because Point-In-Time Restore (PITR) retention only goes up to 35 days and does not support long-term retention.

Answer

Geo-restore feature

Answer

Automated backups retention period

Answer

Point-In-Time Restore (PITR) retention

Question 6

Which TWO of the following are valid reasons to use zone-redundant configuration for Azure SQL Database? (Choose Two.)

Accepted Answer

Protection against a single availability zone failure within a region.. Zone-redundant configuration for Azure SQL Database replicates databases across multiple availability zones within the same region, providing protection against a single zone failure (option C) and offering higher availability than a single-zone deployment (option E). Option A is incorrect because zone redundancy does not reduce read latency; that is achieved by geo-replication or read scale-out. Option B is incorrect because automatic failover to a paired region is a feature of geo-redundant disaster recovery, not zone redundancy. Option D is incorrect because zone redundancy does not protect against a full regional disaster; it only protects within a region.

Answer

Reduce read latency for globally distributed users.

Answer

Automatic failover to a paired region.

Answer

Protection against a regional disaster.

Question 7

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 8

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 9

Your organization uses Azure SQL Database and wants to restrict access to only specific on-premises IP addresses. The database has a public endpoint. Which security feature should you configure?

Accepted Answer

Set firewall rules to allow specific on-premises IP ranges.. To restrict access to specific on-premises IP addresses, you should configure firewall rules to allow those IP ranges. Setting a firewall rule ensures that only traffic from allowed IP addresses can reach the database. Option C directly addresses this requirement.

Answer

Enable 'Allow Azure services and resources to access this server' in the firewall settings.

Answer

Enable Always Encrypted with secure enclaves.

Answer

Create a virtual network service endpoint for SQL.

Answer

Configure a private endpoint for the database.

Question 10

Match each Azure SQL Database pricing tier to its key feature.

Accepted Answer

Basic: Low-cost, lightweight workloads with minimal compute and memory resources.. These tiers define the compute and memory resources available for Azure SQL Database.

Answer

Basic: Balanced compute and memory resources for most enterprise workloads with scalable storage.

Answer

Standard: High memory-to-core ratio for memory-intensive workloads like analytics.

Question 11

You have an Azure SQL Managed Instance with a large number of databases. You need to monitor the storage space used by each database to proactively manage capacity. Which tool should you use?

Accepted Answer

Azure SQL Analytics (preview). Azure SQL Analytics (preview) in Azure Monitor provides a consolidated monitoring solution for Azure SQL databases and managed instances, including detailed storage usage per database. Option A is wrong because Query Performance Insight focuses on query-level metrics like query duration and CPU usage, not per-database storage. Option B is wrong because Automatic tuning is a feature that automatically indexes and optimizes query plans, not a monitoring tool. Option C is wrong because Intelligent Insights provides proactive performance diagnostics and anomaly detection, but does not directly per-database storage monitoring. Therefore, only Azure SQL Analytics meets the requirement.

Answer

Query Performance Insight

Answer

Automatic tuning

Answer

Intelligent Insights

Question 12

You are monitoring an Azure SQL Database that is experiencing high DTU usage. Which TWO metrics should you examine to determine whether the bottleneck is CPU or I/O?

Accepted Answer

Log write bytes per second.. Average CPU percentage directly measures CPU utilization, indicating a CPU bottleneck when high. Log write bytes per second measures the throughput of transaction log writes, which is a key indicator of I/O activity and can reveal an I/O bottleneck. Therefore, both A and B are correct metrics to diagnose whether the high DTU usage is caused by CPU or I/O.

Answer

Number of deadlocks per second.

Answer

Used storage space in GB.

Answer

Number of active sessions.

Question 13

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 14

You need to design a disaster recovery plan for an Azure SQL Managed Instance. The recovery time objective (RTO) is 1 hour, and the recovery point objective (RPO) is 15 minutes. What is the most cost-effective solution?

Accepted Answer

Create an auto-failover group with a secondary instance in a paired region.. Auto-failover groups (Option C) are the most cost-effective solution for Azure SQL Managed Instance with an RTO of 1 hour and RPO of 15 minutes. They provide an RTO of ~1 hour and an RPO of 5 seconds to 5 minutes, meeting the requirements at low cost. Option A is incorrect because active geo-replication is not supported for SQL Managed Instance. Option B is incorrect because log shipping is not natively supported for Managed Instance. Option D is incorrect because point-in-time restore has a longer RTO and RPO, typically not meeting the 15-minute RPO requirement.

Answer

Configure active geo-replication between instances in different regions.

Answer

Set up log shipping to a secondary instance.

Answer

Use point-in-time restore to a secondary region.

Question 15

You have an Azure SQL Database that uses the General Purpose service tier. The database is critical and you need to ensure that it remains available during a planned patching event that updates the underlying hardware. What does Azure SQL Database provide to maintain availability during such events?

Accepted Answer

Automated failover to a built-in standby replica with minimal downtime.. The General Purpose tier includes built-in high availability with automated failover to a standby replica during planned patching events, minimizing downtime. Option A is incorrect because the database is not taken offline; it fails over seamlessly. Option B is incorrect because manual failover is not required—the process is automatic. Option C is incorrect because General Purpose does not provide zone-redundant replicas by default; that is a feature of the Business Critical or Hyperscale tiers.

Answer

The database is taken offline during patching and restored afterward.

Answer

You must manually fail over to a secondary database to avoid downtime.

Answer

Zone-redundant replicas that ensure zero downtime.

Microsoft Azure Database Administrator Associate DP-300 practice test

Three ways to study

Study DP-300 by domain

Study DP-300 by topic

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

Configure and manage automation of tasks practice questions

Plan and configure high availability and disaster recovery practice questions

Implement a secure environment 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

How to use these DP-300 questions

Quick answer