DP-300 Practice Test 9 — 10 Questions

Question 1

You are configuring Microsoft Defender for SQL for an Azure SQL Database. You want to receive email notifications when a suspicious activity is detected. What should you configure?

Accepted Answer

In the Microsoft Defender for SQL settings, enable 'Email notifications to admins and subscription owners'.. Microsoft Defender for SQL includes a dedicated 'Email notifications to admins and subscription owners' setting under its threat detection policy. When enabled, this sends email alerts to Azure subscription owners and administrators whenever Defender detects suspicious activities such as SQL injection, brute-force attacks, or anomalous access patterns. This is the direct, built-in mechanism for email-based alerting on threat detections.

Answer

Configure a vulnerability assessment recurring scan and email the report.

Answer

Create an Azure Monitor alert rule for the 'SQL database threat detection' metric.

Answer

Enable SQL auditing and stream logs to a Log Analytics workspace.

Question 2

You are tuning a query in Azure SQL Database. Which TWO actions can reduce logical reads?

Accepted Answer

Create a nonclustered index on the columns used in WHERE clause. Option B is correct because a nonclustered index on the WHERE-clause columns gives the optimizer a narrow access path, so it can seek directly to matching rows instead of scanning the base table or clustered index, which lowers the number of 8 KB pages read and therefore logical reads. Option E is correct because updating statistics gives the optimizer accurate cardinality estimates, enabling better plan choices such as index seeks and appropriate join strategies, which reduces the pages touched per execution. Option A is not correct because forcing an index with a hint does not by itself reduce logical reads and can even increase them if the optimizer's original plan was better. Option C is not correct because wrapping the query in a stored procedure mainly aids plan reuse and parameterization, not the number of pages read per execution. Option D is not correct because increasing max memory affects buffer pool caching and physical I/O, not the logical read count, which is measured independently of whether pages come from memory or disk.

Answer

Add query hints to force index usage

Answer

Rewrite the query as a stored procedure

Answer

Increase the database max memory setting

Question 3

You are optimizing an Azure SQL Database that uses the Business Critical tier. Which TWO factors affect the maximum log rate?

Accepted Answer

Service level objective (SLO). The maximum log rate for an Azure SQL Database in the Business Critical tier is determined by the service level objective (SLO) and the number of vCores. Option A is correct because the SLO defines the performance tier and hardware configuration, which directly caps the log generation rate (e.g., Business Critical with 4 vCores has a specific log rate limit). Option B is correct because within a given SLO, the log rate scales with the number of vCores—more vCores allow a higher maximum log rate. Option C is incorrect because backup retention period affects storage and recovery, not log throughput. Option D is incorrect because Azure SQL Database manages log files automatically; the number of log files is not a user-configurable factor affecting log rate. Option E is incorrect because page compression reduces data size and I/O but does not directly determine the maximum log generation rate.

Answer

Backup retention period

Answer

Number of log files

Answer

Page compression level

Question 4

You have an Azure SQL Managed Instance in the East US region. To meet a 1-hour RPO and 2-hour RTO, you configure a failover group with a secondary in West US using automatic failover. During a test, you notice that the RTO is consistently 10 minutes longer than required. What is the most likely cause?

Accepted Answer

The failover group has a grace period of 20 minutes configured.. The failover group's automatic failover includes a grace period (default 20 minutes) that delays the actual failover after detecting unavailability. This grace period directly contributes to the RTO, explaining the 10-minute exceedance of the required 2-hour RTO. Option A is incorrect because Microsoft Entra ID authentication does not add noticeable latency to the failover process; authentication occurs independently of replication and failover. Option B is incorrect because asynchronous commit is the standard replication mode for failover groups and does not inherently cause additional delay beyond the grace period; the issue is not the commit mode but the waiting period before failover. Option D is incorrect because the secondary database is automatically seeded when the failover group is created; no manual restore is needed.

Answer

The failover group uses Microsoft Entra ID authentication, which adds latency.

Answer

The secondary database is configured with asynchronous commit, causing delay.

Answer

The secondary database is not seeded and needs to be restored.

Question 5

You manage an Azure SQL Database in the East US region. The database uses the General Purpose service tier with geo-redundant backup storage. You need to ensure that in the event of a regional outage, you can restore the database to the West US region with the least possible downtime. What should you use?

Accepted Answer

