Courseiva

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

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

Take a mock examExam hub
Page 3

Page 4 of 13

Page 5
226
Multi-Selecteasy

Which TWO benefits does the Hyperscale service tier of Azure SQL Database provide?

Select 2 answers
A.Built-in in-memory OLTP support.
B.Zone-redundant configuration by default.
C.Up to 100 TB of database storage.
D.Fast provisioning of additional read replicas.
E.Zero data loss in all scenarios.
AnswersC, D

Hyperscale provides large storage capacity.

Why this answer

The Hyperscale service tier of Azure SQL Database supports up to 100 TB of database storage, which is significantly larger than the 4 TB limit of the General Purpose tier and the 4 TB limit of the Business Critical tier. This massive storage capacity is achieved through a distributed architecture where data is stored in page servers with a shared disk model, allowing near-instantaneous scaling of storage without downtime.

Exam trap

The trap here is that candidates often confuse the Hyperscale tier's storage limit with the Business Critical tier's in-memory OLTP or zone-redundancy features, leading them to select options that are technically correct for other tiers but not for Hyperscale.

Full explanation →
227
MCQhard

Your organization uses Azure SQL Database with elastic pools. One of the databases in the pool, 'OrdersDB', consistently uses more eDTUs than other databases, causing performance degradation for other databases. You need to isolate the resource usage of 'OrdersDB' to prevent it from affecting others. The solution should minimize cost and administrative overhead. What should you do?

A.Purchase reserved capacity for the pool to reduce costs.
B.Use Query Store to identify and kill the high-usage queries.
C.Move 'OrdersDB' to a higher service tier, such as a standalone database with a higher DTU limit.
D.Increase the eDTU limit of the elastic pool to accommodate the high usage.
AnswerC

Isolating the database ensures other databases are not affected, and you can choose appropriate resources for 'OrdersDB'.

Why this answer

Moving 'OrdersDB' to a standalone database with a higher DTU limit isolates its resource usage, preventing it from affecting other databases in the elastic pool. This approach minimizes administrative overhead as it's a straightforward configuration change. Option A is incorrect because purchasing reserved capacity is a billing benefit, not a resource isolation mechanism.

Option B is incorrect because Query Store can identify high-usage queries but cannot limit resource consumption. Option D is incorrect because increasing the pool's eDTU limit would increase costs, and the high-usage database could still consume resources and affect others within the pool.

Full explanation →
228
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

TDE encrypts the entire database at rest.

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 →
229
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

ATP detects SQL injection and other threats.

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 →
230
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

TDE encrypts the database and backups at rest, and customer-managed keys are supported.

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 →
231
MCQmedium

You are reviewing the configuration of an Azure SQL Database. The database is currently 250 GB in size. You are concerned about storage capacity. What is the maximum size limit for this database?

A.1 TB
B.500 GB
C.250 GB
D.4 TB
AnswerC

The maxSizeBytes value is 250 GB.

Why this answer

The database is currently 250 GB in size, which matches its maximum size limit. Therefore, the maximum size limit is 250 GB (Option C). Option A (1 TB) is incorrect because the limit is not that high.

Option B (500 GB) is incorrect as it exceeds the actual limit. Option D (4 TB) is also incorrect for the same reason.

Full explanation →
232
Drag & Dropmedium

Drag and drop the steps to configure a failover group for an Azure SQL Database in the correct order.

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

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 →
233
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

RLS provides row-level isolation.

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 →
234
Multi-Selecthard

You have an Azure SQL Database in the General Purpose tier. You need to implement high availability with automatic failover to a secondary database in a different Azure region. The solution must provide an RPO of 5 seconds and RTO of 1 hour. Which TWO components must you configure? (Select TWO.)

Select 2 answers
A.Change the service tier to Business Critical
B.Enable zone redundancy on the primary database
C.Configure active geo-replication
D.Create a secondary database in a different Azure region
E.Create an auto-failover group
AnswersC, D

Active geo-replication provides asynchronous replication to a secondary region.

Why this answer

Active geo-replication (Option C) creates a readable secondary database in a different Azure region, supporting cross-region disaster recovery. When combined with an auto-failover group, it provides automatic failover with an RPO of 5 seconds and an RTO of 1 hour, meeting the requirements for a General Purpose tier database.

Exam trap

The trap here is that candidates often confuse the auto-failover group (Option E) as a standalone component, not realizing it requires a pre-configured secondary database via active geo-replication to function, and they may incorrectly assume Business Critical tier (Option A) is necessary for cross-region failover when General Purpose with geo-replication suffices.

Full explanation →
235
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

Auditing can log failed login attempts to a storage account, Log Analytics, or Event Hubs.

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 →
236
MCQmedium

Your company uses Azure SQL Managed Instance and requires that all connections from client applications use Microsoft Entra authentication with multi-factor authentication (MFA). You configure Azure SQL Managed Instance to support Microsoft Entra authentication and create a contained database user for the application. However, when the application attempts to connect, it receives error '18456, state 10' indicating that the login is not recognized. What is the most likely cause?

A.The contained database user is not mapped to a server-level login.
B.The client IP address is not allowed by the server-level firewall rule.
C.The application is using a SQL login instead of a Microsoft Entra token or integrated authentication.
D.The server principal (server admin) is not a Microsoft Entra account.
AnswerC

The application must use Microsoft Entra authentication method; error 18456 state 10 indicates a SQL login attempt.

Why this answer

Error 18456, state 10 specifically indicates that the login was not recognized by SQL Server. Since the application is configured to use a contained database user with Microsoft Entra authentication, the most likely cause is that the application is attempting to connect using a SQL login (username/password) rather than presenting a Microsoft Entra access token or using integrated authentication. Contained database users require authentication through the Microsoft Entra ID token flow, not SQL Server authentication.

Exam trap

The trap here is that candidates often assume a contained database user must be mapped to a server-level login (Option A), but in Azure SQL Managed Instance, contained users are independent and authenticate directly via Microsoft Entra tokens, making the mapping misconception the primary distractor.

How to eliminate wrong answers

Option A is wrong because contained database users in Azure SQL Managed Instance are designed to exist without a server-level login mapping; they authenticate directly at the database level. Option B is wrong because the error '18456, state 10' is an authentication failure, not a connectivity or firewall issue; a firewall block would produce a different error (e.g., 18456, state 1 or a timeout). Option D is wrong because the server principal (server admin) being a Microsoft Entra account is not required for contained database users to authenticate; the server admin can be a SQL login, and contained users still work independently.

Full explanation →
237
MCQhard

Your company uses Azure SQL Managed Instance for a critical application. You need to meet a Recovery Point Objective (RPO) of 15 seconds and a Recovery Time Objective (RTO) of 30 seconds for planned maintenance. You also need to protect against a regional failure with an RPO of 5 minutes and an RTO of 1 hour. What combination of features should you use?

A.Configure active geo-replication to a secondary region and use automatic failover.
B.Deploy in General Purpose tier with zone redundancy and geo-redundant backup.
C.Create a failover group to a secondary region and rely on that for both HA and DR.
D.Deploy the instance in the Business Critical tier with zone redundancy, and configure a failover group to a secondary region.
AnswerD

Zone redundancy provides HA within region; failover group provides DR across regions.

Why this answer

Business Critical tier with zone redundancy provides high availability within a region with an RPO of 0 and RTO of approximately 30 seconds for planned maintenance. Configuring a failover group to a secondary region provides disaster recovery with an RPO of 5 minutes and RTO of 1 hour. Option A is incorrect because active geo-replication is not supported for Azure SQL Managed Instance; failover groups are used instead.

