DP-300 Practice Test 22 — 15 Questions

Question 1

Which TWO are valid methods for auditing Azure SQL Database activity? (Choose two.)

Accepted Answer

Azure Storage account. Azure SQL Database auditing writes audit logs to a target destination, and both an Azure Storage account (option C) and a Log Analytics workspace (option D) are supported audit log destinations — the storage account stores audit records as blobs in a container, while the Log Analytics workspace ingests them for querying with KQL and integration with Azure Monitor. These are the two valid auditing targets offered when configuring auditing at the server or database level. Azure Event Grid (option A) is an event-routing service used for reacting to events, not a SQL Database audit log sink, and Azure Monitor Metrics (option B) holds numeric time-series metrics rather than detailed audit records. An on-premises file share (option E) is not a supported destination for Azure SQL Database auditing.

Answer

Azure Event Grid

Answer

Azure Monitor Metrics

Answer

On-premises file share

Question 2

You have an Azure SQL Database that needs to be backed up daily using Azure Automation runbooks. The runbook must trigger an export of the database to a storage account. How should you configure the runbook to authenticate securely to Azure?

Accepted Answer

Enable a system-assigned managed identity for the Automation account. Managed Identity (system-assigned or user-assigned) is the recommended secure authentication method for Azure Automation runbooks, avoiding stored credentials. Option A uses credentials stored in the runbook, which is less secure. Option B uses automation account credentials, which still requires key management. Option D is not a valid type.

Answer

Use a shared access signature (SAS) token stored in the runbook

Answer

Use Automation Account credential assets

Answer

Store the SQL admin credentials as variables in the runbook

Question 3

You are a database administrator for a healthcare company that uses Azure SQL Database for its electronic health records (EHR) system. The database is in the West Europe region using the General Purpose service tier. The company is expanding to the United States and wants to set up disaster recovery with the secondary in East US. The requirements are: RPO of 5 minutes and RTO of 1 hour. The application should automatically failover without manual intervention. Additionally, you must ensure that the secondary database is not used for read traffic to avoid any performance impact on the primary. What should you configure?

Accepted Answer

Create a failover group with a non-readable secondary in East US and enable auto-failover.. A failover group with auto-failover and a non-readable secondary meets all requirements: RPO of 5 minutes can be achieved with async replication, RTO of 1 hour with automatic failover, and no read traffic. Option A is wrong because active geo-replication alone does not provide automatic failover. Option B is wrong because a readable secondary would be used for read traffic. Option D is wrong because zone-redundancy doesn't protect regionally.

Answer

Configure active geo-replication to a secondary database in East US and set up a custom monitoring script to trigger failover.

Answer

Create a failover group with a readable secondary in East US and enable auto-failover.

Answer

Deploy a zone-redundant General Purpose database in West Europe and use geo-restore to East US.

Question 4

You are managing an Azure SQL Database that is used by a real-time analytics application. The database uses the Hyperscale service tier. You notice that the transaction log rate is consistently high, causing performance degradation. You need to reduce the log generation rate without compromising data durability. What should you do?

Accepted Answer

Increase the service tier to Business Critical.. Increasing the service tier to Business Critical does not reduce the transaction log generation rate; it only improves log write throughput. The log generation rate is workload‑dependent and is not changed by moving to a higher tier. None of the provided options achieve the goal of reducing the log generation rate while maintaining durability. To reduce the rate, you would need to optimize the application (e.g., batching, reducing transactions), which is not offered among the choices.

Answer

Enable compression on transaction log backups.

Answer

Create additional nonclustered indexes on frequently updated tables.

Answer

Increase the backup retention period.

Question 5

You need to configure Azure SQL Database to allow connections only from Azure services and from a specific on-premises IP range. Which firewall rule configuration should you apply at the server level?

Accepted Answer

Set 'Allow Azure Services and resources to access this server' to ON and add a firewall rule for the on-premises IP range.. Enabling 'Allow Azure Services and resources to access this server' permits connections from all Azure services (including those from other subscriptions) by adding a special firewall rule that allows Azure IP ranges. Adding a separate firewall rule for the specific on-premises IP range then restricts non-Azure external traffic to only that range. This combination meets the requirement to allow only Azure services and the specified on-premises range.

