Courseiva

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

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

Take a mock examExam hub
Page 8

Page 9 of 13

Page 10
601
MCQmedium

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

A.Always Encrypted, VNet service endpoints, and SQL authentication
B.Microsoft Entra authentication, firewall rules, and managed identity
C.Transparent Data Encryption, IP firewall rules, and connection strings
D.Azure Defender for SQL, firewall rules, and service principal
AnswerB

Managed identity allows application to authenticate without credentials.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
602
Multi-Selecteasy

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

Select 2 answers
A.CPU percentage
B.Log write throughput
C.Deadlock count
D.DTU percentage
E.Query Store size
AnswersA, D

CPU percentage is a standard metric for scaling.

Why this answer

Options A and D are correct because DTU percentage and CPU percentage are standard metrics for autoscaling. Option B is wrong because deadlocks are an event, not a continuous metric. Option C is wrong because log write throughput is not typically used for autoscale.

Option E is wrong because Query Store is not a metric source.

Full explanation →
603
MCQmedium

You manage an Azure SQL Database that supports a critical application. You need to automate the process of rebuilding indexes that have fragmentation above 30% on a weekly basis. The solution must use built-in database features and minimize performance impact. What should you do?

A.Create a SQL Agent job that runs ALTER INDEX REBUILD on all indexes with fragmentation >30%.
B.Use Azure Automation to run a PowerShell script that checks fragmentation and rebuilds indexes.
C.Enable automatic tuning and set the 'Force plan' and 'Create index' options.
D.Schedule a weekly job using elastic jobs to reorganize all indexes.
AnswerC

Automatic tuning automatically manages index creation and performance without manual intervention.

Why this answer

Option C is correct because Azure SQL Database's automatic tuning feature includes the 'Create index' option, which can automatically rebuild indexes with high fragmentation when enabled. This uses built-in database features without requiring external scripts or jobs, and it minimizes performance impact by leveraging the database engine's own intelligent scheduling and execution.

Exam trap

The trap here is that candidates often assume SQL Agent jobs or external automation are necessary for index maintenance, but Azure SQL Database's automatic tuning provides a built-in, low-impact solution that directly addresses fragmentation without requiring custom scripts or jobs.

How to eliminate wrong answers

Option A is wrong because SQL Agent jobs are not available in Azure SQL Database (only in SQL Server on VMs or Managed Instances), and ALTER INDEX REBUILD can be resource-intensive, potentially causing performance impact during execution. Option B is wrong because Azure Automation running PowerShell scripts is an external solution that does not use built-in database features, and it introduces additional complexity and latency compared to native database capabilities. Option D is wrong because elastic jobs are designed for executing T-SQL scripts across multiple databases, not for automating index maintenance within a single database, and reorganize (not rebuild) is less effective for fragmentation above 30%.

Full explanation →
604
MCQmedium

Your organization uses Azure SQL Database with Azure SQL Managed Instance for a multi-tenant SaaS application. You need to ensure that each tenant's data is isolated and that a compromised tenant cannot access other tenants' data. What is the most secure approach?

A.Deploy a separate database per tenant, configure server-level firewall rules per tenant IP, and use Microsoft Entra authentication with application roles.
B.Use dynamic data masking to obfuscate tenant IDs in query results.
C.Implement row-level security (RLS) with a tenant ID filter on all tables.
D.Use column-level encryption with Azure Key Vault for sensitive columns.
AnswerA

This provides strong isolation at the database level and access control at the network and identity layers, minimizing risk of cross-tenant access.

Why this answer

Option A is correct because deploying a separate database per tenant provides the strongest isolation boundary at the storage and compute layer. Combining server-level firewall rules that restrict access to each tenant's IP range and using Microsoft Entra authentication with application roles ensures that even if one tenant's credentials are compromised, the attacker cannot access another tenant's database. This multi-layered approach (network + authentication + authorization) is the most secure for multi-tenant isolation in Azure SQL Managed Instance.

Exam trap

The trap here is that candidates often choose row-level security (RLS) because it seems like a simple, built-in solution, but they overlook that RLS can be bypassed by a compromised tenant with sufficient privileges (e.g., db_owner) or by using direct table access without the security policy context.

How to eliminate wrong answers

Option B is wrong because dynamic data masking only obfuscates data in query results for unauthorized users; it does not prevent a compromised tenant from reading or modifying other tenants' data if they can issue queries directly. Option C is wrong because row-level security (RLS) applies a tenant ID filter at the query level, but a compromised tenant with direct database access could potentially bypass the filter by using elevated privileges or by manipulating session context (e.g., SET CONTEXT_INFO). Option D is wrong because column-level encryption protects specific sensitive columns but does not isolate entire rows or tables; a compromised tenant could still access other tenants' non-encrypted columns or metadata.

Full explanation →
605
MCQeasy

You are setting up Azure SQL Database and need to ensure that only specific Azure services (e.g., Azure Data Factory) can access the database. What should you configure?

A.Create a private endpoint for the database.
B.Add firewall rules for the public IP addresses of the Azure services.
C.Add a firewall rule with start and end IP set to 0.0.0.0.
D.Add a virtual network rule for the subnet where the service is deployed.
AnswerD

Correct: This restricts access to that subnet.

Why this answer

Option D is correct because a virtual network rule allows you to restrict access to your Azure SQL Database to traffic originating from a specific virtual network subnet. When Azure services like Azure Data Factory are deployed in a virtual network, you can create a virtual network rule that references the subnet of that service, ensuring only traffic from that subnet can connect. This provides a more secure and granular access control compared to IP-based firewall rules, as it leverages the Azure backbone network and avoids exposing the database to public IP ranges.

Exam trap

The trap here is that candidates often confuse the 'Allow Azure services' firewall rule (0.0.0.0) with a method to restrict access to specific services, when in reality it permits all Azure services, and they overlook that virtual network rules provide the necessary granularity for service-specific access.

How to eliminate wrong answers

Option A is wrong because a private endpoint assigns a private IP address to the database within a virtual network, which allows connectivity from the virtual network but does not inherently restrict access to specific Azure services; it requires additional configuration like network security groups or service endpoints to filter by service. Option B is wrong because adding firewall rules for the public IP addresses of Azure services is impractical and insecure, as Azure services often use dynamic or shared public IP ranges that can change, and it would expose the database to the public internet. Option C is wrong because adding a firewall rule with start and end IP set to 0.0.0.0 is a special rule that allows Azure services to connect from any Azure IP address, but it does not restrict access to specific services like Azure Data Factory; it permits all Azure services and resources, which is too permissive and not the intended behavior.

Full explanation →
606
MCQhard

You are reviewing an ARM template snippet that configures a Security Alert Policy for an Azure SQL Database. The policy is enabled, and email notifications are sent to the account admin and admin@contoso.com. However, you notice that SQL Injection alerts are disabled. What is the most likely reason for disabling SQL Injection alerts?

A.To reduce the number of false positives and save costs on alert processing.
B.Because the database is configured with a conflicting vulnerability assessment policy that overrides SQL injection detection.
C.Because SQL injection alerts are incompatible with the chosen storage account endpoint.
D.Because SQL injection detection is already handled by Microsoft Defender for SQL.
AnswerD

Microsoft Defender for SQL provides advanced threat protection, so the basic alert policy may be disabled to avoid duplication.

Why this answer

Option C is correct. SQL Injection alerts may be disabled if an alternative mechanism, such as Microsoft Defender for SQL, is used to detect SQL injection attacks. Option A is wrong because disabling alerts does not reduce cost; it reduces security.

Option B is wrong because there is no conflicting policy. Option D is wrong because there is no known compatibility issue.

Full explanation →
607
MCQeasy

You have an Azure SQL Database that uses the General Purpose service tier. You notice that the log write throughput is consistently near the limit. What should you do to improve log write performance?

A.Migrate to the Business Critical service tier.
B.Enable accelerated database recovery.
C.Migrate to the Hyperscale service tier.
D.Increase the DTU purchase model to a higher tier.
AnswerA

Business Critical provides higher log write throughput.

Why this answer

The General Purpose service tier in Azure SQL Database has a maximum log write throughput of 1.5 MB/s for the most common configurations. The Business Critical tier uses local SSD storage and a higher log I/O limit (up to 100 MB/s), which directly addresses log write throughput bottlenecks. Migrating to Business Critical is the correct action because it provides significantly higher log write throughput and lower latency for transaction log writes.

Exam trap

The trap here is that candidates often assume increasing DTUs or moving to Hyperscale will solve all performance issues, but they fail to recognize that log write throughput is a specific architectural limitation of the General Purpose tier that only the Business Critical tier resolves.

How to eliminate wrong answers

Option B is wrong because enabling accelerated database recovery (ADR) improves transaction rollback and recovery times, not log write throughput; it does not increase the log I/O capacity. Option C is wrong because the Hyperscale service tier is designed for large databases with fast scaling and high read throughput, but its log write throughput is still limited compared to Business Critical and is not the primary solution for a log write bottleneck. Option D is wrong because increasing the DTU purchase model to a higher tier (e.g., from S3 to S4) does not change the underlying architecture; General Purpose still uses remote storage with the same log write throughput limitations, regardless of DTU level.

Full explanation →
608
MCQhard

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

A.Always Encrypted with column master key stored in Azure Key Vault
B.Row-Level Security
C.Dynamic Data Masking
D.Transparent Data Encryption
AnswerA

Always Encrypted ensures data is encrypted at the client and the database never sees plaintext, even from DBAs.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
609
MCQhard

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

A.The database is configured with active geo-replication to a secondary region.
B.The database is zone-redundant and is the primary database in its replication relationship.
C.The database is a Hyperscale database with zone redundancy enabled.
D.The database has a readable secondary replica in a different Azure availability zone.
AnswerB

ZoneRedundant true and replicationRole Primary indicate a zone-redundant primary.

Why this answer

Option C is correct. The output shows the database is a primary database with zone redundancy enabled. It does not indicate any geo-replication relationship.

Option A (replica in another zone) is true but not directly shown; zone redundancy means there are replicas in different zones. Option B (geo-replication) is false because replicationRole is Primary, not Secondary. Option D (Hyperscale) is false because Hyperscale does not have zoneRedundant in the same way.

Full explanation →
610
MCQeasy

You are configuring a new Azure SQL Database. The company policy requires that all connections use Microsoft Entra authentication and that no SQL authentication accounts exist. What should you do to prevent creation of SQL authenticated logins?

