Knowledge + Practice

Microsoft Azure Database Administrator Associate DP-300 (DP-300) — Questions 526–600

953 questions total · 13pages · All types, answers revealed

Take a mock exam Exam hub

Page 8 of 13

526

Multi-Selecthard

You are optimizing an Azure SQL Database that uses the Business Critical tier. Which THREE factors affect the maximum log rate?

Select 3 answers

A.Service level objective (SLO)

B.Number of vCores

C.Backup retention period

D.Number of log files

E.Page compression level

AnswersA, B, D

Defines log rate limit.

Why this answer

Options B, C, and E are correct. The service level objective (SLO) defines the log rate limit. The number of data files affects log throughput because log records are written to log file, not data files, but the log rate limit is also influenced by data file count in some tiers? Actually, in Business Critical, the log rate is determined by the SLO and the number of log files? Wait, the correct factors: SLO (B), number of vCores (C) is part of SLO, and the number of log files (E) can affect log throughput.

Option A is wrong because page compression does not directly affect log rate limit. Option D is wrong because backup retention does not affect log rate.

Full explanation →

527

MCQhard

Your company uses Azure SQL Managed Instance with transactional replication. You need to automate the monitoring of replication latency and send an alert if latency exceeds 5 minutes. You want to use Azure Monitoring capabilities. What is the most efficient solution?

A.Use Elastic Database Jobs to run a query on the distributor and log results to a table, then set up a logic app to check the table.

B.Monitor the 'Replication: Dist Delivery Latency' performance counter.

C.Create a SQL Agent job on the publisher that checks sys.dm_repl_sync_status and sends an email.

D.Create an Azure Monitor metric alert on the 'Log Send Queue Size' metric with a threshold of 300000 KB (approximately 5 minutes).

AnswerD

This metric directly reflects replication latency and can trigger alerts.

Why this answer

Option A is correct because Azure SQL Managed Instance exposes replication metrics like Log Send Queue Size and Transaction Delay in Azure Monitor, which can be used to create alerts. Options B and C require custom scripting and are less efficient. Option D uses a deprecated metric.

Full explanation →

528

MCQmedium

You are monitoring an Azure SQL Database using the Automatic Tuning feature. The database has a workload that is read-intensive. You enable the CREATE INDEX and DROP INDEX options. After a week, you observe that the database has created several new indexes automatically. However, you notice that one of the new indexes is causing increased write latency for an application that performs frequent updates. What should you do to resolve the issue without losing the benefits of automatic tuning for other indexes?

A.Use the Azure portal to revert all automatic tuning recommendations for the past week.

B.Manually create the missing indexes that were dropped by automatic tuning.

C.Disable automatic tuning for the entire database.

D.Manually drop the problematic index using a DROP INDEX command.

AnswerD

You can manually revert a specific index while leaving automatic tuning active for other indexes.

Why this answer

Option D is correct because manually dropping the problematic index allows you to resolve the specific performance issue caused by increased write latency while retaining the benefits of automatic tuning for other indexes. The Automatic Tuning feature in Azure SQL Database can create indexes to improve read performance, but these indexes may introduce overhead on write operations. By issuing a DROP INDEX command, you surgically remove only the offending index without disabling the overall tuning mechanism.

Exam trap

The trap here is that candidates may think disabling automatic tuning entirely or reverting all recommendations is necessary, but the correct approach is to manually drop only the problematic index to preserve the benefits of automatic tuning for other indexes.

How to eliminate wrong answers

Option A is wrong because reverting all automatic tuning recommendations for the past week would undo all index changes, including beneficial ones, and does not target the specific problematic index. Option B is wrong because manually creating missing indexes that were dropped by automatic tuning is irrelevant; the issue is a newly created index causing write latency, not missing indexes. Option C is wrong because disabling automatic tuning for the entire database would stop all future tuning recommendations and lose the benefits of automatic index management for other queries, which is an overreaction to a single problematic index.

Full explanation →

529

MCQmedium

You are optimizing an Azure SQL Database that has a heavy workload of both reads and writes. The database has a clustered columnstore index on a large fact table. You notice that the index has high fragmentation and the performance of queries against this table is degrading. What should you do to improve performance?

A.Rebuild the clustered columnstore index

B.Increase the service tier to get more IOPS

C.Drop and recreate the clustered columnstore index

D.Reorganize the clustered columnstore index

AnswerA

Rebuild reduces fragmentation and improves scan performance.

Why this answer

Option C is correct because rebuilding the columnstore index reduces fragmentation and improves performance. Option A is wrong because reorganizing a columnstore index is not supported; only rebuild is. Option B is wrong because dropping and recreating is more disruptive.

Option D is wrong because increasing service tier addresses symptoms not fragmentation.

Full explanation →

530

Multi-Selecthard

You are troubleshooting a transaction log growth issue on an Azure SQL Database. Which THREE conditions can cause the transaction log to grow unexpectedly?

Select 3 answers

A.Replication that has not delivered transactions to the subscriber

B.A memory-optimized table with a large number of rows

C.A long-running transaction that has not been committed

D.A missing index on a large table that causes excessive updates

E.Page compression enabled on tables

AnswersA, C, D

Log space held for replication.

Why this answer

Options A, B, and D are correct. A long-running transaction prevents log truncation. Replication with transactions waiting to be delivered can hold log space.

A missing index can cause large updates that generate many log records. Option C is wrong because a memory-optimized table uses a different log mechanism but does not directly cause log growth more than disk-based tables. Option E is wrong because page compression reduces log size if anything.

Full explanation →

531

MCQeasy

You need to automatically send an email notification when an Azure SQL Database reaches 80% storage usage. What should you configure?

A.Azure Monitor alert with action group

B.Change Data Capture (CDC) with Logic Apps

C.Elastic Database Job with sp_send_dbmail

D.SQL Agent Mail

AnswerA

Azure Monitor can alert on storage metrics and trigger email notifications.

Why this answer

Option C is correct because Azure Monitor alerts can trigger actions like email based on metrics such as storage percent. Option A is wrong because SQL Agent Mail is not available in Azure SQL Database. Option B is wrong because Elastic Database Jobs are for executing scripts.

Option D is wrong because it is for data-level changes, not monitoring.

Full explanation →

532

MCQmedium

You are deploying Azure SQL Database for a critical application. The compliance policy requires that the database be configured for zone redundancy in the primary region and have automated failover to a secondary region with no data loss. What Azure SQL Database offering and feature should you choose?

A.Basic tier with geo-replication

B.Business Critical tier with zone redundancy and active geo-replication

C.General Purpose tier with geo-replication

D.Hyperscale tier with zone redundancy

AnswerB

Business Critical provides zone redundancy and active geo-replication with zero data loss.

Why this answer

The Business Critical tier is the only Azure SQL Database tier that supports both zone redundancy within a region and active geo-replication with synchronous data replication to a secondary region. Zone redundancy ensures high availability within the primary region by distributing replicas across availability zones, while active geo-replication provides automated failover to a secondary region with no data loss because it uses synchronous commit at the primary and asynchronous replication to the secondary, but with the ability to fail over without data loss if the secondary is fully synchronized. This combination meets the compliance requirement for zero data loss during a regional failover.

Exam trap

The trap here is that candidates often confuse the General Purpose tier's geo-replication (which is asynchronous and can lose data) with the Business Critical tier's active geo-replication (which supports synchronous replication for zero data loss), and they overlook that zone redundancy is only available in Business Critical and Hyperscale tiers, but Hyperscale lacks automated failover geo-replication.

How to eliminate wrong answers

Option A is wrong because the Basic tier does not support zone redundancy or geo-replication; it is designed for development and testing with no high availability or disaster recovery capabilities. Option C is wrong because the General Purpose tier uses asynchronous data replication and does not support zone redundancy; it can have geo-replication but cannot guarantee zero data loss during failover due to asynchronous replication. Option D is wrong because the Hyperscale tier supports zone redundancy but does not support active geo-replication for automated failover to a secondary region; its geo-replication is limited to named replicas with manual failover, not automated failover with no data loss.

Full explanation →

533

MCQmedium

Refer to the exhibit. You have an Azure SQL Managed Instance configured with the failover group properties shown. The primary region experiences an outage. After 30 minutes, the failover group has not failed over. You need to force failover immediately. What should you do?

A.Change the failover policy to Manual and then fail over.

B.Set the secondaryType to Standby.

C.Use the Switch-AzSqlDatabaseFailoverGroup cmdlet with -AllowDataLoss.

D.Wait for the grace period to expire.

AnswerC

Even though automatic failover is waiting for the grace period, you can force failover with data loss.

Why this answer

Option A is correct because the grace period is 60 minutes; after 30 minutes, you can force failover with data loss by using -AllowDataLoss. Option B is wrong because the grace period has not expired. Option C is wrong because the failover policy is already Automatic.

Option D is wrong because the secondary type is Geo, which is correct.

Full explanation →

534

MCQhard

Your company has an Azure SQL Managed Instance that hosts multiple databases. You need to implement a solution to automatically detect and alert on potential SQL injection attacks. The solution must integrate with Microsoft Sentinel for incident response. What should you configure?

A.Enable Microsoft Purview Data Map for the Managed Instance

B.Configure Microsoft Defender XDR for SQL

C.Use Microsoft Intune to manage SQL security policies

D.Enable Microsoft Defender for SQL on the Managed Instance

AnswerD

Defender for SQL provides SQL injection alerts and integrates with Sentinel.

Why this answer

Microsoft Defender for SQL on Azure SQL Managed Instance provides built-in SQL injection detection and alerting, which can be integrated directly with Microsoft Sentinel for automated incident response and investigation. This is the correct solution because it offers native vulnerability assessment and threat detection tailored to SQL databases, meeting the requirement for both detection and Sentinel integration.

Exam trap

The trap here is that candidates may confuse Microsoft Defender XDR (a broader security suite) with Microsoft Defender for SQL (the specific service for SQL threat detection), leading them to select Option B instead of the correct D.

How to eliminate wrong answers

Option A is wrong because Microsoft Purview Data Map is a data governance and cataloging service, not a security detection or alerting tool for SQL injection. Option B is wrong because Microsoft Defender XDR (Extended Detection and Response) is a unified security platform for endpoints, identities, and cloud apps, but it does not directly provide SQL injection detection for Azure SQL Managed Instance; that capability is part of Defender for SQL. Option C is wrong because Microsoft Intune is a mobile device management (MDM) and mobile application management (MAM) service, not a tool for configuring SQL security policies or detecting SQL injection attacks.

Full explanation →

535

MCQhard

Your company is migrating a mission-critical application from SQL Server 2019 on-premises to Azure SQL Managed Instance. The database is 1.5 TB and has 50 databases that need to be migrated. The application uses SQL Server Agent jobs, Service Broker, and cross-database queries. The security requirements mandate that the migration must use Microsoft Entra ID authentication, and all data must be encrypted at rest using customer-managed keys in Azure Key Vault. The migration window is limited to 4 hours of downtime. You need to choose the migration method and configure the target environment. What should you do?

A.Use Azure Database Migration Service offline migration with automatic cutover.

B.Deploy a new Managed Instance, configure TDE with customer-managed keys, and use transactional replication to synchronize data.

C.Use Azure Database Migration Service online migration, configure Managed Instance with customer-managed TDE, and enable Entra ID authentication.

