DP-300 Practice Test 25 — 15 Questions

Question 1

You manage an Azure SQL Database that experiences periodic performance degradation. You need to identify the top queries by CPU consumption over the last hour. Which dynamic management view should you query?

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

Accepted Answer

sys.dm_exec_query_stats. sys.dm_exec_query_stats returns one row per cached query plan and includes cumulative execution statistics such as total_worker_time (CPU), total_elapsed_time, and execution_count. Aggregating total_worker_time over the last hour identifies the top CPU-consuming queries. This is the standard DMV for query-level performance analysis in Azure SQL Database.

Answer

sys.dm_exec_sessions

Answer

sys.dm_exec_query_plan

Answer

sys.dm_exec_requests

Question 2

Which TWO actions can you perform using Elastic Database Jobs in Azure SQL Database?

Accepted Answer

Schedule a job to rebuild indexes on a set of databases.. Options B and E are correct. Elastic Database Jobs in Azure SQL Database allow you to schedule and run T-SQL scripts across multiple databases, including those in an elastic pool. You can use them to schedule index rebuilding (B) and to update statistics via T-SQL (E). Option A is incorrect because firewall rules are managed through Azure SQL Server firewall settings, not Elastic Database Jobs. Option C is incorrect because creating users in Microsoft Entra ID (formerly Azure AD) is done through Active Directory, not Elastic Database Jobs. Option D is incorrect because scaling service tiers is an administrative operation handled by Azure SQL Database scaling commands or automation, not by Elastic Database Jobs.

Answer

Add a firewall rule to allow access from a specific IP address.

Answer

Create users in Microsoft Entra ID for database access.

Answer

Automatically scale the service tier of a database based on CPU usage.

Question 3

You need to automatically notify the operations team when an Azure SQL Database reaches 80% storage usage. Which Azure service should you use to create the alert?

Accepted Answer

Azure Monitor. Azure Monitor is the native monitoring and alerting service in Azure. It can collect metrics from Azure SQL Database, including storage usage, and trigger alerts based on thresholds like 80%. You can create an alert rule that sends notifications via email, SMS, or webhook to the operations team.

Answer

Azure Automation

Answer

Microsoft Sentinel

Answer

Azure Logic Apps

Question 4

You need to automate the scaling of an Azure SQL Database in response to CPU usage using Azure Automation. Which Azure service should you use to monitor CPU metrics and trigger the runbook?

Accepted Answer

Azure Monitor metric alerts. Azure Monitor metric alerts are used to monitor CPU metrics and trigger actions such as runbooks in Azure Automation. You can create an alert rule based on a metric like CPU percentage, and configure an action group that triggers a runbook. This is the standard way to automate scaling based on metrics.

Answer

Azure SQL Analytics

Answer

Azure Log Analytics

Answer

Kusto Query Language (KQL)

Question 5

Which TWO actions are valid for implementing column-level encryption in Azure SQL Database using Always Encrypted? (Choose two.)

Accepted Answer

Use randomized encryption for columns that will not be searched.. Always Encrypted supports two encryption types: deterministic and randomized. Randomized encryption is valid for columns that will not be searched because it encrypts the same plaintext into different ciphertexts each time, preventing pattern-based attacks. This makes it suitable for sensitive data like credit card numbers or personal identifiers that only need to be decrypted for use, not queried with equality predicates.

Answer

Store the column encryption key in the database.

Answer

Use a hash of the column value for encryption.

Answer

Encrypt an entire row by specifying a row-level encryption key.

Question 6

You need to configure a long-term retention policy for backups of an Azure SQL Database that must retain weekly full backups for 5 years and monthly full backups for 10 years. Which backup retention feature should you use?

Accepted Answer

Long-Term Retention (LTR) policy. Long-Term Retention (LTR) policies in Azure SQL Database are specifically designed to retain full backups for extended periods — up to 10 years — with configurable weekly, monthly, and yearly retention. To keep weekly full backups for 5 years and monthly full backups for 10 years, the consultant must configure an LTR policy with the appropriate weekly and monthly retention settings.

Answer

Geo-restore feature

Answer

Automated backups retention period

Answer

Point-In-Time Restore (PITR) retention

Question 7

Your company plans to use Azure SQL Managed Instance for a mission-critical application. You need to ensure that all connections to the database are encrypted and that the server's identity is verified. Which configuration should you enforce?

Accepted Answer

Set 'Force Encryption' = ON and 'Trust Server Certificate' = OFF. Setting 'Force Encryption' = ON ensures that all connections to Azure SQL Managed Instance use TLS encryption, while setting 'Trust Server Certificate' = OFF forces the client to validate the server's certificate against a trusted certificate authority (CA). This combination guarantees both data-in-transit encryption and server identity verification, meeting the requirement for a mission-critical application.

Answer

Set 'Force Encryption' = OFF and 'Trust Server Certificate' = OFF

Answer

Set 'Force Encryption' = ON and 'Trust Server Certificate' = ON

