Microsoft Azure Database Administrator Associate DP-300 DP-300 Questions 451–525 | Page 7/8

451

Multi-Selecthard

You are the DBA for an Azure SQL Database named OrdersDB. The security team requires that you implement row-level security (RLS) to ensure that sales representatives can only view orders for their own region. You need to create a security policy that filters rows based on the sales representative's region. Which two actions should you perform? (Choose two.)

Select 2 answers

A.Grant SELECT permission on the Orders table to all sales representatives.

B.Create an inline table-valued function that returns 1 when the sales representative's region matches the row's region.

C.Create a security policy that adds a FILTER PREDICATE on the Orders table using the function.

D.Create a database role for each region and add sales representatives to the appropriate role.

E.Enable Auditing on the Orders table to track access.

AnswersB, C

A predicate function is required for RLS. An inline table-valued function that returns 1 when the user's region matches the row's region is used as the filter predicate in the security policy. This function enforces the row filtering logic based on the current user's region.

Why this answer

Row-level security in Azure SQL Database requires a predicate function that defines the filtering logic and a security policy that applies that function to the table. The inline table-valued function returns 1 when the row should be visible to the user, and the security policy adds a FILTER PREDICATE using that function. Together, they enforce that sales representatives only see orders for their region.

Other actions do not implement RLS.

Exam trap

The trap here is thinking that granting permissions or creating roles alone achieves row-level security, when the essential components are the predicate function and the security policy.

Full explanation →

452

Multi-Selecthard

You manage an Azure SQL Managed Instance that hosts a mission-critical database. The instance is in the East US 2 region. You need to configure a disaster recovery solution that meets the following requirements: (1) provide a readable secondary in a different Azure region, (2) support automatic failover, and (3) minimize administrative effort. Which two actions should you perform? (Choose two.)

Select 2 answers

A.Create a failover group and add the managed instance to it.

B.Create a geo-secondary using the Azure portal by selecting the managed instance and choosing 'Geo-Replication'.

C.Enable zone redundancy on the primary managed instance.

D.Deploy a secondary managed instance in a different region and add it to the failover group.

E.Configure active geo-replication for the managed instance.

AnswersA, D

A failover group for Azure SQL Managed Instance provides a read-write listener endpoint and supports automatic failover to a secondary instance in another region. It meets the requirement for a readable secondary and automatic failover. It also minimizes administrative effort because the failover group manages the replication and endpoint configuration, and you can initiate failover with a single action.

Why this answer

For Azure SQL Managed Instance, cross-region disaster recovery with a readable secondary and automatic failover is achieved by using failover groups. You must deploy a secondary managed instance in a different region and then add both instances to a failover group. Active geo-replication and zone redundancy do not meet the requirements for a cross-region readable secondary with automatic failover.

The failover group provides the listener endpoint and automates the failover process.

Exam trap

The trap here is confusing Azure SQL Database features with Azure SQL Managed Instance capabilities; active geo-replication and portal-based geo-replication are not available for managed instance, and zone redundancy only provides local high availability.

Full explanation →

453

Multi-Selectmedium

You are monitoring an Azure SQL Database using Azure Monitor metrics. You need to configure alerts to notify the operations team when the database is approaching resource limits. Which two metrics should you use to detect potential CPU and I/O bottlenecks? (Choose two.)

Select 2 answers

A.sessions_percent

B.log_write_percent

C.cpu_percent

D.physical_data_read_percent

E.workers_percent

AnswersC, D

The cpu_percent metric represents the percentage of CPU usage by the database. It is a direct indicator of CPU pressure. Alerting when this metric consistently exceeds a threshold, such as 80%, helps identify CPU bottlenecks before they impact performance. This metric is available in Azure Monitor for Azure SQL Database.

Why this answer

To detect CPU and I/O bottlenecks, the cpu_percent and physical_data_read_percent metrics are the most direct indicators. cpu_percent shows CPU utilization, while physical_data_read_percent reflects I/O read pressure. These metrics are available in Azure Monitor and can be used to trigger alerts when thresholds are exceeded, enabling proactive management.

Exam trap

The trap here is choosing metrics like log_write_percent or workers_percent that are related to resource usage but not specifically CPU or read I/O, which are the bottlenecks in question.

Full explanation →

454

Multi-Selectmedium

Which TWO of the following are supported high availability features in Azure SQL Managed Instance?

Select 2 answers

A.Zone-redundant deployment for Business Critical tier

B.Database mirroring

C.Always On Availability Groups (built-in)

D.Log shipping to a secondary instance

E.Windows Server Failover Clustering

AnswersA, C

Zone-redundant deployment spreads Business Critical replicas across availability zones, protecting against datacentre-level failure with automatic failover. This is a supported high availability feature in Azure SQL Managed Instance, unlike General Purpose, which relies on local redundancy only.

Why this answer

Option A is correct because Azure SQL Managed Instance in the Business Critical service tier supports zone-redundant deployment, which places the primary and secondary replicas across different availability zones to protect against datacenter-level failures. Option C is correct because SQL Managed Instance includes a built-in Always On Availability Groups mechanism that automatically provisions and manages replicas for high availability, with the Business Critical tier using local SSD and multiple secondary replicas. Database mirroring (B) is a deprecated SQL Server feature and is not offered as a supported HA option in Azure SQL Managed Instance.

Log shipping to a secondary instance (D) is not a built-in HA feature of SQL Managed Instance, though it can be configured manually for disaster recovery scenarios. Windows Server Failover Clustering (E) is an underlying infrastructure concept used by SQL Server on-premises and Azure VMs, but it is not exposed or supported as a user-configurable HA feature in Azure SQL Managed Instance.

Exam trap

The trap here is that candidates often confuse on-premises SQL Server high availability features (like database mirroring, log shipping, or manual Windows clustering) with the fully managed, built-in capabilities of Azure SQL Managed Instance, leading them to select unsupported legacy options.

Full explanation →

455

MCQeasy

You are configuring managed backup for an Azure SQL Managed Instance as shown in the exhibit. What is the purpose of this configuration?

A.To enable geo-replication for the managed instance.

B.To configure automated backups of the managed instance to Azure Blob Storage.

C.To set up disaster recovery across Azure regions.

D.To configure point-in-time restore for the managed instance.

AnswerB

Managed backup automates full, differential and transaction log backups of the managed instance directly to Azure Blob Storage, giving retention control without manual jobs. This satisfies the exhibit's configuration purpose of automated backup to blob storage.

Why this answer

The configuration shown in the exhibit is for managed backup, which automatically schedules and stores full, differential, and transaction log backups of the Azure SQL Managed Instance to Azure Blob Storage. This ensures that backups are retained and available for restore operations without manual intervention, fulfilling the core purpose of automated backups to Azure Blob Storage.

Exam trap

The trap here is that candidates often confuse the purpose of configuring automated backups (which creates the backup chain) with the point-in-time restore feature (which uses that chain), leading them to select Option D instead of the correct Option B.

How to eliminate wrong answers

Option A is wrong because geo-replication for Azure SQL Managed Instance is configured via failover groups, not through managed backup settings; managed backup does not replicate data to another region. Option C is wrong because disaster recovery across Azure regions is achieved by setting up a failover group with a secondary managed instance in a paired region, not by enabling managed backup. Option D is wrong because point-in-time restore is a feature that relies on the backup chain created by managed backup, but the configuration itself is for creating those backups, not for performing the restore operation.

Full explanation →

456

MCQmedium

You have an Azure SQL Database with sensitive customer data. You need to mask the credit card numbers so that only users with the 'Unmask' permission can see the full number. Non-privileged users should see only the last four digits. Which feature should you implement?

A.Column-level security

B.Row-level security

C.Dynamic Data Masking

D.Always Encrypted

AnswerC

Dynamic Data Masking applies a masking rule at query time, returning only the last four digits to users lacking UNMASK permission while privileged users see full values. This directly meets the requirement without altering stored data or requiring application changes.

Why this answer

Dynamic Data Masking (DDM) is the correct feature because it allows you to obfuscate sensitive data in query results without changing the underlying database. You can define a mask on the credit card column that shows only the last four digits by default, and grant the UNMASK permission to privileged users so they see the full value. This directly meets the requirement of role-based partial masking without altering the stored data.

Exam trap

The trap here is that candidates confuse Dynamic Data Masking with Always Encrypted, thinking encryption is required for masking, but DDM is purely a presentation-layer obfuscation that does not encrypt the underlying data.

How to eliminate wrong answers

Option A is wrong because Column-level security controls read access at the column level (e.g., denying SELECT on a column), but it cannot partially mask data within a column; it either grants or denies full visibility. Option B is wrong because Row-level security filters entire rows based on a predicate function, but it cannot mask individual column values within a row. Option D is wrong because Always Encrypt encrypts data at the client side and never exposes plaintext to the database engine, making it impossible to show partial values (like last four digits) to non-privileged users without complex client-side logic.

Full explanation →

457

MCQeasy

You are preparing a disaster recovery runbook. You plan to use the PowerShell command shown in the exhibit to restore a database to a different region. What must be true for this command to succeed?

A.The source database must have geo-redundant backup storage configured.

B.The source database must have point-in-time restore enabled.

C.The source database must be online at the time of restoration.

D.The target server must be in the same region as the source server.

AnswerA

Geo-restore to a different Azure region requires the source database's backup storage redundancy to be geo-redundant; locally redundant or zone-redundant backups exist only in the primary region, so no copy is available to restore elsewhere.

Why this answer

The Restore-AzSqlDatabase cmdlet with the -FromGeoBackup parameter requires the source database to have geo-redundant backup storage (also known as geo-redundant storage) enabled. This allows restoration from geographically replicated backups. Option B is incorrect because point-in-time restore is not required for a geo-restore; geo-restore uses the geo-redundant backup independently.

Option C is incorrect because the source database does not need to be online at the time of restoration; geo-backups are stored in Azure storage and are available even if the source database is offline. Option D is incorrect because the purpose of geo-restore is to restore to a different region; therefore, the target server can be in a different region than the source.

Full explanation →

458

MCQmedium

You are designing a solution for storing audit logs from Azure SQL Database. The logs must be retained for 7 years and must be immutable to prevent tampering. Which Azure service should you use?

A.Use Azure Files share with read-only permissions

B.Send logs to Azure Log Analytics workspace

C.Store logs in an audit table in Azure SQL Database

D.Azure Blob Storage with immutable storage policy

AnswerD

Azure Blob Storage with an immutable storage policy enforces write-once, read-many retention using time-based legal holds, preventing modification or deletion for the specified period. This satisfies both the 7-year retention and tamper-proof immutability requirements for the audit logs.

Why this answer

Azure Blob Storage with an immutable storage policy (WORM – Write Once, Read Many) is the correct choice because it ensures that audit logs cannot be modified or deleted for a specified retention period (7 years). This meets the immutability and retention requirements for compliance with regulations such as SOX or HIPAA. Azure SQL Database audit logs can be directly streamed to Azure Blob Storage, making it a seamless and secure storage solution.

Exam trap

The trap here is that candidates often confuse 'immutable' with 'read-only permissions' (Option A) or assume that a database table (Option C) can be made immutable by restricting permissions, but true immutability requires storage-level WORM enforcement that cannot be bypassed by any user or process.

How to eliminate wrong answers

Option A is wrong because Azure Files share with read-only permissions does not provide true immutability; permissions can be changed by an administrator, and the underlying data can be modified or deleted, failing the tamper-proof requirement. Option B is wrong because Azure Log Analytics workspace is designed for real-time monitoring and analysis, not long-term immutable storage; it has a maximum retention period of 730 days (2 years) and does not support WORM policies. Option C is wrong because storing logs in an audit table in Azure SQL Database does not guarantee immutability; data can be altered or deleted by users with appropriate permissions, and the database itself is not designed for write-once, read-many compliance storage.

Full explanation →

459

MCQmedium

You are deploying an Azure SQL Database for a new line-of-business application. The application's workload is unpredictable, with periods of near-zero activity and sudden bursts of high read/write volume. The database must scale compute resources automatically without manual intervention, and you want to minimize cost during idle periods. You create the database using the vCore purchasing model. Which service tier should you select?

A.Serverless

B.General Purpose

C.Business Critical

D.Hyperscale

AnswerA

Serverless is a compute tier for single databases in the vCore purchasing model that automatically scales compute based on workload demand and can auto-pause during inactive periods, reducing cost to storage only. It bills per second of compute usage. This matches the requirement for automatic scaling and cost minimization during idle periods without manual intervention.

Why this answer

Serverless compute automatically scales vCores up and down based on workload demand and pauses the database during inactivity, billing only for storage while paused. This directly addresses the need for automatic scaling and cost minimization during idle periods. Other service tiers require manual scaling and do not auto-pause, so they cannot meet the stated requirements.

Exam trap

The trap here is assuming that any vCore-based service tier provides automatic scaling, when in fact only the Serverless compute tier offers auto-scaling and auto-pause.

Full explanation →

460

Multi-Selectmedium

Which TWO of the following are benefits of using Azure SQL Database failover groups compared to active geo-replication alone?

Select 2 answers

A.Support for manual failover.

B.Failover of multiple databases in a single group.

C.Readable secondary replicas for read-scale workloads.

D.Automatic failover based on the grace period.

E.Synchronous data replication to the secondary.

AnswersB, D

Correct: Failover groups allow coordinated failover of multiple databases.

Why this answer

Failover groups extend active geo-replication by allowing you to manage failover for a group of databases as a single unit. This simplifies the failover process when you have multiple databases that must be failed over together to maintain application consistency. Option B is correct because failover groups support the coordinated failover of multiple databases, which active geo-replication alone does not.

Exam trap

The trap here is that candidates confuse the features of active geo-replication (like readable secondaries and manual failover) with the unique benefits of failover groups, which are specifically the ability to fail over multiple databases as a group and the automatic failover based on a grace period.

Full explanation →

461

MCQmedium

You observe that the average of Maximum DTU consumption over the last hour is consistently above 90%. What should you do next?

A.Scale up the database to a higher service tier or increase DTU.

