View Full Microsoft DP-300 Exam Dumps and Practice Test Dumps

 

Q61. Which Azure SQL Database feature provides automatic failover between databases in different Azure regions?

1) Failover groups
2) Query Store
3) Elastic pools
4) Dynamic Data Masking

Correct Answer: 1)

Explanation:

Azure SQL Database failover groups are designed to manage databases across different Azure regions and provide a mechanism for coordinated failover. They can help applications continue operating when the primary region becomes unavailable by directing connections toward the secondary region. Failover groups can also support read-only access to secondary databases depending on configuration. This capability is particularly useful for business continuity and disaster recovery scenarios where regional resilience is required. Query Store addresses performance analysis, elastic pools provide shared compute resources, and Dynamic Data Masking protects sensitive values in query results. Therefore, failover groups are the appropriate feature for cross-region automatic failover.

Q62. Which Azure SQL Database feature provides a secondary readable copy of a database for disaster recovery or read-scale scenarios?

1) Database role
2) Geo-replication
3) Firewall rule
4) Database schema

Correct Answer: 2)

Explanation:

Azure SQL Database active geo-replication creates a secondary database in another Azure region that can be used for disaster recovery and, depending on configuration, read-scale scenarios. Changes from the primary database are replicated to the secondary database. This architecture can help organizations maintain access to data if the primary region experiences an outage. Administrators can use the secondary as part of a recovery strategy and manage failover when necessary. Firewall rules control connectivity, database roles manage permissions, and schemas organize database objects. Therefore, geo-replication is the appropriate feature when a secondary database is required in another region.

Q63. Which type of replica is primarily intended to improve read performance without becoming the primary write replica?

1) Read-only replica
2) Backup copy
3) Database role
4) Firewall replica

Correct Answer: 1)

Explanation:

A read-only replica can be used to distribute read workloads away from the primary database. This approach is useful when an application performs many read operations and the administrator wants to reduce pressure on the primary write workload. Depending on the Azure SQL architecture and configuration, read-scale capabilities can provide additional readable copies of the data. A backup copy is intended primarily for recovery, while database roles manage permissions and firewall configurations control network access. Therefore, when the objective is specifically to handle additional read workloads without directing those operations to the primary write replica, a read-only replica is the appropriate choice.

Q64. Which Azure SQL Database feature helps maintain application connectivity by providing a stable listener endpoint during failover?

1) Failover group listener
2) Query Store
3) Database schema
4) Index

Correct Answer: 1)

Explanation:

A failover group provides listener endpoints that applications can use to connect to the appropriate database server without requiring the application to manually track the active primary server. During a planned or forced failover, the listener can direct connections to the new primary location. This simplifies application connectivity and supports disaster recovery architectures involving multiple Azure regions. Query Store records query performance, a schema organizes database objects, and an index improves data retrieval. Therefore, when an application needs a stable connection endpoint that can continue working after a failover, the failover group listener is the appropriate capability.

Q65. Which Azure SQL Database capability allows an administrator to restore a database after accidental deletion or modification using retained backups?

1) Backup and restore
2) Query Store
3) Dynamic Data Masking
4) Database role

Correct Answer: 1)

Explanation:

Azure SQL Database provides automated backup capabilities that support recovery from accidental data changes, deletion, or other database problems. Administrators can use supported restore mechanisms to create a database from retained backups. Point-in-time restore is particularly useful when a database needs to be recovered to a specific moment within the available retention period. Backup retention policies should be planned according to recovery and compliance requirements. Query Store is designed for performance analysis, Dynamic Data Masking protects sensitive information in query results, and database roles manage permissions. Therefore, backup and restore capabilities provide the appropriate foundation for recovering databases after accidental changes.

Q66. Which recovery option should be used when an administrator needs to recover a database to the state it had shortly before an accidental UPDATE statement?

1) Point-in-time restore
2) Index rebuild
3) Database role assignment
4) Query Store reset

Correct Answer: 1)

Explanation:

Point-in-time restore allows an Azure SQL Database to be recovered to a selected time within the available backup retention period. This is useful when an accidental UPDATE, DELETE, or other unwanted modification changes database contents and the administrator needs to return to an earlier state. The administrator selects an appropriate recovery point before the unwanted operation and restores the database to that point. An index rebuild changes index structures and does not recover deleted or modified data. Database roles control authorization, while Query Store stores performance information. Therefore, point-in-time restore is the appropriate recovery mechanism for this scenario.

Q67. Which backup retention capability is intended for organizations that must retain backups for several years?

1) Long-term retention
2) Query Store
3) Serverless compute
4) Private Endpoint

Correct Answer: 1)

Explanation:

Long-term retention is designed to preserve Azure SQL Database backups for extended periods beyond normal short-term retention. Organizations may use it when business, regulatory, legal, or compliance requirements require historical backups to remain available for months or years. Administrators can define retention policies based on organizational requirements and supported Azure SQL capabilities. Query Store is intended to maintain query performance information, serverless compute manages variable workloads, and Private Endpoint provides private network connectivity. Therefore, when the requirement specifically involves keeping database backups for several years, long-term retention is the appropriate feature to consider.

