Microsoft Azure Database Administrator Associate DP-300 DP-300 Questions 151–225 | Page 3/8

151

MCQeasy

You have an Azure SQL Database that stores sensitive customer data. You need to ensure that the data is encrypted at rest using a customer-managed key stored in Azure Key Vault. What should you configure?

A.Configure dynamic data masking (DDM).

B.Implement row-level security (RLS) to restrict access.

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

D.Enable Always Encrypted for the sensitive columns.

AnswerC

Transparent Data Encryption with a customer-managed key satisfies the encryption-at-rest requirement by wrapping the database encryption key with an asymmetric key held in Azure Key Vault. This gives you control over the key lifecycle, unlike service-managed keys, and TDE operates beneath the application layer, so no schema or query changes are needed.

Why this answer

Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault encrypts the database at rest, using a key that you control and rotate independently. This meets the requirement for encryption at rest with a customer-managed key, as TDE performs real-time I/O encryption and decryption of the data and log files without requiring application changes.

Exam trap

The trap here is that candidates often confuse Always Encrypted (which encrypts specific columns at the client side) with TDE (which encrypts the entire database at rest), and they may choose Always Encrypted because it also uses Azure Key Vault, but it does not meet the 'encryption at rest for the entire database' requirement.

How to eliminate wrong answers

Option A is wrong because Dynamic Data Masking (DDM) obfuscates data in query results to unauthorized users but does not encrypt data at rest; it is a presentation-layer control. Option B is wrong because Row-Level Security (RLS) restricts row access based on user context or predicates, but it does not provide encryption at rest. Option D is wrong because Always Encrypted encrypts sensitive columns at the client-side, protecting data in transit and at rest, but it requires application changes and does not encrypt the entire database at rest; the question specifies encryption at rest for the entire database, not just specific columns.

Full explanation →

152

MCQeasy

Your company 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 Auditing

B.SQL Vulnerability Assessment

C.Microsoft Defender for Cloud (formerly Azure Security Center) with Advanced Threat Protection for Azure SQL Database

D.Azure Policy

AnswerC

Microsoft Defender for Cloud's Advanced Threat Protection for Azure SQL Database analyses audit logs to detect anomalous queries indicative of SQL injection, raising alerts automatically. This directly satisfies the requirement to detect and alert on potential injection attacks without manual monitoring.

Why this answer

Microsoft Defender for Cloud with Advanced Threat Protection for Azure SQL Database is the correct choice because it specifically detects anomalous activities indicating SQL injection attempts, such as unusual SQL queries or patterns, and triggers security alerts. Azure SQL Auditing only logs database events for compliance and forensic analysis, not real-time threat detection. SQL Vulnerability Assessment identifies configuration weaknesses but does not monitor for active attacks.

Azure Policy enforces compliance rules but lacks intrusion detection capabilities.

Exam trap

The trap here is that candidates confuse Azure SQL Auditing (logging) with threat detection, assuming that because auditing records events, it can also alert on attacks, but it lacks the real-time analysis and machine learning required for SQL injection detection.

How to eliminate wrong answers

Option A is wrong because Azure SQL Auditing captures and stores database event logs for auditing and compliance, but it does not analyze logs in real-time to detect or alert on SQL injection attacks. Option B is wrong because SQL Vulnerability Assessment scans for misconfigurations and missing patches, not for active malicious activity like SQL injection. Option D is wrong because Azure Policy enforces resource compliance rules (e.g., requiring TDE or firewall rules) but does not provide threat detection or alerting for database attacks.

Full explanation →

153

MCQeasy

You are a database administrator for a healthcare organization. You need to deploy a new Azure SQL Database that stores protected health information (PHI). The database must be encrypted at rest using a customer-managed key in Azure Key Vault. Additionally, you need to ensure that backups are also encrypted with the same key. Which configuration should you use?

A.Use column-level encryption with a certificate

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

C.Enable Transparent Data Encryption with a service-managed key

D.Enable Always Encrypted with keys stored in Azure Key Vault

AnswerB

Transparent Data Encryption with a customer-managed key in Azure Key Vault encrypts the database, its backups, and transaction logs at rest using the same asymmetric key. This directly satisfies the requirement that backups remain encrypted under the identical customer-managed key, unlike service-managed keys or Always Encrypted, which protects only specific columns.

Why this answer

Transparent Data Encryption (TDE) with a customer-managed key from Azure Key Vault is the correct choice because it encrypts the database at rest, including data files and log files, using a key that you control and manage in Azure Key Vault. This meets the requirement for encrypting protected health information (PHI) at rest with a customer-managed key, and TDE automatically ensures that backups are encrypted with the same database encryption key (DEK), which is protected by the customer-managed key.

Exam trap

The trap here is that candidates often confuse Always Encrypted (which encrypts data at the column level and requires application changes) with TDE (which encrypts the entire database at rest transparently), or they mistakenly think that service-managed keys satisfy a customer-managed key requirement.

How to eliminate wrong answers

Option A is wrong because column-level encryption with a certificate encrypts individual columns, not the entire database at rest, and does not automatically encrypt backups with the same key; it also requires application changes and does not meet the requirement for at-rest encryption of the whole database and backups. Option C is wrong because Transparent Data Encryption with a service-managed key uses a key managed by Microsoft, not a customer-managed key, so it fails the requirement for a customer-managed key in Azure Key Vault. Option D is wrong because Always Encrypted encrypts data in transit and at rest at the column level, but it does not encrypt the entire database or backups automatically; it also requires client-side key management and application changes, and does not fulfill the requirement for at-rest encryption of backups with the same key.

Full explanation →

154

MCQeasy

You are deploying a new Azure SQL Database for a small internal inventory application. The workload is steady, with predictable usage during business hours, and the application team requires a fixed monthly cost with no risk of unexpected overage charges. You need to choose a purchasing model and service tier that meets these requirements. What should you do?

A.Deploy the database by using the vCore purchasing model with the General Purpose service tier and configure auto-scaling.

B.Deploy the database by using the DTU purchasing model with a Standard service tier.

C.Deploy the database by using the DTU purchasing model with the Premium service tier.

D.Deploy the database by using the vCore purchasing model with the Business Critical service tier.

AnswerB

The DTU purchasing model offers fixed, predictable pricing per DTU tier, and the Standard tier is designed for steady, moderate workloads like a small internal application. It provides a simple fixed monthly cost with no risk of unexpected overages, matching the requirement exactly.

Why this answer

The DTU purchasing model with the Standard service tier provides a fixed monthly cost and is designed for steady, moderate workloads, which matches the small internal inventory application. The vCore model, whether Business Critical or General Purpose with auto-scaling, introduces variable costs that do not meet the fixed-cost requirement, and the Premium DTU tier is unnecessarily costly.

Exam trap

The trap here is assuming that auto-scaling always saves money or that a higher service tier is required for reliability, when the key requirement is a fixed, predictable monthly cost.

Full explanation →

155

MCQmedium

You are managing an Azure SQL Database that runs a critical line-of-business application. Users report that a specific query is running slower than usual. You identify that the query is performing a clustered index scan on a large table with over 10 million rows. The table has a clustered index on an identity column and a nonclustered index on a frequently filtered column. You need to minimize the query execution time without adding additional indexes. What should you do?

A.Increase the service tier of the Azure SQL Database to provide more resources.

B.Update all statistics on the table.

C.Rebuild the clustered index to reduce fragmentation.

D.Update the statistics on the nonclustered index only.

AnswerB

Updating all statistics on the table refreshes the histograms and density information for every index and column, giving the query optimizer a current view of data distribution. With accurate cardinality estimates, the optimizer can determine that the predicate is selective and choose an index seek instead of a scan. This is the targeted fix when a plan becomes suboptimal due to stale statistics, and it is the only option here that directly addresses the optimizer's input.

Why this answer

The query is performing a clustered index scan, which means SQL Server is reading all rows in the table. Outdated statistics can cause the optimizer to choose a scan instead of a more efficient seek. Updating all statistics on the table (option B) provides the optimizer with fresh distribution information, potentially allowing it to choose a better execution plan that avoids the scan, thereby reducing query execution time without adding indexes.

Exam trap

The trap here is that candidates often assume a scan is always due to fragmentation (option C) or resource constraints (option A), when in fact the most common cause is stale statistics leading to a poor execution plan choice.

How to eliminate wrong answers

Option A is wrong because increasing the service tier provides more resources (CPU, IO, memory) but does not address the root cause of a suboptimal execution plan; the query may still perform a scan, just faster, and this incurs additional cost. Option C is wrong because rebuilding the clustered index reduces fragmentation, but fragmentation is unlikely to cause a scan to be chosen over a seek; the issue is plan choice, not physical index structure. Option D is wrong because updating only the nonclustered index statistics does not help if the optimizer is considering the clustered index scan; the statistics on the clustered index (or the entire table) must be updated to influence the plan choice for that scan.

Full explanation →

156

Drag & Dropmedium

Drag and drop the steps to configure a failover group 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

Failover groups require a secondary server, then creating the group, adding databases, configuring policy, and testing.

Full explanation →

157

MCQmedium

You are deploying Azure SQL Database for a multi-tenant application. Each tenant's data must be isolated. You need to ensure that tenants cannot access each other's data even if there is a SQL injection vulnerability. Which security feature should you implement?

A.Use Always Encrypted to encrypt sensitive columns.

B.Configure Azure SQL Database auditing to monitor cross-tenant access.

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

D.Implement row-level security (RLS) with a security policy that filters rows by tenant ID.

AnswerD

Row-level security with a security policy filtering by tenant ID enforces isolation inside the database engine itself, so a SQL injection cannot return another tenant's rows regardless of query construction. This satisfies the requirement that tenants cannot access each other's data.

Why this answer

Row-level security (RLS) is the correct choice because it enforces data isolation at the database engine level by filtering rows based on a tenant ID predicate. Even if a SQL injection vulnerability allows an attacker to execute arbitrary queries, RLS ensures that only rows belonging to the attacker's tenant are returned, preventing cross-tenant data access. This is a defense-in-depth measure that works regardless of application-layer flaws.

Exam trap

The trap here is that candidates often confuse data-at-rest encryption (TDE or Always Encrypted) with access control, mistakenly believing encryption alone can prevent unauthorized row access during a SQL injection attack.

How to eliminate wrong answers

Option A is wrong because Always Encrypted protects data at rest and in transit by encrypting specific columns, but it does not control which rows a query can return; an attacker with a SQL injection could still retrieve all encrypted rows (though they would be ciphertext) or bypass the encryption if the injection occurs before decryption. Option B is wrong because auditing only logs database activity for compliance and monitoring; it does not prevent unauthorized access or block cross-tenant data retrieval in real time. Option C is wrong because Transparent Data Encryption (TDE) encrypts the entire database at rest but provides no row-level filtering or access control; an attacker exploiting SQL injection could still read all decrypted data once the database is in use.

Full explanation →

158

MCQmedium

A developer at your company needs to run ad hoc queries against an Azure SQL Database from a workstation on the corporate network. Security policy forbids storing credentials in the application and forbids any inbound public network access to the database. The workstation already has a Microsoft Entra ID-joined identity. What should you configure to meet these requirements?

A.Enable the Allow Azure services and resources to access this server firewall rule and connect over the public endpoint

B.Create a SQL login with a strong password and store the password in the connection string on the workstation

C.Create a contained database user mapped to the Microsoft Entra identity and connect through a private endpoint using Microsoft Entra authentication

D.Issue the developer a shared SQL login and restrict it with an IP-based firewall rule for the corporate egress address

AnswerC

A contained database user mapped to a Microsoft Entra identity lets the developer authenticate with their existing directory credentials, so no password is stored anywhere. Pairing that with a private endpoint keeps all traffic on the private network and removes public exposure. This combination satisfies both the no-stored-credential and no-public-access constraints without extra secrets.

Why this answer

Meeting both constraints requires an authentication method that needs no stored secret and a network path that avoids the public endpoint. A contained database user mapped to a Microsoft Entra identity lets the developer sign in with directory credentials, and a private endpoint routes traffic over the private network. Password-based logins and firewall rules that keep the public endpoint reachable fail the policy.

Exam trap

The trap here is treating a firewall rule as equivalent to removing public access, when an IP rule still leaves the public endpoint reachable.

Full explanation →

159

MCQeasy

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

A.Azure SQL Advanced Threat Protection

B.Azure SQL Database Vulnerability Assessment

C.Azure SQL Auditing

D.Dynamic Data Masking

AnswerC

Azure SQL Auditing writes login events, including failed attempts, to a storage account, Log Analytics workspace, or Event Hub. This captures every failed authentication against the database, satisfying the requirement to audit all failed login attempts.

Why this answer

Azure SQL Auditing is the correct feature because it tracks database events, including failed login attempts, and writes them to an audit log in an Azure Storage account, Log Analytics workspace, or Event Hubs. This allows you to review and analyze authentication failures for security and compliance purposes. The audit logs capture the exact timestamp, source IP address, and the specific error message for each failed login, such as 'Login failed for user'.

Exam trap

The trap here is that candidates often confuse Azure SQL Auditing with Advanced Threat Protection, assuming that threat detection automatically logs all failed logins, but in reality, ATP only alerts on suspicious patterns and does not provide a comprehensive audit trail of every failed login attempt.

How to eliminate wrong answers

