Microsoft Azure Database Administrator Associate DP-300 DP-300 Questions 226–300 | Page 4/8

226

MCQmedium

Your company is deploying a multi-tenant application using Azure SQL Database. Each tenant gets its own database. You need to manage resources efficiently while ensuring performance isolation between tenants. The number of tenants fluctuates, and you want to minimize cost. What is the best strategy?

A.Deploy each tenant's database as a single database with reserved capacity

B.Use a single Hyperscale database with schema per tenant

C.Use a single Azure SQL Managed Instance with multiple databases

D.Use an elastic pool and add databases as needed

AnswerD

Elastic pools share provisioned eDTUs or vCores across databases, so fluctuating tenant counts consume pooled resources rather than per-database minimums. This satisfies cost minimisation while performance isolation is maintained because each tenant retains its own database.

Why this answer

Elastic pools are designed for multi-tenant SaaS scenarios where each tenant has its own database but usage patterns are unpredictable. They provide performance isolation through per-database resource limits (e.g., min/max DTUs or vCores) while sharing a fixed pool of resources, which minimizes cost by allowing idle databases to borrow from others. This matches the requirement of fluctuating tenant counts and cost efficiency.

Exam trap

The trap here is that candidates confuse 'performance isolation' with 'dedicated resources' and choose single databases (Option A), not realizing that elastic pools provide isolation via per-database resource caps while sharing a common pool for cost efficiency.

How to eliminate wrong answers

Option A is wrong because deploying each tenant's database as a single database with reserved capacity locks in fixed resources per database, leading to over-provisioning and higher costs when tenant activity fluctuates. Option B is wrong because a single Hyperscale database with schema per tenant breaks performance isolation — a noisy tenant can consume shared resources (e.g., log throughput or page server I/O) and impact others, plus Hyperscale is optimized for large, single databases, not multi-tenant isolation. Option C is wrong because Azure SQL Managed Instance is a single-instance deployment with shared resources across all databases; it lacks the per-database resource governance and elastic scaling of an elastic pool, and it is more expensive for many small databases.

Full explanation →

227

Multi-Selectmedium

You are monitoring an Azure SQL Database that uses the vCore purchasing model. You need to set up alerts to notify you when the database approaches its resource limits. Which two metrics should you alert on to detect CPU and I/O pressure? (Choose two.)

Select 2 answers

A.log_write_percent

B.physical_data_read_percent

C.cpu_percent

D.sessions_percent

E.workers_percent

AnswersB, C

physical_data_read_percent measures the percentage of physical data reads relative to the limit. High values indicate I/O pressure, which can slow query performance. Alerting on this metric helps identify when the database is I/O bound. In the vCore model, this metric is relevant for detecting storage throughput issues. Setting an alert allows you to investigate and potentially optimize queries or scale up.

Why this answer

The correct metrics are cpu_percent and physical_data_read_percent. cpu_percent directly measures CPU utilization, and physical_data_read_percent measures the percentage of physical data reads, indicating I/O pressure. These are core metrics in the vCore model for monitoring resource consumption. Other metrics like log_write_percent, sessions_percent, and workers_percent are less directly related to the overall CPU and I/O pressure.

Exam trap

The trap here is confusing log_write_percent with overall I/O pressure, when it only measures log write throughput, not data reads.

Full explanation →

228

MCQeasy

You need to encrypt sensitive columns in an Azure SQL Database table so that data is encrypted at rest and in transit between the application and database. Which feature should you use?

A.Row-Level Security

B.Transparent Data Encryption (TDE)

C.Dynamic Data Masking

D.Always Encrypted

AnswerD

Always Encrypted keeps data encrypted at rest and in transit, with keys held outside Azure SQL Database. The client driver encrypts and decrypts values, so ciphertext never leaves the database engine unencrypted. This satisfies the stem's requirement for protection both at rest and between the application and database, unlike Transparent Data Encryption, which only covers data at rest.

Why this answer

Always Encrypted is the correct choice because it encrypts sensitive data both at rest in the database and in transit between the application and the database. It ensures that encryption keys are never revealed to the database engine, so data remains encrypted throughout the entire data path, including during query execution. This meets the requirement for encryption at rest and in transit.

Exam trap

The trap here is that candidates often confuse Transparent Data Encryption (TDE) as covering both at-rest and in-transit encryption, but TDE only encrypts data at rest on disk, not during network transmission or while in memory.

How to eliminate wrong answers

Option A is wrong because Row-Level Security (RLS) controls access to rows based on user identity or context, but it does not encrypt data at rest or in transit. Option B is wrong because Transparent Data Encryption (TDE) encrypts the database files at rest but does not protect data in transit between the application and the database; it also does not prevent the database engine from seeing plaintext data during query processing. Option C is wrong because Dynamic Data Masking obfuscates data in query results for unauthorized users but does not encrypt the underlying data at rest or in transit, and the database engine still processes plaintext data.

Full explanation →

229

MCQhard

You run the query in the exhibit on an Azure SQL Database. The result shows high wait_time_ms for PAGEIOLATCH_SH waits. What does this indicate?

A.I/O subsystem bottleneck for read operations

B.CPU bottleneck

C.Blocking between concurrent transactions

D.Memory pressure

AnswerA

PAGEIOLATCH_SH waits occur when a thread waits for a data page to be read from storage into the buffer pool, so sustained high wait_time_ms points to slow read I/O rather than CPU, locking or memory pressure. This satisfies the stem's read-operation bottleneck constraint.

Why this answer

PAGEIOLATCH_SH waits indicate that a query is waiting for a data page to be read from disk into the buffer pool, which is an I/O operation. High wait_time_ms for this wait type typically points to an I/O subsystem bottleneck for read operations, making option A correct. Option B (CPU bottleneck) is incorrect because PAGEIOLATCH_SH is related to I/O, not CPU.

Option C (blocking) is incorrect because blocking is associated with LOCK waits, not PAGEIOLATCH_SH. Option D (memory pressure) is incorrect; while memory pressure can increase physical I/O, the wait type itself specifically indicates I/O latency for reading pages from disk.

Full explanation →

230

MCQeasy

You have a new Azure SQL Database. You need to ensure that all connections use TLS 1.2 or higher. What should you configure?

A.Set the 'minimal TLS version' to 1.2 in the server's properties in the Azure portal.

B.Set the 'minimal TLS version' to 1.2 in the database's properties.

C.Add a firewall rule to deny connections using TLS 1.0 or 1.1.

D.Enable the 'Force encryption' option in the connection string and require TLS 1.2.

AnswerA

Configuring the server-level minimal TLS version to 1.2 rejects any client handshake negotiating TLS 1.0 or 1.1, enforcing the requirement across every database on that logical server. This is a server property, so it applies to all connections without per-database changes.

Why this answer

To enforce TLS 1.2 or higher for all connections to an Azure SQL Database, you must configure the 'minimal TLS version' setting at the server level in the Azure portal. This setting applies to all databases hosted on that logical server, ensuring that any client attempting to connect with a TLS version lower than 1.2 is rejected. The server-level property directly controls the TLS protocol version accepted during the SSL/TLS handshake, overriding any client-side or database-level settings.

Exam trap

The trap here is that candidates often confuse the 'minimal TLS version' setting with a database-level property or think that firewall rules or connection string options can enforce TLS version restrictions, but Azure SQL Database only exposes this control at the server level.

How to eliminate wrong answers

Option B is wrong because the 'minimal TLS version' setting is a server-level property, not a database-level property; Azure SQL Database does not expose a per-database TLS version configuration. Option C is wrong because firewall rules in Azure SQL Database control IP-based access, not TLS protocol versions; they cannot inspect or deny connections based on the TLS version used. Option D is wrong because 'Force encryption' in the connection string ensures encryption is used but does not enforce a specific TLS version; the client and server may negotiate a lower TLS version (e.g., 1.0 or 1.1) even with encryption enabled.

Full explanation →

231

MCQmedium

You have an Azure SQL Database with active geo-replication. You need to monitor the replication lag to ensure the RPO is met. Which metric should you monitor?

A.DTU consumption

B.Replication lag

C.Data IO percentage

D.Deadlocks

AnswerB

Replication lag measures the delay between the primary and secondary database in seconds, directly quantifying potential data loss against the RPO. Monitoring it confirms whether active geo-replication keeps the secondary close enough to meet the stated recovery point objective.

Why this answer

Active geo-replication in Azure SQL Database continuously ships transaction log records from the primary to the secondary. The 'Replication lag' metric specifically measures the delay between the primary and secondary, which directly maps to your Recovery Point Objective (RPO). Monitoring this metric lets you verify that the secondary is keeping up and that you won't lose more than the acceptable amount of data in a failover.

Exam trap

DP-300 often tests the confusion between performance metrics (DTU, IO) and replication-specific metrics — candidates pick DTU because it sounds like a health indicator, but RPO is about data delay, not resource usage.

How to eliminate wrong answers

Option A is wrong because DTU consumption measures compute resource usage on the primary, not the data synchronization delay to the secondary — high DTU does not necessarily mean high replication lag. Option C is wrong because Data IO percentage reflects storage I/O utilization, which is unrelated to the geo-replication RPO. Option D is wrong because deadlocks indicate concurrency conflicts in the workload, not replication latency.

Full explanation →

232

MCQmedium

You are the database administrator for a company that uses Azure SQL Database. The security team requires that all data at rest be encrypted with a customer-managed key (CMK) stored in Azure Key Vault, rather than the default service-managed key. You need to implement this requirement with the least administrative overhead. What should you do?

A.Create a database master key (DMK) in each user database and encrypt it with a password.

B.Enable Transparent Data Encryption (TDE) with a customer-managed key by configuring the Azure SQL logical server to use a key from Azure Key Vault.

C.Enable Always Encrypted with a column master key stored in Azure Key Vault for all columns in every table.

D.Configure Azure Storage Service Encryption (SSE) with a customer-managed key on the storage account that hosts the database files.

AnswerB

Configuring TDE with a customer-managed key at the logical server level enables Bring Your Own Key (BYOK) for all databases on that server. The server's managed identity accesses the key in Azure Key Vault, so no per-database key management is needed, meeting the requirement with minimal overhead.

Why this answer

TDE with a customer-managed key at the logical server level is the correct approach because it applies to all databases on the server and uses Azure Key Vault for key storage, satisfying the security team's requirement for customer-managed keys. The other options either do not provide full database encryption, require per-column configuration, or apply to services outside Azure SQL Database.

Exam trap

The trap here is assuming that Always Encrypted or a database master key provides data-at-rest encryption for the entire database, when only TDE with a customer-managed key does so at the server level.

Full explanation →

233

MCQhard

You are managing an Azure SQL Database that uses Intelligent Insights. You receive an alert that there is a performance issue with a specific query. You need to analyze the root cause. What should you use?

A.Intelligent Insights report

B.Automatic Tuning recommendations

C.Azure Monitor metrics for the database

D.Query Store to review query execution plans and wait statistics

AnswerD

Query Store persists execution plans, runtime statistics and wait categories per query, letting you identify the regressed plan and the dominant wait type causing the slowdown. It provides the historical plan comparison that Intelligent Insights alerts alone do not expose.

Why this answer

Query Store is the correct tool because it captures historical execution plans, runtime statistics, and wait statistics for individual queries, allowing you to pinpoint the root cause of a performance regression. Intelligent Insights provides high-level diagnostics but not the granular per-query plan and wait data needed for deep analysis of a specific query issue.

Exam trap

The trap here is that candidates confuse Intelligent Insights' automated diagnostics with the granular, query-level historical data that Query Store provides, assuming the alert's source (Intelligent Insights) is also the tool for deep manual investigation.

How to eliminate wrong answers

Option A is wrong because Intelligent Insights provides automated root cause analysis and recommendations at the database level, but it does not expose detailed per-query execution plans or wait statistics for manual investigation. Option B is wrong because Automatic Tuning focuses on automatically applying index and plan regression fixes, not on providing a historical record of query execution plans and waits for root cause analysis. Option C is wrong because Azure Monitor metrics (e.g., DTU/CPU usage, IOPS) show aggregate resource consumption, not per-query execution plans or wait statistics, so they cannot isolate the specific query's performance issue.

Full explanation →

234

Multi-Selecthard

Which THREE metrics should you monitor to proactively detect potential performance issues in an Azure SQL Database?

Select 3 answers

A.Log IO percentage (sys.dm_db_resource_stats)

B.Log backup frequency

C.Database size and growth rate

D.Wait statistics (sys.dm_os_wait_stats)

E.Query Store for query performance regressions

AnswersA, D, E

Log IO percentage from sys.dm_db_resource_stats exposes the ratio of log write throughput to provisioned log IOPS, revealing transaction-log write saturation before commit latency degrades. This directly satisfies the stem's proactive detection requirement, since sustained high log IO percentage signals the log subsystem is throttling writes and performance issues are imminent.

Why this answer

Option A (Log IO percentage via sys.dm_db_resource_stats) is correct because this DMV reports resource utilization such as log write percentage against the service-tier limits, so sustained high log IO percentage signals throttling and impending performance degradation. Option D (Wait statistics via sys.dm_os_wait_stats) is correct because aggregating wait types reveals where sessions are blocked or stalled (for example PAGEIOLATCH, CXPACKET, or WRITELOG), which is the standard method for diagnosing the root cause of slow performance. Option E (Query Store for query performance regressions) is correct because Query Store persists query plans, runtime statistics, and execution history, letting you proactively detect plan regressions and parameter-sniffing issues before users report them.

Option B (Log backup frequency) is not a performance metric for Azure SQL Database, since log backups are managed automatically by the platform and their frequency is not exposed as a tunable performance indicator. Option C (Database size and growth rate) is a capacity-planning metric rather than a real-time performance signal, so it does not proactively reveal latency, blocking, or resource-throttling issues.

