Microsoft DP-300 Administering Microsoft Azure SQL Solutions Exam Dumps and Practice Test Questions Set 6 Q101-120

Visit here for our full Microsoft DP-300 exam dumps and practice test questions.

Question 101 

You want to automatically scale compute resources for a database based on workload demand and pause it during inactivity to reduce costs. Which feature should you enable?

A) Serverless compute tier
B) Business Critical tier
C) Hyperscale tier
D) Elastic Pool

Answer:  A) Serverless compute tier

Explanation:

The Serverless compute tier in Azure SQL Database is specifically designed to handle variable workloads efficiently. It automatically scales compute resources up or down depending on current demand, which means that when the workload increases, it provisions additional CPU and memory to maintain performance. Conversely, when demand decreases, it reduces compute resources to minimize cost. One of the unique aspects of the serverless model is the ability to pause the database entirely during periods of inactivity, which stops billing for compute while still keeping the database accessible. This automatic scaling and pausing make serverless particularly suitable for environments with unpredictable or intermittent workloads.

The Business Critical tier, on the other hand, provides high availability and fixed compute resources. It is optimized for low-latency, mission-critical workloads that require fast I/O, but it does not offer automatic scaling of compute based on demand. Users are charged for the provisioned compute continuously, and there is no mechanism to pause the database during periods of inactivity. While it is excellent for performance-sensitive workloads that require predictable latency, it does not offer the cost optimization features that serverless provides.

Hyperscale is designed for very large databases and provides rapid storage scaling and high performance for massive data sizes. It separates compute and storage, allowing storage to scale independently, which is ideal for databases with high growth or complex workloads. However, hyperscale does not offer automatic pausing or dynamic scaling of compute resources in response to short-term workload fluctuations. Users must provision compute ahead of time, which can lead to overprovisioning and higher costs if the workload is sporadic.

Elastic Pool is a solution for managing multiple databases that share a set of allocated resources. While it helps optimize costs when multiple databases have uneven workloads, it does not automatically scale compute for individual databases or pause them during inactivity. The resource sharing is static, and while it prevents one database from consuming all resources, it does not dynamically respond to changes in workload for a single database.

Given the requirements of automatically scaling compute based on demand and pausing during inactivity to save costs, the serverless compute tier is the only option that directly addresses both aspects. Its dynamic resource management and pause/resume capabilities make it the ideal choice for databases with variable or intermittent usage patterns.

Question 102 

You want to provide high availability across regions with automatic client redirection after failover. Which feature should you enable?

A) Auto-Failover Groups
B) Read Scale-Out
C) Transparent Data Encryption
D) Elastic Pool

Answer:  A) Auto-Failover Groups

Explanation:

Auto-Failover Groups in Azure SQL Database are specifically designed to provide automatic high availability and disaster recovery across regions. They replicate databases to a secondary region and provide automatic failover capabilities. When a failover occurs, client connections are redirected to the secondary replica automatically, without requiring changes to connection strings. This ensures minimal downtime and continuity for applications. Auto-Failover Groups also allow for configurable failover policies, including planned and unplanned failovers, making them suitable for business continuity planning.

Read Scale-Out is a feature that allows offloading of read-only queries to secondary replicas within the same region. While it improves performance for reporting or analytics workloads, it does not provide cross-region high availability or automatic client redirection in case of a regional outage. Its focus is performance optimization rather than failover or disaster recovery.

Transparent Data Encryption secures data at rest by encrypting database files, ensuring that sensitive information is protected. However, it does not provide any mechanisms for high availability or cross-region failover. While essential for security compliance, TDE does not help with automatic client redirection or maintaining application availability during outages.

Elastic Pool allows multiple databases to share resources efficiently, reducing overall costs when workloads fluctuate. Although it is excellent for resource optimization, it does not provide automatic failover across regions or client redirection. Elastic Pool is about managing compute and storage for cost efficiency rather than ensuring high availability or resilience.

Because the scenario specifically requires high availability across regions and automatic client redirection after failover, Auto-Failover Groups is the only option that satisfies both requirements. It ensures seamless failover and continuity for applications without manual intervention.

Question 103 

You need to enforce row-level access restrictions in a table based on user department. Which feature should you implement?

A) Row-Level Security
B) Dynamic Data Masking
C) Always Encrypted
D) Transparent Data Encryption

Answer:  A) Row-Level Security

Explanation:

Row-Level Security (RLS) allows fine-grained access control at the level of individual rows in a database table. Policies can be defined that filter rows based on user attributes, such as department, so that users can only access data relevant to them. This enforcement happens at the database engine level, ensuring that applications cannot bypass access controls. RLS is particularly useful for organizations with multi-tenant data models or compliance requirements that require limiting access to sensitive information.

Dynamic Data Masking hides or obfuscates sensitive column data when queries are executed, allowing users to see only masked values. While it helps prevent exposure of sensitive information in results, it does not restrict access to specific rows. Users still receive all rows, but sensitive columns may be partially masked. Therefore, it does not satisfy the requirement to enforce department-based row restrictions.

Always Encrypted ensures that sensitive data is encrypted both at rest and in transit, and that decryption happens only on the client side. While it prevents the database engine from seeing plaintext data, it does not implement access policies based on user attributes or enforce row-level restrictions. It is a security feature focused on protecting sensitive data, not controlling who sees specific rows.

Transparent Data Encryption encrypts database files at rest to secure them from unauthorized access. It ensures data safety but does not enforce access control for users within the database or filter rows based on user department. Its role is to secure storage rather than control row-level visibility.

Row-Level Security directly addresses the requirement of department-based access restrictions by dynamically filtering rows according to user context. It provides fine-grained access enforcement at the row level, making it the correct choice for scenarios requiring precise access controls.

Question 104 

You want to store audit logs in a centralized, durable, and secure location for regulatory compliance. Which destination should you choose?

A) Azure Storage account
B) Log Analytics workspace
C) Event Hubs
D) Power BI

Answer:  A) Azure Storage account

Explanation:

Azure Storage accounts provide a reliable and highly durable storage solution for long-term retention of audit logs. They offer encryption at rest, redundancy options such as geo-redundant storage, and scalable storage capacity suitable for regulatory compliance. Logs stored in a storage account remain immutable and accessible for auditing and regulatory purposes, making them ideal for long-term retention.

Log Analytics workspace allows you to collect, query, and analyze telemetry data from various sources, including SQL Database. While it is excellent for monitoring and operational analysis, it is not optimized for long-term log retention or regulatory compliance storage. Logs may be stored temporarily, and retention policies are limited compared to the durability offered by a storage account.

Event Hubs is designed for high-throughput event streaming and real-time analytics. It is intended to ingest and process large volumes of events rather than store them permanently. While logs can pass through Event Hubs, it is not a suitable destination for centralized, durable audit log storage required for compliance.

Power BI is a visualization and reporting tool that helps analyze data interactively. It cannot serve as a secure or durable storage solution for audit logs. Its role is presentation and reporting, not secure archival of raw log data.

Azure Storage accounts meet all the requirements for compliance: centralized storage, durability, security, and long-term retention. Therefore, it is the best option for storing audit logs in a regulatory-compliant manner.

Question 105 

You want to monitor query performance and preserve historical execution plans to detect regressions. Which feature should you enable?

A) Query Store
B) Extended Events
C) SQL Auditing
D) Intelligent Insights

Answer:  A) Query Store

Explanation:

Query Store captures a history of query execution plans, runtime statistics, and performance metrics over time. This allows database administrators to analyze trends, identify regressions, and understand why a query’s performance changed. The stored data makes it possible to force a previous execution plan to maintain consistent performance, which is particularly valuable for long-running applications where performance regressions can occur after schema changes or updates.

Extended Events provide a flexible framework for capturing diagnostic events in SQL Server. While they can capture detailed information about query execution and system activity, the data is event-driven rather than structured for historical analysis. It requires manual configuration and analysis, which makes it less suitable for automatically tracking execution plan regressions over time.

SQL Auditing records database activities and transactions for compliance purposes. It focuses on tracking access to sensitive data and operations rather than capturing execution plan history or analyzing performance trends. Auditing is essential for security and compliance, but it does not provide the historical query performance data needed for regression detection.

Intelligent Insights analyzes database performance and provides recommendations for improving workload efficiency. While useful for proactive monitoring and optimization, it does not retain historical execution plans for detailed regression analysis. It is advisory rather than a mechanism to store and compare query execution history.

Query Store is purpose-built for tracking query performance over time, preserving execution plans, and allowing administrators to identify and remediate regressions. Its focus on historical performance makes it the correct feature for monitoring and regression detection.

Question 106 

You need to detect and automatically remediate query plan regressions in Azure SQL Database. Which feature should you use?

A) Automatic Plan Correction
B) Query Store
C) Intelligent Insights
D) Extended Events

Answer:  A) Automatic Plan Correction

Explanation:

Automatic Plan Correction is a feature in Azure SQL Database specifically designed to address query performance regressions caused by suboptimal execution plans. It continuously monitors query performance and identifies queries whose current execution plans result in degraded performance compared to historical baselines. When such regressions are detected, Automatic Plan Correction automatically forces previously known good plans, restoring optimal performance without manual intervention. This ensures that workloads remain efficient and consistent even as changes occur in database statistics or indexes.

Query Store is a complementary feature that tracks query performance over time by storing historical execution plans and runtime statistics. While Query Store is critical for diagnosing query performance issues and provides insights into plan changes, it does not automatically remediate plan regressions. Instead, it serves as a repository of performance history that can be used by Automatic Plan Correction to identify which plans are effective or by DBAs to manually enforce good plans. Query Store is necessary for understanding performance patterns but cannot resolve regressions autonomously.

Intelligent Insights analyzes performance metrics and identifies potential problems, providing actionable recommendations for query tuning or resource adjustments. However, it primarily delivers guidance rather than automated remediation. Database administrators must review these insights and manually implement suggested changes. Although Intelligent Insights is valuable for proactive performance monitoring, it lacks the automated enforcement mechanism required to correct query plan regressions as they occur, making it less suitable for scenarios requiring continuous performance optimization.

Extended Events is a lightweight, highly configurable framework for collecting diagnostic and monitoring data in SQL Server and Azure SQL Database. It allows detailed tracking of events such as query execution, deadlocks, or performance anomalies. However, Extended Events is strictly a data collection and analysis tool; it does not include mechanisms to remediate query performance issues automatically. Using Extended Events, administrators can capture rich telemetry for troubleshooting but must act on it manually.

The correct choice is Automatic Plan Correction because it uniquely combines detection of regressed query plans with the ability to automatically revert to known good plans. Query Store and Intelligent Insights provide monitoring and diagnostic capabilities, while Extended Events provides detailed event data. None of these three alternatives enforce automatic corrective actions. Therefore, for seamless, real-time remediation of performance regressions without manual intervention, Automatic Plan Correction is the feature that meets the requirement.

Question 107 

You want to offload read-only analytics queries to secondary replicas without affecting primary database writes. Which feature should you enable?

A) Read Scale-Out
B) Auto-Failover Groups
C) Elastic Pool
D) Hyperscale replicas

Answer:  A) Read Scale-Out

Explanation:

Read Scale-Out is a feature in Azure SQL Database Business Critical tier that allows read-only workloads to be redirected to one or more secondary replicas. These replicas are exact copies of the primary database but are optimized for query workloads. By offloading reporting, analytics, or other read-intensive operations to these replicas, the primary database remains unaffected, ensuring that write-heavy workloads maintain high performance and low latency. This separation of read and write workloads is essential for scenarios where heavy analytics queries might otherwise degrade transactional performance.

Auto-Failover Groups are primarily designed to provide high availability and disaster recovery. They enable automatic failover between primary and secondary databases in different regions, ensuring business continuity in the event of an outage. While Auto-Failover Groups can redirect connections during a failover, they are not intended for offloading read-only workloads on a continuous basis. They provide redundancy and failover capabilities but do not optimize the distribution of query workloads between replicas for reporting or analytics purposes.

Elastic Pools allow multiple databases to share a set of resources such as DTUs or vCores, providing flexibility and cost efficiency in resource allocation. However, Elastic Pools do not create secondary replicas or provide read-only endpoints for scaling out queries. While they help manage resources across multiple databases, they do not directly address the need to offload read-heavy analytics from a primary database.

Hyperscale replicas exist in the Hyperscale service tier, which supports very large databases with multiple read replicas. However, this option is specific to Hyperscale databases, whereas Read Scale-Out is available for the Business Critical tier. Hyperscale replicas involve different architecture and licensing considerations and are not relevant if the primary database is not using the Hyperscale tier.

The correct choice is Read Scale-Out because it explicitly allows read-only queries to be served from secondary replicas without impacting write performance on the primary database. Auto-Failover Groups, Elastic Pools, and Hyperscale replicas either serve different purposes or are constrained to specific architectures, making them unsuitable for the requirement of read workload offloading.

Question 108 

You want to encrypt sensitive database columns and allow computations without revealing plaintext to administrators. Which feature should you implement?

A) Always Encrypted
B) Transparent Data Encryption
C) Dynamic Data Masking
D) Row-Level Security

Answer:  A) Always Encrypted

Explanation:

Always Encrypted is a security feature that ensures sensitive data, such as personally identifiable information, remains encrypted both at rest and during query execution. Encryption and decryption occur entirely on the client side, meaning that the database engine and administrators never have access to plaintext data. This feature allows computations such as comparisons, equality checks, and aggregations to occur on encrypted columns without exposing the underlying values. It is specifically designed to protect sensitive information from high-privileged users in the database, meeting strict compliance requirements.

Transparent Data Encryption encrypts the data at rest on disk and in backups, providing protection against unauthorized access to physical storage. However, when queries are executed, the database engine decrypts the data automatically, which means administrators or anyone with sufficient database permissions could access plaintext data. While TDE secures storage, it does not protect sensitive data during runtime queries and therefore does not satisfy the requirement to prevent exposure to database administrators.

Dynamic Data Masking is a feature that obscures sensitive data in query results based on user permissions. It allows partial masking of data, such as displaying only the last four digits of a credit card number. However, masking occurs only at query presentation and does not encrypt the data. The underlying plaintext remains fully accessible to users with appropriate privileges. This means Dynamic Data Masking is a visual obfuscation method rather than a true encryption mechanism and does not prevent computations on encrypted data.

Row-Level Security restricts access to specific rows based on user attributes or roles. It allows granular control over which users can view which records but does not encrypt the underlying data. It is useful for implementing data access policies but does not provide confidentiality for sensitive columns or allow computations on encrypted data.

The correct choice is Always Encrypted because it uniquely enables encrypted computation without revealing plaintext to administrators. TDE, Dynamic Data Masking, and Row-Level Security offer protection or access control but do not fully secure sensitive data during query execution, making Always Encrypted the only feature that meets the specified requirements.

Question 109 

You need to maintain backup copies for several years to comply with regulatory retention policies. Which feature should you enable?

A) Long-Term Backup Retention
B) Geo-Redundant Backup Storage
C) Auto-Failover Groups
D) Transparent Data Encryption

Answer:  A) Long-Term Backup Retention

Explanation:

Long-Term Backup Retention (LTR) is designed for scenarios that require storing database backups for extended periods, often years, to comply with regulatory or corporate data retention policies. With LTR, you can schedule and maintain full backups for multiple years in Azure Storage, providing point-in-time restore capabilities for historical data. This ensures organizations meet legal and compliance obligations for long-term data retention while retaining the ability to recover databases from specific backup points.

Geo-Redundant Backup Storage replicates backups to a secondary Azure region to protect against regional outages and disasters. While geo-redundancy enhances availability and disaster recovery, it does not address regulatory retention policies directly, as the focus is on replication for resilience rather than long-term storage duration. Geo-redundancy complements LTR but cannot substitute for it in scenarios that require multi-year backup retention.

Auto-Failover Groups provide high availability and disaster recovery by automatically failing over databases to secondary replicas in another region. They ensure business continuity in the event of outages but do not provide capabilities for maintaining backups for extended periods. Auto-Failover Groups focus on uptime and failover scenarios, not compliance-driven backup retention.

Transparent Data Encryption secures the data at rest by encrypting the database files and backups. While TDE protects the data against unauthorized access, it does not define backup retention duration or storage policies. TDE ensures confidentiality but does not satisfy requirements for multi-year retention of backup copies.

The correct choice is Long-Term Backup Retention because it directly addresses the need to maintain backups for multiple years in compliance with regulatory policies. Geo-Redundant Backup Storage, Auto-Failover Groups, and Transparent Data Encryption provide resilience, high availability, and security but do not fulfill long-term retention requirements, making LTR the feature that meets the specific regulatory need.

Question 110 

You want to monitor anomalous access patterns in Azure SQL Database and receive alerts for potential threats. Which feature should you enable?

A) Threat Detection
B) Query Store
C) Automatic Plan Correction
D) SQL Auditing

Answer:  A) Threat Detection

Explanation:

Threat Detection is a proactive security feature in Azure SQL Database that monitors database activity for unusual patterns and potential threats. It can detect SQL injection attempts, anomalous logins, excessive failed login attempts, and suspicious data access activities. When a potential threat is detected, Threat Detection generates alerts and provides actionable recommendations, enabling administrators to respond quickly to mitigate risks. This real-time monitoring ensures that suspicious behaviors are identified promptly and potential security breaches can be addressed before they escalate.

Query Store captures query performance metrics, execution plans, and runtime statistics. While it is highly effective for diagnosing performance issues and understanding query execution history, it does not monitor security or detect anomalous access patterns. Query Store is focused on performance management rather than threat detection and cannot send alerts related to potential attacks or unauthorized access.

