DP-300 Timed Practice Test 2 — 10 Questions

Question 1

You have an Azure SQL Managed Instance with a large number of databases. You need to monitor the storage space used by each database to proactively manage capacity. Which tool should you use?

Accepted Answer

Azure SQL Analytics (preview). Azure SQL Analytics (preview) in Azure Monitor provides a consolidated monitoring solution for Azure SQL databases and managed instances, including detailed storage usage per database. Option A is wrong because Query Performance Insight focuses on query-level metrics like query duration and CPU usage, not per-database storage. Option B is wrong because Automatic tuning is a feature that automatically indexes and optimizes query plans, not a monitoring tool. Option C is wrong because Intelligent Insights provides proactive performance diagnostics and anomaly detection, but does not directly per-database storage monitoring. Therefore, only Azure SQL Analytics meets the requirement.

Answer

Query Performance Insight

Answer

Automatic tuning

Answer

Intelligent Insights

Question 2

You are monitoring an Azure SQL Database that is experiencing high DTU usage. Which TWO metrics should you examine to determine whether the bottleneck is CPU or I/O?

Accepted Answer

Log write bytes per second.. Average CPU percentage directly measures CPU utilization, indicating a CPU bottleneck when high. Log write bytes per second measures the throughput of transaction log writes, which is a key indicator of I/O activity and can reveal an I/O bottleneck. Therefore, both A and B are correct metrics to diagnose whether the high DTU usage is caused by CPU or I/O.

Answer

Number of deadlocks per second.

Answer

Used storage space in GB.

Answer

Number of active sessions.

Question 3

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.)

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 4

You need to design a disaster recovery plan for an Azure SQL Managed Instance. The recovery time objective (RTO) is 1 hour, and the recovery point objective (RPO) is 15 minutes. What is the most cost-effective solution?

Accepted Answer

Create an auto-failover group with a secondary instance in a paired region.. Auto-failover groups (Option C) are the most cost-effective solution for Azure SQL Managed Instance with an RTO of 1 hour and RPO of 15 minutes. They provide an RTO of ~1 hour and an RPO of 5 seconds to 5 minutes, meeting the requirements at low cost. Option A is incorrect because active geo-replication is not supported for SQL Managed Instance. Option B is incorrect because log shipping is not natively supported for Managed Instance. Option D is incorrect because point-in-time restore has a longer RTO and RPO, typically not meeting the 15-minute RPO requirement.

Answer

Configure active geo-replication between instances in different regions.

Answer

Set up log shipping to a secondary instance.

Answer

Use point-in-time restore to a secondary region.

Question 5

You have an Azure SQL Database that uses the General Purpose service tier. The database is critical and you need to ensure that it remains available during a planned patching event that updates the underlying hardware. What does Azure SQL Database provide to maintain availability during such events?

Accepted Answer

Automated failover to a built-in standby replica with minimal downtime.. The General Purpose tier includes built-in high availability with automated failover to a standby replica during planned patching events, minimizing downtime. Option A is incorrect because the database is not taken offline; it fails over seamlessly. Option B is incorrect because manual failover is not required—the process is automatic. Option C is incorrect because General Purpose does not provide zone-redundant replicas by default; that is a feature of the Business Critical or Hyperscale tiers.

Answer

The database is taken offline during patching and restored afterward.

Answer

You must manually fail over to a secondary database to avoid downtime.

Answer

Zone-redundant replicas that ensure zero downtime.

Question 6

You have an Azure SQL Database configured with active geo-replication to a secondary region. The secondary database is used for read-only workloads. A network latency issue causes replication lag to exceed 10 seconds. You need to ensure reporting queries always see consistent data. What should you configure?

Accepted Answer

Use ApplicationIntent=ReadOnly in connection strings and ensure the secondary is up-to-date.. Using ApplicationIntent=ReadOnly in connection strings directs read-only queries to the secondary replica. To ensure consistent data during replication lag, you must verify the secondary is up-to-date before querying, for example by checking the replication lag via DMVs or using the 'latest' hint. This ensures reporting queries see consistent data. Option B is incorrect because read scale-out is specific to Hyperscale databases, not active geo-replication. Option C is incorrect because an auto-failover group with read-write endpoint does not guarantee read consistency for reporting queries. Option D is incorrect because a failover group with read-only endpoint does not enforce consistency checks.