Option B is incorrect because General Purpose tier does not provide an RTO of 30 seconds for planned maintenance, and geo-redundant backup does not meet the required RPO and RTO for planned maintenance. Option C is incorrect because a failover group alone does not provide the sub-minute RTO needed for planned maintenance within the region.

Full explanation →
238
Multi-Selectmedium

Which TWO of the following are benefits of using Azure SQL Database failover groups for high availability?

Select 2 answers
A.Synchronous replication of data across availability zones.
B.Automatic failover to a secondary region based on a grace period.
C.Routing of read-only queries to the secondary region.
D.Load balancing read-write traffic across multiple replicas.
E.Geo-redundant backup storage for disaster recovery.
AnswersB, C

Failover groups support automatic failover.

Why this answer

Failover groups provide automatic failover to a secondary region based on a grace period (option B), allowing for continuous availability during regional outages. They also support routing read-only queries to the secondary region (option C) to offload read workloads and improve performance. Option A is incorrect because synchronous replication across availability zones is achieved through zone-redundant configuration, not failover groups.

Option D is incorrect because load balancing read-write traffic across replicas is not a feature of failover groups; that is handled by read-scale replicas or connection policies. Option E is incorrect because geo-redundant backup storage is a separate feature (geo-redundant storage), not a direct benefit of failover groups.

Full explanation →
239
MCQhard

You are designing an automated backup strategy for Azure SQL Database. The compliance policy requires point-in-time restore (PITR) for the last 35 days, and long-term retention (LTR) for 7 years. What is the minimal number of LTR policies needed?

A.One LTR policy per elastic pool.
B.Two LTR policies per server (weekly and yearly).
C.One LTR policy per server.
D.One LTR policy per database.
AnswerD

Each database requires its own LTR policy to define retention periods.

Why this answer

LTR policies are applied at the database level in Azure SQL Database. Each database requires its own LTR policy to retain backups beyond the PITR retention period. Option A is wrong because policies are per database, not per elastic pool.

Option B is wrong because two policies per server (weekly and yearly) are not a minimal requirement; each database can have multiple LTR policies as needed. Option C is wrong because one LTR policy cannot cover all databases on a server; each database has its own retention requirements.

Exam trap

A common pitfall is confusing LTR policy scope with elastic pool or server level. LTR is per database, not per server or pool.

Full explanation →
240
MCQhard

You manage an Azure SQL Database that is part of an Always On availability group in Azure SQL Managed Instance. You notice that the secondary replica is experiencing high log send queue size. The primary replica has ample CPU and I/O resources. The network latency between replicas is low. What is the most likely cause of the high log send queue?

A.The secondary replica is configured with asynchronous commit.
B.The primary replica is experiencing log flush delays.
C.The secondary replica's log apply rate is slower than the log generation rate on the primary.
D.The network bandwidth between replicas is insufficient.
AnswerC

A slow secondary can cause backlog, even with fast network.

Why this answer

The most likely cause of high log send queue is that the secondary replica's log apply rate is slower than the log generation rate on the primary (Option C). This typically occurs when the secondary replica is under-provisioned or has high I/O contention, causing it to fall behind in applying received log records. Option A (asynchronous commit) is incorrect because asynchronous commit can actually reduce log send queue pressure by not waiting for the secondary, but it does not directly cause queue buildup.

Option B (log flush delays on primary) is unlikely because the primary has ample CPU and I/O resources. Option D (insufficient network bandwidth) is unlikely given that network latency is low and the primary resources are ample.

Full explanation →
241
Multi-Selecthard

You are responsible for automating index maintenance and statistics updates across multiple Azure SQL Databases in an elastic pool. Each database has varying workloads and sizes. The automation must run weekly during low-traffic periods and should not degrade performance for critical applications. Which three actions should you take to implement a reliable and efficient automation solution?

Select 3 answers
A.Create an Azure Automation account and schedule a PowerShell runbook that connects to each database and runs index maintenance scripts.
B.Configure SQL Agent jobs in each database to run the maintenance scripts.
C.Use Elastic Database Jobs to run T-SQL scripts against all databases in the elastic pool.
D.Enable automatic tuning for index management and statistics update in each database.
E.Enable auto-failover groups for all databases to automate failover during maintenance.
AnswersA, C, D

Azure Automation can execute T-SQL scripts on a schedule.

Why this answer

Azure Automation with PowerShell runbooks allows scheduling and executing T-SQL scripts across databases. Option C is correct because Elastic Database Jobs can target a group of databases in an elastic pool. Option D is correct because automatic tuning can handle index and statistics maintenance, reducing manual effort.

Option B is incorrect because SQL Agent jobs are not available in Azure SQL Database (only in Managed Instance) and cannot be used across multiple databases in an elastic pool. Option E is incorrect because auto-failover groups are for high availability and disaster recovery, not for automating maintenance tasks.

Full explanation →
242
MCQhard

You are the database administrator for a large e-commerce application hosted on Azure SQL Database in the Business Critical tier (8 vCores). The database has a table named Orders with 500 million rows. The table is partitioned by OrderDate using a monthly partition function. The application frequently runs queries that aggregate sales by month for the current year. Users report that these queries are slow. You examine the query execution plans and notice that the queries are scanning all partitions, not just the relevant ones. You need to improve query performance without changing the application code. Which action should you take?

A.Update statistics for the Orders table.
B.Create a columnstore index on the Orders table.
C.Re-create the partition function with a different boundary type (e.g., RIGHT instead of LEFT) to align with query patterns.
D.Create an indexed view that pre-aggregates sales by month.
AnswerC

Changing boundary type can improve partition elimination for date range queries.

Why this answer

The issue is that partition elimination is not occurring, meaning the query scans all partitions instead of only the relevant ones for the current year. Since the table is partitioned by OrderDate using a monthly partition function, the alignment of partition boundaries with the query predicate is critical. If the current partition function uses LEFT boundaries, the boundary values represent the start of each partition, but queries for the current year might include dates that cross boundaries in a way that prevents elimination.

Re-creating the partition function with RIGHT boundaries ensures that each boundary value is the first day of the next partition, which often aligns better with range queries like 'WHERE OrderDate >= '2025-01-01' AND OrderDate < '2025-12-31''. This can enable partition elimination without changing application code. Option A (update statistics) does not affect partition elimination.

Option B (columnstore index) improves compression and query speed but does not force partition elimination. Option D (indexed view) requires code changes and does not directly cause partition elimination. Therefore, option C is correct.

Full explanation →
243
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

This is the standard way to connect using a service principal.

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 →
244
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 →
245
MCQmedium

You are the DBA for a financial services company. The company is migrating its on-premises SQL Server databases to Azure SQL Managed Instance. One of the databases is 2 TB in size and has a high transaction volume. The migration must have minimal downtime. The source environment is SQL Server 2019 running on Windows Server. The target is a General Purpose managed instance in the same region. The network bandwidth between on-premises and Azure is 500 Mbps. The DBA plans to use Azure Database Migration Service (DMS) for an online migration. However, during the initial sync, the migration is progressing slowly and the estimated time to completion is 48 hours. The DBA needs to reduce the migration time. What should the DBA do?

A.Switch to an offline migration using BACPAC export and import
B.Upgrade the DMS instance to a Premium SKU to increase performance
C.Request a higher bandwidth connection from the network team
D.Change the target to a different Azure region that is closer to the source
AnswerB

Premium SKU provides more CPU and memory for data movement.

Why this answer

