DP-300 Practice Test 12 — 15 Questions

Question 1

Drag and drop the steps to troubleshoot a high CPU usage issue in Azure SQL Database in the correct order.

Accepted Answer

Identify high CPU queries, then Analyze execution plans, then Check for missing indexes, then Implement fixes, then Monitor performance.. Start by identifying high CPU queries, analyze plans, check for missing indexes, implement fixes, then monitor.

Answer

Identify high CPU queries, then Check for missing indexes, then Analyze execution plans, then Implement fixes, then Monitor performance.

Answer

Analyze execution plans, then Identify high CPU queries, then Check for missing indexes, then Implement fixes, then Monitor performance.

Answer

Identify high CPU queries, then Analyze execution plans, then Implement fixes, then Check for missing indexes, then Monitor performance.

Question 2

Your company is deploying a new application that uses an Azure SQL Database. The security policy requires that all connections use Microsoft Entra ID authentication and that no SQL authentication users are created. Which server-level setting should you enforce?

Accepted Answer

Enable 'Azure AD-only authentication' on the Azure SQL logical server.. Enabling 'Microsoft Entra ID-only authentication' on the Azure SQL logical server enforces that all connections must use Microsoft Entra ID authentication and blocks any SQL authentication attempts, even if SQL logins exist. This setting directly meets the security policy requirement that no SQL authentication users are created and all connections use Entra ID.

Answer

Set the database to use contained database authentication.

Answer

Configure a server-level firewall rule to allow only Entra ID IPs.

Answer

Set the Entra ID admin for the server and disable SQL authentication.

Question 3

Refer to the exhibit. An Azure SQL Database is receiving Intelligent Insights degradation alerts. Which action should be taken first?

Accepted Answer

Implement automatic tuning recommendations. Intelligent Insights is an Azure SQL Database feature that uses built-in intelligence to detect and diagnose performance degradation, and it surfaces root cause analysis along with recommended actions. The first and most appropriate response is to apply the automatic tuning recommendations it provides, because Intelligent Insights alerts are diagnostic in nature and typically point to issues like missing indexes, parameter sniffing, or plan regressions that automatic tuning can resolve without changing the service tier or storage. Scaling resources or changing tiers does not address the underlying query-level cause and may be unnecessary or costly.

Answer

Increase the maximum storage size

Answer

Change the service tier to BusinessCritical

Answer

Scale up to Standard S3 (100 DTU)

Question 4

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert indicating 'Degradation in performance due to increased log write wait time'. What is the most likely cause of this issue?

Accepted Answer

The log rate limit has been reached due to high transaction throughput. High log write wait times typically indicate that the transaction log throughput is a bottleneck, often due to the log rate limit. Option A is wrong because high CPU utilization would cause other wait types like SOS_SCHEDULER_YIELD, not WRITELOG. Option B is wrong because long-running blocking transactions cause wait types like LCK_M_*, not increased log write wait time. Option D is wrong because insufficient storage space for data files causes different symptoms, such as write errors or data file growth issues, but not specifically log write wait.

Answer

High CPU utilization on the database server

Answer

Long-running blocking transactions

Answer

Insufficient storage space for data files

Question 5

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 6

You manage an Azure SQL Managed Instance that hosts a critical OLTP database. You notice that the average CPU usage is consistently above 90% during business hours. You have enabled Intelligent Insights, which recommends creating a missing index. What should you do first to validate the recommendation before implementing it?

Accepted Answer

Use Query Store to review query performance and missing index details.. Before acting on Intelligent Insights' missing index recommendation, you should validate it against actual workload evidence. Query Store captures query text, plans, runtime stats, and wait categories, so you can confirm the query is genuinely expensive and that the missing index would help. This avoids creating an index that adds write overhead without meaningful read benefit.

Answer

Scale up the managed instance to a higher tier.

Answer

Enable automatic index tuning.

Answer

Create the recommended index immediately.

Question 7

Your Azure SQL Database uses a failover group for disaster recovery. You need to automate a planned failover for disaster recovery testing without data loss. What should you use?

Accepted Answer

