DP-300 Practice Test 2 — 25 Questions

Question 1

You have a new Azure SQL Database. You need to ensure that all connections use TLS 1.2 or higher. What should you configure?

Accepted Answer

Set the 'minimal TLS version' to 1.2 in the server's properties in the Azure portal.. To enforce TLS 1.2 or higher for all connections to an Azure SQL Database, you must configure the 'minimal TLS version' setting at the server level in the Azure portal. This setting applies to all databases hosted on that logical server, ensuring that any client attempting to connect with a TLS version lower than 1.2 is rejected. The server-level property directly controls the TLS protocol version accepted during the SSL/TLS handshake, overriding any client-side or database-level settings.

Answer

Set the 'minimal TLS version' to 1.2 in the database's properties.

Answer

Add a firewall rule to deny connections using TLS 1.0 or 1.1.

Answer

Enable the 'Force encryption' option in the connection string and require TLS 1.2.

Question 2

You are tasked with automating the backups of multiple Azure SQL Databases to ensure long-term retention. Which ONE Azure service can be used to achieve automated backups with retention beyond the default 7-35 days?

Accepted Answer

Azure SQL Database long-term retention (LTR) backup policy. Only Azure SQL Database long-term retention (LTR) backup policy provides automated long-term backup retention for Azure SQL Database (PaaS), allowing retention beyond the default 7-35 days up to 10 years. Option C (Azure Backup for SQL Server in Azure VMs) is for SQL Server on Azure VMs (IaaS), not for Azure SQL Database, so it does not apply. The other options (A, B, D) do not provide automated backup with long-term retention for Azure SQL Database.

Answer

Azure Site Recovery

Answer

Azure Blob Storage snapshots

Answer

Azure Backup for SQL Server in Azure VMs

Answer

Azure Storage lifecycle management

Question 3

You are the database administrator for a healthcare organization that uses Azure SQL Database. You need to implement column-level encryption for a column containing patient Social Security numbers (SSNs). The SSNs must be encrypted at rest and in transit, and only authorized client applications should be able to decrypt them. Which technology should you use?

Accepted Answer

Always Encrypted with column master key stored in Azure Key Vault.. Always Encrypted is the correct choice because it ensures that sensitive data, such as SSNs, is encrypted both at rest and in transit, and the encryption keys are stored client-side (e.g., in Azure Key Vault). This design ensures that only authorized client applications with access to the column master key can decrypt the data, preventing even database administrators or cloud operators from viewing the plaintext values.

Answer

Row-level security (RLS) to restrict access based on user role.

Answer

Dynamic data masking (DDM) to mask SSNs for unauthorized users.

Answer

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

Question 4

You need to protect Azure SQL Database from SQL injection attacks. Which THREE of the following measures should you implement?

Accepted Answer

Enable Microsoft Defender for SQL to detect and alert on SQL injection.. Option A is correct because Microsoft Defender for SQL provides advanced threat protection that specifically detects anomalous and potentially harmful activities such as SQL injection attempts against Azure SQL Database, generating alerts and recommendations. Option B is correct because parameterized queries and stored procedures ensure that user input is treated as data rather than executable SQL code, which is the fundamental application-level defense against SQL injection. Option D is correct because an Azure Web Application Firewall (WAF), for example on Application Gateway or Front Door, inspects HTTP/HTTPS traffic and blocks common injection patterns before they reach the application and database. Option C is not correct because dynamic data masking only obscures sensitive data in query results for unauthorized users; it does not prevent or detect SQL injection. Option E is not correct because Transparent Data Encryption protects data at rest by encrypting database files, but it does not stop SQL injection, which exploits the application's query logic rather than the storage layer.

Answer

Implement dynamic data masking to hide sensitive data from unauthorized users.

Answer

Enable Transparent Data Encryption (TDE) to encrypt the database at rest.

Question 5

You are a database administrator for an Azure SQL Managed Instance. You need to ensure that all connections to the instance use encrypted connections. What should you configure?

Accepted Answer

Set the 'Force Encryption' option to Yes on the server properties.. Setting 'Force Encryption' to Yes on the Azure SQL Managed Instance server properties enforces the use of TLS (Transport Layer Security) for all client connections. This configuration ensures that any client attempting to connect without encryption will be rejected, thereby meeting the requirement that all connections use encrypted connections. The setting is applied at the instance level and overrides client-side encryption preferences.

