Microsoft Azure Database Administrator Associate DP-300 DP-300 Questions 301–375 | Page 5/8

301

MCQeasy

You are planning to deploy a new Azure SQL Database for an internal HR application. The application is used only during business hours on weekdays and can tolerate a brief outage if the database needs to be scaled. To minimize cost, you need the database to automatically scale compute resources based on workload demand and allow the database to be paused when not in use. Which purchasing model and service tier should you choose?

A.DTU-based purchasing model with the Standard service tier

B.vCore-based purchasing model with the Business Critical service tier and provisioned compute

C.vCore-based purchasing model with the General Purpose service tier and serverless compute

D.DTU-based purchasing model with the Basic service tier

AnswerC

The vCore-based General Purpose tier with serverless compute automatically scales compute based on workload and can pause the database during inactivity, billing only for storage while paused. This directly satisfies the need to minimize cost by scaling automatically and pausing when not in use, while still providing the necessary compute for the HR application.

Why this answer

The vCore-based General Purpose tier with serverless compute is designed for intermittent, unpredictable workloads. It automatically scales compute within a configured range and pauses the database when inactive, reducing cost. The other options use provisioned compute or DTU-based models that do not support auto-scaling or auto-pausing, so they cannot meet the cost-saving requirements.

Exam trap

The trap here is assuming that any low-cost tier supports auto-pause and auto-scaling, when in fact only the serverless compute tier in the vCore model provides those capabilities.

Full explanation →

302

MCQeasy

You are the DBA for a company that uses Azure SQL Database. You need to ensure that only authorized users can view sensitive columns (e.g., salary) in the Employees table. You want to obfuscate the data for certain users but allow full access to HR managers. Which feature should you use?

A.Always Encrypted

B.Dynamic Data Masking

C.Row-Level Security (RLS)

D.Transparent Data Encryption (TDE)

AnswerB

Dynamic Data Masking applies masking rules at query time, returning obfuscated values to unauthorised users while privileged roles such as HR managers see the underlying salary data. It satisfies the requirement to hide sensitive columns without altering stored data or application code.

Why this answer

Dynamic Data Masking (DDM) is the correct choice because it obfuscates sensitive columns (e.g., salary) in query results for unauthorized users while allowing full visibility for authorized users like HR managers. DDM applies masking rules at the database level without modifying the underlying data, making it ideal for scenarios where you need to limit exposure of sensitive data to certain roles without changing the application code.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking with Always Encrypted, thinking that encryption is needed for obfuscation, but DDM is specifically designed for on-the-fly data masking without changing the underlying storage or requiring client-side changes.

How to eliminate wrong answers

Option A is wrong because Always Encrypt encrypts data at the client side, ensuring that the database engine never sees plaintext, which prevents even authorized database users (like HR managers) from viewing the data unless they have the column encryption key, making it unsuitable for role-based obfuscation where some users need full access. Option C is wrong because Row-Level Security (RLS) restricts access to rows based on user identity or context, but it does not obfuscate column values; it either shows or hides entire rows, not partial data within a column. Option D is wrong because Transparent Data Encryption (TDE) encrypts the entire database at rest (data files and backups) but does not control or obfuscate data visibility at query time for specific users or columns.

Full explanation →

303

MCQhard

You manage an Azure SQL Database that contains a table with a column named 'CreditCardNumber' that stores sensitive data. You need to ensure that the data in this column is encrypted at rest and in use, and that only specific application users can decrypt it. You also need to minimize performance impact on queries that do not access this column. What should you implement?

A.Always Encrypted with secure enclaves and column master key stored in Azure Key Vault.

B.Dynamic Data Masking on the 'CreditCardNumber' column.

C.Row-Level Security (RLS) with a security policy that filters rows based on user identity.

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

AnswerA

Always Encrypted with secure enclaves protects data at rest and in use, and allows rich computations on encrypted data within the enclave. The column master key in Azure Key Vault controls access, so only clients with permission to the key can decrypt. Queries not accessing the encrypted column are unaffected, minimizing performance impact. This meets all requirements: encryption at rest and in use, granular access control, and minimal impact.

Why this answer

Always Encrypted with secure enclaves is designed to protect sensitive data both at rest and in use. It uses column encryption keys and a column master key stored in a key store such as Azure Key Vault. Only clients with access to the column master key can decrypt data.

Secure enclaves allow computations on encrypted data without exposing it to the database engine. This approach meets the need for granular access control and minimal performance impact on unrelated queries.

Exam trap

The trap here is assuming that Transparent Data Encryption or Dynamic Data Masking provides in-use encryption and granular decryption control, when only Always Encrypted with secure enclaves does.

Full explanation →

304

MCQhard

Refer to the exhibit. An Azure SQL Database is experiencing performance degradation. Based on the Extended Events and wait statistics, which is the most likely root cause?

A.Blocking due to lock contention

B.CPU pressure from high-complexity queries

C.I/O subsystem bottleneck

D.Insufficient memory allocation for the database

AnswerC

Elevated wait times on PAGEIOLATCH and related I/O waits, combined with the Extended Events output, point to storage latency rather than CPU or blocking. The database is waiting on data pages to be read from disk, indicating the underlying I/O subsystem cannot meet demand.

Why this answer

The exhibit shows PAGEIOLATCH_SH and WRITELOG waits dominating the wait statistics, which are classic indicators of I/O subsystem bottlenecks. PAGEIOLATCH_SH waits occur when a session is waiting for a data page to be read from disk into the buffer pool, while WRITELOG waits indicate delays in writing to the transaction log. These waits are not caused by CPU or memory pressure, but by slow disk I/O, making option C the correct root cause.

Exam trap

The trap here is that candidates see PAGEIOLATCH_SH and assume it is always caused by insufficient memory, but the combination with WRITELOG waits clearly points to an I/O bottleneck, not a memory issue.

How to eliminate wrong answers

Option A is wrong because blocking due to lock contention would manifest as LCK_M_* waits (e.g., LCK_M_S, LCK_M_X), not PAGEIOLATCH_SH or WRITELOG waits. Option B is wrong because CPU pressure from high-complexity queries would show SOS_SCHEDULER_YIELD or CXPACKET waits, not I/O-related waits. Option D is wrong because insufficient memory allocation would cause PAGEIOLATCH_SH waits only if memory pressure forces excessive physical I/O, but the presence of WRITELOG waits points directly to a log write bottleneck, not a memory shortage; memory pressure alone would not cause WRITELOG waits.

Full explanation →

305

MCQmedium

You manage an Azure SQL Database that is part of a business-critical application. You need to configure an alert that triggers when the database's CPU usage exceeds 80% for 10 minutes. The alert must notify an operations team via email. You want to minimize administrative effort. What should you do?

A.Enable automatic tuning and configure the FORCE_LAST_GOOD_PLAN option.

B.Create a SQL Agent job that queries sys.dm_db_resource_stats and sends an email if CPU exceeds 80%.

C.Create an alert rule in Azure Monitor with a metric signal for CPU percentage and an action group that sends email.

D.Use Query Store to create a custom alert when CPU usage exceeds 80%.

AnswerC

Azure Monitor alert rules can monitor the CPU percentage metric of an Azure SQL Database. You can set a threshold of 80% and an aggregation granularity of 10 minutes. Associating an action group with email notifications fulfills the requirement with minimal effort, as it is a built-in, integrated solution.

Why this answer

Azure Monitor is the native monitoring solution for Azure SQL Database. It allows you to create metric alerts on CPU percentage with a threshold and time window, and action groups can send emails. This requires no custom code and is the least administrative effort.

SQL Agent is unavailable, automatic tuning is not for alerting, and Query Store lacks alerting features.

Exam trap

The trap here is assuming that SQL Server Agent or Query Store can provide alerting, when Azure SQL Database requires Azure Monitor for native alerting.

Full explanation →

306

MCQmedium

You are a database administrator for an Azure SQL Database. You need to ensure that only specific client IP addresses can connect to the database, while all other traffic is blocked. You also need to allow Azure services to access the database. What should you configure?

A.Disable public network access and configure a service endpoint.

B.Configure a private endpoint and disable public network access.

C.Configure network security group (NSG) rules on the subnet where the Azure SQL Database is deployed.

D.Configure server-level firewall rules to allow the specific client IP addresses and enable the 'Allow Azure services and resources to access this server' setting.

AnswerD

Server-level firewall rules permit the named client IP ranges, while the 'Allow Azure services' toggle opens the special 0.0.0.0 rule that lets platform-originated traffic through. Together they satisfy both constraints: specific client IPs only, plus Azure services access.

Why this answer

Azure SQL Database uses server-level firewall rules to control inbound access. By adding rules for specific client IP addresses and enabling the 'Allow Azure services and resources to access this server' setting, you restrict connections to only those IPs while permitting Azure internal services (e.g., Azure Logic Apps, Azure Functions) to connect. This setting leverages the Azure SQL firewall, which evaluates source IP addresses against the configured rules before allowing a connection.

Exam trap

The trap here is that candidates often confuse network security groups (NSGs) with Azure SQL firewall rules, assuming NSGs can control access to PaaS services like Azure SQL Database, when in fact NSGs only apply to resources within a virtual network and not to the public endpoint of Azure SQL.

How to eliminate wrong answers

Option A is wrong because disabling public network access and configuring a service endpoint would block all public traffic, including the specific client IPs, and service endpoints only secure traffic to Azure SQL from a virtual network, not from arbitrary client IPs. Option B is wrong because configuring a private endpoint and disabling public network access would isolate the database to a virtual network, blocking all public client IPs and requiring clients to be on the same or peered network, which does not meet the requirement to allow specific client IP addresses. Option C is wrong because network security group (NSG) rules apply at the subnet level for resources deployed in a virtual network, but Azure SQL Database is a PaaS service with a public endpoint by default; NSGs cannot control inbound traffic to the Azure SQL Database's public endpoint directly.

Full explanation →

307

MCQeasy

Your company requires that all production databases in Azure SQL Database have an RPO of less than 5 seconds and an RTO of less than 1 minute during a regional outage. You need to recommend a high availability and disaster recovery solution. Which feature should you use?

A.Zone-redundant configuration (Hyperscale service tier)

B.Auto-failover groups with active geo-replication

C.Long-term retention (LTR) backups

D.Geo-restore from geo-redundant backups

AnswerB

Auto-failover groups with active geo-replication replicate asynchronously to a secondary region, giving an RPO of roughly 5 seconds and RTO typically under a minute. This satisfies both the sub-5-second RPO and sub-1-minute RTO constraints for a regional outage.

Why this answer