B.Enable Query Store to analyze top queries.

C.Rebuild all indexes in the database.

D.Do nothing; it's normal for DTU to be high.

AnswerA

Sustained Maximum DTU above 90% indicates the database tier is saturated and cannot absorb further load. Scaling up to a higher service tier or increasing DTUs adds compute and I/O capacity, directly relieving the resource bottleneck causing the high consumption.

Why this answer

A sustained average of Maximum DTU consumption above 90% over an hour indicates the database is consistently resource-bound and approaching its tier ceiling. The correct next step is to scale up to a higher service tier or add DTUs so the workload has headroom and latency does not degrade. Query Store and index maintenance are optimization steps, but the immediate signal is capacity saturation.

Exam trap

DP-300 often tests whether candidates jump to query tuning when the metric itself is a capacity signal — the trap is choosing optimization over scaling for sustained high DTU.

How to eliminate wrong answers

Option B is wrong because Query Store is a diagnostic/optimization tool, not the immediate response to sustained saturation — you enable it to find bad queries, but the resource ceiling still needs raising first. Option C is wrong because rebuilding all indexes is a heavy, indiscriminate operation that can worsen DTU pressure and is not justified by a DTU metric alone. Option D is wrong because consistently exceeding 90% DTU is a documented signal to scale, not a normal steady state.

Full explanation →

462

MCQmedium

You are responsible for an Azure SQL Database that hosts a mission-critical application. You need to configure an alert that fires when the database's CPU usage exceeds 90% for more than 10 minutes. You want to use the built-in Azure Monitor metrics for Azure SQL Database. Which metric should you use?

A.dtu_consumption_percent

B.log_write_percent

C.physical_data_read_percent

D.cpu_percent

AnswerD

The cpu_percent metric represents the percentage of CPU used by the database. It is a standard Azure Monitor metric for Azure SQL Database and is exactly what you need to alert on CPU usage exceeding a threshold. You can create an alert rule on this metric with a condition of greater than 90 and an aggregation granularity of 10 minutes to meet the requirement.

Why this answer

The cpu_percent metric directly measures the percentage of CPU utilized by the Azure SQL Database. It is the appropriate metric to use for an alert on CPU usage exceeding a threshold. Other metrics like dtu_consumption_percent, physical_data_read_percent, and log_write_percent measure different resources and would not accurately reflect CPU usage.

Using cpu_percent ensures the alert fires only when CPU is the bottleneck.

Exam trap

The trap here is confusing DTU consumption with CPU usage; DTU includes multiple resources, so a DTU alert may fire even when CPU is not the issue.

Full explanation →

463

Multi-Selecthard

A database administrator manages an Azure SQL Managed Instance that hosts a mission-critical database. The administrator needs to automate the execution of a T-SQL script that performs index maintenance and then sends an email notification with the results. The solution must use native Azure SQL Managed Instance capabilities and minimize external dependencies. Which two actions should the administrator perform? (Choose two.)

Select 2 answers

A.Configure a Logic App that triggers on a schedule and sends an email via Office 365.

B.Create an Elastic Database Job that targets the Managed Instance database.

C.Deploy an Azure Automation runbook that connects to the Managed Instance and runs the script.

D.Configure Database Mail on the Managed Instance and add a notification step to the job.

E.Create a SQL Server Agent job with a T-SQL step that runs the index maintenance script.

AnswersD, E

Database Mail is supported on Azure SQL Managed Instance and can be configured with an SMTP account. Adding a notification step to the SQL Server Agent job allows the job to send email results directly from the instance, meeting the notification requirement without external services.

Why this answer

SQL Server Agent and Database Mail are both supported natively on Azure SQL Managed Instance. Using a SQL Server Agent job with a T-SQL step and a notification step satisfies the automation and email requirements while keeping the solution self-contained on the instance.

Exam trap

The trap here is assuming that Azure SQL Managed Instance lacks SQL Server Agent and Database Mail, which are actually available.

Full explanation →

464

Multi-Selecthard

Which TWO actions are required to automate the export of an Azure SQL Database to a BACPAC file on a monthly basis? (Choose two.)

Select 2 answers

A.Configure long-term retention (LTR) policy for the database.

B.Use Azure Automation or a scheduled Azure Function to call the Export-AzSqlDatabase cmdlet.

C.Deploy a SQL Server on Azure VM to run the export command.

D.Install SQL Server Integration Services (SSIS) on a virtual machine.

E.Create an Azure Storage account with a container to store the BACPAC file.

AnswersB, E

Export-AzSqlDatabase performs the actual BACPAC export, but it must run unattended. Azure Automation runbooks or a timer-triggered Azure Function supply that monthly schedule and authenticate to the subscription, satisfying the automation requirement the question specifies.

Why this answer

Option B is correct because automating a monthly BACPAC export requires a scheduling/orchestration mechanism, and Azure Automation runbooks or a timer-triggered Azure Function can invoke the Az PowerShell cmdlet Export-AzSqlDatabase (or the equivalent az sql db export CLI command) on a recurring schedule. Option E is correct because a BACPAC export must be written to a destination, and the Export-AzSqlDatabase cmdlet requires a target Azure Storage account and container (specified via -StorageAccountName/-StorageContainerName or a storage key/URI) to hold the resulting .bacpac file. Option A is not required because long-term retention (LTR) applies to automated full/differential/log backup copies for point-in-time restore, not to BACPAC logical exports.

Option C is not required because the export is a platform service performed by Azure SQL Database; no SQL Server on an Azure VM is needed. Option D is not required because SSIS is an ETL tool and plays no role in generating a BACPAC export.

Exam trap

DP-300 often tests the components required for automating BACPAC export, and candidates may incorrectly include LTR or SSIS, which are not relevant.

Full explanation →

465

MCQmedium

You administer an Azure SQL Database named HRDB. The security team requires that any connection to HRDB from outside the corporate network be blocked, but on-premises applications must continue to connect over the existing site-to-site VPN. The database currently has a public endpoint and a firewall rule allowing all Azure services. You need to restrict access so that only the VPN subnet can reach HRDB. What should you configure?

A.Enable Microsoft Defender for SQL and set the Advanced Threat Protection alert type to 'Access from unusual location'.

B.Set the database's Public Network Access to Disabled and create a private endpoint in the VPN-connected virtual network.

C.Configure a database-level firewall rule that allows only the on-premises application's service account and deny all other logins.

D.Add a server-level firewall rule for the VPN subnet's public IP address range and keep the public endpoint enabled.

AnswerB

Disabling public network access removes the public endpoint, and a private endpoint places the logical server inside the VPN-connected VNet so on-premises traffic flows over the private IP. This satisfies both the block-outside requirement and continued connectivity from the corporate network without exposing a public listener.

Why this answer

The requirement is network isolation combined with continued VPN access. Disabling public network access closes the internet-facing endpoint, and a private endpoint in the VPN-connected VNet provides a private IP that on-premises systems can reach over the tunnel. Firewall rules and threat detection do not remove the public listener, so only the private-endpoint approach meets the stated condition.

Exam trap

The trap here is assuming that tightening firewall rules or enabling threat detection removes public exposure, when only disabling public network access and using a private endpoint actually eliminates the public listener.

Full explanation →

466

MCQeasy

Refer to the exhibit. You are configuring Azure SQL Database Transparent Data Encryption (TDE) with customer-managed keys (CMK) stored in Azure Key Vault. The deployment uses a user-assigned managed identity. However, after deployment, the TDE status shows 'Inaccessible'. What is the most likely cause?

A.The key specified in the URI does not exist

B.The user-assigned managed identity is not assigned to the SQL Database server

C.The Key Vault firewall is enabled and does not allow Azure services

D.The managed identity lacks 'Get', 'Wrap Key', and 'Unwrap Key' permissions on the Key Vault key

AnswerD

TDE with customer-managed keys requires the user-assigned managed identity to hold Get, Wrap Key and Unwrap Key permissions on the Key Vault key. Without them, Azure SQL Database cannot unwrap the protector, so TDE reports Inaccessible.

Why this answer

When using customer-managed keys (CMK) for TDE in Azure SQL Database, the managed identity assigned to the logical server must have 'Get', 'Wrap Key', and 'Unwrap Key' permissions on the key in Azure Key Vault. Without these specific permissions, the SQL Database service cannot retrieve or use the key to encrypt or decrypt the database encryption key, resulting in an 'Inaccessible' TDE status.

Exam trap

The trap here is that candidates often assume the issue is with the Key Vault firewall or the identity assignment, but the most common post-deployment cause of 'Inaccessible' TDE is missing cryptographic permissions on the managed identity, not network or identity existence issues.

How to eliminate wrong answers

Option A is wrong because if the key specified in the URI did not exist, the deployment would typically fail during configuration, not result in an 'Inaccessible' status after deployment. Option B is wrong because the user-assigned managed identity must be assigned to the logical SQL server, not the SQL Database server (which is a common misconception); the identity is assigned at the server level, and if it were missing, the deployment would likely fail earlier. Option C is wrong because the Key Vault firewall, when enabled, can block access even if 'Allow trusted Microsoft services' is not configured, but the most common and direct cause of 'Inaccessible' status after a successful deployment is missing key permissions on the managed identity.

Full explanation →

467

Multi-Selectmedium

You manage an Azure SQL Managed Instance that hosts a business-critical database. The compliance team requires that the instance be recoverable in a different Azure region if the primary region becomes unavailable, and that the failover be initiated manually only by authorized administrators. You need to configure a disaster recovery solution. Which two actions should you perform? (Choose two.)

Select 2 answers

A.Configure the failover policy of the failover group to Manual.

B.Configure active geo-replication between the two managed instances.

C.Enable zone redundancy on the primary managed instance.

D.Set the failover group policy to Automatic.

E.Create an auto-failover group that includes the managed instance.

AnswersA, E

Setting the failover policy to Manual ensures that failover only occurs when an administrator explicitly triggers it, matching the compliance requirement. Automatic policy would fail over without human intervention. Manual policy still allows planned and forced failover commands, so authorized personnel retain control.

Why this answer

Auto-failover groups are the supported cross-region disaster recovery feature for Azure SQL Managed Instance, and they provide a listener endpoint. To meet the compliance requirement that failover be manually initiated, the failover policy must be set to Manual. Zone redundancy is intra-region, active geo-replication is not available for managed instances, and Automatic policy would remove the required human control.

Exam trap

The trap here is assuming active geo-replication works for Azure SQL Managed Instance, when only auto-failover groups are supported for cross-region replication.

Full explanation →

468

MCQhard

You are optimizing an Azure SQL Database that uses the General Purpose service tier. The database has a high volume of small transactions and you observe wait statistics showing significant WRITELOG waits. You need to reduce WRITELOG waits for this database. What should you do?

A.Configure the database to use In-Memory OLTP.

B.Change the service tier to Business Critical.

C.Increase the database max size.

D.Enable Accelerated Database Recovery (ADR).

AnswerB

Business Critical uses local SSD for the transaction log, which significantly reduces log write latency compared to the remote storage used in General Purpose. This directly addresses WRITELOG waits by providing lower latency and higher throughput for log writes, making it the appropriate change for this scenario.

Why this answer

WRITELOG waits indicate the transaction log is a bottleneck. In the General Purpose tier, the log resides on remote Azure storage, which has higher latency than local SSD. Moving to Business Critical provides local SSD for the log, reducing latency and increasing throughput, which directly mitigates WRITELOG waits.

The other options do not address the root cause of log write latency.

Exam trap

The trap here is assuming that enabling features like ADR or In-Memory OLTP will reduce log-related waits, when they either increase log volume or do not change log I/O characteristics.

Full explanation →

469

MCQmedium

You manage an Azure SQL Database that runs an online transaction processing (OLTP) workload. Users report that transactions are slow during business hours. You query sys.dm_os_wait_stats and notice a high number of PAGEIOLATCH_SH waits. You need to reduce these waits without changing the application. What should you do?

A.Configure Query Store to capture wait statistics for the database.

B.Enable Accelerated Database Recovery (ADR) on the database.

C.Enable Read Scale-Out and redirect read-only queries to the secondary replica.

D.Increase the database's service tier to add more memory and IOPS.

AnswerD

PAGEIOLATCH_SH waits indicate that queries are waiting for data pages to be fetched from storage into the buffer pool. Increasing the service tier provides more memory (larger buffer pool) and higher IOPS, reducing the frequency and duration of these waits. This directly addresses the I/O bottleneck without modifying the application, making it the appropriate action for this scenario.

Why this answer

PAGEIOLATCH_SH waits occur when SQL Server waits for a data page to be read from disk into the buffer pool. The most direct way to reduce these waits without changing the application is to increase the service tier, which provides more memory for the buffer pool and higher storage IOPS. This reduces the need to read pages from disk and speeds up the reads that do occur.

Exam trap

The trap here is assuming that enabling a diagnostic feature like Query Store or ADR will automatically improve performance, when in fact these features do not address physical I/O bottlenecks.

Full explanation →

470

Multi-Selectmedium

Which TWO metrics should you monitor in Azure SQL Database to detect a potential memory pressure issue?

Select 2 answers

A.Page Life Expectancy

B.Memory Grants Pending

C.Average CPU percent

D.Log IO

E.Data IO

AnswersA, B

Page Life Expectancy measures how long pages remain in the buffer pool before eviction. A sustained drop below a few hundred seconds indicates the buffer pool cannot hold the working set, revealing memory pressure in Azure SQL Database.

Why this answer

Page Life Expectancy (PLE) is a key metric that indicates how long a data page remains in the buffer pool before being evicted. A consistently low PLE (e.g., below 300 seconds) signals that pages are being flushed too quickly due to memory pressure, often from insufficient buffer pool memory. Memory Grants Pending tracks the number of queries waiting for a memory grant to execute; a non-zero value indicates that the server cannot allocate enough memory to satisfy query workspace requirements, directly pointing to memory pressure.

Exam trap

The trap here is that candidates often confuse high CPU or I/O metrics with memory pressure, but CPU and I/O metrics reflect different resource bottlenecks, while PLE and Memory Grants Pending are the direct indicators of memory contention in Azure SQL Database.