Answer

Enable Transparent Data Encryption (TDE).

Answer

Enable Always Encrypted for sensitive columns.

Answer

Configure a firewall rule to allow only specific IP addresses.

Question 6

Drag and drop the steps to configure a SQL Server Agent job in Azure SQL Managed Instance to run a maintenance task in the correct order.

Accepted Answer

Connect to the instance, create a job, define steps, schedule, then enable and start.. Connect to the instance, create a job, define steps, schedule, then enable and start.

Answer

Create a job, define steps, schedule, connect to the instance, then enable and start.

Answer

Connect to the instance, define steps, create a job, schedule, then enable and start.

Answer

Connect to the instance, create a job, schedule, define steps, then enable and start.

Question 7

You are monitoring an Azure SQL Database and notice a pattern of high CPU usage during business hours. You need to identify the queries consuming the most CPU over the last 24 hours. Which dynamic management view should you query?

Accepted Answer

sys.dm_exec_query_stats. sys.dm_exec_query_stats (Option C) is the correct DMV because it returns aggregate performance statistics for cached query plans, including total CPU time (total_worker_time), execution count, and last execution time. By querying this view and ordering by total_worker_time descending, you can identify the queries that have consumed the most CPU over the last 24 hours, directly addressing the pattern of high CPU usage during business hours.

Answer

sys.dm_exec_requests

Answer

sys.dm_exec_sessions

Answer

sys.dm_os_performance_counters

Question 8

Your company is migrating several on-premises SQL Server databases to Azure. The databases range from 50 GB to 2 TB and have varying performance requirements. You need to decide which Azure SQL deployment options to use. The requirements include: 
- Minimal application changes. 
- Support for SQL Server Agent jobs. 
- Ability to scale storage independently from compute. 
- Native support for cross-database queries. 
Which TWO options meet these requirements? (Choose two.)

Accepted Answer

SQL Server on Azure Virtual Machine. SQL Server on Azure Virtual Machine (D) and Azure SQL Managed Instance (E) provide full SQL Server engine compatibility, native SQL Server Agent, cross-database queries, independent storage scaling, and minimal application changes. Azure SQL Database elastic pool (B) does not support SQL Server Agent natively (Azure Elastic Jobs is not SQL Server Agent) and its cross-database query support via elastic query is not as seamless as native; thus it does not meet all requirements. Option A (single database) lacks SQL Server Agent and cross-database queries. Option C (Azure Synapse) is not designed for OLTP and lacks SQL Server Agent and cross-database query support. Therefore, the two correct options are D and E.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database elastic pool

Answer

Azure Synapse Analytics (dedicated SQL pool)

Question 9

A company uses Azure SQL Database for a critical application. They need to automate the process of exporting a database to a storage account every night, ensuring the export is consistent. The solution must minimize administrative overhead. What should they use?

Accepted Answer

Create an Azure Automation runbook that uses the Export-AzureRmSqlDatabase cmdlet and schedule it to run nightly.. Azure Automation runbooks can execute PowerShell cmdlets like Export-AzureRmSqlDatabase (or the newer Export-AzSqlDatabase) to perform a consistent export of an Azure SQL Database to a storage account. By scheduling the runbook to run nightly, you automate the export with minimal administrative overhead, as the export operation uses database snapshots to ensure consistency without requiring complex orchestration.

Answer

Create an Elastic Database Job that runs a T-SQL script to export the database.

Answer

Deploy an Azure Data Factory pipeline with a Copy activity to export the database.

Answer

Use an Azure Logic App with the SQL Server connector to export the database.

Question 10

You are monitoring an Azure SQL Database using dynamic management views (DMVs). You run a query against `sys.dm_exec_query_stats` to find the top 10 queries by total worker time. Several queries show high worker time but low logical reads. The database is not experiencing any blocking or deadlocks. What is the most likely cause of the high worker time?

Accepted Answer

The queries are CPU-bound due to inefficient query plans.. High worker time (CPU time) with low logical reads indicates that the queries are CPU-bound rather than I/O-bound. Inefficient query plans, such as those with large hash joins, sorts, or non-sargable predicates, can cause excessive CPU consumption without generating many logical reads. Option A is incorrect because parameter sniffing typically leads to varying plan quality, but the consistent high worker time across multiple queries suggests a systematic plan efficiency issue, not necessarily parameter sniffing. Option B is incorrect because memory pressure would cause increased I/O activity (lazy writes) which is not observed with low logical reads. Option C is incorrect because transaction log writes are I/O operations and would not cause high worker time with low I/O.

