Courseiva

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

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

Take a mock examExam hub
Page 12

Page 13 of 13

901
MCQmedium

Refer to the exhibit. You are reviewing the JSON configuration of an Azure SQL Database in the Business Critical tier. What is the primary benefit of setting highAvailabilityReplicaCount to 2?

A.It enables automatic failover in case of a node failure.
B.It enables zone redundancy for the database.
C.It ensures zero data loss during a failover.
D.It provides more read-only replicas to offload read workloads.
AnswerD

Additional replicas increase read capacity.

Why this answer

Option B is correct. Setting highAvailabilityReplicaCount to 2 in Business Critical tier creates two additional readable replicas (total of 3 replicas including primary). This improves read performance by allowing more read-only connections to be offloaded to replicas.

Option A (zero data loss) is achieved by synchronous replication, not replica count. Option C (automatic failover) is inherent to Business Critical. Option D (zone redundancy) is determined by the zoneRedundant property, not replica count.

Full explanation →
902
MCQhard

You have an Azure SQL Database in the General Purpose tier. You notice that the log write throughput is consistently above the service tier limit, causing transaction throttling. You need to resolve this without moving to Business Critical. What should you do?

A.Increase the max log size using ALTER DATABASE.
B.Batch transactions and reduce log writes.
C.Enable accelerated database recovery to reduce log I/O.
D.Move to Business Critical tier.
AnswerB

Batching reduces the number of log writes, staying within limits.

Why this answer

Option C is correct because reducing transaction log writes by batching transactions or using minimally logged operations reduces throughput. Option A is wrong because increasing max log size does not affect throughput. Option B is wrong because Premium tier is not allowed.

Option D is wrong because log rate is not configurable.

Full explanation →
903
MCQeasy

You need to automate the backup of an Azure SQL Managed Instance to a storage account every 4 hours. Which feature should you use?

A.Built-in automated backups
B.SQL Agent jobs
C.Long-term retention (LTR) policies
D.Azure Automation with PowerShell runbooks
AnswerD

Azure Automation runbooks can schedule and execute backup commands for Azure SQL Managed Instance.

Why this answer

Long-term retention (LTR) backups are for retaining backups beyond the automated backup period, not scheduling. Azure SQL Managed Instance automated backups are built-in and cannot be customized for frequency. Azure Automation with PowerShell runbooks can invoke backups using T-SQL or REST APIs.

SQL Agent jobs cannot run on managed instance as they are not available.

Full explanation →
904
Multi-Selectmedium

You are planning a migration of on-premises SQL Server databases to Azure SQL Managed Instance. Which TWO tools or services can you use to assess compatibility and identify potential migration blockers? (Select TWO.)

Select 2 answers
A.Data Migration Assistant (DMA)
B.SQL Server Management Studio (SSMS) – Upgrade Data Tier Application wizard
C.Azure Database Migration Service (DMS)
D.Azure Migrate
E.Azure Data Studio – Migration Wizard
AnswersA, D

DMA assesses SQL Server compatibility with Azure SQL Managed Instance.

Why this answer

Data Migration Assistant (DMA) is the correct tool because it performs a detailed assessment of on-premises SQL Server databases, identifying compatibility issues and migration blockers specifically for Azure SQL Managed Instance. It generates a report of feature parity, breaking changes, and behavior differences, enabling you to remediate issues before migration.

Exam trap

The trap here is confusing the assessment tool (DMA) with the migration execution tool (DMS) or assuming that SSMS or Azure Data Studio wizards provide the same depth of compatibility analysis, when in fact DMA is the dedicated assessment tool for Azure SQL Managed Instance migrations.

Full explanation →
905
Multi-Selecteasy

Which TWO of the following are valid methods to secure data in transit for Azure SQL Database?

Select 2 answers
A.Enable Always Encrypted on sensitive columns.
B.Deploy Azure Firewall in front of the database.
C.Configure the server to enforce TLS 1.2 by setting minimal TLS version.
D.Use an Azure Virtual Network (VNet) with a VPN gateway for client connections.
E.Apply a Network Security Group (NSG) to the subnet.
AnswersC, D

Enforcing TLS 1.2 ensures encryption in transit.

Why this answer

Enforcing TLS 1.2 and using an Azure VPN gateway are valid methods to secure data in transit. Column-level encryption (Always Encrypted) protects data in use, not in transit. Network security groups (NSGs) are for network layer filtering, not encryption.

Azure Firewall is a network security service, not specifically for data in transit encryption.

Full explanation →
906
MCQmedium

You are a database administrator for a large e-commerce platform using Azure SQL Database. The application experiences intermittent performance degradation during peak hours. Query Store data shows that a specific stored procedure, `usp_getProducts`, has multiple query variants with different execution plans. Some plans are suboptimal. You need to ensure consistent and optimal performance for this stored procedure without rewriting the application code. What should you do?

A.Enable forced parameterization on the database to promote plan reuse.
B.Create a plan guide to force a specific execution plan for the stored procedure.
C.Implement Query Store hints to force the last known good plan for the stored procedure.
D.Enable automatic tuning in Azure SQL Database and rely on the 'FORCE LAST GOOD PLAN' feature.
AnswerA

Forced parameterization helps reduce plan cache bloat and improves plan stability for queries with literals.

Why this answer

Option C is correct because enabling forced parameterization makes the database treat literal values as parameters, allowing query plans to be reused and reducing plan cache bloat. Option A is wrong because plan guides are for specific queries, not the entire stored procedure. Option B is wrong because it's an after-the-fact analysis tool.

Option D is wrong because index tuning is a reactive measure, not a proactive plan stability solution.

Full explanation →
907
MCQeasy

You have an Azure SQL Database that uses the Business Critical service tier with zone redundancy enabled. The database is deployed in a single region. You want to ensure that if a zone fails, the database remains available with minimal downtime. How does zone redundancy achieve this?

A.It maintains multiple synchronous replicas in different availability zones within the region.
B.It creates read-only replicas in other zones that can be promoted to primary.
C.It replicates data to a secondary region for failover.
D.It uses asynchronous replication to a standby replica in another zone.
AnswerA

Zone redundancy uses synchronous replicas across zones for automatic failover.

Why this answer

Option B is correct. Zone redundancy for Business Critical creates multiple replicas across different availability zones within the same region. If one zone fails, one of the other replicas becomes the primary automatically.

Option A is wrong because zone redundancy does not involve a secondary region. Option C is wrong because the replicas are kept in sync synchronously, not asynchronously. Option D is wrong because the replicas are within the same region.

Full explanation →
908
MCQmedium

Refer to the exhibit. An Azure SQL Database is receiving Intelligent Insights degradation alerts. Which action should be taken first?

A.Increase the maximum storage size
B.Change the service tier to BusinessCritical
C.Scale up to Standard S3 (100 DTU)
D.Implement automatic tuning recommendations
AnswerD

Automatic tuning can optimize queries to reduce resource usage.

Why this answer

The database is hitting DTU limits (100% CPU and data IO). The alert recommends scaling up or optimizing queries. Since CPU and data IO are both high, query optimization is the better first step to avoid cost increase.

Full explanation →
909
Multi-Selectmedium

You have an Azure SQL Database that uses active geo-replication between two regions. You need to ensure minimal data loss during an unplanned failover while maintaining the current configuration. Which THREE settings should you verify? (Choose three.)

Select 3 answers
A.The replication mode is 'async' (asynchronous) with a low RPO target.
B.The seed type is set to 'automatic' to ensure continuous replication.
C.The secondary database is in a paired region with low network latency.
D.The secondary database is configured as 'readable' to avoid lag.
E.The failover group is named with a 'forced' failover policy.
AnswersA, B, C

Async replication is standard; RPO is typically below 5 seconds.

Why this answer

Options A, C, and D are correct. The seed type determines initial synchronization. The secondary type (readable/non-readable) affects performance but not data loss.

The failover group name is for management. Option B is wrong because secondary type does not affect data loss. Option E is wrong because it's part of the failover group name.

Full explanation →
910
Multi-Selecthard

Which THREE security features are available in Azure SQL Database to protect data at rest?