Full explanation →

471

MCQeasy

You need to automate the creation of an Azure SQL Database and a corresponding server-level firewall rule to allow access from a specific IP address. The deployment must be repeatable and version-controlled. What should you use?

A.Create an ARM template that defines both the server firewall rule and the database.

B.Write a PowerShell script that uses New-AzSqlDatabase and New-AzSqlServerFirewallRule.

C.Use the Azure portal to create the database and firewall rule.

D.Use SQL Server Management Studio to script the creation.

AnswerA

ARM templates are declarative JSON files stored in source control, so the server-level firewall rule and database deploy together in one repeatable, version-controlled operation. This directly satisfies the repeatability and version-control constraints, unlike imperative scripts or portal-based creation.

Why this answer

ARM templates are declarative JSON files that define the desired state of Azure resources, including Azure SQL Database and server-level firewall rules. They support idempotent deployments, meaning the same template can be run repeatedly to ensure the environment matches the definition, and they can be version-controlled in source control. This makes them ideal for repeatable, automated deployments.

The template can include both the Microsoft.Sql/servers/firewallRules and Microsoft.Sql/servers/databases resources, ensuring they are created together.

Exam trap

DP-300 often tests the distinction between imperative scripting (like PowerShell) and declarative infrastructure-as-code (like ARM templates) for repeatable, version-controlled deployments, and candidates may incorrectly choose PowerShell because it is a common automation tool, overlooking the requirement for idempotency and version control.

How to eliminate wrong answers

Option B is wrong because while PowerShell scripts can automate deployment, they are imperative and not inherently idempotent or version-controlled; they require additional logic to handle repeatability and state, and they are not declarative templates. Option C is wrong because using the Azure portal is a manual, interactive process that is not repeatable or version-controlled, and it cannot be easily automated. Option D is wrong because SQL Server Management Studio (SSMS) is used for managing SQL Server instances and databases, but it does not provide infrastructure-as-code capabilities for Azure resource deployment, and scripting in SSMS would not automate the creation of Azure SQL Database and firewall rules in a repeatable, version-controlled manner.

Full explanation →

472

MCQmedium

You are a database administrator for a logistics company that uses Azure SQL Database. The company requires an automated task to run every night at 02:00 UTC to archive old shipment records into a separate table. You need to minimize administrative overhead and ensure the task runs reliably even if there is a transient failure. What should you implement?

A.Create an Elastic Job agent, define a target group that includes the database, and create a job with a T-SQL step that performs the archival. Schedule the job to run daily at 02:00 UTC.

B.Use Azure Logic Apps to trigger an Azure Function that executes the archival T-SQL against the database on a daily schedule.

C.Configure a SQL Agent job on the Azure SQL Database to run the archival T-SQL daily at 02:00 UTC.

D.Create an Azure Automation runbook that connects to the database and executes the archival T-SQL, and schedule it with a daily trigger.

AnswerA

Elastic Jobs are designed to automate T-SQL tasks across one or many databases in Azure SQL Database. They natively support scheduling, retry on failure, and logging. By targeting the specific database and scheduling a daily job, you meet the requirement with minimal administrative overhead and built-in reliability for transient failures.

Why this answer

Elastic Jobs provide a native, low-overhead way to schedule and run T-SQL across Azure SQL databases. They include built-in retry logic and logging, which ensures the archival task runs reliably even with transient failures. Other options either are not supported on Azure SQL Database (SQL Agent) or require more administrative effort and custom error handling (Azure Automation, Logic Apps with Functions).

Exam trap

The trap here is assuming that SQL Agent is available in Azure SQL Database, when in fact it is only available in Azure SQL Managed Instance and SQL Server on Azure VMs.

Full explanation →

473

MCQmedium

You are configuring Azure SQL Database firewall rules for a new application. The application runs on Azure VMs in the same region. To minimize latency and security risk, which approach should you use?

A.Add a firewall rule allowing all Azure IP addresses.

B.Configure a virtual network service endpoint and a virtual network firewall rule.

C.Add a firewall rule for each VM's public IP address.

D.Add a firewall rule allowing all Azure services to access the database.

AnswerB

A virtual network service endpoint routes traffic to Azure SQL over the Microsoft backbone, keeping it off the public internet. The virtual network firewall rule then permits only that subnet, minimising both latency and exposure for same-region VMs.

Why this answer

Using a virtual network service endpoint and a virtual network firewall rule allows Azure SQL Database to accept traffic only from the specific subnet hosting the application VMs, without exposing the database to the public internet. This minimizes latency by keeping traffic within the Azure backbone network and reduces the security risk by eliminating broad IP-based rules.

Exam trap

The trap here is that candidates often confuse 'allowing Azure services' (a broad, insecure setting) with the more secure virtual network service endpoint approach, or they mistakenly think adding individual VM public IPs is sufficient for security and latency.

How to eliminate wrong answers

Option A is wrong because allowing all Azure IP addresses opens the database to any Azure service in any region, vastly increasing the attack surface and violating the principle of least privilege. Option C is wrong because assigning a firewall rule for each VM's public IP address is impractical for dynamic IPs, does not leverage Azure's private network, and still exposes the database to internet-based traffic. Option D is wrong because 'allowing all Azure services' is a legacy setting that permits traffic from any Azure service (e.g., Azure Functions, Logic Apps) without subnet-level control, creating unnecessary exposure.

Full explanation →

474

MCQhard

You are responsible for automating backups of on-premises SQL Server databases to Azure Blob Storage. The solution must use the least administrative effort and provide point-in-time restore capability. What should you implement?

A.Configure SQL Server Managed Backup to Microsoft Azure.

B.Install Azure Backup Server on-premises and configure backup of SQL Server databases.

C.Use SQL Server Agent jobs to perform full, differential, and log backups to an Azure Blob Storage URL.

D.Use Azure Data Factory to copy database backups to Blob Storage.

AnswerA

SQL Server Managed Backup to Microsoft Azure automates full, differential and transaction log backups to Blob Storage with built-in scheduling and retention, requiring no custom scripts. It satisfies both constraints: least administrative effort and point-in-time restore through log chain continuity.

Why this answer

SQL Server Managed Backup to Microsoft Azure (also known as Managed Backup) is the correct choice because it provides automated, policy-based backup management with minimal administrative effort. It natively supports point-in-time restore by automatically scheduling full, differential, and transaction log backups to Azure Blob Storage, and it handles backup retention and recovery point management without requiring custom scripts or additional infrastructure.

Exam trap

The trap here is that candidates often confuse Azure Backup Server (a general-purpose backup tool) with SQL Server Managed Backup, or they assume that manually scripting backups with SQL Server Agent jobs is the simplest approach, overlooking the built-in automation and point-in-time restore capabilities of Managed Backup.

How to eliminate wrong answers

Option B is wrong because Azure Backup Server requires installing and maintaining an on-premises server, which increases administrative effort and does not provide native point-in-time restore for SQL Server without additional configuration. Option C is wrong because using SQL Server Agent jobs to manually script full, differential, and log backups to Azure Blob Storage requires significant administrative effort to create, schedule, and maintain the jobs, and it does not offer the automated retention and recovery point management that Managed Backup provides. Option D is wrong because Azure Data Factory is an ETL and data orchestration service, not a backup solution; it cannot perform SQL Server transaction log backups or provide point-in-time restore capabilities.

Full explanation →

475

MCQmedium

You are managing an Azure SQL Database that uses the Business Critical service tier. You need to ensure that the database can handle a sudden increase in transaction log write throughput without experiencing log write waits. Which factor should you primarily consider?

A.The configured backup storage redundancy.

B.The number of vCores allocated to the database.

C.The number of read replicas configured.

D.The size of the database in gigabytes.

AnswerB

In the Business Critical tier, the transaction log write throughput is primarily determined by the number of vCores. Each vCore provides a certain log write rate, and the total log throughput scales linearly with the number of vCores. Therefore, increasing vCores directly increases the maximum log write rate.

Why this answer

In the Business Critical service tier, the maximum transaction log write throughput is directly proportional to the number of vCores allocated to the database. To handle increased log write throughput, you should scale up the number of vCores. Other factors such as database size, read replicas, and backup storage redundancy do not affect the log write rate limit.

Exam trap

The trap here is assuming that database size or read replicas affect log write throughput, when in fact it is solely determined by vCore count in Business Critical.

Full explanation →

476

MCQeasy

You need to ensure that only specific Azure services can access your Azure SQL Database server. You want to allow traffic from Azure services but block all other traffic. What should you configure?

A.Set the firewall rule 'Allow Azure Services and resources to access this server' to ON and remove all other IP rules.

B.Set the firewall rule 'Allow Azure Services and resources to access this server' to OFF and add a rule for 0.0.0.0.

C.Set firewall rules to deny all IP addresses.

D.Set the firewall rule 'Allow Azure Services and resources to access this server' to ON and add a rule for 0.0.0.0.

AnswerA

Enabling that server-level firewall rule permits connections originating from Azure datacentre IP ranges, while removing all other IP rules blocks every other source. This satisfies the requirement to allow Azure services only, though it does not restrict which Azure services connect.

Why this answer

Setting the 'Allow Azure Services and resources to access this server' firewall rule to ON enables a special rule that permits traffic from all Azure datacenter IP ranges, while removing all other IP rules ensures no other external traffic can reach the server. This configuration meets the requirement to allow only Azure services and block all other traffic, as the Azure services rule is a blanket allow for Azure-originated connections without needing specific IP addresses.

Exam trap

The trap here is confusing the 'Allow Azure Services' rule with a generic 0.0.0.0 rule, leading candidates to think they need to add 0.0.0.0 to allow Azure traffic, when in fact the Azure services rule is a distinct mechanism that does not require explicit IP entries.

How to eliminate wrong answers

Option B is wrong because setting the rule to OFF and adding a rule for 0.0.0.0 does not allow Azure services; the 0.0.0.0 rule is typically used to allow all IPs, which contradicts the requirement to block non-Azure traffic. Option C is wrong because denying all IP addresses would block all traffic, including Azure services, failing to meet the requirement to allow Azure services. Option D is wrong because adding a rule for 0.0.0.0 alongside the Azure services rule would allow all IP addresses (including non-Azure traffic), which violates the requirement to block all other traffic.

Full explanation →

477

MCQmedium

You need to monitor Azure SQL Database performance over time and receive alerts when CPU usage exceeds 80%. Which Azure service should you use?

A.Automatic tuning

B.Query Performance Insight

C.Azure Monitor Alerts

D.SQL Assessment

AnswerC

Azure Monitor Alerts evaluates metric rules against Azure SQL Database telemetry and triggers notifications when thresholds such as CPU above 80% are breached. It provides the sustained monitoring and alerting the stem requires, unlike query-level tools or auditing features.

Why this answer

Azure Monitor Alerts is the correct service because it allows you to create metric-based alert rules that trigger when the CPU percentage of an Azure SQL Database exceeds a defined threshold (e.g., 80%). It continuously monitors performance metrics over time and sends notifications (e.g., email, SMS, or webhook) when the condition is met, fulfilling the requirement for both monitoring and alerting.

Exam trap

The trap here is that candidates often confuse Query Performance Insight (which shows historical query performance data) with a monitoring/alerting tool, but it lacks the ability to set proactive threshold-based alerts like Azure Monitor Alerts provides.

How to eliminate wrong answers

Option A is wrong because Automatic tuning is a feature that automatically adjusts index creation, index dropping, and query plan choices to optimize performance; it does not provide monitoring or alerting capabilities. Option B is wrong because Query Performance Insight provides detailed analysis of query performance, including resource consumption and wait statistics, but it does not support proactive alerting based on CPU thresholds. Option D is wrong because SQL Assessment evaluates the configuration and best practices of Azure SQL Database (e.g., security, performance settings) and generates a report, but it does not monitor real-time performance or send alerts.

Full explanation →

478

Multi-Selectmedium

Your company uses Azure SQL Database and needs to comply with GDPR. You must implement data classification and protection. Which TWO actions should you take? (Choose two.)

Select 2 answers

A.Configure sensitivity labels using Microsoft Purview Information Protection.

B.Implement Always Encrypted for all columns containing personal data.

C.Install the Azure Information Protection client on all client machines.

D.Enable Microsoft Defender XDR for the database server.

E.Use SQL Data Discovery & Classification in the Azure portal to classify columns containing personal data.

AnswersA, E

Sensitivity labels can be applied to classified columns and are integrated with Microsoft Purview Information Protection.

Why this answer

Microsoft Purview Information Protection provides sensitivity labels that can be applied to columns in Azure SQL Database to classify and protect personal data, meeting GDPR requirements. These labels enforce encryption, access restrictions, and visual markings, integrating with Azure SQL's data classification capabilities.

Exam trap

The trap here is confusing data classification (labeling and identifying sensitive data) with data encryption (Always Encrypted) or threat detection (Defender XDR), leading candidates to pick security features that do not fulfill the GDPR requirement for classification and labeling.

Full explanation →

479

MCQmedium

You are reviewing an ARM template snippet that configures a long-term retention (LTR) policy for an Azure SQL Database. Based on the exhibit, how long will weekly backups be retained?

A.4 weeks.

B.4 days.

C.4 months.

D.4 years.

AnswerA

The LTR policy's weekly retention setting defines how long each weekly full backup copy remains restorable. The exhibit specifies a weekly retention period of 4 weeks, so weekly backups are kept for four weeks before expiry.

Why this answer

In the ARM template snippet, the weeklyRetention property is set to 'P4W' (ISO 8601 duration format), which means 4 weeks. Therefore, weekly backups are retained for exactly 4 weeks.

Exam trap

The trap is misinterpreting the ISO 8601 duration 'P4W'. 'P4W' represents 4 weeks, not 1 month (P1M), 4 days (P4D), or 4 years (P4Y). Many candidates mistakenly assume 'W' means something else or misread the number, leading to incorrect options like 4 days, 4 months, or 4 years.

How to eliminate wrong answers

