DP-300 Practice Test 24 — 15 Questions

Question 1

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. You need to ensure that the database is protected by Microsoft Defender for Cloud (formerly Azure Security Center) with advanced threat protection. What should you enable?

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

Accepted Answer

Enable Microsoft Defender for Cloud on the subscription or resource. Microsoft Defender for Cloud provides advanced threat protection for Azure SQL Managed Instance at the subscription or resource level. Enabling it on the subscription or the specific resource activates threat detection capabilities, including alerts for SQL injection, brute-force attacks, and anomalous access patterns, without requiring additional services.

Answer

Deploy Microsoft Sentinel and connect the SQL Managed Instance

Answer

Configure Microsoft Purview Data Map

Answer

Enable Azure SQL Database auditing

Question 2

You are a database administrator for a healthcare company. You have an Azure SQL Database that stores patient records. The database is currently accessible from the public internet via firewall rules. You need to implement a secure environment that meets the following requirements:
- All traffic to the database must be private and not traverse the internet.
- The database must be accessible from an Azure Virtual Machine in a specific VNet.
- The solution must minimize management overhead and cost.
- You need to ensure that the database can be failed over to a secondary region in case of an outage.

What should you do?

Accepted Answer

Create a private endpoint in the VNet, disable public network access, and configure a failover group with a private endpoint in the secondary region.. Creating a private endpoint in the VNet ensures that traffic to the Azure SQL Database uses a private IP address within the VNet, never traversing the internet. Disabling public network access enforces this. A failover group with a private endpoint in the secondary region provides cross-region failover while maintaining private connectivity. This solution minimizes management overhead and cost by using managed services.

Answer

Restrict firewall rules to only the VM's public IP and enable active geo-replication.

Answer

Configure a point-to-site VPN from the VM to the database and set up geo-replication.

Answer

Create a VNet service endpoint and a failover group. Keep public access enabled for failover.

Question 3

Your company has an Azure SQL Database that stores sensitive customer data. You need to ensure that data is encrypted at rest and in transit. The database is currently using Transparent Data Encryption (TDE) with service-managed keys. Compliance requirements now mandate that you use customer-managed keys stored in Azure Key Vault. Additionally, all connections must use encrypted connections. What should you do?

Accepted Answer

Configure the Azure SQL Server to use a customer-managed key from Azure Key Vault for TDE and set 'Encrypted connection' to 'Required' on the server.. It directly addresses both requirements: using a customer-managed key from Azure Key Vault for TDE (which replaces the service-managed key) and enforcing encrypted connections by setting 'Encrypted connection' to 'Required' on the Azure SQL Server. This configuration ensures data at rest is encrypted with a key you control, and all client connections must use TLS encryption, meeting compliance mandates without requiring a new database or data migration.

Answer

Create a new Azure SQL Database with TDE enabled using a customer-managed key from Key Vault. Migrate data using SQL Server Management Studio (SSMS) with 'Encrypt connection' enabled.

Answer

Enable Transparent Data Encryption (TDE) with service-managed keys and set 'Minimum TLS version' to 1.2.

Answer

Implement Always Encrypted with keys stored in Azure Key Vault and set 'Encrypted connection' to 'Required' on the server.

Question 4

You manage an Azure SQL Database that uses the General Purpose tier. The database has a failover group with a secondary in a paired region. During a regional outage, you initiate a forced failover. After the outage is resolved, you want to bring the original primary region back online without data loss. What should you do?

Accepted Answer

Wait for data synchronization, then initiate a planned failover.. After a forced failover, the original primary becomes the secondary and enters a state where it may have diverged data. To restore it as primary without data loss, you must wait for geo-replication to catch up and re-synchronize, then perform a planned (graceful) failover. A planned failover ensures no data loss by confirming the secondary is fully synchronized before switching roles.

Answer

Initiate a forced failover from the new primary to the old primary.

Answer

Perform a forced failover again to switch back.

Answer

Delete the failover group and recreate it with the original primary as primary.

Question 5

You need to configure Azure SQL Database to automatically adjust indexing based on workload patterns. Which feature should you enable?

Accepted Answer

Automatic tuning. Automatic tuning in Azure SQL Database continuously analyzes query execution plans and workload patterns, then automatically creates, drops, or rebuilds indexes to improve performance. It uses built-in intelligence to recommend and apply index changes without manual intervention, making it the correct feature for automatically adjusting indexing based on workload patterns.

Answer

Azure Advisor

