DP-300 Practice Test 15 — 15 Questions

Question 1

Which TWO metrics from sys.dm_db_resource_stats should you monitor to identify a disk IO bottleneck in an Azure SQL Database?

Accepted Answer

avg_data_io_percent. The two correct metrics are C, avg_data_io_percent, and E, avg_log_write_percent. avg_data_io_percent reports the percentage of the data-file IOPS/throughput limit consumed by the database, so sustained values near 100% directly indicate a data-file disk IO bottleneck. avg_log_write_percent reports the percentage of the log-write throughput limit consumed, so elevated values reveal a transaction-log write bottleneck. Together these two IO-related counters isolate disk IO pressure in Azure SQL Database. The unmarked options do not belong: avg_cpu_percent (A) measures compute utilization, max_size_percent (B) tracks storage-space consumption against the size limit, and avg_memory_usage_percent (D) measures memory pressure, none of which identify disk IO bottlenecks.

Answer

avg_cpu_percent

Answer

max_size_percent

Answer

avg_memory_usage_percent

Question 2

You are troubleshooting a connectivity issue: an application running on an Azure virtual machine (VM) cannot connect to an Azure SQL Database. The VM is in the same region as the SQL Database. The VM can ping other resources, but the SQL connection fails. The SQL Database has a firewall rule allowing the VM's private IP address. What is the most likely cause?

Accepted Answer

The firewall rule uses the VM's private IP address, but Azure SQL Database sees the VM's public IP address. When an Azure VM connects to Azure SQL Database, the source IP address seen by the SQL firewall is the VM's public outbound IP address due to Source Network Address Translation (SNAT) performed by Azure. Even if the VM is in the same region, traffic to Azure SQL Database egresses through the VM's public IP, not its private IP. Therefore, a firewall rule allowing the private IP will not match, causing the connection to fail.

Answer

The SQL Database has the public endpoint disabled

Answer

The SQL Database firewall is configured at the database level, not the server level

Answer

The VM does not have an outbound security rule allowing traffic to Azure SQL Database

Question 3

You need to automate monitoring and alerting for an Azure SQL Database. Which THREE actions can you achieve using Azure Monitor and SQL Insights?

Accepted Answer

Stream diagnostic logs to a Log Analytics workspace.. Azure Monitor and SQL Insights allow you to configure diagnostic settings to stream SQL Database metrics and resource logs (e.g., query store runtime statistics, wait statistics) to a Log Analytics workspace. This enables centralized log analysis, custom KQL queries, and long-term retention for auditing and troubleshooting.

Answer

Automatically rebuild fragmented indexes.

Answer

Automatically scale the database to a higher service tier.

Question 4

You are reviewing an ARM template for Azure SQL Database. The exhibit shows the database settings. You notice the database is not being automatically paused. What is the most likely explanation?

Accepted Answer

The database uses VBS enclaves which are incompatible with serverless. Auto-pause is only supported for General Purpose serverless databases, and the use of VBS enclave (preferredEnclaveType: "VBS") indicates Always Encrypted with secure enclaves, which is not supported with serverless. Option A is wrong because minCapacity 0.5 is valid for serverless. Option B is wrong because licenseType BasePrice does not affect auto-pause. Option C is wrong because autoPauseDelay 60 minutes is valid; the default is 60.

Answer

The minCapacity is set too low

Answer

The autoPauseDelay is set to 60 minutes

Answer

The licenseType is set to BasePrice

Question 5

You are designing a solution for storing audit logs from Azure SQL Database. The logs must be retained for 7 years and must be immutable to prevent tampering. Which Azure service should you use?

Accepted Answer

Azure Blob Storage with immutable storage policy. Azure Blob Storage with an immutable storage policy (WORM – Write Once, Read Many) is the correct choice because it ensures that audit logs cannot be modified or deleted for a specified retention period (7 years). This meets the immutability and retention requirements for compliance with regulations such as SOX or HIPAA. Azure SQL Database audit logs can be directly streamed to Azure Blob Storage, making it a seamless and secure storage solution.

Answer

Use Azure Files share with read-only permissions

Answer

Send logs to Azure Log Analytics workspace

Answer

Store logs in an audit table in Azure SQL Database

Question 6

You are configuring security for an Azure SQL Database that will be accessed by multiple applications. Each application uses a separate service principal managed in Microsoft Entra ID. You need to ensure that each service principal has the minimum required permissions to access only its own set of tables. What should you implement?

Accepted Answer