Option A is wrong because Azure SQL Advanced Threat Protection is a security intelligence service that detects anomalous activities like SQL injection or brute-force attacks, but it does not provide a configurable audit trail of all failed login attempts; it only alerts on suspicious patterns. Option B is wrong because Azure SQL Database Vulnerability Assessment is a scanning and reporting service that identifies potential database vulnerabilities and misconfigurations, but it does not log or audit individual login events. Option D is wrong because Dynamic Data Masking is a data protection feature that obfuscates sensitive data in query results for unauthorized users, and it has no capability to log or audit authentication failures.

Full explanation →

160

MCQeasy

You manage an Azure SQL Database in the East US region. The database uses the General Purpose service tier with geo-redundant backup storage. You need to ensure that in the event of a regional outage, you can restore the database to the West US region with the least possible downtime. What should you use?

A.Configure active geo-replication to a secondary server in West US.

B.Use the geo-restore feature to restore from the geo-redundant backups to a new database in West US.

C.Create a copy-only backup and manually transfer it to West US.

D.Perform a point-in-time restore to a new database in West US.

AnswerA

Active geo-replication must be configured proactively.

Why this answer

Active geo-replication to a secondary server in West US is the correct choice because it maintains a continuously synchronized secondary database in the target region, enabling a fast failover with minimal downtime if the primary region fails. Geo-restore can restore to West US from geo-redundant backups, but it has a much longer recovery time than failing over to an already synchronized secondary. Creating a copy-only backup and manually transferring it is slow and not automated.

Point-in-time restore is limited to the same region and cannot restore directly to West US.

Full explanation →

161

MCQhard

You are deploying an Azure SQL Database and need to ensure that the database files are encrypted at rest using a key that your organization manages and can revoke. The key must be stored in Azure Key Vault and rotated regularly. You also need to ensure that the database remains accessible during key rotation. What should you do?

A.Enable Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault and configure the key version to be auto-rotated.

B.Enable Transparent Data Encryption (TDE) with a service-managed key and configure automatic key rotation.

C.Enable Always Encrypted with a column master key stored in Azure Key Vault and configure key rotation.

D.Enable Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault and manually update the key version in the database configuration after each rotation.

AnswerA

TDE with a customer-managed key in Azure Key Vault allows your organization to manage and revoke the key. Configuring auto-rotation ensures the key is rotated regularly without downtime, as Azure SQL Database automatically uses the new key version. This meets all requirements: encryption at rest, organizational control, revocability, and continuous availability.

Why this answer

Transparent Data Encryption with a customer-managed key in Azure Key Vault allows the organization to own, manage, and revoke the encryption key. Configuring auto-rotation ensures that the key is rotated regularly without manual intervention, and the database remains accessible because Azure SQL Database automatically switches to the new key version. This satisfies all the stated requirements.

Exam trap

The trap here is confusing TDE with Always Encrypted or assuming that manual key updates are required; auto-rotation is designed to handle key version changes seamlessly.

Full explanation →

162

Multi-Selectmedium

You are designing a disaster recovery strategy for a group of Azure SQL Databases hosted on a logical server in the East US region. The solution must provide automatic failover to a secondary region, support read-only access to the secondary during normal operations, and minimize application connection string changes during failover. You plan to use an auto-failover group. Which two actions should you perform? (Choose two.)

Select 2 answers

A.Configure each database with active geo-replication to the secondary server.

B.Set the failover policy to manual to avoid unexpected failovers.

C.Create a secondary logical server in a different Azure region and add the databases to the failover group.

D.Configure the failover group to use a read-write listener endpoint and a read-only listener endpoint.

E.Enable zone redundancy on the primary databases.

AnswersC, D

An auto-failover group requires a secondary server in a different region. You must create the secondary server and add the databases to the failover group. This enables replication and automatic failover. The secondary server must be in a paired region for optimal performance, but any region works.

Why this answer

To implement an auto-failover group, you must create a secondary server in another region and add the databases to the failover group. You should also configure the read-write and read-only listener endpoints to provide stable connection strings and read-only access to the secondary. Zone redundancy is for intra-region HA, active geo-replication is redundant, and manual failover policy contradicts the automatic failover requirement.

Exam trap

The trap here is thinking that active geo-replication is needed in addition to an auto-failover group; the failover group already handles replication.

Full explanation →

163

MCQmedium

Your company is using Azure SQL Database with Microsoft Entra ID authentication. A developer needs to connect to the database using a service principal. What should you provide to the developer?

A.The connection string with 'Authentication=Active Directory Service Principal' and the service principal's object ID.

B.The service principal's client ID and client secret, and the connection string with 'Authentication=Active Directory Service Principal'.

C.The service principal's username and password.

D.The service principal's managed identity endpoint.

AnswerB

Service principal authentication requires the client ID and client secret as credentials, paired with the Active Directory Service Principal authentication keyword in the connection string, enabling the developer to authenticate non-interactively against Microsoft Entra ID.

Why this answer

To connect to Azure SQL Database using a service principal with Microsoft Entra ID authentication, the developer needs the service principal's client ID and client secret (or certificate) for authentication, and the connection string must include 'Authentication=Active Directory Service Principal' to specify the authentication method. This combination allows the application to obtain an access token from Microsoft Entra ID via the OAuth 2.0 client credentials grant flow, which is then used to authenticate to the database.

Exam trap

The trap here is that candidates often confuse the service principal's object ID (directory object identifier) with the client ID (application identifier), or mistakenly think a service principal uses a username/password like a regular user, when in fact it relies on OAuth 2.0 client credentials with a client ID and secret.

How to eliminate wrong answers

Option A is wrong because the connection string requires 'Authentication=Active Directory Service Principal', but the service principal's object ID is not used in the connection string; instead, the client ID (application ID) is used as the User ID. Option C is wrong because service principals do not have traditional username/password credentials; they authenticate using a client ID and client secret (or certificate) via OAuth 2.0, not a username and password. Option D is wrong because the managed identity endpoint is used for Azure resources with a managed identity (e.g., VM, App Service), not for a service principal; a service principal is a separate application identity that requires explicit client credentials.

Full explanation →

164

Multi-Selectmedium

You are configuring automated backups for an Azure SQL Database. Which TWO settings can you configure?

Select 2 answers

A.Backup compression.

B.Backup frequency (full, differential, log).

C.Point-in-time restore interval.

D.Backup retention period (in days).

E.Geo-redundant storage (GRS) for backups.

AnswersD, E

Configurable from 7 to 35 days.

Why this answer

The backup retention period (in days) is a configurable setting for Azure SQL Database automated backups. You can set the retention period for point-in-time restore (PITR) backups between 1 and 35 days, and for long-term retention (LTR) backups up to 10 years. This directly controls how far back you can restore your database.

Exam trap

The trap here is that candidates confuse the configurable retention period with the non-configurable backup frequency or point-in-time restore interval, assuming they can adjust the schedule of full/differential/log backups or directly set the restore window, when in fact Azure SQL Database manages these automatically based on the retention policy.

Full explanation →

165

MCQmedium

A healthcare company is required to encrypt all patient data at rest and in transit. They are deploying Azure SQL Database. Which combination of features should they implement to meet this requirement?

A.Transparent data encryption (TDE) and TLS 1.2

B.Dynamic data masking and row-level security

C.Azure Active Directory authentication and firewall rules

D.Always Encrypted and transparent data encryption (TDE)

AnswerA

Transparent data encryption performs real-time encryption and decryption of the database, backups, and transaction logs at rest without application changes, while TLS 1.2 secures data in transit between clients and the server. Together they satisfy the healthcare company's dual requirement for Azure SQL Database.

Why this answer

Transparent Data Encryption (TDE) encrypts data at rest by performing real-time I/O encryption and decryption of the database, data files, and transaction logs, while TLS 1.2 encrypts data in transit between the client and Azure SQL Database. Together, they satisfy the requirement to encrypt all patient data both at rest and in transit.

Exam trap

The trap here is that candidates often confuse Always Encrypted with a complete encryption solution for both at rest and in transit, but it only encrypts specific columns and does not encrypt the entire transport channel, leaving the connection vulnerable without TLS.

How to eliminate wrong answers

Option B is wrong because Dynamic Data Masking (DDM) and Row-Level Security (RLS) control data visibility and access at the row level, but they do not encrypt data at rest or in transit. Option C is wrong because Azure Active Directory authentication and firewall rules manage identity and network access, but they provide no encryption of data at rest or in transit. Option D is wrong because Always Encrypted protects sensitive data in transit and at rest with client-side encryption, but when combined with TDE, it does not address the in-transit requirement for the entire connection (TLS is needed for the transport layer); moreover, Always Encrypted is not a replacement for TLS and is often overkill for the stated requirement, which is fully met by TDE + TLS 1.2.

Full explanation →

166

MCQmedium

You are configuring Azure SQL Database for a new e-commerce application that must support high read throughput for product catalog queries. The application uses Entity Framework Core and requires that read-only queries be offloaded to a secondary replica to reduce load on the primary. Which feature should you enable?

A.Enable read scale-out and configure the application to use read-only intent.

B.Enable Query Performance Insights and create indexes for frequent queries.

C.Configure Active Geo-Replication with a readable secondary in a different region.

D.Enable automatic tuning to force parameterization of queries.

AnswerA

Read scale-out provisions a read-only secondary replica, and routing connections with ApplicationIntent=ReadOnly directs Entity Framework Core catalog queries there, offloading the primary. This satisfies the high read throughput requirement without manual replication, since Microsoft Entra ID authentication and the existing connection string remain unchanged.

Why this answer

Read scale-out in Azure SQL Database allows you to offload read-only workloads to a readable secondary replica by setting the application connection string's `ApplicationIntent=ReadOnly`. This reduces load on the primary replica, which is essential for high read throughput in an e-commerce catalog scenario. Entity Framework Core can use this by specifying `ReadOnly` in the connection string or via a custom interceptor.

Exam trap

The trap here is that candidates often confuse read scale-out with Active Geo-Replication, assuming that any readable secondary must be in a different region, but read scale-out works within the same region and is specifically designed for read-only workload offloading.

How to eliminate wrong answers

Option B is wrong because Query Performance Insights is a diagnostic tool for identifying query performance issues, not a feature to offload read traffic to a secondary replica. Option C is wrong because Active Geo-Replication with a readable secondary is designed for disaster recovery and regional read scaling, not for offloading read-only queries within the same region to reduce primary load; it also introduces replication lag and cross-region latency. Option D is wrong because automatic tuning with forced parameterization optimizes query plans by parameterizing non-parameterized queries, but it does not redirect read traffic to a secondary replica.

Full explanation →

167

Multi-Selectmedium

You are optimizing an Azure SQL Database that uses the Hyperscale service tier. You need to reduce the time it takes to perform a database restore. Which TWO factors directly affect the restore time? (Choose two.)

Select 2 answers

A.The backup storage redundancy option.

B.The service level objective (SLO) of the database.

C.The number of transaction log records that need to be replayed.

D.The number of page servers that need to be attached.

E.The size of the database's data files.

AnswersC, D

In Hyperscale, restore operations involve attaching the database to the existing page servers and then replaying the transaction log to bring the database to a consistent point. The amount of log that must be replayed directly impacts the restore duration. A larger log tail results in longer restore times.

Why this answer

In Hyperscale, restore performance is primarily determined by the amount of transaction log that must be replayed and the number of page servers that need to be attached. Data file size and backup redundancy do not directly affect restore time, and the SLO influences resources but not the fundamental restore process.

Exam trap

The trap here is assuming that database size dictates restore time; in Hyperscale, the distributed architecture makes log replay and page server count the key factors.

Full explanation →

168

MCQmedium

You have an Azure SQL Managed Instance configured with a failover group to a secondary region. During a regional outage, the failover group automatically fails over to the secondary. After the primary region is restored, you need to bring the primary back online and re-establish the failover relationship. What should you do?

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

B.Add the original primary as a new secondary to the current primary.

C.Initiate a planned failover from the current primary to the original primary.

D.Remove the original primary from the failover group and re-add it.

AnswerC

A planned failover reverses roles without data loss, making the restored original primary the secondary again and re-establishing replication. Forced failover would risk divergence, so the planned operation is required to rebuild the relationship safely.

Why this answer

After an automatic failover, the original primary becomes the secondary. To restore the original configuration, you perform a planned failover from the current primary (the former secondary) back to the original primary. This re-establishes the original roles and the failover group remains intact.

A planned failover ensures no data loss by synchronizing the databases before switching roles.

Exam trap

DP-300 often tests the misconception that after an automatic failover, you must recreate or reconfigure the failover group to restore the original primary, when in fact a planned failover is the correct and seamless method.

How to eliminate wrong answers

Option A is wrong because deleting and recreating the failover group is unnecessary and disruptive; it would require reconfiguring the entire failover group and could lead to downtime. Option B is wrong because the original primary is already part of the failover group as the secondary; adding it as a new secondary is not possible and would not restore the original roles. Option D is wrong because removing and re-adding the original primary would not change its role; it would still be the secondary, and the failover group would remain in the failed-over state.

Full explanation →

169

Multi-Selectmedium

Which TWO of the following are best practices for securing Azure SQL Database?

Select 2 answers

A.Enable Auditing to block malicious queries.

B.Enable TDE to prevent SQL injection attacks.

C.Use SQL authentication with complex passwords.

D.Enable firewall rules to restrict access to specific IP addresses.

E.Use Azure Active Directory authentication instead of SQL authentication.

AnswersD, E

Restricting access to specific IP addresses via firewall rules satisfies the requirement to limit network exposure of Azure SQL Database. Server-level and database-level firewall rules block connections originating outside approved ranges, ensuring only known clients reach the logical server, which directly reduces the attack surface from arbitrary internet traffic.