Option B is wrong because '4 days' would correspond to a duration like 'P4D' in ISO 8601, not 'P1M'. Option C is wrong because '4 months' would be 'P4M', not 'P1M'. Option D is wrong because '4 years' would be 'P4Y', not 'P1M'.

The trap is misinterpreting the ISO 8601 duration format, where 'P1M' specifically means 1 month, not 4 of any unit.

Full explanation →

480

MCQmedium

You are the database administrator for a global e-commerce company. The company runs its production SQL Server on an Azure Virtual Machine (IaaS) in the West US region. The database is mission-critical and requires a Recovery Point Objective (RPO) of 5 minutes and a Recovery Time Objective (RTO) of 30 minutes in the event of a regional disaster. The VM uses premium SSDs and is backed up daily to a Recovery Services vault with geo-redundant storage. The current backup policy takes full backups weekly, differential backups daily, and transaction log backups every 15 minutes. The VM is in an availability set for high availability within the region. During a recent regional outage simulation, the database was unavailable for 4 hours because the backups needed to be restored to a different region, and the restore process took longer than expected. You need to recommend a solution to meet the RPO and RTO requirements. What should you do?

A.Implement Azure Site Recovery to replicate the VM to a secondary region.

B.Set up log shipping to a secondary SQL Server in a different region and perform manual failover.

C.Configure a SQL Server Always On availability group with a synchronous-commit replica in a secondary Azure region.

D.Increase the frequency of transaction log backups to every 5 minutes and use geo-restore.

AnswerC

An Always On availability group with a synchronous-commit replica in a secondary Azure region replicates every transaction to the secondary before committing, so the RPO is effectively 0 within the failover policy, and automatic failover can bring the listener online in minutes. This is the only option that satisfies both a 30-minute RTO and near-zero data loss, because failover is orchestrated and scripted rather than manual and does not require restoring backups. In Azure, you must place the VMs in the same cloud service or use a load balancer to route traffic to the listener to enable seamless application redirection.

Why this answer

A SQL Server Always On availability group with a synchronous-commit replica in a secondary Azure region provides automatic failover with zero data loss (RPO of 0 seconds) and can meet the 30-minute RTO by enabling fast, automated failover to the secondary region. This solution eliminates the need for manual restore processes and ensures continuous data synchronization, directly addressing the 4-hour outage caused by slow geo-restore.

Exam trap

The trap here is that candidates confuse Azure Site Recovery (VM-level replication) with database-level replication, assuming it provides SQL Server transaction consistency, when in fact it only offers crash-consistent or app-consistent snapshots that may not meet strict RPO/RTO for SQL Server.

How to eliminate wrong answers

Option A is wrong because Azure Site Recovery replicates the entire VM at the hypervisor level, not the SQL Server database level, which can cause data inconsistency and does not guarantee SQL Server transaction-consistent failover, nor does it meet the 5-minute RPO without additional log shipping. Option B is wrong because log shipping requires manual failover and has a built-in delay (typically 15-60 minutes) between log backup and restore, making it impossible to achieve a 5-minute RPO, and manual failover cannot meet the 30-minute RTO reliably. Option D is wrong because increasing transaction log backup frequency to every 5 minutes still relies on geo-restore from a Recovery Services vault, which involves restoring from geo-redundant storage (GRS) that can take hours due to large data volumes and network latency, failing the 30-minute RTO.

Full explanation →

481

MCQmedium

You deploy a new Azure SQL Database and need to ensure that all queries are logged for performance analysis. Which configuration should you enable?

A.Data classification

B.Server-level audit

C.Diagnostic settings for SQLInsights

D.Query Store

AnswerD

Query Store continuously captures query text, execution plans and runtime statistics into internal catalog views, giving historical performance analysis without trace overhead. It satisfies the requirement that all queries be logged for later analysis, and can be enabled per database in Azure SQL Database.

Why this answer

Query Store captures a history of query execution plans, runtime statistics, and wait statistics, enabling detailed performance analysis and troubleshooting. It is the correct choice because it is specifically designed to log query-level performance data for Azure SQL Database without requiring external storage or additional configuration.

Exam trap

The trap here is that candidates often confuse server-level audit or diagnostic settings with query-level logging, but Query Store is the only feature that natively logs query execution plans and runtime statistics for performance analysis in Azure SQL Database.

How to eliminate wrong answers

Option A is wrong because Data Classification is a security feature for identifying and labeling sensitive columns, not for logging query performance. Option B is wrong because Server-level audit logs database events for compliance and security auditing, not query execution details for performance analysis. Option C is wrong because Diagnostic settings for SQLInsights send telemetry to Azure Monitor for broader monitoring, but they do not capture per-query execution plans and runtime statistics like Query Store does.

Full explanation →

482

Multi-Selectmedium

You are designing a secure environment for Azure SQL Database. Which TWO of the following are recommended practices for network security?

Select 2 answers

A.Enable the 'Allow Azure services and resources to access this server' firewall setting.

B.Use VNet service endpoints instead of Private Link to reduce costs.

C.Use Azure Private Link to connect to the database from a virtual network.

D.Disable public network access on the SQL server.

E.Add firewall rules that allow all IP addresses from your organization's IP range.

AnswersC, D

Azure Private Link provisions a private endpoint inside the virtual network, so database traffic traverses the Microsoft backbone rather than the public internet. This removes public exposure, satisfying the network security requirement for private connectivity.

Why this answer

Option C is correct because Azure Private Link (Private Endpoint) provides a private IP address for the Azure SQL logical server inside your VNet, so traffic between the VNet and the database travels over the Microsoft backbone and never exposes the database to the public internet. Option D is correct because disabling public network access on the SQL server ensures the database accepts connections only through approved private paths (such as Private Endpoints) or explicitly permitted exceptions, eliminating the broad public endpoint attack surface. Option A is not recommended because 'Allow Azure services and resources to access this server' creates a firewall exception that permits traffic from any Azure service, which is overly permissive and not a targeted network security control.

Option B is not recommended because VNet service endpoints still route traffic to the database's public endpoint and are generally considered less secure than Private Link, so cost should not drive that choice for a secure design. Option E is not recommended because allowing an entire organization IP range is a broad, IP-based rule that is weaker than private connectivity and can be bypassed if those addresses are compromised or spoofed.

Full explanation →

483

MCQeasy

You are planning the deployment of a new Azure SQL Database for a line-of-business application. The application's workload is not yet known, and you must keep the monthly cost as low as possible while still being able to scale compute resources up or down without redeploying the database. You also need to ensure that storage is billed based on the actual data and log used rather than a pre-provisioned maximum. Which purchasing model and service tier should you choose?

A.Provisioned compute tier with General Purpose service tier

B.Serverless compute tier with General Purpose service tier

C.Hyperscale service tier with provisioned compute

D.Provisioned compute tier with Business Critical service tier

AnswerB

The serverless compute tier automatically scales compute based on workload demand and bills per second of compute used, with a configurable auto-pause delay that can stop compute entirely during inactive periods. Storage is billed based on the actual data and log used rather than a pre-provisioned maximum. This directly meets the requirements of low cost for an unknown workload and the ability to scale without redeployment.

Why this answer

The serverless compute tier in the General Purpose service tier is designed for unpredictable workloads and cost optimisation. It automatically scales compute based on demand and can auto-pause during inactive periods, billing only for compute used. Storage is billed based on actual data and log usage.

Provisioned tiers reserve compute capacity and bill continuously, making them less suitable when the workload is unknown and cost must be minimised.

Exam trap

The trap here is assuming that any service tier can be combined with serverless compute; serverless is only available with the General Purpose service tier.

Full explanation →

484

MCQmedium

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. You need to ensure that the database is protected by Microsoft Defender for Cloud (formerly Azure Security Center) with advanced threat protection. What should you enable?

A.Deploy Microsoft Sentinel and connect the SQL Managed Instance

B.Enable Microsoft Defender for Cloud on the subscription or resource

C.Configure Microsoft Purview Data Map

D.Enable Azure SQL Database auditing

AnswerB

Enabling Microsoft Defender for Cloud at the subscription or resource level activates Defender for SQL, which provides advanced threat protection for Azure SQL Managed Instance. This satisfies the stem's requirement for database protection, since Defender for SQL is enabled through the Defender for Cloud plan rather than instance-level configuration.

Why this answer

Microsoft Defender for Cloud provides advanced threat protection for Azure SQL Managed Instance at the subscription or resource level. Enabling it on the subscription or the specific resource activates threat detection capabilities, including alerts for SQL injection, brute-force attacks, and anomalous access patterns, without requiring additional services.

Exam trap

The trap here is that candidates often confuse auditing (which logs events) with threat protection (which actively detects and alerts on suspicious activity), leading them to select auditing as the answer, or they mistakenly think Microsoft Sentinel is required to enable threat detection when it is actually an optional SIEM integration.

How to eliminate wrong answers

Option A is wrong because Microsoft Sentinel is a SIEM (Security Information and Event Management) solution that ingests security logs from various sources, including Defender for Cloud, but it does not directly enable advanced threat protection for SQL Managed Instance; it is an additional layer for centralized security monitoring, not the mechanism to enable threat protection. Option C is wrong because Microsoft Purview Data Map is a data governance and cataloging service for managing data lineage, classification, and discovery, not a security tool for threat detection or protection against database attacks. Option D is wrong because enabling Azure SQL Database auditing captures and logs database events for compliance and forensic analysis, but it does not provide real-time threat detection or advanced protection against malicious activities like SQL injection or anomalous access patterns.

Full explanation →

485

MCQeasy

You are configuring monitoring for an Azure SQL Database that uses the vCore purchasing model. The database is in the General Purpose service tier. You need to receive an alert when the database's CPU consumption exceeds 90 percent for 10 minutes. What should you create?

A.A smart detection alert in Application Insights.

B.A metric alert rule in Azure Monitor on the CPU percent metric.

C.An activity log alert rule on the database's administrative operations.

D.A log search alert rule based on the AzureDiagnostics table.

AnswerB

Azure Monitor metric alerts evaluate a specific metric against a threshold over a defined time window. The CPU percent metric reflects the percentage of the vCore limit being consumed. By creating a metric alert rule on CPU percent with a threshold of 90 and an aggregation granularity of 10 minutes, you can detect sustained high CPU and trigger notifications as required.

Why this answer

Azure Monitor metric alerts are the correct mechanism for alerting on a specific performance metric such as CPU percent. They allow you to set a threshold, an aggregation window, and an evaluation frequency. For a requirement to alert when CPU exceeds 90 percent for 10 minutes, a metric alert rule on the CPU percent metric with a 10-minute aggregation window is the direct and supported solution.

Exam trap

The trap here is confusing control-plane activity log alerts with performance metric alerts, or overcomplicating the solution by using log search alerts when a simple metric alert suffices.

Full explanation →

486

MCQeasy

You have an Azure SQL Database in the Business Critical tier with zone redundancy enabled. The database experiences a brief outage due to a zone failure. How does the platform automatically recover?

A.You must perform a manual failover to a secondary replica.

B.A replica in another availability zone is automatically promoted to primary.

C.The database is restored from the latest backup.

D.The database becomes read-only until the zone is restored.

AnswerB

Business Critical with zone redundancy maintains a synchronous replica in a separate availability zone. On zone failure, that replica is automatically promoted to primary, satisfying the zone-failure recovery requirement without manual intervention or data loss.

Why this answer

When zone redundancy is enabled in Business Critical, Azure SQL Database maintains replicas across multiple availability zones. If a zone fails, the platform automatically detects the failure and promotes a replica from a surviving zone to primary, typically within seconds to a minute, with no manual intervention. This is built-in HA, not disaster recovery.

Exam trap

DP-300 often tests the confusion between zone redundancy (automatic AZ failover, HA) and geo-replication/geo-restore (regional DR, manual or automatic with FOG) — the word 'zone' signals automatic local failover.

How to eliminate wrong answers

Option A is wrong because manual failover is not required — zone redundancy provides automatic failover. Option C is wrong because restoring from backup is a disaster recovery operation with significant RTO/RPO, not the automatic HA behavior for zone failure. Option D is wrong because the database does not become read-only; the platform promotes another replica to maintain read-write availability.

Full explanation →

487

MCQeasy

You need to audit all successful and failed login attempts to an Azure SQL Database. Which feature should you enable?

A.Azure SQL Auditing

B.Advanced Threat Protection

C.Transparent Data Encryption (TDE)

D.SQL Vulnerability Assessment

AnswerA

Azure SQL Auditing captures both successful and failed authentication attempts, plus the originating IP and user, writing them to a storage account, Log Analytics workspace, or Event Hubs. This directly satisfies the requirement to audit all login attempts.

Why this answer

Azure SQL Auditing is the correct feature because it tracks database events, including both successful and failed login attempts, and writes them to an audit log in your Azure Storage account, Log Analytics workspace, or Event Hubs. This allows you to monitor and review authentication activity for compliance and security analysis. Other features like Advanced Threat Protection, TDE, and Vulnerability Assessment do not capture login event logs.

Exam trap

The trap here is that candidates often confuse Advanced Threat Protection's alerting on suspicious logins with the comprehensive logging of all login attempts provided by Azure SQL Auditing, leading them to select ATP instead.

How to eliminate wrong answers

Option B (Advanced Threat Protection) is wrong because it detects anomalous activities indicating potential threats (e.g., SQL injection, brute force attacks) but does not provide a configurable audit log of all successful and failed login attempts; it alerts on suspicious patterns rather than recording every login event. Option C (Transparent Data Encryption) is wrong because it encrypts the database at rest and in transit but has no capability to log authentication events; it protects data confidentiality, not audit trails. Option D (SQL Vulnerability Assessment) is wrong because it scans for security misconfigurations and vulnerabilities (e.g., missing firewall rules, weak passwords) but does not capture or store login attempt logs; it is a periodic assessment tool, not an ongoing audit mechanism.

Full explanation →

488

MCQhard

You manage an Azure SQL Database that is part of a failover group. You need to automate the failover to the secondary region in the event of a disaster. Which approach should you use?

A.Configure the auto-failover group to automatically fail over.