D.Use Azure backup to URL and restore to Managed Instance, then configure TDE with customer-managed keys.

AnswerC

Online migration minimizes downtime, and Managed Instance supports all required features.

Why this answer

Option C is correct because Azure Database Migration Service (DMS) online migration supports minimal downtime for large databases (1.5 TB) and 50 databases, while maintaining transactional consistency for cross-database queries and Service Broker. Configuring the Managed Instance with customer-managed TDE (Transparent Data Encryption) using Azure Key Vault meets the encryption-at-rest requirement, and enabling Microsoft Entra ID authentication satisfies the security mandate. The online migration mode allows continuous synchronization during the migration window, with a final cutover within the 4-hour downtime limit.

Exam trap

The trap here is that candidates often assume offline migration (Option A or D) is acceptable for large databases, but the 4-hour downtime window makes online migration (Option C) the only viable choice, and they may overlook that transactional replication (Option B) cannot handle cross-database dependencies and Service Broker messages during migration.

How to eliminate wrong answers

Option A is wrong because offline migration with automatic cutover requires the source database to be taken offline for the entire duration of data copy, which would exceed the 4-hour downtime window for a 1.5 TB database. Option B is wrong because transactional replication does not natively support Service Broker or cross-database queries as a migration method; it is designed for ongoing replication of specific tables, not full database migration with schema and dependencies. Option D is wrong because Azure backup to URL and restore to Managed Instance is an offline method that would require the database to be offline for the entire backup and restore process, exceeding the 4-hour window, and it does not inherently configure TDE with customer-managed keys or Entra ID authentication.

Full explanation →

536

MCQhard

You are configuring a private endpoint for an Azure SQL Database. The exhibit shows the current network ACLs. You need to ensure that only traffic from a specific subnet in VNet1 is allowed, and all other traffic is denied. What should you do?

A.No changes needed; the configuration already meets the requirement.

B.Add an IP rule to allow the subnet's IP range.

C.Set ignoreMissingVnetServiceEndpoint to true.

D.Change defaultAction to Allow.

AnswerA

Default deny with a VNet rule for the subnet allows only that subnet.

Why this answer

Option A is correct because the exhibit already has defaultAction: Deny and a virtual network rule for the subnet, which allows traffic from that subnet. Option B is wrong because adding an IP rule would allow traffic from the IP, which is not desired. Option C is wrong because setting ignoreMissingVnetServiceEndpoint to true would allow the rule even if the endpoint is missing, which could cause unintended access.

Option D is wrong because changing defaultAction to Allow would allow all traffic.

Full explanation →

537

MCQmedium

You manage an Azure SQL Database that is part of a business-critical application. You need to ensure that network traffic between the application hosted on Azure VMs and the database is encrypted and does not traverse the public internet. What should you configure?

A.Use TLS 1.2 for all connections to the database.

B.Configure server-level firewall rules to allow only the application VM IP addresses.

C.Enable forced tunneling on the application VMs to route all traffic through the on-premises network.

D.Create a private endpoint for Azure SQL Database in the same virtual network as the application VMs.

AnswerD

Private endpoints provide a private IP address within the VNet, keeping traffic off the public internet.

Why this answer

Option D is correct because creating a private endpoint for Azure SQL Database places the database service on a private IP address within the same virtual network as the application VMs. This ensures that all traffic between the VMs and the database stays entirely within the Microsoft Azure backbone network, never traversing the public internet, while also providing encryption in transit via TLS by default.

Exam trap

The trap here is that candidates often confuse encryption (TLS) with network isolation, assuming that encrypting traffic alone prevents it from traversing the public internet, or they mistakenly believe that IP-based firewall rules create a private network path.

How to eliminate wrong answers

Option A is wrong because using TLS 1.2 only encrypts the connection but does not prevent traffic from traversing the public internet; the database endpoint remains publicly accessible. Option B is wrong because server-level firewall rules restrict access by IP address but still allow traffic over the public internet; they do not provide a private network path. Option C is wrong because forced tunneling routes all VM traffic through an on-premises network, which adds latency and does not keep traffic within Azure; it also does not create a private connection to Azure SQL Database.

Full explanation →

538

Multi-Selecthard

Which THREE features can help protect Azure SQL Database from data exfiltration?

Select 2 answers

A.Microsoft Defender for SQL

B.Always Encrypted

C.Azure SQL Database Ledger

D.Dynamic Data Masking

E.Azure Policy with deny effect for public network access

AnswersA, E

Defender for SQL can alert on anomalous activities that may indicate exfiltration.

Why this answer

Data exfiltration prevention focuses on preventing unauthorized data extraction. Defender for SQL alerts on suspicious activities, Ledger provides tamper-evidence but not prevention, and Azure Policy can enforce rules like blocking public access. Always Encrypted protects data but does not prevent exfiltration by authorized users.

Dynamic Data Masking obfuscates data but does not prevent copying.

Full explanation →

539

MCQhard

Your company has an Azure SQL Managed Instance in the General Purpose tier. You need to configure a failover group for disaster recovery. The secondary managed instance must be in a different region and must also be used for read-only workloads. During a failover, you want to minimize data loss. Which configuration should you use?

A.Enable zone redundancy on the primary and secondary instances.

B.Create a failover group and configure the secondary instance to allow read-only connections.

C.Configure log shipping between the primary and secondary instances.

D.Use active geo-replication instead of a failover group.

AnswerB

Failover groups support readable secondary and minimize data loss.

Why this answer

Option C is correct because failover groups support readable secondary by default, and you can configure the secondary to be used for read-only queries by setting the connection string with ApplicationIntent=ReadOnly. Option A (zone redundancy) is not for geo-DR. Option B (active geo-replication) is not supported for managed instances.

Option D (log shipping) is not built-in.

Full explanation →

540

MCQmedium

You are monitoring an Azure SQL Database and notice a pattern of high CPU usage during business hours. You need to identify the queries consuming the most CPU over the last 24 hours. Which dynamic management view should you query?

A.sys.dm_exec_requests

B.sys.dm_exec_sessions

C.sys.dm_exec_query_stats

D.sys.dm_os_performance_counters

AnswerC

Provides aggregated CPU time for queries over time.

Why this answer

sys.dm_exec_query_stats (Option C) is the correct DMV because it returns aggregate performance statistics for cached query plans, including total CPU time (total_worker_time), execution count, and last execution time. By querying this view and ordering by total_worker_time descending, you can identify the queries that have consumed the most CPU over the last 24 hours, directly addressing the pattern of high CPU usage during business hours.

Exam trap

The trap here is that candidates confuse sys.dm_exec_requests (current activity) with sys.dm_exec_query_stats (historical aggregated stats), leading them to choose Option A because they think 'requests' implies all recent queries, but it only shows currently running queries.

How to eliminate wrong answers

Option A is wrong because sys.dm_exec_requests shows currently executing requests at the moment of querying, not historical CPU consumption over the last 24 hours. Option B is wrong because sys.dm_exec_sessions provides session-level information such as login time and host details, but does not contain per-query CPU usage statistics. Option D is wrong because sys.dm_os_performance_counters returns operating system performance counter values (e.g., CPU utilization percentage), not the specific queries responsible for high CPU usage.

Full explanation →

541

Multi-Selecthard

Which TWO of the following are required steps to configure Azure SQL Database to use a customer-managed key (CMK) for Transparent Data Encryption (TDE) with Azure Key Vault? (Choose two.)

Select 2 answers

A.Create a user-assigned managed identity for the SQL Database server

B.Set the TDE protector to the Key Vault key via the Azure portal or T-SQL

C.Grant the SQL Database server's managed identity Get, Wrap Key, and Unwrap Key permissions on the Key Vault key

D.Store the encryption key in the SQL Database server's hardware security module (HSM)

E.Ensure the SQL Database server is inside the same virtual network as the Key Vault

AnswersB, C

This configures the server to use the CMK.

Why this answer

Option B is correct because setting the TDE protector to the Key Vault key is the step that actually enables customer-managed key (CMK) encryption for Azure SQL Database. This can be done via the Azure portal, PowerShell, Azure CLI, or T-SQL (using ALTER DATABASE SCOPED CONFIGURATION SET TDE_PROTECTOR). Without this step, the database continues to use a service-managed key.

Exam trap

The trap here is that candidates often confuse the required managed identity type (system-assigned vs. user-assigned) or assume network-level constraints (like VNet integration) are mandatory, when in fact only identity and key permissions are strictly required.

Full explanation →

542

MCQeasy

You are monitoring a critical production Azure SQL Database that is experiencing intermittent query timeouts. The database is configured with the General Purpose service tier. You need to identify the root cause of the timeouts with minimal overhead. What should you review first?

A.Implement automatic tuning to force plan regression fixes.

B.Increase the database service tier to Business Critical.

C.Use sys.dm_exec_query_stats to identify queries with high wait statistics.

D.Enable Query Store and review the Regressed Queries report.

AnswerC

Low-overhead way to find problematic queries and their wait types.

Why this answer

Option A is correct because sys.dm_exec_query_stats provides aggregated query performance data without adding significant overhead, and high wait stats often point to resource contention. Option B is wrong because Query Store insights require Query Store to be enabled and may not be the first step. Option C is wrong because automatic tuning recommendations are for optimization, not troubleshooting.

Option D is wrong because scaling up is a reactive measure, not a diagnostic step.

Full explanation →

543

MCQeasy

You are planning to deploy Azure SQL Database for a new application. The application requires a predictable performance with reserved resources and the ability to scale up during peak season. You want to minimize costs. Which purchasing model should you choose?

A.vCore-based purchasing model with reserved capacity

B.DTU-based purchasing model

C.Hyperscale service tier

D.Serverless compute tier

AnswerA

vCore model allows manual scaling and reserved instances reduce costs.

Why this answer

The vCore-based purchasing model with reserved capacity is correct because it allows you to reserve compute resources for a one- or three-year term, providing a significant discount (up to 40-60%) compared to pay-as-you-go pricing. This model also supports predictable performance with dedicated resources and the ability to scale up (e.g., increase vCores or storage) during peak seasons, while the reserved capacity commitment minimizes costs for the baseline workload.

Exam trap

The trap here is that candidates often confuse the DTU model as the default cost-saving option, but the vCore model with reserved capacity actually provides deeper discounts for predictable workloads, while the DTU model lacks such reservation flexibility.

How to eliminate wrong answers

Option B is wrong because the DTU-based purchasing model bundles compute, storage, and I/O into fixed tiers, offering less granular control over resources and no reserved capacity option, making it less cost-effective for predictable workloads that need to scale. Option C is wrong because the Hyperscale service tier is designed for large databases with high storage and rapid scaling needs, but it uses a pay-as-you-go model without reserved capacity discounts, leading to higher costs for predictable, moderate workloads. Option D is wrong because the Serverless compute tier auto-pauses and scales based on demand, which is ideal for intermittent workloads but not for predictable performance with reserved resources, and it does not offer reserved capacity pricing.

Full explanation →

544

Drag & Dropmedium

Drag and drop the steps to configure geo-replication for an Azure SQL Database in the correct order.

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

Steps

Order

Why this order

Geo-replication requires a secondary server in another region, then configuring the secondary database, initiating replication, and verifying the link. Failover is a separate step.

Full explanation →

545