Select 3 answers
A.Transparent Data Encryption (TDE) with customer-managed keys
B.Dynamic Data Masking
C.Transparent Data Encryption (TDE) with service-managed keys
D.Row-Level Security
E.Always Encrypted with secure enclaves
AnswersA, C, E

Encrypts data at rest with customer-controlled keys.

Why this answer

Transparent Data Encryption (TDE) with customer-managed keys (Option A) protects data at rest by encrypting the database files and backups using a key stored in Azure Key Vault, giving the customer full control over key rotation and revocation. TDE with service-managed keys (Option C) also encrypts data at rest but uses a key managed by Azure, which is simpler but offers less control. Always Encrypted with secure enclaves (Option E) protects sensitive data at rest by encrypting columns with keys that are never revealed to the database engine, and the secure enclave allows computations on encrypted data, ensuring data remains encrypted even during processing.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking or Row-Level Security with encryption at rest, but these features do not encrypt stored data; they only control data visibility or output masking, which is a common misconception tested in DP-300.

Full explanation →
911
MCQmedium

You are configuring Azure SQL Database for an e-commerce application that experiences variable traffic. You need to ensure that the database can automatically scale resources based on demand without manual intervention. The solution must also support scaling to zero compute when not in use to save costs. Which Azure SQL Database offering should you use?

A.Azure SQL Database serverless.
B.Azure SQL Database elastic pool.
C.Azure SQL Database Hyperscale.
D.Azure SQL Database Business Critical tier.
AnswerA

Serverless auto-scales and pauses to zero compute during inactivity.

Why this answer

Azure SQL Database serverless is the correct choice because it automatically scales compute resources based on demand and supports pausing the database when idle, effectively scaling to zero compute to save costs. This aligns perfectly with the requirements of variable traffic and cost optimization without manual intervention.

Exam trap

The trap here is that candidates may confuse the auto-scaling of serverless with the resource pooling of elastic pools, or assume Hyperscale's high performance includes cost-saving idle scaling, but only serverless offers the specific 'scale to zero' capability.

How to eliminate wrong answers

Option B is wrong because Azure SQL Database elastic pool provides resource sharing among multiple databases but does not support scaling to zero compute; it maintains a minimum resource allocation. Option C is wrong because Azure SQL Database Hyperscale is designed for large databases with high storage and throughput needs, not for automatic scaling to zero compute or cost savings through pausing. Option D is wrong because the Business Critical tier offers high availability and performance with fixed compute resources, lacking the ability to scale to zero or pause automatically.

Full explanation →
912
MCQhard

You are using Azure SQL Managed Instance with auto-failover groups. The primary region is East US, secondary is West US. The primary region experiences a full outage. How does the failover group behave?

A.The failover group automatically fails over to West US and the read-write endpoint now points to West US.
B.You must manually initiate failover via the portal.
C.The read-only listener continues to point to West US.
D.The failover group remains in primary region and does not fail over.
AnswerA

Automatic failover switches endpoints.

Why this answer

Option D is correct because auto-failover groups automatically fail over to the secondary region with automatic read-write endpoint switch. Option A is incorrect because failover is automatic if the grace period is exceeded. Option B is incorrect because automatic failover does not require manual initiation.

Option C is incorrect because read-only listeners are also updated automatically.

Full explanation →
913
MCQmedium

A company uses Azure SQL Database for a critical application. They need to automate the process of exporting a database to a storage account every night, ensuring the export is consistent. The solution must minimize administrative overhead. What should they use?

A.Create an Azure Automation runbook that uses the Export-AzureRmSqlDatabase cmdlet and schedule it to run nightly.
B.Create an Elastic Database Job that runs a T-SQL script to export the database.
C.Deploy an Azure Data Factory pipeline with a Copy activity to export the database.
D.Use an Azure Logic App with the SQL Server connector to export the database.
AnswerA

Azure Automation can run PowerShell scripts on a schedule; Export-AzureRmSqlDatabase exports the database as a BACPAC to Azure Storage.

Why this answer

Option A is correct because Azure Automation runbooks can execute PowerShell cmdlets like Export-AzureRmSqlDatabase (or the newer Export-AzSqlDatabase) to perform a consistent export of an Azure SQL Database to a storage account. By scheduling the runbook to run nightly, you automate the export with minimal administrative overhead, as the export operation uses database snapshots to ensure consistency without requiring complex orchestration.

Exam trap

The trap here is that candidates often overcomplicate the solution by choosing Azure Data Factory or Logic Apps, thinking they need a full ETL tool, when a simple scheduled PowerShell runbook is the most direct and low-overhead method for a consistent database export to storage.

How to eliminate wrong answers

Option B is wrong because Elastic Database Jobs are designed for executing T-SQL scripts across multiple databases (e.g., schema changes, index maintenance), not for exporting a database to a storage account; they lack native support for storage account interactions. Option C is wrong because Azure Data Factory pipelines with Copy activity can export data, but they require building a full pipeline with linked services and datasets, which introduces more administrative overhead than a simple scheduled runbook for a straightforward export task. Option D is wrong because Azure Logic Apps with the SQL Server connector can perform operations like querying or inserting data, but they do not support exporting an entire database to a storage account as a consistent backup; the connector lacks the necessary export functionality.

Full explanation →
914
MCQmedium

You have an Azure SQL Database that is accessed by a web application. The application uses a service principal (Microsoft Entra application) to connect. You need to rotate the client secret for the service principal without downtime. What should you do?

A.Store the client secret in Azure Key Vault, configure the application to retrieve the secret from Key Vault, and implement a secret rotation policy.
B.Create a new client secret, update the application configuration, and then delete the old secret.
C.Switch to using a client certificate instead of a client secret.
D.Use a system-assigned managed identity for the web application.
AnswerA

Key Vault enables seamless rotation without application changes.

Why this answer

Option A is correct because it enables zero-downtime secret rotation by decoupling the application from a single secret value. By storing the client secret in Azure Key Vault and configuring the application to retrieve it dynamically, you can rotate the secret in Key Vault without changing the application's configuration or restarting it. The application always fetches the latest version of the secret, so rotation is transparent and does not interrupt connectivity.

Exam trap

The trap here is that candidates assume updating the application configuration directly (Option B) is sufficient, but they overlook the risk of downtime during the configuration change window and the lack of atomicity in secret replacement.

How to eliminate wrong answers

Option B is wrong because updating the application configuration to use a new secret and then deleting the old one introduces a window where the application might be using a stale cached secret or fail to connect if the configuration change is not atomic. Option C is wrong because switching to a client certificate does not inherently eliminate downtime; certificate rotation still requires careful planning and could cause outages if not managed properly. Option D is wrong because a system-assigned managed identity cannot be used for an Azure SQL Database that requires a service principal (Microsoft Entra application) connection; managed identities are tied to Azure resources and do not support the same authentication flow as a service principal with a client secret.

Full explanation →
915
MCQhard

Your Azure SQL Database is running in the General Purpose tier. You notice that read queries are experiencing high latency. You need to improve read performance without changing the compute size. What should you implement?

A.Upgrade to the Hyperscale service tier
B.Enable read scale-out
C.Create columnstore indexes on the tables
D.Use in-memory OLTP for the tables
AnswerB

Read scale-out directs read-only queries to a readable secondary, reducing load on the primary.

Why this answer

Read scale-out in Azure SQL Database allows you to offload read-only queries to a secondary replica, reducing latency on the primary replica without changing the compute size. This feature is available in the Premium and Business Critical service tiers, but not in General Purpose. Since the question specifies the General Purpose tier, enabling read scale-out is not actually supported, making this a trick scenario.

However, among the given options, enabling read scale-out is the only one that directly addresses read latency by leveraging a read-only replica, even though it requires a tier change to Premium or Business Critical.

Exam trap

The trap here is that candidates may assume read scale-out is available in all service tiers, but it is only supported in Premium and Business Critical, not in General Purpose, making the correct answer technically unfeasible under the given constraints.

How to eliminate wrong answers

Option A is wrong because upgrading to the Hyperscale service tier changes the service tier, which violates the constraint of not changing the compute size; Hyperscale also uses a different architecture that may not directly improve read latency without additional configuration. Option C is wrong because creating columnstore indexes improves performance for analytical and aggregation queries, not for general read queries experiencing high latency; they are not a direct solution for read latency on transactional workloads. Option D is wrong because in-memory OLTP is designed to accelerate transaction processing and reduce latency for write-heavy workloads, not to improve read query performance; it also requires specific table and stored procedure modifications.

