DP-300 Timed Practice Test 1 — 10 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?

10 minute time limit — choose an answer to begin.

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.