Courseiva

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

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

Take a mock examExam hub
Page 7

Page 8 of 13

Page 9
526
MCQhard

Your company has a strict policy that Azure SQL Database backups must be encrypted with customer-managed keys stored in Azure Key Vault. You configure TDE with AKV integration. After a key rotation, you find that long-running queries start failing with encryption errors. What is the most likely cause?

A.The service principal used for AKV access has expired.
B.The previous key version was disabled or deleted in AKV.
C.The new key is in a different Azure region than the database.
D.The database is using service-managed TDE and cannot switch to customer-managed keys.
AnswerB

TDE requires all previous key versions to be enabled to decrypt existing data.

Why this answer

When TDE is configured with Azure Key Vault (AKV) integration, the database uses the current key version from AKV to encrypt and decrypt data. If the previous key version is disabled or deleted during a key rotation, any long-running queries that still rely on that specific key version for decryption will fail with encryption errors. The database cannot automatically fall back to the new key version for in-flight operations that started before the rotation.

Exam trap

The trap here is that candidates often assume key rotation is seamless and never causes failures, overlooking that disabling or deleting the previous key version can break in-flight operations that still depend on it.

How to eliminate wrong answers

Option A is wrong because the service principal used for AKV access is a separate authentication entity; its expiration would cause a persistent access failure, not selective failures on long-running queries after a key rotation. Option C is wrong because Azure Key Vault and Azure SQL Database can be in different regions; cross-region access is supported and does not cause encryption errors. Option D is wrong because the scenario explicitly states that TDE with AKV integration is configured, meaning customer-managed keys are already in use; the database is not using service-managed TDE.

Full explanation →
527
MCQhard

You are reviewing a JSON configuration for an Azure SQL Database. The exhibit shows the database properties. Which statement about this database is correct?

A.The database has one high-availability replica.
B.The configuration is invalid because General Purpose tier cannot be zone-redundant.
C.The database uses locally-redundant backup storage.
D.The database is in General Purpose tier and supports zone redundancy.
AnswerD

The database is in General Purpose tier and zoneRedundant is true, which is a valid configuration as zone redundancy is supported for General Purpose.

Why this answer

The JSON configuration shows zoneRedundant set to true and the service tier is General Purpose. As of current Azure SQL Database documentation, zone redundancy is supported for the General Purpose tier (vCore purchasing model) in many regions. Therefore, the configuration is valid.

Option D correctly states that the database is in General Purpose tier and supports zone redundancy. Option A is incorrect because highAvailabilityReplicaCount is 0, not 1. Option B is incorrect because zone redundancy is available for General Purpose.

Option C is incorrect because backupStorageRedundancy is Geo (geo-redundant), not locally-redundant.

Exam trap

Candidates may incorrectly assume that zone redundancy is only for Business Critical, but Azure now supports it for General Purpose (vCore) in many regions.

Full explanation →
528
MCQeasy

You are deploying an Azure SQL Database for a new application that requires high availability with automatic failover within the same region. The database must be able to recover from a zonal failure without data loss. Which deployment option should you use?

A.Hyperscale tier with zone redundancy
B.Standard tier with failover group
C.Business Critical tier with zone redundancy enabled
D.Basic tier with active geo-replication
AnswerC

Business Critical tier with zone redundancy provides zonal failover with zero data loss.

Why this answer

The Business Critical tier with zone redundancy enabled provides the highest level of availability within a single Azure region by synchronously replicating data across three different availability zones. This ensures that if one zone fails, automatic failover occurs with no data loss because all transactions are committed to at least two replicas before being acknowledged. This meets the requirement for zonal failure recovery without data loss.

Exam trap

The trap here is that candidates often confuse zone redundancy with geo-replication, assuming that any tier with failover groups or geo-replication can handle zonal failures, but only zone-redundant configurations within the same region guarantee zero data loss during a zonal outage.

How to eliminate wrong answers

Option A is wrong because the Hyperscale tier, while offering high performance and fast scaling, does not support zone redundancy for the primary replica; it only supports zone redundancy for secondary replicas, so a zonal failure could cause data loss or downtime for the primary. Option B is wrong because the Standard tier does not offer zone redundancy; it uses local redundant storage (LRS) and can only provide availability within a single zone, so a zonal failure would result in data loss and downtime. Option D is wrong because the Basic tier does not support zone redundancy or geo-replication; it is designed for development and testing with no SLA for high availability, and active geo-replication is not available on the Basic tier.

Full explanation →
529
MCQhard

Your company has a SQL Server on Azure VM running a critical database. The VM is in a single availability set. You need to achieve a Recovery Point Objective (RPO) of 5 minutes and a Recovery Time Objective (RTO) of 15 minutes in case of an Azure region failure. What should you implement?

A.Configure SQL Server Always On Availability Groups with synchronous commit to a secondary VM in another region.
B.Configure Azure Site Recovery to replicate the VM to a secondary region.
C.Use Azure Backup with daily backups and geo-restore.
D.Store database files on geo-redundant storage (GRS) and enable auto-failover.
AnswerB

Azure Site Recovery provides replication with RPO of seconds to minutes and RTO of minutes.

Why this answer

Azure Site Recovery (ASR) can replicate the Azure VM to a secondary region with a replication frequency as low as 5 minutes, meeting the RPO of 5 minutes. Upon failover, the RTO can be less than 15 minutes if the VM is properly configured and failover is regularly tested. Option A (Always On Availability Groups with synchronous commit) can achieve low RPO, but across regions, synchronous commit introduces significant latency and requires at least three replicas (including a witness) for automatic failover, making it complex and potentially unable to meet the RTO consistently.

Option C (daily backups) has an RPO of 24 hours, failing the 5-minute requirement. Option D (geo-redundant storage) provides storage-level replication but does not ensure application-consistent recovery or VM compute availability; auto-failover for storage does not fail over the VM itself. Therefore, ASR is the best choice for the specified RPO and RTO in a region failure scenario.

Full explanation →
530
MCQmedium

You have an Azure SQL Database that needs to be backed up daily using Azure Automation runbooks. The runbook must trigger an export of the database to a storage account. How should you configure the runbook to authenticate securely to Azure?

A.Use a shared access signature (SAS) token stored in the runbook
B.Use Automation Account credential assets
C.Enable a system-assigned managed identity for the Automation account
D.Store the SQL admin credentials as variables in the runbook
AnswerC

Managed identities provide secure authentication without storing credentials.

Why this answer

Managed Identity (system-assigned or user-assigned) is the recommended secure authentication method for Azure Automation runbooks, avoiding stored credentials. Option A uses credentials stored in the runbook, which is less secure. Option B uses automation account credentials, which still requires key management.

Option D is not a valid type.

Full explanation →
531
Multi-Selecthard

Which THREE of the following are required considerations when configuring a failover group for Azure SQL Managed Instance?

Select 3 answers
A.Both instances must be in the same partner region pair.
B.Both instances must have the same service tier.
C.The secondary instance must be in a different Azure region.
D.Automatic failover must be enabled.
E.The databases must be in the same resource group.
AnswersA, C, E

Correct: Failover groups require partner regions.

Why this answer

Options A, C, and E are correct. For Azure SQL Managed Instance failover groups, both instances must be in the same partner region pair (e.g., East US and West US). The secondary instance must be in a different Azure region to provide geo-redundancy.

The databases must be in the same resource group. Option B is incorrect because the secondary instance can have a different service tier than the primary. Option D is incorrect because automatic failover is not required; you can use manual failover.

Full explanation →
532
MCQmedium

You are reviewing a PowerShell script that configures auditing for an Azure SQL Database. The script sets an audit rule with the specified parameters. After running the script, you notice that SELECT operations are not being audited. What is the most likely cause?

A.The AuditActionGroup specified does not capture SELECT operations.
B.The retention days are set too low, causing logs to be overwritten.
C.The storage endpoint is incorrectly formatted.
D.The storage account access key is invalid.
AnswerA

DATABASE_OBJECT_CHANGE_GROUP captures DDL changes, not SELECT.