Answer

Set 'Force Encryption' = OFF and 'Trust Server Certificate' = ON

Question 8

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 9

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database uses SQL Server Agent jobs, Service Broker, and cross-database queries within the same instance. Which PaaS option should you choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs, Service Broker, and cross-database queries within the same instance. These features are not available in Azure SQL Database, which is a PaaS offering with a more restricted surface area. SQL Server on Azure VM is IaaS and requires manual management, while elastic query in Azure SQL Database only supports cross-database queries across different databases, not the full Service Broker or Agent functionality.

Answer

Azure SQL Database

Answer

SQL Server on Azure Virtual Machine

Answer

Azure SQL Database with elastic query

Question 10

Your Azure SQL Managed Instance is experiencing performance degradation. You suspect a query plan regression caused by parameter-sensitive plan issues. Which feature should you use to identify and resolve the issue?

Accepted Answer

Query Store with Query Store Hints. Query Store with Query Store Hints is the correct feature because it allows you to identify parameter-sensitive plan regression by analyzing historical execution plans stored in Query Store, and then force a specific plan using a hint without changing application code. This directly addresses the root cause of parameter-sensitive plan issues, where different parameter values lead to suboptimal cached plans.

Answer

Intelligent Insights

Answer

Automatic tuning

Answer

Database Advisor

Question 11

Your company has an Azure SQL Managed Instance that hosts multiple databases. You need to implement a solution to automatically detect and alert on potential SQL injection attacks. The solution must integrate with Microsoft Sentinel for incident response. What should you configure?

Accepted Answer

Enable Microsoft Defender for SQL on the Managed Instance. Microsoft Defender for SQL on Azure SQL Managed Instance provides built-in SQL injection detection and alerting, which can be integrated directly with Microsoft Sentinel for automated incident response and investigation. This is the correct solution because it offers native vulnerability assessment and threat detection tailored to SQL databases, meeting the requirement for both detection and Sentinel integration.

Answer

Enable Microsoft Purview Data Map for the Managed Instance

Answer

Configure Microsoft Defender XDR for SQL

Answer

Use Microsoft Intune to manage SQL security policies

Question 12

Your organization requires that all Azure SQL Database backups be retained for 10 years to meet compliance requirements. Which backup retention policy should you configure?

Accepted Answer

Configure long-term retention (LTR) policy.. Long-term retention (LTR) in Azure SQL Database allows you to retain full database backups for up to 10 years, which meets the compliance requirement for 10-year backup retention. LTR policies are configured separately from point-in-time restore (PITR) and store backups in isolated containers for extended periods, ensuring regulatory compliance.

Answer

Configure point-in-time restore (PITR) retention to 10 years.

Answer

Enable automatic tuning to optimize backups.

Answer

Enable geo-redundant backup storage (GRS).

Question 13

Refer to the exhibit. A PowerShell script is used to move an Azure SQL Database into an elastic pool. The script runs without error. Which condition must be true before the script runs?

Accepted Answer

The database must be in the same server as the elastic pool. Moving a database into an elastic pool requires the database to be in the same server as the pool. Option A is wrong because the elastic pool must be in the same server as the database, not a different server. Option C is wrong because the database can be any service tier (Basic, Standard, Premium, etc.) as long as it fits within the pool's resource limits. Option D is wrong because the database size can be any size up to the pool's max size limit, which is not necessarily less than 10 GB.

Answer

The elastic pool must be in a different server

Answer

The database must be in the Basic tier

Answer

The database size must be less than 10 GB

Question 14

You are managing an Azure SQL Database that uses Intelligent Insights. You receive an alert that there is a performance issue with a specific query. You need to analyze the root cause. What should you use?

Accepted Answer

Query Store to review query execution plans and wait statistics. Query Store is the correct tool because it captures historical execution plans, runtime statistics, and wait statistics for individual queries, allowing you to pinpoint the root cause of a performance regression. Intelligent Insights provides high-level diagnostics but not the granular per-query plan and wait data needed for deep analysis of a specific query issue.

Answer

Intelligent Insights report

Answer

Automatic Tuning recommendations

Answer

Azure Monitor metrics for the database

Question 15

Refer to the exhibit. You executed the Azure CLI command to list databases. You need to resume db3 to make it available for connections. Which command should you use?

Accepted Answer

az sql db resume --resource-group rg1 --server server1 --name db3. `az sql db resume` is the command to resume a paused database. Option A is wrong because `az sql db restart` restarts an online database but does not resume a paused one. Option C is wrong because `az sql db start` is not a valid command for Azure SQL Database. Option D is wrong because `az sql db update` can modify properties but cannot resume a paused database; resuming requires a dedicated command.

Answer

az sql db restart --resource-group rg1 --server server1 --name db3

Answer

az sql db start --resource-group rg1 --server server1 --name db3

Answer

az sql db update --resource-group rg1 --server server1 --name db3 --set status=Online