Full explanation →
916
MCQhard

You are designing a disaster recovery strategy for an Azure SQL Database that uses the Business Critical tier. The primary region is East US, and you need to ensure automatic failover with zero data loss in case of a regional outage. The secondary region must be West US. You also need to minimize latency for read-only workloads in the secondary region. What should you implement?

A.Create a failover group with the primary in East US and a readable secondary in West US.
B.Create a failover group with the primary in East US and a non-readable secondary in West US using Basic tier.
C.Configure active geo-replication with a readable secondary in West US.
D.Configure active geo-replication with a readable secondary in West US using Standard tier.
AnswerA

Failover group provides automatic failover and readable secondary with synchronous replication for zero data loss.

Why this answer

A failover group with a readable secondary in the Business Critical tier provides automatic failover with zero data loss because it uses synchronous replication. The readable secondary in West US minimizes latency for read-only workloads by allowing direct connections to the secondary replica. This meets all requirements: automatic failover, zero data loss, and low-latency reads in the secondary region.

Exam trap

The trap here is that candidates often confuse active geo-replication (asynchronous, manual failover) with failover groups (synchronous for Business Critical, automatic failover), and assume any readable secondary guarantees zero data loss regardless of replication mode.

How to eliminate wrong answers

Option B is wrong because a Basic tier secondary cannot be used in a failover group; failover groups require the same service tier as the primary, and Basic tier does not support readable secondaries or synchronous replication. Option C is wrong because active geo-replication does not provide automatic failover; it requires manual initiation or custom scripting, and it does not guarantee zero data loss as it uses asynchronous replication. Option D is wrong because active geo-replication with Standard tier uses asynchronous replication, which cannot guarantee zero data loss, and it also lacks automatic failover capability.

Full explanation →
917
MCQeasy

Your organization requires that all changes to sensitive data in an Azure SQL Database be logged for compliance. You need to capture who changed what data and when, and store the logs in a Log Analytics workspace for analysis. What should you configure?

A.Enable change tracking on the database.
B.Enable Microsoft Defender for Cloud on the server.
C.Configure server-level auditing to send logs to a Log Analytics workspace.
D.Enable Transparent Data Encryption (TDE) with customer-managed keys.
AnswerC

Auditing captures detailed information about database events, including who made changes, and can be sent to Log Analytics.

Why this answer

Server-level auditing in Azure SQL Database can be configured to send audit logs directly to a Log Analytics workspace, capturing detailed information about data changes including who made the change, what was changed, and when. This meets the compliance requirement for logging sensitive data changes and enables analysis using Log Analytics queries.

Exam trap

The trap here is that candidates confuse change tracking (which only detects row changes) with auditing (which captures who, what, and when), or they think security tools like Defender for Cloud provide granular data change logging.

How to eliminate wrong answers

Option A is wrong because change tracking only identifies which rows changed and the fact of a change, but does not capture who made the change or the old/new values, and it does not send logs to Log Analytics. Option B is wrong because Microsoft Defender for Cloud provides security alerts and vulnerability assessments, not granular data change auditing with user identity and timestamp logging. Option D is wrong because Transparent Data Encryption (TDE) with customer-managed keys encrypts data at rest but does not log data changes or provide audit trails.

Full explanation →
918
Multi-Selecthard

You are designing an Azure SQL Database solution for a SaaS application that will host multiple tenants. Each tenant has a dedicated database. You need to ensure that tenants are isolated and resource usage is optimized. Which two features should you use? (Choose TWO.)

Select 2 answers
A.Serverless compute tier
B.DTU-based purchasing model
C.Elastic pool
D.Hyperscale service tier
E.vCore-based purchasing model
AnswersB, C

DTU pools are cost-effective for multi-tenant.

Why this answer

Option B (DTU-based purchasing model) is correct because it provides a simple, pre-configured bundle of compute, storage, and I/O resources, which is ideal for SaaS environments with multiple dedicated databases where predictable performance and isolation are needed. Option C (Elastic pool) is correct because it allows multiple databases to share a fixed pool of DTU resources, optimizing cost and resource utilization across tenants while maintaining logical isolation.

Exam trap

The trap here is that candidates often confuse the vCore-based model with better isolation, but for multi-tenant SaaS with dedicated databases, DTU-based elastic pools provide the optimal balance of isolation and resource optimization.

Full explanation →
919
MCQeasy

You are planning a disaster recovery strategy for an Azure SQL Database that supports a critical application. The database is 500 GB in size and you need to recover it within 1 hour (RTO) with a maximum data loss of 5 minutes (RPO). Which Azure SQL Database feature should you use?

A.Long-term retention (LTR) backups.
B.Point-in-time restore (PITR) with geo-redundant backup storage.
C.Auto-failover group with a secondary in a different region.
D.Active geo-replication to a secondary server in a paired region.
AnswerD

Active geo-replication offers low RPO (seconds to minutes) and fast failover, meeting the requirements.

Why this answer

Option D is correct. Active geo-replication provides asynchronous replication with an RPO of a few seconds to a few minutes, and failover can be initiated manually within minutes, meeting the RTO and RPO. Option A is wrong because point-in-time restore does not provide a separate secondary region.

Option B is wrong because long-term retention is for archival, not DR. Option C is wrong because failover groups use geo-replication but the RPO is still asynchronous; the question asks for the feature, and geo-replication is the core.

Full explanation →
920
MCQhard

You are reviewing an Azure RBAC role assignment for an Azure SQL Database. The role assignment shown in the exhibit is intended to allow a user to read data from the database. However, the user reports they cannot connect to the database. What is the most likely reason?

A.The RBAC role does not grant data plane access; the user must be mapped to a database user and granted database-level permissions.
B.The principal is incorrectly specified; it should be a security group.
C.The scope is too broad; it should be at the server level.
D.The action 'Microsoft.Sql/servers/databases/read' is not valid; it should be 'Microsoft.Sql/servers/databases/dataReader'.
AnswerA

RBAC roles like Contributor or Reader only grant control plane access. Data plane access requires database user mapping and permissions.

Why this answer

Azure RBAC roles control management plane operations (e.g., creating or deleting resources) but do not grant access to the data plane (e.g., reading or writing data in a database). To read data from an Azure SQL Database, the user must be mapped to a database user (via a contained database user or an Azure AD user) and granted database-level permissions such as db_datareader. The RBAC role assignment shown only provides the 'Microsoft.Sql/servers/databases/read' action, which allows reading database metadata (like tags or properties) but not connecting to the database or querying tables.

Exam trap

The trap here is that candidates confuse Azure RBAC roles (management plane) with SQL database-level permissions (data plane), assuming that a role with 'read' in the name allows reading data from tables.

How to eliminate wrong answers

Option B is wrong because the principal type (user, group, or service principal) does not affect data plane access; the core issue is that RBAC does not grant data plane permissions at all. Option C is wrong because expanding the scope to the server level still only grants management plane actions (e.g., listing databases) and does not enable database connectivity or data reading. Option D is wrong because 'Microsoft.Sql/servers/databases/dataReader' is not a valid RBAC action; RBAC actions are management plane operations, and data reader access is granted via SQL-level permissions (e.g., db_datareader role) or Azure AD authentication with contained database users.

Full explanation →
921
Multi-Selectmedium

Your organization uses Azure SQL Managed Instance and needs to implement a defense-in-depth strategy. Which THREE security controls should you implement? (Choose three.)

Select 3 answers
A.Enable advanced threat protection using Microsoft Defender for Cloud.
B.Implement server-level auditing to capture database events.
C.Create columnstore indexes on large tables to improve query performance.
D.Configure network security groups (NSGs) on the subnet to restrict inbound traffic to the managed instance.
E.Create application roles in each database to manage permissions.
AnswersA, B, D

Defender for Cloud provides threat detection and alerts for suspicious activities.

Why this answer