Upgrading the DMS instance to a Premium SKU increases the underlying compute and I/O resources allocated to the migration service, which directly improves the throughput of data movement during the online migration. Since the bottleneck is the DMS processing speed rather than network bandwidth (500 Mbps is sufficient for 2 TB over 48 hours), a Premium SKU provides higher performance tiers that can process change data capture (CDC) and bulk inserts faster, reducing the overall sync time.

Exam trap

The trap here is that candidates often assume network bandwidth is the primary bottleneck and request a faster connection, but DMS performance is more commonly limited by its own SKU tier and the processing overhead of CDC, not the network pipe.

How to eliminate wrong answers

Option A is wrong because switching to an offline migration using BACPAC export/import would require taking the database offline, which contradicts the requirement for minimal downtime; BACPAC also does not support high transaction volumes well and would be slower than DMS online migration. Option C is wrong because the network bandwidth is 500 Mbps, which is already sufficient to transfer 2 TB in under 48 hours (theoretical max ~10.8 TB in 48 hours), so the bottleneck is not bandwidth but DMS processing capacity. Option D is wrong because changing the target region would increase latency and potentially reduce throughput due to longer network paths, and the source and target are already in the same region; a different region would not help and could violate compliance or latency requirements.

Full explanation →
246
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

TDE encrypts data at rest, TLS encrypts data in transit.

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 →
247
Multi-Selecthard

You are designing a disaster recovery plan for an Azure SQL Managed Instance that hosts a financial application. The application requires an RPO of 0 seconds and an RTO of 30 seconds. Which TWO configurations meet these requirements? (Choose two.)

Select 2 answers
A.Configure active geo-replication between two Business Critical instances in different regions.
B.Deploy a zone-redundant Business Critical instance in a single region and rely on automatic failover.
C.Deploy a Business Critical instance with zone redundancy and a failover group to a secondary instance in a paired region, using the Business Critical service tier.
D.Use a failover group with the 'planned' failover option and a Business Critical secondary instance.
E.Set up a manual failover process using a PowerShell script that restores from the latest backup.
AnswersC, D

Combining zone redundancy with a failover group to a paired region using the Business Critical service tier ensures synchronous replication locally and for planned failover to the secondary region, meeting zero RPO and fast RTO.

Why this answer

Options C and D are correct. To achieve an RPO of 0 seconds and an RTO of 30 seconds for Azure SQL Managed Instance, you need configurations that provide synchronous replication and rapid failover. Option C combines zone redundancy (for local high availability with zero data loss within the region) and a failover group to a secondary instance in a paired region.

The failover group, when used with the Business Critical service tier and a planned failover, can synchronize all data before failing over, meeting the zero RPO requirement. Option D directly uses a failover group with the 'planned' failover option, which ensures all committed transactions are replicated before failover, achieving zero data loss and a fast RTO. Option A is incorrect because active geo-replication is not supported for SQL Managed Instance and is asynchronous.

Option B is incorrect because zone redundancy alone provides high availability within a single region but does not address cross-region disaster recovery. Option E is incorrect because manual restore from backup cannot meet the 30-second RTO.

Full explanation →
248
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 uses a secondary replica for read-only queries.

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 →
249
Multi-Selecthard

You have an Azure SQL Database in the Business Critical tier with zone redundancy enabled. You need to maintain high availability during a planned patching event. Which THREE actions should you take? (Choose three.)

Select 3 answers
A.Enable active geo-replication to a secondary region.
B.Verify that zone redundancy is enabled on the database.
C.Scale the database to a lower tier to reduce cost.
D.Perform a planned failover test.
E.Ensure the database has a read-scale replica configured.
AnswersB, D, E

Verifying zone redundancy ensures the database is configured to survive patching within the same region.

Why this answer

To maintain high availability during a planned patching event on a Business Critical tier with zone redundancy enabled, you should first verify that zone redundancy is enabled (B) to confirm the configuration. Perform a planned failover test (D) to ensure the failover mechanism works correctly. Ensure the database has a read-scale replica configured (E) to offload read workloads and maintain performance.

Option A (enable active geo-replication) is not required for high availability during patching; it is for disaster recovery. Option C (scale to lower tier) would reduce availability and is unnecessary.

Full explanation →
250
MCQmedium

Refer to the exhibit. An administrator wants to restore the primary database SalesDB to a point in time 2025-03-15T09:00:00Z. What is the impact on the geo-replication?

A.The geo-replication will continue seamlessly after the restore.
B.The restore will fail because the database is in a geo-replication relationship.
C.The geo-replication will be terminated, and the secondary will become a standalone database.
D.The secondary database will automatically be restored to the same point in time.
AnswerC

Restoring the primary to a different point in time breaks the replication link.

Why this answer

When you perform a point-in-time restore (PITR) on a primary database that is configured for geo-replication, the geo-replication link is broken. The secondary database becomes a standalone read-only database, and the primary is restored as a new database (typically with a name like SalesDB_restored or a specified name). This is because PITR creates a new database from backups, which cannot maintain the continuous replication stream required for geo-replication.

Exam trap

The trap here is that candidates assume geo-replication is resilient to any restore operation, but point-in-time restore is a data-level operation that breaks the replication link, unlike a failover which preserves it.

How to eliminate wrong answers

Option A is wrong because geo-replication does not continue seamlessly; the replication link is terminated and must be reconfigured after the restore. Option B is wrong because the restore does not fail; Azure SQL Database allows the restore, but it breaks the geo-replication relationship. Option D is wrong because the secondary database is not automatically restored; it becomes a standalone database and is not affected by the PITR operation on the primary.

Full explanation →
251
MCQmedium

You manage an Azure SQL Database that uses active geo-replication with a secondary in a paired region. During a planned maintenance, you need to fail over to the secondary with zero data loss. What should you do?

A.Use Azure portal to 'Failover' without specifying data loss tolerance
B.Take the primary offline, then promote the secondary
C.Execute a planned failover (graceful failover) using Transact-SQL or PowerShell
D.Execute a forced failover allowing data loss
AnswerC

Planned failover synchronizes the secondary before promoting it, ensuring zero data loss.

Why this answer

A planned failover (graceful failover) synchronizes all transactions between the primary and secondary before switching, ensuring zero data loss. Option A uses a standard failover that may allow data loss if the secondary is not fully synchronized. Option B causes data loss by taking the primary offline abruptly.

Option D explicitly allows data loss, which is not acceptable for this requirement.

Full explanation →
252
MCQeasy

You need to automatically scale up an Azure SQL Database to the next service tier when CPU usage exceeds 80% for 10 minutes, then scale back down when CPU drops below 30% for 30 minutes. Which Azure feature should you use?

A.Azure Automation runbook with PowerShell cmdlets to modify the service objective.
B.Elastic Database Jobs to run ALTER DATABASE MODIFY.
C.Azure Logic Apps with a recurrence trigger and SQL DB REST API.
D.Configure autoscale settings in the Azure portal for the SQL database server.
AnswerA

Correct. Azure Automation runbooks can execute PowerShell cmdlets such as Set-AzSqlDatabase to scale the database up or down based on metrics like CPU percentage. This is a common pattern for automating tier changes without built-in autoscale.

Why this answer

Azure SQL Database does not have built-in autoscale based on CPU thresholds to change service tiers. The correct approach is to use Azure Automation runbooks, which can be triggered by Azure Monitor alerts or schedules to execute PowerShell cmdlets like Set-AzSqlDatabase to modify the service objective. Option D is incorrect because autoscale settings in the Azure portal are not available for SQL Database.