A.Set the 'public_network_access' to 'Disabled'.
B.Set the 'DisallowSqlAuthentication' property to 'True' on the logical server.
C.Remove the SQL admin login after creating the database.
D.Create an Azure Policy to audit SQL authentication usage.
AnswerB

This property explicitly disallows SQL authentication, enforcing only Microsoft Entra authentication.

Why this answer

Disallowing SQL authentication by setting the 'DisallowSqlAuthentication' property to 'True' via Azure CLI or ARM template ensures that only Microsoft Entra authentication is allowed. Option A is correct. Option B is wrong because it only disables the admin login but does not prevent others.

Option C is wrong because Azure Policy can enforce it but requires additional setup; the direct property is simpler. Option D is wrong because it disables all authentication.

Full explanation →
611
MCQeasy

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

A.Failover groups with auto-failover policy
B.Zone-redundant configuration
C.Point-in-time restore
D.Active geo-replication with manual failover
AnswerA

Failover groups maintain a readable secondary and automatically fail over when primary becomes unavailable, meeting RPO of 5 seconds and RTO of 30 minutes.

Why this answer

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

Backup restore has much higher RPO/RTO.

Full explanation →
612
MCQmedium

You are deploying an Azure SQL Database for a new application that requires consistent performance and low latency. The database will be accessed by users across multiple Azure regions. Which deployment option should you use?

A.Managed Instance with failover groups
B.Single database with Active Geo-Replication
C.Single database in one region
D.Elastic pool in one region
AnswerB

Readable secondaries in multiple regions reduce read latency.

Why this answer

Active Geo-Replication for a single database enables continuous data synchronization to readable secondary replicas in different Azure regions, providing low-latency read access for globally distributed users and supporting manual failover for write availability. This meets the requirement for consistent performance and low latency across multiple regions without the orchestration overhead of failover groups.

Exam trap

The trap here is that candidates often choose failover groups (Option A) thinking they provide readable secondaries, but in a Managed Instance, failover groups do not support readable secondaries until failover occurs, whereas Active Geo-Replication for single databases does.

How to eliminate wrong answers

Option A is wrong because Managed Instance with failover groups provides automated failover but does not support readable secondaries for global read-scale; secondaries are not readable until failover occurs, so it does not address low-latency reads across regions. Option C is wrong because a single database in one region cannot serve users in other regions with low latency, as all traffic must traverse the network to that single region, increasing latency. Option D is wrong because an elastic pool in one region similarly confines all databases to a single region, failing to provide geo-distributed read access or low latency for multi-region users.

Full explanation →
613
MCQhard

Your organization uses Azure SQL Database with a geo-replication configuration. The primary server is in the East US region and the secondary is in West US. You need to ensure that if a regional outage occurs, failover can be initiated manually and the secondary database will have the same security settings as the primary. What should you configure?

A.Configure active geo-replication between the databases and manually initiate failover if needed.
B.Create an auto-failover group with read-write failover policy.
C.Use database copy to create a copy in the secondary region.
D.Use backup and restore to copy the database to the secondary region.
AnswerA

Active geo-replication replicates security settings and allows manual failover.

Why this answer

Option A is correct because active geo-replication allows manual failover and security settings like firewall rules are replicated automatically. Option B is wrong because auto-failover groups require automatic failover and may have different security settings if not configured properly. Option C is wrong because a backup and restore does not replicate security settings automatically.

Option D is wrong because copying the database is a one-time operation.

Full explanation →
614
MCQhard

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

A.Immediately implement the DROP_INDEX recommendation to reduce overhead
B.Create the recommended index to improve performance
C.Revert the plan force because it is causing regression
D.Monitor the query performance to confirm the forced plan resolves the regression
AnswerD

The active recommendation should be monitored for effectiveness.

Why this answer

The recommendation is active and the query regressed, so forcing the last good plan is appropriate. The DBA should verify the plan change improved performance.

Full explanation →
615
Multi-Selectmedium

Which TWO of the following are valid methods to automate backups for Azure SQL Managed Instance? (Select exactly two.)

Select 2 answers
A.Use Azure Backup to schedule full backups.
B.Schedule T-SQL BACKUP DATABASE TO URL statements via SQL Agent jobs.
C.Use Azure Site Recovery to replicate the instance.
D.Configure long-term retention (LTR) policies on the managed instance.
E.Use Azure VM backup by installing the backup extension.
AnswersB, D

SQL Agent jobs can automate copy-only backups to Azure Blob.

Why this answer

Options A and D are correct. Azure SQL Managed Instance supports automated backups via the service (PITR and LTR) and also allows manual copy-only backups to Azure Blob Storage via T-SQL. Option B is wrong because Azure Backup does not natively back up SQL Managed Instance.

Option C is wrong because Azure Site Recovery is for disaster recovery, not backups. Option E is wrong because VM backup is for IaaS VMs, not Managed Instance.

Full explanation →
616
MCQeasy

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

A.Azure SQL Database Vulnerability Assessment
B.Azure SQL Auditing
C.Microsoft Defender for SQL
D.Microsoft Sentinel
AnswerC

Defender for SQL includes Advanced Threat Protection that detects SQL injection.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
617
MCQeasy

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

A.Automatic failover will happen immediately.
B.You must manually initiate failover.
C.Automatic failover will start after 1 hour if the primary is still unreachable.
D.The secondary database will be deleted and re-created.
AnswerC

The grace period delays automatic failover.

Why this answer

Option A is correct because automatic failover occurs only after the grace period expires and the primary is unreachable. Option B is incorrect because the grace period allows time for the primary to recover. Option C is incorrect because the secondary is already provisioned.

Option D is incorrect because you cannot manually failover during the grace period if automatic failover is enabled.

Full explanation →
618
MCQmedium

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

A.Increase the DTU/service tier of the database
B.Create a missing index recommendation
C.Drop and recreate the index used by the query
D.Force the previous query plan using Query Store
AnswerD

Plan forcing enforces the known good plan for consistent performance.

Why this answer

Option C is correct because forcing the previous known good plan via Query Store plan forcing stabilizes performance without code changes. Option A is wrong because index tuning may not revert the plan. Option B is wrong because dropping and recreating the index is disruptive.

Option D is wrong because DTU increase addresses symptoms not root cause.

Full explanation →
619
Multi-Selecthard

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

Select 3 answers
A.Enable Microsoft Defender for SQL to detect and alert on SQL injection.
B.Use parameterized queries or stored procedures in the application.
C.Implement dynamic data masking to hide sensitive data from unauthorized users.
D.Use a web application firewall (WAF) in front of the application to filter malicious inputs.
E.Enable Transparent Data Encryption (TDE) to encrypt the database at rest.
AnswersA, B, D

Defender for SQL includes threat detection for SQL injection patterns.

Why this answer

Microsoft Defender for SQL provides advanced security capabilities, including vulnerability assessment and threat detection. It specifically monitors for anomalous activities that indicate a SQL injection attack, such as unusual login attempts or queries that match known injection patterns, and can alert administrators or trigger automated responses.

Exam trap

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

Full explanation →
620
Multi-Selectmedium

Which TWO actions are valid for implementing column-level encryption in Azure SQL Database using Always Encrypted? (Choose two.)

Select 2 answers
A.Store the column encryption key in the database.
B.Use randomized encryption for columns that will not be searched.
C.Use a hash of the column value for encryption.
D.Encrypt an entire row by specifying a row-level encryption key.
E.Use deterministic encryption for columns that will be used in equality searches.
AnswersB, E

Randomized encryption provides more security but cannot be searched.

Why this answer

Always Encrypted supports two encryption types: deterministic and randomized. Randomized encryption is valid for columns that will not be searched because it encrypts the same plaintext into different ciphertexts each time, preventing pattern-based attacks. This makes it suitable for sensitive data like credit card numbers or personal identifiers that only need to be decrypted for use, not queried with equality predicates.

Exam trap

The trap here is that candidates often confuse Always Encrypted with Transparent Data Encryption (TDE) or row-level security, and mistakenly think encryption keys are stored in the database or that hashing is a valid encryption method for Always Encrypted.

Full explanation →
621
MCQhard

You administer a large Azure SQL Database that is used for a SaaS application. The database has a table with over 1 billion rows that is frequently queried by customer ID. The table currently has a clustered index on an identity column and a nonclustered index on customer ID. Queries that filter by customer ID are experiencing high IO and long execution times. You analyze the execution plan and see that the nonclustered index is used, but there are many key lookups. You need to optimize the query performance while minimizing storage overhead. What should you do?

A.Create a clustered columnstore index on the table
B.Create a filtered index on customer ID for frequent values
C.Partition the table by customer ID
D.Add all queried columns as included columns to the nonclustered index
AnswerA

Reduces IO and storage; eliminates lookups.

Why this answer

Option A is correct because creating a clustered columnstore index on the table will significantly reduce IO by using columnar storage and eliminate key lookups; it also provides high compression to minimize storage. Option B is wrong because adding included columns to the nonclustered index will increase storage size and may not fully eliminate lookups. Option C is wrong because partitioning by customer ID can improve maintenance but may not reduce IO for point queries.

Option D is wrong because creating a filtered index on customer ID is not helpful for many different customer IDs.

Full explanation →
622
MCQmedium

You are monitoring an Azure SQL Database using Query Performance Insight. You see a query with high duration and high CPU usage. The query plan shows a clustered index scan. What is the most likely cause and recommendation?

A.Fragmented clustered index; rebuild the clustered index.
B.Insufficient memory; increase the service tier.
C.Missing nonclustered index; create an index on the predicates.
D.Parameter sniffing; add OPTION (RECOMPILE).
AnswerC

An index seek would reduce CPU and duration.

Why this answer

Query Performance Insight shows a query with high duration and CPU usage, and the query plan reveals a clustered index scan. A clustered index scan reads all rows in the table, which is inefficient when only a subset of rows is needed. The most likely cause is a missing nonclustered index on the columns used in the WHERE clause (predicates), which would allow a seek operation instead of a full scan, reducing both CPU and duration.

Exam trap

The trap here is that candidates confuse a clustered index scan with fragmentation or parameter sniffing, but the scan is a symptom of a missing nonclustered index that would allow a seek, not a problem with the clustered index itself or plan caching.

How to eliminate wrong answers