Option A is correct because enabling advanced threat protection via Microsoft Defender for Cloud provides continuous monitoring for suspicious activities, such as SQL injection or brute-force attacks, and generates security alerts that integrate with Azure Sentinel or SIEM solutions. This is a critical layer in a defense-in-depth strategy, as it detects and responds to threats at the database level.

Exam trap

The trap here is that candidates often confuse performance tuning features (like columnstore indexes) or routine permission management (like application roles) with distinct security controls, failing to recognize that defense-in-depth requires separate, layered protections across network, monitoring, and auditing domains.

Full explanation →
922
MCQeasy

You need to monitor the storage space usage of an Azure SQL Database over time. Which tool should you use?

A.Intelligent Insights
B.Azure SQL Analytics (Azure Monitor)
C.Query Store
D.SQL Server Management Studio (SSMS)
AnswerB

Provides historical metrics including storage.

Why this answer

Option B is correct because Azure SQL Analytics in Azure Monitor provides historical storage metrics. Option A is wrong because Query Store focuses on query performance. Option C is wrong because Intelligent Insights is for proactive diagnostics, not storage monitoring.

Option D is wrong because SSMS does not provide historical monitoring.

Full explanation →
923
MCQmedium

Your Azure SQL Database uses Always Encrypted to protect sensitive columns. You need to allow a reporting application to query encrypted columns without having access to the column encryption key. What should you configure?

A.Rotate the column master key to a hardware security module (HSM).
B.Remove the Always Encrypted configuration from the columns.
C.Enable Always Encrypted with secure enclaves (Intel SGX) and create an enclave-enabled key.
D.Provide the application with the column master key and column encryption key.
AnswerC

Enclaves allow computations on encrypted data within the enclave.

Why this answer

Option C is correct because enabling Always Encrypted with secure enclaves (Intel SGX) allows the reporting application to perform computations on encrypted columns (e.g., equality, pattern matching) without ever exposing the column encryption key to the application. The enclave decrypts the data inside a trusted execution environment, so the application only sees the results of queries, not the raw encryption keys. This meets the requirement of querying encrypted columns without key access.

Exam trap

The trap here is that candidates may think rotating the master key to an HSM (Option A) solves the key access problem, but HSMs only protect the master key storage, not the application's need for the column encryption key to decrypt data.

How to eliminate wrong answers

Option A is wrong because rotating the column master key to an HSM does not change the application's need for the column encryption key; it only changes where the master key is stored, not the access model. Option B is wrong because removing Always Encrypted entirely would expose sensitive data in plaintext, violating the security requirement. Option D is wrong because providing the application with both the column master key and column encryption key would grant it full decryption capability, which directly contradicts the requirement to prevent key access.

Full explanation →
924
MCQmedium

You need to ensure high availability for an Azure SQL Database in the Business Critical service tier. Which feature provides the fastest failover within the same region?

A.Built-in Always On availability groups
B.Zone-redundant configuration
C.Read scale-out
D.Active geo-replication
AnswerA

Provides synchronous replication and automatic failover.

Why this answer

Option B is correct because Business Critical tier uses Always On availability groups with synchronous replication, providing fast automatic failover within the region. Option A is incorrect because zone-redundant configuration adds zone resilience but not faster failover within a zone. Option C is incorrect because read scale-out does not provide failover.

Option D is incorrect because geo-replication is for cross-region failover.

Full explanation →
925
MCQhard

Your organization runs a critical application on Azure SQL Managed Instance. You notice that the `tempdb` database is experiencing contention, leading to PAGELATCH_EX waits. The managed instance has 16 vCores and is using the General Purpose service tier. You need to reduce `tempdb` contention without altering the application code or changing the service tier. What should you do?

A.Enable memory-optimized tempdb metadata to reduce allocation contention.
B.Add 4 additional tempdb data files of equal size.
C.Add 16 tempdb data files of equal size, matching the number of vCores.
D.Enable accelerated database recovery to reduce transaction log overhead.
AnswerC

Distributing tempdb data across multiple files reduces allocation contention; best practice is to have one file per vCore.

Why this answer

Option D is correct because increasing the number of tempdb data files to match the number of vCores (or slightly higher) reduces allocation contention. Option A is wrong because memory-optimized tempdb metadata is for system metadata, not data pages. Option B is wrong because it only adds more files, but optimal number is vCores.

Option C is wrong because accelerated database recovery does not address tempdb contention.

Full explanation →
926
Multi-Selectmedium

You are designing high availability for a critical Azure SQL Database. The database is 1 TB in size and requires an RPO of 0 (zero data loss) and an RTO of less than 30 seconds. Which TWO configurations can meet these requirements?

Select 2 answers
A.Business Critical service tier with zone redundancy enabled.
B.Auto-failover group with a secondary in the same region.
C.Hyperscale service tier with zone redundancy enabled.
D.General Purpose service tier with zone redundancy enabled.
E.Active geo-replication to a secondary in a different region.
AnswersA, C

Business Critical with zone redundancy uses three synchronous replicas across zones, ensuring zero data loss and fast failover.

Why this answer

Options A and D are correct. For zero data loss, you need synchronous replication. Business Critical with zone redundancy provides synchronous replicas within the same region with automatic failover.

Hyperscale with zone redundancy also provides synchronous replicas for the log service. Option B (General Purpose) uses asynchronous replication and does not guarantee zero data loss. Option C (active geo-replication) is asynchronous, so data loss can occur.

Option E (failover groups) relies on geo-replication which is asynchronous.

Full explanation →
927
MCQeasy

You are automating the creation of an Azure SQL database. You need to ensure that the deployment is idempotent using Azure Resource Manager (ARM) templates. Which deployment mode should you use?

A.Complete
B.Automatic
C.Incremental
D.Validate
AnswerC

Incremental mode updates resources in the resource group based on the template, making it idempotent.

Why this answer

Option B is correct because 'Incremental' mode processes the template as an incremental update to the resource group, making it idempotent. 'Complete' mode deletes resources not in the template, which is not idempotent. 'Validate' mode only validates without deploying, and 'Automatic' is not a valid ARM mode.

Full explanation →
928
Multi-Selecteasy

Which TWO are benefits of using a failover group for Azure SQL Database? (Select two.)

Select 2 answers
A.Allows the secondary database to be readable for reporting
B.Enables transparent data encryption (TDE) across regions
C.Provides a single read/write listener endpoint for the primary database
D.Automatically balances read queries between primary and secondary
E.Supports synchronous replication between primary and secondary
AnswersA, C

You can configure the secondary to be readable.

Why this answer

Options A and D are correct. Failover groups provide a single endpoint for read/write and allow a readable secondary. Option B is incorrect because it does not provide built-in load balancing.

Option C is incorrect because TDE is not related to failover groups. Option E is incorrect because failover groups do not provide synchronous replication; they use asynchronous.

Full explanation →
929
MCQeasy

You are troubleshooting a performance issue in Azure SQL Database. You suspect that parameter sniffing is causing suboptimal query plans. What feature can you use to mitigate this without code changes?

A.Reorganize indexes regularly.
B.Use Query Store to force a good plan.
C.Add OPTIMIZE FOR UNKNOWN query hint to all queries.
D.Set the database to FORCE_PARAMETERIZATION = ON.
AnswerD

Forces parameterization to reduce sniffing effects.

Why this answer

Option A is correct because the FORCE_PARAMETERIZATION database option forces parameterization and can help with sniffing issues. Option B is wrong because it's for plan forcing after regression, not prevention. Option C is wrong because it's a query hint, not a database-wide setting.

Option D is wrong because it's for index maintenance.

Full explanation →
930
MCQeasy

You run the Azure CLI command shown in the exhibit for an Azure SQL Database named SalesDB. The output shows that the security alert policy is disabled. You need to enable Microsoft Defender for SQL, including vulnerability assessments, for this database. What should you do?

A.Run 'az sql db update --name SalesDB --resource-group rg-sales --server sql-sales --enable-defender-for-sql true'.
B.Run 'az sql db threat-policy update --name SalesDB --resource-group rg-sales --server sql-sales --state Enabled'.
C.Run 'az sql server update --name sql-sales --resource-group rg-sales --enable-defender-for-sql true'.
D.Configure a server firewall rule to allow Azure services.
AnswerC

Enables Defender for SQL at the server level.

Why this answer