Multi-Selecthard

You have an Azure SQL Database that stores personally identifiable information (PII). You need to classify and label the sensitive columns using Microsoft Purview. Additionally, you want to automatically mask these columns for a specific application user. Which two actions should you take? (Choose two.)

Select 2 answers

A.Apply Dynamic Data Masking (DDM) rules to mask the classified columns for the application user.

B.Enable Always Encrypted on the classified columns.

C.Configure Microsoft Sentinel to monitor access to classified columns.

D.Use Microsoft Purview Data Map to scan and classify the database.

E.Enable Transparent Data Encryption (TDE) on the database.

AnswersA, D

DDM masks data for unauthorized users.

Why this answer

Option A is correct because Dynamic Data Masking (DDM) can be applied to classified columns to obfuscate sensitive data for a specific application user without altering the underlying data. DDM rules are defined at the column level and can be configured to mask data for designated users while allowing full access to others, meeting the requirement to automatically mask PII for the application user.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking with Always Encrypted or TDE, thinking any encryption feature can mask data for specific users, but DDM is the only option that provides user-specific obfuscation without altering the underlying data or requiring application changes.

Full explanation →

546

MCQmedium

You have an Azure SQL Database that stores sensitive data. You need to automatically classify and apply sensitivity labels to new columns as they are added. What should you use?

A.Microsoft Purview Information Protection

B.Azure Policy with custom policy definition

C.Dynamic Data Masking

D.Azure Automation with PowerShell script to run sp_addsensitivityclassification

AnswerA

Purview can automatically scan and classify sensitive data.

Why this answer

Option C is correct because Microsoft Purview Information Protection can automatically classify and label sensitive data in Azure SQL Database. Option A is wrong because Azure Policy can enforce policies but not auto-classify. Option B is wrong because Dynamic Data Masking masks data but does not classify.

Option D is wrong because Azure Automation is not designed for classification.

Full explanation →

547

MCQmedium

You are implementing row-level security (RLS) in Azure SQL Database to enforce data isolation for multiple tenants. Each tenant has a TenantID. The RLS predicate function uses the SESSION_CONTEXT variable set by the application. You need to ensure that users cannot bypass the RLS by setting arbitrary SESSION_CONTEXT values. What should you do?

A.Create a stored procedure that sets the SESSION_CONTEXT and grant the application only execute permission on that procedure; revoke SET permission from users

B.Use dynamic data masking to hide the TenantID column

C.Encrypt the SESSION_CONTEXT values using Always Encrypted

D.Use a firewall rule to restrict access to the database to only the application IP addresses

AnswerA

This ensures that only the stored procedure can set the SESSION_CONTEXT, preventing users from setting arbitrary values.

Why this answer

Option A is correct because by creating a stored procedure that sets the SESSION_CONTEXT and granting the application only EXECUTE permission on that procedure while revoking SET permission from users, you prevent users from arbitrarily setting SESSION_CONTEXT values. This enforces that only the trusted application code can set the context used by the RLS predicate, ensuring tenants cannot impersonate other tenants.

Exam trap

The trap here is that candidates may confuse data protection features like dynamic data masking or Always Encrypted with access control mechanisms, failing to realize that RLS relies on controlling who can set the session context, not on encrypting or masking the data itself.

How to eliminate wrong answers

Option B is wrong because dynamic data masking only obfuscates data at query results; it does not prevent users from setting arbitrary SESSION_CONTEXT values or bypassing RLS. Option C is wrong because Always Encrypt protects data at rest and in transit but does not control who can set SESSION_CONTEXT values; it does not enforce RLS isolation. Option D is wrong because a firewall rule restricts network access but does not prevent an authenticated user from setting arbitrary SESSION_CONTEXT values within the database; it does not address the authorization of SESSION_CONTEXT manipulation.

Full explanation →

548

MCQmedium

Your company has an Azure SQL Database that uses active geo-replication to a secondary region. The primary database is hit by a logical corruption error. You need to restore the database to a point before the corruption occurred with minimal data loss. What should you do?

A.Fail over to the secondary region and then fail back.

B.Use the geo-replicated secondary to recover the data.

C.Seeding the secondary from a backup of the primary.

D.Restore the primary database to a point in time before the corruption using automated backups.

AnswerD

Point-in-time restore can recover to a state before the corruption.

Why this answer

Option D is correct because geo-replication replicates changes that may include the corruption; you cannot revert to a point before corruption without a backup. Option A is wrong because failover would bring the corrupted data to the secondary. Option B is wrong because geo-replication replicates the corruption.

Option C is wrong because point-in-time restore to a time before corruption is the correct approach.

Full explanation →

549

MCQmedium

Your company has a policy that all Azure SQL Databases must have their performance data (DTU/CPU, memory, IO) monitored and analyzed weekly. You need to automate the generation of a weekly report summarizing the top 10 queries by average CPU time. What should you use?

A.Configure a Power BI dashboard that connects directly to Azure SQL Database Query Store.

B.Create an Azure Monitor Workbook that queries the Query Store.

C.Set up a SQL Agent job to run queries against sys.dm_exec_query_stats and email the results.

D.Use the Query Performance Insight blade in the Azure portal and schedule an export to a storage account.

AnswerD

Query Performance Insight has built-in export capabilities for scheduled reports.

Why this answer

Option B is correct because Query Performance Insight is built into Azure SQL Database and provides query-level performance data. Option A is wrong because Azure Monitor Workbooks can visualize data but require a data source; they do not query Query Store directly. Option C is wrong because SQL Agent jobs can run queries but do not have built-in reporting capabilities.

Option D is wrong because Power BI requires an export mechanism.

Full explanation →

550

MCQhard

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is frequently throttled. Which configuration change can help reduce log rate throttling?

A.Increase max degree of parallelism

B.Increase the log rate limit by scaling up the service level objective

C.Reduce backup retention period

D.Add more compute replicas

AnswerB

Higher SLOs provide higher log rate limits.

Why this answer

Increasing the log rate limit allows more transactions per second. Option D is correct. Option A is wrong because Hyperscale does not have compute replicas for log rate.

Option B is wrong because changing backup retention does not affect log rate. Option C is wrong because increasing max degree of parallelism may not reduce log generation.

Full explanation →

551

MCQeasy

You need to ensure that all connections to an Azure SQL Database use encryption. The application uses the JDBC driver. What should you configure in the connection string?

A.Add 'hostNameInCertificate=*.database.windows.net'

B.Add 'encrypt=false' and 'trustServerCertificate=true'

C.Add 'encrypt=true' and 'trustServerCertificate=false'

D.Add 'useEncryption=true' and 'validateServerCertificate=true'

AnswerC

This forces encryption and validates the server certificate.

Why this answer

Option C is correct because for JDBC connections to Azure SQL Database, setting 'encrypt=true' enforces TLS encryption for data in transit, and 'trustServerCertificate=false' ensures that the server's TLS certificate is validated against the trusted certificate authority (CA) chain, preventing man-in-the-middle attacks. This is the recommended configuration for secure connections to Azure SQL Database.

Exam trap

The trap here is that candidates often confuse the JDBC properties with those of other drivers (like ODBC or .NET SqlClient), where 'TrustServerCertificate' or 'Encrypt' may have different default behaviors, leading them to select Option B or D incorrectly.

How to eliminate wrong answers

Option A is wrong because 'hostNameInCertificate=*.database.windows.net' is used to specify the expected hostname in the server's certificate when the server name in the connection string does not match the certificate's subject, but it does not enable or enforce encryption itself. Option B is wrong because 'encrypt=false' disables encryption, and 'trustServerCertificate=true' bypasses certificate validation, which together create an insecure connection vulnerable to eavesdropping. Option D is wrong because 'useEncryption=true' and 'validateServerCertificate=true' are not valid JDBC connection properties; the correct JDBC properties are 'encrypt' and 'trustServerCertificate'.

Full explanation →

552

MCQhard

You are designing a disaster recovery plan for an Azure SQL Managed Instance that hosts a critical OLTP workload. The primary instance is deployed in West Europe. You need to ensure that failover to a secondary region occurs automatically when the primary becomes unavailable due to a regional outage, and that the secondary database is readable during normal operations. You also want to minimize storage costs. What should you configure?

A.Deploy a secondary instance using a Bicep template with geo-zone-redundant storage.

B.Implement auto-failover groups with a readable secondary in another region.

C.Configure active geo-replication between two managed instances.

D.Create a failover group between the primary and a secondary managed instance in a paired region.

AnswerD

Failover groups for SQL Managed Instance provide automatic failover and a readable secondary replica, meeting all requirements.

Why this answer

Option B is correct. Azure SQL Managed Instance supports failover groups with readable secondary replicas. By default, the secondary is read-only and can be used for read-only workloads, reducing the need for separate reporting databases.

The failover group provides automatic failover. Option A is wrong because geo-replication is available only for SQL Database, not SQL Managed Instance. Option C is wrong because a Bicep template is used for deployment, not for configuring DR.

Option D is wrong because auto-failover groups do include readable secondaries, but the correct answer is simply failover groups, as they encompass the feature.

Full explanation →

553

MCQeasy

You need to automate the execution of a T-SQL script against an Azure SQL Database every hour. Which Azure service should you use?

A.Azure Data Factory

B.Elastic Database Jobs

C.Azure Logic Apps

D.Azure Automation

AnswerD

Azure Automation with a runbook can schedule and execute T-SQL scripts.

Why this answer

Azure Automation with a runbook can run PowerShell or Python scripts that execute T-SQL against a database. Option A is wrong because Azure Logic Apps can also run SQL, but it is more suited for integration workflows. Option B is wrong because elastic jobs are for scheduling jobs across multiple databases, but they require a job agent.

Option D is wrong because Azure Data Factory is for data movement and transformation, not simple T-SQL execution.

Full explanation →

554

MCQmedium

You are migrating an on-premises SQL Server database to Azure SQL Managed Instance. The source database uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. Which step is required to enable TDE in the target Managed Instance?

A.Export the certificate to a PFX file, import it to Azure Key Vault, and configure TDE with a key vault key

B.Create a self-signed certificate in the Managed Instance and restore the database with KEEP_TDE

C.Use the same certificate file by uploading it to the Managed Instance via SQL Server Management Studio

D.Disable TDE on the source database before migration, then enable it after migration

AnswerA

Managed Instance supports customer-managed keys in Azure Key Vault.

Why this answer

Azure SQL Managed Instance does not support importing user-provided certificates for TDE. Instead, it relies on Azure Key Vault as the external key store. Therefore, you must export the on-premises TDE certificate to a PFX file, import it into Azure Key Vault, and then configure the Managed Instance to use that key vault key for TDE.

This ensures the database encryption key can be unwrapped and the database remains encrypted during and after migration.

Exam trap

The trap here is that candidates assume on-premises TDE certificates can be directly reused in Azure SQL Managed Instance, but Azure requires all TDE keys to be managed through Azure Key Vault, not via certificate files or self-signed certificates.

How to eliminate wrong answers

Option B is wrong because Managed Instance does not support the KEEP_TDE option; TDE must be configured using Azure Key Vault, not a self-signed certificate. Option C is wrong because you cannot upload a certificate file directly to a Managed Instance via SSMS; TDE in Azure SQL Managed Instance is managed exclusively through Azure Key Vault. Option D is wrong because disabling TDE on the source database would require decrypting the entire database, which is unnecessary and introduces significant downtime and risk; the correct approach is to migrate the TDE-protected database and re-enable TDE using a key vault key.

