DP-300 Practice Test 5 — 25 Questions

Question 1

A company plans to migrate an on-premises SQL Server database to Azure SQL Database Managed Instance. They require a high availability solution that provides automatic failover between replicas within the same region with an RPO of 0 and an RTO of less than 30 seconds. Which service tier should they choose?

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

Accepted Answer

Business Critical. Business Critical service tier in Azure SQL Managed Instance provides built-in high availability with multiple replicas and automatic failover, achieving an RPO of 0 and RTO typically under 30 seconds. It uses Always On availability groups under the hood with local redundant storage and supports read-only routing. This tier is designed for mission-critical workloads requiring the lowest latency and highest resilience.

Answer

General Purpose

Answer

Azure SQL Database (single)

Answer

Hyperscale

Question 2

You need to automate the deployment of an Azure SQL Database using Infrastructure as Code. The deployment should include the database, firewall rules, and threat detection settings. Which tool should you use?

Accepted Answer

Azure Resource Manager templates. Azure Resource Manager (ARM) templates are the native IaC for Azure. Azure Automation runbooks can deploy but are not declarative. Azure CLI can script deployments but is imperative. Azure Policy is for governance, not deployment.

Answer

Azure CLI scripts

Answer

Azure Automation runbooks

Answer

Azure Policy

Question 3

Refer to the exhibit. An Azure SQL Database is experiencing performance degradation. Based on the Extended Events and wait statistics, which is the most likely root cause?

Accepted Answer

I/O subsystem bottleneck. The exhibit shows PAGEIOLATCH_SH and WRITELOG waits dominating the wait statistics, which are classic indicators of I/O subsystem bottlenecks. PAGEIOLATCH_SH waits occur when a session is waiting for a data page to be read from disk into the buffer pool, while WRITELOG waits indicate delays in writing to the transaction log. These waits are not caused by CPU or memory pressure, but by slow disk I/O, making option C the correct root cause.

Answer

Blocking due to lock contention

Answer

CPU pressure from high-complexity queries

Answer

Insufficient memory allocation for the database

Question 4

Your company requires that all production databases in Azure SQL Database have an RPO of less than 5 seconds and an RTO of less than 1 minute during a regional outage. You need to recommend a high availability and disaster recovery solution. Which feature should you use?

Accepted Answer

Auto-failover groups with active geo-replication. Auto-failover groups with active geo-replication provide a group-level failover that can redirect all databases in a group to a secondary region with an RPO of about 5 seconds and an RTO of under a minute, meeting both requirements. Active geo-replication continuously replicates transactions to secondary databases, and the failover group automates the DNS and connection redirection so applications reconnect quickly.

Answer

Zone-redundant configuration (Hyperscale service tier)

Answer

Long-term retention (LTR) backups

Answer

Geo-restore from geo-redundant backups

Question 5

Your organization requires that all changes to sensitive data in an Azure SQL Database be logged for compliance. You need to capture who changed what data and when, and store the logs in a Log Analytics workspace for analysis. What should you configure?

Accepted Answer

Configure server-level auditing to send logs to a Log Analytics workspace.. Server-level auditing in Azure SQL Database can be configured to send audit logs directly to a Log Analytics workspace, capturing detailed information about data changes including who made the change, what was changed, and when. This meets the compliance requirement for logging sensitive data changes and enables analysis using Log Analytics queries.

Answer

Enable change tracking on the database.

Answer

Enable Microsoft Defender for Cloud on the server.

Answer

Enable Transparent Data Encryption (TDE) with customer-managed keys.

Question 6

You are designing an automated backup retention policy for an Azure SQL Database. The business requirement is to retain daily backups for 30 days, weekly backups for 12 weeks, monthly backups for 12 months, and yearly backups for 7 years. Which backup retention type should you configure?

Accepted Answer

Long-term retention (LTR) policy. Long-term retention (LTR) policy in Azure SQL Database is specifically designed to retain full backups beyond the default PITR window, supporting configurable weekly, monthly, and yearly retention periods. The requirement of 30 days daily, 12 weeks weekly, 12 months monthly, and 7 years yearly maps exactly to LTR's weekly/monthly/yearly retention options, which can be set independently.

Answer

Point-in-time restore (PITR) retention

Answer

Backup vault with Azure Backup

Answer

Automated backup policy

Question 7

Drag and drop the steps to restore an Azure SQL Database to a point in time in the correct order.

Accepted Answer

Select the source database, then choose Restore type as Point-in-time, then specify the point in time, then name the new database, then click Create.. The restore process starts by selecting the database, then choosing the restore type, specifying the point in time, naming the new database, and finally creating it.

Answer

Select the source database, then name the new database, then choose Restore type as Point-in-time, then specify the point in time, then click Create.

Answer

Select the source database, then choose Restore type as Point-in-time, then click Create, then name the new database, then specify the point in time.

Answer

Select the source database, then specify the point in time, then choose Restore type as Point-in-time, then name the new database, then click Create.

Question 8

Match each Azure SQL Database service tier to its description.

Accepted Answer