Option C is correct because enabling Microsoft Defender for SQL, including vulnerability assessments, is a server-level configuration in Azure SQL. The command 'az sql server update --enable-defender-for-sql true' activates Defender for SQL on the server, which automatically applies to all databases on that server, including SalesDB. The exhibit shows a database-level security alert policy is disabled, but the required feature is enabled at the server scope, not per-database.

Exam trap

The trap here is that candidates confuse the database-level threat detection policy (legacy) with the server-level Microsoft Defender for SQL (modern), and mistakenly try to enable it per database using 'az sql db' commands instead of the correct server-level command.

How to eliminate wrong answers

Option A is wrong because 'az sql db update' does not support the '--enable-defender-for-sql' parameter; this parameter is only available at the server level. Option B is wrong because 'az sql db threat-policy update' configures the legacy threat detection policy, not Microsoft Defender for SQL, which is a newer, more comprehensive security package that includes vulnerability assessments. Option D is wrong because configuring a server firewall rule to allow Azure services only controls network access, not the security alert or vulnerability assessment features.

Full explanation →
931
MCQhard

You are the database administrator for a large e-commerce company that uses Azure SQL Database for its transactional systems. The environment consists of 100 databases spread across 10 elastic pools in different regions. You need to implement an automated solution to perform the following tasks every night: (1) Run integrity checks (DBCC CHECKDB) on all databases, (2) Rebuild indexes with fragmentation > 30%, (3) Update statistics with full scan for databases that have had significant data changes (>20% of rows). The solution must minimize manual intervention, provide centralized logging, and be resilient to failures (e.g., if one database fails, the others should continue). Which approach should you use?

A.Create an Elastic Database Job with step scripts for each maintenance task, targeting all databases, and configure retry logic.
B.Create a SQL Agent job on each server to run a maintenance script.
C.Use Azure Data Factory pipelines with a ForEach activity to execute stored procedures.
D.Use Azure Automation runbooks with Invoke-SqlCmd to loop through each database.
AnswerA

Elastic Jobs are designed for multi-database maintenance with resilience.

Why this answer

Option B is correct because Elastic Database Jobs can run T-SQL scripts across multiple databases with built-in retry and error handling, and can be scheduled. Option A (SQL Agent) is not available on Azure SQL Database. Option C (Azure Automation) requires custom error handling.

Option D (Azure Data Factory) is not designed for database maintenance tasks.

Full explanation →
932
MCQeasy

Your organization uses Azure SQL Database and wants to restrict access to only specific Azure services and on-premises IP addresses. The database has a public endpoint. Which two security features should you configure?

A.Enable 'Allow Azure services and resources to access this server' in the firewall settings.
B.Enable Always Encrypted with secure enclaves.
C.Set firewall rules to allow specific on-premises IP ranges.
D.Create a virtual network service endpoint for SQL.
E.Configure a private endpoint for the database.
AnswerA, C

This allows other Azure services to connect.

Why this answer

Option A is correct because enabling 'Allow Azure services and resources to access this server' in the Azure SQL Database firewall settings permits traffic from all Azure datacenters, effectively allowing other Azure services (e.g., Azure App Service, Azure Functions) to connect to the database. Option C is correct because configuring firewall rules to allow specific on-premises IP ranges restricts inbound traffic to only those IP addresses, meeting the requirement to allow on-premises access while blocking others.

Exam trap

The trap here is that candidates often confuse network-level access controls (firewall rules and service endpoints) with encryption features (Always Encrypted) or private connectivity (private endpoints), mistakenly thinking they restrict access when they actually address different security aspects like data protection or network isolation.

How to eliminate wrong answers

Option B is wrong because Always Encrypted with secure enclaves is a data encryption feature that protects sensitive data at rest and in use, but it does not control network-level access or firewall rules; it addresses data confidentiality, not connectivity restrictions. Option D is wrong because creating a virtual network service endpoint for SQL allows traffic from a specific Azure virtual network to bypass the public endpoint, but it does not restrict access to only specific Azure services and on-premises IPs; it requires additional network rules and does not inherently block all other traffic. Option E is wrong because configuring a private endpoint for the database provides a private IP address within a virtual network, eliminating public endpoint exposure, but it does not allow on-premises IP access unless combined with a VPN or ExpressRoute; it also does not selectively permit specific Azure services without additional configuration.

Full explanation →
933
Multi-Selectmedium

You are planning a disaster recovery solution for an Azure SQL Managed Instance. The solution must meet the following requirements: provide automatic failover to a secondary region, support read-only workloads on the secondary, and allow manual failback. Which TWO features should you include? (Choose two.)

Select 2 answers
A.Active geo-replication
B.Geo-restore
C.Failover group
D.Manual failover
E.Log shipping
AnswersC, D

Failover groups provide automatic failover and readable secondary.

Why this answer

Options A and D are correct. Failover groups provide automatic failover and support readable secondary. Manual failback is possible by initiating a failover in the opposite direction.

Option B (active geo-replication) is not for managed instances. Option C (geo-restore) does not provide automatic failover. Option E (log shipping) is not built-in.

Full explanation →
934
MCQeasy

You manage an Azure SQL Database that experiences periodic performance degradation. You need to identify the top queries by CPU consumption over the last hour. Which dynamic management view should you query?

A.sys.dm_exec_sessions
B.sys.dm_exec_query_plan
C.sys.dm_exec_query_stats
D.sys.dm_exec_requests
AnswerC

Provides aggregated CPU statistics for cached query plans.

Why this answer

Option A is correct because sys.dm_exec_query_stats provides aggregated performance statistics for cached query plans, including CPU time. Option B is wrong because sys.dm_exec_sessions shows active sessions but not query-level CPU. Option C is wrong because sys.dm_exec_requests shows currently executing requests, not historical data.

Option D is wrong because sys.dm_exec_query_plan shows the actual plan, not CPU stats.

Full explanation →
935
MCQmedium

Your company uses Azure SQL Database and needs to restrict access to a specific column containing credit card numbers. Only users with the 'CreditCardViewer' role should see the full number; others should see only the last four digits. Which feature should you implement?

A.Always Encrypted
B.Row-Level Security
C.Column-level security with GRANT
D.Dynamic Data Masking
AnswerD

Masks sensitive data in query results for unauthorized users.

Why this answer

Dynamic Data Masking (DDM) is the correct choice because it allows you to obfuscate sensitive data in query results without changing the underlying database. You can define a mask on the credit card column that shows only the last four digits to users without the 'CreditCardViewer' role, while users with that role can be granted the UNMASK permission to see the full value.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking with Column-Level Security (GRANT), not realizing that GRANT cannot partially reveal data—it only provides all-or-nothing column access, whereas DDM is designed specifically for partial obfuscation based on permissions.

How to eliminate wrong answers

Option A is wrong because Always Encrypt encrypts data at the client side, preventing the database engine from seeing plaintext values, which would block the ability to selectively show the last four digits based on a database role. Option B is wrong because Row-Level Security controls access to entire rows based on a predicate function, not to individual columns or partial data within a column. Option C is wrong because column-level security with GRANT can restrict access to an entire column, but it cannot partially mask the data—it either allows full visibility or no visibility, not a masked view showing only the last four digits.

Full explanation →
936
MCQmedium

You are designing a data archiving solution for an Azure SQL Database. The database contains a table with over 100 million rows of historical sales data that is rarely accessed. You need to reduce storage costs while keeping the data available for occasional queries. What should you do?

A.Partition the table by date and compress each partition
B.Use page compression on the table
C.Delete old data and store it in Azure Blob Storage as CSV files
D.Use Stretch Database to migrate cold data to Azure Blob Storage
AnswerD

Stretch Database moves cold data to Azure Blob Storage transparently, reducing storage costs while keeping data queryable via SQL Server.

Why this answer

Stretch Database (now deprecated but still tested in DP-300) transparently migrates cold data from an Azure SQL Database table to Azure Blob Storage while keeping the data online and queryable via T-SQL. This reduces storage costs for rarely accessed historical data without requiring application changes, as the database engine automatically routes queries to the appropriate storage tier.

Exam trap

The trap here is that candidates often choose partitioning or compression (A or B) because they reduce storage size, but they fail to recognize that the question explicitly asks for cost reduction by moving data to cheaper storage, not just shrinking it within the same expensive tier.

