Courseiva

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

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

Take a mock examExam hub
Page 3

Page 4 of 13

Page 5
226
MCQhard

Your company has a critical Azure SQL Database named SalesDB in the West US region. To meet disaster recovery requirements, you configured active geo-replication with a readable secondary replica in East US. You need to automate failover in the event of a regional outage. The solution must ensure that the failover is initiated automatically based on health metrics and that the application connection string is updated to point to the new primary. Currently, the application uses a connection string with the primary server name. You have the following requirements: minimize manual intervention, use built-in Azure features, and ensure that failover is tested regularly. You consider the following options: A) Use Azure Traffic Manager with endpoint monitoring to route traffic to the primary and automatically failover. B) Configure auto-failover group with read-write failover policy and grace period. C) Create an Azure Logic App that queries the database health and runs a PowerShell script to perform failover and update DNS. D) Use Azure Automation with a runbook that checks the database status and initiates failover via REST API. Which option best meets the requirements?

A.Use Azure Traffic Manager with endpoint monitoring to route traffic to the primary and automatically failover.
B.Create an Azure Logic App that queries the database health and runs a PowerShell script to perform failover and update DNS.
C.Configure auto-failover group with read-write failover policy and grace period.
D.Use Azure Automation with a runbook that checks the database status and initiates failover via REST API.
AnswerC

Auto-failover groups automatically fail over based on health metrics and provide a listener endpoint that updates automatically.

Why this answer

Option C is correct because auto-failover groups are the built-in Azure feature designed specifically for automating failover of Azure SQL Database geo-replication. They support a read-write failover policy with a configurable grace period that automatically triggers failover based on health metrics, and they automatically update the connection string endpoint for applications using the failover group listener name, not the individual server name. This minimizes manual intervention and allows regular testing via manual failover without affecting the production environment.

Exam trap

The trap here is that candidates may think custom automation (Logic App or Automation runbook) is needed for automatic failover, but they overlook that auto-failover groups already provide built-in, policy-driven automatic failover with connection string transparency, which is the simplest and most reliable solution for this scenario.

How to eliminate wrong answers

Option A is wrong because Azure Traffic Manager operates at the DNS level and does not natively understand Azure SQL Database geo-replication health metrics; it cannot automatically trigger a geo-failover or update the database replication state, and it would require custom endpoint monitoring and manual failover scripts. Option B is wrong because creating a Logic App that runs a PowerShell script introduces custom code, manual maintenance, and potential latency, which does not minimize manual intervention or use built-in Azure features as effectively as auto-failover groups. Option D is wrong because Azure Automation with a runbook that checks database status and initiates failover via REST API is a custom solution that requires development, testing, and ongoing management, and it does not provide automatic connection string updates or the built-in grace period and health monitoring of auto-failover groups.

Full explanation →
227
MCQmedium

Your company has an Azure SQL Database in the General Purpose tier. You need to reduce the recovery point objective (RPO) from 1 hour to less than 1 minute for disaster recovery. Which action should you take?

A.Increase the backup retention period to 35 days.
B.Upgrade the database to Business Critical tier.
C.Enable zone redundancy on the database.
D.Increase the DTU or vCore count.
AnswerB

Business Critical uses synchronous replication, achieving RPO of 0.

Why this answer

Option A is correct because upgrading to Business Critical tier enables synchronous replication, which reduces RPO. Option B is wrong because increasing backup retention does not affect replication RPO. Option C is wrong because enabling zone redundancy does not reduce geo-replication RPO.

Option D is wrong because increasing DTU does not change replication mode.

Full explanation →
228
MCQeasy

You are monitoring an Azure SQL Database using sys.dm_db_wait_stats. You see a high percentage of WRITELOG waits. What is the most likely cause?

A.Tempdb has allocation contention.
B.The transaction log is on a slow I/O subsystem.
C.Queries are blocked by locks.
D.CPU is under pressure.
AnswerB

WRITELOG waits directly relate to log write performance.

Why this answer

Option A is correct because WRITELOG waits indicate that transaction log writes are slower than expected, often due to log I/O latency. Option B is wrong because tempdb contention shows PAGELATCH waits. Option C is wrong because blocking shows LCK_M_* waits.

