DP-300 Practice Questions 1 — 50 Questions

Question 1

A company uses Azure SQL Database and wants to automate the process of refreshing a development database from production backups weekly. Which Azure service should be used to orchestrate this process including restore and post-restore scripts?

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

Accepted Answer

Azure Automation with PowerShell runbooks. Azure Automation with PowerShell runbooks is designed for orchestrating scheduled administrative tasks across Azure resources, including database restore operations and post-restore T-SQL scripts. It supports credentials, schedules, and integration with Azure SQL via the SqlServer module, making it the right tool for weekly refresh automation.

Answer

Elastic Database Jobs

Answer

Azure Logic Apps

Answer

Azure Data Factory

Question 2

You need to audit all successful and failed login attempts to an Azure SQL Database. Which feature should you enable?

Accepted Answer

Azure SQL Auditing. Azure SQL Auditing is the correct feature because it tracks database events, including both successful and failed login attempts, and writes them to an audit log in your Azure Storage account, Log Analytics workspace, or Event Hubs. This allows you to monitor and review authentication activity for compliance and security analysis. Other features like Advanced Threat Protection, TDE, and Vulnerability Assessment do not capture login event logs.

Answer

Advanced Threat Protection

Answer

Transparent Data Encryption (TDE)

Answer

SQL Vulnerability Assessment

Question 3

You have an Azure SQL Database in the General Purpose tier. You notice that the log write throughput is consistently above the service tier limit, causing transaction throttling. You need to resolve this without moving to Business Critical. What should you do?

Accepted Answer

Batch transactions and reduce log writes.. Batching transactions reduces the number of log write operations, lowering the log write throughput below the service tier limit and avoiding throttling. Option A is incorrect because increasing the max log size does not affect the log write rate; it only provides more storage. Option C is incorrect because accelerated database recovery (ADR) improves recovery time and may reduce log I/O for crash recovery, but it does not directly reduce the sustained log write throughput from transaction processing. Option D is incorrect because the requirement explicitly states not to move to Business Critical.

Answer

Increase the max log size using ALTER DATABASE.

Answer

Enable accelerated database recovery to reduce log I/O.

Answer

Move to Business Critical tier.

Question 4

You are configuring Azure SQL Database for an e-commerce application that experiences variable traffic. You need to ensure that the database can automatically scale resources based on demand without manual intervention. The solution must also support scaling to zero compute when not in use to save costs. Which Azure SQL Database offering should you use?

Accepted Answer

Azure SQL Database serverless.. Azure SQL Database serverless is the correct choice because it automatically scales compute resources based on demand and supports pausing the database when idle, effectively scaling to zero compute to save costs. This aligns perfectly with the requirements of variable traffic and cost optimization without manual intervention.

Answer

Azure SQL Database elastic pool.

Answer

Azure SQL Database Hyperscale.

Answer

Azure SQL Database Business Critical tier.

Question 5

You need to monitor the storage space usage of an Azure SQL Database over time. Which tool should you use?

Accepted Answer

Azure SQL Analytics (Azure Monitor). Azure SQL Analytics in Azure Monitor provides historical storage metrics. Option A is wrong because Intelligent Insights is for proactive diagnostics, not storage monitoring. Option C is wrong because Query Store focuses on query performance. Option D is wrong because SSMS does not provide historical monitoring.

Answer

Intelligent Insights

Answer

Query Store

Answer

SQL Server Management Studio (SSMS)

Question 6

Drag and drop the steps to configure transparent data encryption (TDE) for an Azure SQL Database using a customer-managed key in Azure Key Vault in the correct order.

Accepted Answer

Create Azure Key Vault and key, then grant Key Vault permissions to the Azure SQL Database server, then enable TDE with customer-managed key, then select the key and save.. First set up Key Vault with a key, grant permissions, then enable TDE with customer-managed key, select the key, and save.

Answer

Grant Key Vault permissions to the Azure SQL Database server, then create Azure Key Vault and key, then enable TDE with customer-managed key, then select the key and save.

Answer

Enable TDE with customer-managed key, then create Azure Key Vault and key, then grant Key Vault permissions to the Azure SQL Database server, then select the key and save.

Answer

Create Azure Key Vault and key, then enable TDE with customer-managed key, then grant Key Vault permissions to the Azure SQL Database server, then select the key and save.

Question 7

You run the query in the exhibit on an Azure SQL Database. The result shows high wait_time_ms for PAGEIOLATCH_SH waits. What does this indicate?

Accepted Answer