How to eliminate wrong answers

Option A is wrong because partitioning by date and compressing each partition reduces storage size but does not offload data to cheaper storage; the data remains in the premium-priced SQL Database storage. Option B is wrong because page compression only reduces the storage footprint within the database, not moving data to a lower-cost tier like Blob Storage. Option C is wrong because deleting data and storing it as CSV files in Blob Storage breaks the ability to query the data with T-SQL without manual import, and it loses relational integrity and indexing.

Full explanation →
937
MCQmedium

Your Azure SQL Database is experiencing a sudden increase in wait time due to PAGEIOLATCH_SH waits. What should you do to reduce these waits?

A.Increase the database max memory
B.Add appropriate indexes to reduce table scans
C.Enable page compression on large tables
D.Force parameterization of queries
AnswerB

Adding indexes reduces the number of pages read, directly reducing PAGEIOLATCH_SH waits.

Why this answer

Option D is correct because PAGEIOLATCH_SH waits indicate I/O bottlenecks; adding indexes can improve data access patterns and reduce I/O. Option A is wrong because increasing memory will not directly reduce I/O waits if the issue is inefficient queries. Option B is wrong because enabling compression may increase CPU but not necessarily reduce I/O waits.

Option C is wrong because forcing parameterization may help plan stability but not I/O.

Full explanation →
938
MCQeasy

Your company is migrating on-premises SQL Server databases to Azure SQL Database. As part of security compliance, you must ensure that all data at rest is encrypted using customer-managed keys stored in Azure Key Vault. Which Azure SQL Database feature should you enable?

A.Dynamic Data Masking
B.Always Encrypted
C.Row-Level Security
D.Transparent Data Encryption with customer-managed keys in Azure Key Vault
AnswerD

TDE with CMK encrypts the database at rest using keys stored in Key Vault.

Why this answer

Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault is the correct feature because it encrypts SQL Server, Azure SQL Database, and Azure Synapse data files at rest using a customer-controlled key stored in Azure Key Vault. This meets the compliance requirement for encrypting all data at rest with customer-managed keys, as TDE performs real-time I/O encryption and decryption of the database, backups, and transaction log files without requiring application changes.

Exam trap

The trap here is that candidates often confuse 'Always Encrypted' (which protects sensitive columns from DBAs) with 'Transparent Data Encryption' (which encrypts the entire database at rest), leading them to select Always Encrypted when the requirement is for full data-at-rest encryption with customer-managed keys.

How to eliminate wrong answers

Option A is wrong because Dynamic Data Masking obfuscates sensitive data in query results to prevent unauthorized viewing, but it does not encrypt data at rest and does not use customer-managed keys. Option B is wrong because Always Encrypted encrypts data in transit and at rest at the client-side, protecting data from the database administrator, but it is not designed for encrypting the entire database at rest and does not use Azure Key Vault for customer-managed TDE keys. Option C is wrong because Row-Level Security controls access to rows in a table based on user identity or context, but it does not provide any encryption of data at rest.

Full explanation →
939
MCQmedium

A company uses Azure SQL Managed Instance. They need to automate index maintenance for all databases in the instance. The solution must minimize administrative overhead and use built-in Azure features. What should you do?

A.Use Azure Automation with a PowerShell runbook that connects to each database and runs index maintenance.
B.Configure a SQL Agent job on the instance to run index maintenance on the master database.
C.Create an elastic job agent with a T-SQL script for index maintenance targeting all databases.
D.Use Azure Data Factory to schedule a stored procedure execution for each database.
AnswerC

Elastic job agent is designed for automating tasks across databases in a managed instance with minimal overhead.

Why this answer

Option C is correct because an elastic job agent is a built-in Azure feature designed specifically for automating administrative tasks across multiple databases in Azure SQL Managed Instance. It allows you to create a T-SQL script for index maintenance and target all databases in the instance with minimal overhead, as it manages scheduling, retries, and target group membership natively.

Exam trap

The trap here is that candidates might confuse SQL Agent jobs (which are database-scoped in Managed Instance) with the ability to run cross-database tasks, or assume Azure Automation is the only built-in option, when in fact elastic job agents are the native, low-overhead solution for multi-database automation in Azure SQL Managed Instance.

How to eliminate wrong answers

Option A is wrong because Azure Automation with a PowerShell runbook introduces unnecessary administrative overhead and is not a built-in Azure SQL feature; it requires managing runbook accounts, modules, and authentication separately, and does not integrate natively with SQL Managed Instance's database engine. Option B is wrong because configuring a SQL Agent job on the master database only runs maintenance on that single database, not on all databases in the instance; SQL Agent jobs in Managed Instance are scoped to the database where they are created, unless explicitly targeting others via cross-database queries, which is not a built-in automation feature for all databases. Option D is wrong because Azure Data Factory is an orchestration service for data movement and transformation, not a built-in tool for automating index maintenance on SQL Managed Instance; it would require complex pipeline design and external triggers, adding overhead rather than minimizing it.

Full explanation →
940
Multi-Selectmedium

Which TWO actions are required to implement Azure SQL Database automatic failover with zero data loss?

Select 2 answers
A.Create an auto-failover group.
B.Configure active geo-replication.
C.Enable read scale-out.
D.Use Premium or Business Critical service tier.
E.Configure multiple readable secondaries.
AnswersA, D

Auto-failover groups manage failover orchestration.

Why this answer

An auto-failover group is required because it provides automatic failover capability across Azure SQL Database instances, which is essential for achieving zero data loss during a regional outage. The Premium or Business Critical service tier is required because these tiers use locally redundant storage with synchronous replication to ensure that transactions are committed without data loss, unlike the Standard or General Purpose tiers that use asynchronous replication.

Exam trap

The trap here is that candidates often confuse active geo-replication (which is asynchronous and can lose data) with auto-failover groups (which can achieve zero data loss only when used with Premium or Business Critical tiers), leading them to select active geo-replication as a correct answer.

Full explanation →
941
MCQmedium

You are managing an Azure SQL Managed Instance that hosts multiple databases for a financial application. You need to implement a security solution that meets compliance requirements by auditing all database activity and sending the audit logs to a centralized Log Analytics workspace for analysis. The solution must also support real-time alerts on suspicious activities. What should you configure?

A.Enable Microsoft Defender for Cloud and configure SQL vulnerability assessment.
B.Enable auditing to a storage account and use Microsoft Intune for monitoring.
C.Enable auditing to a Log Analytics workspace and integrate with Microsoft Sentinel.
D.Enable Microsoft Purview Data Map for the managed instance.
AnswerC

Auditing to LA workspace and Sentinel enables real-time detection and alerting.

Why this answer

Option C is correct because auditing to a Log Analytics workspace allows centralized collection of audit logs, which can then be integrated with Microsoft Sentinel for real-time analytics, threat detection, and automated alerting on suspicious activities. This meets both the compliance requirement for auditing and the operational need for real-time alerts.

Exam trap

The trap here is that candidates may confuse Microsoft Defender for Cloud (which provides vulnerability assessment and security recommendations) with a full auditing and SIEM solution, or mistakenly think that a storage account plus Intune can provide real-time alerting, when in fact only Log Analytics with Sentinel delivers both centralized auditing and real-time threat detection.

How to eliminate wrong answers

Option A is wrong because Microsoft Defender for Cloud and SQL vulnerability assessment focus on security posture and vulnerability scanning, not on auditing all database activity or sending logs to a Log Analytics workspace for real-time alerts. Option B is wrong because while auditing to a storage account captures logs, Microsoft Intune is a mobile device management (MDM) and endpoint management tool, not a monitoring or alerting solution for database audit logs. Option D is wrong because Microsoft Purview Data Map is designed for data governance, cataloging, and lineage, not for auditing database activity or providing real-time security alerts.

Full explanation →
942
MCQeasy

You are designing a high availability solution for a mission-critical Azure SQL Database. The database must have an uptime SLA of 99.995% and automatically recover from zone-level failures without data loss. Which service tier and redundancy option should you choose?

A.Basic tier with zone redundancy
B.General Purpose tier with zone redundancy
C.Hyperscale tier with zone redundancy
D.Business Critical tier with zone redundancy
AnswerD