Full explanation →

555

MCQhard

Your company has a strict policy that Azure SQL Database backups must be encrypted with customer-managed keys stored in Azure Key Vault. You configure TDE with AKV integration. After a key rotation, you find that long-running queries start failing with encryption errors. What is the most likely cause?

A.The service principal used for AKV access has expired.

B.The previous key version was disabled or deleted in AKV.

C.The new key is in a different Azure region than the database.

D.The database is using service-managed TDE and cannot switch to customer-managed keys.

AnswerB

TDE requires all previous key versions to be enabled to decrypt existing data.

Why this answer

When TDE is configured with Azure Key Vault (AKV) integration, the database uses the current key version from AKV to encrypt and decrypt data. If the previous key version is disabled or deleted during a key rotation, any long-running queries that still rely on that specific key version for decryption will fail with encryption errors. The database cannot automatically fall back to the new key version for in-flight operations that started before the rotation.

Exam trap

The trap here is that candidates often assume key rotation is seamless and never causes failures, overlooking that disabling or deleting the previous key version can break in-flight operations that still depend on it.

How to eliminate wrong answers

Option A is wrong because the service principal used for AKV access is a separate authentication entity; its expiration would cause a persistent access failure, not selective failures on long-running queries after a key rotation. Option C is wrong because Azure Key Vault and Azure SQL Database can be in different regions; cross-region access is supported and does not cause encryption errors. Option D is wrong because the scenario explicitly states that TDE with AKV integration is configured, meaning customer-managed keys are already in use; the database is not using service-managed TDE.

Full explanation →

556

MCQhard

You are reviewing a JSON configuration for an Azure SQL Database. The exhibit shows the database properties. Which statement about this database is correct?

A.The database has one high-availability replica.

B.The configuration is invalid because General Purpose tier cannot be zone-redundant.

C.The database uses locally-redundant backup storage.

D.The database is in General Purpose tier and supports zone redundancy.

AnswerB

Zone redundancy is only supported in Business Critical and Premium tiers.

Why this answer

Option C is correct because zoneRedundant is set to true, which requires a premium service tier. General Purpose (GP) does not support zone redundancy. The configuration is invalid.

Option A is wrong because GP does not support zone redundancy. Option B is wrong because highAvailabilityReplicaCount is 0, not 1. Option D is wrong because backupStorageRedundancy is Geo, which is valid.

Full explanation →

557

MCQeasy

You are deploying an Azure SQL Database for a new application that requires high availability with automatic failover within the same region. The database must be able to recover from a zonal failure without data loss. Which deployment option should you use?

A.Hyperscale tier with zone redundancy

B.Standard tier with failover group

C.Business Critical tier with zone redundancy enabled

D.Basic tier with active geo-replication

AnswerC

Business Critical tier with zone redundancy provides zonal failover with zero data loss.

Why this answer

The Business Critical tier with zone redundancy enabled provides the highest level of availability within a single Azure region by synchronously replicating data across three different availability zones. This ensures that if one zone fails, automatic failover occurs with no data loss because all transactions are committed to at least two replicas before being acknowledged. This meets the requirement for zonal failure recovery without data loss.

Exam trap

The trap here is that candidates often confuse zone redundancy with geo-replication, assuming that any tier with failover groups or geo-replication can handle zonal failures, but only zone-redundant configurations within the same region guarantee zero data loss during a zonal outage.

How to eliminate wrong answers

Option A is wrong because the Hyperscale tier, while offering high performance and fast scaling, does not support zone redundancy for the primary replica; it only supports zone redundancy for secondary replicas, so a zonal failure could cause data loss or downtime for the primary. Option B is wrong because the Standard tier does not offer zone redundancy; it uses local redundant storage (LRS) and can only provide availability within a single zone, so a zonal failure would result in data loss and downtime. Option D is wrong because the Basic tier does not support zone redundancy or geo-replication; it is designed for development and testing with no SLA for high availability, and active geo-replication is not available on the Basic tier.

Full explanation →

558

MCQhard

Your company has a SQL Server on Azure VM running a critical database. The VM is in a single availability set. You need to achieve a Recovery Point Objective (RPO) of 5 minutes and a Recovery Time Objective (RTO) of 15 minutes in case of an Azure region failure. What should you implement?

A.Configure SQL Server Always On Availability Groups with synchronous commit to a secondary VM in another region.

B.Configure Azure Site Recovery to replicate the VM to a secondary region.

C.Use Azure Backup with daily backups and geo-restore.

D.Store database files on geo-redundant storage (GRS) and enable auto-failover.

AnswerB

Azure Site Recovery provides replication with RPO of seconds to minutes and RTO of minutes.

Why this answer

Option A is correct because Azure Site Recovery can replicate the VM to another region and provide the required RPO and RTO. Option B is wrong because Always On Availability Groups on Azure VMs require at least two VMs and a witness, and may not achieve 5-minute RPO without synchronous commit (which adds latency). Option C is wrong because Azure Backup has typical RPO of 24 hours for daily backups.

Option D is wrong because geo-redundant storage does not provide application-consistent recovery.

Full explanation →

559

MCQmedium

You have an Azure SQL Database that needs to be backed up daily using Azure Automation runbooks. The runbook must trigger an export of the database to a storage account. How should you configure the runbook to authenticate securely to Azure?

A.Use a shared access signature (SAS) token stored in the runbook

B.Use Automation Account credential assets

C.Enable a system-assigned managed identity for the Automation account

D.Store the SQL admin credentials as variables in the runbook

AnswerC

Managed identities provide secure authentication without storing credentials.

Why this answer

Option C is correct because Managed Identity (system-assigned or user-assigned) is the recommended secure authentication method for Azure Automation runbooks, avoiding stored credentials. Option A uses credentials stored in the runbook, which is less secure. Option B uses automation account credentials, which still requires key management.

Option D is not a valid type.

Full explanation →

560

Multi-Selecthard

Which THREE of the following are required considerations when configuring a failover group for Azure SQL Managed Instance?

Select 3 answers

A.Both instances must be in the same partner region pair.

B.Both instances must have the same service tier.

C.The secondary instance must be in a different Azure region.

D.Automatic failover must be enabled.

E.The databases must be in the same resource group.

AnswersA, C, E

Correct: Failover groups require partner regions.

Why this answer

Options A, B, and D are correct. Both instances must be in the same partner region pair (e.g., East US and West US). The secondary instance must be in a different region.

The databases must be in the same resource group. Option C is incorrect because the secondary instance can have different service tiers. Option E is incorrect because failover groups support automatic failover, but the question asks for considerations; automatic failover is a feature, not a requirement.

Full explanation →

561

MCQmedium

You are reviewing a PowerShell script that configures auditing for an Azure SQL Database. The script sets an audit rule with the specified parameters. After running the script, you notice that SELECT operations are not being audited. What is the most likely cause?

A.The AuditActionGroup specified does not capture SELECT operations.

B.The retention days are set too low, causing logs to be overwritten.

C.The storage endpoint is incorrectly formatted.

D.The storage account access key is invalid.

AnswerA

DATABASE_OBJECT_CHANGE_GROUP captures DDL changes, not SELECT.

Why this answer

The script likely specifies an AuditActionGroup that does not include the group responsible for capturing SELECT operations. In Azure SQL Database auditing, SELECT operations are captured by the SUCCESSFUL_SCHEMA_OBJECT_ACCESS_GROUP or similar action groups. If the configured AuditActionGroup omits this group, SELECT statements will not be logged, even though other operations may be audited correctly.

Exam trap

The trap here is that candidates may assume all DML operations (including SELECT) are captured by default, but Azure SQL Database auditing requires explicit inclusion of the appropriate action group for SELECT operations.

How to eliminate wrong answers

Option B is wrong because retention days affect how long logs are kept, not which operations are captured; low retention may cause logs to be overwritten but does not prevent SELECT operations from being audited. Option C is wrong because an incorrectly formatted storage endpoint would cause all audit logs to fail to write, not selectively miss SELECT operations. Option D is wrong because an invalid storage account access key would prevent any audit logs from being written to the storage account, not specifically exclude SELECT operations.

Full explanation →

562

MCQmedium

You are managing an Azure SQL Database in the General Purpose service tier with 100 DTUs. The database supports an e-commerce application. Over the past week, you notice that CPU usage frequently reaches 100% during peak hours, causing query timeouts. You have identified that the most expensive query is a SELECT statement that joins five tables and returns aggregated sales data. You need to reduce CPU pressure without changing the service tier or adding indexes. What should you do?

A.Scale up to the Business Critical tier.

B.Increase the DTUs to 200.

C.Use Query Store to identify and force a more efficient execution plan.

D.Add a nonclustered index on the join columns.

AnswerC

Query Store can capture plan history and force a plan that uses less CPU without changing tier or indexes.

Why this answer

Option A is correct because enabling Query Store and forcing a plan that reduces CPU is a non-intrusive way to improve performance. Option B is wrong because it increases DTUs, which changes service tier. Option C is wrong because it adds indexes.

Option D is wrong because scaling up is changing tier.

Full explanation →

563

MCQmedium

You manage an Azure SQL Database that is experiencing performance degradation during peak hours. You suspect that the current pricing tier is insufficient. You need to increase performance with minimal downtime. Which action should you take?

A.Use the Azure portal to scale up the DTU or vCore service tier.

B.Create a new database on a higher tier and copy data manually.

C.Enable read scale-out to offload read workloads.

D.Implement horizontal sharding across multiple databases.

AnswerA

Scaling up is a dynamic operation with minimal downtime.

Why this answer

Scaling up the DTU or vCore service tier in the Azure portal is a dynamic scaling operation that typically completes within minutes and does not require application downtime. Azure SQL Database supports online scaling, meaning the database remains available during the transition, with only a brief connection failover at the end. This directly addresses the need to increase performance during peak hours with minimal disruption.

Exam trap

The trap here is that candidates might confuse scaling up (vertical scaling) with scaling out (horizontal scaling) or offloading reads, and choose a more complex or disruptive option instead of the straightforward, supported online scaling operation.

How to eliminate wrong answers

Option B is wrong because manually creating a new database and copying data introduces significant downtime and complexity, and is not necessary when Azure SQL Database supports online scaling. Option C is wrong because enabling read scale-out only offloads read-only workloads to a readable secondary replica; it does not increase the compute or storage capacity of the primary database to handle peak-hour performance degradation. Option D is wrong because horizontal sharding is a design pattern for distributing data across multiple databases to handle massive scale, not a quick operational fix for a single database experiencing performance issues during peak hours.

Full explanation →

564

MCQmedium

You are a database administrator for an e-commerce company that uses Azure SQL Database to store order data. The company is implementing a new policy that requires all database access to be audited, including both successful and failed attempts. Additionally, the audit logs must be retained for at least one year for compliance purposes. You need to configure auditing for the database. What should you do?

A.Use the 'Audit Logs' feature in the Azure portal to enable auditing and set retention to 365 days

B.Enable auditing on the database and configure it to send logs to a Log Analytics workspace, then set the retention in Log Analytics to 365 days

C.Enable auditing on the server and configure it to send logs to an event hub