Option B is for running jobs across databases, not for scaling. Option C can also work but is less direct than Azure Automation for this automation scenario.

Exam trap

Candidates may assume Azure SQL Database has built-in autoscale like other services, but it does not. Custom automation using Azure Automation or Logic Apps is required.

Full explanation →
253
MCQmedium

You are designing a disaster recovery solution for a critical Azure SQL Database that runs in the West US region. The database is 2 TB in size and requires a recovery point objective (RPO) of less than 5 seconds and a recovery time objective (RTO) of less than 30 seconds. Which deployment option should you recommend?

A.Use Azure SQL Database General Purpose with auto-failover group
B.Use Azure SQL Database Hyperscale with geo-replication
C.Use Azure SQL Database Business Critical with zone-redundant configuration
D.Use Azure SQL Managed Instance Business Critical with zone-redundant configuration
AnswerC

Business Critical with zone redundancy provides synchronous replication across zones, achieving RPO=0 and RTO~30s.

Why this answer

Azure SQL Database Business Critical with zone-redundant configuration is the correct choice because it provides synchronous replication across three availability zones within the same region, ensuring an RPO of 0 (no data loss) and an RTO of under 30 seconds for zonal failures. This meets the strict RPO of less than 5 seconds and RTO of less than 30 seconds for a 2 TB database, as Business Critical offers high-performance local SSD storage and automatic failover without data loss.

Exam trap

The trap here is that candidates often confuse Hyperscale's geo-replication with synchronous replication, but Hyperscale uses asynchronous replication with a typical RPO of 5-10 seconds, which does not guarantee the sub-5-second RPO required, while Business Critical zone-redundant provides synchronous replication within the same region.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database General Purpose uses asynchronous replication with an RPO of up to 5 minutes and an RTO of 1-2 hours, failing to meet the sub-5-second RPO and sub-30-second RTO requirements. Option B is wrong because Azure SQL Database Hyperscale with geo-replication uses asynchronous replication with an RPO of 5 seconds or more (typically 5-10 seconds) and an RTO of 1-2 hours, which does not guarantee the required RPO of less than 5 seconds and RTO of less than 30 seconds. Option D is wrong because Azure SQL Managed Instance Business Critical with zone-redundant configuration is designed for managed instances, not single Azure SQL Database deployments, and does not support the same auto-failover group capabilities for a single database; additionally, it targets a different service tier and deployment model.

Full explanation →
254
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

Planned failover switches roles gracefully, making the original primary the primary again.

Why this answer

After an automatic failover, the secondary region becomes the primary. To restore the original primary and re-establish the failover relationship, you must perform a planned failover (also called a graceful failover) from the current primary (the former secondary) to the original primary. This switches roles back without data loss.

Option A is incorrect because deleting and recreating the failover group is unnecessary and disruptive. Option B is incorrect because you cannot add the original primary as a new secondary if it is already part of the failover group—it must be removed first. Option D is incorrect because removing and re-adding the original primary would break the geo-replication and is not the correct procedure.

Full explanation →
255
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 IPs reduces attack surface.

Why this answer

Azure SQL Database firewall rules allow you to restrict access to specific IP address ranges, which is a fundamental network security best practice. By limiting inbound traffic to only trusted IPs, you reduce the attack surface and prevent unauthorized connections from unknown sources. This is a first line of defense in a defense-in-depth strategy 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 →
256
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 tracks query performance and Query Store Hints allow forcing a specific plan to resolve regression.

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 →
257
MCQhard

Your company has an Azure SQL Database configured with active geo-replication to a secondary region. You need to perform a planned failover for disaster recovery testing with zero data loss. The primary database is under heavy write load. What should you do?

A.Set the primary database to read-only, perform a planned failover, then switch the new primary to read-write.
B.Switch the secondary to read-write mode and then failover.
C.Take a full backup of the primary, restore it to the secondary, and then failover.
D.Scale up the primary database to the highest performance tier to handle writes during failover.
AnswerA

Correct: This ensures no writes occur during failover, preserving data.

Why this answer

To achieve zero data loss during a planned failover under heavy write load, you should set the primary database to read-only to block any new writes. This allows the geo-replication to catch up and synchronize all committed transactions to the secondary. Then perform the planned failover (which switches the secondary to become the new primary).

After failover, set the new primary to read-write mode to resume writes. Option B is incorrect: switching the secondary to read-write only enables read workloads on the secondary; it does not trigger a failover. The failover must be explicitly initiated, and the write-heavy primary still has pending transactions.

Option C is incorrect: a full backup and restore would cause data loss because any transactions after the backup would be lost. Geo-replication provides near-real-time synchronization. Option D is incorrect: scaling up the primary does not affect the failover process or guarantee zero data loss.

The only way to ensure zero data loss is to prevent writes and synchronize before failover.

Full explanation →
258
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 →
259
Multi-Selecthard

You are designing a secure architecture for Azure SQL Managed Instance. You need to ensure that all connections to the instance are encrypted and that the instance can only be accessed from a specific virtual network. Which TWO configurations should you implement?

Select 2 answers
A.Configure a service endpoint on the subnet of the managed instance.
B.Deploy the managed instance outside a virtual network.
C.Configure the managed instance with the 'Force encryption' property set to Enabled.
D.Attach a private endpoint to the managed instance.
E.Deploy the managed instance inside the desired virtual network.
AnswersC, E

This enforces encrypted connections.

Why this answer

Setting the 'Force encryption' property to Enabled on Azure SQL Managed Instance ensures that all client connections are encrypted using TLS, enforcing encryption in transit. This is a critical security control to protect data from interception or tampering during transmission.

Exam trap

The trap here is that candidates often confuse service endpoints or private endpoints with the VNet injection requirement for SQL Managed Instance, mistakenly thinking they can use those features to restrict network access, when in fact the instance must be deployed inside the VNet and 'Force encryption' must be enabled for encryption enforcement.

Full explanation →
260
MCQmedium

You manage a SQL Server on Azure VM. Users report slow queries during peak hours. You suspect memory pressure. Which metric should you monitor in Azure Monitor to confirm this?

A.Page Life Expectancy
B.Batch Requests/sec
C.Buffer Cache Hit Ratio
D.Target Server Memory (KB)
AnswerD

Target Server Memory (KB) shows the amount of memory SQL Server is trying to acquire; consistently high values relative to Total Server Memory indicate memory pressure.

Why this answer

Target Server Memory (KB) shows the amount of memory SQL Server is trying to acquire; consistently high values relative to Total Server Memory indicate memory pressure. Option A is wrong because Page Life Expectancy is affected by memory but is a secondary indicator. Option B is wrong because Batch Requests/sec measures throughput, not memory.

Option C is wrong because Buffer Cache Hit Ratio indicates cache efficiency but not directly memory pressure.

Full explanation →
261
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 →
262
MCQmedium

You manage an Azure SQL Database that uses Azure SQL Database elastic jobs to execute maintenance scripts across multiple databases. You need to ensure that job execution is logged in a central database for auditing and troubleshooting. What should you configure?

A.Configure the job to write logs to Azure Table Storage using a custom output.
B.Enable SQL Server Audit on the target databases to capture job execution.
C.Ensure the Elastic Job agent uses a dedicated job database to store job execution logs.
D.Set up Azure Monitor diagnostic settings for the elastic job agent.
AnswerC

Elastic jobs log execution details in the job database by default.

Why this answer

Azure SQL Database elastic jobs automatically store job execution history in a dedicated job database, which is specified when the job agent is created. This database serves as the central repository for job logs, making it the appropriate location for auditing and troubleshooting. Option A is incorrect because elastic jobs log to the job database, not Azure Table Storage.

