DP-300 Practice Test 3 — 30 Questions

Question 1

Your company uses Azure SQL Database with active geo-replication. You notice that the secondary database in a different region has a high log write latency. Users report that the primary database performance is normal. What is the most likely cause?

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

Accepted Answer

Network latency between the primary and secondary regions. In Azure SQL Database active geo-replication, the secondary database continuously receives transaction log from the primary over the network. High log write latency on the secondary while the primary performs normally points to network latency between the two regions, since the log must traverse the inter-region link and be hardened on the secondary.

Answer

Insufficient log IOPS on the secondary database

Answer

High CPU usage on the primary database

Answer

Excessive read workload on the secondary database

Question 2

You are a database administrator for an Azure SQL Database. You need to ensure that only specific client IP addresses can connect to the database, while all other traffic is blocked. You also need to allow Azure services to access the database. What should you configure?

Accepted Answer

Configure server-level firewall rules to allow the specific client IP addresses and enable the 'Allow Azure services and resources to access this server' setting.. Azure SQL Database uses server-level firewall rules to control inbound access. By adding rules for specific client IP addresses and enabling the 'Allow Azure services and resources to access this server' setting, you restrict connections to only those IPs while permitting Azure internal services (e.g., Azure Logic Apps, Azure Functions) to connect. This setting leverages the Azure SQL firewall, which evaluates source IP addresses against the configured rules before allowing a connection.

Answer

Disable public network access and configure a service endpoint.

Answer

Configure a private endpoint and disable public network access.

Answer

Configure network security group (NSG) rules on the subnet where the Azure SQL Database is deployed.

Question 3

You are analyzing the exhibit KQL query that queries Azure Diagnostics logs for Query Store runtime statistics. The query is intended to show average CPU time per hour for each database. However, the result shows no data for the last 24 hours, although Query Store is enabled on all databases. What is the most likely reason?

Accepted Answer

The diagnostic settings are not configured to send QueryStoreRuntimeStatistics to Log Analytics.. For Query Store runtime statistics to appear in Log Analytics, the Azure SQL Database diagnostic settings must explicitly route the QueryStoreRuntimeStatistics category to the Log Analytics workspace. If that category is not enabled, the KQL query will return no rows even though Query Store is enabled on the databases, because the data never reaches the workspace. This is the most likely cause of the empty result set.

Answer

Query Store is not enabled on the databases.

Answer

The time range in the query is too narrow and excludes the last 24 hours.

Answer

The query syntax is incorrect and needs to use 'project' before 'summarize'.

Question 4

You are configuring performance monitoring for Azure SQL Managed Instance. You need to collect and analyze query performance data with minimal overhead. Which solution should you use?

Accepted Answer

Query Store. Query Store is built-in and designed for low overhead query performance monitoring. Option B is wrong because Azure Monitor metrics provide resource-level metrics, not query-level details. Option C is wrong because Extended Events can have higher overhead and is more for custom event collection. Option D is wrong because SQL Server Profiler is deprecated and has high overhead.

Answer

Azure Monitor metrics

Answer

Extended Events

Answer

SQL Server Profiler

Question 5

You are deploying a new application on Azure SQL Database. The application requires that all connections use a specific login, 'AppUser', with the least privileges necessary. The login should only be able to execute stored procedures in the 'Sales' schema and should not have direct access to underlying tables. What should you do?

Accepted Answer

Create a database role, grant EXECUTE on the 'Sales' schema to the role, and add 'AppUser' to the role.. Creating a database role, granting EXECUTE on the 'Sales' schema to that role, and adding 'AppUser' to the role follows the principle of least privilege. This allows 'AppUser' to execute stored procedures in the Sales schema without granting direct table access, because stored procedures execute with ownership chaining or elevated permissions. This is the most maintainable and secure approach.

Answer

Grant the SELECT permission on the 'Sales' schema to 'AppUser'.

Answer

Add 'AppUser' to the db_datareader role.

Answer