Why this answer

Option D is correct because Azure SQL Database firewall rules (server-level and database-level) restrict inbound connections to approved IP address ranges, reducing the exposed attack surface by denying traffic from unknown sources. Option E is correct because Azure Active Directory (now Microsoft Entra ID) authentication centralizes identity management, supports MFA and conditional access, and eliminates the risk of weak or shared SQL logins and passwords. Option A is incorrect because Auditing records and logs activity for compliance and forensics; it does not block or prevent malicious queries.

Option B is incorrect because Transparent Data Encryption encrypts data at rest (and backups) and does nothing to stop SQL injection, which is an application-layer input validation issue. Option C is incorrect because SQL authentication with complex passwords is a weaker, legacy approach compared to Entra ID authentication, and complex passwords alone do not constitute a best practice for securing Azure SQL Database.

Exam trap

The trap here is that candidates often confuse Auditing (logging) with blocking, or TDE (encryption at rest) with SQL injection prevention, leading them to select options that sound security-related but do not perform the stated function.

Full explanation →

170

MCQhard

Your Azure SQL Managed Instance is experiencing performance degradation. You suspect a query plan regression caused by parameter-sensitive plan issues. Which feature should you use to identify and resolve the issue?

A.Intelligent Insights

B.Automatic tuning

C.Query Store with Query Store Hints

D.Database Advisor

AnswerC

Query Store captures execution plans and runtime statistics, exposing regressed plans for a specific query. Query Store Hints then force a known-good plan without code changes, directly resolving parameter-sensitive plan regression on the Managed Instance.

Why this answer

Query Store with Query Store Hints is the correct feature because it allows you to identify parameter-sensitive plan regression by analyzing historical execution plans stored in Query Store, and then force a specific plan using a hint without changing application code. This directly addresses the root cause of parameter-sensitive plan issues, where different parameter values lead to suboptimal cached plans.

Exam trap

The trap here is that candidates often confuse Intelligent Insights or Automatic tuning as the solution for plan regression, but these features do not provide the ability to manually force a specific plan, which is required for parameter-sensitive plan issues where you need to pin a known good plan.

How to eliminate wrong answers

Option A is wrong because Intelligent Insights is a diagnostic feature that provides proactive health monitoring and root-cause analysis for Azure SQL databases, but it does not allow you to force or hint a specific query plan to resolve parameter-sensitive plan regression. Option B is wrong because Automatic tuning can automatically adjust query plans based on performance, but it does not provide the granular, manual control needed to identify and force a specific plan for parameter-sensitive issues; it may also change plans automatically without your explicit approval. Option D is wrong because Database Advisor provides recommendations for index creation, query performance, and schema changes, but it does not offer the ability to view historical execution plans or apply query hints to fix plan regression.

Full explanation →

171

Matchingmedium

Match each Azure SQL Database service tier to its description.

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

Concepts

Matches

Suitable for small databases with low performance requirements

Balanced performance for most production workloads

High performance and low latency for mission-critical workloads

Highly scalable storage and compute for large databases

Auto-scaling compute based on workload demand

Why these pairings

These are the main service tiers in Azure SQL Database, each designed for different performance and scalability needs.

Full explanation →

172

MCQmedium

You are deploying a new Azure SQL Database for a line-of-business application. The application's workload is unpredictable, with periods of near-zero activity overnight and heavy transactional bursts during business hours. You need to choose a purchasing model and service tier that minimizes cost while automatically scaling compute resources based on demand. What should you do?

A.Deploy the database using the vCore purchasing model with the Serverless compute tier and set the minimum and maximum vCores.

B.Deploy the database using the DTU purchasing model with the Standard service tier.

C.Deploy the database using the vCore purchasing model with the General Purpose service tier and configure auto-scaling.

D.Deploy the database using the vCore purchasing model with the Business Critical service tier and enable geo-replication.

AnswerA

The Serverless compute tier automatically scales compute based on workload demand and can pause during inactive periods, billing only for storage while paused. By setting minimum and maximum vCores, you control cost boundaries while allowing automatic scaling during bursts. This directly meets the requirement to minimize cost while handling unpredictable activity, making it the appropriate choice for this scenario.

Why this answer

The Serverless compute tier in the vCore purchasing model is designed for databases with intermittent, unpredictable usage. It automatically scales compute up during active periods and down or pauses during inactivity, billing only for resources consumed. Setting minimum and maximum vCores provides cost control while enabling automatic scaling.

This aligns with the need to minimize cost while handling variable demand, unlike fixed DTU or provisioned vCore tiers.

Exam trap

The trap here is assuming that any vCore-based tier supports automatic scaling, when in fact only the Serverless compute tier provides that capability.

Full explanation →

173

Multi-Selectmedium

Which THREE of the following are required to configure Microsoft Entra authentication for an Azure SQL Managed Instance?

Select 3 answers

A.Enable the managed instance system-assigned managed identity.

B.Set a Microsoft Entra admin for the managed instance.

C.Ensure the managed instance is deployed in a different virtual network from the clients.

D.Grant the managed instance identity the 'Directory Readers' role in Microsoft Entra ID.

E.Configure the managed instance to allow public network access.

AnswersA, B, D

The managed identity is used to authenticate to Entra ID.

Why this answer

Enabling the system-assigned managed identity for Azure SQL Managed Instance is required because Microsoft Entra authentication relies on this identity to authenticate the instance itself against Microsoft Entra ID. Without a managed identity, the instance cannot securely obtain tokens or perform directory lookups needed for authentication and authorization.

Exam trap

The trap here is that candidates often confuse the 'Directory Readers' role assignment (which is required) with optional network settings like VNet isolation or public access, leading them to incorrectly select C or E as necessary steps.

Full explanation →

174

MCQmedium

You are a database administrator for a logistics company that uses Azure SQL Database. The company requires that a stored procedure, which archives old shipment records, runs every night at 2:00 AM UTC. You need to configure a solution that minimizes administrative overhead and uses built-in Azure SQL Database capabilities. What should you do?

A.Use Azure Automation with a runbook that connects to the database and executes the stored procedure.

B.Configure a SQL Server Agent job on the logical server that hosts the database.

C.Create a SQL Server Integration Services (SSIS) package and schedule it with Azure Data Factory.

D.Create an elastic job agent and define a recurring job with a T-SQL step that executes the stored procedure.

AnswerD

Elastic jobs are a native Azure SQL Database feature designed to automate T-SQL execution across one or many databases on a schedule. By creating an elastic job agent and a job with a T-SQL step, you can run the stored procedure nightly without external orchestration. This minimizes administrative overhead because the scheduling and execution are fully managed by the service, and it directly meets the requirement.

Why this answer

Elastic jobs are the native scheduling mechanism in Azure SQL Database for running T-SQL on a recurring basis. They eliminate the need for external services or additional infrastructure, directly satisfying the requirement to minimize administrative overhead. The other options either rely on features not available in Azure SQL Database or introduce unnecessary complexity.

Exam trap

The trap here is assuming that SQL Server Agent is available in Azure SQL Database because it is a familiar scheduling tool in SQL Server.

Full explanation →

175

MCQmedium

You have an Azure SQL Database named DB1 that uses the Hyperscale service tier. The database has a primary replica and multiple named replicas. You need to ensure that read-only workloads are offloaded to a named replica and that the named replica remains available if the primary replica fails. What should you do?

A.Create a geo-secondary in another region and redirect read-only connections to it.

B.Configure the application to connect to the named replica using its connection string and set ApplicationIntent=ReadOnly.

C.Enable read-scale out on the primary database and use the read-only listener.

D.Use the primary replica's connection string with ApplicationIntent=ReadOnly to route to a named replica.

AnswerB

Named replicas in Hyperscale are separate read-only replicas that can be connected to directly using their own connection string. Setting ApplicationIntent=ReadOnly ensures the connection is routed to a read-only replica if using a listener. This offloads read workloads and the named replica remains available even if the primary fails, as it is an independent replica.

Why this answer

Named replicas in Azure SQL Database Hyperscale are independent read-only replicas that must be accessed via their own connection string. They continue to serve read workloads even if the primary fails, providing high availability for read operations. Using the primary connection string with ApplicationIntent=ReadOnly does not target named replicas; it targets the built-in read-only replicas.

Geo-secondaries are for disaster recovery and not the same as named replicas.

Exam trap

The trap here is assuming that ApplicationIntent=ReadOnly on the primary connection string will automatically route to a named replica, when in fact named replicas require a dedicated connection string.

Full explanation →

176

MCQmedium

You are troubleshooting a connectivity issue: an application running on an Azure virtual machine (VM) cannot connect to an Azure SQL Database. The VM is in the same region as the SQL Database. The VM can ping other resources, but the SQL connection fails. The SQL Database has a firewall rule allowing the VM's private IP address. What is the most likely cause?

A.The SQL Database has the public endpoint disabled

B.The firewall rule uses the VM's private IP address, but Azure SQL Database sees the VM's public IP address

C.The SQL Database firewall is configured at the database level, not the server level

D.The VM does not have an outbound security rule allowing traffic to Azure SQL Database

AnswerB

Azure SQL Database's firewall evaluates the source IP as seen by the gateway, which is the VM's public IP after SNAT, not its private address. The allowlisted private IP therefore never matches, so the connection is refused despite the rule existing.

Why this answer

When an Azure VM connects to Azure SQL Database, the source IP address seen by the SQL firewall is the VM's public outbound IP address due to Source Network Address Translation (SNAT) performed by Azure. Even if the VM is in the same region, traffic to Azure SQL Database egresses through the VM's public IP, not its private IP. Therefore, a firewall rule allowing the private IP will not match, causing the connection to fail.

Exam trap

The trap here is that candidates assume Azure SQL Database sees the VM's private IP because they are in the same region, overlooking Azure's mandatory SNAT for public endpoint connections.

How to eliminate wrong answers

Option A is wrong because disabling the public endpoint would prevent all external connections, but the VM could still connect via a private endpoint or service endpoint if configured; the question states the VM cannot connect, but the issue is specifically about the firewall rule. Option C is wrong because firewall rules at the database level inherit server-level rules, and a database-level rule allowing the private IP would still fail for the same SNAT reason; the level does not change the source IP seen. Option D is wrong because outbound security rules in Azure NSGs control traffic flow but do not alter the source IP seen by the SQL firewall; the VM can ping other resources, indicating outbound connectivity is functional.

Full explanation →

177

MCQhard

Your Azure SQL Database is configured with Active Geo-Replication to a secondary region for disaster recovery. During a routine failover drill, you notice that after failover, the application cannot connect to the new primary because the login credentials fail. The logins are contained in the master database. What is the most likely cause?

A.The DNS name of the secondary server changed after failover.

B.The SQL logins in the master database are not replicated to the secondary server.

C.The firewall rules on the secondary server do not allow connections from the application IP.

D.The application uses contained database users, which are not replicated.

AnswerB

Active Geo-Replication replicates user databases only; the logical server's master database, which holds server-level SQL logins, is not synchronised to the secondary server. After failover, those logins are absent, so authentication fails. Contained database users, stored inside the user database, would replicate and continue working.

Why this answer

When Active Geo-Replication is configured for Azure SQL Database, the secondary server is a separate logical server in a different region. The master database, which contains server-level logins, is not replicated as part of geo-replication; only the user databases are replicated. Therefore, after a failover, the new primary server does not have the server-level logins from the original primary, causing authentication failures for applications using those logins.

Exam trap

The trap here is that candidates often assume all server-level configurations, including logins, are automatically replicated with geo-replication, but in reality, only user databases are replicated, not the master database.

How to eliminate wrong answers

Option A is wrong because the DNS name of the secondary server does not change after failover; the failover process updates the geo-replication listener or the application connection string must point to the secondary server's DNS name, but the DNS name itself remains static. Option C is wrong because firewall rules are replicated as part of the server-level configuration when using geo-replication, and the question specifically states the issue is login credentials failing, not network connectivity. Option D is wrong because contained database users are stored within the user database itself and are automatically replicated with geo-replication, so they would not cause a login failure after failover.

Full explanation →

178

MCQhard

You are a database administrator for a large financial services company. You manage an Azure SQL Database in the Business Critical tier with a failover group configured for disaster recovery. The database has a heavy OLTP workload. You notice that the secondary replica is experiencing high log write latency, impacting the primary's performance due to synchronous commit. You need to minimize the performance impact on the primary while maintaining disaster recovery capabilities. What should you do?

A.Change the backup storage redundancy of the secondary replica to locally-redundant storage (LRS).

B.Add an additional secondary replica to distribute the log write load.

C.Change the failover group to use asynchronous commit mode.

D.Decrease the service tier of the secondary replica to General Purpose.

AnswerC

Changing to asynchronous commit mode allows the primary to commit without waiting for the secondary to harden the log. This directly reduces performance impact on the primary while still maintaining disaster recovery capabilities, albeit with possible data loss.

Why this answer

Changing the failover group to asynchronous commit mode decouples the primary's transaction commit from the secondary's log write. In synchronous mode, the primary waits for the secondary to confirm log hardening, which causes performance impact when the secondary has high log write latency. Asynchronous commit allows the primary to commit without waiting, thus minimizing performance impact while still maintaining disaster recovery capabilities (the secondary will apply changes eventually, though with potential data loss if a failover occurs before sync).

