DP-300 Practice Exam 2 — 20 Questions

Question 1

Which TWO of the following are valid disaster recovery options for Azure SQL Database that provide a secondary in a different Azure region?

Accepted Answer

Active geo-replication. Options A and B are correct. Active geo-replication and Failover groups are both valid disaster recovery options for Azure SQL Database that provide a secondary database in a different Azure region. Options C, D, and E are incorrect: Geo-restore (C) restores from backups and does not maintain a continuous secondary; Zone-redundant configuration (D) provides high availability within a single region, not disaster recovery; Auto-failover groups (E) is a feature that enables automatic failover for failover groups, but it is not a separate disaster recovery option—it is part of failover groups.

Answer

Geo-restore

Answer

Zone-redundant configuration

Answer

Auto-failover groups

Question 2

Refer to the exhibit. You apply this JSON configuration to an Azure SQL Database server. Which actions will be audited?

Accepted Answer

Only successful and failed database authentication attempts. The configuration only includes SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP. Option A is wrong because it includes actions not in the config. Option B is wrong because it includes data modification actions. Option D is wrong because it includes all actions.

Answer

All successful and failed login attempts and all schema changes

Answer

All database-level actions

Answer

All successful and failed logins and all data modification statements

Question 3

You are deploying an Azure SQL Database for a development environment. The database will be used for testing and will be deleted after three months. You want to minimize costs while ensuring the database can be restored to any point within the retention period. Which backup retention policy should you configure?

Accepted Answer

Configure short-term backup retention to 7 days.. The scenario specifies a development database that will be deleted after three months, with the requirement to restore to any point within the retention period. The default short-term backup retention for Azure SQL Database is 7 days, which provides point-in-time restore (PITR) capability within that window. Since the database is temporary and cost minimization is key, 7 days meets the requirement without incurring extra storage costs for longer retention or geo-redundancy.

Answer

Configure geo-redundant backup storage.

Answer

Configure short-term backup retention to 14 days.

Answer

Configure long-term backup retention (LTR) for 1 year.

Question 4

Which THREE factors should you consider when choosing between Azure SQL Database single database and Azure SQL Managed Instance for a new application?

Accepted Answer

Need for cross-database queries and transactions.. Azure SQL Database single database does not support cross-database queries and transactions, whereas Azure SQL Managed Instance provides full SQL Server instance-level features, including cross-database queries and transactions via linked servers or within the same instance. This makes Managed Instance the appropriate choice when the application requires querying or updating multiple databases in a single transaction.

Answer

Requirement for elastic pool scalability.

Answer

Need for automated backups with point-in-time restore.

Question 5

Your company uses Azure SQL Database with the Hyperscale service tier. You notice that the database is experiencing high I/O latency during peak hours. After analyzing the query performance, you determine that the primary bottleneck is due to log write throughput. You need to reduce log write latency without changing the service tier. What should you do?

Accepted Answer

Scale up to a higher Hyperscale service objective.. Scaling up to a higher Hyperscale service objective increases the log write throughput by provisioning more log I/O capacity and faster storage. Since the bottleneck is specifically log write latency, and you cannot change the service tier, increasing the service objective within Hyperscale directly addresses the issue by allocating more resources to the log write path.

Answer

Configure Query Store hints to force parameterization.

Answer

Enable read scale-out to offload read queries.

Answer

Increase the MAXDOP setting for the database.

Question 6

You are configuring performance optimization for an Azure SQL Database that uses the Hyperscale service tier. The database has heavy read-write workloads. Which THREE actions should you take to optimize performance?

Accepted Answer

Create nonclustered columnstore indexes on large fact tables.. Options A, C, and D are correct. Hyperscale benefits from nonclustered indexes on read-intensive tables, accelerated database recovery reduces log IO, and read-scale replicas distribute read load. Option B is wrong because Hyperscale does not support resource governor. Option E is wrong because increasing service tier is not an optimization action.