Create an Azure Automation Runbook that uses the Start-AzSqlDatabaseFailover command with the -AllowDataLoss parameter set to false.. Azure Automation Runbooks can execute the Start-AzSqlDatabaseFailover cmdlet with -AllowDataLoss:$false to perform a planned failover without data loss. Option A (Logic App with failover trigger) is designed for reacting to Azure Monitor alerts, not for scheduled failsafe testing. Option B (configuring automatic failover policy) is for unplanned failovers and would result in data loss if used for testing because automatic failover is asynchronous. Option C (T-SQL ALTER DATABASE FAILOVER scheduled via SQL Server Agent) is invalid because SQL Server Agent is not available in Azure SQL Database (single database or elastic pool); it's only available in Azure SQL Managed Instance. Therefore, D is the correct choice for automated planned failover.

Answer

Set up an Azure Logic App with a failover trigger from Azure Monitor.

Answer

Configure automatic failover policy in the failover group.

Answer

Use a T-SQL script with ALTER DATABASE FAILOVER scheduled via SQL Server Agent.

Question 8

You are reviewing an Azure RBAC role assignment for an Azure SQL Database. The role assignment shown in the exhibit is intended to allow a user to read data from the database. However, the user reports they cannot connect to the database. What is the most likely reason?

Accepted Answer

The RBAC role does not grant data plane access; the user must be mapped to a database user and granted database-level permissions.. Azure RBAC roles control management plane operations (e.g., creating or deleting resources) but do not grant access to the data plane (e.g., reading or writing data in a database). To read data from an Azure SQL Database, the user must be mapped to a database user (via a contained database user or an Microsoft Entra ID user) and granted database-level permissions such as db_datareader. The RBAC role assignment shown only provides the 'Microsoft.Sql/servers/databases/read' action, which allows reading database metadata (like tags or properties) but not connecting to the database or querying tables.

Answer

The principal is incorrectly specified; it should be a security group.

Answer

The scope is too broad; it should be at the server level.

Answer

The action 'Microsoft.Sql/servers/databases/read' is not valid; it should be 'Microsoft.Sql/servers/databases/dataReader'.

Question 9

You need to automate the creation of an Azure SQL Database and a corresponding server-level firewall rule to allow access from a specific IP address. The deployment must be repeatable and version-controlled. What should you use?

Accepted Answer

Create an ARM template that defines both the server firewall rule and the database.. ARM templates are declarative JSON files that define the desired state of Azure resources, including Azure SQL Database and server-level firewall rules. They support idempotent deployments, meaning the same template can be run repeatedly to ensure the environment matches the definition, and they can be version-controlled in source control. This makes them ideal for repeatable, automated deployments. The template can include both the Microsoft.Sql/servers/firewallRules and Microsoft.Sql/servers/databases resources, ensuring they are created together.

Answer

Write a PowerShell script that uses New-AzSqlDatabase and New-AzSqlServerFirewallRule.

Answer

Use the Azure portal to create the database and firewall rule.

Answer

Use SQL Server Management Studio to script the creation.

Question 10

Refer to the exhibit. You are configuring Azure SQL Database Transparent Data Encryption (TDE) with customer-managed keys (CMK) stored in Azure Key Vault. The deployment uses a user-assigned managed identity. However, after deployment, the TDE status shows 'Inaccessible'. What is the most likely cause?

Accepted Answer

The managed identity lacks 'Get', 'Wrap Key', and 'Unwrap Key' permissions on the Key Vault key. When using customer-managed keys (CMK) for TDE in Azure SQL Database, the managed identity assigned to the logical server must have 'Get', 'Wrap Key', and 'Unwrap Key' permissions on the key in Azure Key Vault. Without these specific permissions, the SQL Database service cannot retrieve or use the key to encrypt or decrypt the database encryption key, resulting in an 'Inaccessible' TDE status.

Answer

The key specified in the URI does not exist

Answer

The user-assigned managed identity is not assigned to the SQL Database server

Answer

The Key Vault firewall is enabled and does not allow Azure services

Question 11

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

Question 12