Why this answer

The script likely specifies an AuditActionGroup that does not include the group responsible for capturing SELECT operations. In Azure SQL Database auditing, SELECT operations are captured by the SUCCESSFUL_SCHEMA_OBJECT_ACCESS_GROUP or similar action groups. If the configured AuditActionGroup omits this group, SELECT statements will not be logged, even though other operations may be audited correctly.

Exam trap

The trap here is that candidates may assume all DML operations (including SELECT) are captured by default, but Azure SQL Database auditing requires explicit inclusion of the appropriate action group for SELECT operations.

How to eliminate wrong answers

Option B is wrong because retention days affect how long logs are kept, not which operations are captured; low retention may cause logs to be overwritten but does not prevent SELECT operations from being audited. Option C is wrong because an incorrectly formatted storage endpoint would cause all audit logs to fail to write, not selectively miss SELECT operations. Option D is wrong because an invalid storage account access key would prevent any audit logs from being written to the storage account, not specifically exclude SELECT operations.

Full explanation →
533
MCQmedium

You are managing an Azure SQL Database in the General Purpose service tier with 100 DTUs. The database supports an e-commerce application. Over the past week, you notice that CPU usage frequently reaches 100% during peak hours, causing query timeouts. You have identified that the most expensive query is a SELECT statement that joins five tables and returns aggregated sales data. You need to reduce CPU pressure without changing the service tier or adding indexes. What should you do?

A.Scale up to the Business Critical tier.
B.Increase the DTUs to 200.
C.Use Query Store to identify and force a more efficient execution plan.
D.Add a nonclustered index on the join columns.
AnswerC

Query Store can capture plan history and force a plan that uses less CPU without changing tier or indexes.

Why this answer

Using Query Store to identify and force a more efficient execution plan can reduce CPU usage without changing the service tier or adding indexes. Option A is incorrect because scaling to Business Critical changes the service tier, which is not allowed. Option B is incorrect because increasing DTUs changes the performance level and may increase cost, and it doesn't address the root cause of the expensive query.

Option D is incorrect because adding indexes is explicitly prohibited by the requirement.

Full explanation →
534
MCQmedium

You manage an Azure SQL Database that is experiencing performance degradation during peak hours. You suspect that the current pricing tier is insufficient. You need to increase performance with minimal downtime. Which action should you take?

A.Use the Azure portal to scale up the DTU or vCore service tier.
B.Create a new database on a higher tier and copy data manually.
C.Enable read scale-out to offload read workloads.
D.Implement horizontal sharding across multiple databases.
AnswerA

Scaling up is a dynamic operation with minimal downtime.

Why this answer

Scaling up the DTU or vCore service tier in the Azure portal is a dynamic scaling operation that typically completes within minutes and does not require application downtime. Azure SQL Database supports online scaling, meaning the database remains available during the transition, with only a brief connection failover at the end. This directly addresses the need to increase performance during peak hours with minimal disruption.

Exam trap

The trap here is that candidates might confuse scaling up (vertical scaling) with scaling out (horizontal scaling) or offloading reads, and choose a more complex or disruptive option instead of the straightforward, supported online scaling operation.

How to eliminate wrong answers

Option B is wrong because manually creating a new database and copying data introduces significant downtime and complexity, and is not necessary when Azure SQL Database supports online scaling. Option C is wrong because enabling read scale-out only offloads read-only workloads to a readable secondary replica; it does not increase the compute or storage capacity of the primary database to handle peak-hour performance degradation. Option D is wrong because horizontal sharding is a design pattern for distributing data across multiple databases to handle massive scale, not a quick operational fix for a single database experiencing performance issues during peak hours.

Full explanation →
535
MCQmedium

You are a database administrator for an e-commerce company that uses Azure SQL Database to store order data. The company is implementing a new policy that requires all database access to be audited, including both successful and failed attempts. Additionally, the audit logs must be retained for at least one year for compliance purposes. You need to configure auditing for the database. What should you do?

A.Use the 'Audit Logs' feature in the Azure portal to enable auditing and set retention to 365 days
B.Enable auditing on the database and configure it to send logs to a Log Analytics workspace, then set the retention in Log Analytics to 365 days
C.Enable auditing on the server and configure it to send logs to an event hub
D.Enable auditing on the database, set the audit log destination to a storage account, and configure retention to 365 days
AnswerD

This captures all database events and retains logs for one year.

Why this answer

Azure SQL Database auditing can be configured at the database level to send audit logs to a storage account, which allows you to set a retention policy of 365 days directly on the audit configuration. This meets the requirement to audit both successful and failed attempts and retain logs for at least one year for compliance.

Exam trap

The trap here is that candidates may confuse server-level auditing with database-level auditing or assume that Log Analytics or event hubs inherently support retention policies, but only storage accounts allow direct retention configuration within the audit settings.

How to eliminate wrong answers

Option A is wrong because there is no standalone 'Audit Logs' feature in the Azure portal; auditing is configured via the 'Auditing' blade under the database or server settings, and retention is set within the audit destination configuration, not a separate feature. Option B is wrong because while Log Analytics can be used as a destination for audit logs, retention is configured at the Log Analytics workspace level (not within the audit settings), and the requirement specifies retaining logs for at least one year, which is possible but not the most direct or default method for database-level auditing. Option C is wrong because enabling auditing on the server and sending logs to an event hub does not provide a built-in retention policy; event hubs are for real-time streaming and require additional services to store logs for a year, making it unsuitable for the retention requirement.

Full explanation →
536
MCQmedium

You are a database administrator for a healthcare company that uses Azure SQL Database with Hyperscale tier. The database contains patient records and is critical for operations. You need to automate the process of refreshing the staging database from the production database every night. The refresh process must occur during a maintenance window from 2:00 AM to 4:00 AM. The solution must use point-in-time restore to ensure consistency and must minimize the storage costs. Additionally, the automation must notify the operations team if the refresh fails. What should you do?

A.Use Elastic Database Jobs to run a T-SQL script that uses RESTORE DATABASE from a backup file.
B.Create an Azure Automation runbook that performs a point-in-time restore of the production database to a new database, then renames the databases to swap staging. Schedule the runbook during the maintenance window and configure alerts for failure.
C.Use Azure Data Factory to copy data from production to staging using a copy activity.
D.Use Azure SQL Database export to BACPAC from production and import to staging using Azure Automation.
AnswerB

Correct. Azure Automation runbook with PITR and database rename swap meets all requirements: automation, maintenance window, consistency, cost savings, and failure notification via alerts.

Why this answer

Azure Automation runbooks can schedule a point-in-time restore of the production database to a new database, then rename the databases to swap them, making the restored database the new staging database. This leverages the Hyperscale tier's fast restore and minimizes storage costs by avoiding multiple copies. Alerts can be configured to notify the operations team on failure.

Option A is incorrect because Elastic Database Jobs cannot perform restore operations; they are for running T-SQL scripts across databases, not restoring from backups. Option C is incorrect because Azure Data Factory is an ETL tool and cannot perform point-in-time restore of a database. Option D is incorrect because export/import via BACPAC is slower, more expensive, and does not guarantee point-in-time consistency.

Exam trap

Candidates may think that renaming databases after restore is complex, but Azure SQL Database supports renaming databases via T-SQL or PowerShell, making the swap straightforward.

Full explanation →
537
Multi-Selecteasy

You are configuring security for an Azure SQL Database that will be used by a web application. The application uses a connection string with SQL authentication. You need to protect the database from SQL injection attacks. Which two measures should you implement? (Choose two.)

Select 2 answers
A.Enable Transparent Data Encryption (TDE).
B.Use parameterized queries in the application.
C.Configure Dynamic Data Masking (DDM).
D.Implement stored procedures with input validation.
E.Enable Always Encrypted on sensitive columns.
AnswersB, D

Parameterized queries prevent injection.

Why this answer