Exam trap

DP-300 often tests the difference between performance-monitoring DMVs and operational/capacity metrics — candidates pick database size or backup frequency because they sound like 'monitoring,' but only the three DMV/Query Store options expose runtime performance signals.

Full explanation →

235

MCQmedium

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database. The template configures backup retention. What is the effect of this configuration?

A.Full backups are taken every 12 hours and retained for 7 days.

B.Long-term retention (LTR) is set to 7 days.

C.Point-in-time restore (PITR) backups are retained for 7 days, and differential backups occur every 12 hours.

D.Transaction log backups are taken every 12 hours.

AnswerC

Configuring the retention period to 7 days sets the PITR window, so full backups are kept for seven days and differential backups run every 12 hours. This satisfies the template's backup retention constraint rather than long-term retention, which requires a separate policy.

Why this answer

The ARM template configures the backup retention settings for Azure SQL Database. By default, Azure SQL Database automatically performs full backups every week, differential backups every 12 hours, and transaction log backups every 5–10 minutes. The configuration shown sets the point-in-time restore (PITR) retention period to 7 days, meaning you can restore the database to any point within the last 7 days.

Differential backups occur every 12 hours to support efficient PITR, but the retention setting directly controls how far back you can perform a point-in-time restore.

Exam trap

The trap here is that candidates confuse the PITR retention period with the frequency of backups, or assume that the retention setting controls the backup schedule (e.g., thinking full backups occur every 12 hours), when in fact it only controls how long backups are kept, not how often they are taken.

How to eliminate wrong answers

Option A is wrong because full backups in Azure SQL Database are taken once per week, not every 12 hours, and the retention setting shown does not change the full backup frequency. Option B is wrong because long-term retention (LTR) is a separate feature that retains full backups for up to 10 years, configured via a different policy, not the 7-day PITR retention setting shown. Option D is wrong because transaction log backups are taken every 5–10 minutes, not every 12 hours, and their frequency is not configurable via this retention setting.

Full explanation →

236

MCQmedium

Your company runs a critical application on Azure SQL Managed Instance in the North Europe region. The application requires an RPO of 5 minutes and an RTO of 2 hours during a regional disaster. The current setup uses a single instance with geo-redundant backup storage (RA-GRS). During a disaster recovery planning session, you discover that geo-restore from RA-GRS backups takes approximately 4 hours to complete, which exceeds the RTO. You need to modify the disaster recovery solution to meet the RTO without exceeding the budget significantly. The solution must minimize administrative overhead. What should you do?

A.Change backup storage to locally-redundant (LRS) and rely on point-in-time restore.

B.Enable zone redundancy on the primary instance.

C.Deploy a secondary instance in a different region and configure a failover group.

D.Scale the instance to a higher service tier to improve restore performance.

AnswerC

A failover group with a secondary instance in another region provides asynchronous replication with an RPO of about 5 seconds and rapid failover, meeting the 2-hour RTO. It minimises administrative overhead compared with manual geo-restore, which took 4 hours.

Why this answer

A failover group in Azure SQL Managed Instance provides a secondary instance in a different region with automatic or manual failover, and it replicates data asynchronously with an RPO typically under 5 seconds, well within the 5-minute requirement. Failover groups also provide read-write and read-only listener endpoints, minimizing application changes and administrative overhead. This meets the RTO of 2 hours because failover can be triggered quickly, and the secondary is already provisioned and synchronized.

Exam trap

DP-300 often tests the difference between geo-restore (slow, high RTO) and failover groups (fast, low RTO), so candidates incorrectly assume RA-GRS backups alone can meet aggressive RTO requirements.

How to eliminate wrong answers

Option A is wrong because LRS backups are stored only in the primary region and do not support geo-restore; point-in-time restore from LRS cannot recover from a regional disaster. Option B is wrong because zone redundancy protects against datacenter-level failures within a single region, not a full regional disaster, so it does not meet the cross-region DR requirement. Option D is wrong because scaling to a higher service tier may improve performance but does not change the geo-restore time significantly; geo-restore performance is primarily limited by backup restore throughput and is not directly tied to the service tier in a way that guarantees the 2-hour RTO.

Full explanation →

237

MCQmedium

You manage an Azure SQL Database named OrderDB in the Business Critical service tier. A compliance requirement mandates that the database must remain available even if an entire Azure availability zone fails within the primary region. You need to configure the database to meet this requirement with the least administrative effort. What should you do?

A.Enable zone redundancy for the database.

B.Create a failover group with an automatic failover policy.

C.Increase the database compute size to the maximum vCore limit.

D.Configure active geo-replication to a secondary region.

AnswerA

Zone redundancy in the Business Critical tier automatically provisions replicas across multiple availability zones, so the database remains online if one zone fails. It requires no application changes and minimal configuration, fulfilling the compliance requirement with least effort.

Why this answer

Zone redundancy in the Business Critical tier replicates the database across multiple availability zones, ensuring automatic failover and continuous availability during a zone outage. It is the simplest configuration change that meets the requirement without application modifications or manual intervention.

Exam trap

The trap here is confusing cross-region disaster recovery features like failover groups with intra-region high availability mechanisms such as zone redundancy.

Full explanation →

238

MCQmedium

A company uses Azure SQL Database and wants to automatically send an email notification when an index fragmentation exceeds 30% for any database. Which solution should they implement?

A.Create an Azure Monitor alert based on fragmentation

B.Use Elastic Database Jobs to check fragmentation and send email

C.Configure SQL Agent job to send email

D.Use Azure Automation runbook to query sys.dm_db_index_physical_stats and send email

AnswerD

An Azure Automation runbook can query sys.dm_db_index_physical_stats on a schedule, evaluate fragmentation against the 30% threshold, and dispatch email via Office 365 or SendGrid. Azure SQL Database lacks native alerting on DMV output, so automation is required.

Why this answer

Azure SQL Database does not expose SQL Server Agent, and index fragmentation is not a native Azure Monitor metric, so the correct approach is an Azure Automation runbook that connects to the database, queries sys.dm_db_index_physical_stats, evaluates fragmentation, and sends email via SendGrid, Office 365, or SMTP. This is the standard pattern for custom T-SQL-based monitoring and alerting in Azure SQL Database.

Exam trap

DP-300 often tests whether candidates know that SQL Agent is unavailable in Azure SQL Database (but available in Managed Instance) — the trap is picking the SQL Agent job option because it is the familiar on-premises pattern.

How to eliminate wrong answers

Option A is wrong because Azure Monitor does not expose index fragmentation as a built-in metric — you cannot create an alert directly on fragmentation without first emitting a custom metric. Option B is wrong because Elastic Database Jobs are designed for cross-database administrative tasks (schema changes, index maintenance) but do not natively send email notifications based on query results — you would still need an external mechanism for the email. Option C is wrong because SQL Agent is not available in Azure SQL Database (it is available in Azure SQL Managed Instance and SQL Server on VMs), so you cannot create a SQL Agent job to send email.

Full explanation →

239

Drag & Dropmedium

Drag and drop the steps to restore an Azure SQL Database to a point in time in the correct order.

Drag or tap steps into the slots.

Steps

Order

1Step 1

2Step 2

3Step 3

4Step 4

Why this order

The restore process starts by selecting the database, then choosing the restore type, specifying the point in time, naming the new database, and finally creating it.

Full explanation →

240

MCQeasy

Refer to the exhibit. You executed the Azure CLI command to list databases. You need to resume db3 to make it available for connections. Which command should you use?

A.az sql db restart --resource-group rg1 --server server1 --name db3

B.az sql db resume --resource-group rg1 --server server1 --name db3

C.az sql db start --resource-group rg1 --server server1 --name db3

D.az sql db update --resource-group rg1 --server server1 --name db3 --set status=Online

AnswerB

db3 is a paused Azure SQL database, so resuming it requires the dedicated resume subcommand with the resource group, server and database names. The command az sql db resume --resource-group rg1 --server server1 --name db3 supplies exactly those parameters, restoring db3 to an available state.

Why this answer

`az sql db resume` is the command to resume a paused database. Option A is wrong because `az sql db restart` restarts an online database but does not resume a paused one. Option C is wrong because `az sql db start` is not a valid command for Azure SQL Database.

Option D is wrong because `az sql db update` can modify properties but cannot resume a paused database; resuming requires a dedicated command.

Full explanation →

241

MCQhard

Refer to the exhibit. A PowerShell script is used to move an Azure SQL Database into an elastic pool. The script runs without error. Which condition must be true before the script runs?

A.The elastic pool must be in a different server

B.The database must be in the same server as the elastic pool

C.The database must be in the Basic tier

D.The database size must be less than 10 GB

AnswerB

Elastic pools group databases sharing resources within a single logical server, so a database must already reside on that same server before being added. Moving a database across servers requires a separate operation, such as export/import or geo-restore, which the script does not perform. This satisfies the same-server constraint.

Why this answer

Moving a database into an elastic pool requires the database to be in the same server as the pool. Option A is wrong because the elastic pool must be in the same server as the database, not a different server. Option C is wrong because the database can be any service tier (Basic, Standard, Premium, etc.) as long as it fits within the pool's resource limits.

Option D is wrong because the database size can be any size up to the pool's max size limit, which is not necessarily less than 10 GB.

Full explanation →

242

MCQmedium

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database backup policy. The database is used for a reporting workload that is updated daily. The compliance team requires that point-in-time restore (PITR) be available for the past 30 days. What action should you take?

A.Change the retentionDays property to 30.

B.Change the diffBackupIntervalInHours to 24.

C.No action is needed; the current policy meets the requirement.

D.Add a long-term retention policy with a weekly retention of 30 days.

AnswerA

retentionDays controls PITR retention; setting to 30 meets the requirement.

Why this answer

The retentionDays property in the ARM template controls the point-in-time restore (PITR) retention period. Currently set to 14 days, it must be changed to 30 days to meet the compliance requirement. Option B is incorrect because diffBackupIntervalInHours specifies the interval for differential backups, not retention.

Option C is incorrect because 14 days does not satisfy the 30-day requirement. Option D is incorrect because long-term retention (LTR) is for archival purposes beyond PITR and does not replace the PITR retention setting.

Full explanation →

243

MCQeasy

Your organization has a policy that all Azure SQL Database connections must use Microsoft Entra authentication. You need to ensure that application developers cannot accidentally use SQL authentication. What should you do?

A.Configure server-level firewall rules to block all IP addresses except Azure services.

B.Disable SQL authentication for all contained database users.

C.Create a database-level trigger to reject connections using SQL authentication.

D.Enable 'Azure AD-only authentication' on the logical server.

AnswerD

Enabling 'Azure AD-only authentication' on the logical server disables all SQL authentication methods, including the server-level admin login. This directly enforces the policy that all connections must use Microsoft Entra ID, as any attempt to connect with a SQL username and password is rejected at the server level. This satisfies the constraint of preventing accidental SQL authentication by developers.

Why this answer

Enabling 'Azure AD-only authentication' on the logical server explicitly blocks all SQL authentication connections, including those from contained database users. This setting enforces that only Microsoft Entra ID (formerly Azure AD) principals can authenticate, directly aligning with the policy to prevent accidental use of SQL authentication.

Exam trap

The trap here is that candidates may think disabling SQL authentication for contained users (Option B) is sufficient, but they miss that the server-level authentication policy must be enforced to block all SQL authentication attempts, including those from server-level logins or newly created contained users.

How to eliminate wrong answers

Option A is wrong because server-level firewall rules control network access, not authentication methods; they cannot distinguish between SQL and Entra ID authentication. Option B is wrong because disabling SQL authentication for contained database users does not prevent SQL authentication at the server level; a contained user could still be created with SQL authentication if the server allows it. Option C is wrong because a database-level trigger cannot intercept or reject connections; triggers fire after a connection is established, so they cannot block the initial authentication attempt.

Full explanation →

244

MCQmedium

You are deploying SQL Server on an Azure Virtual Machine. You need to configure a high availability solution that provides automatic failover and does not require a shared storage solution. The solution must support multiple databases and allow for readable secondary replicas. What should you implement?

A.Configure a failover cluster instance (FCI) with a premium file share.

B.Use Azure Site Recovery to replicate the VM to another region.

C.Configure log shipping to a secondary SQL Server instance.

D.Implement an Always On availability group with multiple replicas.

AnswerD

Always On availability groups support automatic failover without shared storage and allow for readable secondary replicas. They can protect multiple databases and are the recommended high availability solution for SQL Server on Azure VMs when shared storage is not desired. This meets all the stated requirements.

Why this answer

Always On availability groups provide automatic failover without shared storage and support readable secondary replicas. They can protect multiple databases and are the standard high availability solution for SQL Server on Azure VMs. Failover cluster instances require shared storage, while log shipping and Azure Site Recovery do not provide automatic failover or readable secondaries.

Exam trap

The trap here is confusing disaster recovery solutions like Azure Site Recovery with high availability solutions that provide automatic failover and readable secondaries.

Full explanation →

245

Multi-Selectmedium

You need to automate the deployment of schema changes to an Azure SQL Database using Azure DevOps. Which THREE components are required? (Choose three.)

Select 3 answers

A.Build pipeline

B.Release pipeline

C.Elastic job agent

D.Azure Automation runbook

E.Variable group

AnswersA, B, E

A build pipeline compiles and packages the database project into a DACPAC artefact, which the release stage later deploys. It is a required component because it produces the deployable schema artefact consumed by subsequent pipeline stages.

Why this answer

Option A (Build pipeline) is correct because a build pipeline is needed to compile the database project (e.g., a SQL Server Database Project / DACPAC) and produce the deployment artifact that will be published to the Azure SQL Database. Option B (Release pipeline) is correct because the release pipeline consumes that artifact and executes the deployment task (such as Azure SQL Database deployment or SqlPackage) against the target Azure SQL Database. Option E (Variable group) is correct because it stores reusable, environment-specific values (server name, database name, credentials, connection strings) that the pipelines reference, keeping secrets and configuration out of the pipeline definition.