Option A is wrong because a fragmented clustered index causes increased I/O and scan overhead, but the primary issue here is the scan itself, not fragmentation; rebuilding the index would not eliminate the scan if the query lacks a supporting index. Option B is wrong because insufficient memory would manifest as page life expectancy issues or disk spills, not a clustered index scan; increasing the service tier does not address the missing index. Option D is wrong because parameter sniffing leads to suboptimal cached plans for different parameter values, but the query plan shows a clustered index scan, which indicates a fundamental missing index issue, not a plan choice problem; adding OPTION (RECOMPILE) would not create the missing index.

Full explanation →
623
MCQhard

Refer to the exhibit. An administrator creates a geo-replica for SalesDB. After 30 minutes, the replication state remains SEEDING. What is the most likely cause?

A.The secondary server sqlsrv2 is in a different region than the primary.
B.The recovery model is set to Full instead of Simple.
C.The primary database SalesDB is not zone-redundant.
D.The secondary database is being used as a read-only replica.
AnswerC

Zone redundancy must be enabled on the primary database for geo-replication to seed successfully.

Why this answer

The exhibit shows zoneRedundant is false. Geo-replication requires zone redundancy enabled on the primary database because Azure SQL Database geo-replication is only supported for zone-redundant databases.

Full explanation →
624
MCQmedium

You manage a mission-critical Azure SQL Database in the East US region. The database uses the Business Critical service tier with zone-redundant high availability enabled. You need to ensure that if an entire Azure region fails, the database can be failed over to a secondary region with minimal data loss. What should you implement?

A.Enable auto-failover groups with read-write failover policy.
B.Configure a failover group with the secondary in a different Azure region.
C.Enable active geo-replication to a secondary server in a paired region.
D.Deploy zone-redundant configuration in East US 2.
AnswerC

Active geo-replication provides asynchronous replication to a secondary region, enabling manual or automatic failover with minimal data loss.

Why this answer

Option C is correct because active geo-replication provides near-real-time asynchronous replication to a secondary region, and with Business Critical, you can configure a readable secondary that can be failed over manually or automatically with minimal data loss. Option A is wrong because failover groups are built on top of geo-replication but do not guarantee minimal data loss by themselves; they rely on the replication mode. Option B is wrong because auto-failover groups include geo-replication but the question asks for the specific feature to implement.

Option D is wrong because zone redundancy protects against zonal failures within a region, not regional failures.

Full explanation →
625
Multi-Selecteasy

You are troubleshooting a performance issue in an Azure SQL Database. You need to identify the queries that are consuming the most CPU over the last hour. Which two methods can you use? (Choose two.)

Select 2 answers
A.sys.dm_exec_query_stats
B.sys.dm_os_wait_stats
C.sys.dm_db_index_usage_stats
D.sys.dm_exec_requests
E.Query Store top resource consuming queries report
AnswersA, E

Provides cumulative CPU time for cached plans.

Why this answer

Options A and C are correct. Query Store can show top queries by CPU over a time period. sys.dm_exec_query_stats provides cumulative CPU time for cached queries. Option B is wrong because sys.dm_os_wait_stats shows waits, not CPU.

Option D is wrong because sys.dm_exec_requests shows currently executing requests, not historical CPU. Option E is wrong because sys.dm_db_index_usage_stats shows index usage.

Full explanation →
626
MCQeasy

You have an Azure SQL Database in the Hyperscale service tier. You need to ensure that read-only workloads are offloaded to a readable secondary. Which configuration should you set?

A.Set ReadOnlyRouting=1 on the database.
B.Add the database to a failover group.
C.Set ReadScale to 1 on the database.
D.Use ApplicationIntent=ReadOnly in the connection string.
AnswerD

This routes queries to a readable secondary.

Why this answer

Option D is correct because setting `ApplicationIntent=ReadOnly` in the connection string directs read-only workloads to a readable secondary replica in Azure SQL Database Hyperscale. This offloads read traffic from the primary, improving performance for write-heavy operations. The Hyperscale tier supports this feature without requiring a failover group or explicit read-scale configuration.

Exam trap

The trap here is that candidates confuse the `ReadScale` property (used in Premium tier) with the Hyperscale tier's always-on read-scale capability, or incorrectly think a failover group is required to enable read-only routing.

How to eliminate wrong answers

Option A is wrong because `ReadOnlyRouting=1` is not a valid Azure SQL Database setting; read-only routing is controlled via connection string intent, not a database-level property. Option B is wrong because adding the database to a failover group enables geo-failover and read-only routing for business continuity, but it is not required for offloading read workloads to a readable secondary in Hyperscale; the Hyperscale tier provides a built-in readable secondary without a failover group. Option C is wrong because `ReadScale` is a property for Azure SQL Database in the Premium tier (set to 1 to enable read-scale out), but in Hyperscale, read-scale is always enabled and does not need a separate configuration flag.

Full explanation →
627
Multi-Selecteasy

Which TWO actions are required to implement transparent data encryption (TDE) with customer-managed keys for an Azure SQL Database?

Select 2 answers
A.Create an Azure Key Vault and a key.
B.Assign the key to the Azure SQL logical server and enable TDE.
C.Set the backup encryption level to 'Encrypted'.
D.Create a server certificate in the database.
E.Configure column encryption keys in the database.
AnswersA, B

Required for customer-managed TDE.

Why this answer

Option A is correct because TDE with customer-managed keys requires an Azure Key Vault to store the key, which provides centralized key management and control. The key in the vault is used to protect the database encryption key (DEK), ensuring that the customer retains ownership and control over the encryption material.

Exam trap

The trap here is that candidates confuse TDE with Always Encrypted or on-premises certificate-based TDE, leading them to select options about column encryption keys or server certificates, which are not part of Azure SQL Database TDE implementation.

Full explanation →
628
MCQhard

Your company uses Azure SQL Database with active geo-replication configured between two regions. The primary database in East US experiences a critical failure, and you initiate a manual failover to the secondary in West US. After the failover, you need to re-establish geo-replication to a new secondary database in a third region (Central US) to restore the DR capability. What is the correct sequence of actions?

A.Delete the old primary and create a new geo-secondary in Central US.
B.Add a new secondary in Central US on the current primary.
C.Perform a planned failover to the old primary, then configure geo-replication to Central US.
D.Remove the geo-replication link to the old primary, then add a new secondary in Central US.
AnswerD

Removing the old link allows you to create a new secondary on the current primary.

Why this answer

Option C is correct. After a failover, the old primary becomes a secondary (if still available). To reconfigure geo-replication with a new secondary, you need to remove the old secondary relationship, then create a new secondary.

Option A is wrong because you cannot add a new secondary without removing the old one first. Option B is wrong because you do not need to create a new primary; the current primary (formerly secondary) is fine. Option D is wrong because geo-replication does not automatically create a new secondary.

Full explanation →
629
Multi-Selecteasy

You are configuring Azure SQL Database auditing. You need to ensure that all database-level authentication failures are logged. Which TWO audit action groups should you include?

Select 2 answers
A.SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
B.FAILED_DATABASE_AUTHENTICATION_GROUP
C.DATABASE_OBJECT_PERMISSION_CHANGE_GROUP
D.DATABASE_OBJECT_CHANGE_GROUP
E.SCHEMA_OBJECT_CHANGE_GROUP
AnswersA, B

Logs successful logins.

Why this answer

Option A is correct because the SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP audit action group captures all successful authentication attempts at the database level. Option B is correct because the FAILED_DATABASE_AUTHENTICATION_GROUP audit action group captures all failed authentication attempts at the database level. Together, these two groups ensure that every database-level authentication event—both successes and failures—is logged, which is required to meet the requirement of logging all database-level authentication failures.

Exam trap

The trap here is that candidates may assume only the FAILED_DATABASE_AUTHENTICATION_GROUP is needed to log failures, but the question requires logging 'all database-level authentication failures'—which implicitly includes both successes and failures to provide complete visibility, or they may confuse authentication groups with object-level change groups that audit DDL or permission changes.

Full explanation →
630
MCQeasy

You manage an Azure SQL Database that uses the General Purpose tier. You need to monitor the performance of the database and identify the top resource-consuming queries. You want to use a built-in feature that requires no additional cost. What should you use?

A.Query Store.
B.Azure SQL Analytics (preview) in Azure Monitor.
C.SQL Server Profiler.
D.sys.dm_exec_query_stats and sys.dm_exec_sql_text DMVs.
AnswerA

Query Store is a built-in, free feature that captures query performance data and helps identify top resource consumers.

Why this answer

Option D is correct because Query Store is a built-in, no-cost feature that tracks query performance. Option A is wrong because Azure SQL Analytics is a paid solution. Option B is wrong because dynamic management views require manual querying.

Option C is wrong because SQL Server Profiler is deprecated and not supported in Azure SQL Database.

Full explanation →
631
Multi-Selecthard

You are designing an automation strategy for an Azure SQL Database that requires the following: 1) Automatically scale up the service tier when CPU usage exceeds 90% for 5 minutes. 2) Automatically scale down when CPU usage drops below 10% for 15 minutes. 3) The solution must be cost-effective and use built-in Azure features. Which TWO options should you combine? (Choose two.)

Select 2 answers
A.Elastic Database Jobs
B.Azure Monitor autoscale
C.Azure Functions
D.Azure Logic Apps with a metric trigger
E.Azure Automation runbook
AnswersD, E

Logic Apps can monitor metrics and trigger actions.

Why this answer

Azure Logic Apps can trigger scaling actions based on metrics, and Azure Automation runbooks can execute the scaling commands. Option B is wrong because elastic jobs are for scheduling database tasks, not scaling. Option D is wrong because autoscale rules are not directly available for Azure SQL Database; you need custom automation.

Option E is wrong because Azure Functions can also be used, but Logic Apps and Automation are the recommended combination.

Full explanation →
632
Multi-Selecteasy

You are a database administrator for a startup that uses Azure SQL Database to run an e-commerce application. The application uses a service principal (Microsoft Entra ID application) to authenticate to the database. You need to grant the service principal the ability to read data from all tables in the 'sales' schema. Which THREE actions should you take?

Select 3 answers
A.Grant CONTROL permission on the database to the user
B.Add the user to the 'sales' database role
C.Create a database user for the service principal using the CREATE USER statement with the FROM EXTERNAL PROVIDER option
D.Grant SELECT on the 'sales' schema to the user
E.Add the user to the 'db_datareader' fixed database role
AnswersC, D, E

