DP-300 Practice Test 40 — 15 Questions

Question 1

You manage an Azure SQL Database named OrdersDB in the East US region. The business requires that OrdersDB be readable from a secondary region during a planned regional failover, and that the failover be initiated manually by a database administrator. You create a failover group and add OrdersDB as a member. Which read-write listener endpoint should applications use to connect to OrdersDB after a manual failover to the secondary region?

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

Accepted Answer

The read-write listener endpoint of the failover group.. A failover group provides a read-write listener endpoint that automatically redirects connections to the current primary database after a failover. Applications use this listener rather than server-specific names or region-specific endpoints, so connectivity is maintained without connection string changes. The read-only listener is only for read-only workloads, and private endpoint IPs are region-bound and unsuitable for cross-region failover.

Answer

The original server name of OrdersDB in the East US region.

Answer

The read-only listener endpoint of the failover group.

Answer

The private endpoint IP address of OrdersDB in the East US region.

Question 2

You are troubleshooting a performance issue in an Azure SQL Database. The database is in the Hyperscale service tier. You observe that read queries are slow, and you suspect that a specific query plan is causing excessive physical reads. You want to identify the query and its plan, and then force a better plan if available. Which tool should you use to capture and analyze the plan, and then force a plan?

Accepted Answer

Query Store, using the Top Resource Consuming Queries report and plan forcing.. Query Store is the appropriate tool because it captures query plans and performance data over time, even in the Hyperscale tier. It enables you to identify the query with excessive physical reads, review its plan history, and force a better plan. Other tools either lack historical data or do not support plan forcing.

Answer

Extended Events session with sql_batch_completed and query_post_execution_showplan events.

Answer

Dynamic management views sys.dm_exec_requests and sys.dm_exec_sql_text.

Answer

Azure SQL Database automatic tuning with CREATE_INDEX.

Question 3

You manage an Azure SQL Database with the General Purpose service tier. The database experiences performance degradation during peak hours. You enable automatic tuning and want to ensure that the database automatically corrects plan regressions caused by parameter sniffing. Which automatic tuning option should you enable?

Accepted Answer

FORCE LAST GOOD PLAN. Enabling FORCE LAST GOOD PLAN automatic tuning allows Azure SQL Database to automatically detect and mitigate plan regressions caused by parameter sniffing. When a query plan regresses, the feature forces the last known good plan, restoring performance without manual intervention. This is the precise mechanism for the described issue.

Answer

DROP INDEX

Answer

CREATE INDEX

Answer

AUTO_UPDATE_STATISTICS

Question 4

You manage an Azure SQL Database that hosts a reporting workload. Users report that a monthly aggregation query sometimes completes in 2 seconds, but other times takes over 60 seconds, even though the underlying data volume is unchanged. Query Store shows the query has two distinct plans, and the faster plan is not always chosen. You need to force the faster plan for this query. What should you do?

Accepted Answer

Use Query Store to force the specific fast plan for the query.. The scenario describes plan variability for a specific query, and Query Store already shows two plans. The most direct and supported way to stabilize performance is to force the faster plan via Query Store. Automatic tuning may help but is not targeted, plan guides with USE PLAN are unsupported in Azure SQL Database, and changing cardinality estimation is a broad change that does not guarantee the desired plan.

Answer

Create a plan guide using sp_create_plan_guide with the OPTION (USE PLAN) hint.

Answer

Enable automatic tuning with the FORCE_LAST_GOOD_PLAN option.

Answer

Enable the LEGACY_CARDINALITY_ESTIMATION database scoped configuration.

Question 5

You support an Azure SQL Database that uses the General Purpose service tier. A nightly ETL process writes millions of rows, and during the load the database occasionally reports error 40501, 'The service is currently busy.' You need to reduce the chance that the ETL job is throttled while keeping the same service tier. What should you do?

Accepted Answer

Implement retry logic with exponential backoff in the ETL application to handle transient throttling.. Resource governance throttling in Azure SQL Database is transient by design; the recommended handling is to retry with backoff rather than change storage or recovery settings. Because the constraint is to remain on General Purpose, the ETL must tolerate brief throttling. Retry logic directly addresses the transient nature of error 40501.

Answer

Increase the database's max size so that the log file can grow without hitting the storage limit.

Answer

Enable Accelerated Database Recovery on the database to reduce log write volume during the bulk load.

