DP-300 Practice Test 20 — 15 Questions

Question 1

You manage an Azure SQL Database that is part of a failover group. You need to automate the failover to the secondary region in the event of a disaster. Which approach should you use?

Accepted Answer

Configure the auto-failover group to automatically fail over.. Auto-failover groups are designed to automatically fail over to the secondary region in the event of a disaster, providing built-in automation. Option C is incorrect because while an Azure Automation runbook could be used to initiate a failover manually, it is redundant since the auto-failover group already handles automatic failover. Options B and D are incorrect because elastic jobs are for management tasks like data consistency, and SQL Server Agent is not available in Azure SQL Database.

Answer

Schedule a failover using elastic jobs.

Answer

Create an Azure Automation runbook that initiates the failover.

Answer

Use a SQL Server Agent job to trigger failover.

Question 2

Your Azure SQL Managed Instance is experiencing high PAGELATCH_SH waits. You need to reduce this contention. What should you implement?

Accepted Answer

Add more data files to the filegroup. Adding more data files to the filegroup spreads out page allocations, reducing contention for allocation structures and thus decreasing PAGELATCH_SH waits. Option A is incorrect because scaling up may provide more resources but does not directly address the page latch contention caused by allocation bottlenecks. Option B is incorrect because delayed durability only affects transaction log write behavior, not page latches. Option C is incorrect because a readable secondary replica does not alleviate latch contention on the primary; it is designed for read workload offloading, not contention reduction.

Answer

Scale up the managed instance to a higher service tier

Answer

Enable delayed durability

Answer

Configure a readable secondary replica

Question 3

You need to prevent users from accidentally deleting an Azure SQL Database. What should you configure?

Accepted Answer

Apply a 'CanNotDelete' Azure Resource Lock on the resource group.. A 'CanNotDelete' Azure Resource Lock on the resource group prevents any user, including those with high-level permissions like Owner, from deleting the Azure SQL Database. This lock overrides all role-based access control (RBAC) permissions at the resource or resource group level, ensuring accidental deletion is blocked even if a user has delete permissions.

Answer

Revoke the db_ddladmin role from users.

Answer

Create an Azure Policy to deny SQL Database creation.

Answer

Set a deny rule in the SQL Database firewall.

Question 4

You are configuring a private endpoint for an Azure SQL Database. The exhibit shows the current network ACLs. You need to ensure that only traffic from a specific subnet in VNet1 is allowed, and all other traffic is denied. What should you do?

Accepted Answer

No changes needed; the configuration already meets the requirement.. The exhibit shows that the private endpoint is configured with a network ACL that has a deny-all default action and an explicit allow rule for the specific subnet in VNet1. Since private endpoints use network policies (like NSG rules) to filter traffic, and the ACL already denies all traffic except the allowed subnet, no changes are needed. The configuration meets the requirement because the private endpoint's network ACLs are evaluated in order, and the explicit allow for the subnet overrides the default deny for all other traffic.

Answer

Add an IP rule to allow the subnet's IP range.

Answer

Set ignoreMissingVnetServiceEndpoint to true.

Answer

Change defaultAction to Allow.

Question 5

Which TWO metrics are available in Azure Monitor for an Azure SQL Database that can be used to set autoscale rules? (Select two.)

Accepted Answer

CPU percentage. Option A (CPU percentage) is correct because Azure SQL Database exposes the 'cpu_percent' metric through Azure Monitor, which reports the average CPU utilization of the database and is a standard metric used to drive autoscale rules (for example, scaling up when CPU exceeds a threshold). Option D (DTU percentage) is correct because the 'dtu_consumption_percent' metric measures the percentage of the Database Transaction Unit limit consumed and is one of the most commonly used metrics for autoscale decisions on DTU-based databases. The unmarked options do not belong: Log write throughput is not a directly exposed Azure Monitor metric for autoscale on Azure SQL Database, Deadlock count is a diagnostic/query-level statistic rather than an autoscale metric, and Query Store size is an internal Query Store property, not an Azure Monitor metric available for autoscale rules.

Answer

Log write throughput

Answer

Deadlock count

Answer

Query Store size

Question 6