Answer

Configure resource governor to limit CPU usage.

Answer

Increase the service tier to Business Critical.

Question 7

Refer to the exhibit. An administrator is trying to perform a point-in-time restore for a managed instance database. The output shows the state of the instance and databases. What is the most likely reason db1 shows 'Restoring' status?

Accepted Answer

The database is being restored from a geo-redundant backup.. The 'Restoring' status on db1 indicates that a restore operation is currently in progress on that database. The geo-backup list shows an available backup, which might be used for a geo-restore or point-in-time restore that is ongoing.

Answer

The database is being upgraded to a new service tier.

Answer

The database is corrupted and Azure is automatically repairing it.

Answer

The database is in the process of replication to a secondary region.

Question 8

You need to migrate an on-premises SQL Server database to Azure SQL Database. The database uses SQL Server Integration Services (SSIS) packages for ETL. You want to continue using SSIS in the cloud with minimal changes. Which Azure service should you use?

Accepted Answer

Use Azure Data Factory with Azure-SSIS Integration Runtime. Azure Data Factory with Azure-SSIS Integration Runtime is the correct choice because it provides a fully managed service that lifts and shifts existing SSIS packages to the cloud with minimal changes. The Azure-SSIS IR runs a dedicated cluster of Azure VMs that host the SSIS engine, allowing you to deploy, execute, and manage your existing SSIS packages directly in Azure without rewriting code.

Answer

Deploy SSIS on Azure SQL Database

Answer

Migrate SSIS packages to Azure Databricks

Answer

Use Azure SQL Managed Instance to run SSIS

Question 9

Your organization needs to comply with a regulation that requires data to be encrypted at rest using a customer-managed key stored in Azure Key Vault. You have an Azure SQL Database. What should you configure?

Accepted Answer

Enable Transparent Data Encryption (TDE) and use a customer-managed key in Azure Key Vault as the TDE protector.. Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault meets the requirement for data at rest encryption using a customer-controlled key. TDE encrypts the database files (data and log files) at rest, and by using a customer-managed key as the TDE protector, you retain control over key rotation and revocation, satisfying regulatory compliance.

Answer

Use Azure Policy to enforce encryption at rest.

Answer

Enable Always Encrypted and store the column master key in Azure Key Vault.

Answer

Configure Azure Storage Service Encryption for the database files.

Question 10

Refer to the exhibit. You are managing db1, which is currently in Standard tier. What is the maximum size you can set for db1 without changing the service objective?

Accepted Answer

268,435,456,000 bytes (250 GB). The first command shows that Standard S2 supports a max size of 268435456000 bytes (250 GB). The second command shows the current max size is 107374182400 bytes (100 GB). Since db1 is already on S2, you can increase the max size up to 250 GB within the same service objective.

Answer

107,374,182,400 bytes (100 GB)

Answer

1,073,741,824,000 bytes (1 TB)

Answer

536,870,912,000 bytes (500 GB)

Question 11

Which THREE components are required to set up elastic jobs in Azure SQL Database?

Accepted Answer

Target groups. Target groups (C) are required because they define the set of databases (or elastic pools) against which an elastic job will execute. Without target groups, the job agent has no scope of execution. The elastic job agent (D) orchestrates job scheduling and execution, and job credentials (E) are needed to authenticate to the target databases for running T-SQL scripts.

Answer

Azure Automation account

Answer

SQL Server Agent

Question 12

You are monitoring an Azure SQL Database using Query Performance Insight and notice that a specific query has a high average duration and high CPU usage. The query plan shows a clustered index scan on a large table. Which two actions should you take to optimize performance? (Choose two.)

Accepted Answer

Update statistics on the table to ensure the optimizer has current information.. Updating statistics provides the query optimizer with current data distribution information, which can lead to a more efficient query plan, potentially avoiding the clustered index scan. In Azure SQL Database, stale statistics are a common cause of suboptimal plans, and updating them is a low-cost, non-disruptive first step before considering index changes.