Configure active geo-replication to a secondary server in West US.. Active geo-replication to a secondary server in West US is the correct choice because it maintains a continuously synchronized secondary database in the target region, enabling a fast failover with minimal downtime if the primary region fails. Geo-restore can restore to West US from geo-redundant backups, but it has a much longer recovery time than failing over to an already synchronized secondary. Creating a copy-only backup and manually transferring it is slow and not automated. Point-in-time restore is limited to the same region and cannot restore directly to West US.

Answer

Use the geo-restore feature to restore from the geo-redundant backups to a new database in West US.

Answer

Create a copy-only backup and manually transfer it to West US.

Answer

Perform a point-in-time restore to a new database in West US.

Question 6

A company uses Azure SQL Database and wants to automatically send an email notification when an index fragmentation exceeds 30% for any database. Which solution should they implement?

Accepted Answer

Use Azure Automation runbook to query sys.dm_db_index_physical_stats and send email. Azure SQL Database does not expose SQL Server Agent, and index fragmentation is not a native Azure Monitor metric, so the correct approach is an Azure Automation runbook that connects to the database, queries sys.dm_db_index_physical_stats, evaluates fragmentation, and sends email via SendGrid, Office 365, or SMTP. This is the standard pattern for custom T-SQL-based monitoring and alerting in Azure SQL Database.

Answer

Create an Azure Monitor alert based on fragmentation

Answer

Use Elastic Database Jobs to check fragmentation and send email

Answer

Configure SQL Agent job to send email

Question 7

You have a SQL Managed Instance that hosts a critical OLTP database. You notice that the average query wait time has increased significantly over the past hour. You need to identify the top resource waits. What should you use?

Accepted Answer

sys.dm_os_wait_stats. C is correct because sys.dm_os_wait_stats is the dynamic management view that aggregates wait statistics across all sessions in the SQL Server instance, including SQL Managed Instance. It provides cumulative wait times categorized by wait type (e.g., PAGEIOLATCH, LCK_M_S), making it the appropriate tool to identify top resource waits when average query wait time increases.

Answer

sys.dm_exec_query_stats

Answer

Query Store Wait Stats in SSMS

Answer

sys.dm_db_index_usage_stats

Question 8

Your company uses Azure SQL Database and needs to protect sensitive columns (e.g., credit card numbers) from being accessed by unauthorized users. You implement Always Encrypted. However, some queries that perform pattern matching on the encrypted column are failing because the column cannot be searched. What should you do to allow pattern matching while maintaining security?

Accepted Answer

Enable Always Encrypted with secure enclaves and use a column master key that supports enclave computations.. Always Encrypted with secure enclaves allows computations, including pattern matching (LIKE, equality, comparisons), on encrypted columns by using a trusted execution environment (e.g., Intel SGX). The column master key must support enclave computations (enclave-enabled key) to permit the SQL Server engine to offload operations to the enclave. This preserves encryption at rest and in transit while enabling rich query patterns.

Answer

Implement row-level security (RLS) to filter rows based on user identity.

Answer

Change the encryption type from randomized to deterministic encryption.

Answer

Use Dynamic Data Masking (DDM) to mask the column for unauthorized users instead of encryption.

Question 9

You have an Azure SQL Database that uses automatic tuning. You notice that a forced plan regression is causing performance degradation. You need to revert to the previous plan and prevent the automatic tuning from forcing the same plan again. What should you do?

Accepted Answer

Create a plan guide for the previous plan and then disable the automatic tuning recommendation for that query.. When automatic tuning forces a plan that causes regression, the correct remediation is to create a plan guide that pins the previous (good) plan for that specific query, then disable the automatic tuning recommendation for that query so Azure SQL doesn't re-force the bad plan. This is a targeted fix that preserves automatic tuning for other queries.

Answer

Reindex the tables involved in the query.

Answer

Disable automatic tuning for the database.

Answer

Run DBCC FREEPROCCACHE to clear the plan cache.

Question 10

You have an Azure SQL Database configured with active geo-replication to a secondary region. The primary region experiences a full outage. You need to fail over with minimal data loss. What should you do?

Accepted Answer

Initiate an unplanned failover from the primary to the secondary.. Initiating an unplanned failover from the primary to the secondary is the correct action because the primary region is experiencing a full outage, and unplanned failover is designed for such disaster scenarios. It promotes the secondary database to become the new primary with minimal data loss, typically within the recovery point objective (RPO).

Answer

Delete the secondary database and create a new one in the primary region.

Answer

Initiate a planned failover from the primary to the secondary.

Answer

Create a new secondary database in the same region as the primary.