Auto-failover groups with active geo-replication provide a group-level failover that can redirect all databases in a group to a secondary region with an RPO of about 5 seconds and an RTO of under a minute, meeting both requirements. Active geo-replication continuously replicates transactions to secondary databases, and the failover group automates the DNS and connection redirection so applications reconnect quickly.

Exam trap

DP-300 often tests the difference between zone redundancy (single-region HA) and geo-replication/failover groups (cross-region DR), and candidates frequently pick zone-redundant options for regional outage scenarios.

How to eliminate wrong answers

Option A is wrong because a zone-redundant Hyperscale configuration protects against a zone failure within a single region, not a regional outage — it does not provide cross-region disaster recovery. Option C is wrong because Long-Term Retention backups are for compliance and long-term archival, not for fast failover; restoring from LTR takes hours and has an RPO measured in hours or days. Option D is wrong because geo-restore from geo-redundant backups has an RPO of up to 1 hour and an RTO of up to 12 hours, far exceeding the 5-second RPO and 1-minute RTO requirements.

Full explanation →

308

Multi-Selecteasy

Which TWO options are required to configure a SQL Server Always On Availability Group on Azure Virtual Machines?

Select 2 answers

A.Internal Load Balancer

B.Azure Files share for witness

C.Windows Server Failover Cluster

D.Azure SQL Database

E.VPN gateway between regions

AnswersA, C

An internal load balancer is required so clients and listeners reach the availability group's listener IP across the cluster nodes, since Azure networking does not support the floating IP that a WSFC listener normally uses. It distributes listener traffic to the current primary replica.

Why this answer

Option A (Internal Load Balancer) is required because in Azure, the availability group listener needs an Azure Internal Load Balancer to route client connections to the current primary replica, since Azure networking does not support the traditional floating IP/ARP method used on-premises. Option C (Windows Server Failover Cluster) is required because Always On Availability Groups are built on top of a WSFC, which provides the cluster quorum, health detection, and failover orchestration for the SQL Server replicas on the Azure VMs. Option B is not required because a cloud witness (an Azure Storage account blob) is the typical witness for a WSFC in Azure, not an Azure Files share.

Option D is incorrect because Azure SQL Database is a separate PaaS offering and cannot host the SQL Server instances that form an Always On Availability Group on VMs. Option E is not required because a VPN gateway is only needed for cross-region or hybrid connectivity, not for the core configuration of an availability group within Azure.

Exam trap

The trap here is that candidates often think a VPN gateway is required for cross-region AGs, but the question asks for required options to configure the AG, and the ILB and WSFC are the only mandatory components; the VPN gateway is optional and only relevant for specific network topologies.

Full explanation →

309

MCQeasy

You are designing an automated backup retention policy for an Azure SQL Database. The business requirement is to retain daily backups for 30 days, weekly backups for 12 weeks, monthly backups for 12 months, and yearly backups for 7 years. Which backup retention type should you configure?

A.Point-in-time restore (PITR) retention

B.Backup vault with Azure Backup

C.Long-term retention (LTR) policy

D.Automated backup policy

AnswerC

Long-term retention extends Azure SQL Database backups beyond the default 7–35 day point-in-time window, storing full backups in RA-GRS blob storage on separate daily, weekly, monthly and yearly schedules. This directly satisfies the stem's 7-year yearly retention requirement, which the built-in short-term policy cannot provide.

Why this answer

Long-term retention (LTR) policy in Azure SQL Database is specifically designed to retain full backups beyond the default PITR window, supporting configurable weekly, monthly, and yearly retention periods. The requirement of 30 days daily, 12 weeks weekly, 12 months monthly, and 7 years yearly maps exactly to LTR's weekly/monthly/yearly retention options, which can be set independently.

Exam trap

DP-300 often tests the confusion between PITR (short-term, up to 35 days) and LTR (long-term, up to 10 years), tempting candidates to pick PITR or Azure Backup when multi-year tiered retention is required.

How to eliminate wrong answers

Option A is wrong because PITR retention only covers 1-35 days of point-in-time restore capability and does not support weekly, monthly, or yearly retention schedules. Option B is wrong because Azure Backup with a Backup vault is used for Azure VMs, file shares, and other workloads — not for Azure SQL Database automated backups, which use the built-in LTR feature. Option D is wrong because the 'automated backup policy' is the default PITR-based backup mechanism and does not provide the multi-year, tiered retention required.

Full explanation →

310

MCQmedium

You manage a SQL Managed Instance in the East US region. The instance must be recoverable within 1 hour in the event of a regional disaster. You need to configure a secondary replica in a paired region with automatic failover. Which solution meets the requirement?

A.Configure log shipping to a secondary instance in West US.

B.Enable geo-redundant backup storage and use geo-restore.

C.Create an auto-failover group with a secondary replica in West US.

D.Deploy an Always On availability group with a synchronous secondary in West US.

AnswerC

Auto-failover groups provide a secondary SQL Managed Instance in a paired region with automatic failover and readable endpoints, meeting the one-hour regional recovery requirement. East US pairs with West US, so the replica location is valid.

Why this answer

An auto-failover group in Azure SQL Managed Instance provides a secondary replica in a paired region with automatic failover, meeting the requirement for recovery within 1 hour. It uses asynchronous replication and allows a single connection string to fail over automatically, ensuring business continuity during a regional disaster.

Exam trap

DP-300 often tests the difference between automatic and manual failover solutions, and candidates may incorrectly choose geo-restore or log shipping because they are familiar with backup-based recovery, missing the requirement for automatic failover.

How to eliminate wrong answers

Option A (Log shipping) is wrong because it requires manual failover and does not provide automatic failover; recovery time depends on the log shipping frequency and manual intervention. Option B (Geo-redundant backup storage and geo-restore) is wrong because geo-restore is a manual process that can take hours and does not provide automatic failover. Option D (Always On availability group with synchronous secondary in West US) is wrong because synchronous replication across regions introduces high latency and is not supported for cross-region replicas in SQL Managed Instance; it would also not provide automatic failover across regions.

Full explanation →

311

MCQhard

You have an Azure SQL Database with a heavy workload. You notice that the `PAGEIOLATCH_SH` wait is the top wait. Which performance issue does this indicate?

A.Blocking

B.CPU bottleneck

C.I/O subsystem bottleneck

D.Memory pressure

AnswerC

`PAGEIOLATCH_SH` waits occur when sessions block acquiring shared latches while pages are read from disk into the buffer pool, so sustained dominance points to the storage layer rather than CPU or locking. This satisfies the stem's heavy-workload constraint by identifying the I/O subsystem as the bottleneck, prompting investigation of disk latency and throughput.

Why this answer

The `PAGEIOLATCH_SH` wait type indicates that a query is waiting for a data page to be read from disk into the buffer pool. Since this is the top wait, it points to an I/O subsystem bottleneck where the storage cannot keep up with the demand for reading pages, causing performance degradation.

Exam trap

The trap here is that candidates confuse `PAGEIOLATCH_SH` with memory pressure or blocking, but the key distinction is that this wait type specifically measures I/O latency for reading pages from disk, not memory availability or lock contention.

How to eliminate wrong answers

Option A is wrong because blocking is indicated by wait types like `LCK_M_*` (e.g., `LCK_M_S` or `LCK_M_X`), not by `PAGEIOLATCH_SH`. Option B is wrong because a CPU bottleneck typically manifests as high `SOS_SCHEDULER_YIELD` or `CXPACKET` waits, not I/O-related latches. Option D is wrong because memory pressure usually shows as `PAGEIOLATCH_EX` (for writes) or `RESOURCE_SEMAPHORE` waits, and while `PAGEIOLATCH_SH` can be exacerbated by insufficient memory, the primary indicator here is an I/O subsystem issue.

Full explanation →

312

MCQhard

You are a database administrator for a financial services company that uses Azure SQL Database. The company must ensure that all database backups are encrypted with a customer-managed key stored in Azure Key Vault. You need to configure the database to meet this requirement. What should you do first?

A.Create an Azure Key Vault and generate or import a key.

B.Enable transparent data encryption (TDE) with a service-managed key.

C.Configure Azure SQL Database auditing to log all access to the database.

D.Enable Advanced Threat Protection on the Azure SQL server.

AnswerA

Before you can use a customer-managed key for TDE, you must have a key in Azure Key Vault. This involves creating a key vault, generating or importing a key, and granting the Azure SQL logical server access to the vault. This is the foundational step to enable TDE with customer-managed keys, ensuring compliance with the requirement.

Why this answer

To use a customer-managed key for TDE, the key must exist in Azure Key Vault and the Azure SQL logical server must be granted permissions to access it. Creating the key vault and key is the necessary first step before enabling TDE with that key. Other options are security features but do not directly enable customer-managed key encryption.

Exam trap

The trap here is confusing security features like auditing or Advanced Threat Protection with encryption key management, which are separate concerns.

Full explanation →

313

MCQeasy

A junior developer at your company connects to an Azure SQL Database using the SQL login 'appuser'. You need to grant 'appuser' the ability to read from a table named dbo.Orders in the Sales schema, but nothing else in the database. You also want to follow the principle of least privilege. What should you do?

A.Grant SELECT on the dbo.Orders table to 'appuser'.

B.Add 'appuser' to the db_datareader role.

C.Add 'appuser' to the db_owner role.

D.Grant CONTROL on the Sales schema to 'appuser'.

AnswerA

A table-level GRANT SELECT gives exactly the read permission required on dbo.Orders and nothing more. It follows least privilege because the user receives no access to other tables, views, or schemas, and it is the most granular standard permission available for this scenario.

Why this answer

The most granular permission that satisfies the requirement is a table-level GRANT SELECT on dbo.Orders. It gives the developer exactly the read access needed while leaving all other objects inaccessible. Role memberships such as db_datareader and db_owner, and schema-level CONTROL, all grant broader rights than the scenario allows.

Exam trap

The trap here is reaching for a built-in role such as db_datareader because it is convenient, when it grants read access to every table rather than the one table required.

Full explanation →

314

MCQmedium

You manage an Azure SQL Database. A security review finds that an application service principal is connecting with a SQL login that has db_owner membership, and that the login's password has not changed in two years. You must reduce the standing privilege and eliminate the long-lived password while keeping the application working. What should you do?

A.Rotate the SQL login password and move it into Azure Key Vault, leaving db_owner membership unchanged

B.Enable Microsoft Entra authentication on the server and keep the SQL login as a fallback for the application

C.Create a contained database user mapped to the service principal's Microsoft Entra identity, grant it only the required permissions, and remove the SQL login

D.Add the service principal to a custom database role with db_owner and enforce a password policy on the login

AnswerC

