DP-300 Practice Questions 2 — 50 Questions

Question 1

You are designing a disaster recovery plan for an Azure SQL Database that uses the Business Critical tier. The database is deployed in the West US region. You need to ensure that if the entire West US region becomes unavailable, the database can be failed over to a secondary region with minimal data loss. What should you implement?

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

Accepted Answer

Configure an auto-failover group with a readable secondary in East US. Auto-failover groups with a readable secondary in East US provide automatic and manual failover capabilities. For Business Critical tier, the secondary is readable and uses asynchronous replication, typically achieving an RPO of 5 seconds or less. This meets the requirement for minimal data loss. Active geo-replication also uses asynchronous replication with similar RPO but requires manual failover and does not support automatic failover. Zone redundancy protects against within-region failures, not regional outages. Geo-restore has a higher RPO and requires manual recovery.

Answer

Configure active geo-replication to East US

Answer

Enable zone redundancy for the database

Answer

Configure geo-restore from the West US database

Question 2

A DBA needs to create a new Azure SQL Database and wants to ensure that the database automatically fails over to a secondary region without manual intervention. The recovery point objective (RPO) is 5 seconds. What should the DBA configure?

Accepted Answer

Active geo-replication with failover group. Active geo-replication with a failover group is the correct choice because it provides automatic, customer-managed failover to a secondary region with an RPO of 5 seconds. The failover group orchestrates the failover of multiple databases simultaneously and supports automatic failover policies, meeting the requirement for zero manual intervention. Standard geo-replication does not support automatic failover, and other options do not provide cross-region disaster recovery.

Answer

Standard geo-replication

Answer

Local redundancy with automatic failover

Answer

Read-scale out

Question 3

You are the database administrator for a global e-commerce company. The company runs its production SQL Server on an Azure Virtual Machine (IaaS) in the West US region. The database is mission-critical and requires a Recovery Point Objective (RPO) of 5 minutes and a Recovery Time Objective (RTO) of 30 minutes in the event of a regional disaster. The VM uses premium SSDs and is backed up daily to a Recovery Services vault with geo-redundant storage. The current backup policy takes full backups weekly, differential backups daily, and transaction log backups every 15 minutes. The VM is in an availability set for high availability within the region. During a recent regional outage simulation, the database was unavailable for 4 hours because the backups needed to be restored to a different region, and the restore process took longer than expected. You need to recommend a solution to meet the RPO and RTO requirements. What should you do?

Accepted Answer

Configure a SQL Server Always On availability group with a synchronous-commit replica in a secondary Azure region.. A SQL Server Always On availability group with a synchronous-commit replica in a secondary Azure region provides automatic failover with zero data loss (RPO of 0 seconds) and can meet the 30-minute RTO by enabling fast, automated failover to the secondary region. This solution eliminates the need for manual restore processes and ensures continuous data synchronization, directly addressing the 4-hour outage caused by slow geo-restore.

Answer

Implement Azure Site Recovery to replicate the VM to a secondary region.

Answer

Set up log shipping to a secondary SQL Server in a different region and perform manual failover.

Answer

Increase the frequency of transaction log backups to every 5 minutes and use geo-restore.

Question 4

You have an Azure SQL Database that needs to be accessed by an application running on an Azure VM. The VM is in a different subscription. You want to minimize administrative overhead and ensure secure connectivity without exposing the database to the public internet. What should you do?

Accepted Answer

Create a private endpoint for the SQL Database in the VM's VNet.. A private endpoint assigns the Azure SQL Database a private IP address from the VM's VNet, enabling secure connectivity over the Microsoft backbone without exposing the database to the public internet. This minimizes administrative overhead as it does not require VPN gateways or complex routing, and it works across subscriptions by linking the private endpoint to the VM's VNet.

Answer

Set up a site-to-site VPN between the VM's VNet and the SQL Database's VNet.

Answer

Use a VNet service endpoint for Azure SQL Database in the VM's VNet.

Answer

Configure a firewall rule to allow the VM's public IP address.

Question 5

Your company has an Azure SQL Database that contains sensitive financial data. You need to ensure that database administrators cannot view the actual data while still being able to perform administrative tasks such as backups and index maintenance. Which feature should you implement?