Answer

Intelligent Insights

Answer

Query Store

Question 6

Your Azure SQL Database is accessed by multiple applications. You need to ensure that all connections use Transport Layer Security (TLS) 1.2 or higher. Which TWO configurations should you verify or enable?

Accepted Answer

Configure client applications to use TLS 1.2 in their connection strings.. Option A is correct because client-side enforcement is required: applications must be configured to negotiate TLS 1.2 or higher (for example, via the connection string or the underlying driver/provider settings), otherwise a client may still attempt to negotiate an older protocol version. Option D is correct because Azure SQL Database (and Azure SQL Managed Instance) exposes a server-level 'minimalTlsVersion' property that, when set to '1.2', rejects connections that attempt to negotiate TLS versions lower than 1.2. Together, A and D ensure both the client and the server enforce TLS 1.2 or higher. Option B is not appropriate because a network security group rule cannot inspect or enforce TLS protocol versions; NSGs filter by IP, port, and protocol (TCP/UDP), not by TLS version. Option C is not relevant because 'DenyPublicNetworkAccess' only controls whether public endpoints are reachable, not the TLS version used. Option E is not applicable because 'ForceEncryption' is a SQL Server on-premises/VM configuration option (set via sp_configure or the protocol properties), not an Azure SQL Database server property.

Answer

Create a network security group rule to block non-TLS traffic.

Answer

Set 'DenyPublicNetworkAccess' to 'Yes' on the server.

Answer

Enable 'ForceEncryption' on the SQL Server instance.

Question 7

Which TWO are valid ways to secure access to an Azure SQL Database?

Accepted Answer

Configure a Virtual Network service endpoint. Option B is correct because a Virtual Network service endpoint extends the Azure VNet identity to Azure SQL Database, allowing you to restrict the logical SQL server's firewall so that only subnets in that VNet can reach the database over the Azure backbone, removing public internet exposure. Option C is correct because a Private Endpoint creates a private IP address for the Azure SQL Database inside your VNet via Azure Private Link, so traffic stays on the Microsoft private network and the public endpoint can be disabled entirely. Option A is not valid because IPsec VPN secures connectivity to a VNet or on-premises network but does not by itself control or secure access to Azure SQL Database's firewall/endpoint. Option D is not valid because Azure SQL Database cannot be domain-joined to an on-premises Active Directory; it supports Microsoft Entra ID authentication (and AD DS via Microsoft Entra Domain Services), not a direct domain join. Option E is not valid because disabling all firewall rules would expose the database publicly and relying only on authentication does not secure network access, which is the point of the question.

Answer

Use IPsec VPN from on-premises

Answer

Join the database to an on-premises Active Directory domain

Answer

Disable all firewall rules and rely only on authentication

Question 8

You need to ensure that all queries executed against an Azure SQL Database are audited and logged to a Log Analytics workspace for security analysis. Which feature should you enable?

Accepted Answer

Azure SQL Auditing. Azure SQL Auditing is the feature specifically designed to track database events and write them to an audit log destination, including a Log Analytics workspace. This enables security analysis by capturing all queries executed against the database, which can then be queried and analyzed within the workspace. The other options focus on vulnerability scanning, threat detection, or security posture management, not on recording query execution logs.

Answer

SQL Vulnerability Assessment

Answer

Advanced Threat Protection

Answer

Microsoft Defender for Cloud

Question 9

Your Azure SQL Database is configured with a failover group between two regions. The primary database experiences a catastrophic failure that prevents any connectivity. You need to initiate a failover to the secondary region. However, the failover group status shows 'Primary is down'. What should you do?

Accepted Answer

Run a forced failover accepting potential data loss.. A forced failover (also called an unplanned failover) is used when the primary database is completely unavailable, and you accept potential data loss. Option A is incorrect because a planned failover requires both the primary and secondary to be online to ensure zero data loss. Option B is incorrect because waiting for the primary to come back online is not an appropriate action when immediate failover is required due to catastrophic failure. Option C is incorrect because you cannot remove the primary database from a failover group when it is down; the failover group must be failed over as a whole.

Answer

Run a planned failover to ensure zero data loss.

Answer

Wait for the primary to come back online and then failover.

Answer

Remove the primary database from the failover group and then failover.

Question 10

Drag and drop the steps to configure an Azure SQL Database elastic pool in the correct order.

Accepted Answer

Create the elastic pool, then add databases to the pool, then configure per-database limits, then monitor pool performance, then scale the pool as needed.. First create the pool, then add databases, configure per-database limits, monitor, and scale.