Answer

The queries suffer from parameter sniffing leading to suboptimal plans.

Answer

The queries are experiencing memory pressure causing excessive lazy writes.

Answer

The queries are waiting on transaction log writes.

Question 11

You have a SQL Server on Azure VM running a mission-critical database. The VM is configured with Azure Site Recovery (ASR) for disaster recovery. During a disaster recovery drill, you notice that the recovered database is not consistent. What is the most likely cause?

Accepted Answer

Application-consistent snapshots are not enabled in the ASR replication policy.. Azure Site Recovery replicates VM disks, and by default it takes crash-consistent snapshots, which capture the disk state as if the power were cut — the database may have in-flight transactions and be inconsistent on recovery. Enabling application-consistent snapshots in the replication policy triggers VSS (Volume Shadow Copy Service) to quiesce the database and flush transactions before snapshotting, ensuring a consistent recovery point.

Answer

The secondary region is not in the same geo as the primary.

Answer

The backup retention period is too short.

Answer

The database is not part of an Always On availability group.

Question 12

Your company has a strict policy that all Azure SQL Databases must have Microsoft Defender for SQL enabled. You need to enforce this policy across all subscriptions using a scalable, automated approach. What should you do?

Accepted Answer

Create an Azure Policy initiative that includes the 'Configure Microsoft Defender for SQL to be enabled' policy and assign it to the root management group.. Azure Policy provides a scalable, automated, and continuous enforcement mechanism across all subscriptions. By creating an initiative that includes the 'Configure Microsoft Defender for SQL to be enabled' policy and assigning it to the root management group, you ensure that any new or existing subscription inherits the policy, and non-compliant resources are automatically remediated or flagged. This approach aligns with the requirement for a strict, company-wide policy without manual intervention.

Answer

Use Azure Blueprints to deploy a predefined ARM template that enables Defender for SQL.

Answer

Create a script that runs periodically to check and enable Defender for SQL on all databases.

Answer

Assign the 'SQL Security Manager' role to a central team to manually enable Defender for SQL.

Question 13

You are reviewing a JSON configuration for an Azure SQL Database. The exhibit shows the database properties. Which statement about this database is correct?

Accepted Answer

The database is in General Purpose tier and supports zone redundancy.. The JSON configuration shows zoneRedundant set to true and the service tier is General Purpose. As of current Azure SQL Database documentation, zone redundancy is supported for the General Purpose tier (vCore purchasing model) in many regions. Therefore, the configuration is valid. Option D correctly states that the database is in General Purpose tier and supports zone redundancy. Option A is incorrect because highAvailabilityReplicaCount is 0, not 1. Option B is incorrect because zone redundancy is available for General Purpose. Option C is incorrect because backupStorageRedundancy is Geo (geo-redundant), not locally-redundant.

Answer

The database has one high-availability replica.

Answer

The configuration is invalid because General Purpose tier cannot be zone-redundant.

Answer

The database uses locally-redundant backup storage.

Question 14

You manage a SQL Managed Instance in the East US region. The instance must be recoverable within 1 hour in the event of a regional disaster. You need to configure a secondary replica in a paired region with automatic failover. Which solution meets the requirement?

Accepted Answer

Create an auto-failover group with a secondary replica in West US.. An auto-failover group in Azure SQL Managed Instance provides a secondary replica in a paired region with automatic failover, meeting the requirement for recovery within 1 hour. It uses asynchronous replication and allows a single connection string to fail over automatically, ensuring business continuity during a regional disaster.

Answer

Configure log shipping to a secondary instance in West US.

Answer

Enable geo-redundant backup storage and use geo-restore.

Answer

Deploy an Always On availability group with a synchronous secondary in West US.

Question 15

You have an Azure SQL Database that is part of an elastic pool. You notice that the pool's eDTU consumption is consistently high, and some databases are experiencing resource contention. You need to ensure that a critical database always gets a minimum amount of resources. What should you configure?

Accepted Answer