A contained database user mapped to the service principal authenticates through Microsoft Entra ID, so no password exists to rotate or leak. Granting only the permissions the application needs removes the db_owner standing privilege. Removing the old SQL login closes the credential path entirely, satisfying both the least-privilege and no-long-lived-password requirements.

Why this answer

Replacing the password-based SQL login with a contained database user mapped to the service principal removes the long-lived secret and lets you grant only the permissions the application actually needs. Rotating a password or renaming the role leaves both problems intact, and merely enabling directory authentication without removing the login does not close the insecure path.

Exam trap

The trap here is believing that moving a password into Key Vault or rotating it satisfies a no-long-lived-password requirement, when the password itself still exists and the excessive role membership is untouched.

Full explanation →

315

Multi-Selecteasy

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

Select 2 answers

A.Set an Microsoft Entra ID administrator for the logical server

B.Create a login in the master database for each Entra ID user

C.Assign a system-assigned managed identity to the logical server

D.Create a contained database user mapped to an Entra ID identity

E.Enable 'contained database authentication' on the server

AnswersA, D

Microsoft Entra ID authentication requires a directory administrator designated on the logical server, since that identity governs token issuance and directory-based logins. Without this server-level administrator, Azure SQL Database cannot validate Microsoft Entra ID tokens, so the remaining configuration steps have no authority to authenticate against.

Why this answer

Setting a Microsoft Entra ID administrator for the logical server (Option A) is required because it establishes the Entra ID tenant as an identity provider for the Azure SQL Database logical server, enabling token-based authentication. Creating a contained database user mapped to an Entra ID identity (Option D) is required because Azure SQL Database uses contained database users for authentication, where the user is authenticated directly against the database without requiring a login in the master database. These two actions together allow Entra ID users to authenticate to the database using their cloud identities.

Exam trap

The trap here is that candidates confuse the on-premises SQL Server requirement of enabling 'contained database authentication' with Azure SQL Database, which always has this enabled, and they mistakenly think creating logins in master is necessary for Entra ID users when contained database users are the correct approach.

Full explanation →

316

Matchingmedium

Match each Azure SQL Database monitoring metric to its meaning.

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

Concepts

Matches

Percentage of DTU or CPU used

Percentage of data I/O limit used

Percentage of log write limit used

Number of deadlocks occurring per minute

Why these pairings

These metrics are used to monitor resource usage and performance in Azure SQL Database.

Full explanation →

317

MCQmedium

You are deploying an Azure SQL Database that will be used by a global application. You need to ensure that read-intensive workloads are offloaded from the primary database to improve performance. Which feature should you enable?

A.Read scale-out.

B.Automatic tuning.

C.Connection pooling.

D.Active geo-replication.

AnswerA

Read scale-out uses the built-in read-only replica, routing read-intensive connections to it via the ApplicationIntent=ReadOnly parameter, so the primary replica handles writes only. This directly satisfies the stem's requirement to offload read workloads and improve performance, without extra cost or configuration on Business Critical or Hyperscale tiers.

Why this answer

Read scale-out (A) is the correct feature because it allows you to offload read-only workloads to a read-only replica of the Azure SQL Database. By enabling the 'Read scale-out' property, the database automatically routes connections with `ApplicationIntent=ReadOnly` to a secondary replica, freeing the primary from read-intensive queries and improving overall performance for write operations.

Exam trap

The trap here is that candidates often confuse Active geo-replication with read scale-out, but the key distinction is that read scale-out is a single-database feature within the same region that transparently routes read-only connections, whereas geo-replication requires explicit connection string changes and is designed for cross-region disaster recovery.

How to eliminate wrong answers

Option B is wrong because Automatic tuning is a feature that uses AI to optimize query performance (e.g., index creation, plan forcing), but it does not offload read workloads to a separate replica. Option C is wrong because Connection pooling is a client-side technique that reuses database connections to reduce latency, but it does not redirect read traffic to a secondary database. Option D is wrong because Active geo-replication creates readable secondary replicas in different regions for disaster recovery and geo-distributed reads, but it requires manual connection string changes or application logic to route read traffic, unlike the transparent, built-in read scale-out feature.

Full explanation →

318

MCQhard

You are tuning an Azure SQL Database that uses the General Purpose service tier. You notice that a specific query has a high average CPU time but a low average elapsed time. Query Store shows that the query plan uses a Hash Match (Aggregate) operator. You need to reduce the CPU consumption of this query. What should you do?

A.Update statistics on the involved tables.

B.Force a plan that uses a Stream Aggregate operator.

C.Create a covering index for the query's join and filter columns.

D.Rewrite the query to reduce the number of rows processed before aggregation.

AnswerD

The high CPU time with a Hash Match (Aggregate) suggests that the aggregation is processing a large number of rows. Reducing the row count earlier in the query (e.g., by adding more selective filters, pre-aggregating in a subquery, or using a indexed view) decreases the work the hash aggregate must perform. This directly lowers CPU consumption. Other options like index changes or plan forcing may help but are less direct and may not address the root cause of excessive rows entering the aggregate.

Why this answer

A Hash Match (Aggregate) operator builds a hash table of group values and is CPU-intensive when processing many rows. The most effective way to reduce its CPU cost is to reduce the number of rows that reach the aggregate. This can be done by filtering earlier, pre-aggregating, or using indexed views.

While indexes and statistics can influence plan choice, they do not directly reduce the CPU work of the hash aggregate if the row count remains high.

Exam trap

The trap here is focusing on index or statistics changes when the high CPU is caused by the aggregation operator processing too many rows, not by missing indexes or outdated statistics.

Full explanation →

319

Multi-Selectmedium

You are deploying an Azure SQL Database and need to enforce that all connections to the database use encrypted channels and that the server presents a specific certificate that the client validates. You also need to ensure that the database cannot be accessed from the public internet except through a private endpoint. Which two actions should you perform? (Choose two.)

Select 2 answers

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

B.Enable Always Encrypted with secure enclaves for the columns that contain sensitive data.

C.Set the Encrypt connection setting to mandatory in the connection string and use TrustServerCertificate=False with a trusted root certificate.

D.Configure an Azure SQL Database firewall rule that allows only the application's public IP address.

E.Configure the Azure SQL Database server to use a private endpoint and disable public network access.

AnswersC, E

Requiring encryption in the connection string forces the client to use TLS, and setting TrustServerCertificate to false makes the client validate the server certificate against a trusted root. This satisfies the requirement that connections use encrypted channels and that the client validates a specific certificate. It works with both public and private endpoints but is essential for certificate validation.

Why this answer

A private endpoint with public network access disabled ensures the logical server is reachable only through a private IP address inside a virtual network, removing exposure to the public internet. Requiring encryption in the connection string and setting TrustServerCertificate to false forces TLS and makes the client validate the server certificate against a trusted root. Together these actions satisfy both the private connectivity and certificate-validation requirements.

Exam trap

The trap here is assuming that Transparent Data Encryption protects data in transit, when TDE only encrypts data at rest and has no effect on the TLS channel or certificate validation.

Full explanation →

320

MCQhard

You have an Azure SQL Database that is part of an elastic pool. You notice that the pool's eDTU consumption is consistently high, and some databases are experiencing resource contention. You need to ensure that a critical database always gets a minimum amount of resources. What should you configure?

A.Configure per-database max eDTU for the critical database

B.Increase the eDTU of the elastic pool

C.Move the critical database to a dedicated service tier

D.Configure per-database min eDTU for the critical database

AnswerD

Per-database min eDTU guarantees the critical database a reserved floor of resources within the elastic pool, so contention from other databases cannot starve it. This directly satisfies the requirement that the critical database always receives a minimum amount of resources.

Why this answer

In an Azure SQL elastic pool, per-database min eDTU (or min vCore) guarantees a floor of resources that a specific database can always consume, even when other databases in the pool are competing for resources. Setting a min eDTU on the critical database ensures it is never starved during contention. This directly addresses the requirement that the critical database 'always gets a minimum amount of resources.'

Exam trap

DP-300 often tests the confusion between min and max eDTU settings — candidates see 'minimum amount of resources' and incorrectly pick max eDTU, forgetting that max is a ceiling, not a floor.

How to eliminate wrong answers

Option A is wrong because per-database max eDTU only caps how much a database can consume — it does not guarantee any minimum, so the critical database could still be starved. Option B is wrong because increasing the pool's total eDTU raises the shared ceiling but does not reserve resources for any specific database; contention can still occur. Option C is wrong because moving to a dedicated tier is a valid workaround but is not the configuration change being asked for, and it defeats the purpose of the elastic pool.

Full explanation →

321

MCQeasy

You need to automate the deployment of schema changes to multiple Azure SQL Databases in different regions. The solution must support rollback and version control. Which technology should you use?

A.Use Azure Data Factory to run stored procedures for schema changes.

B.Use SQL Server Agent jobs to run deployment scripts on schedule.

C.Use Azure DevOps with a database project and release pipelines.

D.Use Azure Automation with PowerShell scripts to execute T-SQL scripts.

AnswerC

Azure DevOps database projects keep schema definitions in version control, and release pipelines deploy them across regions with tracked, repeatable steps. This satisfies both the rollback requirement, via redeploying prior versions, and version control of schema changes.

Why this answer

Azure DevOps with a database project and release pipelines is the correct choice because it provides source control for schema changes, automated deployment across multiple environments, and built-in rollback capabilities through pipeline versioning and deployment history. This approach aligns with infrastructure-as-code principles, enabling consistent, repeatable, and auditable schema deployments to Azure SQL Databases in different regions.

Exam trap

The trap here is that candidates often confuse Azure Data Factory or Azure Automation as valid automation tools for schema changes, overlooking that they lack the version control and rollback capabilities that are explicitly required by the question.

How to eliminate wrong answers

Option A is wrong because Azure Data Factory is an ETL and data orchestration service, not a schema deployment tool; it lacks native version control for schema changes and cannot perform rollback of DDL operations. Option B is wrong because SQL Server Agent jobs run on a single SQL Server instance and cannot be centrally managed for multi-region Azure SQL Databases; they also lack version control and rollback support. Option D is wrong because Azure Automation with PowerShell scripts executes ad-hoc or scheduled scripts but does not provide integrated version control, release pipeline gating, or automated rollback mechanisms for schema changes across multiple databases.

Full explanation →

322

Multi-Selectmedium

You need to design a disaster recovery solution for an Azure SQL Database that uses the General Purpose service tier. The solution must have an RTO of 1 hour and an RPO of 15 minutes. Which TWO options can achieve these requirements?

Select 2 answers

A.Point-in-time restore (PITR) using geo-redundant backup storage.

B.Long-term retention (LTR) backups.

C.Auto-failover group with a secondary in a different region.

D.Active geo-replication to a secondary server in a paired region.