B.Schedule a failover using elastic jobs.

C.Create an Azure Automation runbook that initiates the failover.

D.Use a SQL Server Agent job to trigger failover.

AnswerA

Auto-failover groups replicate databases to a secondary region and trigger failover automatically when the primary becomes unavailable, without manual intervention. This satisfies the disaster-recovery automation requirement, since the group's policy initiates the regional switch based on outage detection rather than operator action.

Why this answer

Auto-failover groups are designed to automatically fail over to the secondary region in the event of a disaster, providing built-in automation. Option C is incorrect because while an Azure Automation runbook could be used to initiate a failover manually, it is redundant since the auto-failover group already handles automatic failover. Options B and D are incorrect because elastic jobs are for management tasks like data consistency, and SQL Server Agent is not available in Azure SQL Database.

Full explanation →

489

Multi-Selectmedium

Which TWO actions are required to enable Microsoft Entra ID authentication for an Azure SQL Database?

Select 2 answers

A.Enable SQL Server authentication only.

B.Set an Microsoft Entra ID admin for the Azure SQL Server.

C.Create contained database users mapped to Microsoft Entra ID identities.

D.Assign the SQL Server Contributor role to the Entra ID users.

E.Enable Azure AD integration on the SQL server.

AnswersB, C

Setting a Microsoft Entra ID admin at the server level is mandatory before Entra authentication can function, because Azure SQL Database derives its identity provider configuration from the logical server. Without a designated admin, the server cannot validate Entra tokens, so this action directly satisfies the prerequisite for enabling Entra authentication.

Why this answer

Option B is correct because enabling Microsoft Entra ID authentication for Azure SQL Database requires provisioning a Microsoft Entra ID administrator at the Azure SQL logical server level, which establishes the trust relationship between the server and the Entra ID tenant. Option C is correct because after the Entra ID admin is set, you must create contained database users in the target database that are mapped to Entra ID identities (for example, CREATE USER [user@domain.com] FROM EXTERNAL PROVIDER), since Entra principals authenticate at the database level via contained users rather than server-level logins. Option A is incorrect because enabling SQL Server authentication only is the opposite of what is needed and does not enable Entra ID authentication.

Option D is incorrect because assigning the SQL Server Contributor RBAC role grants Azure management-plane permissions, not data-plane authentication rights inside the database. Option E is incorrect because there is no separate 'Azure AD integration' toggle to enable on the SQL server; the Entra ID admin setting itself establishes the integration.

Exam trap

The trap is thinking that enabling Entra ID authentication is a single toggle or that assigning an RBAC role like SQL Server Contributor is sufficient; candidates often miss that contained database users must be created for non-admin Entra ID principals.

Full explanation →

490

MCQhard

You are designing a secure environment for Azure SQL Managed Instance. The company requires that all database backups be encrypted using customer-managed keys stored in Azure Key Vault. Which combination of actions should you take?

A.Configure Always Encrypted with keys stored in Key Vault.

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

C.Use Azure Storage Service Encryption to encrypt the backup files.

D.Enable backup encryption using a certificate stored in the managed instance.

AnswerB

TDE with a customer-managed key in Azure Key Vault encrypts data at rest, including automated backups, using keys the customer controls. This directly satisfies the requirement that all database backups be encrypted with customer-managed keys stored in Key Vault.

Why this answer

Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault allows you to encrypt the database backup files using a key that you control. When TDE is enabled and configured with a customer-managed key (CMK) stored in Azure Key Vault, Azure SQL Managed Instance automatically encrypts backups with the same TDE protector key, meeting the requirement for customer-managed backup encryption.

Exam trap

The trap here is that candidates often confuse Always Encrypted (which protects specific columns) with TDE (which encrypts the entire database and its backups), or they assume that Azure Storage Service Encryption (SSE) can be used to meet customer-managed key requirements, when in fact SSE uses platform-managed keys by default and does not apply to backup files in the same way as TDE with CMK.

How to eliminate wrong answers

Option A is wrong because Always Encrypted is a client-side encryption technology that protects sensitive data in transit and at rest within the database, but it does not encrypt the entire database backup files; backup encryption is handled separately by TDE. Option C is wrong because Azure Storage Service Encryption (SSE) encrypts data at rest in Azure Blob Storage using platform-managed keys, not customer-managed keys, and it applies to the storage layer, not to the backup files themselves in a way that satisfies the requirement for customer-managed key control. Option D is wrong because backup encryption using a certificate stored in the managed instance would use a service-managed certificate, not a customer-managed key from Azure Key Vault, and this approach is deprecated in favor of TDE with CMK.

Full explanation →

491

MCQeasy

You are evaluating Azure SQL Database for a new application that requires the database to be isolated from other Azure tenants and to have a dedicated compute and storage resources. The application also requires support for SQL Server Agent and cross-database queries. Which Azure SQL deployment option should you choose?

A.Azure SQL Database hyperscale

B.Azure SQL Database elastic pool

C.Azure SQL Managed Instance

D.Azure SQL Database single database

AnswerC

Azure SQL Managed Instance provides a near-100% compatible SQL Server surface area, including support for SQL Server Agent, cross-database queries, and CLR. It is deployed into a dedicated subnet within an Azure virtual network, providing network isolation. It also offers dedicated compute and storage resources per instance. This meets all the stated requirements for isolation, SQL Server Agent, and cross-database queries.

Why this answer

Azure SQL Managed Instance is the only Azure SQL deployment option that provides a dedicated instance with support for SQL Server Agent, cross-database queries, and network isolation via a dedicated subnet. Single database and elastic pools do not support these features. Hyperscale is a service tier, not a deployment option, and lacks these capabilities.

Therefore, Azure SQL Managed Instance is the correct choice.

Exam trap

The trap here is assuming that Azure SQL Database single database can support SQL Server Agent and cross-database queries; these are only available in Azure SQL Managed Instance.

Full explanation →

492

Multi-Selecthard

You are optimizing an Azure SQL Database that uses the vCore purchasing model. The database is experiencing high RESOURCE_SEMAPHORE waits. You need to identify two actions that can reduce these waits. (Choose two.)

Select 2 answers

A.Scale up to a higher service tier or compute size.

B.Increase the memory-optimized filegroup size.

C.Enable read-scale out.

D.Increase the maximum degree of parallelism (MAXDOP).

E.Optimize queries to reduce their memory grant requirements.

AnswersA, E

Scaling up to a higher service tier or compute size increases the amount of memory available to the database, which directly increases the query workspace memory. This allows more queries to obtain memory grants, reducing RESOURCE_SEMAPHORE waits. This is a valid action to alleviate memory grant pressure.

Why this answer

RESOURCE_SEMAPHORE waits indicate insufficient query workspace memory. Scaling up the compute size provides more memory for grants. Optimizing queries reduces the memory needed per query.

Together, these actions lower the demand and increase the supply of memory grants, reducing waits. The other options either do not affect workspace memory or could worsen the situation.

Exam trap

The trap here is thinking that increasing MAXDOP or enabling read-scale out will reduce memory waits, when they either increase memory demand or do not address primary workload memory pressure.

Full explanation →

493

MCQmedium

A company uses Azure SQL Database and wants to automate the process of refreshing a development database from production backups weekly. Which Azure service should be used to orchestrate this process including restore and post-restore scripts?

A.Elastic Database Jobs

B.Azure Logic Apps

C.Azure Automation with PowerShell runbooks

D.Azure Data Factory

AnswerC

Azure Automation runbooks execute scheduled PowerShell that can invoke Az.Sql cmdlets to restore the production backup and then run post-restore T-SQL scripts. This satisfies the orchestration requirement spanning restore plus subsequent scripting, which a plain backup policy alone cannot perform.

Why this answer

Azure Automation with PowerShell runbooks is designed for orchestrating scheduled administrative tasks across Azure resources, including database restore operations and post-restore T-SQL scripts. It supports credentials, schedules, and integration with Azure SQL via the SqlServer module, making it the right tool for weekly refresh automation.

Exam trap

DP-300 often tests the distinction between orchestration tools — candidates confuse Elastic Database Jobs (T-SQL only) with Azure Automation (external scripting), picking the former because it sounds database-specific.

How to eliminate wrong answers

Option A is wrong because Elastic Database Jobs are for running T-SQL across a set of databases, not for orchestrating restore operations or executing external scripts. Option B is wrong because Logic Apps are workflow automation tools better suited to event-driven integrations, not scheduled database restore orchestration with PowerShell. Option D is wrong because Azure Data Factory is a data integration service for ETL/ELT pipelines, not for database restore and post-restore scripting.

Full explanation →

494

MCQmedium

You are analyzing the exhibit KQL query that queries Azure Diagnostics logs for Query Store runtime statistics. The query is intended to show average CPU time per hour for each database. However, the result shows no data for the last 24 hours, although Query Store is enabled on all databases. What is the most likely reason?

A.Query Store is not enabled on the databases.

B.The diagnostic settings are not configured to send QueryStoreRuntimeStatistics to Log Analytics.

C.The time range in the query is too narrow and excludes the last 24 hours.

D.The query syntax is incorrect and needs to use 'project' before 'summarize'.

AnswerB

The KQL query reads QueryStoreRuntimeStatistics from Log Analytics, so absence of rows means the table was never populated. Query Store being enabled locally only writes to the instance; diagnostic settings must explicitly stream that category to the workspace before any query returns data.

Why this answer

For Query Store runtime statistics to appear in Log Analytics, the Azure SQL Database diagnostic settings must explicitly route the QueryStoreRuntimeStatistics category to the Log Analytics workspace. If that category is not enabled, the KQL query will return no rows even though Query Store is enabled on the databases, because the data never reaches the workspace. This is the most likely cause of the empty result set.

Exam trap

The trap is assuming that enabling Query Store automatically makes its data available in Log Analytics — candidates conflate the feature being on with the telemetry pipeline being configured, and pick a query-syntax or time-range answer instead.

How to eliminate wrong answers

Option A is wrong because the question explicitly states Query Store is enabled on all databases, so this contradicts the given facts. Option C is wrong because the question states the query is intended to show data for the last 24 hours and returns nothing — if the time range were the issue, the query would still return older data when adjusted, but the stated problem is no data at all for the intended window. Option D is wrong because KQL does not require 'project' before 'summarize' — summarize can follow where/filter clauses directly, and the query syntax is not the issue when the underlying data is absent.

Full explanation →

495

MCQmedium

You are a database administrator for a retail company that uses Azure SQL Database with the Serverless compute tier. The database experiences unpredictable idle periods, and you want to minimize costs by automatically pausing the database when it is idle for more than 60 minutes and resuming it when a connection is attempted. However, you also need to ensure that a critical reporting job that runs every hour can connect even if the database is paused. What should you do?

A.Enable the serverless auto-pause feature with a delay of 60 minutes. No additional action is needed; the reporting job will automatically resume the database upon connection.

B.Disable auto-pause for the database and use Azure Automation to scale down the database during idle periods.

C.Use Elastic Database Jobs to keep the database active by running a lightweight query every 59 minutes.

D.Set the auto-pause delay to 0 minutes to minimize costs, and create an Azure Automation runbook to keep the database active during the reporting job.

AnswerA

Azure SQL Database serverless auto-pause with a 60-minute delay suspends the database during idle periods, cutting compute cost. Any incoming connection, including the hourly reporting job, automatically triggers resume, so no extra configuration is required to keep the job working.

Why this answer

Azure SQL Database serverless auto-pause suspends the database after a configurable idle period (minimum 15 minutes, maximum 10,080 minutes) and automatically resumes it on the next connection attempt, with only a short resume latency billed at compute rates. Setting the auto-pause delay to 60 minutes meets the cost goal, and because the hourly reporting job's connection itself triggers the resume, no additional mechanism is required. This is the native, supported behavior of the serverless tier and requires no external orchestration.

Exam trap

DP-300 often tests the misconception that auto-pause requires an external keep-alive or that a paused database cannot be woken by an incoming connection — candidates over-engineer with Automation runbooks when the native resume-on-connect behavior already solves the problem.

How to eliminate wrong answers

Option B is wrong because disabling auto-pause eliminates the primary cost-saving mechanism of the serverless tier and replaces it with a manual scaling runbook that does not actually pause compute — it just changes vCore limits. Option C is wrong because running a keep-alive query every 59 minutes defeats the purpose of auto-pause entirely, keeping the database continuously active and incurring full compute charges. Option D is wrong because an auto-pause delay of 0 minutes is not a valid configuration (minimum is 15 minutes), and using an Automation runbook to keep the database active during the reporting job contradicts the goal of pausing when idle.

Full explanation →

496

MCQeasy

Your organization requires that all Azure SQL Database backups be retained for 10 years to meet compliance requirements. Which backup retention policy should you configure?

A.Configure point-in-time restore (PITR) retention to 10 years.

B.Enable automatic tuning to optimize backups.

C.Configure long-term retention (LTR) policy.

D.Enable geo-redundant backup storage (GRS).

AnswerC

Long-term retention stores full backups in read-access geo-redundant blob storage for up to ten years, satisfying the compliance requirement. The default point-in-time and short-term policies cap at 35 days, so only LTR reaches the mandated decade.

Why this answer

Long-term retention (LTR) in Azure SQL Database allows you to retain full database backups for up to 10 years, which meets the compliance requirement for 10-year backup retention. LTR policies are configured separately from point-in-time restore (PITR) and store backups in isolated containers for extended periods, ensuring regulatory compliance.

Exam trap

The trap here is that candidates often confuse point-in-time restore (PITR) retention with long-term retention (LTR), assuming PITR can be extended to years, but Azure SQL Database caps PITR at 35 days, making LTR the only option for multi-year compliance.

How to eliminate wrong answers

Option A is wrong because point-in-time restore (PITR) retention is limited to a maximum of 35 days for Azure SQL Database, not 10 years, so it cannot satisfy the 10-year compliance requirement. Option B is wrong because automatic tuning optimizes query performance and index management, not backup retention, and has no impact on backup duration or compliance. Option D is wrong because geo-redundant backup storage (GRS) provides geographic redundancy for backups but does not extend the retention period beyond the default PITR or LTR limits; it is a storage option, not a retention policy.