Parameterized queries ensure that user input is treated as data, not executable code, by separating SQL logic from input values. This prevents attackers from injecting malicious SQL statements into the query string, which is the primary defense against SQL injection attacks.

Exam trap

The trap here is that candidates often confuse data-at-rest or data-masking features (TDE, DDM, Always Encrypted) with injection prevention, when in fact only query-level controls like parameterized queries and validated stored procedures directly mitigate SQL injection.

Full explanation →
538
Multi-Selecthard

Which THREE factors should you consider when choosing between vCore and DTU purchase models for Azure SQL Database performance optimization?

Select 3 answers
A.Only vCore supports Azure Hybrid Benefit.
B.DTU is simpler for customers who want a bundled metric.
C.vCore allows reserved instance pricing for cost savings.
D.vCore provides more predictable performance for consistent workloads.
E.Only DTU supports elastic pools.
AnswersB, C, D

DTU combines compute, storage, and I/O.

Why this answer

The DTU (Database Transaction Unit) model bundles compute, storage, and I/O into a single, simple metric, making it easier for customers who want a straightforward, pre-configured performance tier without needing to manage individual resources. This contrasts with the vCore model, which requires separate configuration of vCores, memory, and storage, offering more granular control but greater complexity.

Exam trap

The trap here is that candidates often assume Azure Hybrid Benefit or elastic pools are exclusive to one model, when in fact both features are available across vCore and DTU, leading to incorrect elimination of correct options like B, C, and D.

Full explanation →
539
MCQmedium

You have an Azure SQL Database in the Hyperscale service tier. You need to ensure that the database remains available during a single Azure zone failure. The solution must not require manual intervention. What should you configure?

A.Create a failover group with a secondary in a different zone.
B.Enable zone redundancy on the Hyperscale database.
C.Configure active geo-replication to a secondary server in a different zone.
D.Configure an auto-failover group with a secondary in a different zone.
AnswerB

Hyperscale zone redundancy provides automatic recovery from zone failure.

Why this answer

Hyperscale databases support zone redundancy, which automatically places database replicas in different availability zones, ensuring availability during a single zone failure without manual intervention. Option A is incorrect because failover groups with a secondary in a different zone are used for SQL Managed Instance or other service tiers, not for Hyperscale zone redundancy. Option C is incorrect because active geo-replication is for disaster recovery across regions, not for zone-level failures.

Option D is incorrect because auto-failover groups are for SQL Managed Instance or other tiers, and zone redundancy is the native solution for Hyperscale.

Full explanation →
540
MCQhard

Refer to the exhibit. An Azure SQL Database in the Standard tier (S2: 50 DTU) is consistently showing high DTU consumption. Which action would most effectively reduce DTU usage?

A.Create an index on the tables accessed by Query 1234
B.Increase the log_write_percent by adjusting transaction log settings
C.Scale up to a higher service tier (e.g., S3)
D.Rebuild all indexes in the database
AnswerA

Reducing logical reads via indexing directly lowers DTU usage.

Why this answer

Query 1234 is likely the primary contributor to high DTU consumption, as indicated by the exhibit (not shown here but implied). Creating an index on the tables it accesses can reduce the number of logical reads and improve query performance, directly lowering DTU usage without additional cost. This is the most effective action because it addresses the root cause—poor query performance—rather than masking the symptom with more resources.

Exam trap

The trap here is that candidates often choose scaling up (Option C) as a quick fix, not realizing that it only increases resource limits without addressing the underlying inefficient query or missing index, leading to continued high DTU usage and unnecessary cost.

How to eliminate wrong answers

Option B is wrong because increasing log_write_percent is not a user-configurable setting; it is a metric that reflects the percentage of DTU used for log writes, and adjusting transaction log settings (e.g., log file size or growth increment) does not directly reduce DTU consumption. Option C is wrong because scaling up to a higher service tier (e.g., S3) would increase available DTUs but does not reduce actual DTU usage; it merely accommodates the high consumption, which is a costly workaround. Option D is wrong because rebuilding all indexes in the database is a heavy operation that temporarily increases DTU consumption and may not address the specific query causing the high usage; it is a blunt, resource-intensive approach that could worsen the problem.

Full explanation →
541
MCQeasy

You are designing a security strategy for Azure SQL Managed Instance. The compliance team requires that all database backups be encrypted at rest using a customer-managed key. Which feature should you enable?

A.Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault.
B.Always Encrypted with a column master key in Azure Key Vault.
C.Transparent Data Encryption (TDE) with a service-managed key.
D.Row-Level Security (RLS) with a custom authorization function.
AnswerA

TDE encrypts the underlying database files and backups at rest.

Why this answer

Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault is the correct feature because it encrypts the database at rest, including all backup files, using a key that the customer controls. This satisfies the compliance requirement for customer-managed key encryption of backups, as TDE automatically encrypts backups when the database is encrypted.

Exam trap

The trap here is that candidates confuse Always Encrypted (which encrypts column data but not backups) with TDE (which encrypts the entire database and backups), leading them to select Always Encrypted when the requirement explicitly mentions backup encryption.

How to eliminate wrong answers

Option B is wrong because Always Encrypted protects sensitive data in transit and at rest within the database by encrypting specific columns, but it does not encrypt entire database backups; backups of a database with Always Encrypted columns are not automatically encrypted by this feature. Option C is wrong because TDE with a service-managed key uses a key managed by Azure, not a customer-managed key, so it does not meet the compliance requirement for customer-controlled encryption. Option D is wrong because Row-Level Security (RLS) controls access to rows in a table based on user authorization, but it does not provide any encryption of data at rest or backups.

Full explanation →
542
Matchingmedium

Match each Azure SQL Database command to its function.

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

Concepts
Matches

Sets database-level configuration options

Clears the procedure cache

Changes the recovery model of the database

Creates a database as a copy of another database

Why these pairings

In Azure SQL Database, CREATE DATABASE initializes a new database, ALTER DATABASE changes its configuration, DROP DATABASE removes it, and BACKUP DATABASE creates a backup (though backups are often automatic). Common confusions include swapping CREATE with DROP and ALTER with BACKUP due to similar administrative contexts.

Full explanation →
543
Multi-Selectmedium

You are configuring Azure SQL Database firewall rules. Which THREE actions require the 'Allow Azure services and resources to access this server' setting to be enabled? (Choose three.)

Select 3 answers
A.Azure Data Factory using a self-hosted integration runtime.
B.An on-premises application with a static public IP.
C.A virtual machine using a private endpoint.
D.An Azure Logic App connecting to the database.
E.An Azure App Service connecting to the database.
AnswersA, D, E

If the self-hosted IR is on Azure, it uses Azure IPs.

Why this answer

The 'Allow Azure services and resources to access this server' firewall setting permits connections from any Azure service that does not have a fixed public IP or a private endpoint. Azure Data Factory using a self-hosted integration runtime (IR) runs on-premises or on a VM, but the IR itself initiates outbound connections to Azure SQL Database from a dynamic Azure IP range. Without this setting enabled, the connection is blocked because the self-hosted IR's source IP is not a static, whitelisted address.

Exam trap

The trap here is that candidates often think the 'Allow Azure services' setting is required for any Azure resource, but it is only needed when the resource does not have a static, whitelistable IP or a private endpoint; for example, a VM with a private endpoint does not need this setting because it uses a private IP within the virtual network.

Full explanation →
544
MCQhard

Refer to the exhibit. You are reviewing an Azure Resource Manager template for deploying an Azure SQL Database server. The template sets publicNetworkAccess to Disabled, minimalTlsVersion to 1.2, and azureAdOnlyAuthentication to true. However, the deployment fails with an error. What is the most likely cause?

A.minimalTlsVersion 1.2 is not supported in Azure SQL Database
B.publicNetworkAccess Disabled requires a private endpoint to be defined in the same template
C.When azureAdOnlyAuthentication is true, the administratorLogin and administratorLoginPassword properties must not be specified
D.The password does not meet complexity requirements
AnswerC

Azure AD-only authentication disables SQL authentication, so providing SQL admin credentials is invalid.