Create a contained database user for each service principal and grant SELECT, INSERT, UPDATE, DELETE on specific tables.. It creates a contained database user for each service principal (mapped to the Microsoft Entra ID identity) and grants only the specific table-level permissions (SELECT, INSERT, UPDATE, DELETE) required for that application. This follows the principle of least privilege by avoiding broad database roles and ensuring each service principal can only access its own set of tables.

Answer

Create a contained database user for each service principal and grant the db_owner role.

Answer

Create a server-level login for each service principal and assign db_datareader role in the database.

Answer

Configure a server-level firewall rule for each service principal IP address.

Question 7

You are managing an Azure SQL Database that hosts a customer relationship management (CRM) application. The database has a table named 'Contacts' with columns: ContactID (int, primary key), Name (nvarchar(100)), Email (nvarchar(200)), Phone (nvarchar(20)), and CreditLimit (decimal(18,2)). The compliance team requires that the CreditLimit column be encrypted so that only authorized users can view it. The application must be able to search for exact matches on CreditLimit values. You need to implement encryption without changing the application code significantly. Which encryption method should you use?

Accepted Answer

Always Encrypted with deterministic encryption. Always Encrypted with deterministic encryption is correct because it encrypts the CreditLimit column at the client driver level, ensuring data remains encrypted at rest and in transit, while still allowing exact-match searches (e.g., WHERE CreditLimit = 5000) since deterministic encryption always produces the same ciphertext for a given plaintext. This meets the compliance requirement without requiring significant application code changes, as the Azure SQL Database driver handles encryption and decryption transparently for authorized users.

Answer

Always Encrypted with randomized encryption

Answer

Transparent Data Encryption

Answer

Dynamic Data Masking

Question 8

Your company is deploying a new application that will use Azure SQL Database. You need to ensure that all connections to the database use Microsoft Entra ID authentication. Which step is required to enable this?

Accepted Answer

Configure an Entra ID administrator for the Azure SQL logical server.. To enforce Microsoft Entra ID authentication for all connections to Azure SQL Database, you must first configure an Entra ID administrator at the logical server level. This step establishes the server’s trust relationship with the Entra ID tenant, enabling token-based authentication using OAuth 2.0. Without this administrator, Entra ID authentication cannot be used, and only SQL authentication would be available.

Answer

Enable the Azure SQL Database firewall to allow only Entra ID IP addresses.

Answer

Create a contained database user mapped to an Entra ID identity.

Answer

Set the database to read-only mode.

Question 9

Which THREE actions can be performed by using Elastic Database Jobs in Azure SQL Database? (Choose three.)

Accepted Answer

Run a T-SQL script to update statistics across multiple databases.. Elastic Database Jobs in Azure SQL Database are designed to run T-SQL scripts against a target group of databases, so option A is correct: a job can execute a T-SQL script that runs UPDATE STATISTICS across multiple databases in the target group. Option B is correct because jobs can run T-SQL that queries catalog views such as sys.databases and sys.dm_db_resource_stats and inserts the results into a table for centralized metadata collection. Option E is correct because index maintenance, including ALTER INDEX ... REBUILD, is a T-SQL operation that can be executed by a job against every database in an elastic pool used as the job target. Option C is not correct because creating a new Azure SQL Database is a control-plane operation performed through the Azure portal, PowerShell, CLI, or REST API, not through the T-SQL execution model of Elastic Database Jobs. Option D is not correct because changing the service tier objective (SLO) is also a management/control-plane action, not a T-SQL statement that Elastic Database Jobs can run against target databases.

Answer

Create a new Azure SQL Database.

Answer

Change the service tier objective (SLO) of a database.

Question 10

You are deploying Azure SQL Database for a multi-tenant SaaS application. Each tenant has its own database, and you need to ensure that resource usage is isolated and predictable. You also need to manage performance at the tenant level. Which Azure SQL Database offering should you choose?

Accepted Answer

Azure SQL Database elastic pools with per-database min/max DTU or vCore settings.. Azure SQL Database elastic pools with per-database min/max DTU or vCore settings are the correct choice because they provide resource isolation and predictable performance at the tenant level. Elastic pools allow you to allocate a shared pool of resources across multiple databases while setting per-database minimum and maximum limits, ensuring that no single tenant can consume excessive resources and that each tenant gets a guaranteed baseline. This directly addresses the multi-tenant SaaS requirement for isolated and predictable resource usage.

Answer

Azure SQL Database serverless compute tier.

Answer

Azure SQL Database single databases with DTU-based tier.