Option A is incorrect because backup storage redundancy (LRS vs GRS) only affects backup storage, not live log write latency for replication. Option B is incorrect because adding another secondary does not reduce latency on the existing secondary; it could even increase overhead. Option D is incorrect because decreasing the secondary's service tier to General Purpose would reduce its I/O capacity and likely worsen log write latency.

Full explanation →

179

Multi-Selecthard

You are a database administrator for a manufacturing company that uses Azure SQL Database. The company has a requirement to encrypt sensitive data in transit between the application and the database. Additionally, the company wants to ensure that database administrators (DBAs) cannot view the sensitive data. Which TWO features should you implement?

Select 2 answers

A.Implement row-level security (RLS) to filter rows

B.Enable transparent data encryption (TDE) on the database

C.Configure the server to enforce TLS 1.2 by setting the 'Minimal TLS Version' property

D.Implement dynamic data masking on sensitive columns

E.Use Always Encrypted with a column master key stored in Azure Key Vault

AnswersC, E

Enforcing TLS 1.2 via the Minimal TLS Version property encrypts data in transit between application and server, satisfying the in-transit encryption requirement. It does not hide data from DBAs, so it must pair with Always Encrypted to meet the second constraint.

Why this answer

Option C is correct because setting the server's 'Minimal TLS Version' property to 1.2 forces all client connections to Azure SQL Database to negotiate TLS 1.2, which encrypts data in transit between the application and the database. Option E is correct because Always Encrypted encrypts sensitive column data on the client side using a column master key stored in Azure Key Vault, so the data is never decrypted on the SQL server and DBAs cannot view the plaintext. Option A is incorrect because row-level security only filters which rows a user can access; it does not encrypt data in transit or hide column values from DBAs.

Option B is incorrect because TDE encrypts data at rest (database, log, and backup files) and does not protect data in transit, nor does it prevent DBAs from viewing data since they can still query decrypted values. Option D is incorrect because dynamic data masking only obscures data in query results for non-privileged users and does not encrypt data in transit or prevent DBAs, who can be granted UNMASK permission, from viewing the actual data.

Exam trap

The trap here is that candidates often confuse encryption at rest (TDE) or access control (RLS, masking) with encryption in transit and client-side encryption, leading them to select TDE or dynamic data masking instead of the correct combination of TLS enforcement and Always Encrypted.

Full explanation →

180

MCQmedium

A production Azure SQL Database is experiencing high CPU usage during peak hours. The database uses the S3 service tier. You need to reduce CPU usage without changing the service tier. Which action should you take?

A.Increase the maximum number of concurrent workers.

B.Identify and create missing indexes.

C.Reduce MAXDOP to 1.

D.Increase MAXDOP to 8.

AnswerB

Missing indexes force full table or index scans, so creating them lets the query optimiser seek directly to matching rows, cutting CPU consumed per query. This satisfies the constraint of reducing CPU usage while remaining on the S3 service tier.

Why this answer

High CPU usage in an S3 Azure SQL Database often stems from inefficient query plans caused by missing indexes. Creating appropriate indexes reduces the number of rows scanned and the CPU cycles needed for operations like key lookups and sorting, directly lowering CPU consumption without changing the service tier.

Exam trap

The trap here is that candidates often assume reducing MAXDOP or increasing workers will fix CPU issues, but without addressing the root cause (poor query plans from missing indexes), these changes either exacerbate resource contention or fail to reduce CPU usage.

How to eliminate wrong answers

Option A is wrong because increasing the maximum number of concurrent workers (MAX_WORKERS) would allow more parallel queries to run, likely increasing CPU contention and worsening the problem. Option C is wrong because reducing MAXDOP to 1 forces all queries to run serially, which can increase CPU time per query due to lack of parallelism and may degrade performance for complex queries. Option D is wrong because increasing MAXDOP to 8 on an S3 tier (which has limited resources) can lead to excessive parallelism, causing CPU thrashing and inefficient resource utilization.

Full explanation →

181

MCQhard

You are configuring an Azure SQL Database to support a mission-critical application. The database is in the Business Critical service tier and uses zone redundancy. You need to ensure that the database remains available even if an entire Azure region becomes unavailable. The solution must minimize data loss and provide automatic failover. What should you do?

A.Configure an auto-failover group with a secondary database in a paired Azure region.

B.Enable active geo-replication for the database to a secondary region.

C.Enable geo-backup and configure a long-term retention policy.

D.Configure a failover group with a secondary database in the same region but in a different availability zone.

AnswerA

Auto-failover groups provide automatic failover to a secondary region and allow you to group multiple databases for failover. They support read-write and read-only listener endpoints, and you can set a grace period. This meets the requirement for automatic failover and minimal data loss, as replication is asynchronous but typically low latency. It is the recommended solution for regional disaster recovery.

Why this answer

Auto-failover groups provide automatic failover to a secondary region, minimizing downtime and data loss. They support multiple databases and provide listener endpoints for seamless connection redirection. This is the appropriate solution for regional disaster recovery with automatic failover.

Exam trap

The trap here is confusing active geo-replication, which requires manual failover, with auto-failover groups, which automate the failover process.

Full explanation →

182

MCQmedium

You are analyzing the SQL script in the exhibit. This script is used to query data stored in Azure Blob Storage from Azure SQL Database. What is the primary purpose of the database scoped credential?

A.To store the shared access signature token for accessing the blob storage.

B.To define the file format of the external data.

C.To specify the schema of the external table.

D.To provide the location of the external data source.

AnswerA

The database scoped credential holds the shared access signature token that authenticates SQL Database against the blob endpoint. Without it, the external data source cannot authorise reads, so it satisfies the requirement for querying blob-stored data securely.

Why this answer

The database scoped credential in this context securely stores the Shared Access Signature (SAS) token required to authenticate and authorize access to Azure Blob Storage. When creating an external data source with the CREDENTIAL option, SQL Database uses this credential to pass the SAS token to the storage endpoint, enabling read operations for PolyBase or external table queries.

Exam trap

The trap here is that candidates confuse the credential's role with the external data source's LOCATION or the external table's schema, but the credential is strictly an authentication mechanism, not a metadata or location definition.

How to eliminate wrong answers

Option B is wrong because defining the file format of external data is the role of an external file format object (CREATE EXTERNAL FILE FORMAT), not a credential. Option C is wrong because specifying the schema of the external table is done via the CREATE EXTERNAL TABLE statement with column definitions, not a credential. Option D is wrong because providing the location of the external data source is the purpose of the CREATE EXTERNAL DATA SOURCE statement (which includes the LOCATION parameter), while the credential only supplies authentication secrets.

Full explanation →

183

MCQmedium

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database uses a SQL Server Agent job that runs a PowerShell script to process files. You need to ensure the job continues to run after migration with minimal changes. What should you do?

A.Migrate the database to Azure SQL Managed Instance and recreate the job using SQL Server Agent.

B.Migrate the database to Azure SQL Managed Instance and configure an Azure Automation runbook to run the script.

C.Migrate the database to Azure SQL Managed Instance and use Elastic Jobs to run the PowerShell script.

D.Migrate the database to Azure SQL Database and use Elastic Jobs to run the PowerShell script.

AnswerA

Azure SQL Managed Instance includes SQL Server Agent, so recreating the job there preserves the PowerShell step with minimal changes. Migrating to single database would require replacing the agent with Elastic Jobs or Azure Automation.

Why this answer

Azure SQL Managed Instance supports SQL Server Agent, including jobs that run PowerShell scripts via CmdExec or PowerShell job steps. By migrating the database and recreating the job with the same script, you preserve the existing automation with minimal changes. This is the only option that keeps the job execution environment identical to the on-premises setup.

Exam trap

The trap here is that candidates assume Azure SQL Database is a suitable target because it is the most common PaaS offering, but they overlook that SQL Managed Instance is required to retain SQL Server Agent and PowerShell job step support, which Azure SQL Database lacks entirely.

How to eliminate wrong answers

Option B is wrong because Azure Automation runbooks require additional configuration, credential management, and do not integrate directly with SQL Managed Instance's native job scheduling; this introduces unnecessary complexity and deviates from the minimal-change requirement. Option C is wrong because Elastic Jobs are designed for Azure SQL Database and SQL Managed Instance, but they execute T-SQL scripts, not PowerShell scripts, and would require rewriting the job logic. Option D is wrong because Azure SQL Database does not support SQL Server Agent or PowerShell job steps; migrating to Azure SQL Database would lose this functionality entirely, and Elastic Jobs still cannot run PowerShell scripts.

Full explanation →

184

Multi-Selecteasy

Which TWO are valid ways to secure access to an Azure SQL Database?

Select 2 answers

A.Use IPsec VPN from on-premises

B.Configure a Virtual Network service endpoint

C.Configure a Private Endpoint

D.Join the database to an on-premises Active Directory domain

E.Disable all firewall rules and rely only on authentication

AnswersB, C

A Virtual Network service endpoint extends the Azure virtual network's identity to Azure SQL Database, letting you restrict the server's firewall to specific subnets. This satisfies the stem's requirement for a valid access-securing method, since it removes public internet exposure for traffic originating inside that subnet.

Why this answer

Option B is correct because a Virtual Network service endpoint extends the Azure VNet identity to Azure SQL Database, allowing you to restrict the logical SQL server's firewall so that only subnets in that VNet can reach the database over the Azure backbone, removing public internet exposure. Option C is correct because a Private Endpoint creates a private IP address for the Azure SQL Database inside your VNet via Azure Private Link, so traffic stays on the Microsoft private network and the public endpoint can be disabled entirely. Option A is not valid because IPsec VPN secures connectivity to a VNet or on-premises network but does not by itself control or secure access to Azure SQL Database's firewall/endpoint.

Option D is not valid because Azure SQL Database cannot be domain-joined to an on-premises Active Directory; it supports Azure AD authentication (and AD DS via Azure AD DS), not a direct domain join. Option E is not valid because disabling all firewall rules would expose the database publicly and relying only on authentication does not secure network access, which is the point of the question.

Exam trap

The trap here is that candidates often confuse 'securing access' with 'authentication methods' and incorrectly assume that disabling firewall rules and relying solely on authentication (Option E) is valid, or they think an on-premises AD domain join (Option D) applies to Azure SQL Database, when in fact Azure SQL only supports Azure AD authentication and network-level controls like service endpoints or private endpoints are required for secure access.

Full explanation →

185

MCQmedium

You are the database administrator for a company that uses Azure SQL Database. You need to implement a security solution that automatically detects and alerts on suspicious activities, such as SQL injection attempts. Which feature should you enable?

A.Azure SQL Auditing

B.SQL Vulnerability Assessment

C.Microsoft Defender for SQL

D.Transparent Data Encryption (TDE)

AnswerC

Microsoft Defender for SQL continuously monitors Azure SQL Database activity, detecting anomalous behaviour such as SQL injection attempts and generating alerts. It provides the automatic threat detection the scenario requires, unlike auditing or static firewall rules, which log or block but do not alert on suspicious patterns.

Why this answer