E.Zone redundancy on the primary database.

AnswersC, D

Auto-failover groups use active geo-replication and provide automatic failover. The RPO is the same as active geo-replication (seconds to minutes) and failover completes within minutes, meeting both targets.

Why this answer

Option C (auto-failover group with a secondary in a different region) is correct because failover groups provide a readable/writable secondary in another region with an RPO of about 5 seconds and an RTO typically under 1 hour, satisfying both the 15-minute RPO and 1-hour RTO. Option D (active geo-replication to a secondary server in a paired region) is also correct because it continuously replicates transactions asynchronously with an RPO of roughly 5 seconds and supports a manual/forced failover that can complete well within 1 hour, meeting the stated targets. Option A (PITR using geo-redundant backup storage) is not appropriate because PITR restores to a point in time from backups with an RPO of up to 10 minutes but an RTO that can exceed 1 hour depending on database size, and it is a restore operation rather than a fast failover.

Option B (LTR backups) is incorrect because LTR is designed for long-term compliance retention (up to 10 years) and does not provide the low RTO/RPO needed for disaster recovery. Option E (zone redundancy on the primary database) is incorrect because zone redundancy only protects against a single-zone datacenter failure within the same region and does not address a regional disaster or provide cross-region failover.

Exam trap

DP-300 often tests the confusion between backup-based recovery (PITR/LTR/geo-restore) and replication-based DR (geo-replication/failover groups) — candidates pick PITR because it sounds like a DR feature, but its restore time and retention-bound RPO rarely meet strict RTO/RPO SLAs.

Full explanation →

323

MCQmedium

You are managing an Azure SQL Database that is used by a real-time analytics application. The database uses the Hyperscale service tier. You notice that the transaction log rate is consistently high, causing performance degradation. You need to reduce the log generation rate without compromising data durability. What should you do?

A.Enable compression on transaction log backups.

B.Create additional nonclustered indexes on frequently updated tables.

C.Increase the service tier to Business Critical.

D.Increase the backup retention period.

AnswerC

Increasing the service tier to Business Critical provides better log write performance through local SSD storage, reducing the log generation rate. This option is correct.

Why this answer

Increasing the service tier to Business Critical does not reduce the transaction log generation rate; it only improves log write throughput. The log generation rate is workload‑dependent and is not changed by moving to a higher tier. None of the provided options achieve the goal of reducing the log generation rate while maintaining durability.

To reduce the rate, you would need to optimize the application (e.g., batching, reducing transactions), which is not offered among the choices.

Exam trap

A common trap is to think that compressing log backups reduces log generation, but it only reduces backup size. The actual solution is to upgrade to a higher tier with better log write performance.

Full explanation →

324

MCQmedium

Your company runs a global e-commerce application using Azure SQL Database in the West Europe region. You need to implement a solution that provides automatic failover and allows the secondary region to be used for read-only queries during normal operations. The secondary must be in a different region. Which configuration meets these requirements?

A.Enable read scale-out on the primary database.

B.Create an auto-failover group with a secondary in West US and use the secondary as readable.

C.Configure active geo-replication to a secondary server in West US and implement application-level failover logic.

D.Configure geo-restore with a recovery point objective of 1 hour.

AnswerB

Auto-failover groups replicate asynchronously to a paired region and expose a read-write listener plus read-only listener, giving automatic failover and readable secondary. Placing the secondary in West US meets the different-region constraint while supporting read-only queries.

Why this answer

An auto-failover group in Azure SQL Database provides automatic failover to a secondary region and allows the secondary database to be used for read-only queries when configured as readable. It also provides a read-write and read-only listener endpoint, so applications can connect without changing connection strings after failover. This meets both the automatic failover and read-only query requirements.

Exam trap

DP-300 often tests the distinction between active geo-replication (manual failover, no listener) and auto-failover groups (automatic failover, listener endpoints) — candidates pick active geo-replication when automatic failover is required.

How to eliminate wrong answers

Option A is wrong because read scale-out only provides an additional read-only replica in the same region — it does not provide cross-region failover or a secondary in a different region. Option C is wrong because active geo-replication requires the application to implement its own failover logic (it does not provide automatic failover) and does not provide listener endpoints, so it fails the 'automatic failover' requirement. Option D is wrong because geo-restore is a backup-based recovery method with a much longer RPO (up to 1 hour) and RTO, and it does not provide a continuously available readable secondary for normal operations.

Full explanation →

325

Multi-Selecthard

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

Select 2 answers

A.Service level objective (SLO)

B.Number of vCores

C.Backup retention period

D.Number of log files

E.Page compression level

AnswersA, B

The service level objective sets the provisioned compute and storage limits, which directly cap the transaction log generation rate for Business Critical databases. Higher SLOs provision more log throughput, so the SLO determines the ceiling on log rate independent of workload tuning.

Why this answer

The maximum log rate for an Azure SQL Database in the Business Critical tier is determined by the service level objective (SLO) and the number of vCores. Option A is correct because the SLO defines the performance tier and hardware configuration, which directly caps the log generation rate (e.g., Business Critical with 4 vCores has a specific log rate limit). Option B is correct because within a given SLO, the log rate scales with the number of vCores—more vCores allow a higher maximum log rate.

Option C is incorrect because backup retention period affects storage and recovery, not log throughput. Option D is incorrect because Azure SQL Database manages log files automatically; the number of log files is not a user-configurable factor affecting log rate. Option E is incorrect because page compression reduces data size and I/O but does not directly determine the maximum log generation rate.

Full explanation →

326

MCQmedium

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

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

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

C.Disable automatic tuning for the entire database.

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

AnswerD

Dropping the single problematic index removes the write-latency penalty while Automatic Tuning continues managing the remaining indexes. This satisfies the constraint of retaining automatic tuning benefits elsewhere, since disabling the feature globally would forfeit those gains.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

327

MCQhard

You are the database administrator for a financial services company using Azure SQL Database. The security team mandates that all administrative activities on the SQL logical server be performed using just-in-time (JIT) access with approval workflows, and that permanent elevated permissions be eliminated. You need to implement this requirement with the least amount of custom development. What should you use?

A.Microsoft Entra Privileged Identity Management (PIM) for Azure resources, assigning the SQL Server Contributor role as eligible.

B.Custom Azure Functions that grant and revoke database roles based on approval emails.

C.SQL Database contained users with temporary passwords that expire after a short period.

D.Azure SQL Database auditing with Log Analytics alerts for administrative actions.

AnswerA

Microsoft Entra Privileged Identity Management (PIM) provides just-in-time role activation with approval workflows and time-bound assignments. By making the SQL Server Contributor role eligible rather than permanent, administrators must activate it when needed, satisfying the JIT and approval requirements with minimal custom development. This is the native Azure solution for privileged access management.

Why this answer

Microsoft Entra Privileged Identity Management (PIM) is the native Azure service for just-in-time privileged access. By assigning roles such as SQL Server Contributor as eligible, administrators must activate the role with approval, and the assignment is time-bound. This eliminates permanent elevated permissions and meets the JIT and approval requirements with minimal custom development.

Auditing, custom functions, and contained users do not provide the required approval workflows and JIT activation.

Exam trap

The trap here is assuming that auditing or custom code can enforce JIT access, when the native PIM service is designed specifically for this purpose.

Full explanation →

328

MCQeasy

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

A.Azure Monitor alert with action group

B.Change Data Capture (CDC) with Logic Apps

C.Elastic Database Job with sp_send_dbmail

D.SQL Agent Mail

AnswerA

An Azure Monitor alert evaluates the database's storage metric against an 80% threshold and triggers an action group, which delivers the email notification. This satisfies the requirement for automatic notification without custom scripting or manual monitoring.

Why this answer

Azure Monitor alerts with action groups are the native mechanism for triggering notifications based on platform metrics such as storage percentage on an Azure SQL Database. You create an alert rule on the 'storage' metric with a threshold of 80%, and attach an action group that sends email (or SMS, webhook, etc.). This is the standard, supported approach for metric-based notifications on Azure SQL Database.

Exam trap

DP-300 often tests the misconception that SQL Server features like SQL Agent Mail or sp_send_dbmail are available in Azure SQL Database, when in fact PaaS Azure SQL Database does not support SQL Agent or Database Mail.

How to eliminate wrong answers

Option B is wrong because Change Data Capture tracks row-level data changes for replication/ETL purposes and has nothing to do with storage-usage monitoring or alerting. Option C is wrong because Elastic Database Jobs run T-SQL across databases and sp_send_dbmail requires Database Mail configured on a SQL Server instance — Azure SQL Database does not support SQL Agent or Database Mail. Option D is wrong because SQL Agent Mail is a SQL Server on-premises/IaaS feature; Azure SQL Database (PaaS) does not expose SQL Agent, so this is not available.

Full explanation →

329

MCQhard

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

A.Enable Microsoft Purview Data Map for the Managed Instance

B.Configure Microsoft Defender XDR for SQL

C.Use Microsoft Intune to manage SQL security policies

D.Enable Microsoft Defender for SQL on the Managed Instance

AnswerD

Microsoft Defender for SQL performs threat detection and advanced threat protection, raising alerts for anomalous activity such as SQL injection. Its alerts flow into Microsoft Defender for Cloud and can be streamed to Microsoft Sentinel for incident response.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

330

MCQhard

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

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

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

C.Set ignoreMissingVnetServiceEndpoint to true.

D.Change defaultAction to Allow.

AnswerA

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

Why this answer

The exhibit shows that the private endpoint is configured with a network ACL that has a deny-all default action and an explicit allow rule for the specific subnet in VNet1. Since private endpoints use network policies (like NSG rules) to filter traffic, and the ACL already denies all traffic except the allowed subnet, no changes are needed. The configuration meets the requirement because the private endpoint's network ACLs are evaluated in order, and the explicit allow for the subnet overrides the default deny for all other traffic.

Exam trap

The trap here is that candidates may think they need to add an IP rule for the subnet's IP range (Option B) or change the default action to allow (Option D), not realizing that private endpoints use virtual network rules that already implicitly allow traffic from the subnet, and the default deny action is correct for restricting all other traffic.

How to eliminate wrong answers

Option B is wrong because adding an IP rule to allow the subnet's IP range is unnecessary; the private endpoint already uses the subnet's virtual network identifier, not a raw IP range, and the ACL already allows the subnet via a virtual network rule. Option C is wrong because 'ignoreMissingVnetServiceEndpoint' is a property for Azure SQL Database firewall rules when using service endpoints, not for private endpoint ACLs; it does not apply here. Option D is wrong because changing 'defaultAction' to 'Allow' would permit all traffic, including traffic from outside the specified subnet, which contradicts the requirement to deny all other traffic.