This maps the Microsoft Entra service principal to a database user.

Why this answer

Option C is correct because a service principal (Microsoft Entra ID application) must have a corresponding database user created using the CREATE USER statement with the FROM EXTERNAL PROVIDER clause. This maps the external identity to a database principal, enabling authentication and authorization within Azure SQL Database.

Exam trap

The trap here is that candidates often confuse schemas with database roles, leading them to incorrectly select 'Add the user to the sales database role' (Option B) when no such role exists, or they over-provision by granting CONTROL permission (Option A) instead of using the principle of least privilege.

Full explanation →
633
Multi-Selectmedium

Which TWO are valid methods for auditing Azure SQL Database activity? (Choose two.)

Select 2 answers
A.Azure Event Grid
B.Azure Monitor Metrics
C.Azure Storage account
D.Log Analytics workspace
E.On-premises file share
AnswersC, D

Audit logs can be stored in an Azure Storage account.

Why this answer

Option C is correct because Azure SQL Database auditing can write audit logs directly to an Azure Storage account, which is a supported destination for storing audit records in blob format. Option D is correct because audit logs can also be sent to a Log Analytics workspace, enabling integration with Azure Monitor for advanced querying and alerting on audit data.

Exam trap

The trap here is that candidates often confuse Azure Monitor Metrics (numerical performance data) with Log Analytics (log/event data), or assume that on-premises file shares are supported because SQL Server on-premises supports file-based auditing, but Azure SQL Database is a PaaS service with restricted destination options.

Full explanation →
634
Multi-Selectmedium

You are designing a security strategy for Azure SQL Database. You need to ensure that database access is secured using Microsoft Entra ID (formerly Azure Active Directory) authentication. Which THREE actions should you take? (Choose THREE.)

Select 3 answers
A.Create a Microsoft Entra ID administrator for the Azure SQL logical server.
B.Disable SQL Server authentication after migrating to Entra ID.
C.Configure applications to use Microsoft Entra ID with MFA.
D.Enable Transparent Data Encryption (TDE).
E.Create a contained database user mapped to a Microsoft Entra ID principal.
AnswersA, C, E

An Entra ID admin is required to enable Entra ID authentication.

Why this answer

Creating a Microsoft Entra ID administrator for the Azure SQL logical server is required to enable Entra ID authentication at the server level. This administrator is a user or group from Entra ID that has permissions to manage all databases on the server, including creating contained database users mapped to Entra ID principals. Without this step, Entra ID authentication cannot be configured for the server or its databases.

Exam trap

The trap here is that candidates often confuse enabling Entra ID authentication with disabling SQL Server authentication (Option B) or with enabling TDE (Option D), both of which are independent security controls not required for Entra ID-based access.

Full explanation →
635
Multi-Selecthard

You have an Azure SQL Database that contains sensitive customer data. You need to classify the data and receive recommendations for protecting it. You also need to detect and alert on suspicious access patterns. Which two Azure services should you enable? (Choose two.)

Select 2 answers
A.Microsoft Sentinel
B.Azure Information Protection
C.Microsoft Defender for SQL
D.Microsoft Purview Data Map
E.Azure Policy
AnswersC, D

Detects and alerts on suspicious database access.

Why this answer

Microsoft Defender for SQL (Option C) provides vulnerability assessment, data discovery and classification, and advanced threat protection for Azure SQL Database. It can classify sensitive columns, recommend protection measures, and detect suspicious access patterns such as SQL injection or brute-force attacks, generating alerts for immediate response.

Exam trap

The trap here is that candidates often confuse Microsoft Sentinel's log aggregation capability with the native data classification and threat detection features that are built directly into Microsoft Defender for SQL, leading them to select Sentinel instead of Defender for SQL.

Full explanation →
636
MCQhard

You have an Azure SQL Database that needs to be automatically scaled up during peak hours and scaled down during off-peak. The solution must use native Azure capabilities without custom code. What should you use?

A.Use Azure Functions with timer trigger and PowerShell to change the pricing tier.
B.Configure autoscale for the elastic pool that contains the database.
C.Configure autoscale settings on the Azure SQL Database server.
D.Create Elastic Database Jobs that run ALTER DATABASE to change the service objective at scheduled times.
AnswerD

Elastic Jobs can execute T-SQL to modify the database's service tier on a schedule.

Why this answer

Option B is correct because Elastic Database Jobs can be used to run ALTER DATABASE statements on a schedule. Option A is wrong because Autoscale is not supported for Azure SQL Database. Option C is wrong because Azure Functions would require custom code.

Option D is wrong because Elastic Pool autoscale only adjusts per-database DTUs, not the pool size itself.

Full explanation →
637
MCQeasy

You are responsible for the disaster recovery of an Azure SQL Database that supports a customer-facing application. The database is 50 GB and uses the General Purpose service tier. The application requires an RPO of 1 hour and an RTO of 2 hours. The company has a limited budget and wants to minimize costs. You need to recommend a DR solution that meets the requirements without incurring additional compute costs for a secondary database. Which option should you choose?

A.Enable active geo-replication to a secondary database in a different region.
B.Use point-in-time restore (PITR) with geo-redundant backup storage.
C.Enable zone redundancy on the primary database.
D.Configure a failover group with a secondary in a different region.
AnswerB

PITR does not require a secondary database; it restores from backups, minimizing costs.

Why this answer

Option C is correct. Point-in-time restore using geo-redundant backup storage does not require a secondary database; you restore from backups in case of a regional outage. The RTO of 2 hours is achievable for a 50 GB database, and the RPO of 1 hour is within the backup retention (every 5-10 minutes).

Option A is wrong because active geo-replication incurs compute costs for the secondary. Option B is wrong because failover groups also incur compute costs. Option D is wrong because zone redundancy does not provide cross-region DR.

Full explanation →
638
MCQmedium

You are a database administrator for a large e-commerce company. The company has an Azure SQL Database named 'OrdersDB' in the General Purpose service tier, with a 2 TB database. The database has a table named 'OrderDetails' that contains over 1 billion rows. The table is partitioned by date using a partition function that splits data by month. You notice that queries that filter on a specific date range are performing poorly, and the query plan shows a clustered index scan on the partition column. The table has a clustered columnstore index to support analytics. You need to improve query performance for date-range queries without affecting the analytics workload. What should you do?

A.Create a new partition scheme with a different partition function.
B.Create a nonclustered index on the partition key column (OrderDate) and include other columns as needed.
C.Rebuild the clustered columnstore index with a different compression setting.
D.Change the clustered index to a rowstore B-tree index on the partition key.
AnswerB

Nonclustered index supports seek for date range queries without affecting columnstore.

Why this answer

Option B is correct because creating a nonclustered index on the partition key (OrderDate) allows SQL Server to perform an index seek for date-range queries, avoiding the full clustered columnstore index scan. This index does not interfere with the existing columnstore index, which remains optimal for analytics workloads. The nonclustered index can include additional columns as included columns to cover the query without touching the base table.

Exam trap

The trap here is that candidates assume partitioning alone improves query performance, but without an appropriate index, the query still scans all partitions; they may also mistakenly think rebuilding or changing the clustered index is necessary, ignoring that a nonclustered index can coexist with a columnstore index.

How to eliminate wrong answers

Option A is wrong because creating a new partition scheme with a different partition function does not address the lack of a suitable index for point or range lookups; partitioning alone does not provide an index seek capability. Option C is wrong because rebuilding the clustered columnstore index with a different compression setting (e.g., COLUMNSTORE_ARCHIVE) would not change the scan-based access method for date-range queries and could degrade performance further. Option D is wrong because changing the clustered index to a rowstore B-tree on the partition key would eliminate the columnstore index, breaking the analytics workload that relies on columnstore compression and batch-mode processing.

Full explanation →
639
MCQeasy

You are setting up a new Azure SQL Database for a development team. The database will contain test data that mimics production but with some sensitive fields obfuscated. You need to ensure that developers can query the database without seeing the actual sensitive data. The developers will use Microsoft Entra ID authentication. You have the following requirements: - The sensitive data should be automatically masked in query results for all developers except the database administrator. - The masking should be applied without modifying the application code. - The solution should be easy to manage and not require changes to the data model. What should you implement?

A.Create views that exclude sensitive columns and grant developers access to the views instead of the base tables.
B.Configure dynamic data masking on the sensitive columns, and add the database administrator to the unmask permission.
C.Implement Always Encrypted with column encryption, and grant the developers access to the encryption keys.
D.Create a row-level security policy that denies access to sensitive rows for developers.
AnswerB

Data masking is simple, no code changes, and can be managed via SQL commands.

Why this answer

Option C is correct because dynamic data masking can be configured in the database to mask columns for all users except those with explicit unmask permissions. It does not require code changes. Option A is wrong because Always Encrypted requires application changes.

Option B is wrong because row-level security filters rows, not columns. Option D is wrong because views would require rewriting queries.

Full explanation →
640
MCQeasy

You are monitoring an Azure SQL Database and notice that the average CPU usage is 80% and the average data IO percentage is 70%. You need to identify the most likely cause of the high resource usage. What should you check first?

A.Check for long-running maintenance tasks
B.Check for connection pooling issues
C.Check for blocking and deadlocks
D.Use Query Store to identify top resource-consuming queries
AnswerD

Query Store helps find queries consuming CPU and IO.

Why this answer

Option C is correct because Query Store provides detailed query performance data to identify high resource queries. Option A is incorrect because blocking and deadlocks cause waits, not necessarily high CPU/IO. Option B is incorrect because maintenance tasks like index rebuild are scheduled and not typically the first check.

Option D is incorrect because connection pooling issues cause connection errors, not high CPU/IO.

Full explanation →
641
MCQeasy

You are deploying Azure SQL Database for a new application. You need to ensure that connections from Azure services use a private IP address and do not traverse the public internet. What should you configure?

A.Enable Virtual Network service endpoints for Azure SQL Database
B.Use Azure Private Link for Azure SQL Database
C.Configure the Azure SQL Database firewall to allow Azure services
D.Deploy Azure SQL Database inside a virtual network
AnswerB

Private Link uses private IP addresses in your VNet.

Why this answer

Azure Private Link creates a private endpoint in a virtual network, mapping the Azure SQL Database logical server to a private IP address within that VNet. Traffic from Azure services (e.g., VMs, App Service) to the database uses Microsoft's backbone network via the private endpoint, never traversing the public internet. This meets the requirement for private, non-internet-routed connectivity.