Accepted Answer

Always Encrypted with column master key stored in Azure Key Vault. Always Encrypted with the column master key stored in Azure Key Vault ensures that sensitive data is encrypted at the client side and the encryption keys are never exposed to the database engine. This means database administrators (DBAs) can perform administrative tasks like backups and index maintenance on the encrypted columns without ever being able to view the plaintext data, because the SQL Server instance only sees ciphertext.

Answer

Row-Level Security

Answer

Dynamic Data Masking

Answer

Transparent Data Encryption

Question 6

You are designing a new Azure SQL Database for a critical OLTP workload. The database will be used by a global application with users in North America, Europe, and Asia. The primary requirement is low-latency reads for all regions. You need to choose a deployment option that supports geo-distributed reads and provides a single write endpoint. Which option should you select?

Accepted Answer

Azure SQL Database with failover groups. Azure SQL Database failover groups (Option C) is the correct choice. A failover group provides a single read-write listener endpoint that automatically directs writes to the current primary, while also exposing a read-only listener endpoint that load-balances connections across geo-replicated readable secondaries in other regions. This directly satisfies the requirements for a single write endpoint and low-latency geo-distributed reads. Active geo-replication (Option A) supports readable secondaries but does not provide a single write endpoint. Azure SQL Managed Instance failover groups (Option B) also provide these capabilities, but the scenario specifies Azure SQL Database, not Managed Instance. Hyperscale with geo-replication (Option D) is not required to meet these requirements; Hyperscale is primarily a scalability and performance tier, and geo-replication alone does not provide a single write endpoint.

Answer

Azure SQL Database with active geo-replication

Answer

Azure SQL Managed Instance with failover groups

Answer

Azure SQL Database Hyperscale with geo-replication

Question 7

You are monitoring an Azure SQL Database and notice that the average CPU usage is 80% and the average data IO percentage is 70%. You need to identify the most likely cause of the high resource usage. What should you check first?

Accepted Answer

Use Query Store to identify top resource-consuming queries. High average CPU (80%) and data IO (70%) suggest that the database is under sustained load from inefficient or resource-intensive queries. Query Store captures query execution plans, runtime statistics, and resource consumption per query, making it the fastest way to pinpoint the top resource consumers. Checking Query Store first allows you to identify the specific queries driving CPU and IO, which is the most direct diagnostic step.

Answer

Check for long-running maintenance tasks

Answer

Check for connection pooling issues

Answer

Check for blocking and deadlocks

Question 8

You have an Azure SQL Database that is experiencing high wait times on RESOURCE_SEMAPHORE waits. You need to identify the root cause. What should you check?

Accepted Answer

Queries with large memory grants. RESOURCE_SEMAPHORE waits indicate memory grant pressure, typically caused by queries requesting large memory grants. Option D is correct. Option A is incorrect because blocking and deadlocks produce LCK_M_* waits, not RESOURCE_SEMAPHORE. Option B is incorrect because high CPU usage leads to SOS_SCHEDULER_YIELD waits. Option C is incorrect because disk I/O bottlenecks cause PAGEIOLATCH waits.

Answer

Blocking and deadlocks

Answer

High CPU usage

Answer

Disk I/O bottlenecks

Question 9

You need to monitor the long-running queries in an Azure SQL Database. Which dynamic management view should you query to see queries that have been running for more than 30 seconds?

Accepted Answer

sys.dm_exec_requests. sys.dm_exec_requests is the correct DMV because it returns one row per active request currently executing on the SQL Server instance, including the total elapsed time. You can filter on total_elapsed_time > 30000 to find queries running longer than 30 seconds. It also provides session_id, status, command, and wait information, making it ideal for identifying long-running queries.

Answer

sys.dm_db_resource_stats

Answer

sys.dm_exec_sessions

Answer

sys.dm_exec_query_stats

Question 10

You are a database administrator for a multinational corporation that uses Azure SQL Managed Instance to host multiple databases for different business units. The security policy requires that all connections to the managed instance must use encrypted connections (TLS 1.2 or higher). Additionally, the company wants to minimize the attack surface by restricting network access. You need to configure the managed instance to enforce encrypted connections and block all public internet traffic. What should you do?