Full explanation →

331

MCQmedium

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

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

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

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

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

AnswerD

A private endpoint assigns a private IP from the virtual network subnet to Azure SQL Database, so traffic from the application VMs stays on the Microsoft backbone and never traverses the public internet, satisfying the encryption and isolation requirement.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

332

MCQhard

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

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

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

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

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

AnswerB

Failover groups support readable secondary and minimize data loss.

Why this answer

Failover groups for Azure SQL Managed Instance allow you to configure the secondary instance to allow read-only connections, enabling it to serve read-only workloads. This minimizes data loss by using automatic replication with a Recovery Point Objective (RPO) of less than 1 second. Option A (zone redundancy) provides high availability within a region, not disaster recovery across regions.

Option C (log shipping) is not a built-in feature for managed instances and does not support automatic failover. Option D (active geo-replication) is not supported for managed instances; failover groups are the recommended solution.

Full explanation →

333

MCQmedium

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

A.sys.dm_exec_requests

B.sys.dm_exec_sessions

C.sys.dm_exec_query_stats

D.sys.dm_os_performance_counters

AnswerC

sys.dm_exec_query_stats aggregates cumulative CPU time per cached query plan, letting you rank statements by total worker time across the 24-hour window. Unlike sys.dm_exec_requests, which shows only currently executing queries, it retains historical totals, directly satisfying the requirement to identify the highest-CPU queries during business hours.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

334

Multi-Selecthard

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

Select 2 answers

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

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

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

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

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

AnswersB, C

This configures the server to use the CMK.

Why this answer

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

Exam trap

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

Full explanation →

335

Drag & Dropmedium

Drag and drop the steps to configure geo-replication for an 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

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

Full explanation →

336

MCQmedium

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

A.Microsoft Purview Information Protection

B.Azure Policy with custom policy definition

C.Dynamic Data Masking

D.Azure Automation with PowerShell script to run sp_addsensitivityclassification

AnswerA

Purview can automatically scan and classify sensitive data.

Why this answer

Microsoft Purview Information Protection enables automatic classification and labeling of sensitive data in Azure SQL Database through its data classification capabilities. It can be configured to automatically detect and apply sensitivity labels to new columns based on built-in or custom rules. Option B (Azure Policy) can enforce compliance but does not perform automatic classification of data within the database.

Option C (Dynamic Data Masking) obscures sensitive data but does not classify or label it. Option D (Azure Automation with PowerShell) requires custom scripting and does not provide built-in automatic classification integration for new columns.

Full explanation →

337

MCQmedium

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

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

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

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

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

AnswerD

Automated backups retain point-in-time restore capability, so restoring the primary to a timestamp before the logical corruption removes the bad data. Geo-replication copies corruption to the secondary, so it cannot help; point-in-time restore directly satisfies the minimal-data-loss constraint.

Why this answer

Logical corruption requires point-in-time restore from automated backups to a time before the corruption occurred. Active geo-replication replicates the corruption to the secondary, so it cannot be used to recover. Restoring the primary database to a point in time is the correct approach to minimize data loss.

Exam trap

DP-300 often tests the confusion between using geo-replication for disaster recovery versus point-in-time restore for logical corruption, tricking candidates into choosing failover options.

How to eliminate wrong answers

Option A is wrong because failing over to the secondary would just move the corrupted data to the primary role. Option B is wrong because the geo-replicated secondary contains the same corrupted data. Option C is wrong because seeding the secondary from a backup of the primary would not recover from corruption; it would just copy the corrupted data.

Full explanation →

338

MCQhard

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

A.Increase max degree of parallelism

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

C.Reduce backup retention period

D.Add more compute replicas

AnswerB

Hyperscale log rate limits scale with the service level objective, so scaling up raises the permitted log generation rate. This directly addresses the throttling constraint, unlike storage or replica changes, which do not affect the log rate ceiling.

Why this answer

In Azure SQL Database Hyperscale, log rate is governed by the service level objective, and when the log generation rate exceeds the limit the workload is throttled. Scaling up to a higher service level objective raises the log rate limit, directly relieving the throttling. This is the documented remedy for log rate throttling in Hyperscale.

Exam trap

DP-300 often tests whether candidates understand that Hyperscale log rate is tied to the service level objective, not to parallelism or replica count; candidates frequently pick 'add more replicas' assuming more compute always solves throughput problems.

How to eliminate wrong answers

Option A is wrong because max degree of parallelism controls how many processors a single query can use; it affects query execution parallelism, not the log throughput ceiling, and increasing it can even increase log generation. Option C is wrong because backup retention is a data-protection setting with no bearing on log rate limits. Option D is wrong because adding compute replicas (named replicas) provides additional read-scale compute but does not raise the primary's log rate limit.

Full explanation →

339

MCQeasy

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

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

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

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

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

AnswerC

Setting encrypt=true forces the JDBC driver to negotiate TLS, while trustServerCertificate=false makes it validate the server certificate chain. Together they satisfy the requirement that all connections are genuinely encrypted and authenticated, preventing silent fallback to unencrypted sessions.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

340

MCQeasy

You are responsible for an Azure SQL Database that hosts a reporting workload. The database runs a large number of ad-hoc queries that consume significant CPU. You need to identify the top CPU-consuming queries to optimize them. Which feature should you use?

A.Automatic tuning

B.Azure SQL Auditing

C.Dynamic management views (DMVs)

D.Query Store

AnswerD

Query Store captures query execution plans and runtime statistics, including CPU usage, duration, and reads. It allows you to identify top resource-consuming queries over time. This makes it the ideal tool for finding and optimizing high-CPU queries in Azure SQL Database.

Why this answer

Query Store is designed to capture and persist query performance data, including CPU time. It provides built-in reports to identify top resource consumers, making it the best choice for finding high-CPU queries. Other features like auditing or automatic tuning do not offer the same level of detailed query-level CPU analysis.

Exam trap

The trap here is confusing automatic tuning with Query Store; automatic tuning acts on recommendations but does not provide the detailed query-level CPU metrics needed for manual optimization.

Full explanation →

341

MCQhard

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

A.The database has one high-availability replica.

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

C.The database uses locally-redundant backup storage.

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

AnswerD

The General Purpose service tier supports zone redundancy when the configuration enables it, which the exhibit's properties confirm. This distinguishes it from Business Critical, which also offers zone redundancy, and from Basic or Standard tiers, which do not support it.

Why this answer

The JSON configuration shows zoneRedundant set to true and the service tier is General Purpose. As of current Azure SQL Database documentation, zone redundancy is supported for the General Purpose tier (vCore purchasing model) in many regions. Therefore, the configuration is valid.

Option D correctly states that the database is in General Purpose tier and supports zone redundancy. Option A is incorrect because highAvailabilityReplicaCount is 0, not 1. Option B is incorrect because zone redundancy is available for General Purpose.

Option C is incorrect because backupStorageRedundancy is Geo (geo-redundant), not locally-redundant.

Exam trap

Candidates may incorrectly assume that zone redundancy is only for Business Critical, but Azure now supports it for General Purpose (vCore) in many regions.

Full explanation →

342

MCQmedium

You manage an Azure SQL Database named OrdersDB in the East US region. The business requires that OrdersDB be readable from a secondary region during a planned regional failover, and that the failover be initiated manually by a database administrator. You create a failover group and add OrdersDB as a member. Which read-write listener endpoint should applications use to connect to OrdersDB after a manual failover to the secondary region?

A.The original server name of OrdersDB in the East US region.

B.The read-only listener endpoint of the failover group.

C.The read-write listener endpoint of the failover group.

D.The private endpoint IP address of OrdersDB in the East US region.

AnswerC

The failover group exposes a read-write listener endpoint that always points to the current primary database. After a manual failover, the listener is updated to route connections to the secondary region, allowing applications to reconnect without changing connection strings. This endpoint is the correct choice for read-write access that must survive a regional failover.

Why this answer

A failover group provides a read-write listener endpoint that automatically redirects connections to the current primary database after a failover. Applications use this listener rather than server-specific names or region-specific endpoints, so connectivity is maintained without connection string changes. The read-only listener is only for read-only workloads, and private endpoint IPs are region-bound and unsuitable for cross-region failover.

Exam trap

The trap here is assuming that the original server name or a private endpoint IP will continue to work after a regional failover, when only the failover group listener is designed to redirect clients.

Full explanation →

343

MCQmedium

You are managing an Azure SQL Database that experiences periods of high CPU usage. You need to identify the top resource-consuming queries and their execution plans to optimize performance. You want to use a built-in feature that provides historical query performance data with minimal configuration. What should you use?

A.Dynamic management views (DMVs) such as sys.dm_exec_query_stats

B.Azure SQL Analytics

C.Automatic tuning

D.Query Store

AnswerD

Query Store automatically captures query execution history, including execution plans, runtime statistics, and wait categories. It is built into Azure SQL Database and requires minimal configuration—just enabling it on the database. It allows you to identify top resource-consuming queries and analyze plan changes over time, making it ideal for performance tuning.

Why this answer

Query Store is a built-in feature that captures query execution history, plans, and runtime statistics. It requires minimal setup and provides the historical data needed to identify top CPU-consuming queries and analyze their execution plans, making it the best choice for performance tuning.

Exam trap

The trap here is thinking that DMVs provide historical data, when they only show current cached statistics and lose history on restart.

Full explanation →

344

MCQmedium

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

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

B.Use Automation Account credential assets

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

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

AnswerC

Managed identities provide secure authentication without storing credentials.

Why this answer

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

Option D is not a valid type.

Full explanation →

345

MCQmedium

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

A.The AuditActionGroup specified does not capture SELECT operations.

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

C.The storage endpoint is incorrectly formatted.

D.The storage account access key is invalid.

AnswerA

Auditing captures only the action groups and actions defined in the audit rule. If the configured AuditActionGroup excludes SELECT, statement-level reads are never recorded, so SELECT operations go unaudited even though the rule runs successfully.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

346

MCQmedium

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

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

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

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

D.Implement horizontal sharding across multiple databases.

AnswerA

Scaling up the DTU or vCore service tier through the Azure portal satisfies the minimal-downtime constraint because Azure SQL Database performs this as an online operation, briefly failing over to the new compute level while the database remains available. This directly addresses the insufficient pricing tier causing peak-hour degradation.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

347

Multi-Selecteasy

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

Select 2 answers

A.Enable Transparent Data Encryption (TDE).

B.Use parameterized queries in the application.

C.Configure Dynamic Data Masking (DDM).

D.Implement stored procedures with input validation.

E.Enable Always Encrypted on sensitive columns.

AnswersB, D