Option D is wrong because CPU pressure shows SOS_SCHEDULER_YIELD.

Full explanation →
229
MCQeasy

You are reviewing an ARM template for creating a new Azure SQL Database. The template uses the above JSON. The source database 'db1' was created on 2025-03-01 and has a current backup retention of 7 days. The restore point is 2025-03-15T10:00:00Z. What will be the result of deploying this template?

A.The deployment will fail because the restore point is older than 7 days.
B.The deployment will succeed but the restore point will be ignored and the latest backup will be used.
C.The deployment will fail because the source database is not geo-replicated.
D.The deployment will succeed and create a new database restored to the specified point in time.
AnswerD

The restore point is valid and within retention.

Why this answer

Option D is correct because the ARM template specifies a restore point of 2025-03-15T10:00:00Z, which is within the 7-day backup retention period (the source database was created on 2025-03-01, so backups are available from 2025-03-01 to 2025-03-22). Azure SQL Database supports point-in-time restore (PITR) to any point within the configured backup retention window, and the deployment will create a new database restored to that exact timestamp. The restore point is valid and the template will succeed.

Exam trap

The trap here is that candidates mistakenly think the 7-day retention period is counted backward from the current date, when in fact it is counted forward from the database creation date, making a restore point 14 days after creation valid as long as it falls within the retention window.

How to eliminate wrong answers

Option A is wrong because the restore point 2025-03-15T10:00:00Z is only 14 days after the database creation date (2025-03-01), which is well within the 7-day backup retention period (backups are retained from creation date forward, not backward). Option B is wrong because the restore point is valid and will be honored; Azure SQL Database does not ignore a specified restore point within the retention window. Option C is wrong because geo-replication is not a prerequisite for point-in-time restore; PITR works on any single database regardless of geo-replication status.

Full explanation →
230
MCQhard

You are responsible for a SQL Server 2019 instance on an Azure VM. The VM is part of a failover cluster instance (FCI) using Azure shared disks. During a recent failover test, the cluster took 15 minutes to bring the database online. You need to reduce the failover time to under 5 minutes. What should you do?

A.Remove unnecessary databases from the FCI and distribute them to other instances.
B.Upgrade the VM to a larger size with more CPU and memory.
C.Increase the size of the Azure shared disks.
D.Enable instant file initialization on all SQL Server instances.
AnswerA

Fewer databases to recover reduces failover time.

Why this answer

Option C is correct because reducing the number of databases in the FCI reduces the time to recover each database during failover. Option A is wrong because increasing the disk size does not improve failover time. Option B is wrong because increasing VM size may help but not directly address the root cause.

Option D is wrong because enabling instant file initialization helps with data file growth, not failover recovery.

Full explanation →
231
MCQmedium

You have an Azure SQL Database in the Business Critical tier without zone redundancy. You need to configure a disaster recovery plan that ensures the database is available in a different Azure region within 1 hour of a regional outage. The solution must minimize data loss and not require manual intervention. What should you do?

A.Configure active geo-replication to a secondary region and enable auto-failover groups.
B.Create a failover group between two servers in different regions.
C.Use geo-restore of the latest backup.
D.Configure copy-only backups to a secondary region.
AnswerA

Active geo-replication with auto-failover groups provides automatic failover and minimal data loss.

Why this answer

Option A is correct because active geo-replication provides asynchronous replication to a secondary region with a configurable failover policy, minimizing data loss and enabling automatic failover if configured with auto-failover groups. Option B (failover group) is for managed instances, not single databases. Option C (geo-restore) has a longer RTO and potential data loss.

Option D (copy-only backup) is not a disaster recovery solution.

Full explanation →
232
MCQeasy

You are monitoring an Azure SQL Database using dynamic management views (DMVs). You want to identify the top queries by total CPU time over the last hour. Which DMV should you query?

A.sys.dm_exec_query_stats
B.sys.dm_exec_requests
C.sys.dm_db_index_usage_stats
D.sys.dm_db_resource_stats
AnswerA