Why this answer

When azureAdOnlyAuthentication is set to true in an Azure SQL Database ARM template, the administratorLogin and administratorLoginPassword properties must be omitted because Azure AD authentication replaces SQL authentication as the sole identity provider. Including these properties causes a validation conflict, as the deployment expects no SQL admin credentials when Azure AD-only authentication is enabled.

Exam trap

The trap here is that candidates assume the deployment fails due to a missing private endpoint or password issue, but the real conflict is the simultaneous presence of SQL admin credentials and Azure AD-only authentication, which the ARM template validation explicitly rejects.

How to eliminate wrong answers

Option A is wrong because minimalTlsVersion 1.2 is fully supported in Azure SQL Database and is actually the recommended minimum TLS version. Option B is wrong because publicNetworkAccess Disabled does not require a private endpoint to be defined in the same template; it only blocks public connectivity, and a private endpoint can be added separately or after deployment. Option D is wrong because the error is not related to password complexity; the deployment fails before password validation due to the conflicting properties when azureAdOnlyAuthentication is true.

Full explanation →
545
MCQmedium

You manage an Azure SQL Managed Instance that hosts a critical OLTP database. You notice that the average CPU usage is consistently above 90% during business hours. You have enabled Intelligent Insights, which recommends creating a missing index. What should you do first to validate the recommendation before implementing it?

A.Use Query Store to review query performance and missing index details.
B.Scale up the managed instance to a higher tier.
C.Enable automatic index tuning.
D.Create the recommended index immediately.
AnswerA

Using Query Store allows you to review query performance and missing index details to validate the recommendation before implementation.

Why this answer

Use Query Store to review query performance and missing index details. Intelligent Insights provides recommendations, but you should validate them using Query Store, which shows actual query performance and missing index details. This helps confirm the index will reduce CPU usage without negative side effects.

B is wrong because scaling up increases resources but doesn't address the root cause; it may be unnecessary. C is wrong because automatic index tuning would implement changes without validation, which could be risky. D is wrong because creating the index immediately without validation might cause performance issues or be unnecessary.

Full explanation →
546
Multi-Selectmedium

Which TWO actions should you take to achieve high availability for an Azure SQL Managed Instance with a 99.99% SLA? (Choose two.)

Select 2 answers
A.Enable automated backups with long-term retention.
B.Use Business Critical service tier.
C.Deploy the instance as zone-redundant.
D.Configure geo-replication to a secondary region.
E.Use General Purpose service tier.
AnswersB, C

Business Critical provides the highest SLA within a region.

Why this answer

To achieve the 99.99% SLA for Azure SQL Managed Instance, you must use the Business Critical service tier (which provides local redundancy) and deploy the instance as zone-redundant (which protects against zone failures). Option A (automated backups with long-term retention) is for data durability and recovery, not availability. Option D (geo-replication) is for disaster recovery, not high availability within a region.

Option E (General Purpose) offers a lower SLA (99.99% only with zone redundancy, but still not the highest available; typically 99.99% is achieved with Business Critical and zone redundancy). Therefore, B and C are the correct choices.

Full explanation →
547
MCQmedium

You are reviewing the firewall and virtual network rules for an Azure SQL Database server as shown in the exhibit. The server has public network access disabled. A client application in a different virtual network (vnet2) needs to connect to the database. What must you do to allow the connection?

A.Add a virtual network rule that references subnet2 in vnet2.
B.Add the IP address of the client to the ipRules array.
C.Set publicNetworkAccess to 'Enabled' and add an IP firewall rule.
D.Create a server-level firewall rule to allow all Azure services.
AnswerA

Virtual network rules allow traffic from specific subnets.

Why this answer

Since public network access is disabled, the server rejects all public IP-based connections. To allow a client in a different virtual network (vnet2) to connect, you must create a virtual network rule that references subnet2 in vnet2. This rule establishes a private endpoint-like connection through the Azure backbone, bypassing the public internet and honoring the disabled public network access setting.

Exam trap

The trap here is that candidates assume IP firewall rules can still work when public network access is disabled, but in reality, disabling public network access completely overrides all IP-based rules, making virtual network rules the only viable option for cross-VNet connectivity.

How to eliminate wrong answers

Option B is wrong because adding the client's IP address to the ipRules array requires public network access to be enabled; with publicNetworkAccess set to 'Disabled', all IP firewall rules are ignored. Option C is wrong because setting publicNetworkAccess to 'Enabled' and adding an IP firewall rule would expose the server to the public internet, which is unnecessary and less secure when a virtual network rule can provide private connectivity. Option D is wrong because creating a server-level firewall rule to allow all Azure services (0.0.0.0/0) also requires public network access to be enabled and does not restrict traffic to a specific virtual network; it would allow any Azure service, not just the client in vnet2.

Full explanation →
548
MCQeasy

You need to configure Azure SQL Database to automatically adjust indexing based on workload patterns. Which feature should you enable?

A.Azure Advisor
B.Intelligent Insights
C.Automatic tuning
D.Query Store
AnswerC

Automatic tuning can automatically create and drop indexes.

Why this answer

Automatic tuning in Azure SQL Database continuously analyzes query execution plans and workload patterns, then automatically creates, drops, or rebuilds indexes to improve performance. It uses built-in intelligence to recommend and apply index changes without manual intervention, making it the correct feature for automatically adjusting indexing based on workload patterns.

Exam trap

A common mistake is to confuse features that provide recommendations (Azure Advisor, Intelligent Insights) or capture query performance data (Query Store) with Automatic tuning, which is the only feature that automatically applies index changes based on workload patterns without manual intervention.

How to eliminate wrong answers

Option A is wrong because Azure Advisor provides proactive recommendations for cost, security, reliability, and performance, but it does not automatically adjust indexing; it only suggests manual actions. Option B is wrong because Intelligent Insights uses built-in intelligence to monitor database performance and detect anomalies, but it does not automatically implement index changes; it delivers root cause analysis and recommendations. Option D is wrong because Query Store captures query execution statistics and plan history for troubleshooting and tuning, but it does not automatically adjust indexing; it requires manual analysis or integration with Automatic tuning to apply changes.

Full explanation →
549
Matchingmedium

Match each Azure SQL Database error code to its meaning.

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

Concepts
Matches

Service is busy; retry the request later

Database is not currently available

Cannot open server '%.*ls' requested by the login

Login failed for user

Why these pairings

Common Azure SQL Database error codes: 4060 (database not accessible), 18456 (login failure), 40197 (transient error), 40501 (throttling). Distractors swap the definitions between 4060 and 18456.

Full explanation →
550
MCQeasy

Your organization requires that all Azure SQL Database deployments use the most recent version of SQL Server engine to ensure security and performance. Which deployment option automatically provides the latest engine version without requiring manual upgrades?

A.SQL Server on Azure Virtual Machines.
B.Azure SQL Database elastic pool.
C.Azure SQL Database (single database).
D.Azure SQL Managed Instance.
AnswerC

Azure SQL Database is always updated to the latest engine version.

Why this answer

Azure SQL Database (single database) is a Platform-as-a-Service (PaaS) offering where Microsoft manages the underlying SQL Server engine. Microsoft automatically applies the latest engine version updates during the service's maintenance windows, ensuring you always run a supported and current version without any manual upgrade effort. This aligns directly with the requirement for automatic, hands-off version management.

Exam trap

The trap here is that candidates confuse 'automatic updates' with 'latest engine version' and assume that all PaaS options (like Azure SQL Managed Instance) provide the exact same latest engine version immediately, but in reality, Azure SQL Managed Instance may run a slightly older stable version unless you explicitly opt into newer preview versions, whereas single databases are always on the most current generally available engine.

How to eliminate wrong answers