Full explanation →

497

MCQhard

You are a database administrator for a global e-commerce company. The company uses Azure SQL Database for its product catalog, which is a mission-critical OLTP workload. The database is currently deployed in the West US region using the Business Critical service tier with zone redundancy enabled. The database size is 200 GB and grows at 10 GB per month. The company has a disaster recovery requirement: in the event of a regional outage, the database must be failed over to a secondary region with an RPO of less than 5 seconds and an RTO of less than 1 minute. Additionally, the secondary database must be readable to support read-heavy reporting workloads. The solution must minimize additional compute costs. You need to recommend a configuration. Which option should you choose?

A.Configure active geo-replication to a secondary database in a paired region using Business Critical tier with a readable secondary.

B.Create a failover group within the same region using Business Critical tier with a readable secondary.

C.Add a second zone-redundant replica in the same region and configure a failover group.

D.Upgrade to Hyperscale tier with zone redundancy and configure a named replica in a secondary region.

AnswerA

Active geo-replication provides low RPO and a readable secondary, meeting all requirements.

Why this answer

Active geo-replication to a secondary database in a paired region using Business Critical tier with a readable secondary meets all requirements: it provides cross-region DR with an RPO of less than 5 seconds (synchronous replication within the primary region, asynchronous to secondary), RTO of less than 1 minute (failover is fast), and the secondary is readable for reporting. Zone redundancy within the primary region does not provide cross-region DR, so options B and C are incorrect. Hyperscale tier (option D) is more expensive and does not inherently provide cross-region DR with the required RPO; named replicas add cost.

Full explanation →

498

MCQmedium

You are configuring security for an Azure SQL Database that will be accessed by multiple applications. You need to implement a solution that allows applications to connect using their own managed identities without storing credentials in connection strings. What should you configure?

A.Enable Microsoft Entra ID authentication and assign managed identities to the applications.

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

C.Configure firewall rules to allow application IP addresses.

D.Enable SQL Server authentication and create a login for each application.

AnswerA

Microsoft Entra ID authentication lets each application present its own managed identity, so Azure SQL Database validates the token issued by the platform rather than a stored password. This satisfies the stem's requirement to avoid credentials in connection strings, since the identity's secret is managed and rotated by Azure outside application code.

Why this answer

Microsoft Entra ID authentication allows Azure SQL Database to trust tokens issued by Entra ID for managed identities. By assigning a managed identity to each application, the application can acquire an access token from Azure Managed Identity endpoints and present it to the database without ever storing credentials in connection strings. This eliminates the need for passwords or connection string secrets.

Exam trap

The trap here is that candidates often confuse authentication mechanisms (like Always Encrypted or firewall rules) with identity-based access control, mistakenly thinking they eliminate credential storage when they only address encryption or network filtering.

How to eliminate wrong answers

Option B is wrong because Always Encrypted with a column master key in Azure Key Vault protects data at rest and in transit by encrypting specific columns, but it does not address authentication or eliminate the need for credentials in connection strings. Option C is wrong because configuring firewall rules to allow application IP addresses controls network access but still requires a username and password (or other authentication) in the connection string; it does not remove credential storage. Option D is wrong because enabling SQL Server authentication and creating a login for each application still requires storing a username and password in the connection string, which violates the requirement to avoid credential storage.

Full explanation →

499

MCQeasy

You manage an Azure SQL Database that is critical for a financial application. The database has a read-heavy workload, and you need to monitor and diagnose performance issues. You want to enable a feature that automatically captures detailed information about query plans and runtime statistics for later analysis. Which feature should you enable?

A.Extended Events

B.Query Store

C.Automatic tuning

D.Dynamic management views (DMVs)

AnswerB

Query Store captures query plans and runtime execution statistics automatically, persisting them in the user database. It provides historical data on query performance, enabling you to identify regressions, analyze plan changes, and force plans. For a read-heavy workload, Query Store is essential for diagnosing issues without manual tracing.

Why this answer

Query Store is the built-in feature that automatically captures query plans and runtime statistics, storing them in the database for historical analysis. It is designed for performance troubleshooting and is a prerequisite for automatic tuning. For a read-heavy workload, it provides the necessary insights to identify and resolve performance regressions.

Exam trap

The trap here is confusing automatic tuning with Query Store; automatic tuning depends on Query Store but does not itself capture the detailed performance data.

Full explanation →

500

Multi-Selecthard

You are configuring security for an Azure SQL Managed Instance. The instance will host a critical application that requires always encrypted with secure enclaves. Which TWO actions must you take to support this feature? (Choose two.)

Select 2 answers

A.Select the Intel Software Guard Extensions (Intel SGX) enclave type.

B.Configure the column master key to be stored in Azure Key Vault.

C.Configure a column master key that is enclave-enabled.

D.Enable the enclave attestation policy on the managed instance.

E.Enable Virtualization-Based Security (VBS) enclave type.

AnswersA, C

Intel SGX is the required enclave type for Always Encrypted with secure enclaves on SQL Managed Instance.

Why this answer

Always Encrypted with secure enclaves on Azure SQL Managed Instance requires the Intel Software Guard Extensions (Intel SGX) enclave type. Intel SGX is the only supported enclave technology for this feature on managed instances, providing a trusted execution environment that protects sensitive data in memory during cryptographic operations.

Exam trap

The trap here is that candidates often confuse the requirement for an enclave-enabled column master key (option C) with the need to store the key in Azure Key Vault (option B), but the key location is not a prerequisite for enclave support.

Full explanation →

501

MCQeasy

You are configuring performance monitoring for Azure SQL Managed Instance. You need to collect and analyze query performance data with minimal overhead. Which solution should you use?

A.Query Store

B.Azure Monitor metrics

C.Extended Events

D.SQL Server Profiler

AnswerA

Query Store captures query, plan and runtime statistics inside the database engine itself, with negligible overhead and no external agent. It satisfies the minimal-overhead requirement for Azure SQL Managed Instance, unlike extended events sessions or DMV polling.

Why this answer

Query Store is built-in and designed for low overhead query performance monitoring. Option B is wrong because Azure Monitor metrics provide resource-level metrics, not query-level details. Option C is wrong because Extended Events can have higher overhead and is more for custom event collection.

Option D is wrong because SQL Server Profiler is deprecated and has high overhead.

Full explanation →

502

MCQmedium

You are configuring security for an Azure SQL Database that will be accessed by multiple applications. Each application uses a separate service principal managed in Microsoft Entra ID. You need to ensure that each service principal has the minimum required permissions to access only its own set of tables. What should you implement?

A.Create a contained database user for each service principal and grant the db_owner role.

B.Create a contained database user for each service principal and grant SELECT, INSERT, UPDATE, DELETE on specific tables.

C.Create a server-level login for each service principal and assign db_datareader role in the database.

D.Configure a server-level firewall rule for each service principal IP address.

AnswerB

Contained database users authenticate directly against Microsoft Entra ID at the database layer, so each service principal maps to its own user. Granting table-scoped DML rights enforces least privilege, satisfying the requirement that each application reaches only its own tables.

Why this answer

It creates a contained database user for each service principal (mapped to the Microsoft Entra ID identity) and grants only the specific table-level permissions (SELECT, INSERT, UPDATE, DELETE) required for that application. This follows the principle of least privilege by avoiding broad database roles and ensuring each service principal can only access its own set of tables.

Exam trap

The trap here is that candidates often confuse server-level logins with contained database users for Microsoft Entra ID principals, or mistakenly think that broad roles like db_datareader satisfy the 'minimum required permissions' requirement when the question explicitly demands table-level scoping.

How to eliminate wrong answers

Option A is wrong because granting the db_owner role provides full administrative control over the entire database, far exceeding the minimum required permissions and violating least privilege. Option C is wrong because server-level logins are not supported for Microsoft Entra ID service principals; you must use contained database users, and db_datareader grants read access to all tables, not just specific ones. Option D is wrong because firewall rules control network access at the server level, not permissions to specific tables, and service principals authenticate via Microsoft Entra ID tokens, not IP addresses.

Full explanation →

503

MCQmedium

You are configuring a new Azure SQL Database. The application that will use the database requires that all connections be encrypted and that the database be protected against SQL injection attacks. You also need to minimize administrative effort for monitoring and threat detection. What should you implement?

A.Enable Dynamic Data Masking and configure Azure Monitor alerts for failed logins.

B.Configure Always Encrypted with column encryption and enable auditing to a storage account.

C.Enable Transparent Data Encryption (TDE) and configure a firewall rule to allow only the application's IP address.

D.Enforce SSL connections and enable Azure Defender for SQL (Advanced Threat Protection) with vulnerability assessment.

AnswerD

Enforcing SSL connections ensures that all data in transit is encrypted. Azure Defender for SQL provides advanced threat detection, including alerts for potential SQL injection attacks, and vulnerability assessment helps identify and remediate security misconfigurations. This combination addresses encrypted connections, SQL injection protection, and minimizes monitoring effort through automated alerts, making it the correct solution for the scenario.

Why this answer

Enforcing SSL connections ensures encrypted communication, and Azure Defender for SQL provides advanced threat detection, including SQL injection alerts, along with vulnerability assessment. This combination directly meets the requirements for encrypted connections, SQL injection protection, and reduced monitoring effort. Other options either address different security aspects or do not provide the necessary threat detection and connection encryption.

Exam trap

The trap here is confusing data-at-rest encryption with connection encryption and assuming that firewall rules alone can prevent SQL injection.

Full explanation →

504

MCQmedium

You are configuring security for an Azure SQL Database. The security policy requires that all connections to the database must be encrypted and that the encryption keys must be managed by your organization. You need to implement Transparent Data Encryption (TDE) with a customer-managed key (CMK) stored in Azure Key Vault. What should you do first?

A.Create an Azure Key Vault and grant the Azure SQL logical server's managed identity the get, wrapKey, and unwrapKey permissions on the key.

B.Configure a firewall rule to allow connections from your organization's IP addresses.

C.Create a database master key (DMK) in the master database of the Azure SQL logical server.

D.Enable TDE on the database using the default service-managed key.

AnswerA

To use a customer-managed key for TDE, you must first create an Azure Key Vault, generate or import a key, and then grant the Azure SQL logical server's managed identity the necessary permissions (get, wrapKey, unwrapKey) to access that key. This allows the server to use the key for TDE operations. This is the foundational step before configuring TDE to use the key.

Why this answer

For TDE with customer-managed keys in Azure SQL Database, the first step is to set up Azure Key Vault and grant the logical server's managed identity the required permissions to access the key. This enables the server to use the key for encryption. Once this is done, you can configure TDE to use the customer-managed key.

The other options either do not meet the key management requirement or are not applicable.

Exam trap

The trap here is thinking that you must first enable TDE with a service-managed key before switching to a customer-managed key, or that on-premises concepts like database master keys apply directly to Azure SQL Database TDE.

Full explanation →

505

MCQmedium

You are monitoring an Azure SQL Database that hosts a financial application. You notice that the average DTU consumption is 20%, but occasionally spikes to 95% for 5-minute intervals. Users report slow response times during these spikes. You need to ensure consistent performance without over-provisioning resources. What should you do?

A.Migrate the database to the Hyperscale service tier.

B.Scale the database to a higher service tier to absorb the spikes.

C.Enable Query Store and use the Regressed Queries feature to find slow queries.

D.Identify and optimize the queries running during the spike periods, possibly rescheduling a heavy ETL job.

AnswerD

The intermittent 95% spikes with a 20% baseline indicate workload-driven contention, not insufficient capacity. Tuning the offending queries and rescheduling the heavy ETL job removes the burst at source, avoiding the cost of scaling up for brief peaks.

Why this answer

The issue is not a consistent resource shortage but periodic spikes caused by specific queries, likely from a heavy ETL job. By identifying and optimizing those queries or rescheduling the job, you can eliminate the spikes without permanently scaling up resources, which would waste cost and capacity. This aligns with the DP-300 focus on performance tuning and resource optimization rather than blind scaling.

Exam trap

The trap here is that candidates assume spikes always require scaling up (Option B) or migrating to a higher tier (Option A), but the DP-300 exam emphasizes that optimization and scheduling are often more cost-effective than over-provisioning.

How to eliminate wrong answers

Option A is wrong because Hyperscale is designed for large, highly scalable databases with fast recovery and read scale-out, not for handling occasional DTU spikes; it would over-provision and increase cost unnecessarily. Option B is wrong because scaling to a higher service tier permanently increases DTU capacity to absorb spikes that occur only 5 minutes at a time, leading to over-provisioning and wasted cost for the 80% of time when DTU is at 20%. Option C is wrong because Query Store and Regressed Queries help identify performance regressions over time, but the question already indicates the spikes are caused by known periodic heavy workloads (e.g., ETL), so the immediate action is to optimize or reschedule those queries, not just monitor them.

Full explanation →

506

MCQhard

You administer an Azure SQL Database that contains a table named dbo.Employees with columns for Social Security Number and salary. Company policy requires that support staff querying the table see only the last four digits of the Social Security Number and a masked salary value, while the payroll application, which connects with a different login, must see the actual values. You need to implement this with the least administrative effort and without changing the application queries. What should you do?

A.Apply Dynamic Data Masking rules to the SSN and salary columns and grant the support staff login SELECT on the table.

B.Encrypt the SSN and salary columns with Always Encrypted and distribute the column master key only to the payroll application.

C.Create a view that returns masked values and grant support staff SELECT on the view instead of the table.

D.Create a row-level security policy that filters rows based on the support staff login.

AnswerA

Dynamic Data Masking applies masking at query time for users without the UNMASK permission, while privileged logins retain full visibility. Because masking is transparent to the query text and enforced in the engine, support staff see masked values and the payroll login sees real values without any application changes.

Why this answer

Dynamic Data Masking is designed for exactly this scenario: it masks column values for users who lack the UNMASK permission while leaving the data intact and visible to privileged logins. Because masking is applied by the engine during query execution, no application changes are needed, and the payroll login with UNMASK sees the real values.