Answer

Create a private endpoint for the server.

Answer

Create a virtual network service endpoint and add a VNet firewall rule.

Answer

Set 'Allow Azure Services and resources to access this server' to OFF and add a firewall rule for the on-premises IP range.

Question 6

A company runs SQL Server 2019 on Azure Virtual Machines in an availability set. They need to achieve high availability for a critical database with automatic failover and no shared storage. The solution must minimize downtime during planned maintenance. What should they implement?

Accepted Answer

Create an Always On Availability Group with an availability group listener. An Always On Availability Group (AG) with a listener provides high availability with automatic failover at the database level without requiring shared storage. This solution meets the requirement for automatic failover and minimizes downtime during planned maintenance by allowing manual failover to a synchronized secondary replica with minimal disruption.

Answer

Configure Log Shipping to a secondary VM

Answer

Deploy a Failover Cluster Instance using Azure Shared Disks

Answer

Use Database Mirroring with automatic failover

Question 7

You are reviewing an ARM template for Azure SQL Database. The exhibit shows a resource definition for Transparent Data Encryption (TDE). You need to ensure that the database uses customer-managed keys (CMK) stored in Azure Key Vault instead of service-managed keys. What additional configuration is required?

Accepted Answer

Add a resource of type 'Microsoft.Sql/servers/encryptionProtector' and reference the key vault key.. To use customer-managed keys (CMK) for Transparent Data Encryption (TDE) in Azure SQL Database, you must configure an encryption protector that points to the key in Azure Key Vault. This is done by adding a resource of type 'Microsoft.Sql/servers/encryptionProtector' to the ARM template, which sets the server-level encryption protector to use the specified key vault key. Option D correctly identifies this required resource, as TDE with CMK is managed at the server level, not directly on the database resource.

Answer

Modify the database resource to include a 'keyVaultUri' property.

Answer

Enable Always Encrypted on the database.

Answer

Set the TDE state to 'Disabled' and then re-enable with a customer key.

Question 8

Which TWO metrics should you monitor in Azure SQL Database to detect a potential memory pressure issue?

Accepted Answer

Page Life Expectancy. Page Life Expectancy (PLE) is a key metric that indicates how long a data page remains in the buffer pool before being evicted. A consistently low PLE (e.g., below 300 seconds) signals that pages are being flushed too quickly due to memory pressure, often from insufficient buffer pool memory. Memory Grants Pending tracks the number of queries waiting for a memory grant to execute; a non-zero value indicates that the server cannot allocate enough memory to satisfy query workspace requirements, directly pointing to memory pressure.

Answer

Average CPU percent

Answer

Log IO

Answer

Data IO

Question 9

You have an Azure SQL Database with a heavy workload. You notice that the `PAGEIOLATCH_SH` wait is the top wait. Which performance issue does this indicate?

Accepted Answer

I/O subsystem bottleneck. The `PAGEIOLATCH_SH` wait type indicates that a query is waiting for a data page to be read from disk into the buffer pool. Since this is the top wait, it points to an I/O subsystem bottleneck where the storage cannot keep up with the demand for reading pages, causing performance degradation.

Answer

Blocking

Answer

CPU bottleneck

Answer

Memory pressure

Question 10

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

Accepted Answer

At least one database on the primary server.. Options B, C, and D are correct. A failover group requires a primary server, a secondary server, and at least one database on the primary server. Option A is incorrect because the failover group listener is automatically created and does not need to be separately configured. Option E is incorrect because an elastic pool is optional; failover groups can contain individual databases or elastic pools.

Answer

Failover group listener.

Answer

An elastic pool.

Question 11

You are reviewing a JSON representation of an Azure SQL Database firewall rule. What is the effect of this rule?

Accepted Answer

Allows all IP addresses from 10.0.0.0 to 10.0.0.255.. The JSON representation of the Azure SQL Database firewall rule with startIpAddress '10.0.0.0' and endIpAddress '10.0.0.255' defines a range that allows all IP addresses from 10.0.0.0 to 10.0.0.255 inclusive. Azure SQL Database firewall rules use inclusive IP range matching, so any client with an IP in that range is permitted to connect, provided the rule is enabled.