Grant the EXECUTE permission on each stored procedure individually to 'AppUser'.

Question 6

You are monitoring an Azure SQL Database and notice high PAGELATCH waits. What is the most likely cause?

Accepted Answer

Concurrent inserts into a table with a clustered index causing last-page contention.. High PAGELATCH waits in Azure SQL Database are most commonly caused by concurrent inserts into a table with a clustered index, leading to last-page contention. Multiple threads trying to insert into the same page serialize on the page latch, causing PAGELATCH waits. This is a classic OLTP pattern where a monotonically increasing key (like an IDENTITY column) causes all inserts to target the last page.

Answer

Insufficient buffer pool size leading to frequent reads from disk.

Answer

High CPU usage due to inefficient queries.

Answer

Long-running transactions blocking other queries.

Question 7

Your company wants to ensure business continuity for an Azure SQL Database that is used by a critical application. The database must remain available in the event of a single availability zone failure within a region. Which configuration should you use?

Accepted Answer

Configure zone-redundant availability for the database. Zone-redundant availability for Azure SQL Database replicates the database across multiple availability zones within a region, providing high availability if one zone fails. This ensures the database remains available during a single zone outage, meeting the business continuity requirement.

Answer

Use active geo-replication to a different region

Answer

Deploy the database with locally redundant storage

Answer

Configure read scale-out with a secondary replica

Question 8

You have an Azure SQL Managed Instance used for an e-commerce platform. During a flash sale, you experience a deadlock that causes transaction rollbacks. You need to minimize deadlock occurrences in the future. What should you implement?

Accepted Answer

Enable READ COMMITTED SNAPSHOT isolation level.. Enabling READ COMMITTED SNAPSHOT isolation (RCSI) makes readers use row versioning from tempdb instead of taking shared locks, which eliminates the classic reader-writer deadlock pattern that plagues e-commerce workloads mixing SELECTs and UPDATEs. This directly reduces deadlock frequency without changing application code.

Answer

Configure deadlock graph in Extended Events.

Answer

Enable automatic tuning to force last good plan.

Answer

Increase the instance vCores to improve concurrency.

Question 9

Your company uses Azure SQL Database with active geo-replication. You notice that the secondary database in a different region has a high log write latency. Users report that the primary database performance is normal. What is the most likely cause?

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

Accepted Answer

Create database users from Microsoft Entra ID groups and grant roles to those users.. Azure SQL Database supports creating database users mapped to Microsoft Entra ID (formerly Azure AD) groups. By creating a user for the Entra ID group and then granting database roles to that group user, all members of the group automatically inherit the assigned permissions. This directly satisfies the requirement for role assignment based on group membership without custom scripting or triggers.

Answer

Use Azure RBAC to assign roles to the Entra ID groups.

Answer

Configure a SQL Server Agent job to update database roles based on group membership.

Answer

Create a DDL trigger that assigns roles when users log in.

Question 10

You are managing an Azure SQL Database that experiences intermittent performance degradation. Query Store shows a significant increase in wait time for PAGEIOLATCH_SH. You need to identify the most likely cause. What should you investigate first?

Accepted Answer

Insufficient IOPS or throughput at the database level. PAGEIOLATCH_SH waits indicate I/O subsystem pressure, often due to insufficient IOPS or throughput. Option A is incorrect because out-of-date statistics cause cardinality estimation errors, not I/O waits. Option C is incorrect because missing indexes typically cause table scans but not necessarily PAGEIOLATCH waits. Option D is incorrect because blocking causes waits like LCK_M_*, not PAGEIOLATCH.

Answer

Out-of-date statistics

Answer

Missing indexes

Answer

Blocking from long-running transactions

Question 11

You manage an Azure SQL Database that uses a Serverless compute tier. You notice that during idle periods, the database auto-pauses and then auto-resumes when a connection is made. However, users report that the first query after a pause is slow. You need to improve the performance of the first query. What should you do?

Accepted Answer