D.Enable auditing on the database, set the audit log destination to a storage account, and configure retention to 365 days

AnswerD

This captures all database events and retains logs for one year.

Why this answer

Option D is correct because Azure SQL Database auditing can be configured at the database level to send audit logs to a storage account, which allows you to set a retention policy of 365 days directly on the audit configuration. This meets the requirement to audit both successful and failed attempts and retain logs for at least one year for compliance.

Exam trap

The trap here is that candidates may confuse server-level auditing with database-level auditing or assume that Log Analytics or event hubs inherently support retention policies, but only storage accounts allow direct retention configuration within the audit settings.

How to eliminate wrong answers

Option A is wrong because there is no standalone 'Audit Logs' feature in the Azure portal; auditing is configured via the 'Auditing' blade under the database or server settings, and retention is set within the audit destination configuration, not a separate feature. Option B is wrong because while Log Analytics can be used as a destination for audit logs, retention is configured at the Log Analytics workspace level (not within the audit settings), and the requirement specifies retaining logs for at least one year, which is possible but not the most direct or default method for database-level auditing. Option C is wrong because enabling auditing on the server and sending logs to an event hub does not provide a built-in retention policy; event hubs are for real-time streaming and require additional services to store logs for a year, making it unsuitable for the retention requirement.

Full explanation →

565

MCQmedium

You are a database administrator for a healthcare company that uses Azure SQL Database with Hyperscale tier. The database contains patient records and is critical for operations. You need to automate the process of refreshing the staging database from the production database every night. The refresh process must occur during a maintenance window from 2:00 AM to 4:00 AM. The solution must use point-in-time restore to ensure consistency and must minimize the storage costs. Additionally, the automation must notify the operations team if the refresh fails. What should you do?

A.Use Elastic Database Jobs to run a T-SQL script that uses RESTORE DATABASE from a backup file.

B.Create an Azure Automation runbook that performs a point-in-time restore of the production database to a new database, then renames the databases to swap staging. Schedule the runbook during the maintenance window and configure alerts for failure.

C.Use Azure Data Factory to copy data from production to staging using a copy activity.

D.Use Azure SQL Database export to BACPAC from production and import to staging using Azure Automation.

AnswerB

Point-in-time restore ensures consistency, and Azure Automation can handle scheduling and alerting.

Why this answer

Option C is correct. Azure Automation runbooks can schedule a point-in-time restore of the production database to a new database, then swap the names to make it the staging database. This uses the Hyperscale tier's fast restore capabilities and minimizes storage by not keeping multiple copies.

Option A is incorrect because Elastic Database Jobs cannot perform restore operations. Option B is incorrect because Data Factory is for ETL, not database restore. Option D is incorrect because using export/import is slower and more expensive than PITR.

Full explanation →

566

Multi-Selecteasy

You are configuring security for an Azure SQL Database that will be used by a web application. The application uses a connection string with SQL authentication. You need to protect the database from SQL injection attacks. Which two measures should you implement? (Choose two.)

Select 2 answers

A.Enable Transparent Data Encryption (TDE).

B.Use parameterized queries in the application.

C.Configure Dynamic Data Masking (DDM).

D.Implement stored procedures with input validation.

E.Enable Always Encrypted on sensitive columns.

AnswersB, D

Parameterized queries prevent injection.

Why this answer

Parameterized queries ensure that user input is treated as data, not executable code, by separating SQL logic from input values. This prevents attackers from injecting malicious SQL statements into the query string, which is the primary defense against SQL injection attacks.

Exam trap

The trap here is that candidates often confuse data-at-rest or data-masking features (TDE, DDM, Always Encrypted) with injection prevention, when in fact only query-level controls like parameterized queries and validated stored procedures directly mitigate SQL injection.

Full explanation →

567

Multi-Selecthard

Which THREE factors should you consider when choosing between vCore and DTU purchase models for Azure SQL Database performance optimization?

Select 3 answers

A.Only vCore supports Azure Hybrid Benefit.

B.DTU is simpler for customers who want a bundled metric.

C.vCore allows reserved instance pricing for cost savings.

D.vCore provides more predictable performance for consistent workloads.

E.Only DTU supports elastic pools.

AnswersB, C, D

DTU combines compute, storage, and I/O.

Why this answer

Option B is correct because the DTU (Database Transaction Unit) model bundles compute, storage, and I/O into a single, simple metric, making it easier for customers who want a straightforward, pre-configured performance tier without needing to manage individual resources. This contrasts with the vCore model, which requires separate configuration of vCores, memory, and storage, offering more granular control but greater complexity.

Exam trap

The trap here is that candidates often assume Azure Hybrid Benefit or elastic pools are exclusive to one model, when in fact both features are available across vCore and DTU, leading to incorrect elimination of correct options like B, C, and D.

Full explanation →

568

MCQhard

You are a database administrator for a company that runs a large e-commerce platform on Azure SQL Database. The platform experiences unpredictable traffic spikes during flash sales, causing performance degradation. You need to implement a solution that automatically scales the database resources based on demand without manual intervention. Which feature should you use?

A.Elastic pool

B.Manual scaling using Azure CLI

C.Serverless compute tier

D.Hyperscale service tier

AnswerD

Hyperscale automatically adjusts compute resources and supports rapid scaling.

Why this answer

The Hyperscale service tier (Option D) is correct because it provides near-instantaneous, automatic scaling of compute and storage resources in response to workload demand, without requiring manual intervention. For unpredictable traffic spikes during flash sales, Hyperscale's architecture decouples compute from storage, allowing compute nodes to scale up or down independently and transparently, while its buffer pool and page server design handle high concurrency and large data volumes efficiently.

Exam trap

The trap here is that candidates often confuse the Serverless compute tier (Option C) with automatic scaling, but Serverless is designed for intermittent workloads with auto-pause, not for handling unpredictable high-traffic spikes like flash sales, whereas Hyperscale provides true elastic scaling for large, demanding workloads.

How to eliminate wrong answers

Option A is wrong because Elastic pools are designed for managing multiple databases with variable but predictable resource needs, not for automatically scaling a single large database in response to unpredictable spikes; they require manual configuration of eDTU or vCore limits and do not auto-scale individual databases. Option B is wrong because manual scaling using Azure CLI requires human intervention to execute commands, which defeats the requirement for automatic scaling without manual intervention. Option C is wrong because the Serverless compute tier auto-pauses during inactivity and is intended for intermittent, low-traffic workloads, not for handling unpredictable high-traffic spikes; it has a maximum vCore limit and does not provide the rapid, elastic scaling needed for flash sales.

Full explanation →

569

MCQmedium

You have an Azure SQL Database in the Hyperscale service tier. You need to ensure that the database remains available during a single Azure zone failure. The solution must not require manual intervention. What should you configure?

A.Create a failover group with a secondary in a different zone.

B.Enable zone redundancy on the Hyperscale database.

C.Configure active geo-replication to a secondary server in a different zone.

D.Configure an auto-failover group with a secondary in a different zone.

AnswerB

Hyperscale zone redundancy provides automatic recovery from zone failure.

Why this answer

Option A is correct because Hyperscale supports zone redundancy, which automatically places replicas in different zones. Option B (active geo-replication) is for DR, not zone failure. Option C (failover group) is for managed instances.

Option D (auto-failover group) is for managed instances, not Hyperscale databases.

Full explanation →

570

MCQhard

Refer to the exhibit. An Azure SQL Database in the Standard tier (S2: 50 DTU) is consistently showing high DTU consumption. Which action would most effectively reduce DTU usage?

A.Create an index on the tables accessed by Query 1234

B.Increase the log_write_percent by adjusting transaction log settings

C.Scale up to a higher service tier (e.g., S3)

D.Rebuild all indexes in the database

AnswerA

Reducing logical reads via indexing directly lowers DTU usage.

Why this answer

Query 1234 has high logical reads, indicating inefficient queries causing high DTU. Optimizing it would reduce resource consumption.

Full explanation →

571

MCQmedium

You are a database administrator for a healthcare company that uses Azure SQL Database for its patient management system. The database is currently 500 GB and uses the General Purpose tier in the West US region. The application experiences high read traffic during business hours, and you need to improve read performance without increasing costs significantly. You also need to ensure that read queries are isolated from write workloads. The application supports read-only routing using connection strings. You have the following options: A. Create a secondary replica in the same region using active geo-replication and configure the application to use the secondary for read-only queries. B. Upgrade the database to the Business Critical tier and use the built-in readable secondary. C. Implement read scale-out by adding a readable secondary replica in the same region using the built-in read scale-out feature of the General Purpose tier. D. Create an elastic pool with multiple databases and distribute read queries across them. Which option should you choose?

A.Implement read scale-out in General Purpose tier.

B.Create a secondary replica using active geo-replication and configure read-only routing.

C.Upgrade to Business Critical tier and use its readable secondary.

D.Create an elastic pool and distribute read queries.

AnswerB

Active geo-replication provides a readable secondary without changing the tier, isolating reads.

Why this answer

Option B is correct because active geo-replication allows you to create a readable secondary replica in the same region, which can be used to offload read-only queries from the primary. The application can use connection strings with ApplicationIntent=ReadOnly to automatically route read queries to the secondary, isolating them from write workloads. This improves read performance without upgrading to a higher-cost tier, as the General Purpose tier does not natively support readable secondaries within the same region.

Exam trap

The trap here is that candidates often assume the General Purpose tier supports readable secondaries natively (like the Business Critical tier), but in reality, you must use active geo-replication to create a readable secondary in the same region for the General Purpose tier.

How to eliminate wrong answers

Option A is wrong because the General Purpose tier does not have a built-in read scale-out feature; readable secondaries are only available in the Business Critical tier or via active geo-replication. Option C is wrong because upgrading to the Business Critical tier would significantly increase costs, which contradicts the requirement to avoid increasing costs significantly. Option D is wrong because creating an elastic pool does not provide a readable secondary for read isolation; it distributes databases but does not offload read queries from a single database's primary replica.

Full explanation →

572

MCQeasy

You are designing a security strategy for Azure SQL Managed Instance. The compliance team requires that all database backups be encrypted at rest using a customer-managed key. Which feature should you enable?

A.Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault.

B.Always Encrypted with a column master key in Azure Key Vault.

C.Transparent Data Encryption (TDE) with a service-managed key.

D.Row-Level Security (RLS) with a custom authorization function.

AnswerA

TDE encrypts the underlying database files and backups at rest.

Why this answer

Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault is the correct feature because it encrypts the database at rest, including all backup files, using a key that the customer controls. This satisfies the compliance requirement for customer-managed key encryption of backups, as TDE automatically encrypts backups when the database is encrypted.

Exam trap

The trap here is that candidates confuse Always Encrypted (which encrypts column data but not backups) with TDE (which encrypts the entire database and backups), leading them to select Always Encrypted when the requirement explicitly mentions backup encryption.

How to eliminate wrong answers

Option B is wrong because Always Encrypted protects sensitive data in transit and at rest within the database by encrypting specific columns, but it does not encrypt entire database backups; backups of a database with Always Encrypted columns are not automatically encrypted by this feature. Option C is wrong because TDE with a service-managed key uses a key managed by Azure, not a customer-managed key, so it does not meet the compliance requirement for customer-controlled encryption. Option D is wrong because Row-Level Security (RLS) controls access to rows in a table based on user authorization, but it does not provide any encryption of data at rest or backups.