Automatic Plan Correction addresses query performance regressions by identifying inefficient execution plans and reverting them to known good plans. Its functionality is limited to performance optimization and has no role in monitoring security threats. While it ensures consistent query performance, it cannot detect or alert administrators to abnormal access patterns or suspicious activities within the database.

SQL Auditing provides a detailed record of database activities, such as logins, schema changes, and data access. Auditing is essential for compliance and forensic investigations but is primarily a logging mechanism. It does not actively monitor for anomalies in real time or automatically alert administrators to potential security incidents. Administrators must review audit logs manually to detect suspicious behavior.

The correct choice is Threat Detection because it actively monitors for anomalies and provides real-time alerts for potential security threats. Query Store, Automatic Plan Correction, and SQL Auditing either focus on performance or passive logging and do not provide the real-time anomaly detection and alerting capabilities that Threat Detection offers, making it the appropriate feature for proactive security monitoring.

Question 111 

You want to automatically detect and remediate query performance regressions in Azure SQL Database. Which feature should you enable?

A) Automatic Plan Correction
B) Query Store
C) Intelligent Insights
D) Extended Events

Answer:  A) Automatic Plan Correction

Explanation:

Automatic Plan Correction is designed specifically to identify queries whose execution plans have caused performance regressions and to automatically apply previously known good plans. This feature monitors execution statistics and plan behavior continuously, allowing the system to revert to stable plans when regressions occur, without requiring manual intervention. It is particularly useful in high-transaction or production environments where consistent performance is critical, as it ensures that queries continue to run efficiently and system throughput remains stable. The main advantage of Automatic Plan Correction is its ability to combine monitoring with immediate remediation, keeping the database optimized automatically.

Query Store is often mentioned in the context of performance monitoring, and it indeed provides valuable functionality by capturing historical query execution statistics and plans over time. Administrators can use this historical data to analyze performance trends and identify potential issues. However, Query Store by itself does not automatically correct regressions; it only provides insights and the ability to force a plan manually. While essential for diagnostic purposes, relying solely on Query Store requires human intervention to remediate performance regressions, making it less suitable for fully automated scenarios.

Intelligent Insights provides performance analysis and recommendations for SQL databases. It leverages machine learning to detect anomalies, inefficient queries, and other issues. While it can offer guidance on query tuning and optimization, the application of its recommendations is not automatic. Administrators must review insights and manually implement changes. Intelligent Insights is highly useful for proactive monitoring and decision-making but does not provide the real-time automated plan enforcement that Automatic Plan Correction delivers.

Extended Events is a diagnostic tool for capturing detailed telemetry about SQL Server activity. It allows fine-grained tracking of queries, performance counters, and other internal events, which is helpful for deep troubleshooting. However, Extended Events do not automatically remediate performance regressions or enforce stable query plans. They are primarily used to collect and analyze data post-incident or during performance tuning efforts.

The correct choice is Automatic Plan Correction because it directly addresses the need for automated detection and remediation of query plan regressions. While Query Store, Intelligent Insights, and Extended Events provide monitoring, insights, and diagnostics, only Automatic Plan Correction ensures that regressions are corrected automatically, maintaining stable and predictable database performance without manual effort.

Question 112 

You want to encrypt sensitive columns and allow client applications to perform queries without exposing plaintext to administrators. Which feature should you implement?

A) Always Encrypted
B) Transparent Data Encryption
C) Dynamic Data Masking
D) Row-Level Security

Answer:  A) Always Encrypted

Explanation: 

Always Encrypted is a client-side encryption technology that keeps sensitive column data encrypted both at rest and in transit. The database engine never sees plaintext values, and all encryption and decryption occur on the client side. This ensures that administrators or unauthorized users who have database access cannot read sensitive data, fulfilling strict security and compliance requirements. Additionally, Always Encrypted allows queries to operate on encrypted data in a limited way, such as equality comparisons, making it usable without compromising confidentiality.

Transparent Data Encryption encrypts the entire database at rest, protecting it from unauthorized access to storage media. However, when queries are executed, the data is decrypted by the server, meaning that database administrators or anyone with query access could see plaintext data. While TDE is effective for securing backups and physical storage, it does not prevent exposure during normal query execution, which makes it insufficient for scenarios requiring strict data confidentiality from administrators.