Parameterised queries send user input as bound parameters rather than concatenated SQL text, so the database treats injected payloads as data, never executable code. This directly neutralises the injection vector in the application's SQL-authenticated connection string.

Why this answer

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

Exam trap

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

Full explanation →

348

MCQmedium

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

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

B.Enable zone redundancy on the Hyperscale database.

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

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

AnswerB

Hyperscale zone redundancy provides automatic recovery from zone failure.

Why this answer

Hyperscale databases support zone redundancy, which automatically places database replicas in different availability zones, ensuring availability during a single zone failure without manual intervention. Option A is incorrect because failover groups with a secondary in a different zone are used for SQL Managed Instance or other service tiers, not for Hyperscale zone redundancy. Option C is incorrect because active geo-replication is for disaster recovery across regions, not for zone-level failures.

Option D is incorrect because auto-failover groups are for SQL Managed Instance or other tiers, and zone redundancy is the native solution for Hyperscale.

Full explanation →

349

MCQeasy

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

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

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

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

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

AnswerA

Transparent Data Encryption with a customer-managed key in Azure Key Vault encrypts backups and data files at rest, and the customer controls the key rather than Microsoft. This satisfies the compliance mandate for customer-managed keys on Azure SQL Managed Instance backups.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

350

Multi-Selecthard

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

Select 2 answers

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

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

C.Use Azure Private Link with a private endpoint.

D.Configure server-level IP firewall rules.

E.Enable service endpoints on the subnet.

AnswersB, C

Azure SQL Managed Instance is deployed inside a dedicated subnet within a virtual network, so network security groups applied to that subnet filter inbound and outbound traffic. This satisfies the requirement for configuring network security at the instance level.

Why this answer

Option B is correct because Azure SQL Managed Instance is natively deployed inside an Azure virtual network subnet, and you secure it by using Network Security Groups (NSGs) on that subnet to control inbound and outbound traffic on the required ports (for example, 1433, 5022, 11000-11999). Option C is correct because Azure Private Link with a private endpoint lets clients reach the managed instance over a private IP address within the virtual network, providing private, isolated connectivity rather than exposing it publicly. Option A is not valid because virtual network rules are a feature of Azure SQL Database and Azure Database services, not the configuration model for a Managed Instance, which already lives in a delegated subnet.

Option D is not valid because server-level IP firewall rules apply to Azure SQL Database's logical server, whereas Managed Instance networking is governed by the VNet/subnet and NSG configuration. Option E is not valid because service endpoints are used to restrict access to PaaS services from a subnet, but a Managed Instance is deployed into the subnet itself and does not use service endpoints for its own network security.

Exam trap

The most common trap is confusing Azure SQL Database's firewall and VNet rules with Azure SQL Managed Instance's network security model. Managed Instance relies entirely on VNet integration and NSGs; it does not support virtual network rules, IP firewall rules, or service endpoints.

Full explanation →

351

MCQhard

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

A.minimalTlsVersion 1.2 is not supported in Azure SQL Database

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

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

D.The password does not meet complexity requirements

AnswerC

Enabling azureAdOnlyAuthentication removes SQL authentication entirely, so supplying administratorLogin and administratorLoginPassword conflicts with that setting and fails validation. The template must omit both properties and instead designate a Microsoft Entra ID administrator, satisfying the stem's azureAdOnlyAuthentication constraint.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

352

MCQmedium

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

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

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

C.Enable automatic index tuning.

D.Create the recommended index immediately.

AnswerA

Query Store captures execution plans, runtime statistics and missing-index suggestions per query, letting you confirm the high-CPU queries Intelligent Insights flagged and assess the proposed index's projected impact. This satisfies the stem's requirement to validate the recommendation before implementing it, since Query Store retains historical workload data rather than relying on the advisory alone.

Why this answer

Before acting on Intelligent Insights' missing index recommendation, you should validate it against actual workload evidence. Query Store captures query text, plans, runtime stats, and wait categories, so you can confirm the query is genuinely expensive and that the missing index would help. This avoids creating an index that adds write overhead without meaningful read benefit.

Exam trap

The trap is treating an automated recommendation as a validated fix — candidates often jump to 'create the index' or 'enable automatic tuning' because it sounds proactive, but the question explicitly asks what to do first to validate, which points to Query Store evidence.

How to eliminate wrong answers

Option B is wrong because scaling up the managed instance addresses capacity, not the root cause of the high CPU — it may mask the problem while increasing cost, and it does not validate the index recommendation. Option C is wrong because enabling automatic index tuning would apply changes without first validating them, which is risky on a critical OLTP database and skips the requested validation step. Option D is wrong because creating the index immediately ignores the instruction to validate first; an unvalidated index can degrade write performance and consume storage.

Full explanation →

353

MCQeasy

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

A.Azure Advisor

B.Intelligent Insights

C.Automatic tuning

D.Query Store

AnswerC

Automatic tuning continuously analyses workload telemetry and applies index create, drop, and rebuild recommendations without manual intervention, directly satisfying the requirement to adjust indexing based on workload patterns. Unlike manual index maintenance or Query Store alone, it acts automatically, making it the appropriate choice for Azure SQL Database.

Why this answer

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

Exam trap

A common mistake is to confuse features that provide recommendations (Azure Advisor, Intelligent Insights) or capture query performance data (Query Store) with Automatic tuning, which is the only feature that automatically applies index changes based on workload patterns without manual intervention.

How to eliminate wrong answers

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

Full explanation →

354

MCQeasy

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

A.Azure Automation

B.Microsoft Sentinel

C.Azure Logic Apps

D.Azure Monitor

AnswerD

Azure Monitor hosts metric alerts that evaluate platform metrics such as storage space used against a threshold, triggering action groups to notify the operations team. It satisfies the automatic notification requirement without custom code, unlike Query Store or Elastic Jobs.

Why this answer

Azure Monitor is the native monitoring and alerting service in Azure. It can collect metrics from Azure SQL Database, including storage usage, and trigger alerts based on thresholds like 80%. You can create an alert rule that sends notifications via email, SMS, or webhook to the operations team.

Exam trap

DP-300 often tests the distinction between monitoring/alerting (Azure Monitor) and automation/workflow (Logic Apps, Automation), so candidates may incorrectly choose a service that can send notifications but is not designed for metric-based alerting.

How to eliminate wrong answers

Option A is wrong because Azure Automation is for process automation and configuration management, not for monitoring and alerting on metrics. Option B is wrong because Microsoft Sentinel is a SIEM/SOAR solution for security analytics, not for operational metric alerts. Option C is wrong because Azure Logic Apps is for workflow automation and integration, not for generating alerts based on resource metrics; it could be used as an action in an alert, but not as the alerting service itself.

Full explanation →

355

MCQeasy

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

A.SQL Vulnerability Assessment

B.Advanced Threat Protection

C.Microsoft Defender for Cloud

D.Azure SQL Auditing

AnswerD

Azure SQL Auditing writes query events to a Log Analytics workspace, satisfying the requirement to audit and log all queries for security analysis. It captures statement-level activity directly, unlike metrics or Query Store, which record performance data rather than a security audit trail.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

356

MCQhard

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

A.Add additional email addresses to ensure notification.

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

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

D.Remove the disabledAlerts entries to enable all alerts.

AnswerC

Vulnerability assessment is inactive until its state is Enabled, and the audit mandates 90-day result retention. Setting retentionDays to 90 meets that minimum while enabling scans satisfies the first requirement, so both properties must change together on the server's configuration.

Why this answer

The requirement is to enable vulnerability assessment scans and retain results for at least 90 days. Option C correctly sets the state to Enabled (turning on the scans) and sets retentionDays to 90 (meeting the retention requirement). The other options either leave the state disabled or address unrelated settings like email notifications or alert suppression.

Exam trap

DP-300 often tests the distinction between enabling a feature and configuring its retention, and candidates may mistakenly think that changing retention alone or adjusting notifications is sufficient without enabling the feature.

How to eliminate wrong answers

Option A is wrong because adding email addresses only affects who receives notifications; it does not enable scans or set retention. Option B is wrong because keeping the state as Disabled means scans are not enabled, violating the requirement. Option D is wrong because removing disabledAlerts entries changes which alerts are active but does not enable vulnerability assessment scans or set retention.

Full explanation →

357

MCQhard

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

A.General Purpose service tier.

B.Read scale-out.

C.Geo-redundant backup storage.

D.Zone redundancy enabled on Business Critical tier.

AnswerD

Zone redundancy on the Business Critical tier places the primary and secondary replicas across separate availability zones, so a zonal failure triggers failover to a synchronously committed replica with zero data loss. This satisfies the availability requirement during a zonal failure.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

358

Multi-Selecthard

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

Select 3 answers

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

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

C.A service connection to Azure with appropriate permissions.

D.A schema compare tool to generate deployment scripts.

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

AnswersA, B, C

The Azure SQL Database deployment task in a release pipeline executes the DACPAC or SQL script against the target database, providing the actual deployment mechanism. Without this task, the pipeline has no way to apply schema changes to Azure SQL Database.

Why this answer

Option A is correct because the 'Azure SQL Database deployment' task in an Azure DevOps release pipeline is the built-in mechanism that executes the DACPAC/BACPAC against the target Azure SQL Database, making it essential for automation. Option B is correct because a SQL database project (.sqlproj) declaratively defines the schema and is compiled into a DACPAC, which is the artifact the deployment task consumes to apply schema changes. Option C is correct because the pipeline needs an Azure service connection (ARM service connection) with sufficient permissions to authenticate and deploy to the Azure SQL Database.

Option D is not required because the Azure SQL Database deployment task uses SqlPackage/DACPAC deployment natively and does not depend on a separate schema compare tool. Option E is not required because Microsoft-hosted agents already include the necessary SQL tooling, so a self-hosted agent with SQL tools is optional rather than mandatory.

Exam trap

DP-300 often tests the minimum components for CI/CD database deployment; candidates may incorrectly assume a schema compare tool or self-hosted agent is required, when the built-in task and hosted agents suffice.

Full explanation →

359

MCQeasy

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

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

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

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

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

AnswerA

Serverless compute with auto-pause bills only for active usage, so the database pauses after the one-hour delay and resumes automatically on the next connection. This satisfies the weekday 9-to-5 availability constraint while eliminating compute charges during inactive evenings and weekends, minimising cost for this non-production workload.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

360

MCQhard

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

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

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

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

D.Enable 'autoRotationEnabled' property

AnswerA

Customer-managed keys require the TDE protector to be stored in Azure Key Vault, so the ARM template must supply keyVaultUri, keyName and keyVersion to point at that key. Without these properties, Azure SQL Database defaults to a service-managed key, breaching the policy.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