Option C (Elastic job agent) is not required because it is used to run T-SQL jobs across a set of databases in a pool, not to deploy schema changes through Azure DevOps. Option D (Azure Automation runbook) is not required because runbooks automate operational tasks in Azure, but schema deployment in this scenario is handled by the Azure DevOps build and release pipelines.

Exam trap

DP-300 often tests the confusion between Azure DevOps pipeline components and other Azure services like Elastic Job Agent or Automation runbooks, which are not part of the CI/CD pipeline itself.

Full explanation →

246

Multi-Selecthard

You are monitoring an Azure SQL Database that uses the vCore purchasing model. You need to identify the top resource-consuming queries. You decide to use Query Store. Which two actions should you perform? (Choose two.)

Select 2 answers

A.Configure the MAX_STORAGE_SIZE_MB option for Query Store.

B.Enable the LEGACY_CARDINALITY_ESTIMATION database scoped configuration.

C.Create a custom Extended Events session to capture query metrics.

D.Enable Query Store on the database.

E.Use the Top Resource Consuming Queries report in the Azure portal or SQL Server Management Studio.

AnswersD, E

Query Store must be enabled to capture query execution statistics. By default, it may be off or in read-only mode. Enabling it ensures that query plans and runtime statistics are collected, which is necessary to identify top resource-consuming queries. This is a prerequisite for using Query Store for performance analysis.

Why this answer

To use Query Store for identifying top resource-consuming queries, you must first enable Query Store on the database. Then, you can use the built-in Top Resource Consuming Queries report, which aggregates query statistics and ranks them by resource usage. The other options are either not required or not the intended use of Query Store for this purpose.

Exam trap

The trap here is assuming that additional configuration like MAX_STORAGE_SIZE_MB or Extended Events is necessary, when simply enabling Query Store and using its reports is sufficient.

Full explanation →

247

MCQeasy

You are managing an Azure SQL Database that has automatic tuning enabled. You notice that a recent index creation recommended by automatic tuning has caused a performance regression for some queries. You need to revert the change and prevent automatic tuning from applying similar recommendations in the future. What should you do?

A.Disable automatic tuning on the database.

B.Use the automatic tuning option to revert the last change and then disable the CREATE INDEX tuning option.

C.Manually drop the index and create a database-level DDL trigger to block index creation.

D.Set the automatic tuning option to inherit from the server and disable it at the server level.

AnswerB

Automatic tuning provides a history of applied recommendations and allows you to revert a specific change. After reverting the index creation, you can disable the CREATE INDEX tuning option to prevent automatic tuning from creating indexes in the future. This targeted approach addresses both the immediate issue and the future prevention without disabling other tuning features.

Why this answer

Automatic tuning in Azure SQL Database allows you to revert individual tuning actions. To address the regression, you should revert the index creation and then disable the CREATE INDEX tuning option to prevent future automatic index creation. Disabling all automatic tuning or using manual workarounds like DDL triggers are less precise and can have unintended side effects.

Exam trap

The trap here is thinking that disabling automatic tuning entirely is necessary, when you can revert the specific change and disable only the index creation option.

Full explanation →

248

MCQeasy

You manage an Azure SQL Database server that hosts multiple databases. The security policy requires that all connections to the server use a minimum TLS version of 1.2 and that the setting applies to all databases on the server. What should you configure?

A.Set the Minimum TLS version to 1.2 in the server's Transact-SQL firewall settings.

B.Set the Minimum TLS version to 1.2 in the server's networking properties in the Azure portal.

C.Enable 'Enforce SSL connection' on the server and set the client driver to TLS 1.2.

D.Configure the database's connection policy to 'Proxy' and enable TLS 1.2 in the connection string.

AnswerB

The minimum TLS version is a server-level networking property in Azure SQL Database. Setting it to 1.2 in the Azure portal (or via PowerShell/CLI) enforces that all connections to every database on that server use at least TLS 1.2, satisfying the security policy with a single configuration.

Why this answer

The minimum TLS version is a server-level networking setting in Azure SQL Database. Configuring it to 1.2 in the server's networking properties ensures that all connections to all databases on that server must use TLS 1.2 or higher. Firewall rules, connection policies, and client-side settings do not enforce the minimum TLS version server-wide.

Exam trap

The trap here is confusing the 'Enforce SSL connection' setting with the 'Minimum TLS version' setting; the former only requires encryption, while the latter actually enforces a specific TLS version.

Full explanation →

249

MCQhard

You are responsible for securing an Azure SQL Database. You need to implement data masking for a column that contains credit card numbers, ensuring that users with the db_datareader role see a masked version. However, users with the db_owner role should see the unmasked data. What should you configure?

A.Apply Dynamic Data Masking (DDM) to the credit card column.

B.Implement Row-Level Security (RLS) to filter rows based on user role.

C.Implement Always Encrypted with deterministic encryption.

D.Enable Transparent Data Encryption (TDE).

AnswerA

Dynamic Data Masking applies masking at query time based on the caller's permissions, so db_datareader users receive masked credit card values while db_owner users, who are excluded from masking, still see the full unmasked data. No data is altered at rest.

Why this answer

Dynamic Data Masking (DDM) is the correct choice because it allows you to obfuscate sensitive data in query results for non-privileged users (like db_datareader) while permitting users with elevated permissions (like db_owner) to see the unmasked data. DDM is applied at the column level and does not modify the underlying data; it simply masks the output based on the user's permissions. The db_owner role is exempt from masking by default, meeting the requirement exactly.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking with Always Encrypted, thinking both provide role-based visibility, but Always Encrypted requires key management and does not support partial masking or role-based exemption without separate keys.

How to eliminate wrong answers

Option B is wrong because Row-Level Security (RLS) controls which rows a user can access based on a predicate function, not which columns are masked; it cannot hide the credit card number within a row. Option C is wrong because Always Encrypted with deterministic encryption encrypts data at rest and in transit, but it does not allow role-based masking—users with the encryption key see plaintext, while others see ciphertext, not a masked format. Option D is wrong because Transparent Data Encryption (TDE) encrypts the entire database at rest but does not provide any per-column or per-user masking; it protects against unauthorized access to the physical files, not against authorized database users.

Full explanation →

250

MCQhard

Your company uses Azure SQL Database and needs to protect sensitive columns (e.g., credit card numbers) from being accessed by unauthorized users. You implement Always Encrypted. However, some queries that perform pattern matching on the encrypted column are failing because the column cannot be searched. What should you do to allow pattern matching while maintaining security?

A.Enable Always Encrypted with secure enclaves and use a column master key that supports enclave computations.

B.Implement row-level security (RLS) to filter rows based on user identity.

C.Change the encryption type from randomized to deterministic encryption.

D.Use Dynamic Data Masking (DDM) to mask the column for unauthorized users instead of encryption.

AnswerA

Deterministic encryption only permits equality comparisons, so pattern matching fails. Secure enclaves extend Always Encrypted by performing computations inside a hardware-protected enclave, enabling `LIKE`, range and sorting operations on encrypted columns. Configuring an enclave-enabled column master key satisfies the stem's requirement to search encrypted data while keeping it protected from unauthorised users.

Why this answer

Always Encrypted with secure enclaves allows computations, including pattern matching (LIKE, equality, comparisons), on encrypted columns by using a trusted execution environment (e.g., Intel SGX). The column master key must support enclave computations (enclave-enabled key) to permit the SQL Server engine to offload operations to the enclave. This preserves encryption at rest and in transit while enabling rich query patterns.

Exam trap

The trap here is that candidates often confuse deterministic encryption (which enables equality) with the ability to perform pattern matching, or they mistakenly think Dynamic Data Masking or row-level security can substitute for encrypted search capabilities.

How to eliminate wrong answers

Option B is wrong because row-level security (RLS) controls which rows a user can see based on predicates, but it does not enable pattern matching on encrypted columns; the column remains encrypted and unsearchable. Option C is wrong because changing from randomized to deterministic encryption only enables equality searches (e.g., WHERE column = 'value'), not pattern matching (LIKE '%pattern%'), and deterministic encryption is more vulnerable to frequency analysis attacks. Option D is wrong because Dynamic Data Masking (DDM) only obfuscates data at query results for unauthorized users; it does not encrypt the column, so sensitive data is still stored in plaintext and accessible to privileged users, failing the core security requirement.

Full explanation →

251

MCQmedium

You administer an Azure SQL Database named HRDB. The security team requires that all data at rest be encrypted with a customer-managed key stored in Azure Key Vault, and that the key be automatically rotated every 90 days. You create the Key Vault and grant the logical server's managed identity the necessary permissions. What should you do next to meet the requirement?

A.Configure TDE on the logical server to use a customer-managed key from Azure Key Vault and enable auto-rotation.

B.Enable Transparent Data Encryption (TDE) with a service-managed key on the logical server.

C.Enable Dynamic Data Masking on all sensitive columns.

D.Enable Always Encrypted on all columns containing sensitive data.

AnswerA

Configuring TDE with a customer-managed key (BYOK) stored in Azure Key Vault meets the requirement for customer control. Azure SQL supports automatic key rotation when the key is set to auto-rotate in Key Vault, so the 90-day rotation requirement is satisfied without manual intervention.

Why this answer

The requirement is for encryption at rest using a customer-managed key in Azure Key Vault with automatic rotation. TDE with a customer-managed key (BYOK) is the Azure SQL feature that provides this. Configuring TDE to use a Key Vault key and enabling auto-rotation ensures the key is rotated every 90 days without manual intervention, satisfying the security team's policy.

Exam trap

The trap here is confusing Always Encrypted with TDE, assuming any encryption feature satisfies the customer-managed key requirement.

Full explanation →

252

MCQhard

You are administering an Azure SQL Managed Instance that hosts a busy OLTP database. Users report that during peak hours, queries that typically run in milliseconds now take seconds. You suspect that the issue is related to tempdb contention. Which action should you take to resolve the tempdb contention?

A.Move tempdb to a faster storage tier.

B.Set the database compatibility level to the latest version.

C.Increase the number of tempdb data files.

D.Enable Read Committed Snapshot Isolation (RCSI).

AnswerC

Tempdb contention often arises from allocation bottlenecks when many concurrent connections create and drop temporary objects. Adding more tempdb data files distributes the allocation load across multiple files, reducing contention on allocation pages. This is the recommended approach for resolving tempdb contention in Azure SQL Managed Instance.

Why this answer

Tempdb contention in Azure SQL Managed Instance is commonly caused by allocation bottlenecks when many sessions create temporary objects. Adding more tempdb data files spreads the allocation metadata across multiple files, reducing contention. Other actions like moving to faster storage or changing isolation levels do not address the root cause of allocation contention.

Exam trap

The trap here is assuming that tempdb contention is solely an I/O problem and moving to faster storage will fix it, when the real issue is often allocation contention that requires adding files.

Full explanation →

253

Multi-Selecthard

Which THREE are best practices for securing Azure SQL Database? (Choose three.)

Select 3 answers

A.Use Microsoft Entra ID authentication instead of SQL authentication.

B.Use Azure SQL Database firewall rules to restrict access to known IP addresses.

C.Enable Transparent Data Encryption (TDE) for all databases.

D.Enable public network access to allow flexible connectivity.

E.Grant db_owner role to developers for ease of management.

AnswersA, B, C

Microsoft Entra ID authentication eliminates stored SQL logins and passwords, satisfying the stem's requirement to secure Azure SQL Database. It enforces centralised identity governance, conditional access and multifactor authentication, while supporting managed identities for application connections. This removes credential sprawl and weak password reuse, which SQL authentication cannot prevent.

Why this answer

Option A is correct because Microsoft Entra ID authentication centralizes identity management, supports MFA and conditional access, and eliminates the risk of weak or shared SQL logins and passwords. Option B is correct because Azure SQL Database firewall rules (server-level and database-level) limit inbound connections to specific, known IP address ranges, reducing the exposed attack surface from the public internet. Option C is correct because Transparent Data Encryption (TDE) encrypts data at rest, including backups and log files, protecting against unauthorized access to physical media or backup theft.

Option D is not a best practice because enabling public network access broadens exposure; private endpoints or restricted firewall rules are preferred. Option E is not a best practice because granting db_owner to developers violates least privilege and gives excessive control over the database.

Exam trap

The trap here is that candidates often confuse 'public network access' with 'flexible connectivity' and overlook that private endpoints or Azure service endpoints are the secure alternatives, while also mistakenly thinking that granting db_owner simplifies management without considering the security implications of over-privileged accounts.

Full explanation →

254

Multi-Selectmedium

Which THREE components are required to configure an auto-failover group for Azure SQL Database? (Choose three.)

Select 3 answers

A.Active geo-replication

B.A failover group name

C.Secondary logical server in a different region

D.Primary logical server

E.An availability group listener

AnswersB, C, D

A failover group name is mandatory because it identifies the group and forms the read-write listener endpoint (server.database.windows.net). Without this unique name, Azure cannot create the group or route connections, so it is one of the three required components alongside the primary and secondary servers.

Why this answer

A failover group is a named resource, so option B (a failover group name) is required to create and reference it. Option C (a secondary logical server in a different region) is required because the failover group replicates databases to a partner server, and placing it in a different Azure region provides the geo-redundancy that failover groups are designed for. Option D (the primary logical server) is required because the failover group is defined between a primary and secondary server, with the primary hosting the read-write databases.

Option A is not required because failover groups automatically manage the geo-replication relationship; you do not manually configure active geo-replication as a prerequisite. Option E is not applicable because an availability group listener is an Always On availability group concept for SQL Server, not an Azure SQL Database auto-failover group component.

Exam trap

