DP-300 Multiple Select — 30 Questions

Question 1

You are configuring security for an Azure SQL Managed Instance. The instance will host a critical application that requires always encrypted with secure enclaves. Which TWO actions must you take to support this feature? (Choose two.)

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Select the Intel Software Guard Extensions (Intel SGX) enclave type.. Always Encrypted with secure enclaves on Azure SQL Managed Instance requires the Intel Software Guard Extensions (Intel SGX) enclave type. Intel SGX is the only supported enclave technology for this feature on managed instances, providing a trusted execution environment that protects sensitive data in memory during cryptographic operations.

Answer

Configure the column master key to be stored in Azure Key Vault.

Answer

Enable the enclave attestation policy on the managed instance.

Answer

Enable Virtualization-Based Security (VBS) enclave type.

Question 2

Which THREE of the following are best practices for managing keys in Azure Key Vault for use with Azure SQL Database TDE?

Accepted Answer

Enable soft-delete and purge protection on the Key Vault.. Enabling soft-delete and purge protection on the Key Vault is a best practice because soft-delete retains deleted keys for a configurable retention period (default 90 days), allowing recovery if a key is accidentally deleted. Purge protection prevents permanent deletion of keys even after the soft-delete retention period expires, which is critical for TDE because if the key is permanently lost, the encrypted database becomes inaccessible. Together, these features ensure that the TDE protector key is never irrevocably lost, maintaining database recoverability and compliance.

Answer

Store the Key Vault in the same resource group as the SQL server.

Answer

Disable Key Vault auditing to reduce costs.

Question 3

Which TWO of the following are best practices for securing Azure SQL Database?

Accepted Answer

Enable firewall rules to restrict access to specific IP addresses.. Option D is correct because Azure SQL Database firewall rules (server-level and database-level) restrict inbound connections to approved IP address ranges, reducing the exposed attack surface by denying traffic from unknown sources. Option E is correct because Microsoft Entra ID (now Microsoft Entra ID) authentication centralizes identity management, supports MFA and conditional access, and eliminates the risk of weak or shared SQL logins and passwords. Option A is incorrect because Auditing records and logs activity for compliance and forensics; it does not block or prevent malicious queries. Option B is incorrect because Transparent Data Encryption encrypts data at rest (and backups) and does nothing to stop SQL injection, which is an application-layer input validation issue. Option C is incorrect because SQL authentication with complex passwords is a weaker, legacy approach compared to Entra ID authentication, and complex passwords alone do not constitute a best practice for securing Azure SQL Database.

Answer

Enable Auditing to block malicious queries.

Answer

Enable TDE to prevent SQL injection attacks.

Answer

Use SQL authentication with complex passwords.

Question 4

Which THREE components are required to set up elastic jobs in Azure SQL Database?

Accepted Answer

Target groups. Target groups (C) are required because they define the set of databases (or elastic pools) against which an elastic job will execute. Without target groups, the job agent has no scope of execution. The elastic job agent (D) orchestrates job scheduling and execution, and job credentials (E) are needed to authenticate to the target databases for running T-SQL scripts.

Answer

Azure Automation account

Answer

SQL Server Agent

Question 5

You are tasked with automating the backups of multiple Azure SQL Databases to ensure long-term retention. Which ONE Azure service can be used to achieve automated backups with retention beyond the default 7-35 days?

Accepted Answer

Azure SQL Database long-term retention (LTR) backup policy. Only Azure SQL Database long-term retention (LTR) backup policy provides automated long-term backup retention for Azure SQL Database (PaaS), allowing retention beyond the default 7-35 days up to 10 years. Option C (Azure Backup for SQL Server in Azure VMs) is for SQL Server on Azure VMs (IaaS), not for Azure SQL Database, so it does not apply. The other options (A, B, D) do not provide automated backup with long-term retention for Azure SQL Database.

Answer

Azure Site Recovery

Answer

Azure Blob Storage snapshots

Answer

Azure Backup for SQL Server in Azure VMs

Answer

Azure Storage lifecycle management

Question 6

You need to protect Azure SQL Database from SQL injection attacks. Which THREE of the following measures should you implement?

Accepted Answer

Enable Microsoft Defender for SQL to detect and alert on SQL injection.. Option A is correct because Microsoft Defender for SQL provides advanced threat protection that specifically detects anomalous and potentially harmful activities such as SQL injection attempts against Azure SQL Database, generating alerts and recommendations. Option B is correct because parameterized queries and stored procedures ensure that user input is treated as data rather than executable SQL code, which is the fundamental application-level defense against SQL injection. Option D is correct because an Azure Web Application Firewall (WAF), for example on Application Gateway or Front Door, inspects HTTP/HTTPS traffic and blocks common injection patterns before they reach the application and database. Option C is not correct because dynamic data masking only obscures sensitive data in query results for unauthorized users; it does not prevent or detect SQL injection. Option E is not correct because Transparent Data Encryption protects data at rest by encrypting database files, but it does not stop SQL injection, which exploits the application's query logic rather than the storage layer.

Answer

Implement dynamic data masking to hide sensitive data from unauthorized users.

Answer