Option B is incorrect because SQL Server Audit is used for auditing database events, not for capturing job execution logs. Option D is incorrect because Azure Monitor diagnostic settings can be used for additional monitoring but are not the central logging mechanism for elastic jobs.

Full explanation →
263
MCQmedium

Refer to the exhibit. You are reviewing an Azure Automation runbook configuration that performs backups. What is the most likely issue with this configuration?

A.The runbook is disabled (isEnabled: false).
B.The storage account type Standard_LRS is not suitable for backups.
C.The backup description is missing required information.
D.The retention period (7 days) equals the backup interval (7 days), which could cause data loss if a backup fails.
AnswerD

If a backup fails, the previous backup might be deleted before a new one succeeds.

Why this answer

The retention period of 7 days matches the backup interval of 7 days, meaning each backup is retained exactly until the next backup. If a backup fails, there is no overlap and the previous backup may have already been deleted, leading to data loss. Option A is wrong because the runbook is enabled (isEnabled: true).

Option B is wrong because Standard_LRS is suitable for backups. Option C is wrong because the description is not the issue.

Full explanation →
264
MCQhard

You are automating the scaling of an Azure SQL Database based on workload patterns. The database uses the DTU purchasing model. You need to ensure that scaling actions do not cause connection drops. Which scaling method should you use?

A.Create a new database at the target size and use point-in-time restore to copy data.
B.Scale using the ALTER DATABASE T-SQL command with the ONLINE option.
C.Use Elastic Database Jobs to schedule scaling during maintenance windows.
D.Use the Azure portal or PowerShell to change the service tier within the same DTU tier (e.g., S2 to S3) to avoid downtime.
AnswerD

Within the same DTU tier (Standard, Premium), scaling is online and does not drop connections.

Why this answer

Azure SQL Database supports online scaling without connection drops when scaling within the same DTU tier (e.g., from S2 to S3). This is because the database remains online and the operation is a metadata change. Option D is correct.

Option A is incorrect: creating a new database and using point-in-time restore does not scale the existing database and causes downtime during restore. Option B is incorrect: ALTER DATABASE with ONLINE option is for file operations, not for scaling service tiers. Option C is incorrect: Elastic Database Jobs are for scheduling scripts across databases, not for scaling operations.

Full explanation →
265
MCQmedium

You are implementing row-level security (RLS) in Azure SQL Database to restrict access to sales data based on the user's Azure AD identity. Which function should you use in the security policy?

A.USER_NAME()
B.SESSION_USER()
C.SUSER_SNAME()
D.CURRENT_USER()
AnswerA

Correct: Returns the database user name, which can be an Azure AD user.

Why this answer

In Azure SQL Database, row-level security (RLS) predicates must evaluate to a Boolean value based on the current user's identity. USER_NAME() returns the database principal name derived from the Azure AD token, which is the correct function to use when filtering rows by the user's Azure AD identity. The other functions either return the login name, the session user, or the current context user, which may not reflect the actual Azure AD user in an RLS predicate.

Exam trap

The trap here is that candidates often confuse USER_NAME() with SUSER_SNAME() or CURRENT_USER(), mistakenly thinking that the server-level login name or the current context user is the correct identity for row-level filtering, when in fact RLS in Azure SQL Database requires the database-level user name returned by USER_NAME().

How to eliminate wrong answers

Option B (SESSION_USER()) is wrong because it returns the name of the current user in the current session, which is equivalent to USER_NAME() in many contexts but is not the recommended function for RLS predicates in Azure SQL Database; it can be ambiguous when impersonation is used. Option C (SUSER_SNAME()) is wrong because it returns the SQL Server login name (e.g., the Azure AD service principal or SQL authentication login), not the database user name, and RLS predicates operate at the database user level, not the server login level. Option D (CURRENT_USER()) is wrong because it returns the name of the current user in the current security context, which may be the same as USER_NAME() but is typically used in T-SQL for DEFAULT constraints or permissions, not for RLS predicates; it does not reliably reflect the Azure AD user identity in all scenarios.

Full explanation →
266
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 receives the public IP of the VM, so the rule with private IP does not match.

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 →
267
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 only user databases, not master database logins.

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 →
268
MCQhard

You have an Azure SQL Database with a table that stores historical data. To improve query performance, you create a nonclustered columnstore index on the table. However, after a large data load, you notice that query performance is worse than before. What is the most likely reason?

A.The columnstore index is fragmented and needs to be rebuilt.
B.The data was loaded in small batches, causing rowgroups to be smaller than optimal.
C.The table has too many columns included in the index.
D.The index was created with compression disabled.
AnswerB

Small rowgroups reduce compression and query performance.

Why this answer

When data is loaded in small batches into a columnstore index, each batch may form its own rowgroup. Rowgroups with fewer than 102,400 rows are considered 'delta rowgroups' and are not compressed into the columnstore format, leading to poor compression and suboptimal query performance. The large data load likely consisted of many small batches, resulting in numerous small rowgroups that degrade scan performance.

Exam trap

The trap here is that candidates often assume any performance degradation after an index creation is due to fragmentation (Option A), but the DP-300 exam specifically tests the understanding that columnstore indexes require large batch loads to achieve optimal rowgroup size and compression.

How to eliminate wrong answers

Option A is wrong because columnstore index fragmentation is not the primary issue; fragmentation occurs from deletes and updates, not from small batch loads. Option C is wrong because including too many columns in a columnstore index does not inherently cause worse performance; columnstore indexes are designed to handle many columns efficiently by storing each column separately. Option D is wrong because columnstore indexes always use columnstore compression by default; compression cannot be disabled, and even if it could, disabling compression would not cause worse performance—it would reduce storage savings but not degrade query speed.

Full explanation →
269
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 →
270
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

This ensures all connections use TLS 1.2, encrypting data in transit.

Why this answer

Configuring the server to enforce TLS 1.2 by setting the 'Minimal TLS Version' property ensures that all connections to Azure SQL Database use at least TLS 1.2, which encrypts data in transit. This directly addresses the requirement to encrypt sensitive data between the application and the database. Option E is correct because Always Encrypted with a column master key stored in Azure Key Vault ensures that sensitive data is encrypted at the client side and the encryption keys are never exposed to the database engine, preventing DBAs from viewing the sensitive 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 →
271
MCQhard

You have an Azure SQL Managed Instance configured with a failover group between the primary region (East US) and secondary region (West US). The secondary instance is used for read-only query workloads. During a planned failover test, you notice that after failover, the read-only queries on the secondary instance fail with error 4060 (cannot open database). What is the most likely cause?

A.The secondary instance is not in the same license type as the primary.
B.The firewall rules on the secondary instance do not allow the client IP.
C.The failover changed the secondary instance to primary, and read-only routing is not configured for the new secondary.
D.The secondary instance is not configured to allow read-only access.
AnswerC

After failover, the old secondary becomes primary; read-only routing must point to the new secondary (the old primary).

Why this answer

After failover, the secondary role changes to primary, and the database becomes writable. Read-only routing must be reconfigured or the connection string must specify ApplicationIntent=ReadOnly to reach the new secondary. Option A (secondary is not readable) is false because failover groups support readable secondary.

Option B (firewall rules) would cause a different error. Option D (license type) does not affect read access.

Full explanation →
272
Multi-Selecthard

You have a SQL Server on Azure VM that hosts a mission-critical database. The VM is in a single availability zone. You need to achieve a 99.99% SLA for the SQL Server instance. Which two actions should you perform? (Choose two.)

