DP-300 Practice Test 23 — 15 Questions

Question 1

Which TWO configurations can help improve the performance of an Azure SQL Database experiencing high `WRITELOG` waits?

Accepted Answer

Use in-memory OLTP to reduce log writes.. Option B is correct because In-Memory OLTP (memory-optimized tables and natively compiled stored procedures) reduces WRITELOG waits by minimizing transaction log traffic: memory-optimized tables use a separate, more efficient checkpoint mechanism, and for SCHEMA_ONLY durability and natively compiled procedures the log writes are drastically reduced, directly lowering log I/O pressure. Option C is correct because WRITELOG waits occur when the transaction log becomes the bottleneck; moving to a higher service tier (e.g., from S3 to S6, or to Premium/Business Critical) increases the provisioned log throughput and IOPS, allowing commits to flush to the log faster and reducing wait time. Option A is not correct because Transparent Data Encryption encrypts data at rest and adds CPU overhead rather than reducing log writes. Option D is not correct because Query Store captures query execution statistics for performance troubleshooting; it does not reduce WRITELOG waits and can add minor overhead. Option E is not correct because more frequent backups increase log activity and I/O, potentially worsening rather than improving WRITELOG waits.

Answer

Enable Transparent Data Encryption (TDE).

Answer

Enable Query Store.

Answer

Increase the frequency of database backups.

Question 2

You are the database administrator for a company that uses Azure SQL Database. You need to implement a security solution that automatically detects and alerts on suspicious activities, such as SQL injection attempts. Which feature should you enable?

Accepted Answer