Answer

Move the database to the Hyperscale service tier, which removes all resource governance limits.

Question 6

You administer an Azure SQL Managed Instance that hosts a mission-critical OLTP database. The instance has 16 vCores and uses the Business Critical service tier. Users report periodic stalls during index maintenance. You observe high PAGEIOLATCH_SH waits and want to reduce their impact without changing the service tier. What should you do first?

Accepted Answer

Reduce the frequency or scope of index maintenance operations.. PAGEIOLATCH_SH waits indicate that sessions are waiting for data pages to be read from storage. Aggressive or overly frequent index maintenance forces large scans that flush the buffer pool and generate heavy physical reads. Reducing the frequency or scope of index rebuilds and reorganizations decreases this I/O pressure, directly mitigating the waits without altering the service tier or making invasive schema changes.

Answer

Change the database to the General Purpose service tier.

Answer

Enable In-Memory OLTP for the tables involved in index maintenance.

Answer

Increase the max server memory setting for the instance.

Question 7

You administer an Azure SQL Database that hosts an order-entry application. The database is in the General Purpose tier and uses the default configuration. Users complain that some inserts and updates occasionally wait for several seconds. You observe that the database's log write throughput is frequently near its tier limit, and that many small transactions are committed one row at a time. You need to reduce log write pressure without changing the service tier. What should you do?

Accepted Answer

Batch multiple row modifications into a single transaction to reduce the number of log flush operations.. Log write throughput is consumed per committed transaction because each commit forces a log flush. Many small single-row commits generate far more flushes than a few batched transactions containing the same rows. Batching reduces the number of flushes and lowers log write pressure, which addresses the bottleneck without changing the service tier.

Answer

Enable read committed snapshot isolation so that readers do not block writers.

Answer

Increase the database's max size to allow the transaction log to grow larger before truncation.

Answer

Change the database to the Business Critical tier so that log writes use local SSD.

Question 8

You have an Azure SQL Managed Instance named MI1 in the West Europe region. The instance hosts a mission-critical database that must be recoverable in a different region within 15 minutes of a regional outage. The solution must minimize data loss and administrative overhead. You need to implement a disaster recovery solution. What should you do?

Accepted Answer

Configure an auto-failover group between MI1 and a secondary managed instance in another region.. Auto-failover groups are the recommended disaster recovery feature for Azure SQL Managed Instance. They enable automatic failover to a secondary instance in another region, ensuring rapid recovery and minimal data loss with low administrative effort. Geo-redundant backups and replication methods do not provide the required RTO and automation.

Answer

Enable geo-redundant backup storage for MI1.

Answer

Create a transactional replication subscription to a SQL Server on an Azure VM in another region.

Answer

Use Azure Site Recovery to replicate the managed instance to another region.

Question 9

You are responsible for an Azure SQL Database that supports an order-processing application. The database is configured with the General Purpose service tier. During month-end processing, the application experiences slow response times. You need to determine whether the performance issue is caused by the database reaching its resource limits. Which metric should you monitor in Azure Monitor?

Accepted Answer

CPU percentage. In the vCore purchasing model, CPU percentage is the primary metric that indicates whether the database is approaching its compute limit. During month-end batch processing, sustained high CPU percentage points to insufficient vCores or inefficient queries, guiding you to scale up or optimize. DTU percentage is only relevant for DTU-based tiers, and other metrics do not directly show overall resource saturation.

Answer

Sessions count

Answer

Log write wait time

Answer

DTU percentage

Question 10

You need to migrate an on-premises SQL Server 2017 database to Azure SQL Database. The database contains a table with a column of data type geography and uses cross-database queries. You must determine the appropriate target platform. What should you do?

Accepted Answer

Migrate to Azure SQL Managed Instance.. Azure SQL Managed Instance supports cross-database queries within the same instance and maintains high compatibility with on-premises SQL Server, including support for the geography data type. Azure SQL Database single database and elastic pools do not support cross-database queries, and geo-replication is for disaster recovery, not query compatibility. Therefore, SQL Managed Instance is the correct migration target.

Answer

Migrate to Azure SQL Database elastic pool.

Answer

Migrate to Azure SQL Database with geo-replication enabled.

Answer

Migrate to Azure SQL Database single database.

Question 11