You administer a large Azure SQL Database that is used for a SaaS application. The database has a table with over 1 billion rows that is frequently queried by customer ID. The table currently has a clustered index on an identity column and a nonclustered index on customer ID. Queries that filter by customer ID are experiencing high IO and long execution times. You analyze the execution plan and see that the nonclustered index is used, but there are many key lookups. You need to optimize the query performance while minimizing storage overhead. What should you do?

Accepted Answer

Add all queried columns as included columns to the nonclustered index. The nonclustered index on customer ID is being used, but the many key lookups indicate that the query needs columns not present in the index, forcing a lookup back to the clustered index for each row. Adding the queried columns as included columns to the nonclustered index makes it a covering index, eliminating the key lookups while adding only the necessary columns to the index leaf level — a much smaller storage footprint than a columnstore or partitioning solution.

Answer

Create a clustered columnstore index on the table

Answer

Create a filtered index on customer ID for frequent values

Answer

Partition the table by customer ID

Question 7

You have an Azure SQL Managed Instance configured with an auto-failover group between two regions. You need to ensure that client applications can automatically connect to the secondary instance after a failover without changing connection strings. What should you configure?

Accepted Answer

Use the failover group listener endpoint in the connection string.. An auto-failover group provides a read/write listener endpoint (and a read-only listener) with a DNS name that always points to the current primary. Applications connect to the listener FQDN, so after failover the DNS resolves to the new primary without any connection string change.

Answer

Set up a load balancer with health probes.

Answer

Deploy an Application Gateway with backend pools for each region.

Answer

Configure a Traffic Manager profile with endpoint monitoring.

Question 8

Which TWO actions should you take to implement a secure environment for Azure SQL Database that meets the principle of least privilege?

Accepted Answer

Assign database roles instead of individual permissions.. Assigning database roles instead of individual permissions (Option A) aligns with the principle of least privilege by grouping necessary permissions into predefined roles, reducing the risk of over-privileging and simplifying permission management. Contained database users with Microsoft Entra ID authentication (Option C) eliminate the dependency on server-level logins, allowing authentication at the database level and enabling fine-grained access control without granting server-wide privileges.

Answer

Enable all database features for maximum functionality.

Answer

Use server-level logins for all users.

Answer

Grant the db_owner role to all application users.

Question 9

Which TWO options are valid methods to optimize query performance in Azure SQL Managed Instance?

Accepted Answer

Use columnstore indexes on large tables. Options A and D are correct. Columnstore indexes (A) improve analytical query performance by reducing I/O and using batch processing. Query Store (D) helps identify regressions by tracking execution plans and performance metrics. Option B is incorrect; setting database compatibility level to 150 may enable new features but is not a direct query optimization method. Option C is incorrect; increasing storage size does not improve query performance. Option E is incorrect; Transparent Data Encryption (TDE) secures data but does not enhance performance.

Answer

Set database compatibility level to 150

Answer

Increase the maximum storage size

Answer

Enable Transparent Data Encryption (TDE)

Question 10

You query the sys.dm_geo_replication_link_status dynamic management view for an Azure SQL Database configured with active geo-replication. The exhibit shows the output. What does this indicate about the replication health?

Accepted Answer

The secondary is fully synchronized with minimal lag.. The replicationLag of 5 seconds is minimal and normal for active geo-replication, indicating the secondary is nearly synchronized. The replicationState 'CATCH_UP' confirms that the secondary is fully caught up with the primary. Options B, C, and D are incorrect: B is wrong because a secondary role is expected in geo-replication; C is wrong because 5 seconds lag is not a problem; D is wrong because the state is CATCH_UP, not SEEDING.

Answer

The secondary role indicates a failover has occurred.

Answer

The secondary is 5 seconds behind, indicating a problem.

Answer

The replication is in the seeding phase.

Question 11

Your company has an Azure SQL Database in the General Purpose tier. You need to reduce the recovery point objective (RPO) from 1 hour to less than 1 minute for disaster recovery. Which action should you take?

Accepted Answer