Business Critical with zone redundancy provides 99.995% SLA and automatic zone failure recovery.

Why this answer

Option C is correct because the Business Critical tier with zone redundancy provides an SLA of 99.995% and automatic recovery from zone failures. Option A (General Purpose with zone redundancy) has a lower SLA (99.99%). Option B (Hyperscale with zone redundancy) offers 99.95% SLA for zone-redundant configuration.

Option D (Basic tier) does not support zone redundancy and has a lower SLA.

Full explanation →
943
MCQeasy

You are a database administrator for a hospital that uses Azure SQL Database to store patient records. The hospital's security policy requires that all database access be authenticated using Microsoft Entra ID (formerly Azure AD). You have already created a Microsoft Entra ID user for yourself and granted you the 'db_owner' role. You now need to create a new Microsoft Entra ID user for a nurse who needs read-only access to the database. What should you do first?

A.In the Azure portal, add the nurse as a server-level Microsoft Entra admin
B.Create a SQL login for the nurse on the logical server and then create a user in the database mapped to that login
C.Connect to the master database using SQL authentication and run 'CREATE USER [nurse@hospital.onmicrosoft.com] FROM EXTERNAL PROVIDER'
D.Connect to the database using your Microsoft Entra account and run 'CREATE USER [nurse@hospital.onmicrosoft.com] FROM EXTERNAL PROVIDER'
AnswerD

This creates a contained database user for the nurse in the database.

Why this answer

Option D is correct because the nurse must be created as a contained database user mapped to Microsoft Entra ID. Since the hospital uses Azure SQL Database and requires Microsoft Entra authentication, you must connect to the user database (not master) using your Microsoft Entra account (which has db_owner privileges) and run 'CREATE USER [nurse@hospital.onmicrosoft.com] FROM EXTERNAL PROVIDER'. This creates a database user that authenticates via Microsoft Entra ID without requiring a server-level login, aligning with the security policy.

Exam trap

The trap here is that candidates mistakenly think they need to create a login in the master database first (as in SQL Server or Azure SQL Managed Instance), but Azure SQL Database uses contained database users for Microsoft Entra authentication, so the 'CREATE USER ... FROM EXTERNAL PROVIDER' must be run directly in the user database by a Microsoft Entra-authenticated user.

How to eliminate wrong answers

Option A is wrong because adding the nurse as a server-level Microsoft Entra admin grants full administrative privileges over the logical server, far exceeding the required read-only access and violating the principle of least privilege. Option B is wrong because Azure SQL Database does not support SQL logins for Microsoft Entra users; you cannot create a SQL login mapped to a Microsoft Entra identity, and the approach of creating a SQL login and then a database user is for SQL authentication, not Microsoft Entra authentication. Option C is wrong because connecting to the master database with SQL authentication is not possible if the policy requires Microsoft Entra authentication, and 'CREATE USER ...

FROM EXTERNAL PROVIDER' must be run in the user database, not master, and must be executed by a Microsoft Entra-authenticated principal.

Full explanation →
944
MCQhard

You are the database administrator for a global e-commerce company. They run a mission-critical application on Azure SQL Database in the Business Critical tier. The database is 2 TB and experiences high write throughput. The current setup uses an auto-failover group with a secondary in the same region (West US) for high availability. The client application uses the auto-failover group listener with ReadScale=1 to route read-only queries to the secondary. Recently, during a regional outage that affected West US, the failover to the secondary succeeded, but the application experienced significant performance degradation and many timeouts for read operations. Investigation reveals that the secondary replica was overwhelmed with read traffic after failover. The business requires an RTO of 30 seconds and RPO of 5 seconds. The application must be able to handle read-heavy workloads even during a failover. You need to recommend a solution to improve read scalability and disaster recovery without changing the application code. What should you do?

A.Add a second secondary replica to the auto-failover group in the same region.
B.Deploy an additional readable secondary replica in the Business Critical tier in a different Azure region and configure active geo-replication to that region. Update the connection string to use the geo-secondary for reads.
C.Increase the service objective of the secondary replica in the auto-failover group to a higher DTU or vCore to handle the read load.
D.Change the database to the Hyperscale tier, which supports multiple readable replicas and automatic failover.
AnswerB

This provides additional read capacity and disaster recovery.

Why this answer

Option B is correct because deploying an additional readable secondary replica in a different Azure region via active geo-replication provides both disaster recovery and read scalability. After a regional failover, the geo-secondary can serve read traffic without overwhelming the primary, meeting the RTO of 30 seconds and RPO of 5 seconds. The application can use the geo-secondary for reads by updating the connection string, without changing application code, ensuring read-heavy workloads are handled during failover.

Exam trap

The trap here is that candidates may think adding more replicas in the same region (Option A) or scaling the secondary (Option C) solves the problem, but they fail to recognize that a regional outage requires a geographically separate replica to ensure read scalability and disaster recovery.

How to eliminate wrong answers

Option A is wrong because adding a second secondary replica in the same region does not protect against a regional outage; both replicas would be affected, and the read traffic would still overwhelm the single surviving replica. Option C is wrong because increasing the service objective of the secondary replica does not address the root cause of read traffic overwhelming a single replica after failover; it only provides more resources but still relies on a single replica for reads, which can still be overwhelmed. Option D is wrong because changing to the Hyperscale tier, while supporting multiple readable replicas, does not guarantee the same RTO/RPO as the Business Critical tier with auto-failover groups, and it requires significant architectural changes that may not meet the strict RTO of 30 seconds.

Full explanation →
945
MCQeasy

Refer to the exhibit. You apply this JSON configuration to an Azure SQL Database server. Which actions will be audited?

A.All successful and failed login attempts and all schema changes
B.All database-level actions
C.Only successful and failed database authentication attempts
D.All successful and failed logins and all data modification statements
AnswerC

Only the two groups specified.

Why this answer

Option C is correct because the configuration only includes SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP. Option A is wrong because it includes actions not in the config. Option B is wrong because it includes data modification actions.

Option D is wrong because it includes all actions.

Full explanation →
946
MCQeasy

You are deploying an Azure SQL Database for a development environment. The database will be used for testing and will be deleted after three months. You want to minimize costs while ensuring the database can be restored to any point within the retention period. Which backup retention policy should you configure?

A.Configure geo-redundant backup storage.
B.Configure short-term backup retention to 7 days.
C.Configure short-term backup retention to 14 days.
D.Configure long-term backup retention (LTR) for 1 year.
AnswerB

7-day retention is the default and meets the need for point-in-time restore within retention.

Why this answer

Option B is correct because the scenario specifies a development database that will be deleted after three months, with the requirement to restore to any point within the retention period. The default short-term backup retention for Azure SQL Database is 7 days, which provides point-in-time restore (PITR) capability within that window. Since the database is temporary and cost minimization is key, 7 days meets the requirement without incurring extra storage costs for longer retention or geo-redundancy.

Exam trap

The trap here is that candidates may assume a longer retention period (14 days or 1 year) is always better, but the question explicitly asks to minimize costs while meeting the restore requirement, making the default 7-day retention the most cost-effective choice.

How to eliminate wrong answers

Option A is wrong because geo-redundant backup storage (RA-GRS) is designed for disaster recovery across regions, which is unnecessary for a temporary development database and increases cost. Option C is wrong because configuring short-term retention to 14 days would double the backup storage cost compared to 7 days, and the scenario does not require a retention period longer than the default. Option D is wrong because long-term retention (LTR) for 1 year is intended for compliance or archival needs, not for a database that will be deleted in three months, and it incurs significant additional cost.

Full explanation →
947
Multi-Selecthard

You are configuring performance optimization for an Azure SQL Database that uses the Hyperscale service tier. The database has heavy read-write workloads. Which THREE actions should you take to optimize performance?

Select 3 answers
A.Create nonclustered columnstore indexes on large fact tables.
B.Configure resource governor to limit CPU usage.
C.Enable accelerated database recovery (ADR) to reduce transaction log IO.
D.Configure read-scale replicas to offload reporting queries.
E.Increase the service tier to Business Critical.
AnswersA, C, D

Columnstore indexes improve performance for large analytical queries.