Dynamic Data Masking allows certain data to be obscured in query results based on user roles. It is helpful for reducing exposure of sensitive information to non-privileged users, but it does not encrypt the underlying data. Masked values are still stored in plaintext, and anyone with direct access to the database can retrieve the original values. DDM is more of an access control tool than a full encryption solution.

Row-Level Security controls access to specific rows in a table depending on the user’s role or attributes. It is a powerful mechanism for restricting data access within the database but does not encrypt data. Users who have access to allowed rows still see the data in plaintext, and administrators can still query the database. RLS addresses authorization rather than encryption or query-level confidentiality.

Always Encrypted is the correct solution because it combines both strong encryption and usability, enabling applications to query sensitive columns without exposing plaintext to SQL Server or administrators. The other options provide partial security or access control but do not meet the strict confidentiality requirement of preventing administrative exposure while allowing queries.

Question 113 

You need to offload read-only reporting queries from the primary Business Critical database without impacting write workloads. Which feature should you enable?

A) Read Scale-Out
B) Auto-Failover Groups
C) Elastic Pool
D) Transparent Network Redirect

Answer:  A) Read Scale-Out

Explanation:

Read Scale-Out is a feature available in the Business Critical tier of Azure SQL Database that allows read-only workloads to be directed to one of the secondary replicas. This effectively offloads heavy reporting and analytical queries from the primary database, ensuring that transactional write operations continue without performance degradation. By leveraging secondary replicas for read queries, Read Scale-Out improves overall system performance and allows high-concurrency workloads to coexist without contention on the primary instance.

Auto-Failover Groups provide high availability and disaster recovery by automatically failing over to a secondary database in the event of a primary outage. While this ensures continuity, it does not help offload read workloads during normal operations. Auto-Failover Groups focus on reliability rather than performance optimization for read queries.

Elastic Pool is a mechanism for sharing resources among multiple databases to optimize cost and performance. It allows dynamic allocation of CPU and storage across a pool of databases but does not provide secondary replicas or enable read-only offloading. Elastic Pool is primarily for managing multiple databases under shared resource limits, not for scaling out read operations.

Transparent Network Redirect is a client-side connection mechanism used after failover events to automatically redirect traffic to the new primary. While it simplifies connection management in failover scenarios, it does not provide offloading of read workloads or improve query performance for reporting.

Read Scale-Out is the correct choice because it directly allows read-only queries to be executed on secondary replicas, improving overall system throughput without impacting transactional operations. The other options are either focused on availability, resource sharing, or connection management and do not provide the read workload offloading required in this scenario.

Question 114 

You want to store audit logs securely and durably in Azure for compliance with regulatory requirements. Which destination should you select?

A) Azure Storage account
B) Log Analytics workspace
C) Event Hubs
D) Power BI

Answer:  A) Azure Storage account

Explanation:

Azure Storage accounts provide secure, durable, and cost-effective storage for audit logs. They allow organizations to implement retention policies that comply with regulatory requirements, ensuring long-term retention and protection against unauthorized access. Storage accounts support encryption at rest, redundancy options such as geo-replication, and lifecycle management features, making them an ideal destination for compliance-focused log storage.

Log Analytics workspace is designed for querying and analyzing log data to monitor system health, detect anomalies, and visualize metrics. While it provides rich analytical capabilities, it is not optimized for long-term retention of audit logs or regulatory compliance. Logs stored here may be retained for a limited time, depending on configuration, and may not meet certain audit standards for secure archival.

Event Hubs is a high-throughput streaming platform used to ingest large volumes of event data into various downstream systems. While it facilitates real-time event processing, it is not intended for secure, durable storage of audit logs. Event Hubs is more suitable for data ingestion and streaming pipelines than for compliance archival.

Power BI is a visualization tool for reporting and business intelligence. It does not provide secure or durable storage for raw logs and cannot enforce retention policies or encryption for compliance purposes. Its primary function is to generate insights rather than store original audit data.

Azure Storage accounts are the correct choice because they offer secure, durable, and compliant storage suitable for long-term retention of audit logs. The other options are either analytical, streaming, or visualization tools that do not satisfy compliance and retention requirements for audit data.

Question 115 

You want to monitor query performance trends and preserve historical execution plans to identify regressions. Which feature should you enable?

A) Query Store
B) Extended Events
C) SQL Auditing
D) Intelligent Insights

Answer:  A) Query Store

Explanation:

Query Store captures and retains query execution statistics, execution plans, and runtime metrics over time, providing a comprehensive historical view of database performance. It allows administrators to analyze trends, identify slow-running queries, and detect execution plan regressions. Query Store also supports forcing specific plans to mitigate performance issues, making it an essential tool for proactive performance management.