Option A is wrong because SQL Server on Azure Virtual Machines is an Infrastructure-as-a-Service (IaaS) offering where you are responsible for manually applying SQL Server updates and version upgrades, including major version changes. Option B is wrong because Azure SQL Database elastic pool is built on the same PaaS engine as single databases, but it is a deployment model for pooling resources across multiple databases, not a separate engine version; it also receives automatic updates, but the question asks for a deployment option that 'automatically provides the latest engine version,' and elastic pools share the same automatic update mechanism as single databases, so the distinction is that the question's correct answer is the single database as the most direct and common example, though elastic pools also qualify; however, the exam expects single database as the canonical answer. Option D is wrong because Azure SQL Managed Instance is also a PaaS offering with automatic updates, but it runs a near-100% compatible SQL Server engine that may lag slightly behind the latest engine version available in single databases, and it requires a manual decision to opt into preview or newer engine versions via configuration, so it does not 'automatically provide the latest engine version' without any action.

Full explanation →
551
MCQhard

You are troubleshooting an Always On Availability Group named AG1. The exhibit shows the current state. The primary replica shows PENDING_FAILOVER. The secondary replica is DISCONNECTED with error 35202. What is the most likely cause of this issue?

A.Network connectivity between the replicas is blocked on port 5022
B.The availability group listener is not configured correctly
C.The primary instance encountered a critical error and failed over automatically
D.The cluster has lost quorum
AnswerA

Error 35202 often relates to connectivity issues on the mirroring endpoint port.

Why this answer

The PENDING_FAILOVER state on the primary replica indicates that a failover was initiated but could not complete because the secondary replica is DISCONNECTED. Error 35202 specifically indicates that a connection attempt to the secondary replica failed, which in an Always On Availability Group (AG) is typically due to a network connectivity issue on the dedicated endpoint port (default 5022). Since the secondary is unreachable, the failover cannot finalize, leaving the primary in a pending state.

Exam trap

The trap here is that candidates often confuse the availability group listener port (default 1433) with the replica-to-replica synchronization endpoint port (default 5022), leading them to incorrectly attribute the issue to listener misconfiguration rather than a blocked endpoint port.

How to eliminate wrong answers

Option B is wrong because the availability group listener is used for client connections and routing, not for replica-to-replica synchronization; a misconfigured listener would not cause the secondary to be DISCONNECTED with error 35202. Option C is wrong because if the primary encountered a critical error and failed over automatically, the primary would no longer be in PENDING_FAILOVER—it would have transitioned to a resolving or offline state, and the secondary would have become the new primary. Option D is wrong because cluster quorum loss would affect the entire cluster's ability to manage resources, typically resulting in both replicas being in an unresolved state or the AG going offline entirely, not a specific PENDING_FAILOVER on the primary with a DISCONNECTED secondary.

Full explanation →
552
MCQeasy

You need to automatically notify the operations team when an Azure SQL Database reaches 80% storage usage. Which Azure service should you use to create the alert?

A.Azure Automation
B.Microsoft Sentinel
C.Azure Logic Apps
D.Azure Monitor
AnswerD

Azure Monitor can create metric alerts for storage usage.

Why this answer

Azure Monitor alerts can be configured to trigger based on metrics like storage percent, enabling automatic notification when an Azure SQL Database reaches 80% storage usage. Option A is incorrect because Azure Automation is used for runbooks and process automation, not for alerting. Option B is incorrect because Microsoft Sentinel is a SIEM solution for security analytics, not for simple metric alerts.

Option C is incorrect because Azure Logic Apps can be triggered by alerts, but the alert itself must be created in Azure Monitor.

Full explanation →
553
MCQmedium

Your team uses Azure DevOps to deploy database changes to Azure SQL Database. You need to automate the generation and deployment of database schema changes based on a Git branch merge. Which Azure service should you integrate?

A.Azure Automation Runbook triggered by a webhook from Azure DevOps.
B.Azure Pipelines with a release pipeline that uses the Azure SQL Database deployment task.
C.GitHub Actions with a workflow that runs on pull request merge, using the Azure SQL action.
D.Azure Logic Apps with an HTTP trigger from Azure DevOps, then use SQL connector to run scripts.
AnswerB

Azure Pipelines natively supports database deployments.

Why this answer

Azure Pipelines (part of Azure DevOps) can be configured to trigger on branch merges and includes a built-in Azure SQL Database deployment task for automating schema changes. Option A is wrong because Azure Automation Runbooks are not designed for CI/CD workflows. Option C is wrong because GitHub Actions would require moving outside the existing Azure DevOps ecosystem.

Option D is wrong because Azure Logic Apps are meant for integration workflows, not CI/CD pipeline automation.

Full explanation →
554
MCQeasy

You need to ensure that all queries executed against an Azure SQL Database are audited and logged to a Log Analytics workspace for security analysis. Which feature should you enable?

A.SQL Vulnerability Assessment
B.Advanced Threat Protection
C.Microsoft Defender for Cloud
D.Azure SQL Auditing
AnswerD

Auditing captures database events and can be configured to send logs to Log Analytics workspace.

Why this answer

Azure SQL Auditing is the feature specifically designed to track database events and write them to an audit log destination, including a Log Analytics workspace. This enables security analysis by capturing all queries executed against the database, which can then be queried and analyzed within the workspace. The other options focus on vulnerability scanning, threat detection, or security posture management, not on recording query execution logs.

Exam trap

The trap here is that candidates often confuse 'threat detection' (Advanced Threat Protection) or 'security posture management' (Microsoft Defender for Cloud) with the specific need for a full query audit log, but only Azure SQL Auditing provides the granular, configurable logging of all executed queries to a Log Analytics workspace.

How to eliminate wrong answers

Option A is wrong because SQL Vulnerability Assessment is a service that scans for potential database vulnerabilities and misconfigurations, not a feature that logs query execution. Option B is wrong because Advanced Threat Protection detects anomalous activities and potential threats, but it does not provide a full audit trail of all queries. Option C is wrong because Microsoft Defender for Cloud is a unified security management platform that provides security recommendations and threat protection across cloud workloads, but it does not itself enable per-query auditing to a Log Analytics workspace.

Full explanation →
555
MCQhard

You are reviewing an Azure SQL Database server's vulnerability assessment settings. The exhibit shows the current configuration. A recent security audit requires that vulnerability assessment scans be enabled and that results be retained for at least 90 days. What should you do?

A.Add additional email addresses to ensure notification.
B.Change retentionDays to 90 and keep the state as Disabled.
C.Change state to Enabled and set retentionDays to 90.
D.Remove the disabledAlerts entries to enable all alerts.
AnswerC

Enables the scan and meets the retention requirement.

Why this answer

The vulnerability assessment must be enabled (state: Enabled) and retentionDays must be set to at least 90 to meet the audit requirement. The exhibit shows state as Disabled and retentionDays as 30. Option A is wrong because adding email addresses only affects notifications, not the scan state or retention.

Option B is wrong because retentionDays of 90 with state Disabled means scans are not running. Option D is wrong because disabledAlerts control which alerts are suppressed; removing them does not enable the scan or change retention.

Full explanation →
556
MCQhard

You are evaluating the configuration of an Azure SQL Database as shown in the exhibit. You need to ensure that the database remains available during a zonal failure without data loss. Which feature contributes to this requirement?

A.General Purpose service tier.
B.Read scale-out.
C.Geo-redundant backup storage.
D.Zone redundancy enabled on Business Critical tier.
AnswerD

Zone redundancy provides synchronous replication across zones.

Why this answer

Zone redundancy on the Business Critical tier ensures that database replicas are placed in different availability zones within the same Azure region. During a zonal failure, the service automatically fails over to a synchronous replica in another zone, guaranteeing zero data loss because all transactions are synchronously committed across replicas. This directly meets the requirement of remaining available without data loss during a zonal outage.

Exam trap

The trap here is that candidates often confuse zone redundancy (which protects within a region) with geo-redundancy (which protects across regions), or assume that any service tier with high availability features (like General Purpose) can guarantee zero data loss during a zonal failure, when only the Business Critical tier with zone redundancy provides synchronous replication and automatic failover without data loss.

How to eliminate wrong answers