DP-300 often tests the misconception that active geo-replication must be configured separately before creating an auto-failover group, or that an availability group listener is needed, confusing Azure SQL Database with SQL Server Always On.

Full explanation →

255

MCQeasy

You are the database administrator for a company that uses Azure SQL Managed Instance. You need to allow a specific application to connect to the database using a service principal. The application authenticates with Microsoft Entra ID. What should you configure?

A.Create a contained database user mapped to the Microsoft Entra service principal.

B.Enable Always Encrypted and configure column master key with the service principal.

C.Add a server-level firewall rule with the application's IP address.

D.Create a SQL authentication login and user for the application.

AnswerA

A contained database user mapped to the Microsoft Entra service principal lets the application authenticate via Microsoft Entra ID without a SQL login, satisfying the requirement to connect using a service principal on Azure SQL Managed Instance.

Why this answer

A contained database user mapped to a Microsoft Entra ID service principal allows the application to authenticate directly to the database using its Microsoft Entra identity, without requiring a SQL Server login. This is the correct approach because Azure SQL Managed Instance supports Microsoft Entra authentication for service principals, enabling token-based authentication from applications that authenticate with Microsoft Entra ID.

Exam trap

The trap here is that candidates often confuse network-level controls (firewall rules) or encryption features (Always Encrypted) with authentication mechanisms, or mistakenly think SQL authentication can be used with Microsoft Entra ID service principals, when in fact a contained user mapped to the service principal is required.

How to eliminate wrong answers

Option B is wrong because Always Encrypted with a column master key protects data at rest and in transit but does not provide authentication; it is a data encryption feature, not an identity or access control mechanism. Option C is wrong because a server-level firewall rule controls network access by IP address, not authentication; the application already needs to authenticate, and firewall rules do not grant database access to a service principal. Option D is wrong because SQL authentication uses a username and password stored in the database, which is not compatible with Microsoft Entra ID service principals; the application authenticates via Microsoft Entra ID, not SQL credentials.

Full explanation →

256

MCQmedium

Your Azure SQL Database is configured with a failover group between two regions. The primary database experiences a catastrophic failure that prevents any connectivity. You need to initiate a failover to the secondary region. However, the failover group status shows 'Primary is down'. What should you do?

A.Run a planned failover to ensure zero data loss.

B.Wait for the primary to come back online and then failover.

C.Remove the primary database from the failover group and then failover.

D.Run a forced failover accepting potential data loss.

AnswerD

A forced failover promotes the secondary to primary despite the failover group reporting 'Primary is down', which blocks a normal planned failover. Because the primary is unreachable, asynchronous replication means some committed transactions may not have reached the secondary, so accepting potential data loss is unavoidable to restore write availability.

Why this answer

A forced failover (also called an unplanned failover) is used when the primary database is completely unavailable, and you accept potential data loss. Option A is incorrect because a planned failover requires both the primary and secondary to be online to ensure zero data loss. Option B is incorrect because waiting for the primary to come back online is not an appropriate action when immediate failover is required due to catastrophic failure.

Option C is incorrect because you cannot remove the primary database from a failover group when it is down; the failover group must be failed over as a whole.

Full explanation →

257

Multi-Selecthard

Which THREE are valid methods to implement disaster recovery for Azure SQL Database? (Select three.)

Select 3 answers

A.Failover groups

B.Always On availability groups

C.Active geo-replication

D.Long-term backup retention

E.Geo-restore (point-in-time restore)

AnswersA, C, E

Failover groups provide automatic geo-replication and a listener endpoint, letting you redirect connections to a secondary server in another region without changing connection strings. This satisfies the disaster recovery requirement by enabling rapid recovery from a regional Azure SQL Database outage.

Why this answer

Options A, C, and E are correct. Failover groups and active geo-replication are built-in disaster recovery features for Azure SQL Database that allow failover to a secondary region. Geo-restore (point-in-time restore to a different region) is also a valid DR method by restoring from geo-replicated backups.

Option B (Always On availability groups) is a feature for SQL Server on VMs or on-premises, not for Azure SQL Database. Option D (long-term backup retention) is a backup retention capability, not a disaster recovery method.

Full explanation →

258

Multi-Selectmedium

You are troubleshooting a performance issue on an Azure SQL Database. Which TWO actions should you prioritize to identify the root cause of high resource consumption?

Select 2 answers

A.Rebuild all indexes to improve query performance.

B.Change the database recovery model to Simple.

C.Scale the database to a higher service tier to mitigate the issue.

D.Review the Query Store Top Resource Consuming Queries report.

E.Query sys.dm_exec_query_stats to find queries with high total_worker_time.

AnswersD, E

Query Store captures per-query runtime statistics, including CPU, duration, and logical reads, persisted across plan changes. The Top Resource Consuming Queries report ranks statements by total consumption, directly isolating which queries drive the high resource usage reported in the stem, rather than merely confirming that consumption is elevated.

Why this answer

To identify the root cause of high resource consumption, you should use diagnostic tools that analyze query performance. The Query Store's Top Resource Consuming Queries report (D) provides historical insight into which queries consumed the most resources. Additionally, querying sys.dm_exec_query_stats (E) allows you to find queries with high total_worker_time, indicating CPU-intensive queries.

Options A (rebuilding indexes) and B (changing recovery model) are corrective actions, not diagnostic. Option C (scaling to a higher service tier) is a reactive mitigation that does not identify the root cause. Therefore, options D and E are the correct prioritized actions.

Full explanation →

259

MCQmedium

You are the database administrator for an Azure SQL Database named HRDB. The security team mandates that the database must be protected against SQL injection attacks and that any suspicious activity must be automatically detected and reported. You need to enable a feature that provides this protection with minimal administrative effort. What should you enable?

A.Transparent Data Encryption (TDE)

B.Auditing

C.Advanced Threat Protection

D.Dynamic Data Masking

AnswerC

Advanced Threat Protection for Azure SQL Database detects anomalous activities such as SQL injection attempts, unusual access patterns, and potential brute-force attacks. It sends alerts to the configured recipients and integrates with Microsoft Defender for Cloud. Enabling it requires minimal configuration and directly addresses the requirement for automatic detection and reporting of suspicious activity.

Why this answer

Advanced Threat Protection continuously monitors database activities and uses machine learning to identify potential vulnerabilities and anomalous access patterns, including SQL injection. It automatically raises alerts, satisfying the need for detection and reporting. The other features provide encryption, masking, or logging but lack the automated threat detection capability.

Exam trap

The trap here is confusing auditing or encryption with threat detection, assuming that logging or encrypting data automatically protects against SQL injection and alerts on suspicious behavior.

Full explanation →

260

MCQeasy

You are managing an Azure SQL Database that has Automatic Tuning enabled. You receive an alert that a query plan regression was detected and a plan correction was automatically applied. You want to verify the performance improvement. What should you use?

A.Use sys.dm_exec_query_stats to view current performance.

B.Review the Azure Monitor alert details.

C.Query the Query Store to compare query performance before and after the plan change.

D.Check the automatic tuning log in the Azure portal.

AnswerC

Query Store persists historical execution plans and runtime statistics, letting you compare a query's performance before and after the automatic plan correction. This directly satisfies the requirement to verify improvement, since the regression and forced plan are both recorded with their respective metrics.

Why this answer

Query Store is the built-in feature that captures query plan history and runtime statistics over time, so it can directly compare a query's performance before and after an automatic plan correction was applied. Automatic tuning relies on Query Store as its data source, and the 'Automatic Tuning' recommendation history is also surfaced through Query Store views such as sys.query_store_plan and sys.query_store_runtime_stats. This makes Query Store the authoritative place to verify the improvement.

Exam trap

DP-300 often tests the misconception that Azure Monitor or the tuning log provides the detailed before/after performance evidence, when in fact Query Store is the only feature that retains historical plan and runtime data.

How to eliminate wrong answers

Option A is wrong because sys.dm_exec_query_stats only shows aggregated statistics for currently cached plans and does not retain historical plan data, so it cannot compare before/after a plan regression. Option B is wrong because Azure Monitor alerts only notify that a regression was detected and a correction applied; they do not contain the detailed before/after query performance metrics. Option D is wrong because the automatic tuning log in the portal shows tuning actions taken, not the comparative query performance evidence needed to verify improvement.

Full explanation →

261

MCQmedium

You have an Azure SQL Database configured with active geo-replication to a secondary region. The primary region experiences a full outage. You need to fail over with minimal data loss. What should you do?

A.Initiate an unplanned failover from the primary to the secondary.

B.Delete the secondary database and create a new one in the primary region.

C.Initiate a planned failover from the primary to the secondary.

D.Create a new secondary database in the same region as the primary.

AnswerA

Unplanned failover immediately promotes the geo-secondary to primary without waiting for the primary to recover, accepting the small replication lag as potential data loss. This is the only option that restores write availability during a full regional outage with minimal loss.

Why this answer

Initiating an unplanned failover from the primary to the secondary is the correct action because the primary region is experiencing a full outage, and unplanned failover is designed for such disaster scenarios. It promotes the secondary database to become the new primary with minimal data loss, typically within the recovery point objective (RPO).

Exam trap

The trap is confusing planned failover (for maintenance) with unplanned failover (for outages), or attempting to create new resources during an outage.

How to eliminate wrong answers

Option B is wrong because deleting the secondary and creating a new one in the primary region is impossible during an outage and would result in data loss. Option C is wrong because a planned failover requires the primary to be online and is used for planned maintenance, not for an outage. Option D is wrong because creating a new secondary in the same region as the primary does not help when the primary region is down.

Full explanation →

262

MCQmedium

You need to automate the creation of a new Azure SQL Database whenever a new customer signs up. The solution should use infrastructure as code and integrate with your CI/CD pipeline. What should you use?

A.Create an Azure Automation runbook that calls New-AzureRmSqlDatabase and trigger it from your CI/CD pipeline.

B.Create an ARM template that defines the database and deploy it from your CI/CD pipeline.

C.Set up an Elastic Database Job that runs a CREATE DATABASE statement.

D.Configure a SQL Server Agent job on the logical server to run a CREATE DATABASE statement.

AnswerB

An ARM template declares the Azure SQL Database resource in JSON, so the CI/CD pipeline can deploy it repeatably per customer signup. This satisfies the infrastructure-as-code requirement, unlike portal or manual scripting approaches that lack declarative version control.

Why this answer

B is correct because ARM (Azure Resource Manager) templates are the recommended infrastructure-as-code approach for defining and deploying Azure SQL Databases in a repeatable, declarative manner. Integrating ARM template deployment into a CI/CD pipeline ensures consistent, version-controlled database creation as part of automated workflows, aligning with DevOps best practices.

Exam trap

The trap here is that candidates may confuse operational automation (e.g., runbooks, SQL Agent jobs) with infrastructure-as-code provisioning, mistakenly choosing a scripting or T-SQL approach instead of the declarative ARM template method that natively integrates with CI/CD pipelines.

How to eliminate wrong answers

Option A is wrong because Azure Automation runbooks using the deprecated New-AzureRmSqlDatabase cmdlet (AzureRM module) are not infrastructure as code; they rely on imperative scripting, lack declarative state management, and the AzureRM module is being replaced by Az PowerShell, making this approach outdated and less reliable for CI/CD integration. Option C is wrong because Elastic Database Jobs are designed for executing T-SQL scripts across multiple databases (e.g., schema maintenance, data updates), not for provisioning new databases; they cannot create a new database as part of a CI/CD pipeline. Option D is wrong because SQL Server Agent jobs run within the context of a single logical server and are not designed for infrastructure-as-code automation; they lack integration with CI/CD pipelines, version control, and declarative deployment, and are intended for administrative tasks like maintenance, not provisioning new databases from external triggers.

Full explanation →

263

MCQeasy

You need to automate the deployment of an Azure SQL Database along with its firewall rules and performance tier using infrastructure as code. Which technology should you use?

A.Bicep templates

B.SQL Server Data Tools (SSDT) database projects

C.T-SQL scripts

D.PowerShell scripts

AnswerA

Bicep is a declarative infrastructure-as-code language that defines the Azure SQL Database, its firewall rules and performance tier in a single template, then deploys them repeatably. This satisfies the requirement to automate deployment of all three resources together.

Why this answer

Bicep is a domain-specific language for deploying Azure resources declaratively. It is the recommended infrastructure as code tool for Azure. Option A is correct because Bicep templates allow you to define Azure SQL Database, firewall rules, and performance tier in a declarative manner.

Option B (SSDT) is used for database schema management, not resource deployment. Option C (T-SQL scripts) are for database queries and management, not infrastructure. Option D (PowerShell scripts) can automate tasks but are procedural, not declarative IaC like Bicep.

Full explanation →

264

MCQeasy

You need to recommend a performance monitoring solution for a new Azure SQL Managed Instance deployment. The solution must provide historical query performance data and the ability to compare performance before and after index changes. What should you include in the recommendation?

A.Query Store with custom retention settings

B.SQL Server DMVs

C.Azure SQL Analytics solution in Log Analytics

D.Azure SQL Database Intelligent Insights

AnswerA

Query Store persists query, plan and runtime statistics in the database itself, giving historical performance data and plan comparison across index changes. Custom retention settings keep that history long enough to compare before-and-after behaviour on the Managed Instance.

Why this answer

Query Store is the only feature that persists query execution plans and runtime statistics in the database, enabling historical performance analysis and before/after comparisons for index changes. It captures query text, plans, and runtime metrics over time, which is exactly what is needed to compare performance before and after index modifications. Custom retention settings allow you to control how long the data is kept, ensuring you have the necessary history.

Exam trap

DP-300 often tests the misconception that DMVs provide historical data, but they are transient and reset on restart; Query Store is the only feature designed for historical query performance analysis.

How to eliminate wrong answers