Why this answer

Options A, C, and D are correct. Hyperscale benefits from nonclustered indexes on read-intensive tables, accelerated database recovery reduces log IO, and read-scale replicas distribute read load. Option B is wrong because Hyperscale does not support resource governor.

Option E is wrong because increasing service tier is not an optimization action.

Full explanation →
948
Multi-Selecteasy

You are configuring authentication for Azure SQL Database. Which TWO of the following are supported authentication methods?

Select 2 answers
A.Windows authentication using Kerberos.
B.Microsoft Entra ID authentication with a service principal.
C.OAuth 2.0 token authentication.
D.SQL authentication with a username and password.
E.Certificate-based authentication for SQL logins.
AnswersB, D

Service principals in Microsoft Entra ID can authenticate to Azure SQL Database.

Why this answer

Options B and D are correct. Microsoft Entra ID (formerly Azure AD) authentication is supported, including service principals. SQL authentication with username/password is also supported.

Option A is wrong because Windows authentication is not supported for Azure SQL Database (only for SQL Server on VMs or on-premises). Option C is wrong because certificate-based authentication is not directly supported for SQL logins; it can be used with Always Encrypted but not for authentication. Option E is wrong because OAuth tokens are used with Microsoft Entra ID but not as a standalone method.

Full explanation →
949
MCQeasy

You have an Azure SQL Database that uses the General Purpose service tier. The database is critical and you need to ensure that it remains available during a planned patching event that updates the underlying hardware. What does Azure SQL Database provide to maintain availability during such events?

A.The database is taken offline during patching and restored afterward.
B.You must manually fail over to a secondary database to avoid downtime.
C.Zone-redundant replicas that ensure zero downtime.
D.Automated failover to a built-in standby replica with minimal downtime.
AnswerD

General Purpose tier uses a built-in standby replica for high availability during patching.

Why this answer

Option B is correct. General Purpose tier provides built-in high availability with automatic failover to a standby replica during patching events. This is a standard feature.

Option A is wrong because General Purpose does not have zone-redundant replicas by default. Option C is wrong because the database is not offline; it fails over seamlessly. Option D is wrong because manual failover is not required; it's automatic.

Full explanation →
950
MCQhard

You administer a SQL Managed Instance in the West Europe region. You need to create a disaster recovery replica in North Europe with automated failover. The replica must be readable and support backups. What should you configure?

A.Set up log shipping from West Europe to North Europe.
B.Configure active geo-replication between the instances.
C.Create a failover group, but note the secondary is not readable.
D.Create a failover group with the secondary instance in North Europe.
AnswerD

Failover groups provide automated failover and readable secondary for Managed Instance.

Why this answer

Option B is correct because SQL Managed Instance supports failover groups, which provide automated failover and readable secondary instances. Geo-replication is not supported for Managed Instance. Option A is wrong because geo-replication is not supported.

Option C is wrong because the secondary in a failover group is readable. Option D is wrong because log shipping is not supported.

Full explanation →
951
MCQmedium

A company manages an Azure SQL Database that stores sensitive customer data. The security team mandates that all connections to the database use Azure Active Directory (Azure AD) authentication and that no SQL authentication logins exist. You are tasked with implementing this requirement. What should you do first?

A.Set the server's 'Public network access' to 'Disabled'.
B.Remove the server admin login from the master database.
C.Set an Azure Active Directory admin for the Azure SQL Database server.
D.Deny the CONNECT permission to all SQL authentication logins.
AnswerC

An Azure AD admin must be set before disabling SQL authentication.

Why this answer

Option C is correct because before you can enforce Azure AD-only authentication, you must first designate an Azure AD admin for the Azure SQL Database server. This admin is the only identity that can manage Azure AD users and permissions in the database, and once set, you can then remove or disable SQL authentication logins. Without an Azure AD admin, there is no way to authenticate or manage Azure AD principals within the database, making the transition impossible.

Exam trap

The trap here is that candidates often confuse disabling network access or removing permissions with actually changing the authentication model, but the first required step is always to establish an Azure AD admin to enable Azure AD authentication at the server level.

How to eliminate wrong answers

Option A is wrong because disabling public network access restricts network connectivity but does not affect authentication methods; SQL authentication logins would still exist and could be used if network access were re-enabled. Option B is wrong because removing the server admin login from the master database would break all administrative access before an Azure AD admin is established, potentially locking you out of the server entirely. Option D is wrong because denying CONNECT permission to SQL authentication logins does not remove the logins themselves; they remain in the database and could be re-granted permissions, and this action does not enforce Azure AD-only authentication as a policy.

Full explanation →
952
Multi-Selecthard

Which THREE of the following are best practices for managing keys in Azure Key Vault for use with Azure SQL Database TDE?

Select 3 answers
A.Enable soft-delete and purge protection on the Key Vault.
B.Rotate the keys periodically.
C.Grant the server managed identity 'get', 'wrapKey', and 'unwrapKey' permissions.
D.Store the Key Vault in the same resource group as the SQL server.
E.Disable Key Vault auditing to reduce costs.
AnswersA, B, C

Prevents accidental key loss.

Why this answer

Enabling soft-delete and purge protection on the Key Vault is a best practice because soft-delete retains deleted keys for a configurable retention period (default 90 days), allowing recovery if a key is accidentally deleted. Purge protection prevents permanent deletion of keys even after the soft-delete retention period expires, which is critical for TDE because if the key is permanently lost, the encrypted database becomes inaccessible. Together, these features ensure that the TDE protector key is never irrevocably lost, maintaining database recoverability and compliance.

Exam trap

The trap here is that candidates often think placing the Key Vault in the same resource group simplifies management, but Microsoft explicitly recommends a separate resource group to avoid accidental deletion of the vault when the SQL server is deprovisioned.

Full explanation →
953
MCQmedium

You are configuring Microsoft Defender for SQL for Azure SQL Database. You need to ensure that alerts are sent to the security operations team via email and also integrated with Microsoft Sentinel. What should you configure?

A.In Microsoft Sentinel, connect the Azure SQL Database data source using the built-in connector.
B.Use Azure Logic Apps to forward Defender for SQL alerts to Sentinel.
C.Configure a diagnostic setting on the SQL server to stream logs to a Log Analytics workspace used by Sentinel.
D.Configure the alert rule in Defender for SQL to send email to the security team.
AnswerA

Sentinel has a built-in connector for Azure SQL Database that pulls alerts from Defender for SQL.

Why this answer

Option A is correct because Microsoft Sentinel provides a built-in connector specifically for Azure SQL Database that directly ingests security alerts from Microsoft Defender for SQL. This connector enables seamless integration without requiring additional components, and you can configure email notifications for the security operations team directly within the Defender for SQL alert settings. This satisfies both requirements: alerts are sent via email and streamed to Sentinel for centralized monitoring and incident response.

Exam trap

The trap here is that candidates often confuse diagnostic settings (which stream performance and query logs) with the dedicated Sentinel connector (which ingests security alerts), leading them to choose Option C, or they overcomplicate the solution by selecting Logic Apps (Option B) when a native connector already exists.

How to eliminate wrong answers

Option B is wrong because Azure Logic Apps are not required; Sentinel already has a native connector for Azure SQL Database that directly pulls Defender for SQL alerts, making Logic Apps an unnecessary extra step that adds complexity and cost. Option C is wrong because diagnostic settings stream telemetry logs (such as SQLInsights, QueryStoreRuntimeStatistics) to a Log Analytics workspace, but they do not capture Defender for SQL security alerts; those alerts are ingested via the dedicated Sentinel connector, not through diagnostic logs. Option D is wrong because configuring the alert rule in Defender for SQL to send email only addresses the email notification requirement; it does not integrate with Microsoft Sentinel, leaving the security operations team without centralized alert management and automated incident response capabilities.

Full explanation →
Page 12

Page 13 of 13

All pages

12345678910111213

Practice DP-300 by domain

Target a specific domain to shore up weak areas.

Plan and configure a high availability and disaster recovery environmentPlan and implement data platform resourcesMonitor, configure, and optimize database resourcesConfigure and manage automation of tasksPlan and configure high availability and disaster recoveryImplement a secure environment
See all domains with question counts →