Option A is wrong because the General Purpose service tier uses remote storage and asynchronous replication, which cannot guarantee zero data loss during a zonal failure; it relies on page blob replication that may lose recent writes. Option B is wrong because Read scale-out provides a read-only replica for offloading read workloads, but it does not protect against zonal failures or ensure availability with zero data loss for write operations. Option C is wrong because Geo-redundant backup storage (RA-GRS) protects against regional disasters by storing backups in a paired region, but it does not provide real-time failover or zero data loss during a zonal failure within the primary region.

Full explanation →
557
Multi-Selecthard

Which THREE of the following are required to automate schema deployments to Azure SQL Database using Azure DevOps? (Select exactly three.)

Select 3 answers
A.A release pipeline with a 'Azure SQL Database deployment' task.
B.A SQL database project (.sqlproj) containing the schema.
C.A service connection to Azure with appropriate permissions.
D.A schema compare tool to generate deployment scripts.
E.A self-hosted build agent with SQL tools installed.
AnswersA, B, C

The deployment task executes the schema change.

Why this answer

Options A, B, and C are correct. A release pipeline with the 'Azure SQL Database deployment' task automates the deployment. A SQL database project (.sqlproj) defines the schema to deploy.

A service connection to Azure with appropriate permissions is needed for authentication and authorization. Option D is incorrect because while a schema compare tool can be used to generate scripts, it is not a strict requirement; the deployment task can handle the deployment directly from the project. Option E is incorrect because a self-hosted build agent is not required; Microsoft-hosted agents can be used.

Full explanation →
558
MCQeasy

You are responsible for cost optimization of a non-production Azure SQL Database that is used for development testing. The database is only active during business hours (9 AM to 5 PM) on weekdays. Which compute tier and configuration would minimize cost while ensuring the database is available during working hours?

A.Use the serverless compute tier with auto-pause enabled and a 1-hour auto-pause delay.
B.Use the Hyperscale tier with a minimum of 1 vCore and disable auto-pause.
C.Use the provisioned General Purpose tier with 2 vCores and disable auto-pause.
D.Use the provisioned Business Critical tier with 1 vCore and enable auto-pause.
AnswerA

Correct: Serverless tier with auto-pause pauses the database during inactivity, saving costs outside business hours.

Why this answer

The serverless compute tier with auto-pause enabled and a 1-hour auto-pause delay is the most cost-effective choice for a non-production database used only during business hours. Serverless automatically pauses the database after 1 hour of inactivity (e.g., overnight and weekends), charging only for compute during active periods and storage at all times. This aligns perfectly with the 9 AM–5 PM weekday usage pattern, minimizing cost while ensuring the database is available when needed.

Exam trap

The trap here is that candidates may assume any tier with auto-pause enabled is sufficient, but they overlook that the underlying tier (e.g., Business Critical or Hyperscale) has a much higher base cost and is not designed for cost-optimized dev/test scenarios, making serverless the only truly cost-effective choice for intermittent usage.

How to eliminate wrong answers

Option B is wrong because the Hyperscale tier is designed for large, high-throughput production workloads with rapid scaling and read scale-out, not for cost optimization of a small dev/test database; it incurs higher base costs even at 1 vCore and disabling auto-pause means compute runs 24/7. Option C is wrong because the provisioned General Purpose tier with 2 vCores and auto-pause disabled runs compute continuously, incurring charges for idle hours overnight and weekends, which is wasteful for a non-production database. Option D is wrong because the Business Critical tier is a premium tier with high availability and local SSD storage, intended for mission-critical workloads; even with 1 vCore and auto-pause enabled, it is significantly more expensive than serverless and over-provisioned for development testing.

Full explanation →
559
MCQhard

You are the database administrator for a global e-commerce company that uses Azure SQL Database for its product catalog. The database is currently deployed in the East US region using the General Purpose service tier. The company is expanding to Europe and wants to improve disaster recovery posture. The new requirements are: RPO of 5 seconds, RTO of 1 hour, and the ability to serve read traffic from the secondary region during normal operations. The budget is limited, so you must minimize cost while meeting these requirements. The application connection strings can be updated to a listener endpoint. What should you recommend?

A.Keep the primary as General Purpose, create a readable secondary in the same region, and configure active geo-replication to a secondary in West Europe.
B.Upgrade the primary to Business Critical, configure a failover group with a readable secondary in West Europe using Business Critical, and use the listener endpoint for application connections.
C.Deploy a zone-redundant Business Critical database in East US and use auto-failover groups to a secondary in West Europe with General Purpose.
D.Upgrade the primary database to Business Critical, create a readable secondary in the same region, and use geo-restore for DR.
AnswerB

Business Critical meets RPO, readable secondary serves read traffic, failover group provides automated failover with listener endpoint.

Why this answer

It meets all requirements: upgrading the primary to Business Critical provides synchronous replication with an RPO of 2-5 seconds, and configuring a failover group with a readable secondary in West Europe using Business Critical ensures the secondary can serve read traffic from Europe and failover can occur within 1 hour. The listener endpoint simplifies application connection management. Option A is incorrect because General Purpose tier has an RPO of 5-30 minutes, which cannot meet the 5-second requirement, and a same-region secondary does not serve European read traffic.

Option C is incorrect because zone-redundancy only protects within a region, not across regions, and a General Purpose secondary would have higher RPO. Option D is incorrect because a readable secondary in the same region does not serve read traffic from Europe, and geo-restore has an RTO of hours, not 1 hour.

Full explanation →
560
MCQeasy

You are designing a disaster recovery plan for an Azure SQL Database that stores critical financial data. The RPO must be 5 seconds, and the RTO must be 30 seconds. Which solution should you recommend?

A.Zone-redundant configuration within the primary region.
B.Failover groups with automatic failover policy.
C.Active geo-replication with manual failover.
D.Automated backups with geo-restore.
AnswerB

Correct: Provides low RPO and RTO with automatic failover.

Why this answer

Failover groups with automatic failover policy can achieve an RPO of 5 seconds and an RTO of 30 seconds for Azure SQL Database. Active geo-replication also achieves an RPO of 5 seconds but requires manual failover, increasing RTO beyond 30 seconds. Zone-redundant configuration only protects within a region and does not provide disaster recovery across regions.

Automated backups with geo-restore have much higher RPO and RTO (typically hours).

Full explanation →
561
Multi-Selecthard

Which THREE of the following are features available in Azure SQL Managed Instance that are not available in Azure SQL Database (single database)?

Select 3 answers
A.Transparent Data Encryption (TDE)
B.Cross-database queries (three- and four-part names)
C.Elastic pools
D.Service Broker
E.SQL Agent jobs
AnswersB, D, E

Managed Instance supports cross-database queries, single DB does not.

Why this answer

Cross-database queries using three- and four-part names (e.g., `[DB1].[schema].[table]`) are supported in Azure SQL Managed Instance because it provides full SQL Server engine compatibility, including the ability to query across databases within the same instance. Azure SQL Database single databases are isolated and do not support cross-database queries, as each database is a separate logical boundary without instance-level context.

Exam trap

The trap here is that candidates often confuse features available in both services (like TDE) with those exclusive to Managed Instance, or they incorrectly assume Elastic pools are a Managed Instance feature when they are actually a single-database scaling option.

Full explanation →
562
MCQhard

Refer to the exhibit. You are deploying an Azure SQL Database with Transparent Data Encryption (TDE) enabled via ARM template. The database will contain highly sensitive data, and your security policy requires that the encryption key be managed by your organization using Azure Key Vault. What additional configuration is needed?

A.Set the 'keyVaultUri', 'keyName', and 'keyVersion' properties to reference the key in Key Vault
B.No additional configuration is needed; the template already enables TDE with customer-managed keys
C.Deploy a separate key rotation policy in the ARM template
D.Enable 'autoRotationEnabled' property
AnswerA

Required to use customer-managed keys for TDE.

Why this answer