Accepted Answer

Set the 'Minimal TLS Version' property to 1.2 and set 'Public data endpoint' to 'Disabled'. Setting the 'Minimal TLS Version' property to 1.2 enforces that all connections use TLS 1.2 or higher, meeting the encryption requirement. Disabling the 'Public data endpoint' blocks all public internet traffic, ensuring that only traffic from within the virtual network can reach the managed instance. This combination directly satisfies both security policy goals without relying on additional components like private endpoints or firewall rules.

Answer

Enable a private endpoint and set the 'Minimal TLS Version' property to 1.0

Answer

Disable the public endpoint and enable a service endpoint for the virtual network

Answer

Configure a server-level firewall rule to allow only specific IP addresses and set the 'Minimal TLS Version' property to 1.2

Question 11

Your company uses Azure SQL Database with active geo-replication. You notice that the secondary database in a different region has a high log write latency. Users report that the primary database performance is normal. What is the most likely cause?

Accepted Answer

Network latency between the primary and secondary regions. In Azure SQL Database active geo-replication, the secondary database continuously receives transaction log from the primary over the network. High log write latency on the secondary while the primary performs normally points to network latency between the two regions, since the log must traverse the inter-region link and be hardened on the secondary.

Answer

Insufficient log IOPS on the secondary database

Answer

High CPU usage on the primary database

Answer

Excessive read workload on the secondary database

Question 12

You are a database administrator for an Azure SQL Database. You need to ensure that only specific client IP addresses can connect to the database, while all other traffic is blocked. You also need to allow Azure services to access the database. What should you configure?

Accepted Answer

Configure server-level firewall rules to allow the specific client IP addresses and enable the 'Allow Azure services and resources to access this server' setting.. Azure SQL Database uses server-level firewall rules to control inbound access. By adding rules for specific client IP addresses and enabling the 'Allow Azure services and resources to access this server' setting, you restrict connections to only those IPs while permitting Azure internal services (e.g., Azure Logic Apps, Azure Functions) to connect. This setting leverages the Azure SQL firewall, which evaluates source IP addresses against the configured rules before allowing a connection.

Answer

Disable public network access and configure a service endpoint.

Answer

Configure a private endpoint and disable public network access.

Answer

Configure network security group (NSG) rules on the subnet where the Azure SQL Database is deployed.

Question 13

You are analyzing the exhibit KQL query that queries Azure Diagnostics logs for Query Store runtime statistics. The query is intended to show average CPU time per hour for each database. However, the result shows no data for the last 24 hours, although Query Store is enabled on all databases. What is the most likely reason?

Accepted Answer

The diagnostic settings are not configured to send QueryStoreRuntimeStatistics to Log Analytics.. For Query Store runtime statistics to appear in Log Analytics, the Azure SQL Database diagnostic settings must explicitly route the QueryStoreRuntimeStatistics category to the Log Analytics workspace. If that category is not enabled, the KQL query will return no rows even though Query Store is enabled on the databases, because the data never reaches the workspace. This is the most likely cause of the empty result set.

Answer

Query Store is not enabled on the databases.

Answer

The time range in the query is too narrow and excludes the last 24 hours.

Answer

The query syntax is incorrect and needs to use 'project' before 'summarize'.

Question 14

You are configuring performance monitoring for Azure SQL Managed Instance. You need to collect and analyze query performance data with minimal overhead. Which solution should you use?

Accepted Answer

Query Store. Query Store is built-in and designed for low overhead query performance monitoring. Option B is wrong because Azure Monitor metrics provide resource-level metrics, not query-level details. Option C is wrong because Extended Events can have higher overhead and is more for custom event collection. Option D is wrong because SQL Server Profiler is deprecated and has high overhead.

Answer

Azure Monitor metrics

Answer

Extended Events

Answer

SQL Server Profiler

Question 15

You are deploying a new application on Azure SQL Database. The application requires that all connections use a specific login, 'AppUser', with the least privileges necessary. The login should only be able to execute stored procedures in the 'Sales' schema and should not have direct access to underlying tables. What should you do?

Accepted Answer