Configure per-database min eDTU for the critical database. In an Azure SQL elastic pool, per-database min eDTU (or min vCore) guarantees a floor of resources that a specific database can always consume, even when other databases in the pool are competing for resources. Setting a min eDTU on the critical database ensures it is never starved during contention. This directly addresses the requirement that the critical database 'always gets a minimum amount of resources.'

Answer

Configure per-database max eDTU for the critical database

Answer

Increase the eDTU of the elastic pool

Answer

Move the critical database to a dedicated service tier

Question 16

You need to automate the deployment of schema changes to multiple Azure SQL Databases in different regions. The solution must support rollback and version control. Which technology should you use?

Accepted Answer

Use Azure DevOps with a database project and release pipelines.. Azure DevOps with a database project and release pipelines is the correct choice because it provides source control for schema changes, automated deployment across multiple environments, and built-in rollback capabilities through pipeline versioning and deployment history. This approach aligns with infrastructure-as-code principles, enabling consistent, repeatable, and auditable schema deployments to Azure SQL Databases in different regions.

Answer

Use Azure Data Factory to run stored procedures for schema changes.

Answer

Use SQL Server Agent jobs to run deployment scripts on schedule.

Answer

Use Azure Automation with PowerShell scripts to execute T-SQL scripts.

Question 17

You administer an Azure SQL Database that uses the General Purpose tier. Users report that queries are slow during peak hours. You need to identify if the slow performance is due to log write latency. Which metric should you examine in Azure Monitor?

Accepted Answer

Log write latency. The correct metric to examine is Log write latency (option D), as it directly measures the time taken to write to the transaction log, which can indicate slow performance due to log write latency. Option A (Log IO percent) measures the percentage of log throughput used, not latency. Option B (Transaction log usage) measures log file space used, not performance. Option C (Average IO latency) includes both data and log I/O, so it is not specific to log writes.

Answer

Log IO percent

Answer

Transaction log usage

Answer

Average IO latency

Question 18

You manage an Azure SQL Database that is part of a business-critical application. You need to ensure that network traffic between the application hosted on Azure VMs and the database is encrypted and does not traverse the public internet. What should you configure?

Accepted Answer

Create a private endpoint for Azure SQL Database in the same virtual network as the application VMs.. Creating a private endpoint for Azure SQL Database places the database service on a private IP address within the same virtual network as the application VMs. This ensures that all traffic between the VMs and the database stays entirely within the Microsoft Azure backbone network, never traversing the public internet, while also providing encryption in transit via TLS by default.

Answer

Use TLS 1.2 for all connections to the database.

Answer

Configure server-level firewall rules to allow only the application VM IP addresses.

Answer

Enable forced tunneling on the application VMs to route all traffic through the on-premises network.

Question 19

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is frequently throttled. Which configuration change can help reduce log rate throttling?

Accepted Answer

Increase the log rate limit by scaling up the service level objective. In Azure SQL Database Hyperscale, log rate is governed by the service level objective, and when the log generation rate exceeds the limit the workload is throttled. Scaling up to a higher service level objective raises the log rate limit, directly relieving the throttling. This is the documented remedy for log rate throttling in Hyperscale.

Answer

Increase max degree of parallelism

Answer

Reduce backup retention period

Answer

Add more compute replicas

Question 20

Which TWO of the following are supported high availability features in Azure SQL Managed Instance?

Accepted Answer

Zone-redundant deployment for Business Critical tier. Option A is correct because Azure SQL Managed Instance in the Business Critical service tier supports zone-redundant deployment, which places the primary and secondary replicas across different availability zones to protect against datacenter-level failures. Option C is correct because SQL Managed Instance includes a built-in Always On Availability Groups mechanism that automatically provisions and manages replicas for high availability, with the Business Critical tier using local SSD and multiple secondary replicas. Database mirroring (B) is a deprecated SQL Server feature and is not offered as a supported HA option in Azure SQL Managed Instance. Log shipping to a secondary instance (D) is not a built-in HA feature of SQL Managed Instance, though it can be configured manually for disaster recovery scenarios. Windows Server Failover Clustering (E) is an underlying infrastructure concept used by SQL Server on-premises and Azure VMs, but it is not exposed or supported as a user-configurable HA feature in Azure SQL Managed Instance.

Answer

Database mirroring

Answer

Log shipping to a secondary instance

Answer

Windows Server Failover Clustering