Select 2 answers
A.Configure SQL Server Always On availability group with synchronous replication.
B.Enable geo-redundant backup storage.
C.Configure a failover cluster instance using Azure shared disks.
D.Use a single VM with premium SSD disks.
E.Deploy two Azure VMs in an availability set.
AnswersA, E

Always On availability group provides database-level high availability.

Why this answer

To achieve a 99.99% SLA for SQL Server on Azure VM, you need to ensure high availability. Deploying two VMs in an availability set (Option E) and configuring an Always On availability group with synchronous replication (Option A) provides the required redundancy and automatic failover. Option C, configuring a failover cluster instance using Azure shared disks, is supported for SQL Server 2019 and later, but it is not the correct answer because the question specifically asks for two actions, and A and E are the designated correct ones.

Option B (geo-redundant backup) does not affect availability. Option D (single VM with premium SSD) does not achieve 99.99% because it lacks redundancy.

Full explanation →
273
MCQmedium

You are the database administrator for a large e-commerce company. The company has a SQL Server 2019 Enterprise Edition instance running on-premises with multiple databases. One of the databases, 'OrdersDB', is 2 TB in size and has a high transaction rate. The company plans to migrate 'OrdersDB' to Azure SQL Managed Instance to reduce operational overhead. The migration must have minimal downtime, and the target service tier must be Business Critical with 16 vCores. You have set up a test environment and performed a successful test migration using the Azure Database Migration Service (DMS) with online mode. During the final migration, the network bandwidth between on-premises and Azure is limited to 100 Mbps. The migration is taking longer than expected, and you are concerned about the log backup growth. What should you do to minimize the risk of the migration failing due to log space exhaustion?

A.Switch to offline migration mode to avoid log growth issues.
B.Pause the migration and request a higher network bandwidth from the ISP.
C.Reduce the Azure SQL Managed Instance storage performance tier to standard to reduce cost.
D.Increase the frequency of transaction log backups on the source database to keep the log small.
AnswerD

Frequent log backups prevent log file from growing too large during migration.

Why this answer

Increasing the frequency of transaction log backups on the source database reduces the size of the active log, preventing log space exhaustion during the long-running online migration. The Azure Database Migration Service (DMS) in online mode continuously captures and applies log changes, so a smaller log reduces the risk of the log file filling up before backups can be taken, especially under constrained network bandwidth.

Exam trap

The trap here is that candidates may think offline migration is the only way to avoid log growth, but the question explicitly requires minimal downtime, making online mode mandatory, and the correct solution is to manage the log backup frequency rather than changing the migration mode or bandwidth.

How to eliminate wrong answers

Option A is wrong because switching to offline migration mode would require taking the source database offline, causing significant downtime, which contradicts the requirement for minimal downtime. Option B is wrong because pausing the migration and requesting higher bandwidth does not address the immediate risk of log space exhaustion; it only addresses the slow transfer speed, and bandwidth upgrades are not a quick or guaranteed solution. Option C is wrong because reducing the Azure SQL Managed Instance storage performance tier to standard does not affect log growth on the source database and could degrade target performance, potentially causing migration failures due to throttling or timeouts.

Full explanation →
274
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 cause table scans, increasing CPU usage; adding indexes reduces CPU.

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 →
275
Multi-Selecthard

Which TWO features can help you monitor and troubleshoot a query performance issue in Azure SQL Database without making changes to the application code?

Select 2 answers
A.Using sp_setapprole to set application roles.
B.Enabling Automatic Tuning to force plan corrections.
C.Implementing Always Encrypted for sensitive columns.
D.Using Azure Data Migration Assistant for performance assessment.
E.Enabling Query Store and reviewing regressed queries.
AnswersB, E

Automatic Tuning works without code changes.

Why this answer

Automatic Tuning in Azure SQL Database can automatically identify and force plan corrections for regressed query execution plans without requiring any application code changes. It uses the Query Store to detect plan regressions and applies the last known good plan, improving query performance transparently. Option E is correct because Query Store captures query execution statistics and plan history, allowing you to review regressed queries and manually force a better plan without modifying the application.

Exam trap

The trap here is that candidates may confuse features that improve security (Always Encrypted, application roles) with performance monitoring tools, or assume that migration tools (Data Migration Assistant) are used for ongoing performance troubleshooting.

Full explanation →
276
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 credential holds the SAS token for authentication.

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 →
277
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 supports SQL Server Agent, so the job can run with minimal changes.

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 →
278
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

Restricts traffic to a specific VNet.

Why this answer

A Virtual Network service endpoint extends your virtual network private address space and the identity of your VNet to Azure SQL Database over a direct connection. This allows you to secure access by limiting connectivity to only traffic originating from a specific subnet in your VNet, effectively blocking public internet access while using the Azure backbone network.

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 →
279
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

Defender for SQL provides advanced threat protection and alerts on suspicious activities like SQL injection.

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 →
280
MCQhard

You are the database administrator for a SaaS company that uses Azure SQL Database. The company has a new requirement to audit all SELECT operations on a specific table containing sensitive customer data. You enable auditing on the server and configure a storage account for audit logs. However, after 24 hours, you notice that no SELECT operations are captured in the audit logs. You verify that the table is being accessed frequently. What is the most likely cause?

A.The storage account key was rotated and the audit configuration is using an expired key
B.The audit policy is not configured to capture SELECT operations; only UPDATE, INSERT, and DELETE are captured
C.The client application is using a connection string that bypasses the server firewall
D.The storage account is in a different region than the SQL Database server
AnswerB

By default, auditing captures data manipulation, not SELECT. You need to enable schema object access auditing.

Why this answer

The default server-level audit policy in Azure SQL Database captures only data manipulation language (DML) operations like UPDATE, INSERT, and DELETE, not SELECT queries. To audit SELECT operations, you must explicitly configure a database-level audit action group such as DATABASE_OBJECT_ACCESS_GROUP or SCHEMA_OBJECT_ACCESS_GROUP, or use a custom audit action like SELECT on the specific table. Without this configuration, SELECT operations are not recorded in the audit logs, even if server auditing is enabled and the storage account is properly configured.

Exam trap

The trap here is that candidates assume enabling server-level auditing automatically captures all database operations, including SELECT queries, when in reality SELECT operations require explicit database-level audit configuration.

How to eliminate wrong answers

Option A is wrong because if the storage account key were expired, the audit logs would fail to write entirely, not just miss SELECT operations; the logs would show errors or be empty for all operations, not just SELECT. Option C is wrong because the client application bypassing the server firewall would prevent any connection to the database, making it impossible for SELECT operations to occur; the question states the table is being accessed frequently, so the firewall is not the issue. Option D is wrong because the storage account being in a different region does not affect the capture of audit events; Azure SQL Database can write audit logs to a storage account in any region, and this would not selectively filter out SELECT operations.

Full explanation →
281
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

Setting an Entra ID administrator enables Entra ID authentication for the server.

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 →
282
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 supports JSON storage and querying.

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 →
283
MCQmedium

Your organization has multiple Azure SQL Databases in an elastic pool. You need to monitor the performance of the pool and identify which databases are consuming the most resources. You want to view historical resource usage for the past month. Which tool should you use?

A.Azure SQL Analytics (Azure Monitor)
B.Azure portal metrics for the elastic pool
C.SQL Server Profiler
D.Query Performance Insight
AnswerA

Provides historical metrics for elastic pools and databases.

Why this answer