Answer

Enable read scale-out on the secondary.

Answer

Configure an auto-failover group with read-write endpoint.

Answer

Create a failover group with read-only endpoint enabled.

Question 7

A retail company is migrating its on-premises SQL Server database to Azure SQL Database. The database has a table with 500 million rows and receives 10,000 INSERT operations per second during peak hours. The application requires read-committed snapshot isolation. Which Azure SQL Database tier and configuration should the DBA recommend to minimize cost while meeting performance requirements?

Accepted Answer

Hyperscale tier with 4 vCores. The Hyperscale tier with 4 vCores is correct because it supports up to 100 TB of storage, provides fast scaling for high-volume INSERT workloads (10,000 rows/second), and natively supports read-committed snapshot isolation (RCSI) without additional configuration. It minimizes cost compared to Business Critical while offering the necessary performance and isolation level for a 500-million-row table.

Answer

Business Critical tier with 8 vCores

Answer

Serverless tier with auto-pause

Answer

General Purpose tier with 2 vCores

Question 8

Which TWO actions are required to enable Microsoft Entra ID authentication for an Azure SQL Database?

Accepted Answer

Set an Microsoft Entra ID admin for the Azure SQL Server.. To enable Entra ID authentication, you must set an Entra ID admin for the SQL server and create contained database users mapped to Entra ID identities. Enabling Azure AD (Entra ID) integration is automatic. Assigning Azure RBAC roles is for management plane, not database authentication. The SQL Server authentication mode is always enabled.

Answer

Enable SQL Server authentication only.

Answer

Assign the SQL Server Contributor role to the Entra ID users.

Answer

Enable Azure AD integration on the SQL server.

Question 9

You are reviewing an Azure SQL Database audit policy configuration. The policy is set to audit successful and failed database authentication events. You notice that audit logs are being written to both Azure Blob Storage and Azure Monitor. However, you are concerned about security of the storage account access key in the policy. What is the recommended approach to securely reference the storage account?

Accepted Answer

Configure the server's system-assigned managed identity and grant it access to the storage account.. Using a system-assigned managed identity for the Azure SQL Database logical server eliminates the need to store a storage account access key in the audit policy configuration. Managed identities provide a secure, Azure AD-backed identity that can be granted specific permissions (e.g., Storage Blob Data Contributor) on the storage account, ensuring that access is controlled via RBAC rather than a static key. This approach aligns with security best practices by removing the risk of key exposure or rotation failures.

Answer

Remove the storageAccountAccessKey property and rely on the storage endpoint.

Answer

Use a different storage account with a key that expires daily.

Answer

Disable the Azure Monitor destination and only use Blob Storage.

Question 10

You are a database administrator for a hospital that uses Azure SQL Database to store patient records. The hospital's security policy requires that all database access be authenticated using Microsoft Entra ID (formerly Azure AD). You have already created a Microsoft Entra ID user for yourself and granted you the 'db_owner' role. You now need to create a new Microsoft Entra ID user for a nurse who needs read-only access to the database. What should you do first?

Accepted Answer

Connect to the database using your Microsoft Entra account and run 'CREATE USER [nurse@hospital.onmicrosoft.com] FROM EXTERNAL PROVIDER'. The nurse must be created as a contained database user mapped to Microsoft Entra ID. Since the hospital uses Azure SQL Database and requires Microsoft Entra authentication, you must connect to the user database (not master) using your Microsoft Entra account (which has db_owner privileges) and run 'CREATE USER [nurse@hospital.onmicrosoft.com] FROM EXTERNAL PROVIDER'. This creates a database user that authenticates via Microsoft Entra ID without requiring a server-level login, aligning with the security policy.

Answer

In the Azure portal, add the nurse as a server-level Microsoft Entra admin

Answer

Create a SQL login for the nurse on the logical server and then create a user in the database mapped to that login

Answer

Connect to the master database using SQL authentication and run 'CREATE USER [nurse@hospital.onmicrosoft.com] FROM EXTERNAL PROVIDER'