Disable auto-pause for the serverless database. The slow first query after auto-resume is caused by the cold-start latency of the serverless compute tier, which includes provisioning resources and warming the buffer pool. Disabling auto-pause ensures the database remains online and the buffer pool stays populated, eliminating the cold-start delay for the first query.

Answer

Increase the maximum vCores

Answer

Create a SQL Agent job to ping the database every hour

Answer

Enable Query Store

Question 12

You are deploying an Azure SQL Database using PowerShell as shown in the exhibit. The database will be used by a development team that works intermittently. You need to ensure the database is cost-effective while being available on demand. What is the purpose of the AutoPauseDelayInMinutes parameter?

Accepted Answer

It controls the automatic pausing of the database after a period of inactivity to save costs.. The AutoPauseDelayInMinutes parameter is used with Azure SQL Database serverless compute tier. It specifies the number of minutes of inactivity (no CPU usage or active sessions) after which the database automatically pauses, stopping compute billing while storage remains billed. This makes the database cost-effective for intermittent development workloads because it eliminates compute costs during idle periods and automatically resumes on the first connection.

Answer

It configures the database to pause during a disaster recovery scenario.

Answer

It sets the maximum duration for which the database can be paused.

Answer

It determines how long the database takes to resume after a pause.

Question 13

You are analyzing query performance in an Azure SQL Database. The query in the exhibit returns a list of queries ordered by total_logical_reads. What does high total_logical_reads typically indicate?

Accepted Answer

The query is reading many pages from the buffer pool, possibly due to missing indexes. High total_logical_reads typically indicates that the query is reading many pages from the buffer pool, which often points to missing or inefficient indexes. Option D is correct. Option A is incorrect because high logical reads relate to buffer pool access, not necessarily I/O latency (which is indicated by high physical reads). Option B is incorrect because CPU time is measured by worker_time, not logical reads. Option C is incorrect because while logical reads can increase memory usage, the primary indicator of memory usage is the memory grant, not logical reads.

Answer

The query is experiencing I/O latency

Answer

The query is using a lot of CPU time

Answer

The query is using a lot of memory

Question 14

You are designing an automated backup strategy for Azure SQL Managed Instance. The solution must ensure point-in-time restore (PITR) within 2 hours for the last 7 days and long-term retention (LTR) for 5 years. Which configuration should you use?

Accepted Answer

Set PITR retention to 7 days (default) and configure LTR backup policy with yearly backups for 5 years.. Azure SQL Managed Instance supports point-in-time restore (PITR) with a configurable retention period from 1 to 35 days; the default is 7 days, which satisfies the requirement to restore within 2 hours for the last 7 days (the 2-hour target is a recovery point objective, not retention). For long-term retention (LTR) of 5 years, Managed Instance supports LTR backup policies with yearly backups that can retain backups for up to 10 years. Therefore, setting PITR retention to 7 days (default) and configuring an LTR policy with yearly backups for 5 years meets both requirements. Option A is wrong because Azure Backup for SQL Server in Azure VM is for SQL Server on Azure VMs, not for Managed Instance. Option B is wrong because "geo-redundant backup storage for LTR" is a storage redundancy option, not an LTR retention policy; LTR requires explicit backup frequency and retention configuration. Option C is wrong because PITR retention cannot be set to 2 hours (minimum is 1 day) and custom backup jobs using Elastic Database Jobs are unnecessary; Managed Instance automates backups natively.

Answer

Use Azure Backup for SQL Server in Azure VM to back up the managed instance.

Answer

Set PITR retention to 7 days and use geo-redundant backup storage for LTR.

Answer

Set PITR retention to 2 hours and configure a custom backup job using Elastic Database Jobs.

Question 15

You are a database administrator for a company that uses Azure SQL Database. You need to configure a diagnostic setting to send database metrics to a Log Analytics workspace for long-term analysis. The solution should be cost-effective and include metrics like CPU percentage, data IO, and log IO. What should you do?

Accepted Answer