You have an Azure SQL Database server named sqlsrv1. Several application teams connect using SQL logins. The security team mandates that all authentication use Microsoft Entra ID and that multifactor authentication be enforceable. You need to configure the server so that Entra ID authentication is available to database users. What should you do first?

Accepted Answer

Assign a Microsoft Entra ID admin to the Azure SQL logical server.. Assigning a Microsoft Entra ID administrator to the logical server activates directory authentication for the server. Only after that can you create contained database users mapped to Entra identities and rely on Conditional Access to enforce multifactor authentication. The other choices address encryption, network filtering, or dependent steps that require the directory admin to exist first.

Answer

Enable Transparent Data Encryption on each database.

Answer

Create contained database users for each application in every database.

Answer

Configure a firewall rule to allow the corporate network range.

Question 12

You need to deploy a new Azure SQL Database that will store sensitive financial data. The database must use customer-managed keys for encryption at rest, and the keys must be stored in Azure Key Vault. You want to ensure that the keys are automatically rotated and that the database remains available during rotation. What should you configure?

Accepted Answer

Enable transparent data encryption (TDE) with a customer-managed key stored in Azure Key Vault and configure automatic key rotation.. Transparent data encryption (TDE) with a customer-managed key in Azure Key Vault is the correct solution for encryption at rest with customer-controlled keys. Azure SQL Database supports automatic key rotation, which seamlessly updates the database to use the latest key version. Always Encrypted is for client-side encryption, Azure Disk Encryption is for IaaS VMs, and dynamic data masking is for masking, not encryption.

Answer

Enable Always Encrypted with column master keys stored in Azure Key Vault and configure automatic key rotation.

Answer

Use dynamic data masking with a customer-managed key to encrypt sensitive columns.

Answer

Configure Azure Disk Encryption on the underlying virtual machines hosting the Azure SQL Database.

Question 13

You are monitoring an Azure SQL Database using Azure Monitor metrics. You need to configure alerts to notify the operations team when the database is approaching resource limits. Which two metrics should you use to detect potential CPU and I/O bottlenecks? (Choose two.)

Accepted Answer

cpu_percent. To detect CPU and I/O bottlenecks, the cpu_percent and physical_data_read_percent metrics are the most direct indicators. cpu_percent shows CPU utilization, while physical_data_read_percent reflects I/O read pressure. These metrics are available in Azure Monitor and can be used to trigger alerts when thresholds are exceeded, enabling proactive management.

Answer

sessions_percent

Answer

log_write_percent

Answer

workers_percent

Question 14

You are the DBA for an Azure SQL Database named OrdersDB. The security team requires that you implement row-level security (RLS) to ensure that sales representatives can only view orders for their own region. You need to create a security policy that filters rows based on the sales representative's region. Which two actions should you perform? (Choose two.)

Accepted Answer

Create an inline table-valued function that returns 1 when the sales representative's region matches the row's region.. Row-level security in Azure SQL Database requires a predicate function that defines the filtering logic and a security policy that applies that function to the table. The inline table-valued function returns 1 when the row should be visible to the user, and the security policy adds a FILTER PREDICATE using that function. Together, they enforce that sales representatives only see orders for their region. Other actions do not implement RLS.

Answer

Grant SELECT permission on the Orders table to all sales representatives.

Answer

Create a database role for each region and add sales representatives to the appropriate role.

Answer

Enable Auditing on the Orders table to track access.

Question 15

You manage an Azure SQL Managed Instance that hosts a mission-critical database. The instance is in the East US 2 region. You need to configure a disaster recovery solution that meets the following requirements: (1) provide a readable secondary in a different Azure region, (2) support automatic failover, and (3) minimize administrative effort. Which two actions should you perform? (Choose two.)

Accepted Answer

Create a failover group and add the managed instance to it.. For Azure SQL Managed Instance, cross-region disaster recovery with a readable secondary and automatic failover is achieved by using failover groups. You must deploy a secondary managed instance in a different region and then add both instances to a failover group. Active geo-replication and zone redundancy do not meet the requirements for a cross-region readable secondary with automatic failover. The failover group provides the listener endpoint and automates the failover process.

Answer

Create a geo-secondary using the Azure portal by selecting the managed instance and choosing 'Geo-Replication'.

Answer

Enable zone redundancy on the primary managed instance.

Answer

Configure active geo-replication for the managed instance.