Returns aggregate performance statistics for cached query plans.

Why this answer

Option B is correct because sys.dm_exec_query_stats provides aggregated performance statistics including CPU time. Option A is wrong because it shows currently executing queries, not historical. Option C is wrong because it shows resource governance stats.

Option D is wrong because it shows index usage.

Full explanation →
233
MCQeasy

You need to prevent users from accidentally deleting an Azure SQL Database. What should you configure?

A.Apply a 'CanNotDelete' Azure Resource Lock on the resource group.
B.Revoke the db_ddladmin role from users.
C.Create an Azure Policy to deny SQL Database creation.
D.Set a deny rule in the SQL Database firewall.
AnswerA

Resource lock prevents deletion of Azure resources.

Why this answer

A 'CanNotDelete' Azure Resource Lock on the resource group prevents any user, including those with high-level permissions like Owner, from deleting the Azure SQL Database. This lock overrides all role-based access control (RBAC) permissions at the resource or resource group level, ensuring accidental deletion is blocked even if a user has delete permissions.

Exam trap

The trap here is that candidates confuse database-level permissions (like db_ddladmin) with Azure Resource Manager-level operations, mistakenly thinking that revoking schema modification rights will prevent database deletion, when in fact deletion is an ARM operation controlled by locks or RBAC at the subscription/resource group scope.

How to eliminate wrong answers

Option B is wrong because revoking the db_ddladmin role prevents users from modifying the database schema (e.g., creating or altering tables), but it does not prevent deletion of the database itself, which is an Azure Resource Manager (ARM) operation, not a SQL Server-level operation. Option C is wrong because creating an Azure Policy to deny SQL Database creation prevents new databases from being provisioned, but it does not protect an existing database from being deleted. Option D is wrong because setting a deny rule in the SQL Database firewall controls network access to the database (blocking IP addresses), but it has no effect on the ability to delete the database resource via ARM.

Full explanation →
234
MCQhard

You are reviewing an Azure Resource Manager template snippet for configuring long-term backup retention for an Azure SQL Database. The deployment fails with an error indicating the storage account is not accessible. What is the most likely cause?

A.The server name in the template does not match the actual server
B.The storage container URI is incorrectly formatted
C.The SAS token has expired or is invalid
D.The database is not in the same region as the storage account
AnswerC

Expired or invalid SAS token is a common cause of access errors.

Why this answer

The exhibit shows a SAS token in the template. The most common issue is that the SAS token has expired or is invalid. Option B is correct.

Option A is wrong because the storage container URI is valid. Option C is wrong because the location is valid. Option D is wrong because the SAS token is used for authentication.

Full explanation →
235
MCQhard

Refer to the exhibit. You are troubleshooting an Azure SQL Database auditing configuration. The exhibit shows the blob auditing policy. The storage account access key is null, and the subscription ID is all zeros. What is the most likely issue?

A.Auditing will fall back to Log Analytics workspace.
B.Auditing will work because managed identity is used.
C.Auditing will fail because the storage account access key is null.
D.Auditing will write to the storage account using the system-assigned managed identity.
AnswerC

Blob auditing requires a storage account key or a valid subscription ID with appropriate permissions; null key indicates misconfiguration.

Why this answer

The exhibit shows that the storage account access key is null, which means Azure SQL Database cannot authenticate to the storage account using the access key. Without a valid access key or a configured managed identity, blob auditing will fail because the database cannot write audit logs to the specified storage container. Option C is correct because a null access key directly prevents auditing from functioning when no alternative authentication method is configured.

Exam trap

The trap here is that candidates assume managed identity is automatically used when the access key is null, but in reality, managed identity must be explicitly configured and granted permissions, and the exhibit shows no such configuration.

How to eliminate wrong answers

Option A is wrong because auditing does not automatically fall back to Log Analytics workspace; the audit destination is explicitly set to storage, and if storage fails, auditing fails entirely unless a different destination is configured in the policy. Option B is wrong because managed identity is not automatically used; it must be explicitly enabled and assigned to the SQL Database, and the exhibit shows no indication of a managed identity being configured. Option D is wrong because writing to the storage account using a system-assigned managed identity requires that the managed identity be enabled and that the storage account grants appropriate RBAC permissions (e.g., Storage Blob Data Contributor) to that identity, which is not shown in the exhibit.