General Purpose: Balanced performance for most cloud workloads with compute and storage separation.. These are the main service tiers in Azure SQL Database, each designed for different performance and scalability needs.

Answer

General Purpose: High I/O and low latency with multiple read-only replicas.

Answer

Business Critical: Scalable storage up to 100 TB with auto-scaling compute.

Answer

Hyperscale: Balanced performance for small to medium workloads with compute and storage separation.

Question 9

You need to audit all schema changes (DDL) on an Azure SQL Database for compliance. The audit logs must be retained for 7 years. What should you do?

Accepted Answer

Enable auditing on the database, log to a storage account, and set the retention policy to 7 years.. Azure SQL Database auditing can be configured at the database level to log all database events, including DDL changes, to a storage account. The retention policy can be set to 7 years directly in the audit settings, ensuring compliance with long-term retention requirements. This is the native, supported method for auditing schema changes on Azure SQL Database.

Answer

Create an extended events session to capture DDL events and save to a file.

Answer

Enable change tracking on the database and query the change tracking tables.

Answer

Enable SQL Server Audit at the server level and specify a file destination.

Question 10

You are configuring automated backups for an Azure SQL Database. Which TWO settings can you configure?

Accepted Answer

Backup retention period (in days).. The backup retention period (in days) is a configurable setting for Azure SQL Database automated backups. You can set the retention period for point-in-time restore (PITR) backups between 1 and 35 days, and for long-term retention (LTR) backups up to 10 years. This directly controls how far back you can restore your database.

Answer

Backup compression.

Answer

Backup frequency (full, differential, log).

Answer

Point-in-time restore interval.

Question 11

Your organization has a policy that all Azure SQL Database connections must use Microsoft Entra authentication. You need to ensure that application developers cannot accidentally use SQL authentication. What should you do?

Accepted Answer

Enable 'Azure AD-only authentication' on the logical server.. Enabling 'Microsoft Entra ID-only authentication' on the logical server explicitly blocks all SQL authentication connections, including those from contained database users. This setting enforces that only Microsoft Entra ID (formerly Azure AD) principals can authenticate, directly aligning with the policy to prevent accidental use of SQL authentication.

Answer

Configure server-level firewall rules to block all IP addresses except Azure services.

Answer

Disable SQL authentication for all contained database users.

Answer

Create a database-level trigger to reject connections using SQL authentication.

Question 12

You are responsible for securing an Azure SQL Database. You need to implement data masking for a column that contains credit card numbers, ensuring that users with the db_datareader role see a masked version. However, users with the db_owner role should see the unmasked data. What should you configure?

Accepted Answer

Apply Dynamic Data Masking (DDM) to the credit card column.. Dynamic Data Masking (DDM) is the correct choice because it allows you to obfuscate sensitive data in query results for non-privileged users (like db_datareader) while permitting users with elevated permissions (like db_owner) to see the unmasked data. DDM is applied at the column level and does not modify the underlying data; it simply masks the output based on the user's permissions. The db_owner role is exempt from masking by default, meeting the requirement exactly.

Answer

Implement Row-Level Security (RLS) to filter rows based on user role.

Answer

Implement Always Encrypted with deterministic encryption.

Answer

Enable Transparent Data Encryption (TDE).

Question 13

Match each Azure SQL Database monitoring metric to its meaning.

Accepted Answer

DTU percentage: Measures the percentage of DTU limit used by the database.. These metrics are used to monitor resource usage and performance in Azure SQL Database.

Answer

DTU percentage: Measures the percentage of CPU limit used by the database.

Answer

CPU percentage: Measures the percentage of data I/O limit used by the database.

Answer

Data IO percentage: Measures the percentage of DTU limit used by the database.

Question 14

Which TWO methods can be used to automate index maintenance in Azure SQL Database?

Accepted Answer

Azure Automation runbooks executing T-SQL scripts. Azure Automation runbooks can execute T-SQL scripts against Azure SQL Database using the Invoke-SqlCmd cmdlet or similar, enabling scheduled index maintenance tasks such as rebuilding or reorganizing indexes. This method is fully supported in Azure SQL Database, which lacks SQL Server Agent, and allows for flexible, cloud-native automation.

Answer

SQL Server Agent jobs

Answer

Azure Data Factory pipelines

Answer

Automatic tuning

Question 15

You manage an Azure SQL Database that is experiencing higher than expected DTU consumption. You need to identify which queries are consuming the most resources. Which dynamic management view should you query?

Accepted Answer

Query sys.dm_exec_query_stats. sys.dm_exec_query_stats returns aggregate performance statistics for cached query plans, including total worker time, logical reads, and execution counts. This makes it the correct DMV to identify which queries consume the most resources, as it directly attributes resource usage to individual query statements. By ordering by total_worker_time or total_logical_reads, you can pinpoint the top resource-consuming queries.

Answer

Query sys.dm_exec_requests

Answer

Query sys.dm_os_wait_stats

Answer

Query sys.dm_db_resource_stats

Question 16

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 17

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 18

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 19

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 20

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%