When deploying Azure SQL Database with TDE and customer-managed keys (CMK) via ARM template, you must explicitly specify the 'keyVaultUri', 'keyName', and 'keyVersion' properties under the 'encryptionProtector' resource to link the database to the specific key in Azure Key Vault. Without these properties, the template would only enable TDE with a service-managed key, not the required customer-managed key.

Exam trap

The trap here is that candidates assume enabling TDE in the ARM template automatically uses customer-managed keys, but they overlook the need to explicitly configure the encryption protector with Key Vault properties to switch from service-managed to customer-managed keys.

How to eliminate wrong answers

Option B is wrong because the ARM template shown only enables TDE at the database level (which defaults to service-managed keys), but does not configure the encryption protector to use a customer-managed key from Key Vault; additional properties are required. Option C is wrong because a key rotation policy is not a separate ARM template resource; key rotation is managed via Key Vault's own rotation policy or by updating the key version in the encryption protector, not by a dedicated ARM property. Option D is wrong because 'autoRotationEnabled' is not a valid property for TDE with CMK in Azure SQL Database; automatic key rotation is handled by updating the key version in the encryption protector or by using Key Vault's key rotation, not by an ARM template boolean.

Full explanation →
563
MCQeasy

You need to provision an Azure SQL Database that supports a high-availability SLA of 99.995% and provides automatic failover to a secondary region in case of a regional outage. Which deployment option should you choose?

A.Azure SQL Database in Hyperscale tier
B.Azure SQL Database with zone-redundant configuration
C.Azure SQL Database with active geo-replication and failover group
D.Azure SQL Managed Instance with failover group
AnswerC

This combination provides up to 99.995% SLA and cross-region failover.

Why this answer

Active geo-replication with a failover group provides an SLA of 99.995% by replicating data asynchronously to a secondary region and enabling automatic failover during a regional outage. This is the only option that meets both the high-availability SLA and cross-region disaster recovery requirement.

Exam trap

The trap here is that candidates confuse zone-redundant configuration (which only protects within a region) with cross-region disaster recovery, or assume that any high-SLA tier automatically includes cross-region failover.

How to eliminate wrong answers

Option A is wrong because Hyperscale tier supports rapid scaling and read scale-out but does not provide a 99.995% SLA or automatic cross-region failover; its high-availability is zone-redundant within a single region. Option B is wrong because zone-redundant configuration protects against datacenter failures within a region, not against a full regional outage, and its SLA is 99.995% only for the Business Critical tier, but it lacks cross-region failover. Option D is wrong because Azure SQL Managed Instance with a failover group provides disaster recovery but its SLA is 99.99%, not 99.995%, and it is not a deployment option for Azure SQL Database.

Full explanation →
564
MCQmedium

Your company has a compliance requirement to keep database backups for 10 years. You are using Azure SQL Database. Which backup retention feature should you use?

A.Long-term retention (LTR) policy
B.Geo-redundant storage (GRS)
C.Automated backups
D.Point-in-time restore (PITR)
AnswerA

LTR can retain backups for up to 10 years.

Why this answer

Long-term retention (LTR) policy is the correct feature for keeping database backups for 10 years, as it supports retention of full backups for up to 10 years. Point-in-time restore (PITR) is limited to a maximum of 35 days and cannot meet the 10-year requirement. Geo-redundant storage (GRS) provides geographic redundancy for backups but does not extend the retention period.

Automated backups are retained for a maximum of 35 days by default and cannot be configured for 10-year retention.

Full explanation →
565
MCQmedium

You are a database administrator for a large retail company. The company uses an Azure SQL Database in the Business Critical tier (8 vCores, 480 GB storage) to run its core transaction processing system. The database has automatic tuning enabled, including FORCE_LAST_GOOD_PLAN and CREATE_INDEX. You notice that the database is experiencing high CPU usage (90% average) during peak hours, and the Query Store shows that a specific query (Query ID 123) has regressed. The automatic tuning feature has forced a plan for this query, but the performance is still poor. You need to resolve the CPU issue and ensure the query runs efficiently. What should you do first?

A.Disable automatic tuning and manually create a plan guide for the query.
B.Use Query Store to compare the forced plan with the previous good plan and update statistics.
C.Modify the query to use query hints like OPTIMIZE FOR UNKNOWN.
D.Scale up the database to 16 vCores to handle the CPU load.
AnswerB

Plan regression often due to statistics; updating may let optimizer pick a better plan.

Why this answer

Reviewing the plan history in Query Store helps identify why the forced plan is not optimal, and perhaps the regression is due to parameter sniffing or outdated statistics. Option A is wrong because disabling automatic tuning may cause further regression and is not the first step. Option C is wrong because adding query hints like OPTIMIZE FOR UNKNOWN may not resolve the plan regression and could lead to suboptimal plans for other parameter values.

Option D is wrong because scaling up the database increases resources temporarily but does not address the underlying plan regression issue.

Full explanation →
566
MCQmedium

You have an Azure SQL Database in the General Purpose service tier. The database must be available during a planned patching event that updates the underlying infrastructure. What high availability feature is provided by default?

A.Active geo-replication
B.Zone-redundant availability
C.Built-in high availability with a standby replica
D.Business Critical service tier
AnswerC

The General Purpose service tier provides built-in high availability through automatic compute failover to a different node. While often described as a standby replica, the mechanism is actually compute failover with shared storage, ensuring availability during planned patching events.

Why this answer

The General Purpose service tier provides built-in high availability through automatic failover to a remote database copy on a different compute node, ensuring availability during planned patching events. Option A is incorrect because active geo-replication is a disaster recovery feature that requires manual configuration. Option B is incorrect because zone-redundant availability is an optional configuration for the General Purpose tier, not provided by default.

Option D is incorrect because Business Critical is a separate service tier and not a high availability feature of General Purpose.

Full explanation →
567
MCQmedium

You are a database administrator for a medium-sized e-commerce company. The company runs its online transaction processing (OLTP) workload on an Azure SQL Database in the General Purpose service tier (DTU-based, S3). The database is used for order processing, inventory management, and customer data. Recently, during peak shopping hours (10 AM to 2 PM), users have reported that order entry forms take several seconds to submit, and inventory queries are timing out. Monitoring shows that DTU usage regularly hits 100% during these hours, with high PAGELATCH_IO waits. You need to resolve the performance issue with minimal cost increase. What should you do?

A.Increase the max storage size to 1 TB
B.Increase the service tier to S4 during peak hours
C.Create a read-only replica and offload reporting queries
D.Migrate to the vCore model with Hyperscale service tier
AnswerD

Hyperscale eliminates resource contention and handles high concurrency.

Why this answer

Migrate to the vCore model with Hyperscale service tier. The issue is high DTU usage with PAGELATCH_IO waits, indicating resource contention on I/O. Hyperscale architecture separates compute and storage, eliminating such bottlenecks and providing near-instant scaling for peak loads.

Option A (increase storage) does not address DTU limits. Option B (increase to S4) adds more DTUs but retains the same architecture limitations that cause PAGELATCH_IO. Option C (read replica) only offloads read queries, not write-heavy OLTP.

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

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 →
569
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 used for autoscaling Azure SQL Database.

Why this answer

Options A and D are correct because CPU percentage and DTU percentage are standard metrics for autoscaling Azure SQL Database. Option B is wrong because Log write throughput is not typically used for autoscale rules. Option C is wrong because Deadlock count is an event, not a continuous metric.

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

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

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

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

Microsoft Defender for SQL provides built-in SQL injection detection, which can supersede the need for separate Security Alert Policy rules. When Defender for SQL is enabled, it automatically monitors and alerts on SQL injection attempts, so duplicating with a custom alert policy would be redundant and could cause confusion. Option A is incorrect because disabling alerts does not save costs; it weakens security.

Option B is incorrect because vulnerability assessment policies do not override alert rules. Option C is incorrect because there is no known incompatibility between SQL injection alerts and storage account endpoints.

Full explanation →
573
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 →
574
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 →
575
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

The output shows zoneRedundant is true and replicationRole is Primary. Zone redundancy means the database has replicas in different availability zones within the same region, and replicationRole being Primary indicates this database is the primary in its replication relationship. Therefore, option B is correct.