361

MCQmedium

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

A.Long-term retention (LTR) policy

B.Geo-redundant storage (GRS)

C.Automated backups

D.Point-in-time restore (PITR)

AnswerA

Long-term retention stores full backups in Azure Blob storage for up to 10 years, beyond the default point-in-time and short-term retention windows. That satisfies the compliance requirement without relying on the standard automated backup retention period.

Why this answer

Long-term retention (LTR) policy is the correct feature for keeping database backups for 10 years, as it supports retention of full backups for up to 10 years. Point-in-time restore (PITR) is limited to a maximum of 35 days and cannot meet the 10-year requirement. Geo-redundant storage (GRS) provides geographic redundancy for backups but does not extend the retention period.

Automated backups are retained for a maximum of 35 days by default and cannot be configured for 10-year retention.

Full explanation →

362

MCQmedium

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

A.Active geo-replication

B.Zone-redundant availability

C.Built-in high availability with a standby replica

D.Business Critical service tier

AnswerC

The General Purpose service tier provides built-in high availability through automatic compute failover to a different node. While often described as a standby replica, the mechanism is actually compute failover with shared storage, ensuring availability during planned patching events.

Why this answer

Azure SQL Database in the General Purpose service tier includes built-in high availability with a standby replica, which is provisioned automatically and used to fail over during planned maintenance or unplanned outages. This built-in HA ensures the database remains available during patching events without requiring the customer to configure geo-replication or zone redundancy. The standby replica is maintained synchronously and failover is automatic.

Exam trap

DP-300 often tests the difference between default built-in HA and optional features like zone redundancy or geo-replication, and candidates incorrectly select the more advanced feature when the question asks what is provided by default.

How to eliminate wrong answers

Option A is wrong because active geo-replication is a customer-configured disaster recovery feature for cross-region replication, not the default HA mechanism for planned maintenance. Option B is wrong because zone-redundant availability is an optional configuration (available in Premium/Business Critical and some General Purpose configurations) that must be explicitly enabled, not provided by default. Option D is wrong because Business Critical is a different service tier with local SSD and higher IOPS, not the HA feature itself, and the question specifies General Purpose.

Full explanation →

363

MCQmedium

You are configuring Azure SQL Database for a new application. The security policy requires that all connections use Microsoft Entra authentication and that the database blocks IP addresses from outside your corporate network. You also need to ensure that the application can connect without storing credentials in code. Which combination of features should you implement?

A.Always Encrypted, VNet service endpoints, and SQL authentication

B.Microsoft Entra authentication, firewall rules, and managed identity

C.Transparent Data Encryption, IP firewall rules, and connection strings

D.Azure Defender for SQL, firewall rules, and service principal

AnswerB

Managed identity lets the application obtain Microsoft Entra tokens without embedded credentials, satisfying the no-stored-secrets constraint. Firewall rules restrict access to corporate IP ranges, blocking external addresses, while Microsoft Entra authentication replaces SQL logins entirely. Together these three features meet every stated requirement.

Why this answer

It satisfies all three requirements: Microsoft Entra authentication enforces identity-based access, firewall rules block IP addresses outside the corporate network, and a managed identity allows the application to connect without storing credentials in code by using a system-assigned or user-assigned identity to obtain an access token from Microsoft Entra ID.

Exam trap

The trap here is that candidates often confuse managed identity with a service principal, not realizing that a service principal still requires a secret or certificate to be stored, whereas a managed identity eliminates credential storage entirely.

How to eliminate wrong answers

Option A is wrong because Always Encrypted protects data at rest and in transit but does not enforce authentication or IP-based blocking, and SQL authentication does not meet the Microsoft Entra authentication requirement. Option C is wrong because Transparent Data Encryption (TDE) encrypts data at rest but does not control authentication or credentialless connections, and connection strings typically contain credentials. Option D is wrong because Azure Defender for SQL provides security monitoring and threat detection, not authentication or credentialless connectivity, and a service principal still requires credential management (e.g., client secret or certificate) unless combined with managed identity.

Full explanation →

364

Multi-Selecteasy

Which TWO metrics are available in Azure Monitor for an Azure SQL Database that can be used to set autoscale rules? (Select two.)

Select 2 answers

A.CPU percentage

B.Log write throughput

C.Deadlock count

D.DTU percentage

E.Query Store size

AnswersA, D

CPU percentage is a native Azure Monitor metric for Azure SQL Database, reflecting average compute utilisation. It satisfies the autoscale requirement because Azure Monitor autoscale rules can trigger scale actions directly from this metric, unlike log-based or non-emitted measures.

Why this answer

Option A (CPU percentage) is correct because Azure SQL Database exposes the 'cpu_percent' metric through Azure Monitor, which reports the average CPU utilization of the database and is a standard metric used to drive autoscale rules (for example, scaling up when CPU exceeds a threshold). Option D (DTU percentage) is correct because the 'dtu_consumption_percent' metric measures the percentage of the Database Transaction Unit limit consumed and is one of the most commonly used metrics for autoscale decisions on DTU-based databases. The unmarked options do not belong: Log write throughput is not a directly exposed Azure Monitor metric for autoscale on Azure SQL Database, Deadlock count is a diagnostic/query-level statistic rather than an autoscale metric, and Query Store size is an internal Query Store property, not an Azure Monitor metric available for autoscale rules.

Exam trap

DP-300 often tests which metrics are available for autoscale — candidates pick diagnostic metrics like deadlock count or Query Store size, which are not autoscale triggers.

Full explanation →

365

MCQmedium

You are configuring security for an Azure SQL Database. The security team requires that all administrative actions on the server and databases are logged to an Azure Storage account, and that the logs are retained for 90 days. You need to configure auditing to meet these requirements with minimal effort. What should you do?

A.Enable auditing at the server level and set the retention period to 90 days, targeting an Azure Storage account.

B.Enable auditing at the server level and set the retention period to 0 (unlimited), targeting an Azure Storage account.

C.Enable auditing at the server level and target an Azure Log Analytics workspace with a 90-day retention.

D.Enable auditing at the database level for each database and set the retention period to 90 days, targeting an Azure Storage account.

AnswerA

Server-level auditing in Azure SQL Database automatically audits all databases on the server and can target an Azure Storage account. Setting the retention period to 90 days meets the retention requirement. This configuration applies to all current and future databases, minimizing administrative effort.

Why this answer

Server-level auditing with a 90-day retention targeting an Azure Storage account audits all databases on the server and meets the retention requirement. Database-level auditing would require per-database configuration, and using Log Analytics does not match the storage account requirement. Unlimited retention does not meet the 90-day specification.

Exam trap

The trap here is thinking that database-level auditing is needed for each database, but server-level auditing covers all databases and reduces administrative overhead.

Full explanation →

366

MCQhard

Your company has an Azure SQL Database that contains sensitive financial data. You need to ensure that database administrators cannot view the actual data while still being able to perform administrative tasks such as backups and index maintenance. Which feature should you implement?

A.Always Encrypted with column master key stored in Azure Key Vault

B.Row-Level Security

C.Dynamic Data Masking

D.Transparent Data Encryption

AnswerA

Always Encrypted keeps data encrypted in memory and at rest, so administrators querying the database see only ciphertext while backups and index maintenance still function. Storing the column master key in Azure Key Vault separates key custody from the administrators, satisfying the requirement.

Why this answer

Always Encrypted with the column master key stored in Azure Key Vault ensures that sensitive data is encrypted at the client side and the encryption keys are never exposed to the database engine. This means database administrators (DBAs) can perform administrative tasks like backups and index maintenance on the encrypted columns without ever being able to view the plaintext data, because the SQL Server instance only sees ciphertext.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking with encryption, assuming it provides strong data protection, when in fact it is a lightweight obfuscation that can be easily circumvented by privileged users.

How to eliminate wrong answers

Option B (Row-Level Security) is wrong because it controls which rows a user can see based on a predicate function, but it does not prevent DBAs with elevated permissions (e.g., db_owner) from bypassing the security policy or viewing the data directly. Option C (Dynamic Data Masking) is wrong because it only obfuscates data at the application layer; users with high privileges like db_owner can still query the unmasked data using SELECT or by casting to a different type. Option D (Transparent Data Encryption) is wrong because it encrypts data at rest on disk but does not protect data from being read by authorized users (including DBAs) when the database is online and queries are executed.

Full explanation →

367

MCQhard

Refer to the exhibit. You run the Azure CLI command to check the configuration of an Azure SQL Database named db1. The output shows zoneRedundant is true and replicationRole is Primary. Which statement is true about this database?

A.The database is configured with active geo-replication to a secondary region.

B.The database is zone-redundant and is the primary database in its replication relationship.

C.The database is a Hyperscale database with zone redundancy enabled.

D.The database has a readable secondary replica in a different Azure availability zone.

AnswerB

The CLI output directly reports zoneRedundant true, meaning replicas span availability zones, and replicationRole Primary, meaning this database accepts writes in its geo-replication relationship. Both properties are read straight from the returned configuration, so the statement matches the observed values.

Why this answer

The output shows zoneRedundant is true, meaning the database is deployed across multiple availability zones within its region for high availability, and replicationRole is Primary, meaning it is the primary in a replication relationship (such as a failover group or active geo-replication). Option B correctly combines both facts. The zoneRedundant flag has nothing to do with cross-region replication, so options A and D are incorrect, and Hyperscale is a separate service tier not indicated by these properties.

Exam trap

DP-300 often tests the confusion between zone redundancy (intra-region HA) and geo-replication (cross-region DR), so candidates incorrectly assume zoneRedundant=true implies a secondary region or readable replica.

How to eliminate wrong answers

Option A is wrong because zoneRedundant=true indicates intra-region zone redundancy, not active geo-replication to a secondary region; geo-replication is reflected by replicationRole and partner server settings, not the zoneRedundant flag. Option C is wrong because the output does not indicate the Hyperscale service tier; zone redundancy is available across multiple tiers (General Purpose, Business Critical, Hyperscale) and the properties shown do not identify the tier. Option D is wrong because a readable secondary replica in a different availability zone is not implied by zoneRedundant=true; zone redundancy replicates storage across zones for HA, not for read offloading, and replicationRole=Primary simply means this is the primary replica in a replication topology.

Full explanation →

368

MCQeasy

Your company runs a mission-critical Azure SQL Database in the East US region. To meet an RPO of 5 seconds and an RTO of 30 minutes in the event of a regional outage, which deployment option should you choose?

A.Failover groups with auto-failover policy

B.Zone-redundant configuration

C.Point-in-time restore

D.Active geo-replication with manual failover

AnswerA