Exam trap

The trap here is choosing Always Encrypted or row-level security to limit visibility, when only Dynamic Data Masking provides partial value masking without changing queries.

Full explanation →

507

MCQhard

You have an Azure SQL Database that uses automatic tuning. You notice that a forced plan regression is causing performance degradation. You need to revert to the previous plan and prevent the automatic tuning from forcing the same plan again. What should you do?

A.Reindex the tables involved in the query.

B.Create a plan guide for the previous plan and then disable the automatic tuning recommendation for that query.

C.Disable automatic tuning for the database.

D.Run DBCC FREEPROCCACHE to clear the plan cache.

AnswerB

Creating a plan guide pins the previous execution plan, satisfying the requirement to revert the regressed query, while disabling the automatic tuning recommendation prevents the automatic tuning feature from re-forcing the same plan. Together these two actions address both the immediate regression and its recurrence.

Why this answer

When automatic tuning forces a plan that causes regression, the correct remediation is to create a plan guide that pins the previous (good) plan for that specific query, then disable the automatic tuning recommendation for that query so Azure SQL doesn't re-force the bad plan. This is a targeted fix that preserves automatic tuning for other queries.

Exam trap

DP-300 often tests whether candidates choose the nuclear option (disable automatic tuning entirely) when a targeted fix (plan guide + disable recommendation for that query) is the correct, least-disruptive solution.

How to eliminate wrong answers

Option A is wrong because reindexing does not address plan forcing — it may change statistics and plans unpredictably, but it does not revert or prevent the forced plan. Option C is wrong because disabling automatic tuning for the entire database is overly broad and loses the benefits of automatic tuning for all other queries; the question asks to prevent forcing the same plan again for this query, not to disable tuning globally. Option D is wrong because DBCC FREEPROCCACHE clears the entire plan cache, causing a temporary performance hit and not preventing automatic tuning from re-forcing the bad plan.

Full explanation →

508

MCQhard

Your Azure SQL Database is experiencing high DTU consumption. You need to identify the top resource-consuming queries. What should you do?

A.Use the Query Store reports in the Azure portal

B.Use SQL Server Profiler

C.Create an Extended Events session to capture query events

D.Query sys.dm_exec_query_stats

AnswerA

Query Store captures per-query runtime statistics and execution plans, letting you rank top resource consumers by CPU, duration or reads. This directly identifies the highest DTU-consuming queries, which the stem requires, without needing extended events or manual DMV polling.

Why this answer

Query Store in Azure SQL Database provides built-in reports to identify top resource-consuming queries by CPU, IO, and duration, making it the easiest and most direct method. Option B (SQL Server Profiler) is not supported in Azure SQL Database. Option C (Extended Events) is more complex and not the simplest approach for this task.

Option D (sys.dm_exec_query_stats) can be used but lacks persistent historical data and is more effort than using Query Store reports.

Full explanation →

509

MCQhard

You are designing a data platform for a global SaaS company. The application requires a relational database that can handle up to 50 TB of data and supports high-frequency inserts. The database must be able to scale compute independently from storage and provide fast restores (within minutes) for large databases. Which Azure SQL offering should you choose?

A.Azure SQL Database Business Critical tier

B.Azure SQL Database Standard tier

C.Azure SQL Database Hyperscale tier

D.Azure SQL Managed Instance Business Critical

AnswerC

Hyperscale separates compute from storage and uses snapshot-based backups, so it scales independently and restores 50 TB databases in minutes rather than hours. Its architecture also sustains the high-frequency insert workload the stem requires, unlike other Azure SQL tiers.

Why this answer

The Hyperscale tier of Azure SQL Database is designed for workloads up to 100 TB, decouples compute from storage (allowing independent scaling), and uses a log-based architecture with page servers to enable fast restores (typically within minutes, regardless of database size). This directly matches the requirements for 50 TB data, high-frequency inserts, independent compute/storage scaling, and rapid restore times.

Exam trap

The trap here is that candidates often confuse the Business Critical tier's high availability and performance features with the ability to handle large data volumes and fast restores, overlooking the strict 4 TB size limit and lack of compute/storage decoupling.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database Business Critical tier has a maximum size of 4 TB, far below the required 50 TB, and does not support independent compute/storage scaling. Option B is wrong because Azure SQL Database Standard tier is limited to 1 TB and is designed for lower performance workloads, not high-frequency inserts or fast restores. Option D is wrong because Azure SQL Managed Instance Business Critical has a maximum size of 16 TB, insufficient for 50 TB, and while it offers some scaling, it does not provide the same level of compute/storage decoupling or sub-minute restore capabilities as Hyperscale.

Full explanation →

510

Drag & Dropmedium

Drag and drop the steps to troubleshoot a high CPU usage issue in Azure SQL Database in the correct order.

Drag or tap steps into the slots.

Steps

Order

1Step 1

2Step 2

3Step 3

4Step 4

Why this order

Start by identifying high CPU queries, analyze plans, check for missing indexes, implement fixes, then monitor.

Full explanation →

511

Multi-Selecteasy

Which TWO of the following are native options to automate index maintenance on Azure SQL Database? (Select exactly two.)

Select 2 answers

A.Create Elastic Database Jobs that run index maintenance T-SQL scripts.

B.Use Azure Automation PowerShell runbooks to invoke index rebuilds.

C.Enable automatic tuning with 'CREATE INDEX' and 'DROP INDEX' options.

D.Schedule SQL Agent jobs with ALTER INDEX statements.

E.Use Azure Data Factory to copy data and rebuild indexes.

AnswersA, C

Elastic Database Jobs execute T-SQL across Azure SQL Database instances on a schedule, satisfying the requirement for a native automation mechanism. Unlike SQL Server Agent, which Azure SQL Database lacks, elastic jobs run index rebuild and reorganise scripts directly against the target databases without external tooling.

Why this answer

Option A is correct because Elastic Database Jobs are a native Azure SQL Database feature that lets you define and schedule T-SQL scripts (such as ALTER INDEX ... REBUILD/REORGANIZE) across one or many databases without needing an external orchestrator. Option C is correct because Azure SQL Database's built-in automatic tuning can automatically create and drop indexes based on workload analysis, which is a native, server-side index maintenance capability.

Option B is not native to Azure SQL Database: Azure Automation is a separate Azure service, and its PowerShell runbooks must connect externally to run T-SQL. Option D is wrong because SQL Server Agent is not available in Azure SQL Database (it exists in Azure SQL Managed Instance and SQL Server on Azure VMs). Option E is wrong because Azure Data Factory is a data integration/ETL service, not an index maintenance mechanism.

Exam trap

DP-300 often tests the misconception that SQL Agent is available in Azure SQL Database — it is not; candidates must recognize that Elastic Jobs and automatic tuning are the native automation paths, while SQL Agent belongs to Managed Instance or SQL Server on VMs.

Full explanation →

512

Multi-Selecthard

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

Select 3 answers

A.Failover group listener.

B.At least one database on the primary server.

C.Secondary server.

D.Primary server.

E.An elastic pool.

AnswersB, C, D

Failover groups replicate databases, so at least one database must exist on the primary server before the group can be created. Without a database to replicate, there is nothing for the secondary server to receive.

Why this answer

Options B, C, and D are correct. A failover group requires a primary server, a secondary server, and at least one database on the primary server. Option A is incorrect because the failover group listener is automatically created and does not need to be separately configured.

Option E is incorrect because an elastic pool is optional; failover groups can contain individual databases or elastic pools.

Full explanation →

513

MCQeasy

You need to audit all schema changes in an Azure SQL Database and store the audit logs in a storage account for long-term retention. What should you enable?

A.Azure SQL Auditing with storage account destination.

B.Advanced Threat Protection with email alerts.

C.Query Store with 'Data Flush Interval' set to 1 minute.

D.SQL Vulnerability Assessment with recurring scans.

AnswerA

Azure SQL Auditing captures schema changes such as CREATE, ALTER and DROP through the database audit specification, and the storage account destination provides the long-term retention the scenario requires. This satisfies both the auditing and retention constraints.

Why this answer

Azure SQL Auditing with a storage account destination is the correct choice because it tracks database events, including schema changes (DDL operations), and writes audit logs to Azure Blob Storage for long-term retention. This meets the requirement to audit all schema changes and store logs durably, as storage accounts provide configurable retention policies.

Exam trap

The trap here is that candidates confuse Azure SQL Auditing with other security features like Advanced Threat Protection or Vulnerability Assessment, assuming they all capture schema changes, but only Auditing provides granular event logging with a storage destination for long-term retention.

How to eliminate wrong answers

Option B is wrong because Advanced Threat Protection (ATP) detects anomalous activities (e.g., SQL injection, brute-force attacks) and sends email alerts, but it does not log schema changes or provide long-term audit storage. Option C is wrong because Query Store captures query performance data (execution plans, runtime statistics) with a configurable data flush interval, not schema change events or audit logs. Option D is wrong because SQL Vulnerability Assessment performs periodic scans to identify security misconfigurations and vulnerabilities, but it does not audit schema changes or store logs in a storage account.

Full explanation →

514

Multi-Selecthard

You are troubleshooting a performance issue on an Azure SQL Database. The database is experiencing high PAGELATCH_EX waits. Which TWO measures can help reduce these waits?

Select 2 answers

A.Use a hash distribution or round-robin distribution in a table design

B.Increase MAXDOP for the queries

C.Partition the table to distribute inserts

D.Use OPTIMIZE_FOR_SEQUENTIAL_KEY index option

E.Enable snapshot isolation level

AnswersC, D

PAGELATCH_EX waits arise from concurrent inserts contending on the last page of an ascending index. Partitioning the table across multiple filegroups or partition ranges spreads inserts over several hot pages, reducing that contention and therefore the exclusive page-latch waits the database is experiencing.

Why this answer

Option C is correct because PAGELATCH_EX waits are commonly caused by last-page insert contention on monotonically increasing keys (e.g., IDENTITY), and partitioning the table across multiple partitions/files spreads those inserts across different pages, eliminating the hot last-page latch. Option D is correct because the OPTIMIZE_FOR_SEQUENTIAL_KEY index option, introduced in SQL Server 2019/Azure SQL Database, specifically mitigates last-page insert PAGELATCH_EX contention by managing the insert into the index's last page more efficiently. Option A is not applicable because hash or round-robin distribution is a dedicated SQL pool (formerly SQL DW) table design concept, not a remedy for PAGELATCH_EX in Azure SQL Database.

Option B is wrong because increasing MAXDOP does not reduce page latch contention and can even worsen it by increasing concurrent insert pressure. Option E is wrong because enabling snapshot isolation addresses blocking/locking (LCK waits) and read-write contention, not PAGELATCH_EX waits on data pages.

Exam trap

DP-300 often tests the confusion between PAGELATCH_EX and PAGELATCH_SH or LCK waits, and may include distractors like snapshot isolation which addresses locking, not latching.

Full explanation →

515

MCQeasy

You have an Azure SQL Database named SalesDB. You need to grant a user named 'ReportingUser' the ability to read all data in the Sales schema but not modify any data. You want to follow the principle of least privilege. What should you do?

A.Grant SELECT permission on the Sales schema to ReportingUser.

B.Add ReportingUser to the db_datareader role.

C.Grant CONTROL permission on the Sales schema to ReportingUser.

D.Add ReportingUser to the db_owner role.

AnswerA

Granting SELECT on the Sales schema specifically allows ReportingUser to read all data in that schema while denying access to other schemas. This follows the principle of least privilege by limiting permissions to only what is needed. It is the most precise way to meet the requirement.

Why this answer

Granting SELECT on the Sales schema directly to ReportingUser provides read-only access to only that schema, adhering to least privilege. The other options grant broader permissions that either include unnecessary access to other schemas or allow data modification, which is not required.

Exam trap

The trap here is defaulting to built-in roles like db_datareader, which grant access to the entire database rather than a specific schema.

Full explanation →

516

MCQmedium

You are a database administrator for a multinational corporation that uses Azure SQL Managed Instance to host multiple databases for different business units. The security policy requires that all connections to the managed instance must use encrypted connections (TLS 1.2 or higher). Additionally, the company wants to minimize the attack surface by restricting network access. You need to configure the managed instance to enforce encrypted connections and block all public internet traffic. What should you do?

A.Set the 'Minimal TLS Version' property to 1.2 and set 'Public data endpoint' to 'Disabled'

B.Enable a private endpoint and set the 'Minimal TLS Version' property to 1.0

C.Disable the public endpoint and enable a service endpoint for the virtual network

D.Configure a server-level firewall rule to allow only specific IP addresses and set the 'Minimal TLS Version' property to 1.2

AnswerA

Setting Minimal TLS Version to 1.2 enforces TLS 1.2 or higher on every connection, satisfying the encryption policy. Disabling the public data endpoint removes the public internet-facing endpoint, so only private endpoints or internal VNet connections reach the managed instance, directly meeting the attack-surface restriction.

Why this answer

Setting the 'Minimal TLS Version' property to 1.2 enforces that all connections use TLS 1.2 or higher, meeting the encryption requirement. Disabling the 'Public data endpoint' blocks all public internet traffic, ensuring that only traffic from within the virtual network can reach the managed instance. This combination directly satisfies both security policy goals without relying on additional components like private endpoints or firewall rules.

Exam trap

The trap here is that candidates often confuse disabling the public endpoint with using a private endpoint or firewall rules, failing to realize that both the TLS version enforcement and public endpoint disablement are required to fully meet the security policy.

How to eliminate wrong answers

Option B is wrong because setting 'Minimal TLS Version' to 1.0 allows connections using TLS 1.0, which is not compliant with the requirement for TLS 1.2 or higher, and enabling a private endpoint alone does not block public internet traffic unless the public endpoint is also disabled. Option C is wrong because disabling the public endpoint and enabling a service endpoint does not enforce TLS 1.2; service endpoints only secure traffic to Azure services within the virtual network but do not control the TLS version used. Option D is wrong because configuring a server-level firewall rule to allow only specific IP addresses still leaves the public endpoint enabled, which exposes the managed instance to the internet and does not minimize the attack surface as required.

