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

 

Q21. Which Azure SQL Database purchasing model provides pricing based primarily on vCores and configurable compute and storage resources?

1) DTU-based purchasing model
2) vCore-based purchasing model
3) Database transaction model
4) Storage-only purchasing model

Correct Answer: 2)

Explanation:

The vCore-based purchasing model for Azure SQL Database allows organizations to select compute resources and storage according to workload requirements. It provides more control over CPU, memory, storage, and licensing considerations than the DTU model. The vCore model also supports options such as provisioned and serverless compute, depending on the selected service tier. This flexibility makes it useful when administrators need predictable performance characteristics or want to optimize resource allocation. DTU-based purchasing instead uses a combined measure of CPU, memory, reads, and writes. Therefore, when a requirement specifically mentions selecting virtual cores and configurable resources, the vCore purchasing model is the appropriate choice.

Q22. Which Azure SQL Database compute option can automatically pause a database after a period of inactivity?

1) Provisioned compute
2) Hyperscale compute
3) Serverless compute
4) Elastic pool compute

Correct Answer: 3)

Explanation:

Azure SQL Database serverless compute is designed for workloads with variable or unpredictable usage patterns. It can automatically scale compute resources based on demand and can pause the database after a configured period of inactivity when the workload meets the applicable conditions. During the paused state, compute billing can be reduced, while storage remains available. When activity resumes, the database can automatically restart. Provisioned compute maintains allocated resources continuously, making it more suitable for consistently active workloads. Therefore, serverless compute is the appropriate choice when minimizing compute usage during periods of inactivity and automatically adjusting capacity are important requirements.

Q23. Which Azure SQL Database service tier is designed to support very large databases and rapid storage growth?

1) General Purpose
2) Business Critical
3) Hyperscale
4) Basic

Correct Answer: 3)

Explanation:

The Hyperscale service tier is designed for workloads requiring very large database sizes and scalable storage architecture. Instead of relying on the same storage architecture used by traditional database tiers, Hyperscale separates compute from storage and uses a distributed architecture to support significant database growth. It can also provide rapid scaling of storage without requiring administrators to plan for a fixed maximum storage allocation in the same way as some other tiers. General Purpose and Business Critical are appropriate for many production workloads, but Hyperscale specifically addresses large and rapidly growing databases. Therefore, Hyperscale is the appropriate answer for this requirement.

Q24. A company has several Azure SQL databases with unpredictable usage patterns. Which feature can help share resources among databases?

1) Elastic pool
2) Transparent Data Encryption
3) Query Store
4) Database auditing

Correct Answer: 1)

Explanation:

Azure SQL Database elastic pools allow multiple databases to share a pool of compute resources. This is especially useful when databases have variable or unpredictable workloads that do not all reach peak usage simultaneously. Instead of provisioning each database for its individual maximum demand, an administrator can place compatible databases in an elastic pool and allow them to consume available resources as needed. This approach can improve resource utilization and simplify management for collections of databases. Features such as Query Store and auditing address performance analysis and security rather than shared compute allocation. Therefore, an elastic pool is the appropriate solution for this scenario.

Q25. Which Azure service provides a managed SQL Server instance that supports many instance-level features and provides high compatibility with SQL Server?

1) Azure SQL Database
2) Azure SQL Managed Instance
3) Azure Table Storage
4) Azure Cosmos DB

Correct Answer: 2)

Explanation:

Azure SQL Managed Instance is a fully managed platform-as-a-service offering designed to provide broad compatibility with SQL Server while reducing administrative responsibilities. It supports many SQL Server features that depend on instance-level behavior, making it useful for organizations migrating existing SQL Server applications with fewer application changes. Azure SQL Database provides a more database-centric platform and does not expose the same level of instance functionality. Azure Table Storage and Azure Cosmos DB are different database services and are not managed SQL Server environments. Therefore, Azure SQL Managed Instance is the appropriate choice when SQL Server compatibility and instance-level capabilities are important requirements.

Q26. An organization wants to move an existing SQL Server database to Azure while retaining control over the operating system. Which option should be considered?

1) Azure SQL Database
2) Azure SQL Managed Instance
3) SQL Server on Azure Virtual Machines
4) Azure Table Storage

Correct Answer: 3)

Explanation:

SQL Server on Azure Virtual Machines provides an infrastructure-as-a-service approach in which administrators manage the virtual machine and operating system while running SQL Server within it. This option is useful when applications require operating-system-level access, specific SQL Server configurations, third-party components, or features that are not available in platform-managed services. Azure SQL Database and Azure SQL Managed Instance reduce infrastructure management responsibilities, but they do not provide the same operating-system control. Azure Table Storage is not a SQL Server platform. Therefore, when retaining operating system control is an explicit requirement, SQL Server on an Azure Virtual Machine is the appropriate option.

Q27. Which feature allows an Azure SQL Database to be accessed privately through an Azure virtual network?

1) Public firewall rule
2) Private endpoint
3) Query Store
4) Database role

Correct Answer: 2)

Explanation:

An Azure Private Endpoint provides a private network interface within an Azure virtual network that connects privately to supported Azure services. For Azure SQL Database, using a private endpoint can allow applications to connect through a private IP address rather than relying on public network access. This approach can help organizations design network architectures where database connectivity remains within private networking boundaries. Firewall rules can restrict public connections but do not themselves create a private endpoint. Query Store and database roles address performance and authorization. Therefore, when the requirement is private network connectivity to Azure SQL Database, a private endpoint is the appropriate solution.

Q28. Which authentication method allows users to authenticate to Azure SQL resources by using identities managed through Microsoft Entra ID?

1) Microsoft Entra authentication
2) Windows local authentication
3) Anonymous authentication
4) FTP authentication

Correct Answer: 1)

Explanation:

Microsoft Entra authentication allows users and applications to authenticate to supported Azure SQL services using identities managed through Microsoft Entra ID. This can support centralized identity management, multifactor authentication, group-based access, and integration with organizational identity policies. Depending on the configuration, administrators can use Microsoft Entra users, groups, service principals, or managed identities for database access. SQL authentication remains available for supported scenarios, but it relies on SQL-specific credentials rather than Microsoft Entra identities. Anonymous and FTP authentication are not appropriate mechanisms for securely authenticating users to Azure SQL databases. Therefore, Microsoft Entra authentication is the correct choice.

Q29. Which database permission statement allows an administrator to assign a specific permission to a user or role?

1) DROP
2) DENY
3) GRANT
4) ALTER DATABASE

Correct Answer: 3)

Explanation:

The GRANT statement is used to provide a specified permission to a database principal such as a user or role. For example, an administrator can grant SELECT permission on a table to a database role, allowing members of that role to retrieve data. DENY explicitly prevents a permission, while REVOKE removes a previously granted or denied permission in applicable situations. Database administrators should generally follow the principle of least privilege by granting only the permissions necessary for a user or application to perform its required tasks. Therefore, when the requirement is to assign a specific database permission, the GRANT statement is the appropriate choice.

Q30. Which statement is used to explicitly prevent a database principal from performing a specific permissioned operation?

1) GRANT
2) DENY
3) SELECT
4) EXECUTE

Correct Answer: 2)

Explanation:

The DENY statement explicitly prevents a database principal from receiving a specified permission through normal permission inheritance. It can be applied to users or roles depending on the security design. GRANT provides permissions, while REVOKE removes an existing permission assignment without necessarily creating an explicit prohibition. Understanding the difference is important when designing SQL Server and Azure SQL authorization models. Administrators should carefully evaluate role membership and permission inheritance because an explicit DENY can affect access behavior differently from simply not granting a permission. Therefore, DENY is the correct statement when an administrator needs to explicitly prohibit a database operation.

Q31. Which security feature protects sensitive column values by encrypting data so that database administrators cannot directly access the plaintext values?

1) Dynamic Data Masking
2) Always Encrypted
3) Auditing
4) Query Store

Correct Answer: 2)

Explanation:

Always Encrypted is designed to protect sensitive data by keeping encryption keys under the control of trusted client applications rather than making plaintext values directly available to the database engine. This can help protect sensitive column data from database administrators or other users who should not have access to plaintext information. Dynamic Data Masking instead obscures returned values for certain users but does not provide the same cryptographic protection. Auditing records activities, while Query Store collects query performance information. Therefore, when the requirement specifically involves protecting sensitive column values from database-side access to plaintext, Always Encrypted is the appropriate security feature.

Q32. Which Azure SQL feature can obscure sensitive data in query results without changing the underlying stored data?

1) Dynamic Data Masking
2) Transparent Data Encryption
3) Point-in-time restore
4) Automatic tuning

Correct Answer: 1)

Explanation:

Dynamic Data Masking helps limit exposure of sensitive information by masking portions of data returned to users who do not have permission to see the complete values. The underlying data remains unchanged in the database. For example, a phone number or email address can be displayed in a partially masked form to certain users. Transparent Data Encryption protects database files at rest rather than selectively masking query results. Point-in-time restore is a recovery capability, and automatic tuning focuses on database performance. Therefore, Dynamic Data Masking is the appropriate choice when the objective is to obscure sensitive values in query results while preserving the original stored information.

Q33. Which security feature can restrict which rows a user is allowed to access in a database table?

1) Row-Level Security
2) Transparent Data Encryption
3) Database backup
4) Query Store

Correct Answer: 1)

Explanation:

Row-Level Security enables administrators to control access to individual rows in a database table based on the execution context of the user or application. Security policies can use predicates to determine which rows are visible or modifiable. This is useful in multi-tenant applications or organizations where users should access only records associated with their department, region, customer account, or other security context. Transparent Data Encryption protects data at rest but does not determine which rows users can query. Query Store is a performance feature, and backups are designed for recovery. Therefore, Row-Level Security is the correct feature for controlling row-level data access.

Q34. Which Azure SQL capability records database activity to help organizations investigate security events and satisfy auditing requirements?

1) Automatic scaling
2) Auditing
3) Elastic pools
4) Serverless compute