Microsoft Defender for SQL. Microsoft Defender for SQL (formerly Azure Security Center's Advanced Threat Protection) is the correct feature because it continuously monitors database traffic for anomalous activities, including SQL injection, brute-force attacks, and privilege escalation. When suspicious behavior is detected, it generates a security alert that can be viewed in the Azure portal or integrated with Azure Sentinel for automated response. This is the only option among the choices that provides proactive threat detection and alerting for suspicious activities.

Answer

Azure SQL Auditing

Answer

SQL Vulnerability Assessment

Answer

Transparent Data Encryption (TDE)

Question 3

Which TWO of the following are required steps to configure Azure SQL Database to use a customer-managed key (CMK) for Transparent Data Encryption (TDE) with Azure Key Vault? (Choose two.)

Accepted Answer

Set the TDE protector to the Key Vault key via the Azure portal or T-SQL. Setting the TDE protector to the Key Vault key is the step that actually enables customer-managed key (CMK) encryption for Azure SQL Database. This can be done via the Azure portal, PowerShell, Azure CLI, or T-SQL (using ALTER DATABASE SCOPED CONFIGURATION SET TDE_PROTECTOR). Without this step, the database continues to use a service-managed key.

Answer

Create a user-assigned managed identity for the SQL Database server

Answer

Store the encryption key in the SQL Database server's hardware security module (HSM)

Answer

Ensure the SQL Database server is inside the same virtual network as the Key Vault

Question 4

A company has an Azure SQL Database that is experiencing performance degradation during peak hours. The database is configured with the Standard tier (S2). Which action should you recommend to improve performance without changing the application code?

Accepted Answer

Scale up the database to a higher service objective (e.g., S3).. Scaling up the Azure SQL Database from S2 to a higher service objective (e.g., S3) increases the allocated DTUs/vCores, memory, and IOPS, directly addressing resource saturation during peak hours without requiring any application changes. This is the fastest, least invasive remediation when the workload is CPU/IO bound and the tier is the bottleneck. It preserves connection strings, schema, and code, making it the correct first recommendation.

Answer

Enable Query Store and run the Performance Dashboard.

Answer

Enable read scale-out to offload read queries.

Answer

Create nonclustered indexes on all tables.

Question 5

You manage an Azure SQL Database that has automatic tuning enabled. You receive an alert that the database is experiencing plan regression. The automatic tuning has forced a plan, but performance is still poor. What should you do first?

Accepted Answer

Review the Query Store to identify the root cause of regression.. When automatic tuning has forced a plan but performance remains poor, the first step is to investigate the root cause using Query Store, which captures query plans, runtime statistics, and regressions. Query Store provides the historical data needed to understand why the forced plan is not optimal and whether the regression is due to parameter sniffing, statistics, or schema changes. Only after diagnosis should you consider reverting or disabling automatic tuning.

Answer

Disable automatic tuning and create a plan guide.

Answer

Manually revert to the previous plan using Query Store.

Answer

Scale up the database to reduce resource pressure.

Question 6

You have an Azure SQL Database that stores sensitive data. You need to automatically classify and apply sensitivity labels to new columns as they are added. What should you use?

Accepted Answer

Microsoft Purview Information Protection. Microsoft Purview Information Protection enables automatic classification and labeling of sensitive data in Azure SQL Database through its data classification capabilities. It can be configured to automatically detect and apply sensitivity labels to new columns based on built-in or custom rules. Option B (Azure Policy) can enforce compliance but does not perform automatic classification of data within the database. Option C (Dynamic Data Masking) obscures sensitive data but does not classify or label it. Option D (Azure Automation with PowerShell) requires custom scripting and does not provide built-in automatic classification integration for new columns.

Answer

Azure Policy with custom policy definition

Answer

Dynamic Data Masking

Answer

Azure Automation with PowerShell script to run sp_addsensitivityclassification

Question 7

You are troubleshooting a performance issue on an Azure SQL Database. The database is experiencing high PAGELATCH_EX waits. Which TWO measures can help reduce these waits?

Accepted Answer

Partition the table to distribute inserts. Option C is correct because PAGELATCH_EX waits are commonly caused by last-page insert contention on monotonically increasing keys (e.g., IDENTITY), and partitioning the table across multiple partitions/files spreads those inserts across different pages, eliminating the hot last-page latch. Option D is correct because the OPTIMIZE_FOR_SEQUENTIAL_KEY index option, introduced in SQL Server 2019/Azure SQL Database, specifically mitigates last-page insert PAGELATCH_EX contention by managing the insert into the index's last page more efficiently. Option A is not applicable because hash or round-robin distribution is a dedicated SQL pool (formerly SQL DW) table design concept, not a remedy for PAGELATCH_EX in Azure SQL Database. Option B is wrong because increasing MAXDOP does not reduce page latch contention and can even worsen it by increasing concurrent insert pressure. Option E is wrong because enabling snapshot isolation addresses blocking/locking (LCK waits) and read-write contention, not PAGELATCH_EX waits on data pages.

Answer

Use a hash distribution or round-robin distribution in a table design

Answer

Increase MAXDOP for the queries

Answer

Enable snapshot isolation level

Question 8

You are designing a data platform for a global SaaS company. The application requires a relational database that can handle up to 50 TB of data and supports high-frequency inserts. The database must be able to scale compute independently from storage and provide fast restores (within minutes) for large databases. Which Azure SQL offering should you choose?

Accepted Answer

Azure SQL Database Hyperscale tier. The Hyperscale tier of Azure SQL Database is designed for workloads up to 100 TB, decouples compute from storage (allowing independent scaling), and uses a log-based architecture with page servers to enable fast restores (typically within minutes, regardless of database size). This directly matches the requirements for 50 TB data, high-frequency inserts, independent compute/storage scaling, and rapid restore times.

Answer

Azure SQL Database Business Critical tier

Answer

Azure SQL Database Standard tier

Answer

Azure SQL Managed Instance Business Critical

Question 9

Refer to the exhibit. You are reviewing an ARM template for deploying an Azure SQL Database. The template specifies a point-in-time restore from a source database. The source database is configured with geo-redundant backup storage. You need to ensure that the restored database can be used for disaster recovery in a different region. What is missing from the template to achieve this?

Accepted Answer

Set 'createMode' to 'GeoRestore' and specify a target server in the desired region.. To restore a geo-redundant backup to a different region, the ARM template must use createMode 'GeoRestore' and point to a target logical server in the desired region. A standard PointInTimeRestore restores to the same region and server, so it cannot satisfy the cross-region DR requirement. GeoRestore is the specific createMode that instructs Azure SQL to pull the geo-replicated backup and materialize the database on the specified target server in the alternate region.

Answer

Add a 'location' property to the database resource.

Answer

Set 'zoneRedundant' to true.

Answer

Change 'requestedBackupStorageRedundancy' to 'GeoZone'.

Question 10

You need to monitor Azure SQL Database performance over time and receive alerts when CPU usage exceeds 80%. Which Azure service should you use?

Accepted Answer

Azure Monitor Alerts. Azure Monitor Alerts is the correct service because it allows you to create metric-based alert rules that trigger when the CPU percentage of an Azure SQL Database exceeds a defined threshold (e.g., 80%). It continuously monitors performance metrics over time and sends notifications (e.g., email, SMS, or webhook) when the condition is met, fulfilling the requirement for both monitoring and alerting.

Answer

Automatic tuning

Answer

Query Performance Insight

Answer

SQL Assessment

Question 11

You are a database administrator for a retail company that uses Azure SQL Database with the Serverless compute tier. The database experiences unpredictable idle periods, and you want to minimize costs by automatically pausing the database when it is idle for more than 60 minutes and resuming it when a connection is attempted. However, you also need to ensure that a critical reporting job that runs every hour can connect even if the database is paused. What should you do?

Accepted Answer

Enable the serverless auto-pause feature with a delay of 60 minutes. No additional action is needed; the reporting job will automatically resume the database upon connection.. Azure SQL Database serverless auto-pause suspends the database after a configurable idle period (minimum 15 minutes, maximum 10,080 minutes) and automatically resumes it on the next connection attempt, with only a short resume latency billed at compute rates. Setting the auto-pause delay to 60 minutes meets the cost goal, and because the hourly reporting job's connection itself triggers the resume, no additional mechanism is required. This is the native, supported behavior of the serverless tier and requires no external orchestration.

Answer

Disable auto-pause for the database and use Azure Automation to scale down the database during idle periods.

Answer

Use Elastic Database Jobs to keep the database active by running a lightweight query every 59 minutes.

Answer

Set the auto-pause delay to 0 minutes to minimize costs, and create an Azure Automation runbook to keep the database active during the reporting job.

Question 12

You deploy a new Azure SQL Database and need to ensure that all queries are logged for performance analysis. Which configuration should you enable?

Accepted Answer

Query Store. Query Store captures a history of query execution plans, runtime statistics, and wait statistics, enabling detailed performance analysis and troubleshooting. It is the correct choice because it is specifically designed to log query-level performance data for Azure SQL Database without requiring external storage or additional configuration.

Answer

Data classification

Answer

Server-level audit

Answer

Diagnostic settings for SQLInsights

Question 13

Refer to the exhibit. After a brief outage, the availability group recovered. However, SQL2 shows NOT_HEALTHY and DISCONNECTED. What is the most likely cause?

Accepted Answer

The network connection between SQL1 and SQL2 is still down after the outage.. The most likely cause is that the network connection between SQL1 and SQL2 is still down after the outage. In an Always On availability group, a secondary replica showing NOT_HEALTHY and DISCONNECTED typically indicates a connectivity issue between the primary and secondary. Since the outage occurred, it's plausible that network connectivity was not fully restored.

Answer

The automatic failover policy requires a quorum that is not met.

Answer

The listener is not configured correctly for SQL2.

Answer

The secondary replica SQL2 has an incompatible database version.

Question 14

You have an Azure SQL Database in the Hyperscale service tier. You need to ensure that the database remains available during a single Azure zone failure. The solution must not require manual intervention. What should you configure?

Accepted Answer

Enable zone redundancy on the Hyperscale database.. Hyperscale databases support zone redundancy, which automatically places database replicas in different availability zones, ensuring availability during a single zone failure without manual intervention. Option A is incorrect because failover groups with a secondary in a different zone are used for SQL Managed Instance or other service tiers, not for Hyperscale zone redundancy. Option C is incorrect because active geo-replication is for disaster recovery across regions, not for zone-level failures. Option D is incorrect because auto-failover groups are for SQL Managed Instance or other tiers, and zone redundancy is the native solution for Hyperscale.

Answer

Create a failover group with a secondary in a different zone.

Answer

Configure active geo-replication to a secondary server in a different zone.

Answer

Configure an auto-failover group with a secondary in a different zone.

Question 15

You are designing a secure environment for Azure SQL Database. Which TWO of the following are recommended practices for network security?

Accepted Answer

Use Azure Private Link to connect to the database from a virtual network.. Option C is correct because Azure Private Link (Private Endpoint) provides a private IP address for the Azure SQL logical server inside your VNet, so traffic between the VNet and the database travels over the Microsoft backbone and never exposes the database to the public internet. Option D is correct because disabling public network access on the SQL server ensures the database accepts connections only through approved private paths (such as Private Endpoints) or explicitly permitted exceptions, eliminating the broad public endpoint attack surface. Option A is not recommended because 'Allow Azure services and resources to access this server' creates a firewall exception that permits traffic from any Azure service, which is overly permissive and not a targeted network security control. Option B is not recommended because VNet service endpoints still route traffic to the database's public endpoint and are generally considered less secure than Private Link, so cost should not drive that choice for a secure design. Option E is not recommended because allowing an entire organization IP range is a broad, IP-based rule that is weaker than private connectivity and can be bypassed if those addresses are compromised or spoofed.

Answer

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

Answer

Use VNet service endpoints instead of Private Link to reduce costs.

Answer

Add firewall rules that allow all IP addresses from your organization's IP range.