Full explanation →

573

Multi-Selecthard

Which THREE of the following are valid methods to configure network security for Azure SQL Managed Instance?

Select 3 answers

A.Configure a virtual network rule to allow traffic from a specific subnet.

B.Deploy the instance in an isolated subnet with network security groups (NSGs).

C.Use Azure Private Link with a private endpoint.

D.Configure server-level IP firewall rules.

E.Enable service endpoints on the subnet.

AnswersA, B, C

Virtual network rules are used for Managed Instance.

Why this answer

Option A is correct because Azure SQL Managed Instance requires deployment into a dedicated subnet within a virtual network. Configuring a virtual network rule allows you to explicitly permit traffic from a specific subnet, ensuring that only resources within that subnet can connect to the instance. This is a fundamental network security mechanism for Managed Instance, as it relies on VNet injection for isolation.

Exam trap

The trap here is that candidates often confuse Azure SQL Database's firewall rules with Azure SQL Managed Instance's network security model, mistakenly thinking IP firewall rules or service endpoints apply to Managed Instance, when in fact it relies entirely on VNet integration and NSGs.

Full explanation →

574

Matchingmedium

Match each Azure SQL Database command to its function.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts

Matches

Sets database-level configuration options

Clears the procedure cache

Changes the recovery model of the database

Creates a database as a copy of another database

Why these pairings

These T-SQL commands are used for database management in Azure SQL Database.

Full explanation →

575

Multi-Selectmedium

You are configuring Azure SQL Database firewall rules. Which THREE actions require the 'Allow Azure services and resources to access this server' setting to be enabled? (Choose three.)

Select 3 answers

A.Azure Data Factory using a self-hosted integration runtime.

B.An on-premises application with a static public IP.

C.A virtual machine using a private endpoint.

D.An Azure Logic App connecting to the database.

E.An Azure App Service connecting to the database.

AnswersA, D, E

If the self-hosted IR is on Azure, it uses Azure IPs.

Why this answer

Option A is correct because the 'Allow Azure services and resources to access this server' firewall setting permits connections from any Azure service that does not have a fixed public IP or a private endpoint. Azure Data Factory using a self-hosted integration runtime (IR) runs on-premises or on a VM, but the IR itself initiates outbound connections to Azure SQL Database from a dynamic Azure IP range. Without this setting enabled, the connection is blocked because the self-hosted IR's source IP is not a static, whitelisted address.

Exam trap

The trap here is that candidates often think the 'Allow Azure services' setting is required for any Azure resource, but it is only needed when the resource does not have a static, whitelistable IP or a private endpoint; for example, a VM with a private endpoint does not need this setting because it uses a private IP within the virtual network.

Full explanation →

576

MCQhard

Refer to the exhibit. You are reviewing an Azure Resource Manager template for deploying an Azure SQL Database server. The template sets publicNetworkAccess to Disabled, minimalTlsVersion to 1.2, and azureAdOnlyAuthentication to true. However, the deployment fails with an error. What is the most likely cause?

A.minimalTlsVersion 1.2 is not supported in Azure SQL Database

B.publicNetworkAccess Disabled requires a private endpoint to be defined in the same template

C.When azureAdOnlyAuthentication is true, the administratorLogin and administratorLoginPassword properties must not be specified

D.The password does not meet complexity requirements

AnswerC

Azure AD-only authentication disables SQL authentication, so providing SQL admin credentials is invalid.

Why this answer

When azureAdOnlyAuthentication is set to true in an Azure SQL Database ARM template, the administratorLogin and administratorLoginPassword properties must be omitted because Azure AD authentication replaces SQL authentication as the sole identity provider. Including these properties causes a validation conflict, as the deployment expects no SQL admin credentials when Azure AD-only authentication is enabled.

Exam trap

The trap here is that candidates assume the deployment fails due to a missing private endpoint or password issue, but the real conflict is the simultaneous presence of SQL admin credentials and Azure AD-only authentication, which the ARM template validation explicitly rejects.

How to eliminate wrong answers

Option A is wrong because minimalTlsVersion 1.2 is fully supported in Azure SQL Database and is actually the recommended minimum TLS version. Option B is wrong because publicNetworkAccess Disabled does not require a private endpoint to be defined in the same template; it only blocks public connectivity, and a private endpoint can be added separately or after deployment. Option D is wrong because the error is not related to password complexity; the deployment fails before password validation due to the conflicting properties when azureAdOnlyAuthentication is true.

Full explanation →

577

MCQmedium

You manage an Azure SQL Managed Instance that hosts a critical OLTP database. You notice that the average CPU usage is consistently above 90% during business hours. You have enabled Intelligent Insights, which recommends creating a missing index. What should you do first to validate the recommendation before implementing it?

A.Use Query Store to review query performance and missing index details.

B.Scale up the managed instance to a higher tier.

C.Enable automatic index tuning.

D.Create the recommended index immediately.

AnswerA

Allows validation of the index impact on specific queries.

Why this answer

Option B is correct because you should use Query Store to identify the specific queries causing high CPU and verify the index impact. Option A is wrong because automatic index tuning could be risky without validation. Option C is wrong because scaling up may not address the root cause.

Option D is wrong because the index already exists in the recommendation.

Full explanation →

578

Multi-Selectmedium

Which TWO actions should you take to achieve high availability for an Azure SQL Managed Instance with a 99.99% SLA? (Choose two.)

Select 2 answers

A.Enable automated backups with long-term retention.

B.Use Business Critical service tier.

C.Deploy the instance as zone-redundant.

D.Configure geo-replication to a secondary region.

E.Use General Purpose service tier.

AnswersB, C

Business Critical provides the highest SLA within a region.

Why this answer

Options A and C are correct. Zone-redundant deployment provides resilience against zone failures, and Business Critical service tier provides the highest availability within a zone. Option B is incorrect because General Purpose does not provide 99.99% SLA.

Option D is incorrect because geo-replication is for disaster recovery, not high availability. Option E is incorrect because automatic backups do not contribute to high availability SLA.

Full explanation →

579

MCQmedium

You are reviewing the firewall and virtual network rules for an Azure SQL Database server as shown in the exhibit. The server has public network access disabled. A client application in a different virtual network (vnet2) needs to connect to the database. What must you do to allow the connection?

A.Add a virtual network rule that references subnet2 in vnet2.

B.Add the IP address of the client to the ipRules array.

C.Set publicNetworkAccess to 'Enabled' and add an IP firewall rule.

D.Create a server-level firewall rule to allow all Azure services.

AnswerA

Virtual network rules allow traffic from specific subnets.

Why this answer

Since public network access is disabled, the server rejects all public IP-based connections. To allow a client in a different virtual network (vnet2) to connect, you must create a virtual network rule that references subnet2 in vnet2. This rule establishes a private endpoint-like connection through the Azure backbone, bypassing the public internet and honoring the disabled public network access setting.

Exam trap

The trap here is that candidates assume IP firewall rules can still work when public network access is disabled, but in reality, disabling public network access completely overrides all IP-based rules, making virtual network rules the only viable option for cross-VNet connectivity.

How to eliminate wrong answers

Option B is wrong because adding the client's IP address to the ipRules array requires public network access to be enabled; with publicNetworkAccess set to 'Disabled', all IP firewall rules are ignored. Option C is wrong because setting publicNetworkAccess to 'Enabled' and adding an IP firewall rule would expose the server to the public internet, which is unnecessary and less secure when a virtual network rule can provide private connectivity. Option D is wrong because creating a server-level firewall rule to allow all Azure services (0.0.0.0/0) also requires public network access to be enabled and does not restrict traffic to a specific virtual network; it would allow any Azure service, not just the client in vnet2.

Full explanation →

580

MCQeasy

You need to configure Azure SQL Database to automatically adjust indexing based on workload patterns. Which feature should you enable?

A.Azure Advisor

B.Intelligent Insights

C.Automatic tuning

D.Query Store

AnswerC

Automatic tuning can automatically create and drop indexes.

Why this answer

Automatic tuning in Azure SQL Database continuously analyzes query execution plans and workload patterns, then automatically creates, drops, or rebuilds indexes to improve performance. It uses built-in intelligence to recommend and apply index changes without manual intervention, making it the correct feature for automatically adjusting indexing based on workload patterns.

Exam trap

The trap here is that candidates often confuse Azure Advisor's recommendations with automatic implementation, but Azure Advisor only provides suggestions, whereas Automatic tuning actually applies index changes automatically based on workload patterns.

How to eliminate wrong answers

Option A is wrong because Azure Advisor provides proactive recommendations for cost, security, reliability, and performance, but it does not automatically adjust indexing; it only suggests manual actions. Option B is wrong because Intelligent Insights uses built-in intelligence to monitor database performance and detect anomalies, but it does not automatically implement index changes; it delivers root cause analysis and recommendations. Option D is wrong because Query Store captures query execution statistics and plan history for troubleshooting and tuning, but it does not automatically adjust indexing; it requires manual analysis or integration with Automatic tuning to apply changes.

Full explanation →

581

Matchingmedium

Match each Azure SQL Database error code to its meaning.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts

Matches

Service is busy; retry the request later

Database is not currently available

Cannot open server '%.*ls' requested by the login

Login failed for user

Why these pairings

These are common error codes encountered when working with Azure SQL Database.

Full explanation →

582

MCQeasy

Your organization requires that all Azure SQL Database deployments use the most recent version of SQL Server engine to ensure security and performance. Which deployment option automatically provides the latest engine version without requiring manual upgrades?

A.SQL Server on Azure Virtual Machines.

B.Azure SQL Database elastic pool.

C.Azure SQL Database (single database).

D.Azure SQL Managed Instance.

AnswerC

Azure SQL Database is always updated to the latest engine version.

Why this answer

Azure SQL Database (single database) is a Platform-as-a-Service (PaaS) offering where Microsoft manages the underlying SQL Server engine. Microsoft automatically applies the latest engine version updates during the service's maintenance windows, ensuring you always run a supported and current version without any manual upgrade effort. This aligns directly with the requirement for automatic, hands-off version management.

Exam trap

The trap here is that candidates confuse 'automatic updates' with 'latest engine version' and assume that all PaaS options (like Azure SQL Managed Instance) provide the exact same latest engine version immediately, but in reality, Azure SQL Managed Instance may run a slightly older stable version unless you explicitly opt into newer preview versions, whereas single databases are always on the most current generally available engine.

How to eliminate wrong answers

Option A is wrong because SQL Server on Azure Virtual Machines is an Infrastructure-as-a-Service (IaaS) offering where you are responsible for manually applying SQL Server updates and version upgrades, including major version changes. Option B is wrong because Azure SQL Database elastic pool is built on the same PaaS engine as single databases, but it is a deployment model for pooling resources across multiple databases, not a separate engine version; it also receives automatic updates, but the question asks for a deployment option that 'automatically provides the latest engine version,' and elastic pools share the same automatic update mechanism as single databases, so the distinction is that the question's correct answer is the single database as the most direct and common example, though elastic pools also qualify; however, the exam expects single database as the canonical answer. Option D is wrong because Azure SQL Managed Instance is also a PaaS offering with automatic updates, but it runs a near-100% compatible SQL Server engine that may lag slightly behind the latest engine version available in single databases, and it requires a manual decision to opt into preview or newer engine versions via configuration, so it does not 'automatically provide the latest engine version' without any action.