Microsoft Defender for SQL (formerly Azure Security Center's Advanced Threat Protection) is the correct feature because it continuously monitors database traffic for anomalous activities, including SQL injection, brute-force attacks, and privilege escalation. When suspicious behavior is detected, it generates a security alert that can be viewed in the Azure portal or integrated with Azure Sentinel for automated response. This is the only option among the choices that provides proactive threat detection and alerting for suspicious activities.

Exam trap

The trap here is that candidates often confuse Azure SQL Auditing (which logs events) with Microsoft Defender for SQL (which actively detects threats), leading them to choose Auditing because it sounds like it would 'detect' suspicious activity, but it only records data for manual review, not automatic alerting.

How to eliminate wrong answers

Option A is wrong because Azure SQL Auditing tracks and logs database events (e.g., successful and failed logins, schema changes) for compliance and forensic analysis, but it does not automatically detect or alert on suspicious activities like SQL injection — it only records the raw data for later review. Option B is wrong because SQL Vulnerability Assessment scans for misconfigurations, missing patches, and security best-practice violations (e.g., weak firewall rules or excessive permissions), but it is a static assessment tool that does not monitor real-time traffic or detect active attacks. Option D is wrong because Transparent Data Encryption (TDE) performs real-time encryption and decryption of data at rest (the database files and backups) using a symmetric key, but it has no capability to detect or alert on suspicious database activities.

Full explanation →

186

MCQeasy

Your company is deploying a new application that will use Azure SQL Database. You need to ensure that all connections to the database use Microsoft Entra ID authentication. Which step is required to enable this?

A.Configure an Entra ID administrator for the Azure SQL logical server.

B.Enable the Azure SQL Database firewall to allow only Entra ID IP addresses.

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

D.Set the database to read-only mode.

AnswerA

Configuring a Microsoft Entra ID administrator for the Azure SQL logical server is mandatory before Entra authentication can be enabled. This server-level assignment creates the security principal that authorises token-based connections, satisfying the requirement that all database connections use Microsoft Entra ID rather than SQL logins.

Why this answer

To enforce Microsoft Entra ID authentication for all connections to Azure SQL Database, you must first configure an Entra ID administrator at the logical server level. This step establishes the server’s trust relationship with the Entra ID tenant, enabling token-based authentication using OAuth 2.0. Without this administrator, Entra ID authentication cannot be used, and only SQL authentication would be available.

Exam trap

The trap here is that candidates often confuse the server-level Entra ID administrator configuration (a prerequisite) with the creation of contained database users (a downstream step), leading them to select Option C as the first required step.

How to eliminate wrong answers

Option B is wrong because the Azure SQL Database firewall controls network access by IP address or virtual network rules, not authentication methods; it cannot restrict authentication to Entra ID only. Option C is wrong because creating a contained database user mapped to an Entra ID identity is a step taken after the server-level Entra ID administrator is configured, not the prerequisite to enable Entra ID authentication. Option D is wrong because setting the database to read-only mode prevents write operations but does not enforce any authentication method; it is unrelated to authentication configuration.

Full explanation →

187

MCQeasy

You need to create an Azure SQL Database that will be used by a new application. The database must support JSON data storage and querying. Which data type should you use to store JSON documents?

A.TEXT

B.NVARCHAR

C.XML

D.VARCHAR

AnswerB

NVARCHAR stores JSON documents as text, and Azure SQL Database's built-in JSON functions — JSON_VALUE, JSON_QUERY and OPENJSON — parse and query that text without a native JSON type. This satisfies the requirement to store and query JSON data, unlike XML or other types.

Why this answer

B is correct because Azure SQL Database uses the NVARCHAR data type to store JSON documents as text. JSON is stored as a string in NVARCHAR columns, which supports Unicode characters and allows the use of built-in JSON functions like JSON_VALUE, JSON_QUERY, OPENJSON, and ISJSON for validation and querying. NVARCHAR(MAX) is recommended for large or variable-length JSON documents.

Exam trap

The trap here is that candidates often assume JSON requires a special data type like XML or a binary format, but Azure SQL Database stores JSON as plain text in NVARCHAR, leveraging the same string-based approach used for other semi-structured data.

How to eliminate wrong answers

Option A is wrong because TEXT is a deprecated data type in SQL Server and Azure SQL Database, and it does not support the JSON functions or Unicode characters needed for modern JSON processing. Option C is wrong because XML is designed for XML data and its associated XQuery functions, not for JSON; using XML would require conversion and lacks native JSON support. Option D is wrong because VARCHAR is a non-Unicode data type that cannot properly store JSON containing Unicode characters, and it is not optimized for the JSON functions in Azure SQL Database.

Full explanation →

188

MCQeasy

You are a database administrator for a large financial services company. You need to ensure that all queries that read sensitive customer data use an optimized execution plan. What feature should you enable to automatically identify and fix regressed query plans?

A.Query Store

B.Automatic Tuning

C.Intelligent Insights

D.Database Advisor for SQL Database

AnswerB

Automatic Tuning continuously monitors query execution on Azure SQL and automatically detects plan regressions, forcing a corrected plan without manual intervention. This directly satisfies the requirement to identify and fix regressed plans for sensitive-data queries.

Why this answer

Automatic Tuning in Azure SQL Database and SQL Managed Instance automatically identifies and fixes plan regressions by forcing the last known good plan, and can also create/drop indexes. It directly addresses the requirement to automatically identify and fix regressed query plans without manual intervention.

Exam trap

DP-300 often tests the confusion between Query Store (monitoring/telemetry) and Automatic Tuning (automated remediation), causing candidates to pick Query Store when the question asks for automatic fixing.

How to eliminate wrong answers

Option A is wrong because Query Store captures query execution history and plan performance but does not automatically fix regressions — it is the data source that Automatic Tuning uses. Option C is wrong because Intelligent Insights is a diagnostics feature that detects performance issues and provides root-cause analysis, but it does not automatically correct plan regressions. Option D is wrong because Database Advisor provides recommendations (e.g., index, parameterization) but does not automatically force plans or fix regressions on its own.

Full explanation →

189

MCQmedium

You are the Azure SQL Database administrator for a healthcare company. A new compliance requirement mandates that all data at rest in Azure SQL Database be encrypted with a customer-managed key (CMK) stored in Azure Key Vault, and that you can revoke access to the key at any time. The database is currently encrypted with the default service-managed key. What should you do first to meet this requirement?

A.Enable dynamic data masking on all columns containing sensitive data.

B.Enable Always Encrypted on all sensitive columns using a column master key stored in Azure Key Vault.

C.Configure an Azure SQL Database auditing policy to send logs to a storage account encrypted with a customer-managed key.

D.Create an Azure Key Vault, generate a key, and configure the SQL server's Transparent Data Encryption (TDE) protector to use that key.

AnswerD

This is the correct first step because Azure SQL Database TDE with customer-managed keys requires an Azure Key Vault (or Managed HSM) to store the asymmetric key, and then the logical server's TDE protector must be pointed to that key. Once configured, the database's data encryption key is wrapped by the customer key, allowing revocation via Key Vault permissions.

Why this answer

The requirement is for encryption at rest with a customer-managed key that can be revoked. Azure SQL Database TDE supports customer-managed keys stored in Azure Key Vault. The first step is to create the Key Vault and key, then configure the server's TDE protector to use it.

This ensures the database encryption key is protected by the customer key, and revoking access in Key Vault effectively blocks decryption.

Exam trap

The trap here is confusing Always Encrypted with TDE; Always Encrypted protects individual columns and does not provide database-level encryption at rest with a revocable customer-managed key.

Full explanation →

190

MCQmedium

You are deploying an Azure SQL Managed Instance for a legacy application that requires SQL Server Agent jobs and cross-database queries. The instance must be able to access an on-premises file share for backup and restore operations. You need to configure network connectivity so that the managed instance can communicate with the on-premises network. What should you implement?

A.Enable a public endpoint on the managed instance and allow access from the on-premises public IP address.

B.Configure a site-to-site VPN or ExpressRoute between the on-premises network and the Azure virtual network that hosts the managed instance.

C.Create a VNet peering between the managed instance subnet and the on-premises network.

D.Configure an Azure Private Link endpoint for the managed instance and connect it to the on-premises network.

AnswerB

Azure SQL Managed Instance is deployed inside an Azure virtual network subnet and requires network connectivity to on-premises resources. A site-to-site VPN or ExpressRoute provides the necessary private connection. This allows the managed instance to access on-premises file shares and other resources securely without exposing them to the public internet.

Why this answer

Azure SQL Managed Instance is deployed in a virtual network subnet and requires a site-to-site VPN or ExpressRoute to communicate with on-premises resources. This provides a private, secure connection that allows the managed instance to access on-premises file shares for backup and restore. Public endpoints, VNet peering, and Private Link do not provide the necessary outbound connectivity to on-premises networks.

Exam trap

The trap here is confusing inbound connectivity to the managed instance with outbound connectivity from the managed instance to on-premises resources, leading to the selection of a public endpoint or Private Link instead of a VPN or ExpressRoute.

Full explanation →

191

MCQeasy

You are configuring alerts for an Azure SQL Database. You need to create an alert that fires when the database's DTU consumption exceeds 80% for a sustained period. Which Azure Monitor metric should you use?

A.Sessions count

B.DTU percentage

C.Storage percent

D.CPU percent

AnswerB

DTU percentage is the correct metric because it represents the percentage of DTU consumption relative to the database's DTU limit. This directly aligns with the requirement to alert when DTU consumption exceeds 80%, as it provides a normalized view of resource usage.

Why this answer

The DTU percentage metric provides the percentage of DTU consumption against the database's limit. Setting an alert on this metric with a threshold of 80% will notify when DTU usage exceeds that level, directly meeting the requirement. Other metrics like CPU or storage do not capture the full DTU picture.

Exam trap

The trap here is selecting a component metric like CPU percent instead of the composite DTU percentage metric.

Full explanation →

192

Multi-Selecteasy

Which TWO of the following are valid methods to connect to Azure SQL Database securely?

Select 2 answers

A.Connect using Azure AD authentication with multi-factor authentication.

B.Connect using a shared access key from Azure Storage.

C.Connect using a private endpoint within a virtual network.

D.Connect directly using the server's public IP address without encryption.

E.Connect using SQL authentication with a simple password.

AnswersA, C

Microsoft Entra ID authentication with multi-factor authentication verifies identity through Entra ID and adds a second factor, defeating stolen-password attacks. Traffic remains encrypted via TLS, satisfying the stem's secure connection requirement without embedding credentials in connection strings.

Why this answer

Option A is correct because Azure SQL Database natively supports Azure Active Directory (Azure AD, now Microsoft Entra ID) authentication, and enforcing multi-factor authentication adds a strong identity-verification layer that eliminates static password-only credential risks. Option C is correct because a private endpoint assigns a private IP address from your virtual network to the Azure SQL logical server, so traffic traverses the Microsoft backbone via Azure Private Link instead of the public internet, removing public exposure. Option B is wrong because shared access keys are an Azure Storage construct (used for blobs, queues, tables, files), not an authentication mechanism for Azure SQL Database.

Option D is wrong because connecting over the public IP without encryption (no TLS) exposes credentials and data in transit and is not a secure method. Option E is wrong because SQL authentication with a simple password is weak, lacks MFA, and is vulnerable to brute-force and credential-stuffing attacks.

Exam trap

The trap here is that candidates may confuse shared access keys (a Storage concept) with SQL Database connection methods, or assume that a simple password is acceptable for security, when the exam emphasizes Azure AD and network isolation as the secure standards.

Full explanation →

193

MCQeasy

You are responsible for a set of Azure SQL Databases that are used by different departments in your organization. The databases are deployed in an elastic pool with Standard tier (eDTU 200). Usage patterns show that the marketing database uses high CPU during the day, while the sales database uses high IO at night. You want to optimize costs while ensuring each database gets the resources it needs. What should you do?

A.Configure minimum and maximum eDTU per database in the pool

B.Migrate the pool to a vCore-based elastic pool

C.Add more databases to the pool to spread the load

D.Move each database to a standalone DTU tier

AnswerA

Per-database minimum and maximum eDTU settings let the marketing database reserve CPU during the day while the sales database draws IO capacity at night, sharing the pool's 200 eDTUs. This matches each workload's peak without over-provisioning, satisfying the cost-optimisation constraint.

Why this answer

In an elastic pool, you can set per-database minimum and maximum eDTU limits to guarantee a floor of resources for each database while capping how much any single database can consume. This lets the marketing database get the CPU it needs during the day and the sales database get the IO it needs at night, without one starving the other, and it optimizes cost by keeping the shared pool model.

Exam trap

The trap is thinking that changing the tier or adding databases solves contention — the question's clue about complementary day/night usage points to per-database min/max eDTU configuration within the existing pool.

How to eliminate wrong answers

Option B is wrong because migrating to a vCore-based elastic pool changes the purchasing model (vCores vs eDTUs) but does not by itself solve the resource-contention problem — you would still need to configure per-database min/max, and it may increase cost. Option C is wrong because adding more databases to the pool increases contention for the same eDTU resources, making the problem worse, not better. Option D is wrong because moving each database to a standalone DTU tier eliminates the cost benefit of pooling and over-provisions each database for its peak, which is exactly what the question asks to avoid.

Full explanation →

194

Multi-Selectmedium

You are configuring an Azure Automation runbook to perform daily maintenance tasks on an Azure SQL Database. The runbook will run on a schedule and must securely connect to the database. Which two actions should you perform to enable the runbook to authenticate to the database? (Choose two.)

Select 2 answers

A.Store the SQL admin credentials in Azure Key Vault and retrieve them in the runbook

B.Configure the runbook to use a service principal with a client secret stored in an Automation variable

C.Enable a managed identity for the Automation account

D.Assign the Automation account the Contributor role on the Azure SQL Server

E.Create a contained database user in the target database for the managed identity

AnswersC, E

Enabling a managed identity for the Automation account creates an identity in Azure AD that the runbook can use to authenticate to Azure SQL Database without storing credentials. This is a secure, recommended practice. The identity must then be granted access to the database. This action is essential for the runbook to obtain a token and connect.

Why this answer

To enable an Azure Automation runbook to authenticate to Azure SQL Database securely without storing credentials, you should enable a managed identity for the Automation account and create a contained database user for that identity in the target database. These two actions allow the runbook to obtain an Azure AD token and connect. Other options either involve secret management or grant insufficient permissions.

Exam trap

The trap here is confusing management-plane roles with data-plane permissions; the Contributor role does not grant the ability to run T-SQL, and using Key Vault or service principals introduces unnecessary secret management.

Full explanation →

195

MCQhard

Your Azure SQL Database is configured with the Hyperscale service tier. You observe that log write latency is consistently high, affecting transaction throughput. What is the most likely cause and the recommended mitigation?

A.The log IOPS is limited by the disk performance; increase the provisioned IOPS.

B.The compute replica is undersized; scale up the compute to increase log throughput.

C.High log generation rate is causing log rate governance throttling; reduce the log generation rate by batching transactions.

D.The log write latency is due to network congestion; move the database to a different region.

AnswerC

Log rate governance throttles to protect secondary replicas; reducing log generation mitigates.

Why this answer

In Hyperscale, log rate is governed to protect secondary replicas; high latency indicates throttling. Option A is wrong because Hyperscale uses local SSD for log, not PIOPS. Option B is wrong because log rate governance affects all log writes.

Option D is wrong because scaling up compute doesn't increase log throughput limits.

Full explanation →

196

MCQeasy

A company runs Azure SQL Database and wants to automatically receive an email alert when the database's CPU usage exceeds 90% for 10 minutes. The DBA needs to configure this with minimal effort. What should the DBA do?

A.Use Azure Automation to run a runbook every 10 minutes that checks CPU usage and sends an email.

B.Create an Azure Monitor alert rule on the cpu_percent metric with a threshold of 90 and an action group that sends email.

C.Configure a SQL Server Agent alert on the CPU usage performance condition.

D.Create a Database Mail profile and a SQL Agent operator to send the alert.

AnswerB

Azure Monitor alert rules can evaluate platform metrics like cpu_percent for Azure SQL Database. Setting a threshold of 90 and an action group with email notification directly fulfills the requirement. This is the native, low-effort solution for metric-based alerts on Azure SQL Database.

Why this answer

Azure Monitor alert rules are the native way to monitor platform metrics for Azure SQL Database. By creating a rule on the cpu_percent metric with a threshold and an action group that includes email, the DBA can automatically receive notifications when CPU exceeds 90% for the specified duration. This requires minimal configuration effort.

Exam trap

The trap here is confusing Azure SQL Database with Azure SQL Managed Instance, leading to the assumption that SQL Server Agent alerts and Database Mail are available on Azure SQL Database.

Full explanation →

197

MCQeasy

You are a database administrator for a company that stores sensitive customer data in Azure SQL Database. The security team requires that all access to the database be authenticated using Microsoft Entra ID and that no SQL authentication logins exist. You need to verify that SQL authentication is disabled. What should you do?

A.Set the 'Deny public network access' property to 'Yes'

B.Configure a server-level firewall rule to block all IP addresses

C.In the Azure portal, navigate to the SQL server's 'Microsoft Entra ID' blade and enable 'Azure AD-only authentication'

D.Query sys.sql_logins to check for any SQL authenticated logins

AnswerC

Enabling Microsoft Entra ID-only authentication on the logical server rejects all SQL logins, directly satisfying the requirement that no SQL authentication logins exist. This server-level setting disables the SQL authentication endpoint entirely, so existing SQL logins can no longer connect.

Why this answer

Enabling 'Azure AD-only authentication' in the SQL server's Microsoft Entra ID blade disables SQL authentication entirely, ensuring that only Microsoft Entra ID principals can authenticate. This directly meets the security requirement to eliminate SQL authentication logins, as it prevents any connection attempt using SQL login credentials, even if they exist in the system catalog.

Exam trap

The trap here is that candidates often think querying sys.sql_logins (Option D) is sufficient to verify the requirement, but the question asks to disable SQL authentication, not just check for its existence, and only the Azure AD-only authentication setting enforces the block.

How to eliminate wrong answers

Option A is wrong because setting 'Deny public network access' to 'Yes' only blocks connections from public endpoints, but does not disable SQL authentication; private endpoint connections could still use SQL logins. Option B is wrong because configuring a server-level firewall rule to block all IP addresses restricts network access but does not disable SQL authentication; authenticated users from allowed IPs could still use SQL logins. Option D is wrong because querying sys.sql_logins only checks for existing SQL authenticated logins, but does not disable them or prevent their use; the requirement is to disable SQL authentication, not just verify its existence.

Full explanation →

198

Multi-Selecthard

You are configuring an Azure SQL Database to meet a compliance requirement that mandates encryption of data at rest with customer-managed keys and the ability to audit all access to the database. You need to implement the necessary features. Which two actions should you perform? (Choose two.)

Select 2 answers

A.Implement dynamic data masking on all sensitive columns.

B.Configure Always Encrypted with column master keys in Azure Key Vault.

C.Enable transparent data encryption (TDE) with a customer-managed key stored in Azure Key Vault.

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

E.Configure SQL auditing to write audit logs to an Azure Storage account or Log Analytics workspace.

AnswersC, E

TDE with a customer-managed key in Azure Key Vault satisfies the encryption at rest requirement. It allows you to control the encryption key and meet compliance mandates. This is a direct action to encrypt the database at rest using your own key, which is essential for the scenario.

Why this answer

To meet encryption at rest with customer-managed keys, enable TDE with a customer-managed key in Azure Key Vault. To audit all access, configure SQL auditing to a storage account or Log Analytics. Advanced Threat Protection is for threat detection, dynamic data masking is for masking, and Always Encrypted is for column-level encryption, not full database encryption at rest.

Exam trap

The trap here is confusing Always Encrypted with TDE for encryption at rest; Always Encrypted only protects specific columns and does not encrypt the entire database.

Full explanation →

199

Multi-Selecteasy

You are planning high availability for an Azure SQL Database that runs an e-commerce application. The database uses the Business Critical service tier. Which TWO features are automatically enabled to provide high availability within a single region? (Choose two.)

Select 2 answers

A.Automatic failover to a secondary replica if the primary becomes unavailable.

B.Multiple synchronous replicas (one primary, two secondary) in the same region.

C.Auto-failover groups with a secondary in another region.

D.Active geo-replication to a paired region.

E.Read scale-out with a readable secondary replica.

AnswersA, B

Business Critical provisions a primary plus secondary replicas with synchronous commit, so the platform automatically redirects connections to a healthy replica when the primary fails. This built-in failover satisfies the single-region high availability requirement without manual intervention.

Why this answer

Option A is correct because in the Business Critical tier Azure SQL Database automatically provisions a primary replica plus secondary replicas and performs automatic failover to a secondary if the primary becomes unavailable, with no user action required. Option B is correct because Business Critical uses Always On availability group technology to maintain multiple synchronous replicas (one primary and two secondary) within the same region, ensuring data redundancy and fast failover. Option C is incorrect because auto-failover groups are a user-configured feature for cross-region disaster recovery, not an automatic single-region HA capability.

Option D is incorrect because active geo-replication is a manually configured cross-region replication feature, not automatically enabled for single-region HA. Option E is incorrect because read scale-out using a readable secondary is an optional configuration for offloading read workloads, not an automatic HA mechanism.

Exam trap

The trap is conflating single-region automatic HA (built into Business Critical) with cross-region DR features like auto-failover groups and active geo-replication, which are separate and manually configured.

Full explanation →

200

MCQeasy

You need to configure a long-term retention policy for backups of an Azure SQL Database that must retain weekly full backups for 5 years and monthly full backups for 10 years. Which backup retention feature should you use?

A.Geo-restore feature

B.Long-Term Retention (LTR) policy

C.Automated backups retention period

D.Point-In-Time Restore (PITR) retention

AnswerB

LTR extends retention beyond the default point-in-time window, letting you keep weekly full backups for 5 years and monthly full backups for 10 years independently. Standard short-term retention cannot meet these multi-year durations, so LTR directly satisfies the stated 5-year and 10-year requirements.

Why this answer

Long-Term Retention (LTR) policies in Azure SQL Database are specifically designed to retain full backups for extended periods — up to 10 years — with configurable weekly, monthly, and yearly retention. To keep weekly full backups for 5 years and monthly full backups for 10 years, the consultant must configure an LTR policy with the appropriate weekly and monthly retention settings.

Exam trap

The trap is confusing short-term automated backup retention (up to 35 days) with long-term retention, causing candidates to select the automated backups option for multi-year compliance requirements.

How to eliminate wrong answers

Option A is wrong because geo-restore uses geo-replicated backups for disaster recovery, not for long-term retention compliance. Option C is wrong because the automated backups retention period (1–35 days) is for short-term point-in-time restore, not multi-year retention. Option D is wrong because PITR retention is limited to a maximum of 35 days and cannot satisfy 5- or 10-year requirements.

Full explanation →

201

MCQmedium

Your company plans to use Azure SQL Managed Instance for a mission-critical application. You need to ensure that all connections to the database are encrypted and that the server's identity is verified. Which configuration should you enforce?

A.Set 'Force Encryption' = OFF and 'Trust Server Certificate' = OFF

B.Set 'Force Encryption' = ON and 'Trust Server Certificate' = OFF

C.Set 'Force Encryption' = ON and 'Trust Server Certificate' = ON

D.Set 'Force Encryption' = OFF and 'Trust Server Certificate' = ON

AnswerB

Forcing encryption guarantees the connection is TLS-protected, while disabling Trust Server Certificate compels the client to validate the server certificate chain against a trusted authority, verifying the instance's identity. This satisfies both the encryption and identity-verification requirements without bypassing certificate validation, unlike trusting self-signed certificates.

Why this answer

Setting 'Force Encryption' = ON ensures that all connections to Azure SQL Managed Instance use TLS encryption, while setting 'Trust Server Certificate' = OFF forces the client to validate the server's certificate against a trusted certificate authority (CA). This combination guarantees both data-in-transit encryption and server identity verification, meeting the requirement for a mission-critical application.

Exam trap

The trap here is that candidates often confuse 'Trust Server Certificate' = ON as a convenience setting that simplifies connections, not realizing it disables certificate validation and undermines security for mission-critical workloads.

How to eliminate wrong answers

Option A is wrong because setting 'Force Encryption' = OFF allows unencrypted connections, violating the encryption requirement. Option C is wrong because setting 'Trust Server Certificate' = ON instructs the client to trust the server certificate without validation, bypassing identity verification and potentially allowing man-in-the-middle attacks. Option D is wrong because setting 'Force Encryption' = OFF permits unencrypted connections, and 'Trust Server Certificate' = ON disables certificate validation, failing both encryption and identity verification requirements.

Full explanation →

202

MCQmedium

You manage an Azure SQL Database that uses the General Purpose tier. The database has a failover group with a secondary in a paired region. During a regional outage, you initiate a forced failover. After the outage is resolved, you want to bring the original primary region back online without data loss. What should you do?

A.Initiate a forced failover from the new primary to the old primary.

B.Perform a forced failover again to switch back.

C.Wait for data synchronization, then initiate a planned failover.

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

AnswerC

After a forced failover the original primary becomes the secondary and must resynchronise. Waiting for synchronisation to complete, then running a planned failover, returns workloads to the original region with no data loss, satisfying the stated requirement.

Why this answer

After a forced failover, the original primary becomes the secondary and enters a state where it may have diverged data. To restore it as primary without data loss, you must wait for geo-replication to catch up and re-synchronize, then perform a planned (graceful) failover. A planned failover ensures no data loss by confirming the secondary is fully synchronized before switching roles.

Exam trap

DP-300 often tests the difference between forced and planned failover, and candidates mistakenly think any failover can be used to switch back without considering data synchronization requirements.

How to eliminate wrong answers

Option A is wrong because initiating a forced failover from the new primary to the old primary would again risk data loss and is not the correct way to restore the original primary after an outage. Option B is wrong because performing another forced failover does not guarantee synchronization and can cause additional data loss. Option D is wrong because deleting and recreating the failover group is disruptive, unnecessary, and does not ensure data consistency; it also loses the existing replication configuration.

Full explanation →

203

Multi-Selecteasy

You are monitoring an Azure SQL Database. You need to identify which built-in tools can provide real-time performance data without additional cost. Which THREE should you select?

Select 3 answers

A.Azure Monitor Metrics

B.Performance Insights

C.Query Store

D.Dynamic Management Views (DMVs)

E.SQL Server Profiler

AnswersA, C, D

Azure Monitor provides free metrics for Azure SQL Database.

Why this answer

Azure Monitor Metrics is a built-in, no-cost feature that collects and stores platform metrics from Azure SQL Database at near-real-time intervals (typically every minute). It provides performance counters such as DTU/CPU usage, data IO, and log write percentages without requiring additional configuration or licensing, making it a correct choice for real-time performance data.

Exam trap

The trap here is that candidates confuse Performance Insights (an AWS service) with Azure's Query Performance Insight, or assume SQL Server Profiler is a built-in, cost-free tool for Azure SQL Database when it is neither native nor free.

Full explanation →

204

Multi-Selecthard

You need to automate monitoring and alerting for an Azure SQL Database. Which THREE actions can you achieve using Azure Monitor and SQL Insights?

Select 3 answers

A.Automatically rebuild fragmented indexes.

B.Automatically scale the database to a higher service tier.

C.Stream diagnostic logs to a Log Analytics workspace.

D.Set up an alert when DTU consumption exceeds 80% for 5 minutes.

E.Create a custom metric alert based on a KQL query.

AnswersC, D, E

Diagnostic settings allow log streaming.

Why this answer

Azure Monitor and SQL Insights allow you to configure diagnostic settings to stream SQL Database metrics and resource logs (e.g., query store runtime statistics, wait statistics) to a Log Analytics workspace. This enables centralized log analysis, custom KQL queries, and long-term retention for auditing and troubleshooting.

Exam trap

The trap here is that candidates confuse monitoring and alerting capabilities (Azure Monitor/SQL Insights) with automated remediation actions (like index rebuilds or auto-scaling), which are separate Azure services or manual tasks.

Full explanation →

205

MCQhard

You are reviewing an ARM template for Azure SQL Database. The exhibit shows the database settings. You notice the database is not being automatically paused. What is the most likely explanation?

A.The minCapacity is set too low

B.The autoPauseDelay is set to 60 minutes

C.The licenseType is set to BasePrice

D.The database uses VBS enclaves which are incompatible with serverless

AnswerD

Serverless does not support VBS enclaves.

Why this answer

Auto-pause is only supported for General Purpose serverless databases, and the use of VBS enclave (preferredEnclaveType: "VBS") indicates Always Encrypted with secure enclaves, which is not supported with serverless. Option A is wrong because minCapacity 0.5 is valid for serverless. Option B is wrong because licenseType BasePrice does not affect auto-pause.

Option C is wrong because autoPauseDelay 60 minutes is valid; the default is 60.

Full explanation →

206

MCQhard

You are automating index maintenance for an Azure SQL Database using an Azure Automation runbook. The runbook connects to the database and executes T-SQL to rebuild fragmented indexes. You need to ensure the runbook can authenticate without storing credentials in the script. What should you configure?

A.A service principal with a client secret stored in the Automation account certificates

B.A managed identity for the Automation account with a contained database user in the target database

C.A SQL login with a strong password stored in an Automation variable

D.Azure Key Vault to store the SQL admin credentials, retrieved by the runbook at runtime

AnswerB

A managed identity for the Automation account allows the runbook to authenticate to Azure SQL Database without embedding credentials. You create a contained database user mapped to the managed identity and grant necessary permissions. This approach eliminates secret management and is the recommended secure method for Azure services to access databases. The runbook can use the identity to obtain a token and connect using Azure AD authentication.

Why this answer

The most secure and streamlined method for an Azure Automation runbook to authenticate to Azure SQL Database without storing credentials is to use a managed identity. The Automation account's managed identity is registered in Azure AD, and you create a contained database user in the target database for that identity. The runbook then connects using Azure AD authentication, eliminating secrets.

This is the recommended practice for automation.

Exam trap

The trap here is believing that storing credentials in Azure Key Vault or Automation variables fully satisfies the requirement, but those still involve managing secrets, whereas a managed identity eliminates them.

Full explanation →

207

MCQmedium

Refer to the exhibit. You are reviewing an ARM template for deploying an Azure SQL Database. The template specifies a point-in-time restore from a source database. The source database is configured with geo-redundant backup storage. You need to ensure that the restored database can be used for disaster recovery in a different region. What is missing from the template to achieve this?

A.Add a 'location' property to the database resource.

B.Set 'zoneRedundant' to true.

C.Change 'requestedBackupStorageRedundancy' to 'GeoZone'.

D.Set 'createMode' to 'GeoRestore' and specify a target server in the desired region.

AnswerD

GeoRestore is the only createMode that restores a geo-redundant backup into a different region, so the template must set createMode to GeoRestore and name a target server in the desired region. This satisfies the cross-region disaster recovery requirement.

Why this answer

To restore a geo-redundant backup to a different region, the ARM template must use createMode 'GeoRestore' and point to a target logical server in the desired region. A standard PointInTimeRestore restores to the same region and server, so it cannot satisfy the cross-region DR requirement. GeoRestore is the specific createMode that instructs Azure SQL to pull the geo-replicated backup and materialize the database on the specified target server in the alternate region.

Exam trap

DP-300 often tests the distinction between PointInTimeRestore (same region) and GeoRestore (cross-region), tricking candidates into thinking that adding a location property or changing backup redundancy alone enables cross-region recovery.

How to eliminate wrong answers

Option A is wrong because simply adding a 'location' property to the database resource does not change the restore semantics; the database inherits the server's region, and a PointInTimeRestore createMode still targets the original region. Option B is wrong because zoneRedundant applies to availability-zone resiliency within a single region, not cross-region disaster recovery. Option C is wrong because 'GeoZone' is not a valid value for requestedBackupStorageRedundancy; valid values are Local, Zone, and Geo, and changing backup redundancy alone does not perform a cross-region restore.

Full explanation →

208

MCQhard

You are deploying an Azure SQL Managed Instance to support a lift-and-shift migration of an on-premises SQL Server database. The application requires the ability to restore a database from a backup taken on an on-premises SQL Server 2019 instance. The backup file is stored in an Azure Storage account. You need to restore the database to the managed instance. What should you do first?

A.Import the backup file into an Azure SQL Database using the SqlPackage utility.

B.Use the RESTORE DATABASE statement with the FROM URL clause pointing to the backup file in Azure Storage.

C.Configure Azure Backup to back up the on-premises SQL Server and restore directly to the managed instance.

D.Use the Data Migration Assistant to migrate the database schema and data to the managed instance.

AnswerB

Azure SQL Managed Instance supports native RESTORE DATABASE from a backup file stored in Azure Blob Storage using the FROM URL clause. This is the primary method for restoring on-premises backups to a managed instance, provided the backup is a full backup and the storage account is accessible with a SAS token or managed identity.

Why this answer

Azure SQL Managed Instance supports the RESTORE DATABASE statement with the FROM URL clause, allowing you to restore a native SQL Server backup stored in Azure Blob Storage. This is the correct first step to restore the on-premises backup. Other tools like SqlPackage or DMA do not support native backup restoration.

Exam trap

The trap here is assuming that any migration tool can restore a native backup, when only the RESTORE DATABASE ... FROM URL command is designed for that purpose on Managed Instance.

Full explanation →

209

MCQmedium

Your Azure SQL Managed Instance is experiencing high PAGELATCH_SH waits. You need to reduce this contention. What should you implement?

A.Scale up the managed instance to a higher service tier

B.Enable delayed durability

C.Configure a readable secondary replica

D.Add more data files to the filegroup

AnswerD

Adding data files spreads insert activity across multiple allocation bitmaps, relieving PAGELATCH_SH contention on last-page insert hotspots. This directly addresses the stem's high PAGELATCH_SH waits, which stem from concurrent inserts competing for the same page and allocation structures within a single file.

Why this answer

Adding more data files to the filegroup spreads out page allocations, reducing contention for allocation structures and thus decreasing PAGELATCH_SH waits. Option A is incorrect because scaling up may provide more resources but does not directly address the page latch contention caused by allocation bottlenecks. Option B is incorrect because delayed durability only affects transaction log write behavior, not page latches.

Option C is incorrect because a readable secondary replica does not alleviate latch contention on the primary; it is designed for read workload offloading, not contention reduction.

Full explanation →

210

MCQhard

Refer to the exhibit. After a brief outage, the availability group recovered. However, SQL2 shows NOT_HEALTHY and DISCONNECTED. What is the most likely cause?

A.The automatic failover policy requires a quorum that is not met.

B.The listener is not configured correctly for SQL2.

C.The secondary replica SQL2 has an incompatible database version.

D.The network connection between SQL1 and SQL2 is still down after the outage.

AnswerD

A persistent network partition prevents SQL2 from exchanging heartbeat messages with the primary replica, so the availability group reports DISCONNECTED and NOT_HEALTHY. Without connectivity, the secondary cannot synchronise or confirm its state, directly matching the stem's post-outage symptom of a replica that recovered but remains unreachable.

Why this answer

The most likely cause is that the network connection between SQL1 and SQL2 is still down after the outage. In an Always On availability group, a secondary replica showing NOT_HEALTHY and DISCONNECTED typically indicates a connectivity issue between the primary and secondary. Since the outage occurred, it's plausible that network connectivity was not fully restored.

Exam trap

The trap is that candidates might focus on quorum or listener issues, but the specific combination of NOT_HEALTHY and DISCONNECTED on one secondary points directly to a network connectivity problem.

How to eliminate wrong answers

Option A is wrong because a quorum issue would affect the availability group's ability to failover, but it would not cause a specific secondary to be DISCONNECTED; other replicas might also be affected. Option B is wrong because a misconfigured listener would affect client connectivity, not the health state of the secondary replica. Option C is wrong because an incompatible database version would prevent the secondary from joining the availability group in the first place, not cause a sudden disconnection after an outage.

Full explanation →

211

MCQhard

You have an Azure SQL Database with Query Store enabled. You notice that a critical stored procedure has regressed in performance. You need to force a previous, better-performing execution plan for that query. What should you do?

A.Disable and re-enable Query Store to reset the plans.

B.Execute sys.sp_query_store_set_hints to add a query hint.

C.Use sys.dm_db_tuning_recommendations to apply a plan.

D.Execute sys.sp_query_store_force_plan with the query_id and plan_id.

AnswerD

Query Store persists multiple plans per query, and sys.sp_query_store_force_plan pins the specified plan_id to the query_id, forcing the optimiser to reuse the previously better-performing plan without altering the stored procedure or clearing the plan cache.

Why this answer

`sys.sp_query_store_force_plan` is the dedicated system stored procedure in Azure SQL Database that forces the Query Store to use a specific execution plan for a given query. When a stored procedure's performance regresses, you identify the query_id and plan_id of the better-performing plan from Query Store views and execute this procedure to pin that plan, overriding the optimizer's choice.

Exam trap

The trap here is that candidates confuse the DMV for recommendations (`sys.dm_db_tuning_recommendations`) with the actual stored procedure to enforce a plan, or they think resetting Query Store is a valid troubleshooting step when it actually destroys historical data.

How to eliminate wrong answers

Option A is wrong because disabling and re-enabling Query Store would clear all historical plan data and force statistics, losing the ability to identify and force a previous good plan, which is counterproductive. Option B is wrong because `sys.sp_query_store_set_hints` is used to add query-level hints (e.g., `RECOMPILE`, `MAXDOP`) to influence future plan generation, not to force an existing historical plan. Option C is wrong because `sys.dm_db_tuning_recommendations` is a dynamic management view that surfaces automated tuning recommendations (e.g., create/drop indexes, force plan), but applying a plan force requires executing `sys.sp_query_store_force_plan` directly; the DMV itself does not apply changes.

Full explanation →

212

MCQhard

You are deploying an Azure SQL Database that will contain highly sensitive personal data. The security policy requires that the data be encrypted at rest, in transit, and in use. Additionally, the encryption keys must be stored in a hardware security module (HSM) and be customer-managed. Which combination of features should you implement?

A.TDE with a service-managed key, enforce TLS 1.2, and Always Encrypted with a column master key in Key Vault.

B.TDE with a customer-managed key in Key Vault, enforce TLS 1.2, and use Dynamic Data Masking.

C.TDE with a customer-managed key in Key Vault, enforce TLS 1.2, and Always Encrypted with a column master key stored in Windows Certificate Store.

D.TDE with a customer-managed key in Azure Key Vault (HSM-backed), enforce TLS 1.2, and Always Encrypted with a column master key in Azure Key Vault (HSM-backed).

AnswerD

TDE with a customer-managed HSM-backed key in Azure Key Vault covers encryption at rest, enforced TLS 1.2 secures data in transit, and Always Encrypted with an HSM-backed column master key protects data in use. This satisfies all three encryption states plus customer-managed HSM keys.

Why this answer

It satisfies all requirements: encryption at rest via TDE with a customer-managed key stored in an HSM-backed Key Vault, encryption in transit by enforcing TLS 1.2, and encryption in use via Always Encrypted with the column master key also stored in an HSM-backed Key Vault. This ensures that all three states of data are encrypted and that keys are both customer-managed and hardware-protected.

Exam trap

The trap here is that candidates may confuse Dynamic Data Masking with encryption in use, or overlook that storing keys in Key Vault does not automatically imply HSM protection unless the vault is specifically HSM-backed.

How to eliminate wrong answers

Option A is wrong because TDE with a service-managed key does not meet the customer-managed key requirement, and storing the column master key in Key Vault alone does not guarantee HSM protection unless the vault is HSM-backed. Option B is wrong because Dynamic Data Masking does not encrypt data in use; it only obfuscates data at query time, failing the encryption-in-use requirement. Option C is wrong because storing the column master key in the Windows Certificate Store does not use an HSM, violating the requirement that keys be stored in a hardware security module.

Full explanation →

213

MCQmedium

You are the database administrator for a company that uses Azure SQL Database. The security team requires that all data in transit between the application and the database be encrypted, and they want to enforce a minimum TLS version of 1.2 at the server level. The application connects using the server's fully qualified domain name. What should you configure to meet this requirement with the least administrative effort?

A.Configure a private endpoint for the Azure SQL server and disable public network access.

B.Create a server-level firewall rule that allows only the application's IP address and set 'Allow Azure services' to OFF.

C.Enable Transparent Data Encryption (TDE) on the database and use a customer-managed key.

D.Set the 'Minimum TLS version' to 1.2 in the Azure SQL server's firewall and virtual networks settings.

AnswerD

The Azure SQL logical server has a 'Minimum TLS version' setting under Security > Firewall and virtual networks (or Networking) in the Azure portal. Setting it to 1.2 enforces TLS 1.2 or higher for all connections to the server, satisfying the requirement without application changes or additional infrastructure.

Why this answer

The requirement is to enforce a minimum TLS version for all connections to the Azure SQL server. The logical server's 'Minimum TLS version' setting directly controls the minimum TLS version accepted. Configuring this setting to 1.2 ensures that any client attempting to connect with an older TLS version is rejected, and it requires no changes to the application or additional networking components.

Exam trap

The trap here is confusing encryption in transit with encryption at rest or network isolation features, and assuming that a private endpoint or TDE automatically enforces a minimum TLS version.

Full explanation →

214

MCQeasy

You are monitoring an Azure SQL Database using Azure Monitor metrics. You need to create an alert that fires when the database's CPU usage exceeds 90% for 10 minutes. Which metric should you use?

A.dtu_consumption_percent

B.cpu_percent

C.physical_data_read_percent

D.log_write_percent

AnswerB

The cpu_percent metric in Azure Monitor for Azure SQL Database represents the percentage of CPU used by the database. It is the correct metric to monitor for CPU usage thresholds. Setting an alert on cpu_percent with a threshold of 90 and an aggregation window of 10 minutes will fire when the average CPU percentage exceeds 90% over that period, meeting the requirement.

Why this answer

Azure SQL Database exposes several Azure Monitor metrics, including cpu_percent, which directly measures CPU usage as a percentage. To alert when CPU exceeds 90% for 10 minutes, you create an alert rule on the cpu_percent metric with a threshold of 90 and an aggregation granularity of 10 minutes. Other metrics like dtu_consumption_percent or log_write_percent measure different resources and would not satisfy the specific CPU monitoring requirement.

Exam trap

The trap here is confusing DTU consumption with CPU usage; DTU combines multiple resources, so it can be high even when CPU is low, making it unsuitable for a CPU-specific alert.

Full explanation →

215

Drag & Dropmedium

Drag and drop the steps to configure an Azure SQL Database elastic pool in the correct order.

Drag or tap steps into the slots.

Steps

Order

1Step 1

2Step 2

3Step 3

4Step 4

Why this order

First create the pool, then add databases, configure per-database limits, monitor, and scale.

Full explanation →

216

Drag & Dropmedium

Drag and drop the steps to configure automatic tuning 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

Automatic tuning is configured via the portal by selecting the database, navigating to automatic tuning, enabling options, and saving.

Full explanation →

217

MCQeasy

You need to monitor the performance of an Azure SQL Database and set up alerts when the DTU consumption exceeds 80% for more than 5 minutes. Which Azure service should you use?

A.Azure Monitor metric alerts

B.Azure Advisor

C.Azure SQL Insights (preview)

D.Log Analytics workspace

AnswerA

Azure Monitor metric alerts evaluate platform metrics such as DTU consumption against thresholds on a defined evaluation frequency and aggregation window, satisfying the requirement to fire when DTU exceeds 80% sustained for more than 5 minutes. Azure SQL Database emits DTU percentage automatically, so no instrumentation is needed.

Why this answer

Azure Monitor metric alerts can be configured on DTU percentage. Option B is wrong because Azure Advisor provides recommendations but not real-time alerts. Option C is wrong because Azure SQL Insights is for visualization, not alerting.

Option D is wrong because Log Analytics workspaces store logs but do not natively provide metric alerts.

Full explanation →

218

MCQmedium

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert indicating 'Degradation in performance due to increased log write wait time'. What is the most likely cause of this issue?

A.High CPU utilization on the database server

B.Long-running blocking transactions

C.The log rate limit has been reached due to high transaction throughput

D.Insufficient storage space for data files

AnswerC

High transaction throughput saturates the transaction log write rate, hitting the Azure SQL Database log rate limit and producing increased log write wait time. Intelligent Insights attributes this specific wait category to log throughput throttling, not to CPU, memory, or storage IOPS pressure elsewhere.

Why this answer

High log write wait times typically indicate that the transaction log throughput is a bottleneck, often due to the log rate limit. Option A is wrong because high CPU utilization would cause other wait types like SOS_SCHEDULER_YIELD, not WRITELOG. Option B is wrong because long-running blocking transactions cause wait types like LCK_M_*, not increased log write wait time.

Option D is wrong because insufficient storage space for data files causes different symptoms, such as write errors or data file growth issues, but not specifically log write wait.

Full explanation →

219

Multi-Selectmedium

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

Select 2 answers

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

B.Assign a Microsoft Entra ID user or group as the Azure SQL Database admin

C.Set the database's authentication mode to 'Azure AD Only Authentication'

D.Deploy the database in Azure SQL Managed Instance

E.Register the application in Microsoft Entra ID

AnswersA, B

This is for granting access, not enabling authentication.

Why this answer

To enable Microsoft Entra ID authentication for an Azure SQL Database, you must first assign a Microsoft Entra ID user or group as the Azure SQL Database admin (Option B). After the admin is assigned, you create contained database users mapped to Microsoft Entra ID identities in the database (Option A) so those identities can authenticate. Setting the database's authentication mode to 'Azure AD Only Authentication' (Option C) is optional and enforces that only Microsoft Entra ID identities can connect; it is not required to enable Microsoft Entra ID authentication.

Azure SQL Managed Instance (Option D) and registering an application in Microsoft Entra ID (Option E) are not required.

Exam trap

Candidates often select only the admin assignment or mistakenly choose Azure AD Only mode. Enabling Microsoft Entra ID authentication requires both assigning an Entra ID admin and then creating contained database users mapped to Entra ID identities. Azure AD Only mode is an optional hardening step.

Full explanation →

220

MCQeasy

You are the database administrator for an Azure SQL Database that contains several tables with columns that store personally identifiable information (PII). The security team requires that these columns be identified and labeled as 'Confidential' in the database. You need to implement a solution that automatically classifies these columns based on their names and data patterns. What should you use?

A.SQL Data Discovery and Classification.

B.Always Encrypted.

C.Transparent Data Encryption (TDE).

D.Dynamic Data Masking (DDM).

AnswerA

SQL Data Discovery and Classification is a feature in Azure SQL Database that scans your database, identifies columns that may contain sensitive data based on names and data patterns, and allows you to apply classification labels such as 'Confidential'. It provides recommendations and can automatically classify columns, meeting the requirement to identify and label PII columns.

Why this answer

SQL Data Discovery and Classification is the built-in feature in Azure SQL Database that scans for potentially sensitive columns, such as those containing PII, and allows you to apply classification labels like 'Confidential'. It uses pattern matching and column names to provide recommendations, which can be reviewed and applied. This directly meets the requirement to identify and label PII columns.

Exam trap

The trap here is confusing data protection features like DDM or Always Encrypted with data discovery and classification, which is a separate capability for identifying and labeling sensitive data.

Full explanation →

221

Multi-Selectmedium

Which THREE components are required to set up elastic jobs in Azure SQL Database?

Select 3 answers

A.Azure Automation account

B.SQL Server Agent

C.Target groups

D.An elastic job agent

E.Job credentials

AnswersC, D, E

Target groups define the servers and databases that elastic jobs execute against, satisfying the requirement to specify which Azure SQL Database targets the job runs on. Without a target group, the job agent has no scope for execution, so this component is mandatory alongside the job agent and job credentials.

Why this answer

Target groups (C) are required because they define the set of databases (or elastic pools) against which an elastic job will execute. Without target groups, the job agent has no scope of execution. The elastic job agent (D) orchestrates job scheduling and execution, and job credentials (E) are needed to authenticate to the target databases for running T-SQL scripts.

Exam trap

The trap here is that candidates confuse SQL Server Agent (an on-premises/IaaS tool) with the cloud-native elastic job agent, or assume an Azure Automation account is needed for scheduling, when in fact elastic jobs have their own built-in scheduling and agent service.

Full explanation →

222

MCQmedium

You are responsible for automating index maintenance in Azure SQL Database. You need to ensure that index rebuilds and reorganizations are performed only when fragmentation exceeds 30% and 10%, respectively, and that the job runs weekly. Which approach should you use?

A.Create a SQL Agent job using T-SQL script with sys.dm_db_index_physical_stats.

B.Use Elastic Database Jobs to run a script that rebuilds/reorganizes indexes.

C.Schedule a maintenance task using SQL Server Agent in a VM running SQL Server.

D.Enable automatic index tuning in Azure SQL Database with the desired fragmentation thresholds.

AnswerB

Elastic Database Jobs run T-SQL against Azure SQL Database on a schedule, letting a script evaluate sys.dm_db_index_physical_stats and rebuild above 30% or reorganise above 10% fragmentation weekly. No SQL Agent exists in Azure SQL Database.

Why this answer

Elastic Database Jobs are the native Azure SQL Database mechanism for scheduling and executing T-SQL across one or more databases without requiring a separate VM or SQL Server Agent. The job can run a custom T-SQL script that queries sys.dm_db_index_physical_stats to evaluate fragmentation and conditionally issue ALTER INDEX ... REBUILD or REORGANIZE based on the 30% and 10% thresholds.

This satisfies both the automation and the weekly schedule requirements within the PaaS environment.

Exam trap

DP-300 often tests the misconception that SQL Server Agent is available in Azure SQL Database, or that automatic tuning can be customized with specific fragmentation thresholds; candidates must remember that Elastic Database Jobs are the correct PaaS-native scheduling solution.

How to eliminate wrong answers

Option A is wrong because SQL Agent is not available in Azure SQL Database (PaaS); it only exists in SQL Server on-premises or on Azure VMs. Option C is wrong because it introduces an IaaS VM with SQL Server Agent, which is unnecessary overhead and not the intended solution for Azure SQL Database. Option D is wrong because automatic index tuning in Azure SQL Database does not let you configure custom fragmentation thresholds for rebuild/reorganize actions; it uses its own internal logic and does not expose those knobs.

Full explanation →

223

Multi-Selecthard

Which TWO of the following are best practices for managing firewall rules for Azure SQL Database?

Select 2 answers

A.Use IP-based firewall rules for all client connections, including Azure services.

B.Create firewall rules with broad IP ranges (e.g., 0.0.0.0/0) to simplify management.

C.Use Azure Private Link to connect from Azure VNets instead of opening firewall rules to IP ranges.

D.Audit all firewall rule changes using Azure Activity Logs.

E.Enable the 'Allow Azure Services' firewall rule to allow connections from Azure services.

AnswersC, D

Azure Private Link provides a private IP endpoint within the VNet, eliminating public IP firewall rules entirely. This satisfies the best-practise requirement to avoid exposing the database to internet ranges, reducing attack surface while maintaining connectivity from Azure VNets.

Why this answer

Option C is correct because Azure Private Link (Private Endpoint) gives Azure SQL Database a private IP inside your VNet, so clients connect over the Microsoft backbone without any public IP firewall rules, which is the recommended way to restrict access from Azure VNets. Option D is correct because Azure Activity Logs record control-plane operations such as creating, updating, or deleting firewall rules, providing the audit trail needed to detect unauthorized or accidental rule changes. Options A and B are not best practices: IP-based rules for all clients, especially Azure services, expose the logical server to the public internet, and broad ranges like 0.0.0.0/0 defeat the purpose of a firewall.

Option E is not recommended because the 'Allow Azure Services' rule (0.0.0.0) permits connections from any Azure tenant, not just your own resources, so it should be avoided in favor of Private Link or specific IP rules.

Exam trap

The trap here is that candidates often think only one correct answer exists, but the question explicitly asks for TWO. Candidates may select Option E ('Allow Azure Services') thinking it is a best practice, but it is actually too broad and should be avoided. Auditing (Option D) is often overlooked as a management best practice, but it is essential for security governance.

Full explanation →

224

MCQmedium

You administer an Azure SQL Database that must remain available if the primary region becomes unavailable. The business requires a secondary region with read-only access for reporting, and during a failover the application connection string must not change. You configure an auto-failover group. Which feature of the auto-failover group satisfies the requirement that the application connection string remains unchanged after failover?

A.The geo-secondary database's server-level firewall rules.

B.The transparent data encryption (TDE) protector stored in Azure Key Vault.

C.The automatic backup retention policy of the primary database.

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

AnswerD

The read-write listener endpoint is a stable DNS name that always points to the current primary database. Applications connect to this listener instead of the server name, so after a geo-failover the same connection string continues to work without modification. This directly satisfies the requirement that the application connection string must not change during a regional failover.

Why this answer

An auto-failover group creates listener endpoints with stable DNS names. The read-write listener always resolves to the current primary, so applications using that listener do not need to change their connection strings after a geo-failover. Firewall rules, backup retention, and TDE protectors are important for security and recoverability but do not provide a stable connection endpoint.

Exam trap

The trap here is assuming that any failover group configuration automatically preserves the application connection string, when only the listener endpoint provides that stability.

Full explanation →

225

MCQmedium

Your company has an Azure SQL Database that stores sensitive customer data. You need to ensure that data is encrypted at rest and in transit. The database is currently using Transparent Data Encryption (TDE) with service-managed keys. Compliance requirements now mandate that you use customer-managed keys stored in Azure Key Vault. Additionally, all connections must use encrypted connections. What should you do?

A.Create a new Azure SQL Database with TDE enabled using a customer-managed key from Key Vault. Migrate data using SQL Server Management Studio (SSMS) with 'Encrypt connection' enabled.

B.Configure the Azure SQL Server to use a customer-managed key from Azure Key Vault for TDE and set 'Encrypted connection' to 'Required' on the server.

C.Enable Transparent Data Encryption (TDE) with service-managed keys and set 'Minimum TLS version' to 1.2.

D.Implement Always Encrypted with keys stored in Azure Key Vault and set 'Encrypted connection' to 'Required' on the server.

AnswerB

Configuring a customer-managed key in Azure Key Vault for TDE satisfies the compliance mandate for customer-controlled encryption at rest, while setting Encrypted connection to Required enforces encryption in transit for all connections to the Azure SQL server.

Why this answer

It directly addresses both requirements: using a customer-managed key from Azure Key Vault for TDE (which replaces the service-managed key) and enforcing encrypted connections by setting 'Encrypted connection' to 'Required' on the Azure SQL Server. This configuration ensures data at rest is encrypted with a key you control, and all client connections must use TLS encryption, meeting compliance mandates without requiring a new database or data migration.

Exam trap

The trap here is that candidates often confuse Always Encrypted with TDE, thinking column-level encryption satisfies the 'at rest' requirement for the entire database, or they assume that setting 'Minimum TLS version' alone ensures all connections are encrypted, when in fact 'Encrypted connection' must be explicitly set to 'Required' to reject unencrypted connections.

How to eliminate wrong answers

Option A is wrong because creating a new database and migrating data is unnecessary; you can change the TDE key type on the existing database by configuring the server to use a customer-managed key from Key Vault, and 'Encrypt connection' in SSMS only affects that specific migration session, not all future connections. Option C is wrong because it keeps service-managed keys for TDE, which does not satisfy the compliance requirement for customer-managed keys, and setting 'Minimum TLS version' to 1.2 only enforces a minimum protocol version but does not require encrypted connections for all clients. Option D is wrong because Always Encrypted protects data in use and in transit at the column level, but it does not encrypt the entire database at rest (TDE is needed for that), and it does not address the requirement to use customer-managed keys for TDE.

Full explanation →

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