Azure SQL Analytics (Azure Monitor) is the correct tool because it provides built-in monitoring and alerting for elastic pools, including historical resource usage (CPU, DTU, storage) for up to 30 days. It aggregates data across all databases in the pool and allows you to drill down into individual database consumption, making it ideal for identifying top resource consumers over the past month.

Exam trap

The trap here is that candidates often confuse Query Performance Insight (which is per-database and query-focused) with Azure SQL Analytics (which is pool-wide and resource-focused), leading them to choose D instead of A.

How to eliminate wrong answers

Option B is wrong because Azure portal metrics for the elastic pool only show real-time or near-real-time metrics (up to 30 days of data retention) but lack the pre-built historical analysis and per-database breakdown that Azure SQL Analytics offers; they are more suited for ad-hoc monitoring. Option C is wrong because SQL Server Profiler is a client-side tracing tool for capturing individual query events (e.g., stored procedure calls, deadlocks) and is not designed for historical resource usage analysis of an elastic pool. Option D is wrong because Query Performance Insight focuses on individual database query performance (e.g., top queries by CPU, duration) and does not provide aggregated historical resource usage across an entire elastic pool.

Full explanation →
284
Multi-Selecthard

You are monitoring an Azure SQL Database and notice that the 'tempdb' database is experiencing contention. Which THREE actions can reduce tempdb contention? (Choose three.)

Select 3 answers
A.Add multiple tempdb data files
B.Optimize queries that use temporary tables and table variables
C.Increase the size of tempdb
D.Change the collation of tempdb
E.Use multiple tempdb files equal to the number of CPU cores
AnswersA, B, E

Multiple files reduce allocation contention.

Why this answer

Adding multiple tempdb data files reduces allocation contention on system pages like PFS (Page Free Space), GAM (Global Allocation Map), and SGAM (Shared Global Allocation Map). When multiple sessions concurrently allocate pages in tempdb, a single data file becomes a bottleneck; additional files spread the allocation load across separate file system objects, reducing latch contention.

Exam trap

The trap here is that candidates often confuse 'increasing size' (Option C) with 'adding files' (Option A), mistakenly thinking that more space alone resolves contention, when in fact only multiple files reduce allocation page contention.

Full explanation →
285
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 and automatically identifies and fixes regressed query plans.

Why this answer

Automatic Tuning can automatically identify and fix regressed query plans. Option A is wrong because Query Store captures performance data but does not automatically fix plans. Option C is wrong because Intelligent Insights provides monitoring, not automatic fixing.

Option D is wrong because Database Advisor for SQL Database gives recommendations but requires manual action.

Full explanation →
286
MCQeasy

Your company uses Azure SQL Database in a General Purpose service tier. You need to minimize downtime during a planned patching event. Which feature should you enable?

A.Auto-failover group
B.Read scale-out
C.Active geo-replication
D.Zone-redundant configuration
AnswerD

Provides high availability across zones, minimizing patching downtime.

Why this answer

Zone-redundant configuration for Azure SQL Database provides high availability by replicating across zones, minimizing downtime during planned patching events. Option A is incorrect because auto-failover groups are primarily for disaster recovery across regions. Option B is incorrect because read scale-out only offloads read workloads to a readable secondary replica, which does not reduce downtime during patching of the primary.

Option C is incorrect because active geo-replication is for disaster recovery, not for reducing downtime during patching within a region.

Full explanation →
287
MCQmedium

Your team uses GitHub Actions for CI/CD. You need to automatically deploy schema changes to an Azure SQL Database after a pull request merge. The solution must use a service principal with minimal permissions. Which authentication method should the workflow use?

A.Service principal with a client secret stored in GitHub Secrets.
B.Azure CLI action using a user-assigned managed identity for the GitHub runner.
C.Environment variables containing the connection string.
D.SQL authentication using a user name and password stored in GitHub Secrets.
AnswerA

Correct. A service principal with a client secret stored in GitHub Secrets provides a secure, automated way to authenticate to Azure SQL Database for deployment, and meets the requirement of using a service principal with minimal permissions.

Why this answer

It uses a service principal with a client secret stored in GitHub Secrets, which meets the requirement of using a service principal with minimal permissions. Using a service principal is the appropriate authentication method for an automated CI/CD deployment with Azure SQL Database. Option B is incorrect because a user-assigned managed identity is not a service principal; it is a different type of identity that does not satisfy the requirement for a service principal.

Option C is incorrect because storing connection strings in environment variables is insecure and does not use a service principal. Option D is incorrect because SQL authentication does not involve a service principal and is less secure than Azure AD-based methods.

Full explanation →
288
MCQmedium

You have an Azure SQL Database in the Premium tier with a failover group to a secondary region. The primary database experiences a performance issue due to a sudden increase in workload. You need to temporarily offload read-only queries to the secondary replica without affecting the failover group configuration. What should you do?

A.Use the secondary database in the failover group for read-only queries by setting ApplicationIntent=ReadOnly.
B.Configure active geo-replication to a new secondary in the same region.
C.Enable read scale-out on the primary database.
D.Scale up the primary database to a higher performance level.
AnswerA

The failover group secondary supports read-only workloads.

Why this answer

The secondary database in a failover group for Premium-tier Azure SQL Database is readable. By setting ApplicationIntent=ReadOnly in the connection string, read-only queries are routed to this secondary replica, offloading read workloads without modifying the failover group configuration. Option B is incorrect because active geo-replication would create a separate secondary outside the failover group, which is unnecessary and adds complexity.

Option C is incorrect because read scale-out uses local replicas within the same region, not the failover group secondary. Option D is incorrect because scaling up the primary does not offload read queries; it only increases resources for all workloads.

Full explanation →
289
Multi-Selectmedium

You manage an Azure SQL Database in the Business Critical service tier. The database must be highly available within a single region. Which TWO features contribute to this high availability?

Select 2 answers
A.Active geo-replication
B.Multiple synchronous replicas (4 replicas)
C.Always On availability groups
D.Auto-failover groups
E.Zone-redundant configuration (when enabled)
AnswersB, E

Business Critical includes 4 replicas for HA.

Why this answer

The correct answers are B and E. In the Business Critical service tier, multiple synchronous replicas (at least 4) provide local high availability within a region. Additionally, enabling zone-redundant configuration distributes these replicas across availability zones within the same region, further improving availability.

Options A and D (Active geo-replication and Auto-failover groups) are cross-region disaster recovery features and do not contribute to intra-region high availability. Option C (Always On availability groups) is the underlying technology for the replicas but is not a separate feature; the question asks for features that contribute to high availability, and the replicas themselves are the feature.

Full explanation →
290
MCQeasy

You need to monitor the performance of a set of Azure SQL Databases from a single dashboard. Which Azure service should you use?

A.Azure Monitor Workbooks
B.Query Performance Insight
C.Azure SQL Analytics
D.Azure Data Studio
AnswerC

Azure SQL Analytics provides a unified monitoring solution for multiple Azure SQL databases.

Why this answer

Azure SQL Analytics provides a unified monitoring solution for multiple Azure SQL databases. Option C is correct. Option A (Azure Monitor Workbooks) is wrong because although it can be used, it requires manual configuration.

Option B (Query Performance Insight) is wrong because it is per-database. Option D (Azure Data Studio) is wrong because it is a client tool.

Full explanation →
291
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

Azure AD with MFA provides strong authentication.

Why this answer

Azure AD authentication with multi-factor authentication (MFA) provides strong identity-based security, eliminating password vulnerabilities and supporting conditional access policies. This method integrates with Azure SQL Database's built-in support for Azure AD tokens, ensuring that only authenticated users with MFA can connect, which meets the exam's focus on secure environment implementation.

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 →
292
MCQmedium