I/O subsystem bottleneck for read operations. PAGEIOLATCH_SH waits indicate that a query is waiting for a data page to be read from disk into the buffer pool, which is an I/O operation. High wait_time_ms for this wait type typically points to an I/O subsystem bottleneck for read operations, making option A correct. Option B (CPU bottleneck) is incorrect because PAGEIOLATCH_SH is related to I/O, not CPU. Option C (blocking) is incorrect because blocking is associated with LOCK waits, not PAGEIOLATCH_SH. Option D (memory pressure) is incorrect; while memory pressure can increase physical I/O, the wait type itself specifically indicates I/O latency for reading pages from disk.

Answer

CPU bottleneck

Answer

Blocking between concurrent transactions

Answer

Memory pressure

Question 8

Your Azure SQL Database is experiencing a sudden increase in wait time due to PAGEIOLATCH_SH waits. What should you do to reduce these waits?

Accepted Answer

Add appropriate indexes to reduce table scans. PAGEIOLATCH_SH waits indicate I/O bottlenecks caused by excessive page reads from disk. Adding appropriate indexes reduces the number of pages read by enabling more efficient data access (e.g., index seeks instead of table scans), directly reducing I/O. Option A is incorrect because increasing max memory does not address the underlying query inefficiency driving I/O. Option C is incorrect because page compression reduces storage but may increase CPU and does not primarily reduce I/O waits. Option D is incorrect because forcing parameterization improves plan reuse but does not target I/O reduction.

Answer

Increase the database max memory

Answer

Enable page compression on large tables

Answer

Force parameterization of queries

Question 9

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'

Question 10

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 11

You administer a SQL Managed Instance in the West Europe region. You need to create a disaster recovery replica in North Europe with automated failover. The replica must be readable and support backups. What should you configure?

Accepted Answer

Create a failover group with the secondary instance in North Europe.. SQL Managed Instance supports failover groups for automated failover between regions, and the secondary instance in a failover group is readable and supports backups. Option A is incorrect because log shipping is not supported for SQL Managed Instance. Option B is incorrect because active geo-replication is not supported for SQL Managed Instance; failover groups are the appropriate solution. Option C is incorrect because the secondary instance in a failover group is readable and can be used for read-only workloads.

Answer

Set up log shipping from West Europe to North Europe.

Answer

Configure active geo-replication between the instances.

Answer

Create a failover group, but note the secondary is not readable.

Question 12

You are reviewing the ARM template snippet for an Azure SQL Database failover group. The primary server is in East US. The secondary is in West Europe. The readWriteEndpoint has automatic failover with a grace period of 60 minutes. The readOnlyEndpoint is disabled. After a complete outage in East US, what will happen?

Accepted Answer

The failover group will wait 60 minutes, then automatically fail over to West Europe.. With automatic failover enabled on the read/write endpoint and a 60-minute grace period, Azure SQL failover groups wait the full grace period after detecting the primary outage before promoting the secondary. Only after that window elapses does the geo-secondary in West Europe become the new primary. The readOnlyEndpoint setting is irrelevant to read/write failover behavior.

Answer

The failover group will not fail over automatically because the grace period is too long.

Answer

The failover group will immediately fail over to West Europe.

Answer

The failover group requires manual failover because readOnlyEndpoint is disabled.

Question 13

Which THREE of the following are best practices for managing keys in Azure Key Vault for use with Azure SQL Database TDE?

Accepted Answer

Enable soft-delete and purge protection on the Key Vault.. Enabling soft-delete and purge protection on the Key Vault is a best practice because soft-delete retains deleted keys for a configurable retention period (default 90 days), allowing recovery if a key is accidentally deleted. Purge protection prevents permanent deletion of keys even after the soft-delete retention period expires, which is critical for TDE because if the key is permanently lost, the encrypted database becomes inaccessible. Together, these features ensure that the TDE protector key is never irrevocably lost, maintaining database recoverability and compliance.

Answer

Store the Key Vault in the same resource group as the SQL server.

Answer

Disable Key Vault auditing to reduce costs.

Question 14

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database. The template configures backup retention. What is the effect of this configuration?

Accepted Answer

Point-in-time restore (PITR) backups are retained for 7 days, and differential backups occur every 12 hours.. The ARM template configures the backup retention settings for Azure SQL Database. By default, Azure SQL Database automatically performs full backups every week, differential backups every 12 hours, and transaction log backups every 5–10 minutes. The configuration shown sets the point-in-time restore (PITR) retention period to 7 days, meaning you can restore the database to any point within the last 7 days. Differential backups occur every 12 hours to support efficient PITR, but the retention setting directly controls how far back you can perform a point-in-time restore.

Answer

Full backups are taken every 12 hours and retained for 7 days.

Answer

Long-term retention (LTR) is set to 7 days.

Answer

Transaction log backups are taken every 12 hours.

Question 15

You are configuring managed backup for an Azure SQL Managed Instance as shown in the exhibit. What is the purpose of this configuration?