Q68. Which Azure SQL feature can automatically create indexes based on observed query workload patterns?

1) Automatic tuning
2) Azure DNS
3) Microsoft Entra authentication
4) Database auditing

Correct Answer: 1)

Explanation:

Azure SQL automatic tuning can analyze database workload behavior and provide or apply supported performance recommendations, including certain index-related actions. This capability can help reduce the manual effort required to identify and implement some common performance improvements. Administrators should still monitor the effects of automated changes and understand the workload before relying entirely on automation. Azure DNS manages name resolution, Microsoft Entra authentication manages identity-based access, and auditing records database activity. Therefore, when a scenario describes automatically identifying and implementing supported index improvements based on workload information, automatic tuning is the appropriate answer.

Q69. Which SQL Server performance metric represents the time a session spends waiting for a resource before it can continue?

1) Wait statistics
2) Database schema
3) Backup retention
4) Firewall rules

Correct Answer: 1)

Explanation:

Wait statistics provide information about the resources or conditions that cause database sessions to wait before continuing execution. They can help administrators identify performance bottlenecks involving CPU availability, storage I/O, locking, memory, or other resource conditions. Analyzing waits can provide useful insight beyond simply looking at query duration because it helps identify what the database engine is waiting for. Database schemas organize objects, backup retention controls recovery storage duration, and firewall rules manage network access. Therefore, when troubleshooting database performance and identifying resource-related delays, wait statistics are an important diagnostic source.

Q70. Which type of wait occurs when a transaction is waiting because another transaction holds a conflicting lock?

1) Lock wait
2) Network DNS wait
3) Backup retention wait
4) Authentication role wait

Correct Answer: 1)

Explanation:

A lock wait occurs when a database session cannot proceed because another session holds a lock that conflicts with the requested operation. Locking is necessary to maintain transactional consistency, but excessive blocking can reduce application performance and increase query response times. Administrators can investigate blocking sessions, transaction duration, indexes, and application behavior when lock waits become significant. Network DNS and authentication concepts do not describe SQL Server locking behavior, while backup retention controls how long backups are preserved. Therefore, when one transaction must wait for another transaction to release a conflicting lock, the situation is described as a lock wait.

Q71. Which SQL Server feature can help control CPU and memory resource usage for workloads by defining resource pools and workload groups?

1) Resource Governor
2) Query Store
3) Transparent Data Encryption
4) Database Auditing

Correct Answer: 1)

Explanation:

Resource Governor provides mechanisms for controlling and monitoring SQL Server resource consumption by allowing administrators to define resource pools and workload groups. It can help isolate workloads and prevent one workload from consuming disproportionate resources under supported SQL Server configurations. Administrators can classify sessions and apply resource controls based on workload requirements. Query Store focuses on collecting query performance history, Transparent Data Encryption protects data at rest, and auditing records database activity. Therefore, when the requirement involves managing CPU and memory resource allocation among workloads, Resource Governor is the relevant SQL Server feature.

Q72. Which Azure service can send notifications when an Azure SQL resource experiences a defined monitoring condition?

1) Azure Monitor Alerts
2) Azure Table Storage
3) Azure DNS
4) Azure Private DNS only

Correct Answer: 1)

Explanation:

Azure Monitor Alerts can notify administrators when specified metric or log conditions occur. For Azure SQL resources, administrators can configure alerts around supported performance or operational signals and connect them to notification or automation mechanisms. This allows database teams to respond more quickly to conditions such as unusual resource utilization or other monitored events. Azure Table Storage is a data storage service, Azure DNS provides public DNS capabilities, and Azure Private DNS supports private name resolution. These services do not provide the same general-purpose Azure resource alerting capability. Therefore, Azure Monitor Alerts is the appropriate choice for notification-based monitoring.

Q73. Which Azure SQL monitoring tool provides historical information about query execution and plan changes?

1) Query Store
2) Azure DNS
3) Private Endpoint
4) Database role

Correct Answer: 1)

Explanation:

Query Store captures query text, execution information, runtime statistics, and execution plans over time for supported database configurations. This historical information allows administrators to investigate changes in query behavior and determine whether a query became slower after an execution plan changed. Query Store is particularly useful because traditional performance troubleshooting may otherwise focus only on the current state. Azure DNS and Private Endpoint address networking, while database roles manage authorization. Therefore, when an administrator needs historical query execution information and plan-change analysis, Query Store is the appropriate tool.

Q74. Which query optimization operation can remove unused or redundant indexes after analyzing workload requirements?

1) Index maintenance
2) Database authentication
3) Data masking
4) Backup compression only

Correct Answer: 1)

Explanation:

Index maintenance involves reviewing and managing indexes to ensure they support the actual workload efficiently. Removing unnecessary or redundant indexes can reduce storage consumption and decrease the overhead associated with INSERT, UPDATE, and DELETE operations because every relevant index may need maintenance when table data changes. Administrators should not remove indexes solely because they appear unused over a short observation period; workload patterns and application requirements should be considered carefully. Authentication controls identity, data masking protects displayed values, and backup compression affects backup storage. Therefore, index maintenance is the appropriate concept for evaluating and removing unnecessary indexes.

Q75. Which index type is generally designed to support efficient searches on columns used for equality and range predicates?

1) B-tree-based index
2) Database role
3) Backup index
4) Firewall index

Correct Answer: 1)

Explanation:

B-tree-based indexes are commonly used to efficiently locate rows for equality and range predicates. SQL Server uses structures such as clustered and nonclustered indexes that are based on B-tree organization for many common relational database workloads. Proper index key selection and ordering can significantly affect query performance. Administrators should consider the queries, selectivity, data modification patterns, and storage requirements before creating an index. Database roles and firewall configurations serve authorization and networking purposes rather than data access optimization. Therefore, a B-tree-based index is an appropriate structure for efficiently supporting many equality and range search operations.

Q76. Which SQL Server operation can reduce fragmentation in an existing index without completely rebuilding the index?

1) ALTER INDEX REORGANIZE
2) DROP DATABASE
3) DELETE TABLE
4) CREATE USER

Correct Answer: 1)

Explanation:

ALTER INDEX REORGANIZE can reduce fragmentation by reorganizing the existing leaf-level pages of an index. It is generally an online operation and can be useful when administrators need to address moderate fragmentation while maintaining access to the index. ALTER INDEX REBUILD is another maintenance option and may be more resource-intensive but can perform a more comprehensive reconstruction. DROP DATABASE and DELETE TABLE are destructive database operations, while CREATE USER creates an authentication principal. Therefore, when the requirement specifically asks for an index maintenance operation that reorganizes an existing index rather than completely rebuilding it, ALTER INDEX REORGANIZE is correct.

Q77. Which SQL Server operation creates a new copy of an index structure to address fragmentation and rebuild its pages?

1) ALTER INDEX REBUILD
2) ALTER USER
3) DELETE
4) GRANT

Correct Answer: 1)

Explanation:

ALTER INDEX REBUILD reconstructs an index, creating a new index structure based on the existing table data and index definition. Rebuilding can address fragmentation and can also update index statistics as part of the operation under appropriate configurations. Because rebuilding can consume significant CPU, memory, I/O, and transaction log resources, administrators should plan maintenance according to workload requirements and available maintenance windows. ALTER USER changes user properties, DELETE removes rows, and GRANT assigns permissions. Therefore, when the objective is to reconstruct an index to address fragmentation, ALTER INDEX REBUILD is the appropriate operation.

Q78. Which database configuration determines the SQL Server behavior and compatibility features available to a database?

1) Compatibility level
2) Firewall rule
3) Backup name
4) Storage account label

Correct Answer: 1)

Explanation:

Database compatibility level controls certain database engine behaviors and determines which compatibility features are available to a database. It can be useful when administrators migrate databases to newer versions or environments and need to manage changes in query processing behavior. Compatibility level can sometimes allow an organization to adopt newer database engine capabilities while controlling application compatibility during migration. Firewall rules control network access, while backup names and storage labels do not determine SQL Server query behavior. Therefore, compatibility level is the appropriate configuration when the requirement involves controlling database behavior and compatibility with a particular SQL Server engine version.

Q79. Which database feature can help administrators identify whether query performance changed after an execution plan was modified?

1) Query Store
2) Database schema
3) Firewall rules
4) Data masking

Correct Answer: 1)

Explanation:

Query Store maintains historical information about query execution and plans, allowing administrators to compare performance across different periods and plan versions. This makes it useful for identifying regressions that occur after an execution plan changes. Administrators can examine runtime statistics and execution plans to determine whether a new plan is associated with increased duration or resource consumption. Database schemas organize database objects, firewall rules control network access, and data masking protects sensitive values in query results. Therefore, Query Store is the appropriate feature when the objective is to investigate performance changes associated with execution plan modifications.

Q80. Which practice helps ensure that database administrators can recover an Azure SQL Database if production data becomes corrupted or accidentally deleted?

1) Maintaining an appropriate backup and recovery strategy
2) Disabling all monitoring
3) Removing database permissions
4) Increasing query timeout values

Correct Answer: 1)

Explanation:

A well-designed backup and recovery strategy is essential for protecting databases against accidental deletion, corruption, application errors, and other data-loss scenarios. Azure SQL Database provides automated backup capabilities and supported restore options, but administrators must understand retention periods and recovery requirements to design an appropriate strategy. Depending on business needs, this can include point-in-time recovery and long-term backup retention. Disabling monitoring does not protect data, removing permissions alone does not provide recovery capability, and increasing query timeouts only affects query execution behavior. Therefore, maintaining an appropriate backup and recovery strategy is the correct practice for ensuring database recoverability.