View Full Microsoft DP-300 Exam Dumps and Practice Test Dumps
Q81. Which Azure SQL Database purchasing model allows you to select resources based on virtual CPU cores?
1) DTU-based model
2) vCore-based model
3) Serverless model
4) Consumption model
Correct Answer: 2)
Explanation:
The vCore-based purchasing model allows administrators to select Azure SQL Database resources based on virtual CPU cores, memory, and storage requirements. This model provides greater flexibility when sizing a database for a specific workload. It is also useful when comparing Azure SQL Database resources with existing SQL Server environments because administrators can evaluate CPU and memory requirements directly. The DTU-based model combines CPU, memory, and I/O into a single performance unit. The vCore model is commonly selected when organizations need greater control over resource configuration, scaling, and cost management for their relational database workloads.
Q82. Which Azure SQL Database option can automatically pause compute resources when the database remains inactive?
1) Hyperscale
2) Business Critical
3) Serverless compute
4) Elastic pool
Correct Answer: 3)
Explanation:
Azure SQL Database serverless compute is designed for workloads with intermittent or unpredictable usage. When configured with an appropriate auto-pause setting, the database can automatically pause compute resources after a specified period of inactivity. This can help reduce compute costs during periods when the database is not being actively used. When activity resumes, compute resources can restart automatically. Serverless is particularly useful for development, testing, and applications with variable workloads. Administrators should consider the workload pattern and possible resume latency before selecting serverless compute for a production database.
Q83. Which Azure SQL Database feature allows multiple databases to share a pool of compute resources?
1) Elastic pool
2) Private endpoint
3) Query Store
4) Failover group
Correct Answer: 1)
Explanation:
An elastic pool provides a shared collection of compute resources for multiple Azure SQL databases. It is useful when databases have different or unpredictable usage patterns because they do not all need to be provisioned for their maximum possible demand simultaneously. Databases within the pool can share available resources according to workload requirements. Elastic pools are particularly useful for applications that manage many databases, such as software-as-a-service environments. By sharing resources, organizations can improve utilization and manage costs more efficiently. Administrators should evaluate the combined workload of the databases when determining the appropriate pool configuration.
Q84. Which Azure deployment option provides the greatest control over the operating system running SQL Server?
1) Azure SQL Database
2) Azure SQL Managed Instance
3) SQL Server on Azure Virtual Machines
4) Azure Database for MySQL
Correct Answer: 3)
Explanation:
SQL Server on Azure Virtual Machines provides the greatest operating-system control because administrators manage the virtual machine that hosts SQL Server. This allows them to configure Windows settings, install software, manage operating-system components, and control many SQL Server configuration options. This deployment model is useful when an application requires specific operating-system configurations or compatibility with an existing SQL Server environment. However, greater control also means greater administrative responsibility. Organizations must manage tasks such as operating-system maintenance, patching, configuration, and availability planning. Managed Azure SQL services reduce these infrastructure management responsibilities.
Q85. Which Azure SQL service provides broad SQL Server compatibility while reducing infrastructure management responsibilities?
1) Azure Table Storage
2) Azure SQL Managed Instance
3) Azure Cosmos DB
4) Azure Blob Storage
Correct Answer: 2)
Explanation:
Azure SQL Managed Instance is a fully managed relational database service designed to provide broad compatibility with SQL Server while reducing infrastructure administration. It supports many SQL Server features and instance-level capabilities that are important for applications being migrated from traditional SQL Server environments. Microsoft manages the underlying infrastructure, allowing database administrators to focus more on database configuration and workload management. Managed Instance is useful when organizations want to move existing SQL Server workloads to Azure with fewer application changes. It provides a managed experience while retaining many capabilities associated with traditional SQL Server.
Q86. Which feature provides a private IP address for accessing an Azure SQL resource from an Azure virtual network?
1) Public IP address
2) Private endpoint
3) Service tag
4) Network security group
Correct Answer: 2)
Explanation:
A private endpoint creates a private network interface with a private IP address inside an Azure virtual network. Applications can use this private connection to communicate with supported Azure services without relying on a publicly accessible endpoint. For Azure SQL resources, private endpoints can help organizations implement network isolation and reduce exposure to public network access. Proper DNS configuration is also important because clients need to resolve the service name to the private endpoint address. Private endpoints are commonly used together with virtual networks and private DNS zones when designing secure database connectivity.
Q87. Which identity option allows an Azure application to authenticate to supported services without storing credentials in application code?
1) Managed identity
2) SQL authentication login
3) Local Windows account
4) Database administrator account
Correct Answer: 1)
Explanation:
A managed identity gives an Azure resource an identity that applications can use to authenticate to supported Azure services without storing passwords or client secrets in application code. Azure manages the credentials associated with the identity, reducing the need for administrators and developers to maintain secrets manually. After enabling the managed identity, administrators must assign the required permissions to the identity on the target resource or database. This approach can improve credential management and simplify authentication. Managed identities are especially useful for Azure-hosted applications that need controlled access to Azure SQL Database and other Azure services.
Q88. Which SQL statement is used to remove a previously granted permission?
1) GRANT
2) DENY
3) REVOKE
4) CREATE USER
Correct Answer: 3)
Explanation:
The REVOKE statement is used to remove a previously granted or denied permission from a database principal. Database administrators use GRANT, DENY, and REVOKE to manage access to database objects and resources. GRANT provides a permission, while DENY explicitly prevents a principal from using a permission. REVOKE removes the explicit permission assignment. Understanding the difference between these commands is important when managing database security and troubleshooting access problems. Administrators should also review role memberships because a user may continue to have access through another database role even after a direct permission has been revoked.
Q89. Which feature protects sensitive column data by encrypting it so that the database engine does not have access to the plaintext in supported configurations?
1) Dynamic Data Masking
2) Transparent Data Encryption
3) Always Encrypted
4) Row-Level Security
Correct Answer: 3)
Explanation:
Always Encrypted is designed to protect sensitive information stored in selected database columns. In supported configurations, encryption and decryption occur on the client side, which means the database engine does not have access to the plaintext values or encryption keys. This provides additional protection for highly sensitive information such as financial or personally identifiable data. Transparent Data Encryption protects database files at rest, while Dynamic Data Masking changes how sensitive values are displayed to users. Row-Level Security controls access to individual rows. Therefore, Always Encrypted is specifically intended for protecting sensitive column values.
Q90. Which security feature allows administrators to restrict users so they can access only specific rows in a table?
1) Transparent Data Encryption
2) Row-Level Security
3) Dynamic Data Masking
4) Database Auditing
Correct Answer: 2)
Explanation:
Row-Level Security allows database administrators to control access to individual rows based on the identity, role, or session context of a user. For example, an organization can create a security policy that allows employees to see only records belonging to their department. The filtering is enforced at the database level, helping prevent unauthorized access to other rows. Dynamic Data Masking serves a different purpose because it changes how sensitive values are displayed. Transparent Data Encryption protects stored database files, while auditing records database activity. Row-Level Security is therefore appropriate when access must be restricted according to individual records.
Q91. Which Azure SQL capability records database activity for security, monitoring, and compliance purposes?
1) Query Store
2) Azure SQL Auditing
3) Automatic tuning
4) Elastic pools
Correct Answer: 2)
Explanation:
Azure SQL Auditing records database events so administrators can review activity for security, compliance, and investigative purposes. Audit information can help organizations understand who accessed a database and which operations were performed, depending on the configured auditing settings. Audit records can be sent to supported destinations for storage and analysis. Query Store has a different purpose because it collects query performance and execution information. Automatic tuning focuses on database performance optimization, while elastic pools provide shared compute resources for multiple databases. Auditing is therefore primarily a security and governance capability rather than a database performance feature.
Q92. Which Azure security capability provides threat detection and security recommendations for SQL workloads?
1) Microsoft Defender for SQL
2) Azure Load Balancer
3) Azure DNS
4) Azure Bastion
Correct Answer: 1)
Explanation:
Microsoft Defender for SQL provides security capabilities designed to help protect SQL workloads and identify potential security threats. It can provide alerts related to suspicious database activities and recommendations that can help administrators improve the security posture of SQL resources. Defender for SQL complements other controls such as authentication, authorization, auditing, encryption, and network security. Azure Load Balancer is designed to distribute network traffic, Azure DNS provides name resolution, and Azure Bastion provides secure administrative access to virtual machines. Therefore, Microsoft Defender for SQL is the capability specifically focused on SQL workload security monitoring and protection.
Q93. Which Azure SQL recovery option allows a database to be restored to a specific point within the backup retention period?
1) Point-in-time restore
2) Elastic pool
3) Query Store
4) Automatic tuning
Correct Answer: 1)
Explanation:
Point-in-time restore allows administrators to restore an Azure SQL Database to a specific point within the available backup retention period. This capability is useful when accidental deletion, incorrect updates, or other logical database problems occur. Instead of restoring only the most recent backup, administrators can select an earlier point in time that represents the desired database state. The restored database can then be used for recovery or investigation. Point-in-time restore is an important part of a database recovery strategy because it provides flexibility when responding to accidental changes and other logical data issues.
Q94. Which feature is designed to retain Azure SQL Database backups for a longer period to meet long-term recovery requirements?
1) Query Store
2) Long-term retention
3) Dynamic Data Masking
4) Automatic tuning
Correct Answer: 2)
Explanation:
Long-term retention, commonly called LTR, allows Azure SQL Database backups to be retained for an extended period beyond the normal short-term backup retention window. It is useful for organizations that need to preserve database backups for regulatory, compliance, or historical recovery requirements. Administrators can configure retention policies according to organizational needs. LTR is different from point-in-time restore, which is intended for recovering a database to a specific time within the standard retention period. Long-term retention provides an approach for preserving selected backups for much longer periods while maintaining a recoverable copy of the database.
Q95. Which feature can automatically identify and apply supported performance recommendations to an Azure SQL Database?
1) Automatic tuning
2) Private endpoint
3) Azure SQL Auditing
4) Microsoft Entra ID
Correct Answer: 1)
Explanation:
Automatic tuning helps Azure SQL Database identify certain performance-related opportunities and can automatically apply supported tuning recommendations. Depending on the workload and configuration, automatic tuning can help manage indexes and query execution plans. The purpose is to reduce the amount of manual performance administration required from database administrators. Administrators can review tuning recommendations and configure which automatic tuning options are enabled. Private endpoints provide network connectivity, auditing records database activity, and Microsoft Entra ID provides identity and authentication capabilities. Automatic tuning is therefore specifically associated with automated database performance optimization.
Q96. Which Azure service can collect metrics and provide monitoring capabilities for Azure SQL resources?
1) Azure Monitor
2) Azure Storage Explorer
3) Azure DNS
4) Azure Bastion
Correct Answer: 1)
Explanation:
Azure Monitor provides monitoring capabilities for Azure resources, including Azure SQL databases and related services. It can collect platform metrics and logs that administrators can use to understand resource performance and identify potential problems. Azure Monitor can also be used with alerts, dashboards, and other monitoring components to help administrators respond to changing conditions. Azure DNS is responsible for name resolution, Azure Bastion provides secure access to virtual machines, and Storage Explorer is primarily a management tool for Azure Storage resources. Azure Monitor is therefore an important component of an Azure SQL monitoring strategy.
Q97. Which database object is commonly used to improve the performance of queries that frequently search or sort data using specific columns?
1) Index
2) Trigger
3) View
4) Stored procedure
Correct Answer: 1)
Explanation:
An index provides an optimized data structure that can help the database engine locate rows more efficiently when executing queries. Indexes can be particularly useful for columns frequently used in search predicates, joins, sorting, or grouping operations. However, indexes also require storage and maintenance, and excessive indexing can negatively affect write performance because indexes may need to be updated when data changes. Database administrators should therefore evaluate query workloads and execution plans before creating indexes. Proper indexing can significantly improve read performance when the index matches the access patterns used by important database queries.
Q98. Which SQL Server feature provides information about how the database engine intends to execute a query?
1) Execution plan
2) Database role
3) Firewall rule
4) Audit policy
Correct Answer: 1)
Explanation:
An execution plan shows how the database engine processes a query. It can display operations such as index seeks, scans, joins, sorts, and other activities used to retrieve or modify data. Database administrators can analyze execution plans to identify expensive operations and understand why a query may be performing poorly. Execution plans can also help determine whether indexes, statistics, query changes, or other tuning actions may be appropriate. They are an important tool for SQL Server performance troubleshooting because they provide visibility into the query optimizer’s selected approach for executing a statement.
Q99. Which feature stores query execution information and can help identify changes in query performance over time?
1) Query Store
2) Elastic pool
3) Private endpoint
4) Database firewall
Correct Answer: 1)
Explanation:
Query Store collects query-related information such as query text, execution plans, and runtime performance data. It allows administrators to analyze how queries perform over time and identify situations where a query’s performance changes after an execution plan change. Query Store can also help administrators investigate plan regressions and determine whether a different execution plan may improve performance. This makes it an important tool for performance troubleshooting in Azure SQL and SQL Server environments. Unlike a firewall or private endpoint, Query Store focuses on database workload performance rather than network connectivity or security configuration.
Q100. Which practice is most important when designing a database backup and recovery strategy?
1) Store only the latest backup
2) Disable automated backups
3) Define recovery objectives and regularly test restores
4) Use indexes instead of backups
Correct Answer: 3)
Explanation:
A reliable database backup and recovery strategy should begin by defining recovery objectives, including how much data loss is acceptable and how quickly the database must be restored after an incident. Administrators should configure appropriate backup retention and recovery options based on these requirements. Regular restore testing is also important because having backups does not guarantee that the recovery process will work as expected. Organizations should periodically validate their recovery procedures and ensure that responsible personnel understand the process. A tested recovery strategy helps reduce uncertainty during incidents and provides greater confidence that databases can be recovered when required.