Correct Answer: 2)

Explanation:

Azure SQL Auditing records database events and can help organizations monitor database activity for security, compliance, and investigation purposes. Depending on the configuration, audit records can capture information about actions such as database operations and authentication-related activity. The resulting logs can be stored and analyzed using supported Azure services. Automatic scaling and elastic pools focus on resource management, while serverless compute adjusts compute resources based on workload activity. Auditing should be designed according to organizational retention and compliance requirements so that useful records are available when needed. Therefore, Auditing is the appropriate capability for recording database activity for security analysis.

Q35. Which Azure SQL security service can help identify potential vulnerabilities and threats affecting SQL databases?

1) Microsoft Defender for SQL
2) Azure Load Balancer
3) Azure DNS
4) Azure Storage Explorer

Correct Answer: 1)

Explanation:

Microsoft Defender for SQL provides security capabilities designed to help protect SQL Server environments and Azure SQL resources. It can provide threat detection and security assessments that help administrators identify potential risks, suspicious activities, and configuration weaknesses. This complements other security controls such as authentication, authorization, encryption, and auditing. Azure Load Balancer is a networking service, Azure DNS manages name resolution, and Azure Storage Explorer is a management tool for storage resources. Therefore, when an administrator needs SQL-focused threat detection and security assessment capabilities, Microsoft Defender for SQL is the appropriate service to consider.

Q36. Which Azure SQL Database recovery feature allows an administrator to restore a database to a specific time within the configured backup retention period?

1) Database cloning
2) Point-in-time restore
3) Query Store
4) Data masking

Correct Answer: 2)

Explanation:

Point-in-time restore allows an Azure SQL Database to be restored to a selected point within the available backup retention period. This capability is useful when accidental deletion, incorrect updates, or other database changes need to be reversed. The restored database is created from the available backups and transaction information maintained by the service. It is important for administrators to understand the configured retention period because a restore point outside that period may not be available. Query Store is intended for query performance information, while data masking protects displayed information. Therefore, point-in-time restore is the appropriate recovery feature for this requirement.

Q37. Which Azure SQL capability is intended to retain database backups for an extended period beyond normal short-term backup retention?

1) Long-term retention
2) Query Store
3) Dynamic Data Masking
4) Automatic tuning

Correct Answer: 1)

Explanation:

Long-term retention, commonly abbreviated as LTR, allows supported Azure SQL databases to retain backups for much longer periods than standard short-term backup retention. It is useful when organizations have regulatory, legal, or business requirements to preserve database backups for months or years. Administrators can configure retention policies according to organizational requirements and supported service capabilities. Query Store focuses on query performance history, Dynamic Data Masking protects sensitive information in query results, and automatic tuning addresses performance optimization. Therefore, when the requirement is to preserve database backups for an extended period for compliance or historical recovery purposes, long-term retention is the appropriate capability.

Q38. Which Azure SQL feature can automatically recommend or apply certain performance improvements based on observed workload behavior?

1) Automatic tuning
2) Auditing
3) Private Link
4) Microsoft Entra ID

Correct Answer: 1)

Explanation:

Azure SQL automatic tuning uses workload information and database performance signals to identify opportunities for optimization. Depending on the supported configuration and recommendation, it can help with actions such as index-related improvements or plan correction. Automatic tuning is intended to reduce the amount of manual performance management required by database administrators while maintaining appropriate controls over automated changes. Query Store provides important historical query performance information and can support tuning decisions, but it is not itself the mechanism responsible for automatically applying tuning recommendations. Therefore, automatic tuning is the appropriate answer when the requirement is automated performance optimization based on workload behavior.

Q39. Which Azure service can collect SQL-related monitoring data and support analysis of database performance metrics and logs?

1) Azure Monitor
2) Azure DNS
3) Azure Bastion
4) Azure Front Door

Correct Answer: 1)

Explanation:

Azure Monitor provides monitoring capabilities for Azure resources, including supported Azure SQL services. It can collect metrics and logs and allows administrators to analyze resource health, performance, and operational behavior. Database administrators can use monitoring information to investigate issues such as resource pressure, abnormal activity, or changing workload patterns. Azure Monitor can also integrate with alerting and other Azure monitoring components. Azure DNS, Azure Bastion, and Azure Front Door serve different purposes related to name resolution, secure administrative access, and application delivery. Therefore, Azure Monitor is the appropriate service when centralized monitoring of Azure SQL performance and operational data is required.

Q40. Which database object is commonly used to store a predefined collection of permissions that can be assigned to users?

1) Index
2) Database role
3) Stored data page
4) Execution plan

Correct Answer: 2)

Explanation:

A database role is used to group permissions and simplify authorization management. Administrators can assign permissions to a role and then add appropriate users or other principals to that role rather than individually granting every permission to every user. This makes security administration easier to manage and can support the principle of least privilege when roles are carefully designed. Indexes improve data access performance, execution plans describe how queries are processed, and data pages are storage structures rather than authorization objects. Therefore, when the requirement is to create a predefined collection of permissions that can be assigned through membership, a database role is the appropriate database object.