Answer

Azure SQL Managed Instance with multiple databases.

Question 11

You are configuring automated backup retention for Azure SQL Managed Instance. The compliance policy requires that you be able to restore a database to any point within the last 90 days, and that you keep backups for a minimum of 7 years for auditing purposes. Which backup retention policy should you configure?

Accepted Answer

Set PITR retention to 35 days and configure a long-term retention (LTR) policy to keep weekly backups for 7 years.. Azure SQL Managed Instance's point-in-time restore (PITR) retention is capped at 35 days, so to meet the 90-day restore requirement, you must combine a 35-day PITR policy with a long-term retention (LTR) policy that keeps weekly full backups for 7 years. LTR allows you to restore to any point within the LTR window by using full, differential, and log backups, satisfying both the 90-day point-in-time restore and the 7-year auditing compliance.

Answer

Set point-in-time restore (PITR) retention to 90 days.

Answer

Set PITR retention to 90 days and configure geo-replication to achieve the 7-year retention.

Answer

Configure geo-redundant backup storage with a retention of 90 days.

Question 12

You are monitoring an Azure SQL Database using sys.dm_db_wait_stats. You see a high percentage of WRITELOG waits. What is the most likely cause?

Accepted Answer

The transaction log is on a slow I/O subsystem.. WRITELOG waits occur when a session is waiting for the transaction log to be written to disk, typically at commit time. A high percentage of WRITELOG waits therefore points to slow transaction log I/O — the log disk cannot keep up with the write rate, so commits stall.

Answer

Tempdb has allocation contention.

Answer

Queries are blocked by locks.

Answer

CPU is under pressure.

Question 13

Your Azure SQL Managed Instance is configured with a long-term backup retention policy of 10 years. You need to reduce storage costs while still meeting a compliance requirement to retain monthly backups for 7 years. What should you do?

Accepted Answer

Configure a new long-term retention policy that retains monthly backups for 7 years.. Azure SQL Managed Instance long-term retention (LTR) policies are defined per-database and specify separate weekly, monthly, and yearly retention periods; you can set the monthly retention to 7 years while removing or shortening the other tiers. This directly satisfies the compliance requirement (monthly backups kept 7 years) while eliminating the storage cost of the unnecessary 10-year policy. LTR backups are stored in Azure Blob storage and billed separately from the instance, so right-sizing the policy is the correct cost-optimization action.

Answer

Disable long-term backup retention and rely solely on point-in-time restore backups.

Answer

Set the point-in-time restore retention period to 7 years.

Answer

Migrate the database to Azure SQL Database and use geo-redundant storage.

Question 14

You need to automate the deployment of Azure SQL Database with a specific configuration across multiple environments (dev, test, prod). The deployment must include firewall rules, auditing settings, and threat detection policies. Which THREE tools can be used to implement this automation?

Accepted Answer

ARM templates with Bicep. ARM templates with Bicep (A) are correct because Bicep is a declarative IaC language that transpiles to ARM JSON, allowing you to define Azure SQL Database resources, firewall rules, auditing settings, and threat detection policies as code and deploy them idempotently across dev, test, and prod. Azure PowerShell (C) is correct because cmdlets such as New-AzSqlServerFirewallRule, Set-AzSqlServerAudit, and Set-AzSqlDatabaseThreatDetectionPolicy let you script the full deployment and configuration of Azure SQL Database programmatically. Azure CLI (E) is correct because commands like az sql server firewall-rule create, az sql server audit-policy update, and az sql db threat-policy update provide cross-platform scripting for the same automation. Azure Migrate (B) is not correct because it is a discovery, assessment, and migration service for on-premises workloads, not a deployment automation tool. Azure Data Studio (D) is not correct because it is an interactive query and management client, not an automation or infrastructure-as-code tool for repeatable multi-environment deployments.

Answer

Azure Migrate

Answer

Azure Data Studio

Question 15

You have an Azure SQL Database that uses automatic tuning. Which TWO benefits does automatic tuning provide?

Accepted Answer

Automatically identify and correct query plan regressions. Options B and E are correct. Automatic tuning can identify and correct query plan regressions (B) and automatically create missing indexes (E). Option A is wrong because automatic tuning does not automatically scale the service tier; that's auto-scale. Option C is wrong because automatic tuning does not automatically create read replicas. Option D is wrong because automatic tuning does not automatically update statistics.

Answer

Automatically scale up the database service tier

Answer

Automatically create read replicas

Answer

Automatically update statistics