Exam trap

The trap here is that candidates confuse Virtual Network service endpoints (which still use the public endpoint and do not provide a private IP) with Private Link (which provides a true private IP), or incorrectly assume Azure SQL Database can be deployed inside a VNet like a managed instance.

How to eliminate wrong answers

Option A is wrong because Virtual Network service endpoints provide a direct route from the VNet to Azure SQL Database over the Azure backbone, but the connection still uses the public endpoint of the database (the logical server's FQDN resolves to a public IP). Traffic does not traverse the internet, but it does not use a private IP address from the VNet; the source traffic is source-NATed to the VNet's public IP. Option C is wrong because configuring the firewall to 'Allow Azure Services' permits connections from any Azure service (e.g., Azure Data Factory, Azure Functions) using the service's public IP range, not a private IP address, and traffic may still traverse the internet.

Option D is wrong because Azure SQL Database is a Platform-as-a-Service (PaaS) offering that cannot be directly deployed inside a virtual network; it can only be integrated via Private Link or service endpoints, not placed inside a VNet like an IaaS VM.

Full explanation →
642
MCQmedium

You manage an Azure SQL Database that must run a maintenance task every Sunday at 2:00 AM UTC. The task must be resilient to failures and automatically retry if it fails. You need to configure this using Azure automation. What is the most appropriate solution?

A.Create an Azure Logic App with a recurrence trigger scheduled for Sunday at 2:00 AM UTC and configure a retry policy.
B.Create an Azure Automation Runbook and schedule it to run weekly. Add custom error handling for retries.
C.Use T-SQL Agent job in Azure SQL Database with a schedule and set up retry via Transact-SQL.
D.Create an Azure Function with a timer trigger and implement retry logic in code.
AnswerA

Logic Apps provide built-in scheduling and retry policies.

Why this answer

Option C is correct because Azure Logic Apps provides built-in retry policies and can be scheduled to run at specific times. Option A is wrong because Azure Automation Runbooks can be scheduled but require more custom error handling for retries. Option B is wrong because Azure Functions can be triggered by timer, but retry logic requires custom implementation.

Option D is wrong because T-SQL Agent jobs in Azure SQL Database are limited and do not support retry policies natively.

Full explanation →
643
MCQeasy

A DBA needs to create a new Azure SQL Database and wants to ensure that the database automatically fails over to a secondary region without manual intervention. The recovery point objective (RPO) is 5 seconds. What should the DBA configure?

A.Active geo-replication with failover group
B.Standard geo-replication
C.Local redundancy with automatic failover
D.Read-scale out
AnswerA

Failover groups provide automatic failover and synchronous replication for RPO of 5 seconds.

Why this answer

Active geo-replication with a failover group is the correct choice because it provides automatic, customer-managed failover to a secondary region with an RPO of 5 seconds. The failover group orchestrates the failover of multiple databases simultaneously and supports automatic failover policies, meeting the requirement for zero manual intervention. Standard geo-replication does not support automatic failover, and other options do not provide cross-region disaster recovery.

Exam trap

The trap here is that candidates often confuse 'standard geo-replication' with 'active geo-replication with failover group,' assuming both support automatic failover, but only the latter provides the automatic, policy-driven failover required for zero manual intervention.

How to eliminate wrong answers

Option B is wrong because standard geo-replication requires manual initiation of failover and does not support automatic failover, failing the 'without manual intervention' requirement. Option C is wrong because local redundancy with automatic failover refers to zone-redundant configurations within a single region, not cross-region failover, and cannot meet the RPO of 5 seconds for regional disasters. Option D is wrong because read-scale out is a feature for offloading read-only workloads to a replica in the same region, not for disaster recovery or automatic failover to a secondary region.

Full explanation →
644
MCQeasy

You need to automate the process of scaling an Azure SQL Database up during peak hours and down during off-peak hours to optimize cost. The solution must be serverless and not require any custom infrastructure. What should you use?

A.Create a SQL Agent job that runs ALTER DATABASE MODIFY (SERVICE_OBJECTIVE = ...).
B.Create a scheduled Azure Logic App that uses the Azure SQL Database REST API to update the service objective.
C.Use an Elastic Job agent to run a script that changes the service objective.
D.Enable autoscale on the Azure SQL Database.
AnswerB

Logic Apps can automate scaling without additional infrastructure.

Why this answer

Option D is correct because Azure Logic Apps can be scheduled to run PowerShell or CLI scripts to change the service tier. Option A is wrong because autoscale is not available for Azure SQL Database. Option B is wrong because SQL Agent cannot change the service tier.

Option C is wrong because elastic jobs are for executing scripts on multiple databases, not for scaling.

Full explanation →
645
MCQmedium

You need to audit schema changes on an Azure SQL Database. Specifically, you must capture details of any DDL statements executed by any user. The audit logs must be stored in a Log Analytics workspace for analysis. What should you configure?

A.Create DDL triggers that write to a table
B.Configure database-level auditing with blob storage destination
C.Configure server-level auditing with a Log Analytics workspace destination
D.Create an extended events session to capture DDL events
AnswerC

Server-level auditing captures all DDL changes and can stream to Log Analytics.

Why this answer

Option C is correct because Azure SQL Database server-level auditing with a Log Analytics workspace destination captures all DDL statements executed by any user and stores them in a Log Analytics workspace for analysis. This meets the requirement to audit schema changes and store logs in Log Analytics, as server-level auditing captures events for all databases on the server, including DDL operations, and supports Log Analytics as a destination.

Exam trap

The trap here is that candidates may confuse database-level auditing with blob storage as sufficient, but the question explicitly requires Log Analytics workspace destination, which is only supported at the server-level auditing configuration in Azure SQL Database.

How to eliminate wrong answers

Option A is wrong because DDL triggers that write to a table are a custom, manual solution that does not integrate with Log Analytics and can be bypassed or disabled, failing to provide a reliable audit trail. Option B is wrong because database-level auditing with blob storage destination stores logs in Azure Blob Storage, not in a Log Analytics workspace, and does not meet the requirement for Log Analytics analysis. Option D is wrong because an extended events session captures DDL events but requires manual configuration to send data to Log Analytics, and it is not a native auditing feature for Azure SQL Database; server-level auditing is the recommended approach for compliance and integration with Log Analytics.

Full explanation →
646
Multi-Selectmedium

You are a database administrator for a healthcare organization that uses Azure SQL Database to store protected health information (PHI). The compliance team requires that you implement controls to prevent unauthorized access and detect potential data exfiltration. Which TWO actions should you take?

Select 2 answers
A.Enable transparent data encryption (TDE) on the database
B.Implement dynamic data masking for columns containing PHI
C.Enable change tracking on the database
D.Enable Microsoft Defender for SQL on the server
E.Enable auditing on the database
AnswersB, D

Dynamic data masking obfuscates sensitive data from non-privileged users, reducing the risk of exposure.

Why this answer

Dynamic data masking (B) limits exposure of PHI by obfuscating sensitive data in query results to non-privileged users, directly preventing unauthorized viewing. Microsoft Defender for SQL (D) provides advanced threat detection and alerts for anomalous activities like SQL injection or data exfiltration attempts, meeting the detection requirement. Together, they address both prevention and detection of unauthorized access and data exfiltration.

Exam trap

The trap here is that candidates often confuse TDE (encryption at rest) with data masking (protection during use), or assume auditing alone satisfies detection requirements, missing that Defender for SQL provides active threat detection beyond passive logging.

Full explanation →
647
MCQmedium

You have an Azure SQL Database that uses SQL Server Agent for automation tasks. You migrated the database to Azure SQL Managed Instance. After migration, some SQL Server Agent jobs fail because they reference a linked server that no longer exists. You need to automate the removal of all linked server references from the jobs. What should you do?

A.Use elastic jobs to remove the linked server references.
B.Create a T-SQL script that updates the job steps to remove linked server references, and execute it via Azure Automation runbook.
C.Use Azure CLI to remove the linked server references.
D.Use Azure Automation to run a PowerShell script that removes the linked server references.
AnswerB

A T-SQL script can modify SQL Agent jobs, and Azure Automation can execute it.

Why this answer

PowerShell with the 'Get-AzSqlInstanceDatabase' cmdlet can list databases, but to modify jobs, you need to use T-SQL or the SQL Agent cmdlets. Option A is wrong because Azure Automation does not directly modify SQL Agent jobs. Option B is wrong because elastic jobs are separate from SQL Agent.

Option D is wrong because Azure CLI does not have SQL Agent management cmdlets.

Full explanation →
648
MCQhard

A DBA is migrating a large on-premises database to Azure SQL Database using the Data Migration Assistant (DMA). The migration fails with an error indicating that the source database contains cross-database queries. What is the best remediation?

A.Use elastic database query to reference external tables
B.Create a linked server in Azure SQL Database
C.Migrate to Azure SQL Managed Instance instead
D.Refactor the application to eliminate cross-database queries or use elastic query
AnswerD

Refactoring or using elastic query resolves the issue.

Why this answer

Azure SQL Database does not support cross-database queries natively. The Data Migration Assistant (DMA) blocks migrations that rely on such queries because the PaaS service lacks the necessary server-level context. The correct remediation is to refactor the application to eliminate cross-database dependencies or use elastic query, which provides a read-only, schema-bound mechanism to query remote databases via external data sources and external tables.

Exam trap

The trap here is that candidates often choose Azure SQL Managed Instance (Option C) because it supports cross-database queries and linked servers, but the question asks for the 'best remediation'—which is to refactor the application to align with the PaaS model, not to change the target platform to avoid the issue.

How to eliminate wrong answers

Option A is wrong because elastic database query is a read-only feature that allows querying remote Azure SQL databases via external tables, but it does not support write operations or replace the need to refactor cross-database queries that involve updates or complex joins across databases. Option B is wrong because Azure SQL Database does not support linked servers; linked servers are a SQL Server feature that requires a full instance, which is not available in the single database or elastic pool model. Option C is wrong because migrating to Azure SQL Managed Instance would preserve cross-database query capability, but it is not the best remediation—it avoids refactoring rather than addressing the root cause, and it may increase cost and complexity unnecessarily.

Full explanation →
649
Matchingmedium

Match each Azure SQL Database high availability feature to its description.

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

Concepts
Matches

Asynchronous replication to a secondary region

Group of databases that fail over together

Replicas across different availability zones

Data replicated within a single datacenter

Why these pairings

These features provide different levels of availability and disaster recovery for Azure SQL Database.

Full explanation →
650
MCQhard

Your company has a strict policy that all Azure SQL Databases must have Microsoft Defender for SQL enabled. You need to enforce this policy across all subscriptions using a scalable, automated approach. What should you do?

A.Create an Azure Policy initiative that includes the 'Configure Microsoft Defender for SQL to be enabled' policy and assign it to the root management group.
B.Use Azure Blueprints to deploy a predefined ARM template that enables Defender for SQL.
C.Create a script that runs periodically to check and enable Defender for SQL on all databases.
D.Assign the 'SQL Security Manager' role to a central team to manually enable Defender for SQL.
AnswerA

Azure Policy enforces compliance across all subscriptions automatically.

Why this answer

Option A is correct because Azure Policy provides a scalable, automated, and continuous enforcement mechanism across all subscriptions. By creating an initiative that includes the 'Configure Microsoft Defender for SQL to be enabled' policy and assigning it to the root management group, you ensure that any new or existing subscription inherits the policy, and non-compliant resources are automatically remediated or flagged. This approach aligns with the requirement for a strict, company-wide policy without manual intervention.

Exam trap

The trap here is that candidates often confuse Azure Blueprints (which are for initial deployment and governance) with Azure Policy (which provides continuous enforcement and remediation), leading them to choose Option B instead of A.

How to eliminate wrong answers

Option B is wrong because Azure Blueprints deploy resources at creation time but do not continuously enforce or remediate non-compliant resources after deployment; they are not a real-time policy enforcement tool. Option C is wrong because a periodic script is not scalable, introduces latency between checks, and does not provide continuous compliance monitoring or automatic remediation like Azure Policy does. Option D is wrong because assigning the 'SQL Security Manager' role to a central team relies on manual processes, which are error-prone, not scalable, and violate the requirement for an automated approach.

Full explanation →
651
MCQhard

You manage an Azure SQL Managed Instance in the East US region. To meet a 5-second RPO, you configure a failover group with a secondary in West US. During a planned maintenance, the primary becomes unavailable. The failover group is set to automatic with a grace period of 1 hour. You need to minimize downtime. What should you do?

A.Drop the failover group and create a new one with immediate failover.
B.Wait for the automatic failover to occur after the grace period.
C.Alter the failover group grace period to 0 and wait for automatic failover.
D.Initiate a manual forced failover to the secondary.
AnswerD

Manual failover instantly promotes the secondary.

Why this answer

Option D is correct because performing a manual failover immediately reduces downtime without waiting for the grace period. Option A is wrong because waiting for automatic failover would take up to 1 hour. Option B is wrong because altering the grace period requires downtime.

Option C is wrong because creating a new secondary takes time.

Full explanation →
652
MCQeasy

You are migrating an on-premises SQL Server database with 5 TB of data to Azure SQL Database. The database has multiple large tables and indexes. You need to minimize downtime during migration. Which service should you use?

A.Export to BACPAC and import using SqlPackage
B.Azure Database Migration Service with online migration mode
C.Backup and restore using URL with log shipping
D.Transactional replication from on-premises to Azure SQL Database
AnswerB

DMS online mode minimizes downtime by continuously replicating changes.

Why this answer

Azure Database Migration Service (DMS) with online migration mode is the correct choice because it minimizes downtime by continuously synchronizing ongoing changes from the on-premises SQL Server to Azure SQL Database while the initial bulk data copy is in progress. This allows you to cut over to the target with only a brief pause, which is critical for a 5 TB database where offline methods would cause extended unavailability.

Exam trap

The trap here is that candidates often confuse Azure SQL Database with SQL Server on Azure VMs, assuming that native backup/restore or log shipping (Option C) is available for Azure SQL Database, when in fact those features are only supported for IaaS-based SQL Server instances.

How to eliminate wrong answers

Option A is wrong because exporting a 5 TB database to a BACPAC file and importing via SqlPackage is an offline process that locks the database for the entire duration, causing significant downtime, and the BACPAC format is not optimized for very large databases, often leading to failures or extremely slow performance. Option C is wrong because backup and restore using URL with log shipping is not supported for Azure SQL Database (it is only supported for SQL Server on Azure VMs or on-premises); Azure SQL Database does not allow direct log shipping or restore of native backups from on-premises. Option D is wrong because transactional replication from on-premises to Azure SQL Database is not supported as a publisher-to-subscriber topology for Azure SQL Database as a target; Azure SQL Database can only be a subscriber in transactional replication, and even then, it requires specific configurations and does not support all data types or schema changes, making it unsuitable for a full migration of a 5 TB database with indexes.

Full explanation →
653
MCQhard

You have an Azure SQL Database with Intelligent Insights enabled. You receive an alert that 'SQLInsights: Resource utilization is consistently high'. You need to determine whether the issue is caused by an increase in user workload or a degradation in query performance. Which Intelligent Insights dimension should you review?

A.Metric
B.Severity
C.Impact
D.Resource type
AnswerC

Shows cause: workload increase or query regression.

Why this answer

Option A is correct because the 'Impact' dimension shows whether performance degradation is due to increased workload or query regression. Option B is wrong because 'Metric' shows the metric name, not root cause. Option C is wrong because 'Resource type' is the Azure resource type.

Option D is wrong because 'Severity' indicates alert level, not cause.

Full explanation →
654
MCQeasy

You have an Azure SQL Managed Instance with a database that is used for reporting. The reporting queries are read-only and can tolerate some latency. You want to offload the reporting workload from the primary instance to a secondary read-only replica. Which feature should you use?

A.SQL Server Integration Services (SSIS)
B.Readable secondary in a failover group
C.Transaction replication
D.Auto-failover group with read-write secondary
AnswerB

Allows read-only queries to be routed to the secondary.

Why this answer

Option A is correct because failover groups with readable secondary allow read-only routing. Option B is wrong because auto-failover groups are for disaster recovery, not read-only offloading. Option C is wrong because SQL Server Integration Services is for ETL.

Option D is wrong because transaction replication requires additional configuration and is not native to Managed Instance.

Full explanation →
655
Multi-Selecthard

Which THREE actions can help reduce the frequency of parameter-sensitive plan (PSP) problems in Azure SQL Database? (Choose three.)

Select 3 answers
A.Use the Optimize for ad hoc workloads setting (or OPTIMIZE FOR UNKNOWN).
B.Disable parameter sniffing by using the DISABLE_PARAMETER_SNIFFING hint.
C.Add the RECOMPILE query hint to problematic queries.
D.Create separate cached plans using forced parameterization.
E.Enable Query Store and use the Performance Dashboard.
AnswersA, C, D

Helps balance plan choice.

Why this answer

Options A, B, and C are correct. Using RECOMPILE forces a new plan per execution, optimizing for unknown uses average distribution, and creating multiple cached plans via parameterization. Option D is wrong because query hints force specific plans.

Option E is wrong because it only captures data.

Full explanation →
656
MCQmedium

You are configuring automated backup retention for Azure SQL Managed Instance. The compliance policy requires that you be able to restore a database to any point within the last 90 days, and that you keep backups for a minimum of 7 years for auditing purposes. Which backup retention policy should you configure?

A.Set PITR retention to 35 days and configure a long-term retention (LTR) policy to keep weekly backups for 7 years.
B.Set point-in-time restore (PITR) retention to 90 days.
C.Set PITR retention to 90 days and configure geo-replication to achieve the 7-year retention.
D.Configure geo-redundant backup storage with a retention of 90 days.
AnswerA

PITR covers 35 days; LTR can retain backups for up to 10 years, satisfying the 7-year requirement.

Why this answer

Azure SQL Managed Instance's point-in-time restore (PITR) retention is capped at 35 days, so to meet the 90-day restore requirement, you must combine a 35-day PITR policy with a long-term retention (LTR) policy that keeps weekly full backups for 7 years. LTR allows you to restore to any point within the LTR window by using full, differential, and log backups, satisfying both the 90-day point-in-time restore and the 7-year auditing compliance.

Exam trap

The trap here is that candidates assume PITR retention can be extended to any value, but Azure SQL Managed Instance enforces a hard 35-day maximum, forcing you to combine PITR with LTR to meet longer retention requirements.

How to eliminate wrong answers

Option B is wrong because PITR retention in Azure SQL Managed Instance has a maximum of 35 days, not 90 days, so it cannot meet the 90-day restore requirement. Option C is wrong because PITR retention cannot be set to 90 days (max 35), and geo-replication does not provide backup retention; it provides disaster recovery but does not extend backup retention to 7 years. Option D is wrong because geo-redundant backup storage (RA-GRS) only affects storage redundancy, not retention duration; it still cannot exceed the 35-day PITR limit and does not address the 7-year auditing requirement.

Full explanation →
657
MCQhard

Your company has a SQL Server on Azure VM hosting a critical database. You need to ensure high availability with automatic failover and no data loss during a planned patching event. The solution must minimize cost. Which configuration should you use?

A.Migrate to Azure SQL Database Managed Instance and configure geo-replication.
B.Configure log shipping with a secondary VM in another region.
C.Use a single VM with a failover cluster instance (FCI) using Storage Spaces Direct.
D.Deploy two Azure VMs in an availability set, configure SQL Server Always On availability group with synchronous commit and automatic failover.
AnswerD

Synchronous replication ensures zero data loss; automatic failover works for planned patching.

Why this answer

Option A is correct because SQL Server Always On availability groups with synchronous replication and automatic failover meet the requirement of no data loss and automatic failover. Option B is wrong because FCI with Storage Spaces Direct does not provide automatic failover for planned patching. Option C is wrong because log shipping is not automatic.

Option D is wrong because Azure SQL Database Managed Instance is not for SQL Server on Azure VM.

Full explanation →
658
MCQhard

You are reviewing a PowerShell script that is part of an Azure Automation runbook. The script is intended to monitor resource usage of an Azure SQL Database and trigger an alert if DTU usage exceeds 80%. The script runs successfully but does not trigger the alert. What is the most likely reason?

A.The script must use the -OutputAs parameter to return results.
B.The script uses Out-GridView, which is not supported in Azure Automation.
C.The Invoke-SqlCmd cmdlet is not compatible with Azure SQL Database.
D.The query syntax is incorrect for Azure SQL Database.
AnswerB

Out-GridView requires an interactive session and does not work in Azure Automation runbooks.

Why this answer

The script uses 'Out-GridView', which displays results in a GUI window. In Azure Automation, there is no interactive desktop, so the command fails silently or does not output anything. The alert logic likely depends on the output.

Option B is wrong because 'Invoke-SqlCmd' works with Azure SQL Database if the credential has access. Option C is wrong because the 'Query' is valid. Option D is wrong because 'Out-GridView' does not block execution but prevents output from being captured.

Full explanation →
659
MCQhard

Refer to the exhibit. You are deploying an Azure SQL Database using this ARM template. After deployment, you need to automate the scaling of the database to a higher service tier when DTU consumption exceeds 80% for 5 minutes. Which Azure service should you use to trigger the scaling?

A.Azure SQL Analytics
B.Azure Automation Update Management
C.SQL Server Agent
D.Azure Monitor metric alert
AnswerD

Metric alerts can trigger runbooks to scale the database.

Why this answer

Option B is correct because Azure Monitor metric alerts can trigger an Automation runbook that scales the database. The exhibit shows a General Purpose database, which supports scaling. Option A is for SQL Agent.

Option C is for on-premises. Option D is for performance data, not triggering actions.

Full explanation →
660
MCQhard

Your company uses Azure SQL Managed Instance and needs to automate patching and maintenance. The compliance team requires that all maintenance windows be predefined and that no maintenance occurs outside these windows. What should you configure?

A.Configure a maintenance configuration for the virtual machine scale set
B.Set a maintenance window in the Azure SQL Database server settings
C.Use Azure Update Management to schedule patching
D.Assign a maintenance configuration to the SQL Managed Instance
AnswerD

Azure Maintenance Configurations allow you to define maintenance windows for SQL Managed Instance.

Why this answer

Option D is correct because Azure Maintenance Configurations allow you to schedule maintenance windows for Azure SQL Managed Instance, ensuring compliance. Option A is for Azure VMs, not SQL MI. Option B is for patching at the OS level, not applicable.

Option C is for Azure SQL Database, not Managed Instance.

Full explanation →
661
Multi-Selectmedium

You need to configure authentication for Azure SQL Database. Which TWO options are supported?

Select 2 answers
A.Multi-factor authentication
B.Certificate-based authentication
C.Azure Active Directory authentication
D.SQL authentication
E.Windows authentication
AnswersC, D

Correct: Azure AD authentication is supported.

Why this answer

Azure SQL Database supports Azure Active Directory (Azure AD) authentication, which allows you to centrally manage identities and access. This includes support for modern authentication protocols like OAuth 2.0 and integration with Azure AD, enabling features such as conditional access and managed identities.

Exam trap

The trap here is that candidates often confuse Azure AD authentication with Windows authentication, thinking that because Azure AD is Microsoft's cloud identity service, it supports the same Kerberos-based Windows authentication as on-premises SQL Server, but Azure SQL Database does not support Windows authentication at all.

Full explanation →
662
MCQmedium

A company is deploying Azure SQL Managed Instance and needs to ensure that the failover group provides automatic failover with zero data loss during a regional outage. The secondary region is 500 miles away. Which data replication mode should be configured?

A.Synchronous replication
B.Geo-replication
C.Snapshot replication
D.Asynchronous replication
AnswerA

Synchronous replication ensures zero data loss.

Why this answer

Azure SQL Managed Instance failover groups require synchronous replication to guarantee zero data loss during automatic failover. Synchronous replication ensures that transactions are committed on both the primary and secondary replicas before acknowledging the commit, so no data is lost even if the primary region fails. This mode is mandatory for failover groups with the 'automatic failover' and 'zero data loss' requirements, despite the 500-mile distance which introduces latency.

Exam trap

The trap here is that candidates often assume asynchronous replication is acceptable for disaster recovery, but the question explicitly requires 'zero data loss,' which only synchronous replication can guarantee in a failover group context.

How to eliminate wrong answers

Option B (Geo-replication) is wrong because geo-replication uses asynchronous replication by default, which can result in data loss of up to 5 seconds of transactions during a failover. Option C (Snapshot replication) is wrong because snapshot replication is a point-in-time copy mechanism used for reporting or backups, not for continuous synchronization or automatic failover. Option D (Asynchronous replication) is wrong because asynchronous replication does not guarantee zero data loss; it commits transactions on the primary first and replicates later, potentially losing uncommitted data during a sudden outage.

Full explanation →
663
MCQhard

You have a SQL Server on Azure VM running SQL Server 2022 with a Distributed availability group (DAG) for disaster recovery across two Azure regions. You need to ensure that the secondary replica can become the primary without data loss if the primary region fails. What setting must be configured?

A.Backup preference set to secondary
B.Asynchronous commit mode on the distributed AG
C.Synchronous commit mode on the distributed AG
D.Automatic seeding enabled
AnswerC

Synchronous commit ensures no data loss on failover.

Why this answer

Option D is correct because synchronous commit mode with automatic seeding ensures that all committed transactions are replicated to the secondary before acknowledgment. Option A is incorrect because asynchronous commit may cause data loss. Option B is incorrect because backup priority does not affect data loss.

Option C is incorrect because automatic seeding is for initial setup, not ongoing synchronization.

Full explanation →
664
MCQhard

You are designing a secure environment for Azure SQL Managed Instance. You need to ensure that all connections from client applications use a private endpoint, and no public endpoint is accessible. What should you configure?

A.Set the public endpoint to 'Enabled' and create a firewall rule with a deny-all rule.
B.Enable the public endpoint and use a VNet service tag in a firewall rule.
C.Set the public endpoint to 'Disabled' and configure a private endpoint in the same virtual network with a private DNS zone.
D.Use a VNet service endpoint for Azure SQL Managed Instance and configure a network security group.
AnswerC

This completely removes public access and forces private connections.

Why this answer

Option C is correct because Azure SQL Managed Instance requires the public endpoint to be explicitly disabled to ensure no public traffic can reach it. By configuring a private endpoint within the same virtual network and linking it to a private DNS zone, client applications resolve the managed instance's fully qualified domain name (FQDN) to the private IP address, enforcing all connections through the private endpoint and eliminating public endpoint accessibility.

Exam trap

The trap here is that candidates often confuse Azure SQL Database's firewall rules and service endpoints with Azure SQL Managed Instance's networking model, where only private endpoints can fully disable public access, and service endpoints are not a valid option for Managed Instance.

How to eliminate wrong answers

Option A is wrong because setting the public endpoint to 'Enabled' and adding a deny-all firewall rule does not truly disable the public endpoint; the endpoint remains exposed and could be targeted by attacks, and firewall rules do not prevent DNS resolution to the public IP. Option B is wrong because enabling the public endpoint and using a VNet service tag in a firewall rule still leaves the public endpoint accessible; service tags only help filter traffic but do not block public internet connectivity. Option D is wrong because VNet service endpoints are not supported for Azure SQL Managed Instance; only private endpoints can be used to connect securely, and network security groups (NSGs) cannot restrict traffic to a managed instance's private endpoint.

Full explanation →
665
MCQmedium

Your company is deploying a new Azure SQL Database that will store Personally Identifiable Information (PII). The security team requires that all queries against the database are audited and that any access to sensitive columns is logged with the actual data values. Which combination of features should you enable?

A.Enable Transparent Data Encryption (TDE) and configure a server-level audit specification for schema changes.
B.Enable Dynamic Data Masking on the sensitive columns and use Azure SQL Database Auditing.
C.Enable SQL Server Audit on the database and create a database audit specification that captures SELECT and UPDATE on the sensitive columns.
D.Enable Azure SQL Database Auditing and configure the diagnostic settings to send logs to a Log Analytics workspace.
AnswerC

Correct: SQL Server Audit can capture actual data values when auditing data modification operations.

Why this answer

Option C is correct because SQL Server Audit (available in Azure SQL Database) allows you to create a database audit specification that captures SELECT and UPDATE operations on specific columns, including the actual data values accessed. This meets the security requirement to log access to sensitive columns with the actual data, whereas other options either lack column-level detail or do not capture the data values.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking (which only hides data at query time) with auditing that captures actual values, or they assume Azure SQL Database Auditing alone logs data values, when in fact it only logs metadata about the query, not the sensitive data itself.

How to eliminate wrong answers

Option A is wrong because Transparent Data Encryption (TDE) encrypts data at rest but does not audit queries or log access to sensitive columns; server-level audit specifications for schema changes only track structural modifications, not data access. Option B is wrong because Dynamic Data Masking obfuscates data from non-privileged users but does not log actual data values; Azure SQL Database Auditing alone captures query events but not the actual data values accessed unless combined with a feature like SQL Server Audit that includes data capture. Option D is wrong because Azure SQL Database Auditing with diagnostic settings sends audit logs to Log Analytics but, by default, does not include the actual data values from queries; it only records that a query occurred, not the sensitive column values.

Full explanation →
666
MCQeasy

You manage an Azure SQL Database with the Premium service tier. You need to reduce storage costs by automatically deleting old audit logs that are older than 90 days. What should you configure?

A.Create an Extended Events session that filters and deletes old audit events.
B.Reduce the backup retention period to 90 days.
C.Set the audit retention policy to 90 days in the server's audit settings.
D.Configure a lifecycle management policy on the Azure Storage account that stores the audit logs.
AnswerD

Automatically deletes blobs older than 90 days.

Why this answer

Option B is correct because Azure SQL Database audit logs are stored in Azure Storage and a retention policy can be set on the storage account to automatically delete blobs after a specified number of days. Option A is wrong because the audit retention policy in SQL Database is only for the number of days to retain audit records, but it does not automatically delete from storage; deletion is handled by storage lifecycle management. Option C is wrong because Extended Events are not the primary method for audit log cleanup.

Option D is wrong because backup retention is unrelated to audit logs.

Full explanation →
667
MCQeasy

You are tasked with designing a disaster recovery plan for an Azure SQL Database. The database is mission-critical and must have a Recovery Point Objective (RPO) of 5 seconds and a Recovery Time Objective (RTO) of 30 minutes in case of a regional failure. Which configuration should you implement?

A.Long-term backup retention
B.Automated backups with geo-restore
C.Zone-redundant configuration
D.Active geo-replication with failover group
AnswerD

Provides low RPO and fast RTO.

Why this answer

Active geo-replication with failover groups is the correct choice because it provides a continuous, synchronous replication mechanism that can achieve an RPO of 5 seconds and an RTO of 30 minutes during a regional failure. The failover group automates the process of failing over to a readable secondary database in a paired region, meeting both the stringent RPO and RTO requirements without manual intervention.

Exam trap

The trap here is that candidates often confuse zone-redundant configuration (which only protects against zone failures within a region) with active geo-replication (which protects against a full regional outage), leading them to select option C despite the requirement for regional disaster recovery.

How to eliminate wrong answers

Option A is wrong because long-term backup retention is designed for archival and compliance purposes, not for meeting low RPO/RTO targets; it retains backups for years but cannot restore within 30 minutes or with a 5-second data loss. Option B is wrong because automated backups with geo-restore have an RPO of up to 1 hour (based on backup frequency) and an RTO that can exceed several hours due to the time required to restore a full backup to a different region, failing both the 5-second RPO and 30-minute RTO. Option C is wrong because zone-redundant configuration protects against a single availability zone failure within a region, not a complete regional outage, and thus cannot satisfy the requirement for regional disaster recovery.

Full explanation →
668
MCQmedium

You are deploying Azure SQL Database for a multi-tenant SaaS application. Each tenant has its own database, and you need to ensure that resource usage is isolated and predictable. You also need to manage performance at the tenant level. Which Azure SQL Database offering should you choose?

A.Azure SQL Database elastic pools with per-database min/max DTU or vCore settings.
B.Azure SQL Database serverless compute tier.
C.Azure SQL Database single databases with DTU-based tier.
D.Azure SQL Managed Instance with multiple databases.
AnswerA

Elastic pools provide resource isolation and predictable performance per database.

Why this answer

Azure SQL Database elastic pools with per-database min/max DTU or vCore settings are the correct choice because they provide resource isolation and predictable performance at the tenant level. Elastic pools allow you to allocate a shared pool of resources across multiple databases while setting per-database minimum and maximum limits, ensuring that no single tenant can consume excessive resources and that each tenant gets a guaranteed baseline. This directly addresses the multi-tenant SaaS requirement for isolated and predictable resource usage.

Exam trap

The trap here is that candidates often confuse elastic pools with serverless or single databases, thinking that serverless provides isolation or that single databases are the only way to guarantee performance, but they miss the key requirement for per-tenant resource control and cost efficiency that elastic pools uniquely offer.

How to eliminate wrong answers

Option B (Azure SQL Database serverless compute tier) is wrong because it is designed for intermittent, unpredictable workloads with auto-scaling and auto-pausing, which does not provide the predictable, isolated resource guarantees needed for multi-tenant SaaS with per-tenant performance management. Option C (Azure SQL Database single databases with DTU-based tier) is wrong because each database is isolated with fixed resources, but it lacks the ability to manage performance at the tenant level across a pool; you would need to over-provision for each tenant, leading to inefficiency and higher costs. Option D (Azure SQL Managed Instance with multiple databases) is wrong because it is a fully managed instance of SQL Server with shared resources across databases, offering no per-database resource isolation or min/max settings, making it unsuitable for predictable tenant-level performance isolation.

Full explanation →
669
Multi-Selectmedium

Which TWO actions can help reduce `PAGEIOLATCH_UP` waits on an Azure SQL Database?

Select 2 answers
A.Add a nonclustered index to reduce update I/O.
B.Reduce MAXDOP to 1.
C.Increase the log write throughput by moving to a higher tier.
D.Increase the buffer pool size by adding more memory.
AnswersA, C

Index improvement reduces the I/O needed for updates.

Why this answer

A is correct because adding a nonclustered index can reduce the number of pages that need to be updated during write operations, thereby decreasing the I/O load that causes `PAGEIOLATCH_UP` waits. These waits occur when a task is waiting for a page I/O request to complete while holding an update latch. By optimizing the index structure, fewer pages are modified, reducing the I/O contention.

Exam trap

The trap here is that candidates often confuse `PAGEIOLATCH_UP` waits with general I/O bottlenecks and incorrectly assume that adding memory or reducing parallelism will resolve the issue, when in fact the wait is specifically tied to page-level latch contention during I/O, which is best addressed by reducing the I/O workload through indexing or increasing I/O throughput.

Full explanation →
670
MCQeasy

You have an Azure SQL Database that is experiencing high wait times on RESOURCE_SEMAPHORE waits. You need to identify the root cause. What should you check?

A.Blocking and deadlocks
B.High CPU usage
C.Disk I/O bottlenecks
D.Queries with large memory grants
AnswerD

RESOURCE_SEMAPHORE waits indicate memory grant pressure.

Why this answer

Option A is correct because RESOURCE_SEMAPHORE waits indicate memory grant pressure, often due to large queries requiring memory. Option B is incorrect because disk I/O causes PAGEIOLATCH waits. Option C is incorrect because blocking causes LCK_M_* waits.

Option D is incorrect because CPU pressure causes SOS_SCHEDULER_YIELD waits.

Full explanation →
671
MCQhard

You are designing a database solution for an e-commerce application that requires high read throughput with sub-5 ms latency. The application runs on Azure VMs. You need to choose between Azure SQL Database and Azure SQL Managed Instance. Which factor most strongly supports choosing Azure SQL Database over SQL Managed Instance?

A.Azure SQL Database offers a wider range of built-in instance-level features.
B.Azure SQL Database supports Hyperscale tier with sub-5 ms read latency on local SSDs.
C.Azure SQL Database allows databases up to 100 TB in size.
D.Azure SQL Database supports active geo-replication with read-scale secondaries.
AnswerB

Hyperscale uses a distributed architecture with local SSD caches on compute nodes, enabling low latency reads.

Why this answer

Option B is correct because the Hyperscale tier of Azure SQL Database uses a distributed architecture with local SSD-based buffer pool extensions on compute nodes, enabling sub-5 ms read latency for data cached locally. This directly meets the e-commerce application's requirement for high read throughput with low latency, whereas Azure SQL Managed Instance relies on remote premium page blobs for storage, which introduces higher latency for reads that are not cache-resident.

Exam trap

The trap here is that candidates often assume Azure SQL Managed Instance provides lower latency because it is 'closer to SQL Server,' but they overlook that Azure SQL Database Hyperscale's local SSD caching is specifically designed for ultra-low-latency reads, while Managed Instance's storage architecture introduces additional network hops.

How to eliminate wrong answers

Option A is wrong because Azure SQL Managed Instance actually offers a wider range of built-in instance-level features (e.g., SQL Agent, cross-database queries, linked servers) compared to Azure SQL Database, which is a platform-as-a-service offering with fewer instance-scoped capabilities. Option C is wrong because Azure SQL Managed Instance supports databases up to 100 TB in size (with Business Critical tier), while Azure SQL Database Hyperscale supports up to 100 TB as well, so this is not a differentiating factor. Option D is wrong because both Azure SQL Database and Azure SQL Managed Instance support active geo-replication with readable secondaries; this feature does not uniquely favor Azure SQL Database over Managed Instance.

Full explanation →
672
MCQmedium

You manage a critical SQL Server on Azure Virtual Machine running SQL Server 2019. The VM is in a single availability set. You need to ensure automatic failover in case of a zone-level failure. What should you implement?

A.Deploy the VM in an availability zone and configure an Always On availability group with a secondary replica in another zone.
B.Place the VM in an availability set.
C.Enable geo-replication to another Azure region.
D.Configure automated backups with long-term retention.
AnswerA

Zone-redundant deployment provides automatic failover for zone failures.

Why this answer

Option A is correct because deploying the VM in an availability zone with Azure SQL Managed Instance or SQL Server on Azure VM with Always On availability groups using zone-redundant storage provides zone-level resilience. Option B is incorrect because geo-replication is for regional failures, not zonal. Option C is incorrect because automated backups do not provide failover.

Option D is incorrect because a single VM in an availability set protects against rack failures but not zone failures.

Full explanation →
673
MCQmedium

Your Azure SQL Database is configured with active geo-replication. You need to automate the failover process in case of a regional outage. The solution should ensure minimal data loss and support testing without affecting the production environment. What should you use?

A.Configure an Azure Traffic Manager profile with endpoint monitoring and failover.
B.Use Azure Load Balancer with a health probe to redirect traffic.
C.Create an Azure Automation runbook that monitors health and executes a manual failover using PowerShell cmdlet Start-AzSqlDatabaseFailover.
D.Enable automatic failover for the geo-replication group.
AnswerC

Azure Automation can run a script to check health and trigger failover, providing automated response while allowing manual testing.

Why this answer

Azure SQL Database supports active geo-replication with manual or forced failover. To automate, you can use Azure Automation runbooks with the Start-AzSqlDatabaseFailover cmdlet. Option A is correct.

Option B is for virtual networks. Option C is for load balancing. Option D is incorrect because geo-replication does not have automatic failover.

Full explanation →
674
MCQeasy

You are monitoring an Azure SQL Database and notice that the average CPU usage is consistently above 90%. The database is using the S3 service tier. What should you do first to resolve this performance issue?

A.Enable read scale-out
B.Wait for Automatic tuning to provide recommendations
C.Scale down to a lower service tier
D.Scale up to a higher service tier
AnswerD

Provides more CPU resources to handle the load.

Why this answer

Scaling up to a higher service tier (e.g., S4) provides more CPU resources. Option A is correct. Option B is wrong because the database is not at the maximum tier (S12 is max for Standard).

Option C is wrong because enabling read scale-out does not increase CPU. Option D is wrong because query store recommendations take time.

Full explanation →
675
Multi-Selecthard

You are tasked with designing a disaster recovery solution for Azure SQL Database that meets an RPO of 5 seconds and an RTO of 30 seconds during a regional outage. Which TWO features should you combine?

Select 2 answers
A.General Purpose service tier
B.Business Critical service tier
C.Failover groups with active geo-replication
D.Geo-restore from GRS backups
E.Zone-redundant configuration
AnswersB, C

Provides low latency and high availability.

Why this answer

Options B and E are correct because failover groups with active geo-replication provide low RPO and RTO, and using Business Critical service tier in both regions ensures low latency and high performance. Options A, C, and D are incorrect because zone redundancy is within region, geo-restore has high RTO, and General Purpose might not meet RPO.

Full explanation →
Page 8

Page 9 of 13

Page 10

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 →