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

 

Question 121. Which Azure service provides centralized monitoring for database resources and applications?

1) Azure Monitor
2) Azure DevOps
3) Azure Storage
4) Azure DNS

Answer: 1) Azure Monitor

Explanation:
Azure Monitor collects and analyzes telemetry from Azure resources, applications, and infrastructure. Database administrators can use it to monitor metrics, logs, alerts, and resource health. For Azure SQL Database, Azure Monitor can help identify performance issues, resource utilization patterns, and operational problems. Administrators can create alerts based on conditions such as high CPU usage or other monitored metrics. Azure DevOps focuses on development and delivery processes, Azure Storage provides storage services, and Azure DNS manages DNS-related functions. Therefore, Azure Monitor is the appropriate service for centralized monitoring and operational visibility.

Question 122. Which SQL Server feature helps administrators analyze query performance over time?

1) Query Store
2) Database Mail
3) SQL Server Browser
4) SQL Server Agent

Answer: 1) Query Store

Explanation:
Query Store collects query text, execution plans, runtime statistics, and other performance information over time. This historical data allows database administrators to investigate query performance changes and identify queries that may have become slower. Query Store can also help compare execution plans and determine whether a plan change contributed to a performance problem. It is especially useful for troubleshooting performance issues that are difficult to reproduce because administrators can examine information from previous executions. SQL Server Agent handles automation, Database Mail supports email notifications, and SQL Server Browser assists with instance discovery.

Question 123. Which authentication service is commonly used to manage identities for Azure SQL Database?

1) Microsoft Entra ID
2) Azure Storage
3) Azure DNS
4) Azure Backup

Answer: 1) Microsoft Entra ID

Explanation:
Microsoft Entra ID provides identity and access management capabilities that can be used with Azure SQL Database. It allows administrators to authenticate users and applications through centralized identity management instead of relying exclusively on SQL authentication. Entra-based authentication can support organizational security requirements and integrate with identity policies. Database administrators can configure Microsoft Entra administrators and create database users associated with Entra identities. Azure Storage is designed for data storage, Azure DNS handles name resolution, and Azure Backup provides backup functionality. Therefore, Microsoft Entra ID is the appropriate identity service for this scenario.

Question 124. Which Transact-SQL statement creates a new database?

1) CREATE DATABASE
2) ADD DATABASE
3) NEW DATABASE
4) MAKE DATABASE

Answer: 1) CREATE DATABASE

Explanation:
The CREATE DATABASE statement is used to create a new database in SQL Server. It can be used with basic syntax or extended to define database files, file sizes, growth settings, and other configuration options. Database administrators may use CREATE DATABASE when manually provisioning databases or when implementing automated deployment scripts. Once the database has been created, administrators can configure security, recovery settings, indexes, and other database properties. The other options are not valid Transact-SQL commands for creating a SQL Server database, making CREATE DATABASE the correct choice.

Question 125. Which feature provides automated backups for Azure SQL Database?

1) Automatic backups
2) SQL Server Agent
3) Database Mail
4) Query Store

Answer: 1) Automatic backups

Explanation:
Azure SQL Database provides automated backups as part of its managed database service. These backups support data protection and enable recovery operations such as point-in-time restore within the supported retention period. Because Azure SQL Database is a platform-managed service, many backup tasks that would normally require manual configuration on traditional SQL Server installations are handled by Azure. SQL Server Agent is primarily used for scheduling jobs, Database Mail provides email functionality, and Query Store captures query performance information. Automated backups therefore provide the required mechanism for protecting Azure SQL Database data.

Question 126. Which recovery option allows an Azure SQL Database to be restored to a specific point in time?

1) Point-in-time restore
2) Full-text search
3) Database cloning
4) Index rebuild

Answer: 1) Point-in-time restore

Explanation:
Point-in-time restore allows an Azure SQL Database to be restored to a specific moment within the available backup retention period. This capability is useful when accidental data changes, deletions, or other database issues occur and the administrator needs to recover the database to an earlier state. The restored database is typically created as a separate database rather than directly overwriting the existing database. Full-text search provides text-search capabilities, database cloning creates copies for specific purposes, and index rebuilding improves index structures. Point-in-time restore is specifically designed for time-based database recovery.

Question 127. Which type of index determines the physical order of data rows in a SQL Server table?

1) Clustered index
2) Nonclustered index
3) Full-text index
4) XML index

Answer: 1) Clustered index

Explanation:
A clustered index determines the logical order in which data rows are stored within the table’s data structure. Because the table data itself is organized according to the clustered index key, a table can have only one clustered index. A clustered index can be useful for queries that frequently retrieve ranges of values or sort data according to the indexed columns. Nonclustered indexes maintain separate structures that reference the underlying data, while full-text and XML indexes are designed for specialized search requirements. Proper clustered index selection can therefore have a significant impact on database query performance.