Option B is wrong because DMVs provide only current, in-memory performance data and are reset when the instance restarts, so they cannot provide historical data for before/after comparisons. Option C is wrong because Azure SQL Analytics (a Log Analytics solution) is deprecated and does not capture detailed query-level performance data or execution plans needed for index change analysis. Option D is wrong because Intelligent Insights is an automatic diagnostic service that detects performance issues but does not provide the granular historical query performance data or the ability to compare before/after index changes.

Full explanation →

265

MCQmedium

You are responsible for security compliance of Azure SQL databases. You need to audit all successful and failed login attempts and store the audit logs in a Log Analytics workspace for analysis. You also want to detect potential brute-force attacks. What should you implement?

A.Configure Azure Policy to enforce auditing on all SQL databases in the subscription.

B.Enable SQL Vulnerability Assessment and schedule recurring scans.

C.Enable Azure SQL Auditing for the server, configure the audit log destination to Log Analytics, and enable Microsoft Sentinel for threat detection.

D.Enable Advanced Threat Protection (ATP) for Azure SQL Database.

AnswerC

Server-level Azure SQL Auditing captures successful and failed logins, and routing those records to a Log Analytics workspace enables querying. Microsoft Sentinel then correlates the ingested data to detect brute-force patterns, satisfying both the auditing and threat-detection requirements.

Why this answer

Azure SQL Auditing captures both successful and failed login attempts (audit logs) and can be configured to send them directly to a Log Analytics workspace for centralized analysis. Microsoft Sentinel, when enabled, provides built-in analytics rules to detect brute-force attacks by correlating failed login patterns across time and IP addresses, fulfilling the threat detection requirement.

Exam trap

The trap here is that candidates confuse Advanced Threat Protection (ATP) with the combination of auditing and Sentinel, assuming ATP alone covers login auditing and brute-force detection, but ATP does not capture all login attempts nor store them in Log Analytics for custom analysis.

How to eliminate wrong answers

Option A is wrong because Azure Policy enforces compliance rules (e.g., requiring auditing to be enabled) but does not itself capture login audit logs or detect brute-force attacks; it only ensures the auditing setting is applied. Option B is wrong because SQL Vulnerability Assessment identifies database misconfigurations and missing patches, not login attempts or brute-force patterns; it focuses on security vulnerabilities, not authentication events. Option D is wrong because Advanced Threat Protection (ATP) for Azure SQL Database detects anomalous activities like SQL injection or unusual access patterns, but it does not specifically audit all successful and failed login attempts nor store those logs in Log Analytics; ATP relies on telemetry separate from the audit log stream.

Full explanation →

266

MCQeasy

You are responsible for an Azure SQL Managed Instance that hosts a critical database. You need to configure alerts to notify the operations team when the average CPU usage of the instance exceeds 80% for 10 minutes. You want to use the built-in monitoring capabilities of Azure. What should you create?

A.A SQL Server Agent job that queries sys.dm_os_performance_counters and sends an email.

B.A Log Analytics workspace query with a scheduled alert.

C.An Azure Automation runbook that runs a T-SQL query and sends a notification.

D.An Azure Monitor alert rule based on the CPU percentage metric.

AnswerD

Azure Monitor provides platform metrics for Azure SQL Managed Instance, including CPU percentage. You can create an alert rule that evaluates the average CPU percentage over a 10-minute window and triggers when it exceeds 80%. This is the native, straightforward method to achieve the requirement without additional configuration.

Why this answer

Azure Monitor metric alerts are the built-in mechanism for alerting on platform metrics like CPU percentage. You can specify the aggregation (average), the threshold (80%), and the evaluation period (10 minutes). This meets the requirement efficiently.

The other options involve custom scripting or log-based approaches that are not necessary for a simple metric threshold alert.

Exam trap

The trap here is overcomplicating the solution by using SQL Server Agent or Automation, when Azure Monitor natively supports metric alerts for Managed Instance.

Full explanation →

267

MCQeasy

You need to configure a backup policy for Azure SQL Database that allows restoring to any point within the last 7 days. What is the minimum point-in-time restore retention period you should set?

A.1 day

B.7 days

C.14 days

D.3 days

AnswerB

A 7-day point-in-time restore retention period satisfies the stem's requirement to restore to any point within the last 7 days, since Azure SQL Database's minimum configurable PITR retention is exactly 7 days. Setting 7 days meets the constraint without exceeding it, making it the minimum valid value.

Why this answer

Azure SQL Database supports point-in-time restore (PITR) with a configurable retention period between 1 and 35 days, with a default of 7 days. To allow restoring to any point within the last 7 days, the minimum retention period that satisfies the requirement is exactly 7 days. Setting a shorter period (1 or 3 days) would not meet the stated requirement, and 14 days exceeds the minimum needed.

Exam trap

DP-300 often tests the distinction between the default retention (7 days) and the configurable range (1-35 days), causing candidates to pick a value that is either too short or assumes the default is the only option.

How to eliminate wrong answers

Option A is wrong because a 1-day retention only allows restoring to points within the last 24 hours, which does not satisfy the 7-day requirement. Option C is wrong because 14 days exceeds the minimum required retention and is not the minimum value that meets the requirement. Option D is wrong because 3 days only covers the last 72 hours, which is insufficient for a 7-day restore window.

Full explanation →

268

MCQmedium

You have an Azure SQL Database that is part of a failover group with automatic failover. The primary region experiences a complete outage. The failover group automatically fails over to the secondary region. After the primary region is restored, you need to ensure the database is operational in the primary region with minimal data loss. What should you do?

A.Initiate a manual failover of the failover group back to the primary region.

B.Wait for automatic failback to occur.

C.Restore the database from a geo-redundant backup.

D.Delete the failover group and recreate it with the original primary as the new primary.

AnswerA

After the primary region recovers, the failover group remains on the secondary. A manual failover returns the primary to active role, restoring operational service there while the group's asynchronous replication limits data loss to the last synchronised transaction.

Why this answer

Azure SQL failover groups support automatic failover to the secondary region, but failback is not automatic. After the primary region is restored, you must manually initiate a failover to return the primary database to the original primary region. This ensures the database is operational in the primary region with minimal data loss, as the failover group maintains replication.

Exam trap

DP-300 often tests the misconception that failover groups automatically fail back; candidates must remember that failback is always manual.

How to eliminate wrong answers

Option B is wrong because Azure SQL failover groups do not perform automatic failback; failback requires manual initiation. Option C is wrong because restoring from a geo-redundant backup would lose recent transactions and is unnecessary when the failover group is intact. Option D is wrong because deleting and recreating the failover group is disruptive, unnecessary, and does not leverage the existing replication topology.

Full explanation →

269

MCQhard

You are configuring automatic tuning for an Azure SQL Database. The database has a heavy OLTP workload. You want to automatically correct query plan choice regressions without manual intervention. Which automatic tuning option should you enable?

A.DROP_INDEX

B.CREATE_INDEX

C.CORRECT_INDEX

D.FORCE_LAST_GOOD_PLAN

AnswerD

FORCE_LAST_GOOD_PLAN detects plan-choice regressions by comparing a query's performance against its previous good plan, then forces the earlier plan automatically. This satisfies the OLTP requirement to correct regressions without manual intervention, unlike CREATE INDEX or DROP INDEX tuning options.

Why this answer

FORCE_LAST_GOOD_PLAN, is the correct automatic tuning option for Azure SQL Database to automatically correct query plan choice regressions. When the database engine detects that a newly compiled query plan performs worse than the previously known good plan, it can automatically force the last known good plan without manual intervention, which is ideal for a heavy OLTP workload where performance stability is critical.

Exam trap

The trap here is that candidates often confuse index tuning options (CREATE_INDEX, DROP_INDEX) with query plan regression correction, mistakenly thinking that creating or dropping indexes will fix a plan choice regression, when in fact FORCE_LAST_GOOD_PLAN is the specific feature designed for that purpose.

How to eliminate wrong answers

Option A is wrong because DROP_INDEX is an automatic tuning option that identifies and drops unused or duplicate indexes to improve write performance and reduce storage, but it does not address query plan regressions. Option B is wrong because CREATE_INDEX automatically creates missing indexes that improve query performance based on the workload, but it does not correct query plan choice regressions. Option C is wrong because CORRECT_INDEX is not a valid automatic tuning option in Azure SQL Database; the valid index-related options are CREATE_INDEX and DROP_INDEX only.

Full explanation →

270

MCQmedium

You are reviewing the long-term retention (LTR) policy for an Azure SQL Database. The exhibit shows the current policy. You need to ensure that backups are retained for at least 10 years for compliance. What should you do?

A.Increase the yearly retention to P10Y.

B.Change the weekOfYear to 10.

C.Increase the monthly retention to P120M.

D.Increase the weekly retention to P10W.

AnswerA

The yearly retention period governs how long yearly full backups persist; setting it to P10Y retains them for ten years, satisfying the compliance requirement. Weekly, monthly, and daily retention values do not extend coverage to a decade.

Why this answer

