DP-300 Practice Test 41 — 15 Questions

Question 1

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 2

You need to automatically send a weekly email report summarizing the performance metrics (DTU consumption, storage used) of all Azure SQL Databases in a subscription. Which Azure service should you use?

Accepted Answer

Azure Logic App with a recurrence trigger, using Azure Monitor connector to get metrics and Office 365 Outlook connector to send email.. Azure Logic Apps provide a no-code/low-code solution with a recurrence trigger to run weekly, the Azure Monitor connector to retrieve metrics (DTU consumption, storage) from Azure SQL Databases, and the Office 365 Outlook connector to send the email report. Option A is less ideal because Azure Automation Runbooks require custom scripting for email and are more complex. Option C is incorrect because Elastic Database Jobs are designed for T-SQL administration tasks across databases, not for gathering metrics via Azure Monitor. Option D is incorrect because Azure Data Factory focuses on data movement and transformation, not on scheduling email reports with live metrics.

Answer

Azure Automation Runbook with PowerShell to collect metrics and send email via Send-MailMessage.

Answer

Elastic Database Job that queries sys.dm_db_resource_stats and sends email via sp_send_dbmail.

Answer

Azure Data Factory pipeline that runs weekly and sends an email with the report.

Question 3

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 error 35201 indicates a connection timeout. SQL2 is configured for automatic failover and synchronous commit. After the restart, SQL2 remains disconnected and not healthy. This is likely because the network connectivity between SQL1 and SQL2 is still interrupted or there is a persistent issue. Since SQL3 is connected and healthy, the issue is specific to SQL2.

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 4

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is frequently throttled. Which configuration change can help reduce log rate throttling?

Accepted Answer

Increase the log rate limit by scaling up the service level objective. Increasing the log rate limit by scaling up the service level objective (SLO) allows more transactions per second, reducing log rate throttling. Option B is correct. Option A is wrong because increasing max degree of parallelism does not directly affect log rate; it may even increase log generation. Option C is wrong because backup retention period does not influence log rate. Option D is wrong because adding compute replicas does not increase the log rate limit; log rate is tied to the primary replica's SLO.

Answer

Increase max degree of parallelism

Answer

Reduce backup retention period

Answer

Add more compute replicas

Question 5

You are deploying Azure SQL Database for a critical application. The compliance policy requires that the database be configured for zone redundancy in the primary region and have automated failover to a secondary region with no data loss. What Azure SQL Database offering and feature should you choose?

Accepted Answer

Business Critical tier with zone redundancy and active geo-replication. The Business Critical tier is the only Azure SQL Database tier that supports both zone redundancy within a region and active geo-replication with synchronous data replication to a secondary region. Zone redundancy ensures high availability within the primary region by distributing replicas across availability zones, while active geo-replication provides automated failover to a secondary region with no data loss because it uses synchronous commit at the primary and asynchronous replication to the secondary, but with the ability to fail over without data loss if the secondary is fully synchronized. This combination meets the compliance requirement for zero data loss during a regional failover.

Answer

Basic tier with geo-replication

Answer

General Purpose tier with geo-replication

Answer

Hyperscale tier with zone redundancy

Question 6

You are managing an Azure SQL Database that hosts a customer relationship management (CRM) application. The database has a table named 'Contacts' with columns: ContactID (int, primary key), Name (nvarchar(100)), Email (nvarchar(200)), Phone (nvarchar(20)), and CreditLimit (decimal(18,2)). The compliance team requires that the CreditLimit column be encrypted so that only authorized users can view it. The application must be able to search for exact matches on CreditLimit values. You need to implement encryption without changing the application code significantly. Which encryption method should you use?

Accepted Answer

Always Encrypted with deterministic encryption. Always Encrypted with deterministic encryption is correct because it encrypts the CreditLimit column at the client driver level, ensuring data remains encrypted at rest and in transit, while still allowing exact-match searches (e.g., WHERE CreditLimit = 5000) since deterministic encryption always produces the same ciphertext for a given plaintext. This meets the compliance requirement without requiring significant application code changes, as the Azure SQL Database driver handles encryption and decryption transparently for authorized users.

Answer

Always Encrypted with randomized encryption

Answer

Transparent Data Encryption

Answer

Dynamic Data Masking

Question 7

You administer an Azure SQL Database that uses the General Purpose tier. Users report that queries are slow during peak hours. You need to identify if the slow performance is due to log write latency. Which metric should you examine in Azure Monitor?

Accepted Answer

Log write latency. The correct metric to examine is Log write latency (option D), as it directly measures the time taken to write to the transaction log, which can indicate slow performance due to log write latency. Option A (Log IO percent) measures the percentage of log throughput used, not latency. Option B (Transaction log usage) measures log file space used, not performance. Option C (Average IO latency) includes both data and log I/O, so it is not specific to log writes.

Answer

Log IO percent

Answer

Transaction log usage

Answer

Average IO latency

Question 8

Your company uses Azure SQL Database with Microsoft Entra ID authentication. You need to ensure that only users from a specific Microsoft Entra ID tenant can access the database. What should you configure?

Accepted Answer

Create a server-level firewall rule with IP range '0.0.0.0' and then use Microsoft Entra ID authentication with a conditional access policy.. To restrict access to a specific Microsoft Entra ID tenant, you must first set the server-level firewall to allow all Azure IPs (0.0.0.0) so that Azure SQL Database can accept connections from any Azure resource, and then enforce tenant-level restrictions using a conditional access policy. This combination ensures that only users from the specified tenant can authenticate, while the firewall rule handles network-level access from Azure services.

Answer

Enable Azure AD conditional access policy to restrict sign-ins to the specific tenant.

Answer

Set the server firewall to allow only the tenant's IP range.

Answer