Question 128. Which command can be used to rebuild an existing SQL Server index?

1) ALTER INDEX … REBUILD
2) UPDATE INDEX
3) CREATE INDEX REPAIR
4) REPAIR TABLE INDEX

Answer: 1) ALTER INDEX … REBUILD

Explanation:
The ALTER INDEX statement with the REBUILD option can be used to rebuild an existing SQL Server index. Rebuilding an index recreates its structure and can help address fragmentation and improve access efficiency when appropriate. Database administrators may perform index maintenance based on workload characteristics and fragmentation analysis rather than rebuilding every index unnecessarily. The command can be targeted at a specific index or, with appropriate syntax, multiple indexes. UPDATE INDEX and the other listed commands are not valid SQL Server statements for rebuilding an index. Proper maintenance planning is important because rebuilding indexes can consume system resources.

Question 129. Which Azure SQL Database capability allows read-only workloads to be directed to secondary replicas?

1) Read scale-out
2) Automatic tuning
3) Transparent Data Encryption
4) Query Store

Answer: 1) Read scale-out

Explanation:
Read scale-out allows supported Azure SQL Database configurations to direct read-only workloads to secondary replicas. This can help reduce the workload placed on the primary database when applications perform significant numbers of read operations. Applications must be designed or configured appropriately to take advantage of the capability. Automatic tuning focuses on performance recommendations and corrections, Transparent Data Encryption protects data at rest, and Query Store captures query performance information. Read scale-out is therefore specifically associated with distributing eligible read workloads to replicas while maintaining the primary database for appropriate operations.

Question 130. Which Azure service should be used to securely store database credentials and other secrets?

1) Azure Key Vault
2) Azure Monitor
3) Azure DNS
4) Azure Load Balancer

Answer: 1) Azure Key Vault

Explanation:
Azure Key Vault provides secure storage and management for secrets, cryptographic keys, and certificates. Database administrators and application developers can use it to protect sensitive credentials rather than embedding them directly in application source code or configuration files. Key Vault integrates with Azure identity and access management capabilities, allowing authorized applications and users to retrieve secrets according to configured permissions. Azure Monitor is used for monitoring, Azure DNS provides DNS services, and Azure Load Balancer distributes network traffic. Therefore, Azure Key Vault is the appropriate service for securely managing database credentials and other sensitive information.

Question 131. Which fixed database role provides full administrative permissions within a SQL Server database?

1) db_datareader
2) db_datawriter
3) db_owner
4) db_securityreader

Answer: 3) db_owner

Explanation:
The db_owner fixed database role provides members with full permissions within the database. Members of this role can perform most database-level administrative activities, including creating, modifying, and deleting database objects and managing permissions. Because db_owner provides extensive privileges, administrators should assign membership carefully and follow the principle of least privilege whenever possible. The db_datareader role is primarily used for reading data, while db_datawriter allows data modification. These roles do not provide the same level of database-wide authority as db_owner.

Question 132. Which fixed database role allows members to read data from all user tables and views?

1) db_datareader
2) db_datawriter
3) db_owner
4) db_ddladmin

Answer: 1) db_datareader

Explanation:
The db_datareader fixed database role provides members with permission to read data from all user tables and views in a database. It is useful when a user or application needs broad read access but does not require permission to modify data or administer database objects. The db_datawriter role focuses on data modification, while db_owner provides extensive database-level permissions. The db_ddladmin role is associated with data definition language operations. Assigning db_datareader can simplify permission management for read-only users, although more granular permissions may be preferable when strict access control is required.

Question 133. Which fixed database role allows members to insert, update, and delete data in all user tables?

1) db_datareader
2) db_datawriter
3) db_owner
4) db_backupoperator

Answer: 2) db_datawriter

Explanation:
The db_datawriter fixed database role allows members to modify data in all user tables within the database. This includes operations such as INSERT, UPDATE, and DELETE. It does not provide the same database-wide administrative authority as db_owner, and it is different from db_datareader, which focuses on reading data. Database administrators should evaluate whether broad data modification access is actually required before assigning this role. In environments with strict security requirements, more specific permissions can be granted to users or applications instead of using a broad fixed database role.

Question 134. Which Azure SQL feature can automatically apply certain performance recommendations such as index changes?

1) Automatic tuning
2) Azure DNS
3) Azure Policy
4) Azure Storage

Answer: 1) Automatic tuning