Answer

Rebuild the clustered index to reduce fragmentation.

Answer

Force a different query plan using Query Store hints.

Answer

Increase the DTU service tier of the database.

Question 13

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 14

You need to monitor the performance of an Azure SQL Database and set up alerts when the DTU consumption exceeds 80% for more than 5 minutes. Which Azure service should you use?

Accepted Answer

Azure Monitor metric alerts. Azure Monitor metric alerts can be configured on DTU percentage. Option B is wrong because Azure Advisor provides recommendations but not real-time alerts. Option C is wrong because Azure SQL Insights is for visualization, not alerting. Option D is wrong because Log Analytics workspaces store logs but do not natively provide metric alerts.

Answer

Azure Advisor

Answer

Azure SQL Insights (preview)

Answer

Log Analytics workspace

Question 15

You are configuring Microsoft Defender for SQL for Azure SQL Database. You need to ensure that alerts are sent to the security operations team via email and also integrated with Microsoft Sentinel. What should you configure?

Accepted Answer

In Microsoft Sentinel, connect the Azure SQL Database data source using the built-in connector.. Microsoft Sentinel provides a built-in connector for Azure SQL Database that ingests security alerts from Microsoft Defender for SQL, satisfying the Sentinel integration requirement. The email notification requirement is addressed separately by configuring alert rules within Defender for SQL to send emails to the security operations team. Option A enables the Sentinel integration, while email notifications are a complementary configuration within Defender for SQL, not part of Option A itself.

Answer

Use Azure Logic Apps to forward Defender for SQL alerts to Sentinel.

Answer

Configure a diagnostic setting on the SQL server to stream logs to a Log Analytics workspace used by Sentinel.

Answer

Configure the alert rule in Defender for SQL to send email to the security team.

Question 16

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 17

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 18

Which TWO options are valid methods to optimize query performance in Azure SQL Managed Instance?

Accepted Answer

Use columnstore indexes on large tables. Options A and D are correct. Columnstore indexes (A) improve analytical query performance by reducing I/O and using batch processing. Query Store (D) helps identify regressions by tracking execution plans and performance metrics. Option B is incorrect; setting database compatibility level to 150 may enable new features but is not a direct query optimization method. Option C is incorrect; increasing storage size does not improve query performance. Option E is incorrect; Transparent Data Encryption (TDE) secures data but does not enhance performance.

Answer

Set database compatibility level to 150

Answer

Increase the maximum storage size

Answer

Enable Transparent Data Encryption (TDE)

Question 19

You manage an Azure SQL Database that experiences periodic performance degradation. You need to identify the top queries by CPU consumption over the last hour. Which dynamic management view should you query?

Accepted Answer

sys.dm_exec_query_stats. (sys.dm_exec_query_stats) is correct because this DMV returns aggregated performance statistics for cached query plans, including total CPU time. It can be filtered to look at queries over the last hour. Option A (sys.dm_exec_sessions) shows current session-level information, not query-level CPU. Option B (sys.dm_exec_query_plan) returns the actual execution plan, not performance statistics. Option D (sys.dm_exec_requests) shows currently executing requests, not historical CPU consumption.

Answer

sys.dm_exec_sessions

Answer

sys.dm_exec_query_plan

Answer

sys.dm_exec_requests

Question 20

Which THREE factors should you consider when choosing between vCore and DTU purchase models for Azure SQL Database performance optimization?

Accepted Answer

DTU is simpler for customers who want a bundled metric.. The DTU (Database Transaction Unit) model bundles compute, storage, and I/O into a single, simple metric, making it easier for customers who want a straightforward, pre-configured performance tier without needing to manage individual resources. This contrasts with the vCore model, which requires separate configuration of vCores, memory, and storage, offering more granular control but greater complexity.

Answer

Only vCore supports Azure Hybrid Benefit.

Answer

Only DTU supports elastic pools.