DP-300 Practice Test 11 — 15 Questions

Question 1

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?

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

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 2

Which THREE metrics should you monitor to detect a memory pressure issue in Azure SQL Database?

Accepted Answer

avg_page_life_expectancy. B (avg_page_life_expectancy) is correct because it measures how long (in seconds) a data page stays in the buffer pool before being evicted. A low value (typically below 300 seconds) indicates that pages are being flushed quickly due to memory pressure, forcing more physical I/O. This is a direct indicator of insufficient memory for the buffer cache.

Answer

avg_io_stall_ms

Answer

log_bytes_used

Question 3

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?

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

Accepted Answer

Create an Azure Automation runbook with PowerShell that uses the Az.Sql module to perform failover and log to a table.. Azure Automation runbooks can be scheduled to run monthly and use the Az.Sql module to perform a planned failover of the failover group and log results to a table. Although this relies on an external Azure service, it is the only viable option to automate failover for a failover group because T-SQL does not support failover group failover. Option A is incorrect because Elastic Database Jobs are not available for Managed Instance. Option C is incorrect because Data Factory is not designed for failover orchestration. Option D is incorrect because the ALTER AVAILABILITY GROUP command cannot be used to failover a failover group in Azure SQL Managed Instance; failover groups require Azure-level operations.

Answer

Use Elastic Database Jobs to run T-SQL that initiates failover and logs results.

Answer

Use Azure Data Factory to execute a stored procedure that performs failover.

Answer

Create a SQL Agent job with T-SQL that performs the planned failover using ALTER AVAILABILITY GROUP and logs the results to a table.

Question 4

You need to implement a disaster recovery solution for a SQL Server on Azure Virtual Machine using SQL Server Always On Availability Groups. The secondary replica must be in a different Azure region. What is the minimum number of VMs required to achieve automatic failover?

Accepted Answer

2. (2 VMs). To achieve automatic failover, an Always On Availability Group requires at least two SQL Server replicas (primary and secondary) configured for synchronous-commit mode with automatic failover. A two-node Windows Server Failover Cluster also requires a witness (cloud witness or file share witness) to maintain quorum during automatic failover, but that witness does not require an additional VM. Therefore, the minimum number of VMs remains 2. Option A (1 VM) offers no high availability. Option B (3 VMs) exceeds the minimum. Option C (4 VMs) is unnecessary.

Answer

1

Answer

3

Answer

4

Question 5

You manage an Azure SQL Database that is accessed by several applications. You need to implement the principle of least privilege for database access. Which three actions should you take? (Choose three.)

Accepted Answer

Create contained database users instead of server-level logins.. Contained database users are authenticated directly within the database, independent of the server-level logins. This aligns with the principle of least privilege by avoiding the need for server-level permissions, which would grant broader access across the server. In Azure SQL Database, contained users are the recommended approach for database-level access control, as they limit the blast radius of a compromised credential to a single database.

Answer

Add users to the db_datareader role.

Answer

Configure firewall rules to restrict IP addresses.

Question 6

A company has an Azure SQL Database that is experiencing performance degradation during peak hours. The database is configured with the Standard tier (S2). Which action should you recommend to improve performance without changing the application code?

Accepted Answer

Scale up the database to a higher service objective (e.g., S3).. Scaling up to a higher service objective (e.g., S3) increases DTUs, providing more resources to handle peak loads without requiring any application code changes. Option B is wrong: Query Store aids in performance monitoring and troubleshooting but does not directly improve performance. Option C is wrong: read scale-out offloads read-only workloads to a readable secondary, but the issue is general performance degradation, not specifically read-heavy. Option D is wrong: creating nonclustered indexes may improve query performance but often requires application or query adjustments, and does not guarantee improvement without code changes.

Answer

Enable Query Store and run the Performance Dashboard.

Answer

Enable read scale-out to offload read queries.

Answer

Create nonclustered indexes on all tables.

Question 7

Which TWO methods can be used to automatically restart an Azure SQL Database after a maintenance operation?

Accepted Answer

Azure CLI az sql db pause/resume. The Azure CLI `az sql db pause/resume` can be scripted and automated, effectively restarting the database. Option E is also correct because an Azure Automation runbook can use the `Start-AzSqlDatabase` cmdlet (and corresponding `Stop-AzSqlDatabase`) to stop and start the database, which achieves an automated restart. Options B, C, and D are incorrect: B is manual via the portal, C is manual via SSMS, and D's `ALTER DATABASE SET ONLINE` does not restart the database.

Answer

Azure Portal stop/start

Answer

SQL Server Management Studio (SSMS) restart command

Answer

Elastic Database Job with ALTER DATABASE SET ONLINE

Question 8

You have an Azure SQL Managed Instance that needs to be accessed from an on-premises application. The connection must be encrypted and the on-premises network uses an ExpressRoute circuit. You need to configure the managed instance to only accept connections from the on-premises network. What should you do?

Accepted Answer

Connect the on-premises network to the managed instance's virtual network using ExpressRoute and configure network security group (NSG) rules to restrict inbound traffic to the on-premises subnet.. Azure SQL Managed Instance is deployed into a virtual network, and the most secure way to allow only on-premises traffic is to extend the on-premises network into that VNet via ExpressRoute (private peering) and then use Network Security Group (NSG) rules to restrict inbound connections to only the on-premises subnet. This ensures the connection remains private, encrypted (via the ExpressRoute circuit), and does not expose a public endpoint.