Failover groups with an auto-failover policy replicate databases asynchronously to a secondary region, typically achieving an RPO under 5 seconds, and automatically redirect connections after an outage. This satisfies both the 5-second RPO and the 30-minute RTO, unlike geo-restore, which can take hours.

Why this answer

Failover groups with auto-failover policy provide automatic failover with an RPO of 5 seconds (default) and RTO of 30 minutes. Active geo-replication requires manual failover, which does not meet the RTO. Zone-redundant configuration only protects within a region.

Backup restore has much higher RPO/RTO.

Full explanation →

369

MCQeasy

You are responsible for an Azure SQL Database that hosts a financial application. The database is in the General Purpose service tier. You need to ensure that the database automatically scales compute resources based on workload demand without manual intervention. What should you configure?

A.Create an elastic pool and add the database to it.

B.Enable serverless compute tier for the database.

C.Enable automatic tuning for the database.

D.Configure auto-scaling in the Azure portal for the database.

AnswerB

The serverless compute tier automatically scales compute resources based on workload demand and bills per second of compute usage. It pauses the database during inactive periods, which can reduce cost. For a financial application with variable demand, serverless provides automatic scaling without manual intervention, meeting the requirement. It is available in the General Purpose service tier, so no tier change is needed.

Why this answer

The serverless compute tier in Azure SQL Database automatically scales compute based on workload and can pause during inactivity. It is designed for single databases with intermittent or unpredictable usage. Unlike manual scaling or elastic pools, serverless requires no manual intervention to adjust resources.

Automatic tuning optimizes queries but does not scale compute. Therefore, enabling serverless compute tier is the correct choice to achieve automatic scaling without manual intervention.

Exam trap

The trap here is confusing automatic tuning with automatic scaling; automatic tuning optimizes queries, not compute resources.

Full explanation →

370

MCQhard

Refer to the exhibit. An automatic tuning recommendation to force the last good plan is active. What should the database administrator do next?

A.Immediately implement the DROP_INDEX recommendation to reduce overhead

B.Create the recommended index to improve performance

C.Revert the plan force because it is causing regression

D.Monitor the query performance to confirm the forced plan resolves the regression

AnswerD

Forcing a plan changes execution behaviour, so the administrator must verify the regression is actually resolved. Monitoring query performance confirms the forced plan restores the previous good performance before the recommendation is left active or reverted.

Why this answer

When an automatic tuning recommendation to force the last good plan is active, the correct next step is to monitor the query performance to confirm that the forced plan resolves the regression. This is because plan forcing is a corrective action that may or may not improve performance; validation through monitoring ensures the change is beneficial before taking further steps like creating or dropping indexes.

Exam trap

Azure often tests the misconception that an automatic tuning recommendation should be immediately implemented or reverted without first monitoring its impact, leading candidates to choose premature actions like dropping indexes or reverting plans.

How to eliminate wrong answers

Option A is wrong because dropping an index based on a recommendation that is unrelated to the plan force could degrade performance if the index is still needed for other queries. Option B is wrong because creating a recommended index is not the immediate action when a plan force is active; the forced plan should be validated first to ensure it resolves the regression. Option C is wrong because reverting the plan force without monitoring its effect is premature; the forced plan may be the correct fix, and reverting could reintroduce the regression.

Full explanation →

371

MCQeasy

Your organization uses Azure SQL Database and wants to automatically detect and alert on potential SQL injection attacks. Which Azure service should you enable?

A.Azure SQL Database Vulnerability Assessment

B.Azure SQL Auditing

C.Microsoft Defender for SQL

D.Microsoft Sentinel

AnswerC

Microsoft Defender for SQL provides Advanced Threat Protection, continuously analysing query patterns and audit logs to flag anomalous behaviour indicative of SQL injection. It satisfies the requirement for automatic detection and alerting without manual monitoring, raising alerts through Microsoft Defender for Cloud and integrating with Microsoft Entra ID for investigation.

Why this answer

Microsoft Defender for SQL (option C) is the correct answer because it provides advanced SQL security capabilities, including a dedicated SQL injection detection engine that analyzes database activity patterns and alerts on suspicious queries. Unlike other options, Defender for SQL specifically monitors for SQL injection attempts by evaluating query anomalies and known attack signatures, making it the appropriate service for automatic detection and alerting.

Exam trap

The trap here is that candidates often confuse Vulnerability Assessment (which finds weaknesses) or Auditing (which logs events) with the active threat detection capability of Defender for SQL, not realizing that only Defender for SQL provides automatic, real-time SQL injection detection and alerting without additional configuration.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database Vulnerability Assessment focuses on identifying misconfigurations, missing patches, and security weaknesses in the database schema, not on real-time detection of SQL injection attacks. Option B is wrong because Azure SQL Auditing logs database events for compliance and forensic analysis but does not include built-in threat detection or alerting for SQL injection patterns. Option D is wrong because Microsoft Sentinel is a SIEM (Security Information and Event Management) solution that aggregates logs from multiple sources; while it can ingest SQL audit logs and be configured to detect SQL injection, it is not the native Azure service specifically designed for automatic detection and alerting on SQL Database, and it requires additional setup and custom analytics rules.

Full explanation →

372

MCQeasy

Your company has an Azure SQL Database with a failover group configured to a secondary region. The primary region experiences a temporary network issue. The failover group is set to automatic failover with a grace period of 1 hour. What will happen?

A.Automatic failover will happen immediately.

B.You must manually initiate failover.

C.Automatic failover will start after 1 hour if the primary is still unreachable.

D.The secondary database will be deleted and re-created.

AnswerC

With automatic failover and a one-hour grace period, Azure waits that duration while the primary remains unreachable, then promotes the secondary. The grace period delays the switchover, so failover only begins once the hour elapses and the outage persists.

Why this answer

In an Azure SQL failover group with automatic failover and a grace period of 1 hour, the failover is not immediate. Azure waits for the grace period to elapse while the primary remains unreachable before initiating automatic failover to the secondary region. This prevents unnecessary failovers during brief network blips.

Exam trap

DP-300 often tests the grace period behavior in failover groups — candidates assume automatic failover is immediate, but the grace period delays it to prevent unnecessary failovers.

How to eliminate wrong answers

Option A is wrong because automatic failover is not immediate when a grace period is configured — the grace period exists specifically to delay failover and avoid false positives. Option B is wrong because the failover group is set to automatic failover, so manual initiation is not required; manual failover is only needed when automatic failover is disabled. Option D is wrong because failover does not delete or re-create the secondary database — it promotes the secondary to primary and the old primary becomes the new secondary once it recovers.

Full explanation →

373

MCQmedium

You are managing an Azure SQL Database that is experiencing intermittent performance degradation. Query Store shows that a specific query's execution plan changed, causing increased CPU usage. You need to ensure consistent performance without rewriting the application. What should you do?

A.Increase the DTU/service tier of the database

B.Create a missing index recommendation

C.Drop and recreate the index used by the query

D.Force the previous query plan using Query Store

AnswerD

Forcing the previous plan through Query Store pins the known-good execution plan, bypassing the regressed plan chosen by the optimiser. This restores consistent CPU performance without application changes, directly satisfying the requirement to avoid rewriting code. Plan forcing applies immediately to the identified query, resolving the intermittent degradation.

Why this answer

Force the previous query plan using Query Store. This approach directly addresses the root cause by locking the query to a known good plan, ensuring consistent performance without application changes. Option A is incorrect because increasing the DTU/service tier may temporarily improve performance but does not fix the plan regression.

Option B is incorrect because creating a missing index recommendation may help but does not guarantee the query will use the previous plan. Option C is incorrect because dropping and recreating the index is disruptive and may not force the plan to revert.

Full explanation →

374

MCQhard

You are troubleshooting a performance issue in an Azure SQL Database. The database is in the Hyperscale service tier. You observe that read queries are slow, and you suspect that a specific query plan is causing excessive physical reads. You want to identify the query and its plan, and then force a better plan if available. Which tool should you use to capture and analyze the plan, and then force a plan?

A.Extended Events session with sql_batch_completed and query_post_execution_showplan events.

B.Dynamic management views sys.dm_exec_requests and sys.dm_exec_sql_text.

C.Query Store, using the Top Resource Consuming Queries report and plan forcing.

D.Azure SQL Database automatic tuning with CREATE_INDEX.

AnswerC

Query Store is fully supported in the Hyperscale service tier and captures query plans and runtime statistics. It allows you to identify resource-intensive queries, view their plans, and force a specific plan. This directly meets the need to capture, analyze, and force a plan for a query causing excessive physical reads.

Why this answer

Query Store is the appropriate tool because it captures query plans and performance data over time, even in the Hyperscale tier. It enables you to identify the query with excessive physical reads, review its plan history, and force a better plan. Other tools either lack historical data or do not support plan forcing.

Exam trap

The trap here is assuming that Hyperscale does not support Query Store or plan forcing, when in fact Query Store is fully supported and is the recommended tool for plan analysis and forcing.

Full explanation →

375

Multi-Selecthard

You need to protect Azure SQL Database from SQL injection attacks. Which THREE of the following measures should you implement?

Select 3 answers

A.Enable Microsoft Defender for SQL to detect and alert on SQL injection.

B.Use parameterized queries or stored procedures in the application.

C.Implement dynamic data masking to hide sensitive data from unauthorized users.

D.Use a web application firewall (WAF) in front of the application to filter malicious inputs.

E.Enable Transparent Data Encryption (TDE) to encrypt the database at rest.

AnswersA, B, D

Microsoft Defender for SQL provides threat detection that identifies anomalous and injection-like query patterns against the database, raising alerts for investigation. It adds a detection layer that complements input validation and query parameterisation, forming part of a layered defence.

Why this answer

Option A is correct because Microsoft Defender for SQL provides advanced threat protection that specifically detects anomalous and potentially harmful activities such as SQL injection attempts against Azure SQL Database, generating alerts and recommendations. Option B is correct because parameterized queries and stored procedures ensure that user input is treated as data rather than executable SQL code, which is the fundamental application-level defense against SQL injection. Option D is correct because an Azure Web Application Firewall (WAF), for example on Application Gateway or Front Door, inspects HTTP/HTTPS traffic and blocks common injection patterns before they reach the application and database.

Option C is not correct because dynamic data masking only obscures sensitive data in query results for unauthorized users; it does not prevent or detect SQL injection. Option E is not correct because Transparent Data Encryption protects data at rest by encrypting database files, but it does not stop SQL injection, which exploits the application's query logic rather than the storage layer.

Exam trap

The trap here is that candidates often confuse data protection features like dynamic data masking or TDE with SQL injection prevention, when in fact they address entirely different threats (data exposure at query time vs. data at rest encryption).

Full explanation →

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