Answer

Blocks all IP addresses from 10.0.0.0 to 10.0.0.255.

Answer

Allows all IP addresses except 10.0.0.0 to 10.0.0.255.

Answer

Allows only the IP address 10.0.0.0.

Question 12

You are deploying an Azure SQL Database that will be used by a global application. You need to ensure that read-intensive workloads are offloaded from the primary database to improve performance. Which feature should you enable?

Accepted Answer

Read scale-out.. Read scale-out (A) is the correct feature because it allows you to offload read-only workloads to a read-only replica of the Azure SQL Database. By enabling the 'Read scale-out' property, the database automatically routes connections with `ApplicationIntent=ReadOnly` to a secondary replica, freeing the primary from read-intensive queries and improving overall performance for write operations.

Answer

Automatic tuning.

Answer

Connection pooling.

Answer

Active geo-replication.

Question 13

Which TWO actions should you take to secure Azure SQL Database against SQL injection attacks?

Accepted Answer

Use parameterized queries in application code. Option D is correct because parameterized queries cause the database engine to treat user input strictly as data rather than executable SQL, so injected payloads cannot alter the query's structure or logic. Option E is correct because stored procedures that accept parameters likewise bind input values to typed parameters, preventing attackers from concatenating malicious SQL into the statement. Both techniques address the root cause of SQL injection at the application/database interface. Option A (Transparent Data Encryption) only encrypts data at rest and does nothing to stop injection. Option B (auditing) merely records activity for detection and forensics, not prevention. Option C (firewall rules) restricts network origins but cannot stop injection from an already-trusted client or application.

Answer

Enable Transparent Data Encryption

Answer

Enable auditing for all database operations

Answer

Configure firewall rules to allow only trusted IP addresses

Question 14

You are setting up a new Azure SQL Database for a development team. The database will contain test data that mimics production but with some sensitive fields obfuscated. You need to ensure that developers can query the database without seeing the actual sensitive data. The developers will use Microsoft Entra ID authentication. You have the following requirements:
- The sensitive data should be automatically masked in query results for all developers except the database administrator.
- The masking should be applied without modifying the application code.
- The solution should be easy to manage and not require changes to the data model.

What should you implement?

Accepted Answer

Configure dynamic data masking on the sensitive columns, and add the database administrator to the unmask permission.. Dynamic Data Masking (DDM) can be configured on sensitive columns to automatically mask data in query results without modifying application code or the data model. The database administrator can be added to the unmask permission to see the actual data. Option A is incorrect because creating views would require changes to the data model and application queries. Option C is incorrect because Always Encrypted requires application code changes to handle encryption/decryption. Option D is incorrect because Row-Level Security filters rows based on predicates, not columns, and does not mask data.

Answer

Create views that exclude sensitive columns and grant developers access to the views instead of the base tables.

Answer

Implement Always Encrypted with column encryption, and grant the developers access to the encryption keys.

Answer

Create a row-level security policy that denies access to sensitive rows for developers.

Question 15

You are monitoring an Azure SQL Database using the Automatic Tuning feature. The database has a workload that is read-intensive. You enable the CREATE INDEX and DROP INDEX options. After a week, you observe that the database has created several new indexes automatically. However, you notice that one of the new indexes is causing increased write latency for an application that performs frequent updates. What should you do to resolve the issue without losing the benefits of automatic tuning for other indexes?

Accepted Answer

Manually drop the problematic index using a DROP INDEX command.. Manually dropping the problematic index allows you to resolve the specific performance issue caused by increased write latency while retaining the benefits of automatic tuning for other indexes. The Automatic Tuning feature in Azure SQL Database can create indexes to improve read performance, but these indexes may introduce overhead on write operations. By issuing a DROP INDEX command, you surgically remove only the offending index without disabling the overall tuning mechanism.

Answer

Use the Azure portal to revert all automatic tuning recommendations for the past week.

Answer

Manually create the missing indexes that were dropped by automatic tuning.

Answer

Disable automatic tuning for the entire database.