Full explanation →

583

MCQeasy

You need to create an Azure SQL Database that automatically scales storage up to 4 TB. Which service tier should you select?

A.Standard

B.Hyperscale

C.Basic

D.Business Critical

AnswerD

Business Critical supports up to 4 TB (with provisioned compute).

Why this answer

Business Critical is the correct service tier because it supports up to 4 TB of storage with provisioned compute, high-performance local SSD storage, and built-in high availability. Standard tier maxes out at 1 TB, Basic at 2 GB, and Hyperscale is designed for up to 100 TB but uses a different architecture with page servers and log services, not the fixed 4 TB limit.

Exam trap

The trap here is that candidates often assume Hyperscale is the only tier for large storage, but the question specifies 'automatically scales storage up to 4 TB'—a fixed cap that matches Business Critical, not Hyperscale's unbounded scaling model.

How to eliminate wrong answers

Option A is wrong because Standard tier has a maximum storage limit of 1 TB, not 4 TB. Option B is wrong because Hyperscale supports up to 100 TB, which exceeds the 4 TB requirement, but its architecture (with page servers and buffer pool extension) is not designed for a fixed 4 TB limit and is intended for very large databases, not the specific 4 TB cap. Option C is wrong because Basic tier is limited to 2 GB of storage, far below the 4 TB requirement.

Full explanation →

584

MCQhard

You are troubleshooting an Always On Availability Group named AG1. The exhibit shows the current state. The primary replica shows PENDING_FAILOVER. The secondary replica is DISCONNECTED with error 35202. What is the most likely cause of this issue?

A.Network connectivity between the replicas is blocked on port 5022

B.The availability group listener is not configured correctly

C.The primary instance encountered a critical error and failed over automatically

D.The cluster has lost quorum

AnswerA

Error 35202 often relates to connectivity issues on the mirroring endpoint port.

Why this answer

The PENDING_FAILOVER state on the primary replica indicates that a failover was initiated but could not complete because the secondary replica is DISCONNECTED. Error 35202 specifically indicates that a connection attempt to the secondary replica failed, which in an Always On Availability Group (AG) is typically due to a network connectivity issue on the dedicated endpoint port (default 5022). Since the secondary is unreachable, the failover cannot finalize, leaving the primary in a pending state.

Exam trap

The trap here is that candidates often confuse the availability group listener port (default 1433) with the replica-to-replica synchronization endpoint port (default 5022), leading them to incorrectly attribute the issue to listener misconfiguration rather than a blocked endpoint port.

How to eliminate wrong answers

Option B is wrong because the availability group listener is used for client connections and routing, not for replica-to-replica synchronization; a misconfigured listener would not cause the secondary to be DISCONNECTED with error 35202. Option C is wrong because if the primary encountered a critical error and failed over automatically, the primary would no longer be in PENDING_FAILOVER—it would have transitioned to a resolving or offline state, and the secondary would have become the new primary. Option D is wrong because cluster quorum loss would affect the entire cluster's ability to manage resources, typically resulting in both replicas being in an unresolved state or the AG going offline entirely, not a specific PENDING_FAILOVER on the primary with a DISCONNECTED secondary.

Full explanation →

585

MCQeasy

You need to automatically notify the operations team when an Azure SQL Database reaches 80% storage usage. Which Azure service should you use to create the alert?

A.Azure Automation

B.Microsoft Sentinel

C.Azure Logic Apps

D.Azure Monitor

AnswerD

Azure Monitor can create metric alerts for storage usage.

Why this answer

Azure Monitor alerts can be configured to trigger based on metrics like storage percent. Option B is wrong because Azure Automation is for runbooks, not alerting. Option C is wrong because Microsoft Sentinel is a SIEM, not for simple metric alerts.

Option D is wrong because Azure Logic Apps can be triggered by alerts, but the alert itself is created in Azure Monitor.

Full explanation →

586

MCQmedium

Your team uses Azure DevOps to deploy database changes to Azure SQL Database. You need to automate the generation and deployment of database schema changes based on a Git branch merge. Which Azure service should you integrate?

A.Azure Automation Runbook triggered by a webhook from Azure DevOps.

B.Azure Pipelines with a release pipeline that uses the Azure SQL Database deployment task.

C.GitHub Actions with a workflow that runs on pull request merge, using the Azure SQL action.

D.Azure Logic Apps with an HTTP trigger from Azure DevOps, then use SQL connector to run scripts.

AnswerB

Azure Pipelines natively supports database deployments.

Why this answer

Option D is correct because Azure Pipelines (part of Azure DevOps) can be configured to run on branch merges and execute SQL scripts using the Azure SQL Database deployment task. Option A is wrong because GitHub Actions could be used but the team already uses Azure DevOps. Option B is wrong because Azure Automation Runbooks are not CI/CD focused.

Option C is wrong because Azure Logic Apps are not designed for CI/CD.

Full explanation →

587

MCQeasy

You need to ensure that all queries executed against an Azure SQL Database are audited and logged to a Log Analytics workspace for security analysis. Which feature should you enable?

A.SQL Vulnerability Assessment

B.Advanced Threat Protection

C.Microsoft Defender for Cloud

D.Azure SQL Auditing

AnswerD

Auditing captures database events and can be configured to send logs to Log Analytics workspace.

Why this answer

Azure SQL Auditing is the feature specifically designed to track database events and write them to an audit log destination, including a Log Analytics workspace. This enables security analysis by capturing all queries executed against the database, which can then be queried and analyzed within the workspace. The other options focus on vulnerability scanning, threat detection, or security posture management, not on recording query execution logs.

Exam trap

The trap here is that candidates often confuse 'threat detection' (Advanced Threat Protection) or 'security posture management' (Microsoft Defender for Cloud) with the specific need for a full query audit log, but only Azure SQL Auditing provides the granular, configurable logging of all executed queries to a Log Analytics workspace.

How to eliminate wrong answers

Option A is wrong because SQL Vulnerability Assessment is a service that scans for potential database vulnerabilities and misconfigurations, not a feature that logs query execution. Option B is wrong because Advanced Threat Protection detects anomalous activities and potential threats, but it does not provide a full audit trail of all queries. Option C is wrong because Microsoft Defender for Cloud is a unified security management platform that provides security recommendations and threat protection across cloud workloads, but it does not itself enable per-query auditing to a Log Analytics workspace.

Full explanation →

588

MCQhard

You are reviewing an Azure SQL Database server's vulnerability assessment settings. The exhibit shows the current configuration. A recent security audit requires that vulnerability assessment scans be enabled and that results be retained for at least 90 days. What should you do?

A.Add additional email addresses to ensure notification.

B.Change retentionDays to 90 and keep the state as Disabled.

C.Change state to Enabled and set retentionDays to 90.

D.Remove the disabledAlerts entries to enable all alerts.

AnswerC

Enables the scan and meets the retention requirement.

Why this answer

Option D is correct because the exhibit shows the vulnerability assessment is disabled (state: Disabled) and retentionDays is 30, which is less than 90. You must enable it and set retentionDays to at least 90. Option A is wrong because the disabledAlerts list is about alerts not to send, not the scan itself.

Option B is wrong because emailAddresses do not affect scan frequency or retention. Option C is wrong because the state must be enabled, not just retentionDays increased.

Full explanation →

589

MCQhard

You are evaluating the configuration of an Azure SQL Database as shown in the exhibit. You need to ensure that the database remains available during a zonal failure without data loss. Which feature contributes to this requirement?

A.General Purpose service tier.

B.Read scale-out.

C.Geo-redundant backup storage.

D.Zone redundancy enabled on Business Critical tier.

AnswerD

Zone redundancy provides synchronous replication across zones.

Why this answer

Zone redundancy on the Business Critical tier ensures that database replicas are placed in different availability zones within the same Azure region. During a zonal failure, the service automatically fails over to a synchronous replica in another zone, guaranteeing zero data loss because all transactions are synchronously committed across replicas. This directly meets the requirement of remaining available without data loss during a zonal outage.

Exam trap

The trap here is that candidates often confuse zone redundancy (which protects within a region) with geo-redundancy (which protects across regions), or assume that any service tier with high availability features (like General Purpose) can guarantee zero data loss during a zonal failure, when only the Business Critical tier with zone redundancy provides synchronous replication and automatic failover without data loss.

How to eliminate wrong answers

Option A is wrong because the General Purpose service tier uses remote storage and asynchronous replication, which cannot guarantee zero data loss during a zonal failure; it relies on page blob replication that may lose recent writes. Option B is wrong because Read scale-out provides a read-only replica for offloading read workloads, but it does not protect against zonal failures or ensure availability with zero data loss for write operations. Option C is wrong because Geo-redundant backup storage (RA-GRS) protects against regional disasters by storing backups in a paired region, but it does not provide real-time failover or zero data loss during a zonal failure within the primary region.

Full explanation →

590

Multi-Selecthard

Which THREE of the following are required to automate schema deployments to Azure SQL Database using Azure DevOps? (Select exactly three.)

Select 3 answers

A.A release pipeline with a 'Azure SQL Database deployment' task.

B.A SQL database project (.sqlproj) containing the schema.

C.A service connection to Azure with appropriate permissions.

D.A schema compare tool to generate deployment scripts.

E.A self-hosted build agent with SQL tools installed.

AnswersA, B, C

The deployment task executes the schema change.

Why this answer

Options A, C, and D are correct. A service connection is needed to authenticate to Azure; a database project is the source of schema; a release pipeline automates deployment. Option B is wrong because a self-hosted agent is optional; Microsoft-hosted agents can be used.

Option E is wrong because schema compare is a tool, not a requirement.

Full explanation →

591

MCQeasy

You are responsible for cost optimization of a non-production Azure SQL Database that is used for development testing. The database is only active during business hours (9 AM to 5 PM) on weekdays. Which compute tier and configuration would minimize cost while ensuring the database is available during working hours?

A.Use the serverless compute tier with auto-pause enabled and a 1-hour auto-pause delay.

B.Use the Hyperscale tier with a minimum of 1 vCore and disable auto-pause.

C.Use the provisioned General Purpose tier with 2 vCores and disable auto-pause.

D.Use the provisioned Business Critical tier with 1 vCore and enable auto-pause.

AnswerA

Correct: Serverless tier with auto-pause pauses the database during inactivity, saving costs outside business hours.

Why this answer

The serverless compute tier with auto-pause enabled and a 1-hour auto-pause delay is the most cost-effective choice for a non-production database used only during business hours. Serverless automatically pauses the database after 1 hour of inactivity (e.g., overnight and weekends), charging only for compute during active periods and storage at all times. This aligns perfectly with the 9 AM–5 PM weekday usage pattern, minimizing cost while ensuring the database is available when needed.

Exam trap

The trap here is that candidates may assume any tier with auto-pause enabled is sufficient, but they overlook that the underlying tier (e.g., Business Critical or Hyperscale) has a much higher base cost and is not designed for cost-optimized dev/test scenarios, making serverless the only truly cost-effective choice for intermittent usage.

How to eliminate wrong answers