Enable Transparent Data Encryption (TDE) to encrypt the database at rest.

Question 7

Your company is migrating several on-premises SQL Server databases to Azure. The databases range from 50 GB to 2 TB and have varying performance requirements. You need to decide which Azure SQL deployment options to use. The requirements include: 
- Minimal application changes. 
- Support for SQL Server Agent jobs. 
- Ability to scale storage independently from compute. 
- Native support for cross-database queries. 
Which TWO options meet these requirements? (Choose two.)

Accepted Answer

SQL Server on Azure Virtual Machine. SQL Server on Azure Virtual Machine (D) and Azure SQL Managed Instance (E) provide full SQL Server engine compatibility, native SQL Server Agent, cross-database queries, independent storage scaling, and minimal application changes. Azure SQL Database elastic pool (B) does not support SQL Server Agent natively (Azure Elastic Jobs is not SQL Server Agent) and its cross-database query support via elastic query is not as seamless as native; thus it does not meet all requirements. Option A (single database) lacks SQL Server Agent and cross-database queries. Option C (Azure Synapse) is not designed for OLTP and lacks SQL Server Agent and cross-database query support. Therefore, the two correct options are D and E.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database elastic pool

Answer

Azure Synapse Analytics (dedicated SQL pool)

Question 8

Which TWO of the following are supported high availability features in Azure SQL Managed Instance?

Accepted Answer

Zone-redundant deployment for Business Critical tier. Option A is correct because Azure SQL Managed Instance in the Business Critical service tier supports zone-redundant deployment, which places the primary and secondary replicas across different availability zones to protect against datacenter-level failures. Option C is correct because SQL Managed Instance includes a built-in Always On Availability Groups mechanism that automatically provisions and manages replicas for high availability, with the Business Critical tier using local SSD and multiple secondary replicas. Database mirroring (B) is a deprecated SQL Server feature and is not offered as a supported HA option in Azure SQL Managed Instance. Log shipping to a secondary instance (D) is not a built-in HA feature of SQL Managed Instance, though it can be configured manually for disaster recovery scenarios. Windows Server Failover Clustering (E) is an underlying infrastructure concept used by SQL Server on-premises and Azure VMs, but it is not exposed or supported as a user-configurable HA feature in Azure SQL Managed Instance.

Answer

Database mirroring

Answer

Log shipping to a secondary instance

Answer

Windows Server Failover Clustering

Question 9

Which TWO of the following are benefits of using Azure SQL Database failover groups compared to active geo-replication alone?

Accepted Answer

Failover of multiple databases in a single group.. Failover groups extend active geo-replication by allowing you to manage failover for a group of databases as a single unit. This simplifies the failover process when you have multiple databases that must be failed over together to maintain application consistency. Option B is correct because failover groups support the coordinated failover of multiple databases, which active geo-replication alone does not.

Answer

Support for manual failover.

Answer

Readable secondary replicas for read-scale workloads.

Answer

Synchronous data replication to the secondary.

Question 10

Your organization uses Azure SQL Managed Instance and needs to implement a defense-in-depth strategy. Which THREE security controls should you implement? (Choose three.)

Accepted Answer

Enable advanced threat protection using Microsoft Defender for Cloud.. Option A is correct because enabling advanced threat protection via Microsoft Defender for Cloud provides detection and alerting for anomalous activities and potential vulnerabilities on Azure SQL Managed Instance, which is a core detective control in a defense-in-depth strategy. Option B is correct because server-level auditing captures database events and writes them to an audit log, providing the monitoring and traceability required for a layered security approach. Option D is correct because configuring network security groups (NSGs) on the subnet restricts inbound traffic to the managed instance, enforcing network-level segmentation and reducing the attack surface, which is a fundamental preventive control. Option C is not a security control; columnstore indexes are a performance optimization for analytical workloads and do not contribute to defense-in-depth. Option E is not the best fit because application roles manage permissions within a database but do not address the broader layered security controls expected in a defense-in-depth strategy for Azure SQL Managed Instance.

Answer

Create columnstore indexes on large tables to improve query performance.

Answer

Create application roles in each database to manage permissions.

Question 11

Which TWO are valid methods to connect to an Azure SQL Database without exposing a public endpoint?

Accepted Answer

Use a service endpoint. A service endpoint extends your virtual network private address space and the identity of your VNet to Azure SQL Database over a direct connection on the Azure backbone network. This allows you to secure your logical SQL server to accept traffic only from a specific subnet, eliminating the need for a public endpoint while still using the public endpoint's DNS name internally.

Answer

Use Always Encrypted

Answer

Use a public endpoint with a firewall rule

Answer

Use a site-to-site VPN gateway and connect to public endpoint

Question 12

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 13

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 14

Which TWO of the following are native options to automate index maintenance on Azure SQL Database? (Select exactly two.)

Accepted Answer