Answer

Add databases to an existing pool first, then create the elastic pool, then configure per-database limits, then monitor, then scale.

Answer

Create the elastic pool, then configure per-database limits, then add databases, then monitor, then scale.

Answer

Create the elastic pool, then add databases, then monitor, then configure per-database limits, then scale.

Question 11

You are reviewing an Azure SQL Database server's vulnerability assessment settings. The exhibit shows the current configuration. A recent security audit requires that vulnerability assessment scans be enabled and that results be retained for at least 90 days. What should you do?

Accepted Answer

Change state to Enabled and set retentionDays to 90.. The requirement is to enable vulnerability assessment scans and retain results for at least 90 days. Option C correctly sets the state to Enabled (turning on the scans) and sets retentionDays to 90 (meeting the retention requirement). The other options either leave the state disabled or address unrelated settings like email notifications or alert suppression.

Answer

Add additional email addresses to ensure notification.

Answer

Change retentionDays to 90 and keep the state as Disabled.

Answer

Remove the disabledAlerts entries to enable all alerts.

Question 12

You are troubleshooting a performance degradation on an Azure SQL Database. You notice that the database is hitting the maximum DTU limit frequently. Which action should you take first to reduce DTU consumption?

Accepted Answer

Use Query Performance Insight to identify and optimize top resource-consuming queries. The first step to reduce DTU consumption is to identify the queries causing the high resource usage using Query Performance Insight. This tool provides detailed metrics on top resource-consuming queries, enabling targeted optimization. Scaling up or rebuilding indexes without identifying the root cause may not address the underlying inefficiency and could increase costs unnecessarily.

Answer

Increase the log rate limit

Answer

Scale up the database to a higher service tier

Answer

Rebuild all indexes in the database

Question 13

You are optimizing an Azure SQL Database that runs a reporting workload. The database is in the General Purpose tier. You notice that many queries are performing table scans on large tables. Which TWO actions would most likely improve query performance without increasing costs?

Accepted Answer

Update statistics on the tables.. Option A is correct because stale or missing statistics prevent the query optimizer from accurately estimating cardinality, often forcing scans instead of seeks; running UPDATE STATISTICS (or relying on auto-update statistics) gives the optimizer better row-count estimates and can produce more efficient plans at no extra cost. Option E is correct because creating nonclustered indexes on the columns referenced in WHERE clauses gives the optimizer a covering or seekable access path, converting full table scans on large tables into index seeks or scans of a much smaller structure, which directly improves reporting query performance without changing the service tier. Option B is not appropriate because upgrading to Business Critical increases cost, violating the 'without increasing costs' constraint. Option C is not appropriate because raising MAXDOP to 8 changes parallelism for the whole workload and does not address the root cause of scans, and it can even hurt performance or increase resource usage. Option D is not appropriate because enabling automatic tuning may create or drop indexes and force plans, but it is not a guaranteed, immediate fix for the observed scans and does not by itself ensure the specific WHERE-clause columns are indexed.

Answer

Upgrade to Business Critical tier.

Answer

Increase MAXDOP to 8.

Answer

Enable automatic tuning.

Question 14

You are a database administrator for a large financial services company. You need to ensure that all queries that read sensitive customer data use an optimized execution plan. What feature should you enable to automatically identify and fix regressed query plans?

Accepted Answer

Automatic Tuning. Automatic Tuning in Azure SQL Database and SQL Managed Instance automatically identifies and fixes plan regressions by forcing the last known good plan, and can also create/drop indexes. It directly addresses the requirement to automatically identify and fix regressed query plans without manual intervention.

Answer

Query Store

Answer

Intelligent Insights

Answer

Database Advisor for SQL Database

Question 15

Your organization uses Azure SQL Database and wants to automatically detect and alert on potential SQL injection attacks. Which Azure service should you enable?

Accepted Answer

Microsoft Defender for SQL. Microsoft Defender for SQL (option C) is the correct answer because it provides advanced SQL security capabilities, including a dedicated SQL injection detection engine that analyzes database activity patterns and alerts on suspicious queries. Unlike other options, Defender for SQL specifically monitors for SQL injection attempts by evaluating query anomalies and known attack signatures, making it the appropriate service for automatic detection and alerting.

Answer

Azure SQL Database Vulnerability Assessment

Answer

Azure SQL Auditing

Answer

Microsoft Sentinel