Which TWO tools can be used to automate the deployment of database schema changes to Azure SQL Database as part of a CI/CD pipeline? (Choose two.)

Accepted Answer

GitHub Actions with the Azure SQL Database deployment action. Option B is correct because GitHub Actions provides the 'Azure SQL Database deployment' action (azure/sql-action), which executes scripts or DACPAC/BACPAC deployments against Azure SQL Database as an automated pipeline step. Option C is correct because an Azure DevOps release pipeline can use the 'Azure SQL Database deployment' task (SqlAzureDacpacDeployment) to deploy a SQL Server database project's DACPAC, applying schema changes automatically during CI/CD. Option A is not a pipeline automation tool; Azure Data Studio with SQLCMD mode is an interactive client for running scripts manually. Option D, the Import and Export Wizard, only moves data between sources and does not manage schema versioning or pipeline deployment. Option E, SSMS, is a manual GUI administration tool and does not itself automate CI/CD schema deployment.

Answer

Azure Data Studio with SQLCMD mode

Answer

SQL Server Import and Export Wizard

Answer

SQL Server Management Studio (SSMS)

Question 13

Your company runs a critical application on Azure SQL Managed Instance in the North Europe region. The application requires an RPO of 5 minutes and an RTO of 2 hours during a regional disaster. The current setup uses a single instance with geo-redundant backup storage (RA-GRS). During a disaster recovery planning session, you discover that geo-restore from RA-GRS backups takes approximately 4 hours to complete, which exceeds the RTO. You need to modify the disaster recovery solution to meet the RTO without exceeding the budget significantly. The solution must minimize administrative overhead. What should you do?

Accepted Answer

Deploy a secondary instance in a different region and configure a failover group.. A failover group in Azure SQL Managed Instance provides a secondary instance in a different region with automatic or manual failover, and it replicates data asynchronously with an RPO typically under 5 seconds, well within the 5-minute requirement. Failover groups also provide read-write and read-only listener endpoints, minimizing application changes and administrative overhead. This meets the RTO of 2 hours because failover can be triggered quickly, and the secondary is already provisioned and synchronized.

Answer

Change backup storage to locally-redundant (LRS) and rely on point-in-time restore.

Answer

Enable zone redundancy on the primary instance.

Answer

Scale the instance to a higher service tier to improve restore performance.

Question 14

You are a database administrator for a company that stores sensitive customer data in Azure SQL Database. The security team requires that all access to the database be authenticated using Microsoft Entra ID and that no SQL authentication logins exist. You need to verify that SQL authentication is disabled. What should you do?

Accepted Answer

In the Azure portal, navigate to the SQL server's 'Microsoft Entra ID' blade and enable 'Azure AD-only authentication'. Enabling 'Microsoft Entra ID-only authentication' in the SQL server's Microsoft Entra ID blade disables SQL authentication entirely, ensuring that only Microsoft Entra ID principals can authenticate. This directly meets the security requirement to eliminate SQL authentication logins, as it prevents any connection attempt using SQL login credentials, even if they exist in the system catalog.

Answer

Set the 'Deny public network access' property to 'Yes'

Answer

Configure a server-level firewall rule to block all IP addresses

Answer

Query sys.sql_logins to check for any SQL authenticated logins

Question 15

A company manages an Azure SQL Database that stores sensitive customer data. The security team mandates that all connections to the database use Azure Active Directory (Azure AD) authentication and that no SQL authentication logins exist. You are tasked with implementing this requirement. What should you do first?

Accepted Answer

Set an Azure Active Directory admin for the Azure SQL Database server.. Before you can enforce Microsoft Entra ID-only authentication, you must first designate an Microsoft Entra ID admin for the Azure SQL Database server. This admin is the only identity that can manage Microsoft Entra ID users and permissions in the database, and once set, you can then remove or disable SQL authentication logins. Without an Microsoft Entra ID admin, there is no way to authenticate or manage Microsoft Entra ID principals within the database, making the transition impossible.

Answer

Set the server's 'Public network access' to 'Disabled'.

Answer

Remove the server admin login from the master database.

Answer

Deny the CONNECT permission to all SQL authentication logins.