Accepted Answer

To configure automated backups of the managed instance to Azure Blob Storage.. The configuration shown in the exhibit is for managed backup, which automatically schedules and stores full, differential, and transaction log backups of the Azure SQL Managed Instance to Azure Blob Storage. This ensures that backups are retained and available for restore operations without manual intervention, fulfilling the core purpose of automated backups to Azure Blob Storage.

Answer

To enable geo-replication for the managed instance.

Answer

To set up disaster recovery across Azure regions.

Answer

To configure point-in-time restore for the managed instance.

Question 16

You have an Azure SQL Managed Instance configured with a failover group to a secondary region. During a regional outage, the failover group automatically fails over to the secondary. After the primary region is restored, you need to bring the primary back online and re-establish the failover relationship. What should you do?

Accepted Answer

Initiate a planned failover from the current primary to the original primary.. After an automatic failover, the original primary becomes the secondary. To restore the original configuration, you perform a planned failover from the current primary (the former secondary) back to the original primary. This re-establishes the original roles and the failover group remains intact. A planned failover ensures no data loss by synchronizing the databases before switching roles.

Answer

Delete the failover group and recreate it with the original primary as the primary.

Answer

Add the original primary as a new secondary to the current primary.

Answer

Remove the original primary from the failover group and re-add it.

Question 17

Your organization uses Azure SQL Database and needs to automate email notifications when a database reaches 80% storage usage. Which native Azure feature can you use?

Accepted Answer

Create an Azure Monitor alert rule on the 'storage_percent' metric with an email action group.. Azure Monitor alert rules on the 'storage_percent' metric with an email action group are the native Azure feature for this requirement. Azure SQL Database emits the storage_percent metric to Azure Monitor, and alert rules can trigger when it exceeds 80%, invoking an action group that sends email. This is fully managed, requires no SQL Agent, and works for both single databases and elastic pools.

Answer

Create a SQL Agent alert that fires when the storage is above 80% and sends an email.

Answer

Configure Database Mail to send alerts automatically.

Answer

Create an Elastic Database Job that checks storage and sends email via sp_send_dbmail.

Question 18

Which TWO of the following are best practices for securing Azure SQL Database?

Accepted Answer

Enable firewall rules to restrict access to specific IP addresses.. Option D is correct because Azure SQL Database firewall rules (server-level and database-level) restrict inbound connections to approved IP address ranges, reducing the exposed attack surface by denying traffic from unknown sources. Option E is correct because Microsoft Entra ID (now Microsoft Entra ID) authentication centralizes identity management, supports MFA and conditional access, and eliminates the risk of weak or shared SQL logins and passwords. Option A is incorrect because Auditing records and logs activity for compliance and forensics; it does not block or prevent malicious queries. Option B is incorrect because Transparent Data Encryption encrypts data at rest (and backups) and does nothing to stop SQL injection, which is an application-layer input validation issue. Option C is incorrect because SQL authentication with complex passwords is a weaker, legacy approach compared to Entra ID authentication, and complex passwords alone do not constitute a best practice for securing Azure SQL Database.

Answer

Enable Auditing to block malicious queries.

Answer

Enable TDE to prevent SQL injection attacks.

Answer

Use SQL authentication with complex passwords.

Question 19

Your organization requires that all Azure SQL Database administrators use multi-factor authentication (MFA) when connecting. Which authentication method must be used?

Accepted Answer

Microsoft Entra ID authentication with Conditional Access policy. Microsoft Entra ID authentication combined with a Conditional Access policy is required to enforce multi-factor authentication (MFA) for Azure SQL Database administrators. Conditional Access policies can mandate MFA as a condition for authentication, which is not possible with SQL Server authentication, certificate-based authentication, or Windows authentication alone. This method integrates with Microsoft Entra ID (formerly Azure AD) to provide the necessary security controls.

Answer

SQL Server authentication

Answer

Certificate-based authentication

Answer

Windows authentication

Question 20

You are tasked with automating index maintenance for an Azure SQL Database. Which Azure service should you use to run T-SQL scripts on a recurring schedule?

Accepted Answer

Elastic Database Jobs. Elastic Database Jobs (B) is the correct service for automating T-SQL script execution across Azure SQL Database on a recurring schedule. It is specifically designed for Azure SQL Database and Azure SQL Managed Instance, providing a job scheduler that can run T-SQL scripts against multiple databases, handle retries, and manage job history. SQL Server Agent is not available in Azure SQL Database (only in SQL Server on-premises or Azure SQL Managed Instance), making Elastic Database Jobs the appropriate choice for this PaaS scenario.

Answer

SQL Server Agent

Answer

Azure Automation Runbook

Answer

Azure Logic Apps