Option B is wrong because the Hyperscale tier is designed for large, high-throughput production workloads with rapid scaling and read scale-out, not for cost optimization of a small dev/test database; it incurs higher base costs even at 1 vCore and disabling auto-pause means compute runs 24/7. Option C is wrong because the provisioned General Purpose tier with 2 vCores and auto-pause disabled runs compute continuously, incurring charges for idle hours overnight and weekends, which is wasteful for a non-production database. Option D is wrong because the Business Critical tier is a premium tier with high availability and local SSD storage, intended for mission-critical workloads; even with 1 vCore and auto-pause enabled, it is significantly more expensive than serverless and over-provisioned for development testing.

Full explanation →

592

MCQhard

You are the database administrator for a global e-commerce company that uses Azure SQL Database for its product catalog. The database is currently deployed in the East US region using the General Purpose service tier. The company is expanding to Europe and wants to improve disaster recovery posture. The new requirements are: RPO of 5 seconds, RTO of 1 hour, and the ability to serve read traffic from the secondary region during normal operations. The budget is limited, so you must minimize cost while meeting these requirements. The application connection strings can be updated to a listener endpoint. What should you recommend?

A.Keep the primary as General Purpose, create a readable secondary in the same region, and configure active geo-replication to a secondary in West Europe.

B.Upgrade the primary to Business Critical, configure a failover group with a readable secondary in West Europe using Business Critical, and use the listener endpoint for application connections.

C.Deploy a zone-redundant Business Critical database in East US and use auto-failover groups to a secondary in West Europe with General Purpose.

D.Upgrade the primary database to Business Critical, create a readable secondary in the same region, and use geo-restore for DR.

AnswerB

Business Critical meets RPO, readable secondary serves read traffic, failover group provides automated failover with listener endpoint.

Why this answer

Option D is correct because it uses Business Critical in the primary region for low RPO and readable secondary in the secondary region for read traffic, while using a failover group for automated failover. Option A is wrong because General Purpose cannot meet 5-second RPO. Option B is wrong because a readable secondary in the same region doesn't serve read traffic from Europe.

Option C is wrong because zone-redundancy doesn't protect regionally.

Full explanation →

593

MCQeasy

You are designing a disaster recovery plan for an Azure SQL Database that stores critical financial data. The RPO must be 5 seconds, and the RTO must be 30 seconds. Which solution should you recommend?

A.Zone-redundant configuration within the primary region.

B.Failover groups with automatic failover policy.

C.Active geo-replication with manual failover.

D.Automated backups with geo-restore.

AnswerB

Correct: Provides low RPO and RTO with automatic failover.

Why this answer

Option A is correct because failover groups with automatic failover can achieve RPO of 5 seconds and RTO of 30 seconds. Option B is incorrect because active geo-replication has a higher RPO (typically 5 seconds as well) but requires manual failover, increasing RTO. Option C is incorrect because automated backups have much higher RPO and RTO.

Option D is incorrect because zone-redundant configuration only protects within a region.

Full explanation →

594

Multi-Selecthard

Which THREE of the following are features available in Azure SQL Managed Instance that are not available in Azure SQL Database (single database)?

Select 3 answers

A.Transparent Data Encryption (TDE)

B.Cross-database queries (three- and four-part names)

C.Elastic pools

D.Service Broker

E.SQL Agent jobs

AnswersB, D, E

Managed Instance supports cross-database queries, single DB does not.

Why this answer

Cross-database queries using three- and four-part names (e.g., `[DB1].[schema].[table]`) are supported in Azure SQL Managed Instance because it provides full SQL Server engine compatibility, including the ability to query across databases within the same instance. Azure SQL Database single databases are isolated and do not support cross-database queries, as each database is a separate logical boundary without instance-level context.

Exam trap

The trap here is that candidates often confuse features available in both services (like TDE) with those exclusive to Managed Instance, or they incorrectly assume Elastic pools are a Managed Instance feature when they are actually a single-database scaling option.

Full explanation →

595

MCQhard

Refer to the exhibit. You are deploying an Azure SQL Database with Transparent Data Encryption (TDE) enabled via ARM template. The database will contain highly sensitive data, and your security policy requires that the encryption key be managed by your organization using Azure Key Vault. What additional configuration is needed?

A.Set the 'keyVaultUri', 'keyName', and 'keyVersion' properties to reference the key in Key Vault

B.No additional configuration is needed; the template already enables TDE with customer-managed keys

C.Deploy a separate key rotation policy in the ARM template

D.Enable 'autoRotationEnabled' property

AnswerA

Required to use customer-managed keys for TDE.

Why this answer

Option A is correct because when deploying Azure SQL Database with TDE and customer-managed keys (CMK) via ARM template, you must explicitly specify the 'keyVaultUri', 'keyName', and 'keyVersion' properties under the 'encryptionProtector' resource to link the database to the specific key in Azure Key Vault. Without these properties, the template would only enable TDE with a service-managed key, not the required customer-managed key.

Exam trap

The trap here is that candidates assume enabling TDE in the ARM template automatically uses customer-managed keys, but they overlook the need to explicitly configure the encryption protector with Key Vault properties to switch from service-managed to customer-managed keys.

How to eliminate wrong answers

Option B is wrong because the ARM template shown only enables TDE at the database level (which defaults to service-managed keys), but does not configure the encryption protector to use a customer-managed key from Key Vault; additional properties are required. Option C is wrong because a key rotation policy is not a separate ARM template resource; key rotation is managed via Key Vault's own rotation policy or by updating the key version in the encryption protector, not by a dedicated ARM property. Option D is wrong because 'autoRotationEnabled' is not a valid property for TDE with CMK in Azure SQL Database; automatic key rotation is handled by updating the key version in the encryption protector or by using Key Vault's key rotation, not by an ARM template boolean.

Full explanation →

596

MCQeasy

You need to provision an Azure SQL Database that supports a high-availability SLA of 99.995% and provides automatic failover to a secondary region in case of a regional outage. Which deployment option should you choose?

A.Azure SQL Database in Hyperscale tier

B.Azure SQL Database with zone-redundant configuration

C.Azure SQL Database with active geo-replication and failover group

D.Azure SQL Managed Instance with failover group

AnswerC

This combination provides up to 99.995% SLA and cross-region failover.

Why this answer

Active geo-replication with a failover group provides an SLA of 99.995% by replicating data asynchronously to a secondary region and enabling automatic failover during a regional outage. This is the only option that meets both the high-availability SLA and cross-region disaster recovery requirement.

Exam trap

The trap here is that candidates confuse zone-redundant configuration (which only protects within a region) with cross-region disaster recovery, or assume that any high-SLA tier automatically includes cross-region failover.

How to eliminate wrong answers

Option A is wrong because Hyperscale tier supports rapid scaling and read scale-out but does not provide a 99.995% SLA or automatic cross-region failover; its high-availability is zone-redundant within a single region. Option B is wrong because zone-redundant configuration protects against datacenter failures within a region, not against a full regional outage, and its SLA is 99.995% only for the Business Critical tier, but it lacks cross-region failover. Option D is wrong because Azure SQL Managed Instance with a failover group provides disaster recovery but its SLA is 99.99%, not 99.995%, and it is not a deployment option for Azure SQL Database.

Full explanation →

597

MCQmedium

Your company has a compliance requirement to keep database backups for 10 years. You are using Azure SQL Database. Which backup retention feature should you use?

A.Long-term retention (LTR) policy

B.Geo-redundant storage (GRS)

C.Automated backups

D.Point-in-time restore (PITR)

AnswerA

LTR can retain backups for up to 10 years.

Why this answer

Option D is correct because long-term retention (LTR) policy can retain full backups for up to 10 years. Point-in-time restore (A) is limited to 35 days. Geo-redundant storage (B) is about storage redundancy, not retention period.

Automated backups (C) have a maximum retention of 35 days.

Full explanation →

598

MCQmedium

You are a database administrator for a large retail company. The company uses an Azure SQL Database in the Business Critical tier (8 vCores, 480 GB storage) to run its core transaction processing system. The database has automatic tuning enabled, including FORCE_LAST_GOOD_PLAN and CREATE_INDEX. You notice that the database is experiencing high CPU usage (90% average) during peak hours, and the Query Store shows that a specific query (Query ID 123) has regressed. The automatic tuning feature has forced a plan for this query, but the performance is still poor. You need to resolve the CPU issue and ensure the query runs efficiently. What should you do first?

A.Disable automatic tuning and manually create a plan guide for the query.

B.Use Query Store to compare the forced plan with the previous good plan and update statistics.

C.Modify the query to use query hints like OPTIMIZE FOR UNKNOWN.

D.Scale up the database to 16 vCores to handle the CPU load.

AnswerB

Plan regression often due to statistics; updating may let optimizer pick a better plan.

Why this answer

Option B is correct because reviewing the plan history in Query Store helps identify why the forced plan is not optimal, and perhaps the regression is due to parameter sniffing or outdated statistics. Option A is wrong because disabling automatic tuning may cause further regression. Option C is wrong because scaling up might be unnecessary if plan issue is fixable.

Option D is wrong because modifying data types is a heavy change.

Full explanation →

599

MCQmedium

You have an Azure SQL Database in the General Purpose service tier. The database must be available during a planned patching event that updates the underlying infrastructure. What high availability feature is provided by default?

A.Active geo-replication

B.Zone-redundant availability

C.Built-in high availability with a standby replica

D.Business Critical service tier

AnswerC

General Purpose provides local HA via compute failover.

Why this answer

Option B is correct because the General Purpose service tier uses local redundancy with built-in high availability through a standby replica on a different node. Option A is wrong because zone redundancy is not default for General Purpose. Option C is wrong because geo-replication requires manual setup.

Option D is wrong because Business Critical is a different tier.

Full explanation →

600

MCQmedium

You are a database administrator for a medium-sized e-commerce company. The company runs its online transaction processing (OLTP) workload on an Azure SQL Database in the General Purpose service tier (DTU-based, S3). The database is used for order processing, inventory management, and customer data. Recently, during peak shopping hours (10 AM to 2 PM), users have reported that order entry forms take several seconds to submit, and inventory queries are timing out. Monitoring shows that DTU usage regularly hits 100% during these hours, with high PAGELATCH_IO waits. You need to resolve the performance issue with minimal cost increase. What should you do?

A.Increase the max storage size to 1 TB

B.Increase the service tier to S4 during peak hours

C.Create a read-only replica and offload reporting queries

D.Migrate to the vCore model with Hyperscale service tier

AnswerD

Hyperscale eliminates resource contention and handles high concurrency.

Why this answer

Option B is correct because switching to the vCore model with Hyperscale tier provides better scalability and eliminates resource contention by separating compute and storage, reducing PAGELATCH_IO waits. Option A is wrong because scaling to S4 only adds more DTU but does not address the architecture limitation causing PAGELATCH_IO. Option C is wrong because adding a read-only replica does not help write-heavy OLTP.

Option D is wrong because increasing storage does not affect DTU limits.

Full explanation →

Page 8 of 13

All pages

1 2 3 4 5 6 7 8 9 10 11 12 13

Practice DP-300 by domain

Target a specific domain to shore up weak areas.

Plan and configure a high availability and disaster recovery environment Plan and implement data platform resources Monitor, configure, and optimize database resources Configure and manage automation of tasks Plan and configure high availability and disaster recovery Implement a secure environment

See all domains with question counts →