Full explanation →
236
MCQmedium

A company runs a critical Azure SQL Database in the West US region. To meet a Recovery Point Objective (RPO) of 5 seconds and a Recovery Time Objective (RTO) of 30 seconds during a regional outage, which deployment option should be used?

A.Use an Auto-Failover Group with a secondary in West US 2
B.Deploy the database in the Business Critical tier with zone-redundant configuration in West US
C.Enable geo-zone-redundant backup storage and perform point-in-time restore
D.Configure Active Geo-Replication with a readable secondary in East US
AnswerB

Zone-redundant Business Critical provides fast failover within seconds and RPO of 5 seconds.

Why this answer

The Business Critical tier with zone-redundant configuration provides synchronous replication of data across three availability zones within the same Azure region, ensuring zero data loss (RPO=0) and automatic failover within seconds. This meets the stringent RPO of 5 seconds and RTO of 30 seconds during a regional outage because zone redundancy protects against zone-level failures, and the failover is automatic and fast. In contrast, other options either introduce asynchronous replication (which cannot guarantee an RPO of 5 seconds) or rely on cross-region failover that exceeds the required RTO.

Exam trap

The trap here is that candidates often assume cross-region replication (Auto-Failover Groups or Active Geo-Replication) is required for regional outages, but the question specifies a regional outage within the same region (West US), where zone-redundant configuration within the same region can meet the aggressive RPO and RTO, whereas cross-region options introduce asynchronous replication and longer failover times.

How to eliminate wrong answers

Option A is wrong because Auto-Failover Groups use asynchronous replication (typically with a lag of 5–30 seconds or more), which cannot guarantee an RPO of 5 seconds, and failover across regions (West US to West US 2) may take longer than 30 seconds due to DNS propagation and transaction log catch-up. Option C is wrong because geo-zone-redundant backup storage and point-in-time restore are designed for data recovery from backups, not for high availability or automatic failover; the RTO would be measured in hours, not seconds, due to the time required to restore a database. Option D is wrong because Active Geo-Replication uses asynchronous replication (typically with an RPO of 5–30 seconds or more), and failover to a secondary in East US would introduce cross-region latency and DNS changes, making it impossible to meet a 30-second RTO.

Full explanation →
237
MCQhard

You are migrating an on-premises SQL Server database to Azure SQL Database. The database uses Service Broker for asynchronous messaging. After migration, you notice that performance is degraded. What should you do to optimize?

A.Enable Service Broker in Azure SQL Database by setting ENABLE_BROKER.
B.Scale up the database to a higher service tier to improve Service Broker performance.
C.Replace Service Broker with Azure Queue Storage or Event Grid.
D.Configure Service Broker to use external activation via Azure Functions.
AnswerC

Azure SQL Database does not support Service Broker; use PaaS messaging services.

Why this answer

Option B is correct because Service Broker is not fully supported in Azure SQL Database; you should use Azure Queue Storage or Event Grid. Option A is wrong because Service Broker cannot be enabled in Azure SQL Database. Option C is wrong because Service Broker uses internal activation, not external.

Option D is wrong because scaling up does not solve the feature incompatibility.

Full explanation →
238
MCQhard

Your company is planning to migrate on-premises SQL Server databases to Azure SQL Managed Instance. The security team requires that all database connections be encrypted and that the server's identity be verified using a certificate from a trusted public certificate authority (CA). What should you configure?

A.Set the 'Force Encryption' property to True for the managed instance.
B.Install a custom CA-signed certificate on the managed instance.
C.Enable Transparent Data Encryption (TDE) with a server certificate.
D.Configure Always Encrypted with column master key in Key Vault.
AnswerA

Correct: This ensures encryption for all connections, and the server certificate is from a trusted CA.

Why this answer

Setting 'Force Encryption' to True on the managed instance enforces TLS encryption for all client connections and ensures the server presents a certificate from a trusted public CA to verify its identity. This meets both requirements: encryption of data in transit and server identity verification via a trusted certificate chain.