The long-term retention (LTR) policy in Azure SQL Database uses ISO 8601 duration formats for each retention tier: weekly (P#W), monthly (P#M), and yearly (P#Y). To retain backups for at least 10 years, the yearly retention period must be set to P10Y, which represents 10 years. Only the yearly retention option supports multi-year durations, making it the correct choice for a 10-year compliance requirement.

Exam trap

DP-300 often tests the distinction between retention duration parameters (P10Y, P120M, P10W) and configuration parameters like weekOfYear, causing candidates to confuse the backup selection week with the retention period itself.

How to eliminate wrong answers

Option B is wrong because weekOfYear specifies which week of the year is used as the yearly backup (a value from 1 to 52), not the retention duration. Option C is wrong because P120M represents 120 months, but the monthly retention field only accepts durations up to P120M in theory — however, the monthly LTR tier is designed for shorter retention (up to 120 months) and is not the intended mechanism for a 10-year compliance policy; the yearly tier is the correct one for multi-year retention. Option D is wrong because P10W represents only 10 weeks, far short of 10 years, and the weekly tier is capped at a much shorter maximum retention.

Full explanation →

271

MCQeasy

You have an Azure SQL Database that is experiencing performance issues. You suspect that a recent deployment introduced a regression in a stored procedure. You need to identify the query plan change and the specific query that is performing poorly. What should you use?

A.Azure Monitor metrics

B.Query Store

C.SQL Server Profiler

D.Dynamic management views (DMVs)

AnswerB

Query Store captures query plans, runtime statistics, and history, allowing you to identify plan changes and performance regressions over time. You can pinpoint the stored procedure and see when its plan changed and how performance degraded. This is the ideal tool for diagnosing regressions because it retains historical data and provides built-in reporting for plan changes and top resource consumers.

Why this answer

Query Store is the correct tool because it retains historical query plans and runtime statistics, enabling you to detect plan changes and performance regressions. It can show when a stored procedure's plan changed and how its performance degraded. Other tools either lack historical data (DMVs), are unavailable (Profiler), or lack query-level detail (Azure Monitor metrics).

Exam trap

The trap here is assuming that real-time tools like DMVs or Profiler can show historical plan changes, when only Query Store provides that capability.

Full explanation →

272

MCQhard

You are planning to deploy an Azure SQL Managed Instance to host several databases migrated from an on-premises SQL Server. The instance must support cross-database queries, SQL Server Agent jobs, and Service Broker. You need to ensure that the instance can handle the expected IOPS and throughput requirements. Which configuration should you implement?

A.Deploy the instance in the Business Critical service tier with local SSD storage.

B.Deploy the instance in the General Purpose service tier with standard storage.

C.Deploy the instance in the General Purpose service tier and enable the In-Memory OLTP feature.

D.Deploy the instance in the Business Critical service tier and configure geo-replication for read scale-out.

AnswerA

The Business Critical service tier in Azure SQL Managed Instance uses local SSD storage and provides the lowest latency and highest IOPS and throughput. It also includes a built-in read-only replica for offloading reporting. It fully supports cross-database queries, SQL Server Agent, and Service Broker. This tier is designed for high-performance and high-availability requirements, making it the best fit for the described workload.

Why this answer

The Business Critical service tier in Azure SQL Managed Instance uses local SSD storage, delivering the lowest latency and highest IOPS and throughput. It also supports cross-database queries, SQL Server Agent, and Service Broker, which are required. The General Purpose tier uses remote storage and may not meet high-performance demands.

In-Memory OLTP alone does not overcome storage limitations, and geo-replication is for disaster recovery, not local performance. Therefore, Business Critical with local SSD is the correct choice.

Exam trap

The trap here is assuming that enabling In-Memory OLTP in the General Purpose tier will provide the same performance as the Business Critical tier, when the storage layer is the limiting factor.

Full explanation →

273

Multi-Selectmedium

You manage an Azure SQL Database that is accessed by several applications. You need to implement the principle of least privilege for database access. Which three actions should you take? (Choose three.)

Select 3 answers

A.Create contained database users instead of server-level logins.

B.Assign users to custom database roles with specific permissions.

C.Add users to the db_datareader role.

D.Configure firewall rules to restrict IP addresses.

E.Grant permissions at the object level (e.g., SELECT on specific tables) rather than at the schema level.

AnswersA, B, E

Contained users reduce server-level privilege.

Why this answer

Contained database users are authenticated directly within the database, independent of the server-level logins. This aligns with the principle of least privilege by avoiding the need for server-level permissions, which would grant broader access across the server. In Azure SQL Database, contained users are the recommended approach for database-level access control, as they limit the blast radius of a compromised credential to a single database.

Exam trap

The trap here is that candidates may confuse network security controls (firewall rules) with database access controls, or assume that built-in roles like db_datareader are acceptable for least privilege, when in fact they grant excessive permissions.

Full explanation →

274

MCQeasy

A company has an Azure SQL Database that is experiencing performance degradation during peak hours. The database is configured with the Standard tier (S2). Which action should you recommend to improve performance without changing the application code?

A.Scale up the database to a higher service objective (e.g., S3).

B.Enable Query Store and run the Performance Dashboard.

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

D.Create nonclustered indexes on all tables.

AnswerA

Scaling up to S3 increases the allocated DTUs and associated compute, memory, and IO resources for the same database, relieving peak-hour contention without application changes. Vertical scaling within the Standard tier is the direct remedy when the current service objective is the bottleneck.

Why this answer

Scaling up the Azure SQL Database from S2 to a higher service objective (e.g., S3) increases the allocated DTUs/vCores, memory, and IOPS, directly addressing resource saturation during peak hours without requiring any application changes. This is the fastest, least invasive remediation when the workload is CPU/IO bound and the tier is the bottleneck. It preserves connection strings, schema, and code, making it the correct first recommendation.

Exam trap

DP-300 often tests the difference between diagnosing a problem (Query Store, Performance Dashboard) and fixing it (scaling, indexing), so candidates who pick the diagnostic tool over the actual remediation lose the point.

How to eliminate wrong answers

Option B is wrong because enabling Query Store and running the Performance Dashboard are diagnostic activities that identify problematic queries — they do not by themselves improve performance. Option C is wrong because read scale-out is only available on the Premium/Business Critical tiers, not Standard S2, and it only offloads read-only workloads, which does not help if the bottleneck is writes or CPU. Option D is wrong because blindly creating nonclustered indexes on all tables is a dangerous anti-pattern that increases write overhead and storage, and it is not a targeted fix for a resource-constrained tier.

Full explanation →

275

MCQhard

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database uses SQL Server Agent jobs, Service Broker, and cross-database queries within the same instance. Which PaaS option should you choose?

A.Azure SQL Managed Instance

B.Azure SQL Database

C.SQL Server on Azure Virtual Machine

D.Azure SQL Database with elastic query

AnswerA

Azure SQL Managed Instance supports SQL Server Agent, Service Broker and cross-database queries within the same instance, so the workload migrates as PaaS with minimal code change, unlike Azure SQL Database which lacks these instance-scoped features.

Why this answer

Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs, Service Broker, and cross-database queries within the same instance. These features are not available in Azure SQL Database, which is a PaaS offering with a more restricted surface area. SQL Server on Azure VM is IaaS and requires manual management, while elastic query in Azure SQL Database only supports cross-database queries across different databases, not the full Service Broker or Agent functionality.

Exam trap

The trap here is that candidates often confuse Azure SQL Database with Azure SQL Managed Instance, assuming that all PaaS SQL offerings support the same features, but Azure SQL Database deliberately omits instance-scoped features like Agent and Service Broker to maintain a multi-tenant architecture.

How to eliminate wrong answers

Option B is wrong because Azure SQL Database does not support SQL Server Agent jobs, Service Broker, or cross-database queries within the same instance; it uses elastic jobs and has limited cross-database query capabilities via elastic query. Option C is wrong because SQL Server on Azure Virtual Machine is an IaaS solution, not a PaaS option, and requires you to manage the underlying OS and SQL Server instance, including high availability and backups. Option D is wrong because Azure SQL Database with elastic query only enables cross-database queries across different databases in the same logical server, but it still lacks SQL Server Agent and Service Broker support, and is not a separate PaaS offering.

Full explanation →

276

MCQmedium

You are designing a new Azure SQL Database for a critical OLTP workload. The database will be used by a global application with users in North America, Europe, and Asia. The primary requirement is low-latency reads for all regions. You need to choose a deployment option that supports geo-distributed reads and provides a single write endpoint. Which option should you select?

A.Azure SQL Database with active geo-replication

B.Azure SQL Managed Instance with failover groups

C.Azure SQL Database with failover groups

D.Azure SQL Database Hyperscale with geo-replication

AnswerC

Failover groups only support one readable secondary region.

Why this answer

Azure SQL Database failover groups (Option C) is the correct choice. A failover group provides a single read-write listener endpoint that automatically directs writes to the current primary, while also exposing a read-only listener endpoint that load-balances connections across geo-replicated readable secondaries in other regions. This directly satisfies the requirements for a single write endpoint and low-latency geo-distributed reads.

Active geo-replication (Option A) supports readable secondaries but does not provide a single write endpoint. Azure SQL Managed Instance failover groups (Option B) also provide these capabilities, but the scenario specifies Azure SQL Database, not Managed Instance. Hyperscale with geo-replication (Option D) is not required to meet these requirements; Hyperscale is primarily a scalability and performance tier, and geo-replication alone does not provide a single write endpoint.

Exam trap

Candidates often assume that a specialized tier such as Hyperscale is required for geo-distributed reads, or they confuse active geo-replication with failover groups. The key distinction is that failover groups provide a single read-write listener endpoint plus a read-only listener for geo-distributed reads, whereas active geo-replication requires connections to individual server endpoints and does not provide a single write endpoint.

How to eliminate wrong answers

Option A is wrong because active geo-replication for Azure SQL Database supports readable secondaries but does not provide automatic failover groups with a listener endpoint, making it less suitable for a global application requiring managed failover and low-latency reads. Option B is wrong because Azure SQL Managed Instance with failover groups supports geo-replication but is not optimized for the Hyperscale distributed storage architecture that provides the fastest read scale-out for global OLTP workloads. Option C is wrong because Azure SQL Database with failover groups (non-Hyperscale) uses the standard tier, which has limited storage and performance scaling compared to Hyperscale, and does not offer the same level of read replica distribution for global low-latency reads.

Full explanation →

277

MCQhard

You manage an Azure SQL Managed Instance that hosts several databases. You need to automate the process of patching the operating system and SQL Server engine with minimal downtime. What should you use?

A.Configure the maintenance window for the Managed Instance.

B.Use an Elastic Job agent to run a script that applies updates.

C.Schedule a manual patching using the Azure portal.

D.Use Azure Update Manager to schedule patching.

AnswerA

Azure SQL Managed Instance handles OS and SQL engine patching automatically; the maintenance window merely schedules when that built-in patching occurs, letting you align it to low-traffic periods. Since the stem requires automation with minimal downtime, configuring the window satisfies both constraints without manual intervention or additional tooling.

Why this answer

Azure SQL Managed Instance provides a built-in maintenance window feature that allows you to schedule patching of the underlying OS and SQL Server engine with minimal downtime. Configuring the maintenance window ensures updates are applied during a specified time, reducing impact on production workloads.

Exam trap

DP-300 often tests the difference between automated patching features of Azure SQL Managed Instance and other Azure services, and candidates may incorrectly choose Azure Update Manager or manual methods.

How to eliminate wrong answers

Option B is wrong because Elastic Job agent is used for automating and running T-SQL scripts across databases, not for patching the managed instance. Option C is wrong because manual patching via the Azure portal is not automated and does not provide minimal downtime; patching is managed by Azure. Option D is wrong because Azure Update Manager is for managing updates on VMs and servers, not for Azure SQL Managed Instance, which has its own patching mechanism.

Full explanation →

278

MCQmedium

You are planning to migrate an on-premises SQL Server 2019 database to Azure SQL Managed Instance. The database uses cross-database queries and SQL Server Agent jobs. You need to ensure that the migration supports these features with minimal changes. What should you do first?

A.Deploy an Azure SQL Managed Instance and restore a backup of the on-premises database directly.

B.Run Data Migration Assistant (DMA) to assess the database for compatibility issues with Azure SQL Managed Instance.

C.Use the Azure Database Migration Service (DMS) to perform an online migration without prior assessment.

D.Configure transactional replication from the on-premises SQL Server to Azure SQL Managed Instance.

AnswerB

Data Migration Assistant (DMA) assesses on-premises SQL Server databases for compatibility with Azure SQL Managed Instance and identifies unsupported features. It provides detailed reports on cross-database queries, SQL Server Agent jobs, and other components, helping you plan the migration. Running DMA first ensures you understand any necessary changes before migrating, minimizing surprises.

Why this answer

Data Migration Assistant (DMA) is the recommended tool to assess on-premises SQL Server databases for compatibility with Azure SQL Managed Instance. It identifies unsupported features such as certain cross-database queries and SQL Server Agent job steps, allowing you to plan remediation. Azure DMS is for migration, transactional replication is an alternative but not an assessment, and direct restore does not evaluate compatibility.

Exam trap

The trap here is assuming that Azure Database Migration Service can assess compatibility; it only migrates, so you must use DMA for assessment first.

Full explanation →

279

MCQmedium

You need to automate the deployment of an Azure SQL Database and its schema updates as part of a CI/CD pipeline. The pipeline must apply T-SQL scripts to the database after deployment. Which Azure DevOps task should you use to execute the T-SQL scripts against Azure SQL Database?

A.Azure PowerShell task

B.Azure SQL Database deployment task

C.Command line task

D.Azure CLI task

AnswerB

The Azure SQL Database deployment task in Azure Pipelines is designed to execute T-SQL scripts against an Azure SQL Database. It supports inline scripts or script files and handles authentication. This task is the native way to apply schema updates in a CI/CD pipeline for Azure SQL Database.

Why this answer

The Azure SQL Database deployment task is built for executing T-SQL scripts against Azure SQL Database in a pipeline. It supports both inline scripts and script files, and it handles authentication via service connections. This makes it the most efficient and reliable choice for applying schema changes in CI/CD.

Exam trap

The trap here is choosing a generic task like Azure PowerShell or Command Line, which can work but require custom code, instead of the purpose-built SQL deployment task.

Full explanation →

280

MCQmedium

You query the sys.dm_geo_replication_link_status dynamic management view for an Azure SQL Database configured with active geo-replication. The exhibit shows the output. What does this indicate about the replication health?

A.The secondary is fully synchronized with minimal lag.

B.The secondary role indicates a failover has occurred.

C.The secondary is 5 seconds behind, indicating a problem.

D.The replication is in the seeding phase.

AnswerA

Correct.

Why this answer

The replicationLag of 5 seconds is minimal and normal for active geo-replication, indicating the secondary is nearly synchronized. The replicationState 'CATCH_UP' confirms that the secondary is fully caught up with the primary. Options B, C, and D are incorrect: B is wrong because a secondary role is expected in geo-replication; C is wrong because 5 seconds lag is not a problem; D is wrong because the state is CATCH_UP, not SEEDING.

Full explanation →

281

MCQeasy

You are configuring Microsoft Defender for SQL for an Azure SQL Database. You want to receive email notifications when a suspicious activity is detected. What should you configure?

A.Configure a vulnerability assessment recurring scan and email the report.

B.Create an Azure Monitor alert rule for the 'SQL database threat detection' metric.

C.In the Microsoft Defender for SQL settings, enable 'Email notifications to admins and subscription owners'.

D.Enable SQL auditing and stream logs to a Log Analytics workspace.

AnswerC

Enabling email notifications to admins and subscription owners within the Microsoft Defender for SQL settings routes alerts for suspicious activity to the specified recipients. This satisfies the requirement to be notified when anomalous database access is detected.

Why this answer

Microsoft Defender for SQL includes a dedicated 'Email notifications to admins and subscription owners' setting under its threat detection policy. When enabled, this sends email alerts to Azure subscription owners and administrators whenever Defender detects suspicious activities such as SQL injection, brute-force attacks, or anomalous access patterns. This is the direct, built-in mechanism for email-based alerting on threat detections.

Exam trap

The trap here is that candidates often confuse the purpose of vulnerability assessment (periodic scanning) with real-time threat detection, or assume that Azure Monitor metric alerts are the correct way to receive email notifications for Defender for SQL alerts, when in fact the email notification is configured directly within the Defender for SQL settings.

How to eliminate wrong answers

Option A is wrong because vulnerability assessment recurring scans generate periodic reports on database vulnerabilities, not real-time email notifications for suspicious activity detection. Option B is wrong because there is no Azure Monitor metric named 'SQL database threat detection'; threat detection alerts are surfaced through Defender for SQL's own alerting system, not via Azure Monitor metric alerts. Option D is wrong because enabling SQL auditing and streaming logs to Log Analytics enables log collection and analysis, but does not by itself configure email notifications for suspicious activity; that requires an additional alert rule or action group.

Full explanation →

282

Multi-Selectmedium

You need to automate the monitoring of Azure SQL Database performance and receive alerts when certain conditions are met. Which TWO Azure services can be used together to achieve this?

Select 2 answers

A.Azure Sentinel

B.Log Analytics Workspace

C.Azure Monitor Alerts

D.Application Insights

E.Azure Advisor

AnswersB, C

Log Analytics stores and queries the diagnostic and metric data collected from Azure SQL Database. It supplies the log store that alert rules evaluate, satisfying the requirement for a queryable repository underpinning automated performance monitoring and alerting.

Why this answer

Log Analytics Workspace (B) is correct because it is the service that stores and queries the diagnostic and metric telemetry collected from Azure SQL Database, enabling you to write Kusto Query Language (KQL) queries that define the performance conditions to monitor. Azure Monitor Alerts (C) is correct because it evaluates those log/metric queries against defined thresholds and triggers notifications (for example, email, SMS, or action groups) when the conditions are met. Together, Log Analytics Workspace provides the data and query layer while Azure Monitor Alerts provides the rule evaluation and notification layer, forming the standard automated monitoring and alerting pipeline for Azure SQL Database.

Azure Sentinel (A) is a SIEM/SOAR tool focused on security threat detection, not general SQL performance monitoring. Application Insights (D) targets application-level telemetry (APM) for web apps and services, not Azure SQL Database performance metrics. Azure Advisor (E) only provides best-practice recommendations and does not perform real-time condition-based alerting.

Full explanation →

283

MCQhard

An Azure SQL Database contains personally identifiable information (PII). You need to mask the PII columns from non-administrative users while allowing administrators to see the actual data. Which feature should you use?

A.Always Encrypted

B.Transparent Data Encryption (TDE)

C.Dynamic Data Masking

D.Row-Level Security

AnswerC

Dynamic Data Masking applies masking rules at query time, returning masked values to non-administrative users while privileged accounts with UNMASK permission see actual data, satisfying the requirement to hide PII from non-administrators without altering stored values.

Why this answer

Dynamic Data Masking (DDM) is the correct choice because it selectively obscures sensitive PII columns in query results for non-administrative users, while leaving the data unmasked for users with elevated permissions (e.g., db_owner or the UNMASK permission). This is achieved by defining masking rules on specific columns, such as email or phone number, without altering the underlying stored data.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking with Always Encrypted, thinking both hide data from the database engine, but DDM only masks output while Always Encrypted encrypts data at the client and prevents the server from ever seeing plaintext.

How to eliminate wrong answers

Option A is wrong because Always Encrypted encrypts data at the client side, preventing the database engine from seeing plaintext values, which would block administrators from reading the actual data unless they have the column encryption key. Option B is wrong because Transparent Data Encryption (TDE) encrypts the entire database at rest (on disk), but does not control access or mask data for specific users during queries. Option D is wrong because Row-Level Security (RLS) restricts which rows a user can see based on a predicate function, but it does not mask or obfuscate column values within visible rows.

Full explanation →

284

MCQhard

You have an Azure SQL Managed Instance named MI1 in the West Europe region. The instance hosts a mission-critical database that must be recoverable within 30 minutes in the event of a regional outage. You need to implement a disaster recovery solution that minimizes data loss and administrative effort. The solution must support read-only access to the secondary during normal operations. What should you configure?

A.Configure geo-replication for the database to a secondary managed instance in North Europe.

B.Set up a failover group with a secondary managed instance, but do not enable the read-only listener.

C.Use log shipping to a secondary managed instance in North Europe.

D.Create an auto-failover group with MI1 as the primary and a secondary managed instance in North Europe.

AnswerD

Auto-failover groups for Azure SQL Managed Instance support automatic failover and provide a read-only listener endpoint for the secondary. This allows read-only access during normal operations. The RPO is typically less than 5 seconds, and the RTO is typically under 30 minutes, meeting the requirement. It also minimizes administrative effort because failover is automatic.

Why this answer

Auto-failover groups for Azure SQL Managed Instance are the correct choice because they provide automatic failover, a read-only listener for the secondary, and low RPO/RTO. They are specifically designed for cross-region disaster recovery for managed instances. Geo-replication is not available for managed instances, log shipping is not native, and omitting the read-only listener fails the read-only access requirement.

Exam trap

The trap here is confusing Azure SQL Database features like active geo-replication with SQL Managed Instance capabilities; managed instances do not support active geo-replication.

Full explanation →

285

MCQeasy

You are tasked with automating index maintenance for an Azure SQL Database. Which Azure service should you use to run T-SQL scripts on a recurring schedule?

A.SQL Server Agent

B.Elastic Database Jobs

C.Azure Automation Runbook

D.Azure Logic Apps

AnswerB

Elastic Database Jobs run T-SQL against Azure SQL Database on a defined recurrence, satisfying the scheduled index-maintenance requirement. Unlike SQL Agent, which is unavailable in Azure SQL Database, elastic jobs target logical servers and databases directly, executing scripts such as index rebuilds without external orchestration.

Why this answer

Elastic Database Jobs (B) is the correct service for automating T-SQL script execution across Azure SQL Database on a recurring schedule. It is specifically designed for Azure SQL Database and Azure SQL Managed Instance, providing a job scheduler that can run T-SQL scripts against multiple databases, handle retries, and manage job history. SQL Server Agent is not available in Azure SQL Database (only in SQL Server on-premises or Azure SQL Managed Instance), making Elastic Database Jobs the appropriate choice for this PaaS scenario.

Exam trap

The trap here is that candidates confuse SQL Server Agent (available in Azure SQL Managed Instance) with Azure SQL Database (single database/elastic pool), mistakenly assuming Agent is available for all Azure SQL offerings, when in fact Elastic Database Jobs is the correct scheduler for the PaaS Azure SQL Database service.

How to eliminate wrong answers

Option A is wrong because SQL Server Agent is not available in Azure SQL Database (single database or elastic pool); it is only supported in SQL Server on-premises, Azure SQL Managed Instance, and SQL Server on Azure VMs. Option C is wrong because Azure Automation Runbooks are designed for PowerShell or Python workflows, not for direct T-SQL execution against Azure SQL Database; they would require additional modules and connection management, making them less suitable for simple recurring T-SQL scripts. Option D is wrong because Azure Logic Apps are orchestration services for integrating apps and data, not a native T-SQL scheduler; they can execute SQL queries via connectors but lack the built-in job scheduling, retry policies, and database-targeting features of Elastic Database Jobs.

Full explanation →

286

MCQmedium

You are a DBA for a company that uses Azure SQL Database for its customer relationship management (CRM) system. The database is currently in the Standard tier (DTU S2) and is experiencing performance degradation during end-of-month reporting. Reports that aggregate large amounts of data take over 30 minutes to run. You notice that the database's DTU usage averages 80% during these reports, with high IO. You need to improve report performance without significantly increasing cost. The reports are read-only and can tolerate some staleness. What should you do?

A.Increase the service tier to S3 during the end-of-month period

B.Add nonclustered indexes to the tables used in reports

C.Convert the tables to clustered columnstore indexes

D.Create a read-only replica and direct reports to it

AnswerA

Increasing to S3 during reporting period provides more resources, improving performance without a permanent cost increase. This is the most feasible option given the tier limitation.

Why this answer

Increasing the service tier to S3 during the end-of-month period provides more DTUs and IO resources, which directly addresses the performance degradation without a permanent cost increase. The cost is only incurred during the reporting period, making it a cost-effective short-term solution. Option D is incorrect because read-only replicas are not supported in the Standard (DTU) S2 tier; they are only available in Premium, Business Critical, and Hyperscale tiers.

Therefore, this option is not feasible. Options B and C may help but are not as effective or risk affecting write performance.

Exam trap

The trap is assuming read-only replicas are available in all Azure SQL Database tiers. They are only supported in Premium, Business Critical, and Hyperscale tiers, not in Standard (DTU) tiers.

Full explanation →

287

MCQhard

You manage a business-critical Azure SQL Database named OrdersDB in the Business Critical service tier. The database is in the East US region. The company requires a secondary readable copy in West US that provides a recovery point objective (RPO) of 5 seconds and a recovery time objective (RTO) of 30 seconds during a regional outage. You need to implement the solution with the least administrative effort. What should you do?

A.Enable zone redundancy for the database and rely on Azure to fail over to a secondary zone.

B.Create a failover group with the West US server as the secondary and configure automatic failover policy.

C.Configure a long-term retention policy and restore the database to a West US server during an outage.

D.Configure active geo-replication to a West US server and create an Azure Automation runbook to fail over.

AnswerB

A failover group with automatic failover policy provides a readable secondary, automatic failover within the RTO, and stable listener endpoints. For Business Critical databases, replication uses local redundant storage and the RPO is typically less than 5 seconds. This meets the RPO, RTO, and least administrative effort requirements.

Why this answer

A failover group with automatic failover policy provides the needed readable secondary, automatic failover, and stable endpoints. For Business Critical, replication is synchronous within the region and asynchronous across regions with an RPO typically under 5 seconds. Zone redundancy and long-term retention do not address cross-region DR, and active geo-replication requires manual failover or custom automation.

Exam trap

The trap here is confusing zone redundancy, which only protects against a datacenter failure within a region, with cross-region disaster recovery.

Full explanation →

288

MCQhard

Your company uses Azure SQL Database with Microsoft Entra ID (formerly Azure AD) authentication. You need to grant a group of external consultants access to a specific database with read-only permissions. The consultants are from a partner organization that uses their own Microsoft Entra ID tenant. What should you do?

A.Invite the consultants as guest users in your Microsoft Entra ID tenant using B2B collaboration, then create a contained database user for each guest user

B.Create a contained database user mapped to the consultants' Microsoft Entra ID user principal names (UPNs)

C.Configure Azure SQL Database to trust the partner's Microsoft Entra ID tenant

D.Create a SQL Server authentication login and user for the consultants

AnswerA

B2B collaboration brings the partner tenant's identities into your tenant as guests, which Microsoft Entra authentication can then resolve. Contained database users created from those guest identities grant read-only access scoped to the database without server-level logins.

Why this answer

External consultants from a different Microsoft Entra ID tenant must first be invited as guest users via B2B collaboration to your tenant. Once they are guest users, you can create contained database users in Azure SQL Database mapped to their guest user identities (e.g., their UPN in your tenant) and grant them read-only permissions (e.g., db_datareader role). This approach respects the isolation of the partner's tenant while enabling access through your tenant's identity.

Exam trap

The trap here is that candidates assume you can directly map a contained database user to an external UPN without first establishing cross-tenant identity via B2B collaboration, or they mistakenly think Azure SQL Database can natively trust another Entra ID tenant.

How to eliminate wrong answers

Option B is wrong because you cannot directly create a contained database user mapped to a user principal name (UPN) from an external Microsoft Entra ID tenant; Azure SQL Database only recognizes identities from the tenant it is linked to. Option C is wrong because Azure SQL Database does not support trusting an external Microsoft Entra ID tenant directly; cross-tenant trust must be established via B2B collaboration at the Microsoft Entra ID level. Option D is wrong because SQL Server authentication logins and users bypass Microsoft Entra ID authentication entirely, which violates the requirement to use Microsoft Entra ID authentication and does not leverage the partner's existing identities.

Full explanation →

289

MCQmedium

You have a SQL Managed Instance that hosts a critical OLTP database. You notice that the average query wait time has increased significantly over the past hour. You need to identify the top resource waits. What should you use?

A.sys.dm_exec_query_stats

B.Query Store Wait Stats in SSMS

C.sys.dm_os_wait_stats

D.sys.dm_db_index_usage_stats

AnswerC

sys.dm_os_wait_stats aggregates cumulative wait statistics by wait type across the instance, letting you rank the top resource waits causing the slowdown. It directly satisfies the need to identify which resource the OLTP workload is waiting on.

Why this answer

C is correct because sys.dm_os_wait_stats is the dynamic management view that aggregates wait statistics across all sessions in the SQL Server instance, including SQL Managed Instance. It provides cumulative wait times categorized by wait type (e.g., PAGEIOLATCH, LCK_M_S), making it the appropriate tool to identify top resource waits when average query wait time increases.

Exam trap

The trap here is that candidates confuse performance metrics DMVs (like sys.dm_exec_query_stats) with wait statistics DMVs, or they assume Query Store Wait Stats is the primary diagnostic tool for real-time wait analysis, when sys.dm_os_wait_stats is the direct and authoritative source for identifying top resource waits.

How to eliminate wrong answers

Option A is wrong because sys.dm_exec_query_stats returns aggregated performance statistics for cached query plans (e.g., CPU time, logical reads), not wait statistics; it cannot show resource waits. Option B is wrong because Query Store Wait Stats in SSMS is a feature that surfaces wait statistics from the Query Store, but it relies on the Query Store being enabled and configured, and it does not provide the comprehensive, instance-level wait statistics that sys.dm_os_wait_stats does for immediate diagnosis. Option D is wrong because sys.dm_db_index_usage_stats tracks index usage patterns (seeks, scans, updates), not wait times or resource contention.

Full explanation →

290

MCQhard

You have an Azure SQL Managed Instance named MI1 in the East US region. The company requires a disaster recovery solution that provides a readable secondary in the West US region and automatic failover. You need to configure the solution with the least administrative effort. What should you do?

A.Use Azure Site Recovery to replicate MI1 to West US.

B.Create a failover group with MI1 as the primary and a secondary managed instance in West US.

C.Configure active geo-replication from MI1 to a managed instance in West US.

D.Set up transactional replication from MI1 to a managed instance in West US.

AnswerB

Failover groups are supported for Azure SQL Managed Instance and provide automatic failover, readable secondary, and stable listener endpoints. They require minimal administrative effort because they handle replication and failover configuration. This meets all requirements for the scenario.

Why this answer

Failover groups are the only option that provides automatic failover and a readable secondary for Azure SQL Managed Instance. Active geo-replication is not supported for Managed Instance, transactional replication requires manual failover, and Azure Site Recovery is for VMs. Failover groups also provide stable listener endpoints, reducing administrative effort.

Exam trap

The trap here is assuming that active geo-replication, which works for Azure SQL Database, also works for Azure SQL Managed Instance.

Full explanation →

291

Multi-Selecteasy

You are monitoring an Azure SQL Database. You need to identify which two metrics are most important for detecting a memory pressure issue. Which TWO should you select?

Select 2 answers

A.Log IO percentage

B.Memory grants pending

C.Page life expectancy

D.CPU percentage

E.Data IO percentage

AnswersB, C

Memory grants pending counts queries waiting for workspace memory before execution, so sustained non-zero values directly signal memory pressure. Unlike buffer cache hit ratio, which reflects page availability, this metric exposes grant contention at the workspace level, satisfying the stem's requirement to detect memory pressure in Azure SQL Database.

Why this answer

Memory grants pending (B) is a key indicator of memory pressure because it counts the number of queries waiting for a workspace memory grant; a sustained nonzero value means SQL Server cannot satisfy concurrent memory requests, directly signaling memory contention. Page life expectancy (C) measures how long pages stay in the buffer pool, and a low or declining PLE indicates that data pages are being evicted too quickly, which is a classic symptom of buffer pool memory pressure. Together these two metrics isolate memory-related stress rather than I/O or CPU behavior.

Log IO percentage (A) and Data IO percentage (E) reflect storage throughput/latency, and CPU percentage (D) reflects processor utilization, so none of them directly diagnose memory pressure.

Full explanation →

292

MCQmedium

You are deploying an Azure SQL Database for a new line-of-business application. The database must remain fully available during planned maintenance windows and provide a secondary copy in a different Azure region for disaster recovery. You need to configure the deployment to meet these requirements with minimal administrative effort. What should you implement?

A.Configure an auto-failover group that includes the primary Azure SQL Database and a secondary database in a paired region.

B.Enable active geo-replication for the Azure SQL Database and configure the application to connect to the secondary database manually during failover.

C.Create a zone-redundant Azure SQL Database and rely on the built-in high availability within the primary region.

D.Deploy the Azure SQL Database with the Business Critical service tier and configure long-term retention backups to a different region.

AnswerA

Auto-failover groups provide a read-write listener endpoint and a read-only listener endpoint, enabling seamless failover to a secondary region during planned or unplanned outages. They also support planned failover for maintenance, keeping the application available. This directly meets the requirements with minimal administrative effort because the failover policy and listener endpoints are managed by Azure.

Why this answer

Auto-failover groups are designed to provide disaster recovery and high availability across regions with minimal administrative overhead. They include a read-write listener and a read-only listener, enabling applications to reconnect automatically after failover. Planned failover can be initiated for maintenance, keeping the database available.

Active geo-replication lacks automatic failover and listener endpoints, zone redundancy only covers a single region, and Business Critical tier does not provide cross-region replication.

Exam trap

The trap here is assuming that zone redundancy or Business Critical tier alone provides cross-region disaster recovery, when they only protect against local failures.

Full explanation →

293

Multi-Selecthard

Which THREE of the following are required steps to configure a failover group for an Azure SQL Database with a readable secondary in a different region?

Select 3 answers

A.Create a server-level firewall rule on the secondary server to allow client IPs.

B.Add the primary database to the failover group.

C.Configure a grace period of at least 1 hour.

D.Create a secondary server in the target region with the same administrative login.

E.Set the failover group's read/write failover policy to 'Automatic' or 'Manual'.

AnswersB, D, E

A failover group is a container that holds the databases to be replicated and failed over. Adding the primary database to the group initiates geo-replication to the paired secondary server, so this step is mandatory before the group can serve read/write traffic.

Why this answer

Option B is correct because a failover group is defined over databases, so the primary database must be added to the failover group (via New-AzSqlDatabaseFailoverGroup or the portal) to be replicated and made failover-eligible. Option D is correct because you must first provision a secondary Azure SQL logical server in the target region, and it must use the same administrator login and password as the primary server so the contained database users and logins resolve after failover. Option E is correct because the failover group requires a read/write failover policy to be set to either Automatic or Manual, which determines whether Azure initiates failover automatically or only on manual trigger.

Option A is not required because failover group configuration does not mandate a server-level firewall rule on the secondary server; firewall rules are managed separately and the secondary is typically accessed through the failover group listener. Option C is not required because the grace period is a configurable value (default 1 hour) that can be set to other durations, and it is not a mandatory step to create the failover group.

Exam trap

The trap here is that candidates may think creating firewall rules on the secondary server is required for failover group setup, but those rules are only needed for direct client connections to the secondary, not for the replication or failover process itself.

Full explanation →

294

MCQmedium

You are deploying an Azure SQL Database for a new line-of-business application. The application's usage pattern is unpredictable, with long idle periods and occasional bursts of heavy read/write activity. You need to minimize compute cost while ensuring the database automatically scales compute resources based on workload demand. The database must remain online during scaling operations. What should you do?

A.Configure the database with the General Purpose service tier and the Serverless compute tier, setting an appropriate auto-pause delay.

B.Configure the database with the Basic service tier and set a maximum database size of 2 GB.

C.Configure the database with the Hyperscale service tier and enable read-scale out.

D.Configure the database with the Business Critical service tier and enable zone redundancy.

AnswerA

The Serverless compute tier automatically scales vCores based on workload and can pause the database during idle periods, reducing cost. Setting an auto-pause delay allows the database to go offline after inactivity, and it resumes automatically on the next connection. This matches the requirement to minimize cost while handling bursts and staying online during scaling.

Why this answer

The Serverless compute tier in Azure SQL Database is designed for intermittent, unpredictable workloads. It automatically scales compute based on demand and can pause during inactivity, reducing cost. Configuring an appropriate auto-pause delay ensures the database pauses when idle and resumes on the next connection, keeping the database available during scaling without manual intervention.

Exam trap

The trap here is assuming that high-availability features like zone redundancy or read-scale out provide automatic cost-saving compute scaling, when they address resilience or read performance instead.

Full explanation →

295

MCQmedium

You need to migrate an on-premises SQL Server 2019 database to Azure SQL Database with minimal downtime. The database is 500 GB and uses some features not supported in Azure SQL Database, such as FileTables. What is the best migration strategy?

A.Set up log shipping to an Azure SQL Database

B.Perform an offline migration using Azure Database Migration Service

C.Export a BACPAC file from the source and import it to Azure SQL Database

D.Use Azure Database Migration Service with online mode after removing FileTables

AnswerD

Azure Database Migration Service online mode replicates ongoing transaction log changes while the 500 GB database copies, satisfying the minimal-downtime constraint. Removing FileTables first resolves the unsupported-feature blocker, since Azure SQL Database cannot host them. Offline modes or backup restores would incur hours of write downtime instead.

Why this answer

Azure Database Migration Service (DMS) with online mode supports minimal-downtime migrations by continuously replicating ongoing changes from the source SQL Server to Azure SQL Database. However, FileTables are not supported in Azure SQL Database, so they must be removed from the source database before migration. This approach ensures near-zero downtime while addressing the unsupported feature.

Exam trap

The trap here is that candidates may assume log shipping (Option A) is viable for Azure SQL Database, but log shipping is not supported for Azure SQL Database as it requires SQL Server Agent and file-level restore operations, which are not available in that PaaS offering.

How to eliminate wrong answers

Option A is wrong because log shipping is not supported to Azure SQL Database; it only works between on-premises SQL Server instances or to SQL Server on Azure VMs, not to Azure SQL Database. Option B is wrong because an offline migration using Azure Database Migration Service would require taking the source database offline, causing significant downtime, which contradicts the requirement for minimal downtime. Option C is wrong because exporting a BACPAC file and importing it to Azure SQL Database is an offline process that does not support ongoing replication, leading to downtime, and it also does not handle unsupported features like FileTables automatically.

Full explanation →

296

MCQeasy

Your organization uses Azure SQL Database and needs to automate email notifications when a database reaches 80% storage usage. Which native Azure feature can you use?

A.Create an Azure Monitor alert rule on the 'storage_percent' metric with an email action group.

B.Create a SQL Agent alert that fires when the storage is above 80% and sends an email.

C.Configure Database Mail to send alerts automatically.

D.Create an Elastic Database Job that checks storage and sends email via sp_send_dbmail.

AnswerA

Azure Monitor alert rules evaluate platform metrics such as storage_percent and trigger action groups, which deliver email notifications. This satisfies the requirement for native, automated alerting at the 80% threshold without custom code or external tooling, unlike query-based or scheduled approaches.

Why this answer

Azure Monitor alert rules on the 'storage_percent' metric with an email action group are the native Azure feature for this requirement. Azure SQL Database emits the storage_percent metric to Azure Monitor, and alert rules can trigger when it exceeds 80%, invoking an action group that sends email. This is fully managed, requires no SQL Agent, and works for both single databases and elastic pools.

Exam trap

DP-300 often tests the misconception that SQL Server features like SQL Agent, Database Mail, and sp_send_dbmail are available in Azure SQL Database, when in fact they are only supported in Azure SQL Managed Instance or on-premises SQL Server.

How to eliminate wrong answers

Option B is wrong because SQL Agent is not available in Azure SQL Database (it is available in Azure SQL Managed Instance and SQL Server on-premises), so SQL Agent alerts cannot be created on Azure SQL Database. Option C is wrong because Database Mail is a SQL Server feature that is not supported in Azure SQL Database, and it is used for sending mail from T-SQL, not for metric-based alerting. Option D is wrong because Elastic Database Jobs run T-SQL on a schedule and could theoretically query storage, but they are not a native alerting mechanism and would require custom scripting plus an external mail relay, which Azure SQL Database does not provide via sp_send_dbmail.

Full explanation →

297

MCQeasy

A company plans to migrate an on-premises SQL Server database to Azure SQL Database Managed Instance. They require a high availability solution that provides automatic failover between replicas within the same region with an RPO of 0 and an RTO of less than 30 seconds. Which service tier should they choose?

A.Business Critical

B.General Purpose

C.Azure SQL Database (single)

D.Hyperscale

AnswerA

Business Critical uses Always On availability groups with multiple synchronous replicas in the same region, delivering zero data loss (RPO 0) and automatic failover typically under 30 seconds. Its local SSD and built-in read replica satisfy the stem's same-region, low-latency failover constraint, unlike General Purpose's single remote-storage replica.

Why this answer

Business Critical service tier in Azure SQL Managed Instance provides built-in high availability with multiple replicas and automatic failover, achieving an RPO of 0 and RTO typically under 30 seconds. It uses Always On availability groups under the hood with local redundant storage and supports read-only routing. This tier is designed for mission-critical workloads requiring the lowest latency and highest resilience.

Exam trap

DP-300 often tests the difference between General Purpose and Business Critical HA capabilities, and candidates may incorrectly assume Hyperscale or single database tiers offer the same sub-30-second RTO with zero RPO.

How to eliminate wrong answers

Option B is wrong because General Purpose uses remote storage and provides an RTO of around 1 hour and RPO of 5-10 minutes, not meeting the sub-30-second RTO and zero RPO. Option C is wrong because Azure SQL Database (single) is a different deployment model, not Managed Instance, and its HA characteristics vary by tier; the question specifies Managed Instance. Option D is wrong because Hyperscale is optimized for large databases and read-scale, but its HA SLA and failover times are not as stringent as Business Critical for the stated RPO/RTO.

Full explanation →

298

MCQhard

You are a database administrator for a financial services company. You have deployed an Azure SQL Database and configured auditing using the JSON policy shown in the exhibit. After a security incident, you need to review all successful and failed login attempts to the database. However, you notice that login events are not being captured in the audit logs. What is the most likely reason?

A.The audit logs are being sent to Azure Monitor instead of blob storage

B.The retention days are set too high, causing logs to be truncated

C.The audit actions and groups do not include login events

D.Auditing is disabled at the server level

AnswerC

The audit policy's action list omits the relevant login groups, so authentication events are never written to the audit log. Azure SQL Database auditing captures only the actions and action groups explicitly specified in the policy; without SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP or FAILED_DATABASE_AUTHENTICATION_GROUP, both successful and failed logins remain uncaptured.

Why this answer

The JSON policy shown in the exhibit defines audit actions and groups, but it does not include the `SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP` or `FAILED_DATABASE_AUTHENTICATION_GROUP` action groups. These groups are required to capture both successful and failed login attempts (authentication events) in Azure SQL Database. Without them, login events are not recorded in the audit logs, regardless of other settings.

Exam trap

The trap here is that candidates assume auditing automatically captures all security events, but Azure SQL Database requires explicit inclusion of authentication action groups to log login attempts, and the JSON policy in the exhibit likely omits these groups.

How to eliminate wrong answers

Option A is wrong because the destination of audit logs (Azure Monitor vs. blob storage) does not affect which events are captured; it only changes where the logs are stored. Option B is wrong because retention days control how long logs are kept, not which events are captured; setting retention too high would not cause logs to be truncated or missing. Option D is wrong because if auditing were disabled at the server level, no audit logs would be generated at all, but the question states that other events (not login events) are being captured, implying auditing is enabled.

Full explanation →

299

MCQmedium

You are a database administrator for a logistics company that uses Azure SQL Database. You need to automate the process of copying data from an on-premises SQL Server to Azure SQL Database every night. The data volume is large, and you want to minimize the impact on the source server. You also need to ensure that the copy operation is resilient to transient failures. What should you use?

A.Azure Elastic Jobs with a T-SQL script that uses OPENROWSET to read from the on-premises server.

B.Azure Data Factory with a self-hosted integration runtime and a copy activity.

C.Azure Automation runbook that uses the Invoke-Sqlcmd cmdlet to copy data.

D.SQL Server Integration Services (SSIS) with an Azure-SSIS integration runtime.

AnswerB

Azure Data Factory with a self-hosted integration runtime can connect to on-premises SQL Server and Azure SQL Database. The copy activity can be scheduled and supports parallel reads to minimize source impact. It also has built-in retry policies to handle transient failures, making it resilient. This is the recommended approach for large-scale data movement.

Why this answer

Azure Data Factory with a self-hosted integration runtime is the best solution. It can connect to on-premises SQL Server, perform parallel data extraction to reduce source impact, and has built-in retry and resilience features. It is designed for scheduled, large-scale data movement.

The other options either lack the necessary connectivity, are not optimized for performance, or do not provide the required resilience.

Exam trap

The trap here is assuming that Elastic Jobs can directly query on-premises sources, but they run in Azure and require additional connectivity.

Full explanation →

300

MCQeasy

You need to automate the deployment of an Azure SQL Database using Infrastructure as Code. The deployment should include the database, firewall rules, and threat detection settings. Which tool should you use?

A.Azure CLI scripts

B.Azure Automation runbooks

C.Azure Policy

D.Azure Resource Manager templates

AnswerD

ARM templates declaratively define the Azure SQL Database, firewall rules, and threat detection settings in one deployment, satisfying the Infrastructure as Code requirement. They natively support all three resource types without custom scripting or extra tooling.

Why this answer

Azure Resource Manager (ARM) templates are the native IaC for Azure. Azure Automation runbooks can deploy but are not declarative. Azure CLI can script deployments but is imperative.

Azure Policy is for governance, not deployment.

Full explanation →

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