Extended Events provides detailed diagnostic information by capturing specific SQL Server events. While it is highly flexible and useful for troubleshooting complex performance problems, it does not automatically maintain historical execution plans or provide an easy way to analyze trends over time. Extended Events requires manual analysis and is better suited for ad hoc diagnostics.

SQL Auditing records database access and modification activities for compliance and security purposes. It does not track query execution plans or performance metrics. While auditing is critical for monitoring user activity and ensuring accountability, it does not provide insights into query performance trends or regressions.

Intelligent Insights analyzes performance issues and provides recommendations for optimization. It can detect anomalous queries and suggest corrective actions, but it does not store historical execution plans. Administrators must manually act on its recommendations, making it less effective for ongoing trend analysis compared to Query Store.

Query Store is the correct solution because it combines performance trend monitoring with historical execution plan retention. It enables administrators to detect regressions, analyze query behavior over time, and take corrective actions proactively, ensuring continuous performance optimization.

Question 116 

You need to enforce row-level access restrictions for a table based on user department for compliance purposes. Which feature should you enable?

A) Row-Level Security
B) Dynamic Data Masking
C) Always Encrypted
D) Transparent Data Encryption

Answer:  A) Row-Level Security

Explanation:

Row-Level Security is a feature specifically designed to enforce fine-grained access control at the row level. It works by applying security predicates to database tables, dynamically filtering rows based on the attributes of the user querying the data. For instance, if access should be limited by department, Row-Level Security can use a predicate function to ensure that users only see rows corresponding to their own department. This approach allows the same table to contain all data while preventing unauthorized access, which is critical for compliance and regulatory scenarios.

Dynamic Data Masking, in contrast, does not restrict access to rows but only masks sensitive column values for certain users. It is primarily intended to reduce the risk of accidental exposure of sensitive information in query results without modifying the underlying data. While this improves data privacy for selected columns, it does not prevent users from accessing entire rows that they should not see. Therefore, it cannot meet compliance requirements that involve restricting access based on row-level conditions.

Always Encrypted provides encryption for sensitive data at the client level, ensuring that plaintext values are never revealed to the database engine. While this secures data and prevents unauthorized users from seeing sensitive values, it does not provide row-level access control or filtering. Users who have access to the table can still see metadata and other rows that they might not be authorized to access. This makes Always Encrypted insufficient for scenarios where visibility must be limited by attributes such as department.

Transparent Data Encryption secures the database at rest by encrypting the physical files on disk. It protects against threats such as unauthorized access to storage media but does not implement any form of access control within the database itself. All users with database access can still query and view all rows, making it unrelated to the requirement for restricting row visibility.

Question 117 

You want to detect anomalous access patterns and receive alerts for potential database security threats. Which feature should you enable?

A) Threat Detection
B) Query Store
C) Automatic Plan Correction
D) SQL Auditing

Answer:  A) Threat Detection

Explanation:

Threat Detection is designed to proactively monitor database activity and identify unusual patterns that could indicate security threats. It can detect potential SQL injection attempts, unusual login behavior, access from unexpected geographic locations, and other anomalous activities. When such events are detected, it generates alerts for administrators, enabling timely investigation and mitigation of potential security incidents. This makes it ideal for environments where real-time awareness of security threats is critical.

Query Store, while extremely useful for monitoring query performance and analyzing execution plans over time, focuses solely on performance metrics. It records query execution history and plan changes but does not detect or alert administrators about security anomalies. Therefore, it cannot serve as a solution for identifying suspicious access or potential attacks.

Automatic Plan Correction is a feature for improving query performance by detecting and automatically fixing plan regressions. Its scope is limited to performance optimization and it does not interact with security-related events or monitor for anomalous user behavior. Using it for security purposes would be ineffective.

SQL Auditing records database events and activities, providing a detailed history of who accessed what data and when. While it is essential for compliance and forensic analysis, it does not automatically analyze patterns or generate alerts in real time for suspicious activity. Administrators would need to manually review logs, which could delay response to potential threats.

Considering these options, Threat Detection is the only feature that actively monitors, identifies, and alerts administrators about potential security incidents in real time. It is tailored for detecting anomalous access patterns and providing immediate visibility into security risks, making it the correct choice for the scenario described.

Question 118 

You need to maintain database backups for multiple years to satisfy regulatory retention requirements. Which feature should you enable?