In the Azure portal, add a diagnostic setting for the database to stream 'AllMetrics' to a Log Analytics workspace.. Azure SQL Database diagnostic settings allow streaming platform metrics (CPU percentage, data IO, log IO) directly to a Log Analytics workspace by selecting the 'AllMetrics' category. This is the native, cost-effective mechanism for long-term metric retention and analysis without custom code. It requires no T-SQL jobs or preview features and integrates directly with Azure Monitor.

Answer

Enable Azure SQL Insights (preview) for the database.

Answer

Enable Query Store and configure it to export to Log Analytics.

Answer

Create a T-SQL job that periodically inserts sys.dm_db_resource_stats into a table in Log Analytics.

Question 16

Which TWO are valid methods to connect to an Azure SQL Database without exposing a public endpoint?

Accepted Answer

Use a service endpoint. A service endpoint extends your virtual network private address space and the identity of your VNet to Azure SQL Database over a direct connection on the Azure backbone network. This allows you to secure your logical SQL server to accept traffic only from a specific subnet, eliminating the need for a public endpoint while still using the public endpoint's DNS name internally.

Answer

Use Always Encrypted

Answer

Use a public endpoint with a firewall rule

Answer

Use a site-to-site VPN gateway and connect to public endpoint

Question 17

You are troubleshooting a failover group for Azure SQL Database. The automatic failover is not triggering as expected during a regional outage. You verify that the grace period for data loss is set to 3600 seconds. The outage lasts 30 minutes. What is the most likely reason the automatic failover did not occur?

Accepted Answer

The outage duration is less than the grace period for data loss.. The grace period for data loss (also called the 'grace period' or 'data loss grace period') is the maximum time the failover group waits before forcing a failover when the primary region is unavailable. If the outage duration is shorter than this grace period, Azure SQL Database will not trigger automatic failover because it still hopes to recover the primary without data loss. Here, the grace period is 3600 seconds (60 minutes) and the outage lasted only 30 minutes, so the condition for automatic failover was not met. Thus, the most likely reason is that the outage duration is less than the grace period.

Answer

The failover policy is set to manual.

Answer

The grace period for data loss is too short.

Answer

The secondary region is also experiencing an outage.

Question 18

You are designing a secure environment for Azure SQL Managed Instance. The company requires that all database backups be encrypted using customer-managed keys stored in Azure Key Vault. Which combination of actions should you take?

Accepted Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault.. Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault allows you to encrypt the database backup files using a key that you control. When TDE is enabled and configured with a customer-managed key (CMK) stored in Azure Key Vault, Azure SQL Managed Instance automatically encrypts backups with the same TDE protector key, meeting the requirement for customer-managed backup encryption.

Answer

Configure Always Encrypted with keys stored in Key Vault.

Answer

Use Azure Storage Service Encryption to encrypt the backup files.

Answer

Enable backup encryption using a certificate stored in the managed instance.

Question 19

You have an Azure SQL Database in the General Purpose service tier. The database must be available during a planned patching event that updates the underlying infrastructure. What high availability feature is provided by default?

Accepted Answer

Built-in high availability with a standby replica. Azure SQL Database in the General Purpose service tier includes built-in high availability with a standby replica, which is provisioned automatically and used to fail over during planned maintenance or unplanned outages. This built-in HA ensures the database remains available during patching events without requiring the customer to configure geo-replication or zone redundancy. The standby replica is maintained synchronously and failover is automatic.

Answer

Active geo-replication

Answer

Zone-redundant availability

Answer

Business Critical service tier

Question 20

You are reviewing an ARM template snippet that configures a long-term retention (LTR) policy for an Azure SQL Database. Based on the exhibit, how long will weekly backups be retained?

Accepted Answer

4 weeks.. In the ARM template snippet, the weeklyRetention property is set to 'P4W' (ISO 8601 duration format), which means 4 weeks. Therefore, weekly backups are retained for exactly 4 weeks.

Answer

4 days.

Answer

4 months.

Answer

4 years.