Answer

Enable a public endpoint and restrict access using a firewall rule.

Answer

Configure a server-level firewall rule to allow the on-premises IP range.

Answer

Create a private endpoint for the managed instance and configure DNS.

Question 9

You query the sys.dm_geo_replication_link_status dynamic management view for an Azure SQL Database configured with active geo-replication. The exhibit shows the output. What does this indicate about the replication health?

Accepted Answer

The secondary is fully synchronized with minimal lag.. The replicationLag of 5 seconds is minimal and normal for active geo-replication, indicating the secondary is nearly synchronized. The replicationState 'CATCH_UP' confirms that the secondary is fully caught up with the primary. Options B, C, and D are incorrect: B is wrong because a secondary role is expected in geo-replication; C is wrong because 5 seconds lag is not a problem; D is wrong because the state is CATCH_UP, not SEEDING.

Answer

The secondary role indicates a failover has occurred.

Answer

The secondary is 5 seconds behind, indicating a problem.

Answer

The replication is in the seeding phase.

Question 10

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 11

Your organization uses Azure SQL Database Hyperscale tier. You need to ensure that if the primary region fails, the database can be failed over to a secondary region with minimal data loss. What should you configure?

Accepted Answer

Set up active geo-replication to a secondary region.. Active geo-replication to a secondary region provides low RPO (typically 5-10 seconds) by continuously replicating changes asynchronously. This minimizes data loss during a regional failure. Option A (geo-restore) relies on geo-redundant backups and has an RPO of hours, not minimal data loss. Option B (zone redundancy) protects within a region, not across regions. Option D (auto-failover group within same region) does not handle region failure.

Answer

Use geo-restore from geo-redundant backups.

Answer

Enable zone redundancy on the Hyperscale database.

Answer

Configure an auto-failover group with a secondary in the same region.

Question 12

You are designing a disaster recovery plan for an Azure SQL Database that is used by a critical application. The database is currently in the West US region. You need to ensure that if a regional outage occurs, the database can be failed over to another region with minimal data loss. The solution must also minimize costs for the secondary replica. Which deployment option should you recommend?

Accepted Answer

Configure active geo-replication to a secondary in East US using a lower service tier.. Active geo-replication allows you to create a readable secondary database in a different region (East US) with a lower service tier, which reduces costs while still providing a disaster recovery target. In the event of a regional outage, you can manually initiate a failover to the secondary, and because replication is asynchronous, data loss is limited to the replication lag (typically a few seconds). This meets the requirement of minimal data loss and cost minimization.

Answer

Deploy an auto-failover group with a secondary in East US using the same service tier.

Answer

Deploy a zone-redundant database in West US with a failover group to East US.

Answer

Use a failover group with a secondary in East US and enable zone redundancy on both.

Question 13

You are responsible for securing Azure SQL Managed Instance. You need to ensure that only encrypted connections are allowed and that all client connections use the latest TLS protocol. Which TWO configurations should you implement?

Accepted Answer

Set the 'Minimal TLS Version' to 1.2.. Options A and D are correct. Setting 'Minimal TLS Version' to 1.2 ensures that only encrypted connections using TLS 1.2 or higher are accepted, enforcing the latest protocol. Enabling 'Force SSL' ensures that all client connections are encrypted. Option B is incorrect because firewall rules control network access, not encryption. Option C is incorrect because Microsoft Defender for SQL provides security threat detection, not encryption enforcement. Option E is incorrect because Transparent Data Encryption (TDE) encrypts data at rest, not in transit.

Answer

Configure a firewall rule to allow only specific IP addresses.

Answer

Enable Microsoft Defender for SQL.

Answer

Enable Transparent Data Encryption (TDE).

Question 14

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert that resource usage has exceeded 90% for the past hour. What is the recommended first step to troubleshoot?

Accepted Answer

Review the Intelligent Insights root cause analysis report.. Intelligent Insights provides a root cause analysis report that can help identify the issue. Option A is wrong because scaling up should only be done after identifying the root cause. Option B is wrong because checking wait statistics is lower-level; Intelligent Insights already provides a diagnosis. Option C is wrong because Query Store may not be enabled by default and is not the first step.

Answer

Scale up the database to a higher service tier immediately.

Answer

Check sys.dm_os_wait_stats for high wait types.

Answer

Enable the Query Store and wait for data collection.

Question 15

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 returns currently executing requests, including their start time and elapsed time, which can be used to identify queries running longer than 30 seconds. Option B (sys.dm_db_resource_stats) is incorrect because it provides resource consumption metrics (CPU, IO, memory) over time, not currently running queries. Option C (sys.dm_exec_sessions) is incorrect because it shows active sessions but does not include query execution details like elapsed time. Option D (sys.dm_exec_query_stats) is incorrect because it contains aggregated query performance statistics, not currently running queries.

Answer

sys.dm_db_resource_stats

Answer

sys.dm_exec_sessions

Answer

sys.dm_exec_query_stats