Create a database role, grant EXECUTE on the 'Sales' schema to the role, and add 'AppUser' to the role.. Creating a database role, granting EXECUTE on the 'Sales' schema to that role, and adding 'AppUser' to the role follows the principle of least privilege. This allows 'AppUser' to execute stored procedures in the Sales schema without granting direct table access, because stored procedures execute with ownership chaining or elevated permissions. This is the most maintainable and secure approach.

Answer

Grant the SELECT permission on the 'Sales' schema to 'AppUser'.

Answer

Add 'AppUser' to the db_datareader role.

Answer

Grant the EXECUTE permission on each stored procedure individually to 'AppUser'.

Question 16

You are monitoring an Azure SQL Database and notice high PAGELATCH waits. What is the most likely cause?

Accepted Answer

Concurrent inserts into a table with a clustered index causing last-page contention.. High PAGELATCH waits in Azure SQL Database are most commonly caused by concurrent inserts into a table with a clustered index, leading to last-page contention. Multiple threads trying to insert into the same page serialize on the page latch, causing PAGELATCH waits. This is a classic OLTP pattern where a monotonically increasing key (like an IDENTITY column) causes all inserts to target the last page.

Answer

Insufficient buffer pool size leading to frequent reads from disk.

Answer

High CPU usage due to inefficient queries.

Answer

Long-running transactions blocking other queries.

Question 17

Your company wants to ensure business continuity for an Azure SQL Database that is used by a critical application. The database must remain available in the event of a single availability zone failure within a region. Which configuration should you use?

Accepted Answer

Configure zone-redundant availability for the database. Zone-redundant availability for Azure SQL Database replicates the database across multiple availability zones within a region, providing high availability if one zone fails. This ensures the database remains available during a single zone outage, meeting the business continuity requirement.

Answer

Use active geo-replication to a different region

Answer

Deploy the database with locally redundant storage

Answer

Configure read scale-out with a secondary replica

Question 18

You have an Azure SQL Managed Instance used for an e-commerce platform. During a flash sale, you experience a deadlock that causes transaction rollbacks. You need to minimize deadlock occurrences in the future. What should you implement?

Accepted Answer

Enable READ COMMITTED SNAPSHOT isolation level.. Enabling READ COMMITTED SNAPSHOT isolation (RCSI) makes readers use row versioning from tempdb instead of taking shared locks, which eliminates the classic reader-writer deadlock pattern that plagues e-commerce workloads mixing SELECTs and UPDATEs. This directly reduces deadlock frequency without changing application code.

Answer

Configure deadlock graph in Extended Events.

Answer

Enable automatic tuning to force last good plan.

Answer

Increase the instance vCores to improve concurrency.

Question 19

Your company uses Azure SQL Database with a server-level Microsoft Entra ID admin. You need to implement a solution where database-level roles are automatically assigned based on the user's group membership in Microsoft Entra ID. What should you use?

Accepted Answer

Create database users from Microsoft Entra ID groups and grant roles to those users.. Azure SQL Database supports creating database users mapped to Microsoft Entra ID (formerly Azure AD) groups. By creating a user for the Entra ID group and then granting database roles to that group user, all members of the group automatically inherit the assigned permissions. This directly satisfies the requirement for role assignment based on group membership without custom scripting or triggers.

Answer

Use Azure RBAC to assign roles to the Entra ID groups.

Answer

Configure a SQL Server Agent job to update database roles based on group membership.

Answer

Create a DDL trigger that assigns roles when users log in.

Question 20

You are managing an Azure SQL Database that experiences intermittent performance degradation. Query Store shows a significant increase in wait time for PAGEIOLATCH_SH. You need to identify the most likely cause. What should you investigate first?

Accepted Answer

Insufficient IOPS or throughput at the database level. PAGEIOLATCH_SH waits indicate I/O subsystem pressure, often due to insufficient IOPS or throughput. Option A is incorrect because out-of-date statistics cause cardinality estimation errors, not I/O waits. Option C is incorrect because missing indexes typically cause table scans but not necessarily PAGEIOLATCH waits. Option D is incorrect because blocking causes waits like LCK_M_*, not PAGEIOLATCH.

Answer

Out-of-date statistics

Answer

Missing indexes

Answer

Blocking from long-running transactions