Full explanation →

517

MCQhard

You are planning to deploy an Azure SQL Managed Instance to host several databases migrated from an on-premises SQL Server. The applications use cross-database queries, SQL Server Agent jobs, and CLR assemblies. You need to ensure the instance can support these features and that the network configuration allows the instance to be reached from an on-premises network over a site-to-site VPN. What should you do first?

A.Create a virtual network with a gateway subnet and configure a site-to-site VPN gateway, then deploy the instance into the same subnet as the gateway.

B.Create a virtual network with a subnet delegated to Microsoft.Sql/managedInstances and configure a private endpoint for the instance.

C.Create a virtual network with a subnet delegated to Microsoft.Sql/managedInstances and enable public endpoint access on the instance.

D.Create a virtual network with a dedicated subnet delegated to Microsoft.Sql/managedInstances and configure a site-to-site VPN gateway.

AnswerD

Azure SQL Managed Instance must be deployed into a dedicated subnet delegated to Microsoft.Sql/managedInstances, and the subnet must not contain other resources. A site-to-site VPN gateway in the same virtual network or a peered network provides connectivity from on-premises. This configuration supports cross-database queries, SQL Server Agent, and CLR because those are built-in managed instance capabilities.

Why this answer

Azure SQL Managed Instance must be placed in a dedicated subnet delegated to Microsoft.Sql/managedInstances, and that subnet cannot host other resources. To connect from on-premises over a site-to-site VPN, you also need a VPN gateway in the virtual network or a peered network. The managed instance natively supports cross-database queries, SQL Server Agent, and CLR, so the network and subnet configuration is the critical first step.

Exam trap

The trap here is assuming that a managed instance can share a subnet with a VPN gateway, when it requires its own dedicated delegated subnet.

Full explanation →

518

MCQmedium

You have an Azure SQL Managed Instance that is experiencing performance degradation. You suspect a query is causing excessive blocking. You need to identify the blocking chain and the resource holding the lock. Which DMV should you query?

A.sys.dm_exec_requests

B.sys.dm_tran_locks and sys.dm_os_waiting_tasks

C.sys.dm_exec_query_stats

D.sys.dm_tran_active_snapshot_database_transactions

AnswerB

sys.dm_tran_locks reveals the granted and requested locks with their owning sessions, while sys.dm_os_waiting_tasks exposes the blocking chain through blocking_session_id. Together they satisfy the stem's need to identify both the blocking chain and the lock holder.

Why this answer

To identify the blocking chain and the specific resource holding the lock, you need to combine lock metadata with wait information. sys.dm_tran_locks shows current locks and their resource types (e.g., RID, KEY, PAGE, OBJECT), while sys.dm_os_waiting_tasks reveals which sessions are waiting on those locks and the blocking session ID. Together, these DMVs allow you to trace the blocking chain from the blocked session back to the blocker and pinpoint the exact resource causing contention.

Exam trap

The trap here is that candidates often pick sys.dm_exec_requests (Option A) because it shows wait_type and blocking_session_id, but it lacks the granular lock resource information (e.g., RID, KEY) that sys.dm_tran_locks provides, which is essential for identifying the exact resource holding the lock.

How to eliminate wrong answers

Option A is wrong because sys.dm_exec_requests shows currently executing requests and their wait types, but it does not provide detailed lock resource information (e.g., which specific row or key is locked) needed to identify the exact resource holding the lock. Option C is wrong because sys.dm_exec_query_stats aggregates query performance metrics (CPU, I/O, duration) over time and does not contain real-time lock or blocking chain data. Option D is wrong because sys.dm_tran_active_snapshot_database_transactions is specific to snapshot isolation level transactions and tracks version store usage, not blocking chains or lock resources.

Full explanation →

519

MCQhard

Your organization has Azure SQL Database with several databases. You need to implement a solution that allows a junior DBA to view the security logs for failed logins but not modify any security settings. What is the minimum role assignment needed on the logical server?

A.Assign the SQL Security Manager role.

B.Assign the Reader role.

C.Assign the Contributor role.

D.Assign the SQL DB Contributor role.

AnswerA

Incorrect because the SQL Security Manager role allows updating security policies, which is more than read-only access and violates the requirement to not modify security settings.

Why this answer

The SQL Security Manager role is the minimum built-in Azure RBAC role that grants read access to SQL security-related logs, including failed login audit logs, at the logical server scope. While it can also manage security policies, it is the least-privileged built-in role that satisfies the requirement to view the failed login security logs. The Reader role only provides read access to Azure resource metadata and does not expose SQL security logs.

Exam trap

Candidates may assume the generic Reader role is enough for any read-only task, but SQL security logs require the SQL Security Manager role, which is the minimum role that grants visibility into those logs.

How to eliminate wrong answers

Option B is wrong because the Reader role provides read-only access to all resources but does not include the specific permissions to view security logs like failed logins, which require the SQL Security Manager role. Option C is wrong because the Contributor role grants full management access to all resources, including the ability to modify security settings, which exceeds the requirement of view-only access. Option D is wrong because the SQL DB Contributor role allows management of databases but not the logical server's security logs, and it also includes permissions to modify database configurations, which is more than needed.

Full explanation →

520

MCQhard

The query returns a list of query hashes with high average duration. You need to identify which queries are most likely causing CPU pressure. What additional metric should you include?

A.Include wait_stats to see blocking.

B.Include count_executions to see frequency.

C.Include avg_logical_reads to see I/O consumption.

D.Include avg_cpu_time to measure CPU usage.

AnswerD

Query Store's avg_cpu_time exposes CPU consumption per query hash, directly revealing which queries drive CPU pressure. Duration alone can reflect waits or blocking, so adding avg_cpu_time isolates the actual CPU-heavy offenders the stem asks for.

Why this answer

To identify queries causing CPU pressure, you need to measure CPU usage per query. The `avg_cpu_time` metric directly indicates how much CPU time each query consumes on average, making it the most relevant additional metric.

Exam trap

The trap is assuming that high logical reads or wait stats directly indicate CPU pressure, when CPU time is the direct measure.

How to eliminate wrong answers

Option A is wrong because wait_stats shows blocking and waits, which may indicate CPU pressure indirectly but does not directly measure CPU usage per query. Option B is wrong because count_executions shows frequency, which can contribute to total CPU but does not indicate per-execution CPU cost. Option C is wrong because avg_logical_reads measures I/O consumption, not CPU usage.

Full explanation →

521

MCQhard

You have an Azure SQL Database that needs to be accessed by an application running on an Azure VM. The VM is in a different subscription. You want to minimize administrative overhead and ensure secure connectivity without exposing the database to the public internet. What should you do?

A.Set up a site-to-site VPN between the VM's VNet and the SQL Database's VNet.

B.Use a VNet service endpoint for Azure SQL Database in the VM's VNet.

C.Create a private endpoint for the SQL Database in the VM's VNet.

D.Configure a firewall rule to allow the VM's public IP address.

AnswerC

A private endpoint provisions an Azure NIC with a private IP address from the VM's VNet to the SQL Database logical server, making the database appear as a native resource inside that VNet. Traffic between the VM and the database flows entirely over the Microsoft backbone and never traverses the public internet, even though the SQL Database can reside in a different subscription via Private Link. The private endpoint is deployed in the VM's VNet while the connection to the SQL Database resource is approved, enabling cross-subscription private connectivity with no public exposure.

Why this answer

A private endpoint assigns the Azure SQL Database a private IP address from the VM's VNet, enabling secure connectivity over the Microsoft backbone without exposing the database to the public internet. This minimizes administrative overhead as it does not require VPN gateways or complex routing, and it works across subscriptions by linking the private endpoint to the VM's VNet.

Exam trap

The trap here is that candidates often confuse VNet service endpoints with private endpoints, assuming service endpoints provide the same level of isolation, but service endpoints still rely on the public endpoint of Azure SQL and do not remove public exposure.

How to eliminate wrong answers

Option A is wrong because a site-to-site VPN requires a VPN gateway in both VNets, which adds significant administrative overhead and cost, and is unnecessary when a simpler private endpoint can provide cross-subscription connectivity. Option B is wrong because a VNet service endpoint does not assign a private IP to the SQL Database; it still routes traffic over the public endpoint of Azure SQL, and the database's firewall must allow the VM's VNet, which does not provide the same level of isolation as a private endpoint. Option D is wrong because exposing the VM's public IP address in a firewall rule directly exposes the database to the public internet, violating the requirement for secure connectivity without public exposure.

Full explanation →

522

MCQeasy

You are a database administrator for a retail company that uses Azure SQL Database. The security team wants to prevent SQL injection attacks by ensuring that all application queries use parameterized statements. Which built-in Azure feature should you enable to help detect and alert on potential SQL injection attempts?

A.Enable auditing on the database

B.Enable data discovery and classification

C.Enable Microsoft Defender for SQL

D.Enable SQL vulnerability assessment

AnswerC

Microsoft Defender for SQL detects anomalous query patterns and injection attempts, raising alerts in Microsoft Defender for Cloud. It satisfies the requirement to detect and alert on potential SQL injection, rather than merely preventing it through parameterisation, which is an application-side coding practise outside Azure SQL Database's built-in controls.

Why this answer

Microsoft Defender for SQL includes advanced threat detection capabilities that continuously monitor database activity for anomalous patterns, including SQL injection attempts. When enabled, it analyzes query execution patterns and can alert on suspicious queries that deviate from parameterized statement usage, directly addressing the security team's requirement to detect and alert on potential SQL injection attacks.

Exam trap

The trap here is that candidates confuse passive auditing or assessment features (which log or scan for vulnerabilities) with active threat detection that monitors and alerts on real-time attack patterns like SQL injection.

How to eliminate wrong answers

Option A is wrong because auditing records database events for compliance and forensic analysis but does not actively detect or alert on SQL injection patterns in real time. Option B is wrong because data discovery and classification identifies sensitive columns and recommends classification labels, but it has no mechanism to analyze query patterns or detect injection attempts. Option D is wrong because SQL vulnerability assessment scans for misconfigurations and missing patches, not for active injection attempts or anomalous query behavior.

Full explanation →

523

Multi-Selecthard

You manage an Azure SQL Database that experiences high PAGELATCH_EX waits on tempdb during peak transaction processing. You need to reduce these waits. Which two actions should you perform? (Choose two.)

Select 2 answers

A.Enable Accelerated Database Recovery (ADR).

B.Switch to the Business Critical service tier.

C.Use memory-optimized tempdb metadata.

D.Increase the number of tempdb data files.

E.Increase the database's max size.

AnswersC, D

Memory-optimized tempdb metadata removes the latch contention on system pages that track temp object metadata. This significantly reduces PAGELATCH_EX waits in workloads with heavy temp table usage. It is a recommended configuration for Azure SQL Database when tempdb contention is observed.

Why this answer

PAGELATCH_EX waits on tempdb indicate contention on allocation pages during concurrent temp object creation. Adding more tempdb data files spreads the allocation load, and enabling memory-optimized tempdb metadata removes latch contention on metadata pages. These two actions directly target the cause.

Other options like ADR or scaling tiers do not address the allocation bottleneck.

Exam trap

The trap here is assuming that any performance improvement like switching to Business Critical or enabling ADR will fix tempdb contention, when the issue specifically requires addressing allocation page contention through file count or memory-optimized metadata.

Full explanation →

524

MCQeasy

You are managing an Azure SQL Database that supports a reporting application. Users report that queries are slow during business hours. You suspect that the database is experiencing CPU pressure. Which metric should you monitor to confirm this?

A.DTU percentage

B.CPU percentage

C.Log write percentage

D.Data IO percentage

AnswerB

CPU percentage is a metric available in Azure Monitor for Azure SQL Database that shows the percentage of CPU used by the database. A consistently high CPU percentage directly indicates CPU pressure. This is the most specific and direct metric to confirm that slow queries are due to CPU bottlenecks, making it the correct choice.

Why this answer

CPU percentage is the direct metric for CPU utilization in Azure SQL Database. Unlike DTU percentage, which aggregates multiple resources, CPU percentage isolates processor usage. When CPU percentage is consistently high, it confirms CPU pressure as the cause of slow queries.

The other metrics measure I/O or log throughput, which are unrelated to CPU bottlenecks.

Exam trap

The trap here is choosing DTU percentage because it is a common metric, but it combines CPU, I/O, and memory, so it does not specifically confirm CPU pressure.

Full explanation →

525

MCQeasy

You need to automate the deployment of Azure SQL Database logical servers and databases using Bicep. What is the best practice for storing the administrative password securely?

A.Reference the password from Azure Key Vault using the getSecret function

B.Use the adminPassword property with a generated password

C.Use an environment variable in the deployment script

D.Store the password as a plain text parameter in the Bicep file

AnswerA

Referencing the password via Key Vault's getSecret function keeps the credential out of the Bicep template and its deployment history, satisfying the requirement to store the administrative password securely. The secret is resolved at deployment time from Key Vault, so plaintext never appears in source control or ARM deployment records.

Why this answer

Azure Key Vault is the recommended secure storage for secrets like administrative passwords in Azure deployments. Using the `getSecret` function in Bicep allows you to reference a secret from Key Vault at deployment time without exposing the password in the Bicep file or deployment logs, aligning with Azure security best practices and the principle of least privilege.

Exam trap

The trap here is that candidates may think environment variables or generated passwords are acceptable for automation, but the DP-300 exam specifically tests the secure secret management pattern using Azure Key Vault with Bicep's `getSecret` function, not just any method of hiding the password.

How to eliminate wrong answers

Option B is wrong because using the `adminPassword` property with a generated password, while functional, does not securely store the password; it is typically passed as a parameter and can be exposed in deployment logs or outputs. Option C is wrong because environment variables in the deployment script are not encrypted and can be captured in process dumps or logs, failing to meet security compliance requirements. Option D is wrong because storing the password as a plain text parameter in the Bicep file directly exposes the secret in source control and deployment history, violating fundamental security practices.

Full explanation →

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