Configure a network security group (NSG) to block all traffic except from the tenant's IP range.

Question 9

You are deploying an Azure SQL Database that will contain highly sensitive personal data. The security policy requires that the data be encrypted at rest, in transit, and in use. Additionally, the encryption keys must be stored in a hardware security module (HSM) and be customer-managed. Which combination of features should you implement?

Accepted Answer

TDE with a customer-managed key in Azure Key Vault (HSM-backed), enforce TLS 1.2, and Always Encrypted with a column master key in Azure Key Vault (HSM-backed).. It satisfies all requirements: encryption at rest via TDE with a customer-managed key stored in an HSM-backed Key Vault, encryption in transit by enforcing TLS 1.2, and encryption in use via Always Encrypted with the column master key also stored in an HSM-backed Key Vault. This ensures that all three states of data are encrypted and that keys are both customer-managed and hardware-protected.

Answer

TDE with a service-managed key, enforce TLS 1.2, and Always Encrypted with a column master key in Key Vault.

Answer

TDE with a customer-managed key in Key Vault, enforce TLS 1.2, and use Dynamic Data Masking.

Answer

TDE with a customer-managed key in Key Vault, enforce TLS 1.2, and Always Encrypted with a column master key stored in Windows Certificate Store.

Question 10

You manage an Azure SQL Database that is part of a business-critical application. You need to ensure that network traffic between the application hosted on Azure VMs and the database is encrypted and does not traverse the public internet. What should you configure?

Accepted Answer

Create a private endpoint for Azure SQL Database in the same virtual network as the application VMs.. Creating a private endpoint for Azure SQL Database places the database service on a private IP address within the same virtual network as the application VMs. This ensures that all traffic between the VMs and the database stays entirely within the Microsoft Azure backbone network, never traversing the public internet, while also providing encryption in transit via TLS by default.

Answer

Use TLS 1.2 for all connections to the database.

Answer

Configure server-level firewall rules to allow only the application VM IP addresses.

Answer

Enable forced tunneling on the application VMs to route all traffic through the on-premises network.

Question 11

Which THREE considerations are important when planning a migration from SQL Server on-premises to Azure SQL Managed Instance?

Accepted Answer

File tables and FILESTREAM.. Azure SQL Managed Instance does not support FILESTREAM or FileTables. These features rely on the NTFS file system for storing BLOB data, which is not available in the managed instance environment. Any migration using FILESTREAM or FileTables must be refactored to use alternative storage, such as Azure Blob Storage with the remote BLOB store (RBS) or separate file storage.

Answer

SQL Server Integration Services (SSIS) packages.

Answer

Use of contained databases.

Question 12

The query returns a list of query hashes with high average duration. You need to identify which queries are most likely causing CPU pressure. What additional metric should you include?

Accepted Answer

Include avg_cpu_time to measure CPU usage.. Avg_cpu_time directly measures CPU usage per query, helping correlate high duration with CPU pressure. Option A is incorrect because wait_stats show wait types (e.g., blocking), not CPU consumption. Option B is incorrect because execution count alone does not indicate CPU usage; a query could run frequently but use little CPU. Option C is incorrect because avg_logical_reads measure I/O, not CPU pressure.

Answer

Include wait_stats to see blocking.

Answer

Include count_executions to see frequency.

Answer

Include avg_logical_reads to see I/O consumption.

Question 13

You need to recommend a performance monitoring solution for a new Azure SQL Managed Instance deployment. The solution must provide historical query performance data and the ability to compare performance before and after index changes. What should you include in the recommendation?

Accepted Answer

Query Store with custom retention settings. Query Store captures historical query performance data and allows comparing performance before and after index changes, making it the correct choice for this requirement. SQL Server DMVs (Option B) only provide current state, not historical trends. Azure SQL Analytics solution (Option C) provides aggregated metrics but lacks per-query historical comparison. Intelligent Insights (Option D) offers diagnostic analysis but not detailed historical query data.

Answer

SQL Server DMVs

Answer

Azure SQL Analytics solution in Log Analytics

Answer

Azure SQL Database Intelligent Insights

Question 14

You are reviewing the long-term retention (LTR) policy for an Azure SQL Database. The exhibit shows the current policy. You need to ensure that backups are retained for at least 10 years for compliance. What should you do?

Accepted Answer

Increase the yearly retention to P10Y.. The current yearly retention is P3Y (3 years), which is insufficient for the 10-year compliance requirement. Increasing it to P10Y retains yearly backups for 10 years. Option B is incorrect because weekOfYear specifies which week's backup is retained for yearly retention, not the retention period. Option C is incorrect because monthly retention at P120M (120 months = 10 years) would retain 120 monthly backups, but the requirement is for yearly retention, and monthly retention doesn't cover full 10-year compliance on its own. Option D is incorrect because weekly retention at P10W (10 weeks) is far less than 10 years.

Answer

Change the weekOfYear to 10.

Answer

Increase the monthly retention to P120M.

Answer

Increase the weekly retention to P10W.

Question 15

Your organization runs a critical e-commerce application on Azure SQL Database. You notice that during peak hours, query performance degrades significantly. You have enabled Intelligent Insights and Query Store. Which diagnostic metric would you analyze first to identify the root cause of the performance degradation?

Accepted Answer

DTU/CPU consumption. DTU/CPU consumption directly correlates with query performance during peak load. With Intelligent Insights and Query Store enabled, analyzing DTU/CPU consumption helps identify resource contention as the primary cause of degradation. Option A (Query store wait statistics) is useful for detailed analysis but is not the first metric to check. Option B (Deadlock count) is not a typical cause of sustained degradation. Option D (Failed connections) indicates availability issues, not performance.

Answer

Query store wait statistics

Answer

Deadlock count

Answer

Failed connections