Exam trap

The trap here is that candidates confuse encryption in transit (Force Encryption/TLS) with encryption at rest (TDE) or column-level encryption (Always Encrypted), and mistakenly think a custom CA certificate is needed when Azure SQL Managed Instance already uses a trusted public CA certificate by default.

How to eliminate wrong answers

Option B is wrong because Azure SQL Managed Instance automatically provisions a certificate from a trusted public CA (Microsoft's CA) for TLS connections; installing a custom CA-signed certificate is not supported and would not replace the built-in certificate. Option C is wrong because Transparent Data Encryption (TDE) encrypts data at rest, not in transit, and does not address connection encryption or server identity verification. Option D is wrong because Always Encrypted protects sensitive data at the column level with client-side encryption and a column master key in Key Vault, but it does not enforce encryption for all database connections or verify the server's identity via a public CA certificate.

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

Option C is correct because 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 →
240
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

Option D is correct because moving the database to a higher service tier or a separate pool isolates its resource usage and prevents impact on others. Option A is wrong because scaling the pool affects all databases. Option B is wrong because query store does not limit resources.

Option C is wrong because reserved capacity is a billing benefit, not a resource isolation mechanism.

Full explanation →
241
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 →
242
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 →
243
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 →
244
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 exhibit shows maxSizeBytes: 268435456000, which is 250 GB. Option D is correct. Option A is wrong because 4 TB is not the limit shown.

Option B is wrong because 1 TB is incorrect. Option C is wrong because 500 GB is incorrect.

Full explanation →
245
MCQmedium

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

A.Increase the service tier of the Azure SQL Database to provide more resources.
B.Update all statistics on the table.
C.Rebuild the clustered index to reduce fragmentation.
D.Update the statistics on the nonclustered index only.
AnswerB

Updating statistics helps the query optimizer generate a more accurate cardinality estimate, which may lead to an index seek instead of a scan.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

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

Why this order

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

Full explanation →
247
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 →
248
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 →
249
MCQeasy

You are configuring Azure SQL Database automatic tuning. You want to enable the feature that automatically creates and drops indexes based on workload patterns. Which option should you enable?

A.CREATE INDEX
B.Force Parameterization
C.Create Index and Drop Index
D.Force Last Good Plan
AnswerC

Automatic index management based on workload.

Why this answer

Option D is correct because the automatic tuning option for index management is called 'Create Index' and 'Drop Index'. Option A is wrong because 'Force Last Good Plan' is for plan regression. Option B is wrong because 'Force Parameterization' is a different tuning option.

Option C is wrong because 'CREATE INDEX' is a manual command, not an automatic tuning option.

Full explanation →
250
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 →
251
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 →
252
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

Option A is correct because a failover group provides cross-region DR with RPO of 5 minutes and RTO of 1 hour, while zone redundancy (available in Business Critical) provides HA within region with RPO of 0 and RTO of ~30 seconds for planned maintenance. Option B is wrong because active geo-replication is not available for Managed Instance. Option C is wrong because failover groups do not provide sub-minute RTO for planned maintenance alone.

Option D is wrong because zone redundancy alone does not protect against regional failure.

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

Options A and D are correct. Failover groups provide automatic failover (A) and read-only routing (D). Option B is wrong because failover groups do not provide load balancing within a region.

Option C is wrong because failover groups do not replicate data synchronously across zones within a region (zone-redundancy does). Option E is wrong because failover groups do not provide backup redundancy.

Full explanation →
254
MCQeasy

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

A.Configure active geo-replication to a secondary server in West US.
B.Use the geo-restore feature to restore from the geo-redundant backups to a new database in West US.
C.Create a copy-only backup and manually transfer it to West US.
D.Perform a point-in-time restore to a new database in West US.
AnswerB

Geo-restore uses the geo-redundant backup and can restore to any region.

Why this answer

Option B is correct because geo-restore from geo-redundant backups allows restoring to a different region. Option A is wrong because active geo-replication is not configured by default. Option C is wrong because point-in-time restore is for within the same region.

Option D is wrong because copy-only backup is not relevant.

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

Option C is correct because LTR policies are per database, so you need one policy for each database. Option A is wrong because one policy per server is not possible; policies are database-level. Option B is wrong because two policies per server is incorrect.

Option D is wrong because one policy per elastic pool is incorrect.

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

Option A is correct because if the secondary replica is not able to apply log records quickly enough, the log send queue grows. This can happen if the secondary has insufficient resources or is under load. Option B is wrong because direct network latency is low.

Option C is wrong because the primary has ample resources. Option D is wrong because log flush on the primary is not the issue.

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

Option A is correct because 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 E is correct because Azure SQL Database's automatic tuning can handle index and statistics maintenance, reducing manual effort.

Option B is incorrect because enabling auto-failover groups does not automate maintenance tasks. Option D is incorrect because SQL Agent jobs are not available in Azure SQL Database (only in Managed Instance).

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

Option A is correct because partition elimination requires the predicate to be on the partitioning column. In this case, the queries likely do not filter on OrderDate, or the predicate is not sargable. Adding a clustered index on OrderDate would help if the queries filter on OrderDate, but the stem says the queries aggregate by month for the current year, so they likely have a WHERE clause on OrderDate.

If partition elimination is not happening, the most common reason is that the partition function is not aligned with the query predicate. Re-creating the partition function with a different boundary may help if the current boundaries are not matching. However, the best action is to ensure the queries use the partition column in the WHERE clause.

Since we cannot change the code, we can modify the partition function to align with the typical query pattern. Option B is wrong because creating views does not force partition elimination. Option C is wrong because columnstore indexes do not directly cause partition elimination.

Option D is wrong because updating statistics does not fix partition elimination.

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

Option B is correct because 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 →
260
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

Option D is correct because 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 →
261
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 →
262
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

Option A is correct because 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 →
263
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

Business Critical provides synchronous commit within the region. Failover group with planned failover can achieve zero data loss and fast RTO.

Why this answer

Options A and C are correct. To achieve zero data loss, you need synchronous replication within the primary region and a failover group that uses planned failover. Active geo-replication is asynchronous.

Manual failover may not meet RTO. Option B is wrong because active geo-replication is asynchronous. Option D is wrong because manual failover is too slow.

Option E is wrong because zone-redundancy alone does not provide zero RPO across regions.

Full explanation →
264
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 →
265
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

Zone redundancy ensures replicas in different zones.

Why this answer

Options A, B, and D are correct. Business Critical with zone redundancy automatically handles patching with no data loss and minimal downtime. You should verify zone redundancy is enabled (A), ensure the database is configured to use a read-scale replica (B) to offload read workloads, and test failover (D) to ensure readiness.

Option C (enable geo-replication) is not necessary for HA. Option E (scale down) is not needed.

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

Restoring the primary database to an earlier point in time will break the geo-replication link. The secondary database will become a regular database, and replication must be reconfigured after the restore. The restore is possible because the requested time is within the restore window.

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

Option A is correct because a planned failover (graceful failover) ensures no data loss by synchronizing all transactions before switching. Option B causes data loss. Option C and D are not valid for planned failover.

Full explanation →
268
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.
AnswerD

Azure SQL Database supports autoscale based on metrics.

Why this answer

Option D is correct because autoscale settings in Azure SQL Database can be configured to scale based on metrics like CPU percentage. Options A and B are for different purposes. Option C is not a built-in feature.

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

Option B is correct. After automatic failover, the secondary becomes the primary. To restore the original primary, you perform a planned failover (also called a graceful failover) to switch back without data loss.

Option A is wrong because you cannot add a new secondary without removing the old one. Option C is wrong because you need to perform a planned failover, not delete and recreate. Option D is wrong because you should not remove the secondary; you need to fail back.

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

Option D is correct because 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 →
272
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 →
273
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

Option A is correct because to achieve zero data loss, you should set the database to read-only to prevent any writes, then failover, and finally resume writes on the new primary. Option B is incorrect because scaling up does not guarantee zero data loss. Option C is incorrect because backup and restore would cause data loss.

Option D is incorrect because you should not switch to read-only mode, as that would prevent writes.

Full explanation →
274
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 →
275
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

Option C is correct because 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 →
276
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

High target vs actual indicates memory pressure.

Why this answer

Option B is correct because 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 Buffer Cache Hit Ratio indicates cache efficiency but not directly memory pressure. Option C is wrong because Page Life Expectancy is affected by memory but is a secondary indicator.

Option D is wrong because Batch Requests/sec measures throughput, not memory.

Full explanation →
277
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 →
278
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

Option A is correct because elastic jobs automatically log job execution history in the job database (which is a user-defined database). Option B is wrong because the elastic job agent uses a job database, not Azure Table Storage. Option C is wrong because Azure Monitor logs require separate configuration.

Option D is wrong because SQL Server Audit is for database auditing, not job logging.

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

Option B is correct because the backup schedule frequency is set to every 7 days, but the retentionDays is only 7, meaning each backup will be retained exactly until the next backup, leaving no overlap and potentially causing gaps. Option A is wrong because Standard_LRS is fine for backups. Option C is wrong because isEnabled is true.

Option D is wrong because the description is not an issue.

Full explanation →
280
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 (no connection drops) for both DTU and vCore models. However, when scaling up or down, a brief failover may occur if the target service objective is different enough. For DTU, scaling between tiers (e.g., Standard to Premium) causes a failover.

Within the same tier, scaling is typically online. Option A is correct. Option B is incorrect because PITR does not scale.

Option C is not necessary. Option D is incorrect because scaling is online for same-tier changes.

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

Option D is correct because contained database users are replicated automatically, but non-contained logins in master are not replicated with Active Geo-Replication. Since logins are in master, they are not present on the secondary after failover. Option A is wrong because Firewall rules for Azure services are enabled by default.

Option B is wrong because contained users are replicated. Option C is wrong because DNS changes are not required for connectivity after failover if using the same connection string with failover group.

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

Option B is correct because 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 →
285
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.
AnswerA

This reduces log write latency on the secondary replica because LRS has lower write latency than GRS, while still maintaining disaster recovery.

Why this answer

Option C is correct because changing the secondary replica's backup storage redundancy from locally-redundant storage (LRS) to geo-redundant storage (GRS) might cause increased latency, but here the issue is high log write latency. Actually, the correct answer is to change the secondary's backup storage redundancy to LRS (if it is GRS) to reduce latency. However, the options are as given.

Option A is wrong because decreasing the secondary replica's service tier reduces latency but also reduces redundancy. Option B is wrong because asynchronous commit reduces protection. Option D is wrong because adding more replicas increases cost and complexity.

The best answer is C, as it addresses the storage latency issue without compromising data protection.

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

Options A and E are correct. Option A enforces TLS 1.2 for data in transit. Option E uses Always Encrypted with a column master key stored in Azure Key Vault, which prevents DBAs from accessing the encryption keys and thus the sensitive data.

Option B is wrong because TDE encrypts data at rest but does not protect data in transit or from DBAs. Option C is wrong because dynamic data masking can be bypassed by DBAs with elevated permissions. Option D is wrong because row-level security does not encrypt data.

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

Option C is correct because 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 →
288
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

Option A and C are correct. Deploying two VMs in an availability set (or across zones) and configuring an Always On availability group provides high availability and meets the SLA. Option B is wrong because a single VM with premium disks does not achieve 99.99%.

Option D is wrong because FCI with single VM does not provide high availability. Option E is wrong because backup redundancy does not affect availability.

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

Option D is correct because 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 →
290
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 →
291
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

Option B is correct because 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 →
292
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 →
293
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 →
294
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

Option B is correct because 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 →
295
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 →
296
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

Option B is correct because 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 →
297
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 →
298
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 →
299
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 →
300
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

Option A correct because adding files reduces allocation contention. Option C correct because using multiple tempdb files distributes contention. Option D correct because optimizing queries that use tempdb reduces load.

Option B wrong because increasing size does not reduce contention. Option E wrong because changing collation is unrelated.

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 →