A) Long-Term Backup Retention
B) Geo-Redundant Backup Storage
C) Auto-Failover Groups
D) Transparent Data Encryption

Answer:  A) Long-Term Backup Retention

Explanation:

Long-Term Backup Retention allows organizations to store backups of their databases in Azure Storage for extended periods, often spanning several years. This capability is crucial for meeting regulatory and compliance requirements that mandate data retention for defined durations. It supports both point-in-time recovery and compliance-driven archival storage, enabling organizations to retrieve historical backups even years after their creation.

Geo-Redundant Backup Storage focuses on high availability and disaster recovery. It replicates backups across different geographic regions to protect against regional failures. While this ensures resiliency and data protection, it does not inherently provide multi-year retention. Backups might still be automatically deleted after the default retention period, failing to meet long-term regulatory requirements.

Auto-Failover Groups are intended to provide high availability and business continuity for databases. They replicate data between primary and secondary databases across regions, ensuring minimal downtime in case of failures. This feature does not control backup retention or support multi-year archival storage; its focus is on operational continuity rather than regulatory retention.

Transparent Data Encryption encrypts the database at rest, ensuring that physical files on disk are secure. While it enhances data security and protects against unauthorized access to storage media, it does not influence backup scheduling or retention periods. Without a separate retention policy, encrypted backups may still be deleted before the regulatory retention period is met.

Question 119 

You want to reduce compute costs for a database that is idle most of the day while ensuring automatic scaling during workload peaks. Which deployment model should you select?

A) Serverless compute tier
B) Hyperscale tier
C) Business Critical tier
D) Elastic Pool

Answer:  A) Serverless compute tier

Explanation:

Serverless compute tier is designed for workloads with intermittent usage patterns. It automatically scales compute resources up or down depending on current demand and can pause the database when it is idle. Pausing reduces compute costs significantly because billing stops while the database is inactive. Once activity resumes, the serverless database automatically resumes operations and scales resources based on workload, providing cost efficiency without manual intervention.

Hyperscale tier is ideal for very large databases requiring rapid storage expansion and fast read/write scaling. While it provides independent compute and storage scaling, it does not pause idle databases, meaning cost savings are limited when workloads are inactive. Its main advantage is scale rather than cost reduction for low-utilization workloads.

The Business Critical tier offers consistent, high-performance compute and low-latency storage. It is designed for mission-critical workloads with heavy transactional demands. While it provides excellent performance, it does not pause during idle periods and does not offer automatic scaling, so it is not suitable for cost savings in low-usage scenarios.

Elastic Pool allows multiple databases to share a pool of resources. This helps smooth resource usage across databases but does not automatically pause or scale individual databases. Idle databases still consume a portion of the shared pool, so cost reduction is limited compared to serverless compute.

Overall, Serverless compute tier is the only option that provides automatic scaling and the ability to pause idle databases, effectively reducing costs while maintaining performance during peak workloads. Its design matches the requirement for intermittent database usage, making it the correct choice.

Question 120 

You want to offload reporting queries from a primary Business Critical Azure SQL Database without impacting write operations. Which feature should you enable?

A) Read Scale-Out
B) Auto-Failover Groups
C) Elastic Pool
D) Transparent Network Redirect

Answer:  A) Read Scale-Out

Explanation:

Read Scale-Out is a feature in the Business Critical tier that allows read-only queries to be directed to secondary replicas. By offloading reporting and analytical workloads to these replicas, the primary database is freed from read-intensive operations, ensuring that write performance remains unaffected. This is particularly useful in scenarios where reporting queries are complex or frequent and could degrade the performance of transactional operations on the primary database.

Auto-Failover Groups provide high availability and disaster recovery. They allow automatic failover to a secondary database in case of regional outages. While they replicate data for resilience, they do not redirect or offload read-only workloads under normal operations. Their focus is reliability, not query load balancing.

Elastic Pool allows multiple databases to share resources, which can help balance resource usage across databases. However, it does not create secondary replicas for read workloads. Reporting queries executed on the primary database would still consume primary resources, so it does not meet the requirement to offload reporting queries.

Transparent Network Redirect ensures that clients are seamlessly redirected to the appropriate database after failover events. This improves application continuity but does not provide additional compute capacity for read queries or offload the primary database. It is strictly a connectivity feature rather than a performance optimization tool for read workloads.

Considering these options, Read Scale-Out is the only feature specifically designed to route read-only queries to secondary replicas, improving reporting performance without affecting primary database writes. This makes it the correct solution for offloading reporting workloads in a Business Critical Azure SQL Database.