Upgrade the database to Business Critical tier.. None of the listed options reduce the geo-replication RPO to under 1 minute. Upgrading to Business Critical (B) improves local HA with synchronous replicas but does not affect geo-replication, which remains asynchronous with an RPO of about 1 hour. Enabling zone redundancy (C) protects against zone failures, not regional disasters. Increasing backup retention (A) or DTU/vCore (D) does not change replication mode. To achieve sub-minute RPO for geo-disaster recovery, you must configure Active Geo-Replication or a failover group, which are not among the options.

Answer

Increase the backup retention period to 35 days.

Answer

Enable zone redundancy on the database.

Answer

Increase the DTU or vCore count.

Question 12

Which of the following are valid methods to authenticate to Azure SQL Database using Microsoft Entra ID? (Select all that apply.)

Accepted Answer

Microsoft Entra ID managed identity. Microsoft Entra ID offers several authentication methods for Azure SQL Database. Managed identity (A) and integrated authentication (C) are directly supported without additional token acquisition steps. Certificate-based authentication (D) and password authentication (E) require an intermediate application or client library to obtain an Entra ID token, and are not considered direct authentication methods for Azure SQL Database. AD FS tokens (B) are not directly accepted; Entra ID must issue the token after authentication.

Answer

Active Directory Federation Services (AD FS) token

Answer

Microsoft Entra ID certificate-based authentication

Answer

Microsoft Entra ID password authentication

Question 13

You are configuring security for an Azure SQL Database that will be used by a web application. The application uses a connection string with SQL authentication. You need to protect the database from SQL injection attacks. Which two measures should you implement? (Choose two.)

Accepted Answer

Use parameterized queries in the application.. Parameterized queries ensure that user input is treated as data, not executable code, by separating SQL logic from input values. This prevents attackers from injecting malicious SQL statements into the query string, which is the primary defense against SQL injection attacks.

Answer

Enable Transparent Data Encryption (TDE).

Answer

Configure Dynamic Data Masking (DDM).

Answer

Enable Always Encrypted on sensitive columns.

Question 14

Which TWO of the following are valid methods to configure network security for Azure SQL Managed Instance?

Accepted Answer

Deploy the instance in an isolated subnet with network security groups (NSGs).. Option B is correct because Azure SQL Managed Instance is natively deployed inside an Azure virtual network subnet, and you secure it by using Network Security Groups (NSGs) on that subnet to control inbound and outbound traffic on the required ports (for example, 1433, 5022, 11000-11999). Option C is correct because Azure Private Link with a private endpoint lets clients reach the managed instance over a private IP address within the virtual network, providing private, isolated connectivity rather than exposing it publicly. Option A is not valid because virtual network rules are a feature of Azure SQL Database and Azure Database services, not the configuration model for a Managed Instance, which already lives in a delegated subnet. Option D is not valid because server-level IP firewall rules apply to Azure SQL Database's logical server, whereas Managed Instance networking is governed by the VNet/subnet and NSG configuration. Option E is not valid because service endpoints are used to restrict access to PaaS services from a subnet, but a Managed Instance is deployed into the subnet itself and does not use service endpoints for its own network security.

Answer

Configure a virtual network rule to allow traffic from a specific subnet.

Answer

Configure server-level IP firewall rules.

Answer

Enable service endpoints on the subnet.

Question 15

Which TWO actions are required to enable Microsoft Entra ID authentication for an Azure SQL Database?

Accepted Answer

Create contained database users mapped to Microsoft Entra ID identities. To enable Microsoft Entra ID authentication for an Azure SQL Database, you must first assign a Microsoft Entra ID user or group as the Azure SQL Database admin (Option B). After the admin is assigned, you create contained database users mapped to Microsoft Entra ID identities in the database (Option A) so those identities can authenticate. Setting the database's authentication mode to 'Microsoft Entra ID Only Authentication' (Option C) is optional and enforces that only Microsoft Entra ID identities can connect; it is not required to enable Microsoft Entra ID authentication. Azure SQL Managed Instance (Option D) and registering an application in Microsoft Entra ID (Option E) are not required.

Answer

Set the database's authentication mode to 'Azure AD Only Authentication'

Answer

Deploy the database in Azure SQL Managed Instance

Answer

Register the application in Microsoft Entra ID