Create Elastic Database Jobs that run index maintenance T-SQL scripts.. Option A is correct because Elastic Database Jobs are a native Azure SQL Database feature that lets you define and schedule T-SQL scripts (such as ALTER INDEX ... REBUILD/REORGANIZE) across one or many databases without needing an external orchestrator. Option C is correct because Azure SQL Database's built-in automatic tuning can automatically create and drop indexes based on workload analysis, which is a native, server-side index maintenance capability. Option B is not native to Azure SQL Database: Azure Automation is a separate Azure service, and its PowerShell runbooks must connect externally to run T-SQL. Option D is wrong because SQL Server Agent is not available in Azure SQL Database (it exists in Azure SQL Managed Instance and SQL Server on Azure VMs). Option E is wrong because Azure Data Factory is a data integration/ETL service, not an index maintenance mechanism.

Answer

Use Azure Automation PowerShell runbooks to invoke index rebuilds.

Answer

Schedule SQL Agent jobs with ALTER INDEX statements.

Answer

Use Azure Data Factory to copy data and rebuild indexes.

Question 15

You need to automate the monitoring of Azure SQL Database performance and receive alerts when certain conditions are met. Which TWO Azure services can be used together to achieve this?

Accepted Answer

Log Analytics Workspace. Log Analytics Workspace (B) is correct because it is the service that stores and queries the diagnostic and metric telemetry collected from Azure SQL Database, enabling you to write Kusto Query Language (KQL) queries that define the performance conditions to monitor. Azure Monitor Alerts (C) is correct because it evaluates those log/metric queries against defined thresholds and triggers notifications (for example, email, SMS, or action groups) when the conditions are met. Together, Log Analytics Workspace provides the data and query layer while Azure Monitor Alerts provides the rule evaluation and notification layer, forming the standard automated monitoring and alerting pipeline for Azure SQL Database. Azure Sentinel (A) is a SIEM/SOAR tool focused on security threat detection, not general SQL performance monitoring. Application Insights (D) targets application-level telemetry (APM) for web apps and services, not Azure SQL Database performance metrics. Azure Advisor (E) only provides best-practice recommendations and does not perform real-time condition-based alerting.

Answer

Azure Sentinel

Answer

Application Insights

Answer

Azure Advisor

Question 16

Which THREE are best practices for securing Azure SQL Database? (Choose three.)

Accepted Answer

Use Microsoft Entra ID authentication instead of SQL authentication.. Option A is correct because Microsoft Entra ID authentication centralizes identity management, supports MFA and conditional access, and eliminates the risk of weak or shared SQL logins and passwords. Option B is correct because Azure SQL Database firewall rules (server-level and database-level) limit inbound connections to specific, known IP address ranges, reducing the exposed attack surface from the public internet. Option C is correct because Transparent Data Encryption (TDE) encrypts data at rest, including backups and log files, protecting against unauthorized access to physical media or backup theft. Option D is not a best practice because enabling public network access broadens exposure; private endpoints or restricted firewall rules are preferred. Option E is not a best practice because granting db_owner to developers violates least privilege and gives excessive control over the database.

Answer

Enable public network access to allow flexible connectivity.

Answer

Grant db_owner role to developers for ease of management.

Question 17

You are configuring automated backups for an Azure SQL Database. Which TWO settings can you configure?

Accepted Answer

Backup retention period (in days).. The backup retention period (in days) is a configurable setting for Azure SQL Database automated backups. You can set the retention period for point-in-time restore (PITR) backups between 1 and 35 days, and for long-term retention (LTR) backups up to 10 years. This directly controls how far back you can restore your database.

Answer

Backup compression.

Answer

Backup frequency (full, differential, log).

Answer

Point-in-time restore interval.

Question 18

Which TWO methods can be used to automate index maintenance in Azure SQL Database?

Accepted Answer

Azure Automation runbooks executing T-SQL scripts. Azure Automation runbooks can execute T-SQL scripts against Azure SQL Database using the Invoke-SqlCmd cmdlet or similar, enabling scheduled index maintenance tasks such as rebuilding or reorganizing indexes. This method is fully supported in Azure SQL Database, which lacks SQL Server Agent, and allows for flexible, cloud-native automation.

Answer

SQL Server Agent jobs

Answer

Azure Data Factory pipelines

Answer

Automatic tuning

Question 19

Which TWO metrics in Azure SQL Database indicate that the database might need to be scaled up?

Accepted Answer

Log write percent consistently above 90%. High DTU/CPU consumption (E) and high log write percent (C) both indicate that the database is approaching or hitting resource limits, suggesting a need to scale up. Options A, B, and D are incorrect because consistently low metrics (data IO, session percent, memory) indicate underutilization, not a need to scale up.

Answer

Data IO percentage consistently below 20%

Answer

Session percent consistently below 10%

Answer

Memory consumption consistently below 30%

Question 20

You are monitoring an Azure SQL Database. You need to identify which built-in tools can provide real-time performance data without additional cost. Which THREE should you select?

Accepted Answer

Azure Monitor Metrics. Azure Monitor Metrics is a built-in, no-cost feature that collects and stores platform metrics from Azure SQL Database at near-real-time intervals (typically every minute). It provides performance counters such as DTU/CPU usage, data IO, and log write percentages without requiring additional configuration or licensing, making it a correct choice for real-time performance data.

Answer

Performance Insights

Answer

SQL Server Profiler