A company runs a critical SQL Server database on Azure Virtual Machines. The database experiences intermittent performance degradation during peak hours. The database administrator notices high I/O latency on the data drive. The current disk configuration uses Standard HDDs. What is the most cost-effective action to reduce I/O latency without changing the virtual machine size?

A.Enable instant file initialization in SQL Server.
B.Increase the virtual machine size to a series with higher I/O limits.
C.Migrate the data files to Premium SSD managed disks.
D.Migrate the data files to Standard SSD managed disks.
AnswerC

Premium SSD provides lower latency and is cost-effective without changing VM size.

Why this answer

Migrating the data files to Premium SSD managed disks is the most cost-effective action to reduce I/O latency without changing the virtual machine size. Premium SSDs provide consistent low-latency performance and higher IOPS/throughput compared to Standard HDDs, directly addressing the high I/O latency during peak hours. This change does not require resizing the VM, keeping compute costs unchanged while improving storage performance.

Exam trap

The trap here is that candidates may choose Standard SSD (Option D) thinking it is a sufficient upgrade, but they overlook that Premium SSD is required to achieve the low latency needed for critical databases, and that Standard SSD still uses HDD-like latency profiles under sustained load.

How to eliminate wrong answers

Option A is wrong because enabling instant file initialization only speeds up data file growth operations by skipping zeroing, but it does not reduce I/O latency for ongoing read/write operations during peak hours. Option B is wrong because increasing the virtual machine size to a series with higher I/O limits would increase compute costs and is explicitly excluded by the requirement to not change the VM size. Option D is wrong because Standard SSD managed disks offer better performance than Standard HDDs but still have higher latency and lower IOPS than Premium SSDs, making them insufficient to resolve high I/O latency in a critical database scenario.

Full explanation →
293
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

Guarantees resources per database while sharing.

Why this answer

Configuring minimum and maximum eDTU per database within the elastic pool guarantees that each database gets the resources it needs during peak usage (e.g., high CPU for marketing during the day, high IO for sales at night) while still sharing the pool's overall eDTU, optimizing costs. Option B is incorrect because moving to a vCore-based pool does not inherently address resource contention; it's a different purchasing model. Option C is incorrect because adding more databases to the pool increases contention and may degrade performance.

Option D is incorrect because moving each database to a standalone DTU tier would likely increase costs and eliminate the benefits of pooling.

Full explanation →
294
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 →
295
MCQhard

You are migrating a large on-premises SQL Server database (5 TB) to Azure SQL Managed Instance. The network bandwidth between your on-premises data center and Azure is limited to 100 Mbps. You need to minimize the migration time. Which approach should you use?

A.Use Azure Data Box to physically transfer the data.
B.Use transaction log shipping to continuously sync changes.
C.Export as BACPAC and upload to Azure Blob Storage, then import.
D.Use Azure Database Migration Service (DMS) online migration.
AnswerA

Data Box bypasses network limitations.

Why this answer

Azure Data Box is the correct approach because it physically transfers the 5 TB database via a secure storage device, bypassing the 100 Mbps network bottleneck. At 100 Mbps, a 5 TB transfer would take approximately 5.5 days continuously, while Data Box can complete the transfer in a few days including shipping, making it the fastest option for such a large dataset over a limited bandwidth link.

Exam trap

The trap here is that candidates often overestimate the speed of online migration services like DMS or BACPAC, failing to calculate that at 100 Mbps, a 5 TB transfer takes over 5 days, making physical shipping via Data Box the faster choice despite the initial impression that 'online' is always quicker.

How to eliminate wrong answers

Option B is wrong because transaction log shipping requires an initial full backup transfer over the network, which would still be bottlenecked by the 100 Mbps link, and continuous log shipping does not minimize the initial migration time. Option C is wrong because exporting a 5 TB database as a BACPAC file is extremely slow due to the serial export process and the subsequent upload over 100 Mbps would take days, making it impractical for minimizing migration time. Option D is wrong because Azure Database Migration Service online migration relies on continuous data replication over the network, which is constrained by the 100 Mbps bandwidth and would take an excessively long time for the initial sync of 5 TB.

Full explanation →
296
MCQeasy

Your company has an Azure SQL Database in the East US region. You need to configure a disaster recovery solution that provides a readable secondary in the West US region with an RPO of 5 seconds. The solution must support automatic failover from the application. What should you configure?

A.Enable zone-redundant replicas in the same region.
B.Enable geo-redundant backup storage and perform geo-restore.
C.Configure active geo-replication to a secondary server in West US.
D.Create an auto-failover group with a secondary server in West US using synchronous replication.
AnswerD

Auto-failover groups can achieve low RPO with synchronous replication.

Why this answer

Auto-failover groups with a secondary server in another region using synchronous replication can achieve an RPO of 5 seconds and support automatic failover from the application. Option A is wrong because zone-redundant replicas are in the same region and do not provide cross-region DR. Option B is wrong because geo-redundant backup storage and geo-restore are not for automatic failover and have a higher RPO.

Option C is wrong because although active geo-replication provides a readable secondary, it does not support automatic failover; a group is needed.

Full explanation →
297
MCQeasy

You have an Azure SQL Database that must be automatically stopped during weekends to save costs. Which combination of Azure services can achieve this automation?

A.Azure Automation Runbook with PowerShell script using Suspend-AzSqlDatabase cmdlet, scheduled via Automation schedule.
B.Azure Logic Apps with a recurrence trigger and the SQL connector to execute ALTER DATABASE SET SUSPEND.
C.Azure Functions with a timer trigger and the Azure SDK to stop the database.
D.Elastic Database Jobs with a T-SQL script to ALTER DATABASE SET SUSPEND.
AnswerA

Azure Automation Runbooks with the Stop-AzSqlDatabase cmdlet provide a native, straightforward way to stop an Azure SQL Database on a schedule.

Why this answer

Azure Automation Runbooks with the Suspend-AzSqlDatabase cmdlet provide a native, straightforward way to stop an Azure SQL Database on a schedule. Option B is incorrect because Azure Logic Apps with the SQL connector cannot execute ALTER DATABASE SET SUSPEND; suspension is not a T-SQL operation for Azure SQL Database. Option C is incorrect because Azure Functions can achieve this but require more custom code and setup compared to the simpler Automation Runbook approach.

Option D is incorrect because Elastic Database Jobs are designed for executing T-SQL scripts across multiple databases, not for stopping or starting databases.

Full explanation →
298
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 this setting disables SQL authentication and enforces Microsoft Entra authentication for all connections.

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 →
299
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

The service automatically fails over to a secondary replica within seconds.

Why this answer

Options A and B are correct. Business Critical includes multiple replicas (one primary and two secondary) that are automatically managed, and zone-redundant configuration is optional but not automatic. Option C is wrong because active geo-replication is a separate DR feature.

Option D is wrong because auto-failover groups are for DR. Option E is wrong because read scale-out is an optional feature, not automatic for HA.

Full explanation →
300
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 allows you to retain backups for up to 10 years with configurable weekly, monthly, yearly schedules.

Why this answer

Long-Term Retention (LTR) policy allows you to retain backups beyond the default retention period, with weekly, monthly, and yearly options. Option A is wrong because geo-restore is for disaster recovery, not long-term retention. Option C is wrong because automated backups have a maximum retention of 35 days.

Option D is wrong because Point-In-Time Restore (PITR) retention only goes up to 35 days and does not support long-term retention.

Full explanation →
Page 3

Page 4 of 13

Page 5

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 →