Explanation:
Automatic tuning in Azure SQL can analyze database workload behavior and provide or apply supported performance recommendations. Depending on configuration and the available tuning options, it can address issues such as missing indexes or problematic index usage. This feature helps administrators reduce manual performance management and respond to changing workloads. Administrators can control whether recommendations are automatically applied or only presented for review. Azure DNS provides name resolution, Azure Storage provides storage services, and Azure Policy is used for governance and compliance. Automatic tuning is therefore the feature most directly associated with automated database performance optimization.

Question 135. Which SQL Server feature encrypts database data files at rest?

1) Transparent Data Encryption
2) Always Encrypted
3) Row-Level Security
4) Dynamic Data Masking

Answer: 1) Transparent Data Encryption

Explanation:
Transparent Data Encryption, commonly called TDE, protects data at rest by encrypting database files. It helps protect the database if the underlying storage or database files are accessed without authorization. TDE operates at the storage level and generally does not require applications to make changes to their queries. Always Encrypted is designed to protect sensitive column values from certain access scenarios, while Row-Level Security controls which rows users can access. Dynamic Data Masking helps obscure sensitive values in query results. Therefore, TDE is the feature specifically associated with encryption of database files at rest.

Question 136. Which statement permanently removes a table and its associated data from a database?

1) DELETE TABLE
2) REMOVE TABLE
3) DROP TABLE
4) CLEAR TABLE

Answer: 3) DROP TABLE

Explanation:
The DROP TABLE statement removes a table definition from the database along with the data stored in that table. It is a structural database operation and should be used carefully because it removes the table object itself. DELETE is different because it removes rows while leaving the table structure in place. Before dropping a table, administrators should verify dependencies, permissions, application requirements, and recovery considerations. The commands REMOVE TABLE and CLEAR TABLE are not standard Transact-SQL statements for deleting a table object. Therefore, DROP TABLE is the correct command for permanently removing the table structure.

Question 137. Which statement modifies existing rows in a SQL Server table?

1) ALTER
2) UPDATE
3) MODIFY
4) CHANGE

Answer: 2) UPDATE

Explanation:
The UPDATE statement modifies values in existing rows within a SQL Server table. Administrators and developers can use a WHERE clause to identify which rows should be changed. Without an appropriate filtering condition, an UPDATE statement can modify every row in the table, so careful testing and transaction management are important. ALTER TABLE is used to change table structure rather than row data. MODIFY and CHANGE are not standard SQL Server statements for updating stored values. UPDATE is therefore the appropriate command when the requirement is to change data in existing records.

Question 138. Which statement removes selected rows from a SQL Server table while preserving the table structure?

1) DELETE
2) DROP
3) REMOVE
4) ERASE

Answer: 1) DELETE

Explanation:
The DELETE statement removes rows from a table while leaving the table definition and other database objects intact. A WHERE clause can be used to identify specific rows that should be deleted. If the WHERE clause is omitted, all rows in the table can be removed, so administrators should use DELETE carefully. DROP TABLE has a much broader effect because it removes the table object itself. REMOVE and ERASE are not standard Transact-SQL commands for deleting table rows. DELETE is therefore the correct statement for removing selected records while retaining the table structure.

Question 139. Which Azure SQL feature provides automatic failover between databases in different Azure regions?

1) Auto-failover groups
2) Query Store
3) Automatic tuning
4) Azure Monitor

Answer: 1) Auto-failover groups

Explanation:
Azure SQL auto-failover groups provide a mechanism for managing geo-replication and automatic failover between databases hosted in different Azure regions. They are designed to support business continuity by allowing applications to reconnect to a secondary database when a failover occurs. Failover groups can also provide listener endpoints that simplify connection management for applications. Query Store focuses on query performance, Automatic tuning manages selected performance recommendations, and Azure Monitor provides monitoring capabilities. Auto-failover groups therefore address the requirement for automated database failover across Azure regions.

Question 140. Which Microsoft tool provides a graphical environment for managing SQL Server and executing T-SQL queries?

1) SQL Server Management Studio
2) Azure Storage Explorer
3) Microsoft Word
4) Windows Event Viewer

Answer: 1) SQL Server Management Studio

Explanation:
SQL Server Management Studio, commonly known as SSMS, provides a graphical environment for administering SQL Server and supported Azure SQL resources. Database administrators can use SSMS to connect to database servers, create and manage databases, execute Transact-SQL queries, configure security, inspect database objects, and perform various administrative tasks. Azure Storage Explorer is designed for managing Azure storage resources, Microsoft Word is a document-processing application, and Windows Event Viewer displays operating system and application event logs. SSMS is therefore the appropriate tool for graphical SQL Server administration and T-SQL query execution.