Option A is incorrect because active geo-replication involves a secondary in a different region, not just zone redundancy. Option C is incorrect because Hyperscale databases have a different architecture; zoneRedundant may be supported but the output does not specify Hyperscale. Option D is incorrect because a readable secondary replica is not indicated; zone redundancy does not necessarily imply a readable secondary.

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

Setting the 'DisallowSqlAuthentication' property to 'True' on the logical server enforces that only Microsoft Entra authentication can be used to connect to the Azure SQL Database. This property prevents the creation of any SQL authenticated logins, including the SQL admin account, and ensures compliance with the policy that no SQL authentication accounts exist.

Exam trap

The trap here is that candidates often confuse network-level controls (like disabling public network access) with authentication-level controls, or assume that auditing or post-creation removal of the SQL admin is sufficient to enforce a no-SQL-authentication policy.

How to eliminate wrong answers

Option A is wrong because disabling public network access only restricts network connectivity to the database, not authentication methods; it does not prevent the creation of SQL authenticated logins. Option C is wrong because removing the SQL admin login after creating the database does not prevent the initial creation of SQL authenticated logins, and the SQL admin account can be re-enabled or recreated unless the property is set. Option D is wrong because creating an Azure Policy to audit SQL authentication usage only monitors and reports on authentication activity, but does not actively prevent the creation of SQL authenticated logins.

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

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 →
578
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 →
579
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

When an automatic tuning recommendation to force the last good plan is active, the correct next step is to monitor the query performance to confirm that the forced plan resolves the regression. This is because plan forcing is a corrective action that may or may not improve performance; validation through monitoring ensures the change is beneficial before taking further steps like creating or dropping indexes.

Exam trap

Azure often tests the misconception that an automatic tuning recommendation should be immediately implemented or reverted without first monitoring its impact, leading candidates to choose premature actions like dropping indexes or reverting plans.

How to eliminate wrong answers

Option A is wrong because dropping an index based on a recommendation that is unrelated to the plan force could degrade performance if the index is still needed for other queries. Option B is wrong because creating a recommended index is not the immediate action when a plan force is active; the forced plan should be validated first to ensure it resolves the regression. Option C is wrong because reverting the plan force without monitoring its effect is premature; the forced plan may be the correct fix, and reverting could reintroduce the regression.

Full explanation →
580
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 B and D are correct. Azure SQL Managed Instance supports automated backups through the service's built-in point-in-time restore (PITR) and long-term retention (LTR) policies (option D). Additionally, you can schedule manual copy-only backups to Azure Blob Storage using T-SQL BACKUP DATABASE TO URL statements via SQL Agent jobs (option B).

Option A is incorrect because Azure Backup does not natively support Azure SQL Managed Instance. Option C is incorrect because Azure Site Recovery is a disaster recovery solution, not a backup service. Option E is incorrect because Azure VM backup is designed for IaaS virtual machines, not for PaaS managed instances.

Full explanation →
581
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 →
582
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

With automatic failover configured and a grace period of 1 hour, Azure SQL Database will wait for the grace period to expire before initiating failover. If the primary region remains unreachable after 1 hour, automatic failover occurs. Option A is incorrect because failover does not happen immediately; it waits for the grace period.

Option B is incorrect because manual failover is possible but not required; automatic failover will proceed after the grace period. Option D is incorrect because the secondary database is not deleted or re-created; it is already provisioned and will become the primary upon failover.

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

Force the previous query plan using Query Store. This approach directly addresses the root cause by locking the query to a known good plan, ensuring consistent performance without application changes. Option A is incorrect because increasing the DTU/service tier may temporarily improve performance but does not fix the plan regression.

Option B is incorrect because creating a missing index recommendation may help but does not guarantee the query will use the previous plan. Option C is incorrect because dropping and recreating the index is disruptive and may not force the plan to revert.

Full explanation →
584
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 →
585
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 →
586
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
AnswerD

Adding all queried columns as included columns to the nonclustered index makes it covering, eliminating key lookups and reducing IO with minimal storage overhead.

Why this answer

Adding all queried columns as included columns to the existing nonclustered index on customer ID creates a covering index. This eliminates the need for key lookups, reducing IO and improving query performance for point lookups by customer ID. The storage overhead is minimal since included columns are stored only at the leaf level.

Option A is wrong because a clustered columnstore index is designed for analytical workloads and can degrade point lookup performance. Option B is wrong because a filtered index on frequent values still may not cover all columns, leading to key lookups. Option C is wrong because partitioning does not eliminate key lookups and can add complexity without performance benefit for point queries.

Exam trap

The trap is that clustered columnstore indexes are often suggested for large tables to reduce storage and improve IO, but they are optimized for analytic workloads, not high-frequency point lookups. For point lookup queries, a covering nonclustered index is a better choice.

Full explanation →
587
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 →
588
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

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

sys.dm_exec_query_stats (Option A) returns aggregate performance statistics for cached query plans, including total CPU time (total_worker_time), which can be filtered by creation_time or last_execution_time to focus on the last hour. Query Store's top resource consuming queries report (Option E) provides a built-in, graphical view of queries ranked by CPU, duration, or other metrics over a configurable time window, making it ideal for identifying high-CPU queries in the last hour.

Exam trap

The trap here is that candidates often confuse sys.dm_exec_requests (current state) with sys.dm_exec_query_stats (historical aggregates), or assume wait stats directly identify CPU-heavy queries, when in fact they indicate what queries are waiting on, not what is consuming CPU.

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

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

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

After a manual failover, the former secondary becomes the new primary. The old primary, if still available, becomes a secondary. To restore DR capability with a new secondary in Central US, you must first remove the existing geo-replication link between the current primary and the old primary (the orphaned secondary).

Without removing this link, you cannot add another secondary. Once the link is removed, you can create a new geo-secondary in Central US from the current primary. Therefore, option D is correct.

Option A is incorrect because deleting the old primary is unnecessary and could be destructive. Option B is incorrect because a new secondary cannot be added while a geo-replication link already exists to the old primary. Option C is incorrect because performing a planned failover back to the old primary would cause unnecessary downtime and does not facilitate adding a secondary in Central US.

Full explanation →
593
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 metric thresholds using a metric trigger, and Azure Automation runbooks can execute the scaling commands (such as changing the service tier) via PowerShell or Azure CLI. Together, they provide a cost-effective, built-in solution. Option A (Elastic Database Jobs) is used for scheduled database tasks like index maintenance, not scaling.

Option B (Azure Monitor autoscale) is not directly available for Azure SQL Database; autoscale is only for Azure Virtual Machine scale sets, App Service, etc. Option C (Azure Functions) could also be used, but the recommended combination for this scenario is Logic Apps and Automation runbooks because they are serverless and integrate natively with Azure SQL Database.

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

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

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 →
596
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 →
597
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 →
598
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

Elastic Database Jobs can be used to run ALTER DATABASE statements on a schedule, enabling automated scaling without custom code. Option A is wrong because it requires custom code (PowerShell) and is not a native Azure SQL capability. Option B is wrong because autoscale for elastic pools adjusts per-database DTUs, not the overall database service objective, and does not support scheduled scaling.

Option C is wrong because autoscale settings are not available for Azure SQL Database at the server level.

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

Point-in-time restore (PITR) using geo-redundant backup storage provides disaster recovery without requiring a secondary database, thus avoiding additional compute costs. Geo-redundant backups are automatically stored in a paired region, so in the event of a regional outage, you can restore the database to any point within the backup retention period (typically up to 35 days). For a 50 GB database, the RTO of 2 hours is achievable, and the RPO of 1 hour is satisfied because backups are taken every 5-10 minutes.

Option A (active geo-replication) would incur compute costs for a readable secondary database. Option C (zone redundancy) provides high availability within a region, not cross-region DR. Option D (failover group with a secondary) also requires a secondary database costing compute resources.

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

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

Page 8 of 13

Page 9

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 →