Courseiva

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

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

Take a mock examExam hub
Page 11

Page 12 of 13

Page 13
826
MCQhard

The query returns a list of query hashes with high average duration. You need to identify which queries are most likely causing CPU pressure. What additional metric should you include?

A.Include wait_stats to see blocking.
B.Include count_executions to see frequency.
C.Include avg_logical_reads to see I/O consumption.
D.Include avg_cpu_time to measure CPU usage.
AnswerD

CPU time directly measures CPU consumption.

Why this answer

Avg_cpu_time directly measures CPU usage per query, helping correlate high duration with CPU pressure. Option A is incorrect because wait_stats show wait types (e.g., blocking), not CPU consumption. Option B is incorrect because execution count alone does not indicate CPU usage; a query could run frequently but use little CPU.

Option C is incorrect because avg_logical_reads measure I/O, not CPU pressure.

Full explanation →
827
MCQeasy

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert that resource usage has exceeded 90% for the past hour. What is the recommended first step to troubleshoot?

A.Scale up the database to a higher service tier immediately.
B.Check sys.dm_os_wait_stats for high wait types.
C.Enable the Query Store and wait for data collection.
D.Review the Intelligent Insights root cause analysis report.
AnswerD

Provides automated analysis of performance issues.

Why this answer

Intelligent Insights provides a root cause analysis report that can help identify the issue. Option A is wrong because scaling up should only be done after identifying the root cause. Option B is wrong because checking wait statistics is lower-level; Intelligent Insights already provides a diagnosis.

Option C is wrong because Query Store may not be enabled by default and is not the first step.

Full explanation →
828
MCQeasy

You are a database administrator for a retail company that uses Azure SQL Database. The security team wants to prevent SQL injection attacks by ensuring that all application queries use parameterized statements. Which built-in Azure feature should you enable to help detect and alert on potential SQL injection attempts?

A.Enable auditing on the database
B.Enable data discovery and classification
C.Enable Microsoft Defender for SQL
D.Enable SQL vulnerability assessment
AnswerC

Microsoft Defender for SQL includes threat detection for SQL injection and anomalous activity.

Why this answer

Microsoft Defender for SQL includes advanced threat detection capabilities that continuously monitor database activity for anomalous patterns, including SQL injection attempts. When enabled, it analyzes query execution patterns and can alert on suspicious queries that deviate from parameterized statement usage, directly addressing the security team's requirement to detect and alert on potential SQL injection attacks.

Exam trap

The trap here is that candidates confuse passive auditing or assessment features (which log or scan for vulnerabilities) with active threat detection that monitors and alerts on real-time attack patterns like SQL injection.

How to eliminate wrong answers

Option A is wrong because auditing records database events for compliance and forensic analysis but does not actively detect or alert on SQL injection patterns in real time. Option B is wrong because data discovery and classification identifies sensitive columns and recommends classification labels, but it has no mechanism to analyze query patterns or detect injection attempts. Option D is wrong because SQL vulnerability assessment scans for misconfigurations and missing patches, not for active injection attempts or anomalous query behavior.

Full explanation →
829
MCQhard

Your company uses GitHub Actions to deploy changes to Azure SQL Database. You need to ensure that rollbacks can be performed automatically if a deployment fails. Which strategy should you implement?

A.Use Azure App Service deployment slots for the database
B.Use GitHub Actions deployment environments with approval gates
C.Create a database copy or snapshot before each deployment
D.Use Azure SQL Managed Instance point-in-time restore
AnswerC

A snapshot or copy can be used to restore the previous state if deployment fails.

Why this answer

The correct strategy is to create a database copy or snapshot before each deployment. This allows you to revert the database to its pre-deployment state if the deployment fails, enabling automated rollback. Option A (Azure App Service deployment slots) is designed for app deployment, not databases.

Option B (approval gates) controls when deployments proceed but does not automate rollback. Option D (point-in-time restore) can restore to a point in time but is not a deployment-specific rollback method and may not cover the exact previous state.

Full explanation →
830
MCQeasy

You have an Azure SQL Database in the Business Critical tier with a failover group to a secondary region. The primary region experiences a full outage. What is the expected recovery time objective (RTO) and recovery point objective (RPO) if you initiate a manual failover?

A.RTO of 5 minutes, RPO of 1 second.
B.RTO of 30 seconds, RPO of 5 seconds.
C.RTO of 1 hour, RPO of 5 minutes.
D.RTO of 30 seconds, RPO of 0.
AnswerD

Azure SQL Database Business Critical tier with failover groups uses synchronous replication, achieving an RPO of 0 (no data loss) and an RTO of approximately 30 seconds for manual failover.

Why this answer

In Azure SQL Database Business Critical tier with a failover group, synchronous replication ensures zero data loss during a failover, resulting in an RPO of 0. The RTO for a manual failover is typically around 30 seconds. Option A is incorrect because the RTO is 5 minutes, which is too high for Business Critical.

Option B is incorrect because the RPO is 5 seconds, not zero. Option C is incorrect because both RTO (1 hour) and RPO (5 minutes) are much higher than what Business Critical offers.

Full explanation →
831
MCQeasy

Your organization uses Azure SQL Database Hyperscale tier. You need to ensure that if the primary region fails, the database can be failed over to a secondary region with minimal data loss. What should you configure?

A.Use geo-restore from geo-redundant backups.
B.Enable zone redundancy on the Hyperscale database.
C.Set up active geo-replication to a secondary region.
D.Configure an auto-failover group with a secondary in the same region.
AnswerC

Correct. Active geo-replication continuously replicates to a secondary region, providing low RPO and enabling failover with minimal data loss.

Why this answer

Active geo-replication to a secondary region provides low RPO (typically 5-10 seconds) by continuously replicating changes asynchronously. This minimizes data loss during a regional failure. Option A (geo-restore) relies on geo-redundant backups and has an RPO of hours, not minimal data loss.

Option B (zone redundancy) protects within a region, not across regions. Option D (auto-failover group within same region) does not handle region failure.

Exam trap

Candidates may mistakenly think geo-restore (backups) offers low RPO, but it has a much higher RPO than active geo-replication.

Full explanation →
832
MCQhard

You are deploying an Azure SQL Managed Instance for a sensitive healthcare application. The compliance requirement mandates that all data at rest be encrypted using a customer-managed key stored in Azure Key Vault. The key must be automatically rotated every 90 days. Which configuration should you implement?

A.Use dynamic data masking with a masking function that encrypts sensitive data.
B.Implement row-level security to restrict access to sensitive data.
C.Enable Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault and set a key rotation policy.
D.Enable Always Encrypted with column master key stored in Azure Key Vault and configure automatic key rotation.
AnswerC

TDE with customer-managed keys supports automatic rotation via Azure Key Vault.

Why this answer

Transparent Data Encryption (TDE) with a customer-managed key (CMK) stored in Azure Key Vault is the correct choice because it encrypts the entire database at rest using a key that the customer controls and rotates. Azure SQL Managed Instance supports TDE with CMK, and you can configure automatic key rotation by setting a key rotation policy in Azure Key Vault (e.g., every 90 days). This satisfies the compliance requirement for customer-managed encryption and automated rotation.

Exam trap

The trap here is that candidates confuse Always Encrypted (column-level encryption with client-side key management) with TDE (database-level encryption at rest), and assume automatic key rotation applies to Always Encrypted, whereas Azure Key Vault's automatic rotation only applies to TDE protectors, not Always Encrypted column master keys.

How to eliminate wrong answers

Option A is wrong because dynamic data masking does not encrypt data; it only obfuscates it in query results for unauthorized users, leaving the underlying data in plaintext at rest. Option B is wrong because row-level security controls access to rows based on user predicates but does not encrypt data at rest. Option D is wrong because Always Encrypted encrypts data at the column level in client applications, not the entire database at rest, and its key rotation is manual or application-driven, not automatically enforced by Azure Key Vault policies for TDE.

Full explanation →
833
MCQeasy

You need to automate the deployment of Azure SQL Database logical servers and databases using Bicep. What is the best practice for storing the administrative password securely?

A.Reference the password from Azure Key Vault using the getSecret function
B.Use the adminPassword property with a generated password
C.Use an environment variable in the deployment script
D.Store the password as a plain text parameter in the Bicep file
AnswerA

Key Vault integration securely retrieves secrets during deployment.

Why this answer

Azure Key Vault is the recommended secure storage for secrets like administrative passwords in Azure deployments. Using the `getSecret` function in Bicep allows you to reference a secret from Key Vault at deployment time without exposing the password in the Bicep file or deployment logs, aligning with Azure security best practices and the principle of least privilege.

Exam trap

The trap here is that candidates may think environment variables or generated passwords are acceptable for automation, but the DP-300 exam specifically tests the secure secret management pattern using Azure Key Vault with Bicep's `getSecret` function, not just any method of hiding the password.

How to eliminate wrong answers

Option B is wrong because using the `adminPassword` property with a generated password, while functional, does not securely store the password; it is typically passed as a parameter and can be exposed in deployment logs or outputs. Option C is wrong because environment variables in the deployment script are not encrypted and can be captured in process dumps or logs, failing to meet security compliance requirements. Option D is wrong because storing the password as a plain text parameter in the Bicep file directly exposes the secret in source control and deployment history, violating fundamental security practices.

Full explanation →
834
MCQhard

You are reviewing an ARM template for creating a new Azure SQL Database. The template uses the above JSON to create a database named 'db2' from 'db1'. The source database 'db1' is currently in a failed state due to a storage issue. What will be the result of deploying this template?

A.The deployment will fail because db1 is not in a recoverable state.
B.It will create an empty database because the source is not accessible.
C.It will delete db1 and create db2 as a replacement.
D.It will create a new database by recovering db1 to its last known good state.
AnswerA

Recovery mode requires the source to be dropped or after geo-failover.

Why this answer

The ARM template creates a new database by copying from a source database. Azure SQL Database requires the source database to be in an online and healthy state to perform a copy operation. Since db1 is in a failed state due to a storage issue, it is not accessible for copying, so the deployment will fail.

Exam trap

The trap here is that candidates may confuse a database copy with a point-in-time restore, assuming that a failed source can still be used to create a new database via recovery, but the copy operation explicitly requires an online source.

How to eliminate wrong answers

Option B is wrong because Azure SQL Database does not create an empty database when the source is inaccessible; the copy operation requires a valid, online source. Option C is wrong because the ARM template does not include a delete operation; it only creates a new database from a source, and Azure SQL Database does not automatically delete the source during a copy. Option D is wrong because the template specifies a copy operation, not a point-in-time restore; recovering to a last known good state would require a different ARM template or a restore command, not a database copy.

Full explanation →
835
MCQmedium

Refer to the exhibit. You run the above PowerShell command to set the Transparent Data Encryption (TDE) protector for an Azure SQL Database server. What is the result?

A.The command fails because the service principal does not have permissions to the key vault.
B.Transparent Data Encryption is disabled.
C.The TDE protector for the database "mydb" is updated.
D.The server’s TDE protector is changed to a customer-managed key from Azure Key Vault.
AnswerD

The command sets the protector to a key vault key.

Why this answer

The command `Set-AzSqlServerTransparentDataEncryptionProtector` configures the TDE protector at the server level. By specifying the `-Type AzureKeyVault` and `-KeyId` parameters, it sets the server's TDE protector to a customer-managed key stored in Azure Key Vault. Therefore, Option D is correct.

Option A is incorrect because the command does not fail due to permissions; it succeeds if permission is granted. Option B is incorrect because TDE remains enabled; only the protector key is changed. Option C is incorrect because the command updates the server's TDE protector, not a specific database.

Full explanation →
836
MCQmedium

You are troubleshooting a performance degradation on an Azure SQL Database. You notice that the database is hitting the maximum DTU limit frequently. Which action should you take first to reduce DTU consumption?

A.Increase the log rate limit
B.Scale up the database to a higher service tier
C.Use Query Performance Insight to identify and optimize top resource-consuming queries
D.Rebuild all indexes in the database
AnswerC

Query Performance Insight helps identify and optimize top resource-consuming queries, directly reducing DTU usage.

Why this answer

Query Performance Insight helps identify the top resource-consuming queries. Optimizing those queries is the most direct way to reduce DTU consumption. Option A is wrong because increasing the log rate limit does not reduce DTU consumption.

Option B is wrong because scaling up increases cost and may not address the root cause. Option D is wrong because rebuilding all indexes can increase DTU temporarily.

Full explanation →
837
Multi-Selecthard

You are designing an automated backup strategy for Azure SQL Database. Which TWO actions satisfy a requirement to maintain daily backups for 30 days and ensure recoverability if the Azure region becomes unavailable?

Select 2 answers
A.Enable geo-redundant backup storage for the server.
B.Deploy a read-scale replica in a different Azure region.
C.Set point-in-time restore retention to 30 days.
D.Set up a long-term retention policy with a retention of 30 days.
E.Configure active geo-replication to a secondary region.
AnswersA, C

Geo-redundant backup storage (RA-GRS) replicates backups to a paired region, ensuring regional recoverability.

Why this answer

Enabling geo-redundant backup storage (RA-GRS) for Azure SQL Database ensures that automated backups are replicated to a paired Azure region. This satisfies the requirement for recoverability if the primary region becomes unavailable, as backups can be restored from the secondary region. Combined with setting point-in-time restore retention to 30 days (Option C), you maintain daily backups for the full 30-day retention period, meeting both requirements.

Exam trap

The trap here is confusing high-availability features (like geo-replication or read-scale replicas) with backup retention and storage redundancy, leading candidates to select options that provide failover capability but do not satisfy the backup retention or geo-redundant backup storage requirements.

Full explanation →
838
MCQeasy

You are optimizing an Azure SQL Database that runs a heavy reporting workload. The database uses the General Purpose tier. You notice that many queries are scanning large tables. What is the best first action to improve performance?

A.Partition the large tables by date.
B.Analyze the missing index recommendations from Query Store.
C.Scale up to Business Critical tier.
D.Implement columnstore indexes on all large tables.
AnswerB

Identifies specific indexes to reduce scans.

Why this answer

The best first action is to analyze the missing index recommendations from Query Store. Query Store tracks query performance and provides actionable index recommendations based on actual workload patterns. Partitioning (A) can help manage large tables but does not directly address scan issues caused by missing indexes.

Scaling up to Business Critical (C) is costly and premature without first analyzing the workload. Columnstore indexes (D) are beneficial for analytical queries but should be implemented selectively based on query patterns, not as a blanket solution. Therefore, analyzing missing index recommendations is the optimal starting point for optimization.

Full explanation →
839
MCQeasy

You are designing a secure environment for Azure SQL Database. Which authentication method provides the strongest security and supports multi-factor authentication?

A.Certificate-based authentication
B.Azure Active Directory authentication
C.SQL authentication with strong passwords
D.Windows authentication
AnswerB

Azure AD authentication supports MFA and conditional access.

Why this answer

Azure Active Directory (Azure AD) authentication is the recommended method for Azure SQL Database because it supports multi-factor authentication (MFA), conditional access policies, and identity-driven security. It eliminates the need for password management and leverages Azure AD's built-in security features, providing the strongest security posture for cloud-native environments.

Exam trap

The trap here is that candidates often assume Windows authentication (Option D) is available in Azure SQL Database because of their on-premises experience, but Azure SQL Database does not support Windows authentication—only Azure AD authentication provides integrated identity management and MFA.

How to eliminate wrong answers

Option A is wrong because certificate-based authentication is not a native authentication method for Azure SQL Database; it can be used only as part of Azure AD authentication or for specific scenarios like service principals, not as a standalone method. Option C is wrong because SQL authentication with strong passwords still relies on a static credential stored in the database, making it vulnerable to brute-force attacks and lacking MFA support. Option D is wrong because Windows authentication is not supported for Azure SQL Database; it is only available for on-premises SQL Server or Azure SQL Managed Instance when integrated with Active Directory.

Full explanation →
840
MCQhard

A company uses Azure SQL Database with elastic pools. They need to automatically scale up the pool DTU when CPU usage exceeds 80% for 5 minutes and scale down when below 20% for 10 minutes. Which solution should they implement?

A.Use Elastic Database Transactions to handle scaling
B.Configure autoscale settings on the elastic pool in the Azure portal
C.Use Azure Automation runbook with PowerShell to check metrics and scale
D.Create a SQL Agent job to monitor and alter the pool
AnswerC

Azure Automation runbooks can use Get-AzMetric and Set-AzSqlElasticPool to implement custom autoscaling.

Why this answer

Azure SQL Database autoscale is not natively supported; scaling must be done programmatically. Azure Automation with PowerShell runbooks can query metrics and trigger scaling. SQL Agent jobs are not available in Azure SQL Database.

Elastic Database Transactions are for distributed transactions, not scaling. Azure Logic Apps can also be used but are more complex for this scenario.

Full explanation →
841
MCQhard

Your company is migrating on-premises SQL Server databases to Azure SQL Managed Instance. You need to ensure that database backups are encrypted at rest using customer-managed keys stored in Azure Key Vault. You also need to allow the backup service to access the keys. What should you configure?

A.Use Always Encrypted with column master key stored in Azure Key Vault.
B.Configure Azure Backup for SQL Server in Azure VM and use Backup Center to manage encryption.
C.Enable Transparent Data Encryption (TDE) with customer-managed keys and grant the managed instance's system-assigned managed identity 'get', 'wrapKey', and 'unwrapKey' permissions on the key vault.
D.Configure server-level firewall rules to allow Azure services to access the server.
AnswerC

This allows the managed instance to use the customer-managed key for TDE, which encrypts backups.

Why this answer

Transparent Data Encryption (TDE) with customer-managed keys (CMK) in Azure SQL Managed Instance encrypts database backups at rest. To allow the Azure backup service to access the key for backup encryption, the managed instance's system-assigned managed identity must be granted 'get', 'wrapKey', and 'unwrapKey' permissions on the Azure Key Vault where the CMK is stored. This ensures that backups are encrypted using the customer-controlled key, meeting the requirement for encryption at rest with customer-managed keys.

Exam trap

The trap here is that candidates confuse Always Encrypted (which protects column data) with TDE (which protects the entire database and backups), leading them to select Option A instead of the correct TDE-based solution.

How to eliminate wrong answers

Option A is wrong because Always Encrypted protects column data in transit and at rest on the client side, not database backups; it does not encrypt backups or involve the backup service. Option B is wrong because Azure Backup for SQL Server in Azure VM is for SQL Server on Azure VMs, not Azure SQL Managed Instance, and Backup Center is a management interface, not a mechanism to encrypt backups with customer-managed keys. Option D is wrong because server-level firewall rules control network access, not encryption of backups; they do not address key management or backup encryption requirements.

Full explanation →
842
MCQeasy

You are deploying Azure SQL Database for a new application that requires predictable performance and minimal latency. You need to choose a service tier that provides dedicated resources and allows scaling independently for compute and storage. Which service tier should you select?

A.Business Critical
B.General Purpose
C.Basic
D.Hyperscale
AnswerA

Business Critical provides dedicated resources and low latency, suitable for mission-critical applications.

Why this answer

The Business Critical service tier is designed for applications requiring predictable performance and minimal latency, as it provides dedicated resources (CPU and memory) and uses local SSD storage for the database. It also supports independent scaling of compute and storage, allowing you to adjust DTUs or vCores without affecting storage capacity, which aligns with the requirement for independent scaling.

Exam trap

The trap here is that candidates often confuse 'dedicated resources' with 'General Purpose' because it also offers isolated compute, but they overlook that Business Critical uses local SSD for minimal latency, whereas General Purpose relies on remote storage with higher latency.

How to eliminate wrong answers

Option B (General Purpose) is wrong because it uses remote blob storage (Azure Premium Blob) with a page server architecture, which introduces higher latency compared to local SSD, and while it offers independent scaling of compute and storage, it does not provide the dedicated resource isolation and low-latency guarantees needed for minimal latency. Option C (Basic) is wrong because it is a low-cost tier with limited performance, no dedicated resources, and does not support independent scaling of compute and storage; it is intended for small databases with low concurrency. Option D (Hyperscale) is wrong because, although it offers independent scaling of compute and storage and uses a distributed architecture with local buffer pool extension, it is optimized for very large databases and high throughput, not necessarily for minimal latency in a predictable performance scenario, and it introduces additional complexity with its multiple compute nodes and page servers.

Full explanation →
843
MCQhard

You are a database administrator for a technology company that uses Azure SQL Database to support a multi-tenant SaaS application. Each tenant has its own database. The security team requires that users from one tenant should never be able to access data from another tenant, even if a user's credentials are compromised. You need to implement a solution that enforces tenant isolation at the database level. The solution must be transparent to the application and must not require application code changes. What should you do?

A.Use Always Encrypted to encrypt each tenant's data with a separate column encryption key
B.Create a contained database user for each tenant in their respective database and ensure that cross-database queries are not allowed by configuring server-level firewall rules to block access between databases
C.Implement elastic queries to query across all tenant databases and use a view to filter by tenant ID
D.Create a linked server between all tenant databases and use a security policy to restrict access
AnswerB

Correct. Contained database users per tenant in their own database, combined with firewall rules that block cross-database queries, ensure that each user can only access their own tenant's database.

Why this answer

Each tenant has its own database, and by creating contained database users specific to each tenant and preventing cross-database access through firewall rules or private endpoints, you enforce tenant isolation. Even if credentials are compromised, the attacker can only access the single tenant's database. Option A is incorrect because Always Encrypted protects data at rest and in transit but does not restrict which data a user can query; it does not prevent cross-tenant access.

Option C is incorrect because elastic queries are designed to query across databases, which would allow cross-tenant access if not carefully restricted. Option D is incorrect because a linked server enables cross-database queries, increasing the risk of cross-tenant access.

Full explanation →
844
MCQhard

You are designing a high availability solution for an Azure SQL Database that supports an online transaction processing (OLTP) workload. The database must be available with an SLA of 99.995% and must withstand a regional outage. Which configuration should you choose?

A.Business Critical tier with zone-redundancy only.
B.General Purpose tier with active geo-replication to a secondary region.
C.Hyperscale tier with geo-replication.
D.Business Critical tier with zone-redundancy and a failover group with geo-replication.
AnswerD

Business Critical with zone-redundancy provides 99.995% SLA and geo-replication protects against regional failure.

Why this answer

The Business Critical tier with zone redundancy provides a 99.995% SLA within a region. However, to withstand a regional outage, you need geo-replication. A failover group with active geo-replication ensures that if the primary region fails, you can fail over to a secondary region.

Option A (Business Critical zone-redundancy only) protects against zone failures within a region but not a full regional outage. Option B (General Purpose with active geo-replication) has a lower SLA (99.99%) and does not meet the 99.995% requirement. Option C (Hyperscale with geo-replication) also has a lower SLA (99.95%) than required.

Full explanation →
845
MCQmedium

You are deploying a new application on Azure SQL Database. The application requires that all connections use a specific login, 'AppUser', with the least privileges necessary. The login should only be able to execute stored procedures in the 'Sales' schema and should not have direct access to underlying tables. What should you do?

A.Grant the SELECT permission on the 'Sales' schema to 'AppUser'.
B.Add 'AppUser' to the db_datareader role.
C.Create a database role, grant EXECUTE on the 'Sales' schema to the role, and add 'AppUser' to the role.
D.Grant the EXECUTE permission on each stored procedure individually to 'AppUser'.
AnswerC

Schema-level EXECUTE permission grants execute on all stored procedures in the schema, meeting the requirement.

Why this answer

Creating a database role with EXECUTE permission on the 'Sales' schema grants execute on all stored procedures in that schema, adhering to least privilege. Option A is wrong because granting SELECT on the schema would give read access to underlying tables, which is not allowed. Option B is wrong because adding AppUser to the db_datareader role provides read access to all tables in the database, exceeding the required permissions.

Option D is not wrong in terms of functionality but is more administrative work compared to using a schema-level grant; however, for the requirement of least privilege, granting EXECUTE on each stored procedure individually would still work but is less efficient than the schema-level approach.

Full explanation →
846
MCQmedium

You manage an Azure SQL Database that is experiencing higher than expected DTU consumption. You need to identify which queries are consuming the most resources. Which dynamic management view should you query?

A.Query sys.dm_exec_requests
B.Query sys.dm_os_wait_stats
C.Query sys.dm_exec_query_stats
D.Query sys.dm_db_resource_stats
AnswerC

Correct. sys.dm_exec_query_stats returns aggregate performance statistics for cached query plans, allowing identification of queries with high CPU, I/O, or duration.

Why this answer

sys.dm_exec_query_stats returns aggregate performance statistics for cached query plans, including CPU time, logical reads, and duration, making it ideal for identifying queries that consume the most resources in Azure SQL Database. Option A (sys.dm_exec_requests) shows only currently executing requests, not historical data. Option B (sys.dm_os_wait_stats) provides server-level wait statistics, not per-query resource usage.

Option D (sys.dm_db_resource_stats) shows database-level DTU consumption per minute, not per-query details.

Full explanation →
847
Multi-Selecthard

Your organization has an Azure SQL Database server. You need to ensure that only applications running on Azure virtual machines in a specific virtual network can connect to the database. Which THREE actions should you take?

Select 3 answers
A.Add a service endpoint for Azure SQL Database to the virtual network.
B.Add a virtual network rule for the specific subnet of the VMs.
C.Set the 'Allow Azure Services and resources to access this server' setting to OFF.
D.Remove all existing firewall rules that allow public IP ranges.
E.Set the 'Allow Azure Services and resources to access this server' setting to ON.
AnswersB, C, D

Correct: This allows traffic from that subnet.

Why this answer

Adding a virtual network rule for the specific subnet of the VMs allows Azure SQL Database to accept traffic only from that subnet when a service endpoint is enabled. This restricts access to the database to only those applications running on Azure VMs within the specified virtual network, ensuring network-level isolation.

Exam trap

The trap here is that candidates often confuse enabling a service endpoint (Option A) with actually restricting access, forgetting that a virtual network rule is required to explicitly allow traffic from the specific subnet.

Full explanation →
848
MCQhard

Refer to the exhibit. An administrator tries to restore the database OrdersDB to a point in time 2025-03-14 10:00 UTC and receives error 406. What is the most likely reason?

A.The database is currently in a geo-replication secondary role.
B.The long-term retention policy interferes with point-in-time restore.
C.The requested restore point is outside the 7-day retention period.
D.The automated backup frequency is 12 hours, so point-in-time restore is not supported.
AnswerA

Correct. Error 406 occurs when the database is in a geo-replication secondary role, which prevents point-in-time restore.

Why this answer

Error 406 in Azure SQL Database indicates that the database is not in a state that allows recovery to the specified point in time. This commonly occurs when the database is a secondary replica in a geo-replication relationship. Geo-secondary databases are read-only and cannot be restored to a point in time directly; instead, the primary database must be used for restore operations.

Options B, C, and D are incorrect: long-term retention does not interfere with PITR, the requested time is within the 7-day retention window, and Azure SQL Database automatically takes transaction log backups every 5-10 minutes, enabling PITR regardless of full backup frequency.

Exam trap

Candidates often associate error 406 with retention or backup frequency issues, but in Azure SQL Database, error 406 specifically relates to the database state—most often when it is a geo-replication secondary.

Full explanation →
849
MCQmedium

Your organization runs a critical e-commerce application on Azure SQL Database. You notice that during peak hours, query performance degrades significantly. You have enabled Intelligent Insights and Query Store. Which diagnostic metric would you analyze first to identify the root cause of the performance degradation?

A.Query store wait statistics
B.Deadlock count
C.DTU/CPU consumption
D.Failed connections
AnswerC

DTU/CPU consumption is the primary metric to analyze because peak load directly increases resource usage, leading to performance degradation.

Why this answer

DTU/CPU consumption directly correlates with query performance during peak load. With Intelligent Insights and Query Store enabled, analyzing DTU/CPU consumption helps identify resource contention as the primary cause of degradation. Option A (Query store wait statistics) is useful for detailed analysis but is not the first metric to check.

Option B (Deadlock count) is not a typical cause of sustained degradation. Option D (Failed connections) indicates availability issues, not performance.

Full explanation →
850
Multi-Selectmedium

You are a database administrator for a bank that uses Azure SQL Database to store transaction data. The compliance team requires that you implement a solution to encrypt data at rest and in transit. Additionally, the solution must allow the database to be restored to any point in time within the last 35 days. Which TWO actions should you take?

Select 2 answers
A.Enable Microsoft Defender for SQL
B.Implement dynamic data masking on sensitive columns
C.Enable Always Encrypted for all columns containing sensitive data
D.Enable transparent data encryption (TDE) on the database
E.Configure the point-in-time restore (PITR) retention period to 35 days
AnswersD, E

TDE encrypts data at rest, including backups and transaction log files.

Why this answer

Transparent Data Encryption (TDE) encrypts the database at rest, including backups and transaction log files, which satisfies the compliance requirement for data at rest. TDE is a built-in feature of Azure SQL Database that requires no application changes and is enabled at the database level.

Exam trap

The trap here is that candidates often confuse dynamic data masking or Always Encrypted with encryption at rest, failing to recognize that TDE is the primary Azure SQL Database feature for encrypting the entire database at rest, while PITR retention is a separate backup configuration that must be explicitly set to meet the 35-day requirement.

Full explanation →
851
MCQhard

Your company is planning to deploy a new application that uses Azure SQL Database with a Hyperscale service tier. The application requires near-instantaneous scaling of compute resources without downtime. The database will handle large data volumes (over 10 TB). Additionally, the application team wants to use the latest database features, including JSON support and graph queries. Which considerations should you evaluate before choosing Hyperscale?

A.Hyperscale does not support graph data features such as node and edge tables.
B.Hyperscale does not support zone-redundant configuration for high availability.
C.Hyperscale does not support JSON functions like JSON_VALUE and JSON_QUERY.
D.Hyperscale does not support cross-database queries or elastic queries.
AnswerD

Correct: Hyperscale does not support cross-database queries or elastic query features.

Why this answer

Azure SQL Database Hyperscale does not support cross-database queries or elastic queries. This limitation is critical for the application team if they need to query across multiple databases in a single query, which is a common requirement for distributed applications. The Hyperscale architecture separates compute from storage, making cross-database query capabilities unavailable, unlike other service tiers such as General Purpose or Business Critical.

Exam trap

The trap here is that candidates often assume Hyperscale supports all features of other tiers due to its advanced capabilities, but Microsoft specifically excludes cross-database and elastic queries in Hyperscale, making it a common oversight in exam questions.

How to eliminate wrong answers

Option A is wrong because Hyperscale fully supports graph data features, including node and edge tables, as part of the SQL Server graph database capabilities available in Azure SQL Database. Option B is wrong because Hyperscale does support zone-redundant configuration for high availability, which provides resilience within an Azure region by replicating across availability zones. Option C is wrong because Hyperscale supports all JSON functions, including JSON_VALUE and JSON_QUERY, as these are standard T-SQL features available across all service tiers.

Full explanation →
852
MCQmedium

Refer to the exhibit. You are reviewing the firewall rule JSON for an Azure SQL Server. What does this rule allow?

A.No traffic; this rule blocks all incoming connections.
B.All traffic from any IP address.
C.Traffic from IP address 0.0.0.0.
D.Traffic from Azure services.
AnswerA

0.0.0.0 to 0.0.0.0 is an explicit deny rule.

Why this answer

This rule is a firewall rule with start IP address '0.0.0.0' and end IP address '0.0.0.0', which in Azure SQL Server firewall configuration explicitly blocks all incoming traffic. The IP range 0.0.0.0 to 0.0.0.0 is a special reserved range that denies any connection attempts, regardless of source IP. This is the default behavior when no other allow rules exist, effectively creating a deny-all rule.

Exam trap

The trap here is that candidates confuse the '0.0.0.0' IP address with 'allow all' (as in 0.0.0.0/0 in networking) or with the 'Allow Azure Services' feature, not realizing that in Azure SQL firewall, a rule with start and end both set to 0.0.0.0 explicitly blocks all traffic.

How to eliminate wrong answers

Option B is wrong because the rule specifies a single IP address (0.0.0.0) rather than the range 0.0.0.0 to 255.255.255.255, which would be required to allow all traffic. Option C is wrong because traffic from IP address 0.0.0.0 is not a valid source IP for actual network traffic; 0.0.0.0 is reserved for 'any' or 'default route' and cannot be a source address in practice. Option D is wrong because allowing traffic from Azure services requires a special rule with start IP '0.0.0.0' and end IP '0.0.0.0' but with the 'Allow Azure Services' flag enabled (or using the service tag), which is not indicated in the JSON; the rule shown is a standard IP-based rule that blocks all traffic.

Full explanation →
853
MCQhard

You are a database administrator for a multinational corporation that uses Azure SQL Managed Instance. The instance is part of a failover group for disaster recovery. You need to automate the process of testing the failover group by performing a planned failover to the secondary region and then failing back. The test must be performed monthly during a maintenance window. The automation must ensure that the failover group is in a healthy state before and after the test and must log the results to a table. What should you do?

A.Use Elastic Database Jobs to run T-SQL that initiates failover and logs results.
B.Create an Azure Automation runbook with PowerShell that uses the Az.Sql module to perform failover and log to a table.
C.Use Azure Data Factory to execute a stored procedure that performs failover.
D.Create a SQL Agent job with T-SQL that performs the planned failover using ALTER AVAILABILITY GROUP and logs the results to a table.
AnswerB

Correct. Azure Automation runbooks can be scheduled to run monthly, use the Az.Sql module to perform planned failover and failback, and log results to a table. Although not entirely self-contained, it is the only viable option.

Why this answer

Azure Automation runbooks can be scheduled to run monthly and use the Az.Sql module to perform a planned failover of the failover group and log results to a table. Although this relies on an external Azure service, it is the only viable option to automate failover for a failover group because T-SQL does not support failover group failover. Option A is incorrect because Elastic Database Jobs are not available for Managed Instance.

Option C is incorrect because Data Factory is not designed for failover orchestration. Option D is incorrect because the ALTER AVAILABILITY GROUP command cannot be used to failover a failover group in Azure SQL Managed Instance; failover groups require Azure-level operations.

Exam trap

Candidates may assume that T-SQL can be used to failover a failover group, but in Azure SQL Managed Instance, failover groups require Azure-level PowerShell or REST API.

Full explanation →
854
MCQmedium

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

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

Additional replicas increase read capacity.

Why this answer

Setting highAvailabilityReplicaCount to 2 in Business Critical tier creates two additional readable replicas (total of 3 replicas including primary). This improves read performance by allowing more read-only connections to be offloaded to replicas. Option A (automatic failover) is inherent to Business Critical, not dependent on replica count.

Option B (zone redundancy) is determined by the zoneRedundant property, not replica count. Option C (zero data loss) is achieved by synchronous replication, not replica count.

Full explanation →
855
MCQhard

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

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

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

Why this answer

Batching transactions reduces the number of log write operations, lowering the log write throughput below the service tier limit and avoiding throttling. Option A is incorrect because increasing the max log size does not affect the log write rate; it only provides more storage. Option C is incorrect because accelerated database recovery (ADR) improves recovery time and may reduce log I/O for crash recovery, but it does not directly reduce the sustained log write throughput from transaction processing.

Option D is incorrect because the requirement explicitly states not to move to Business Critical.

Exam trap

Candidates may think that increasing log size or enabling ADR will solve throttling, but only reducing log writes (via batching or minimally logged operations) addresses the throughput limit.

Full explanation →
856
MCQeasy

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

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

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

Why this answer

Azure SQL Managed Instance has built-in automated backups, but they run automatically and cannot be customized to run every 4 hours or target a specific storage account. Long-term retention (LTR) policies are for retaining backups beyond the automated backup period, not for scheduling. SQL Server Agent is available in Azure SQL Managed Instance and can be used to schedule jobs, but it does not provide a direct way to back up to an Azure storage account every 4 hours.

Azure Automation with PowerShell runbooks can invoke backups using REST APIs or T-SQL, and schedule them precisely, making it the best choice for this requirement.

Full explanation →
857
Multi-Selectmedium

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

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

DMA assesses SQL Server compatibility with Azure SQL Managed Instance.

Why this answer

Data Migration Assistant (DMA) is the correct tool because it performs a detailed assessment of on-premises SQL Server databases, identifying compatibility issues and migration blockers specifically for Azure SQL Managed Instance. It generates a report of feature parity, breaking changes, and behavior differences. Azure Migrate is also correct because it provides a unified migration assessment and discovery platform, including database assessment capabilities for Azure SQL targets.

Both tools can assess compatibility and identify blockers before migration.

Exam trap

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

Full explanation →
858
Multi-Selecteasy

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

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

Enforcing TLS 1.2 ensures encryption in transit.

Why this answer

Azure SQL Database allows you to enforce a minimum TLS version, such as TLS 1.2, which ensures that all client connections use at least that protocol version. This directly secures data in transit by preventing downgrade attacks and blocking older, less secure TLS versions like TLS 1.0 or 1.1.

Exam trap

The trap here is that candidates often confuse data-in-transit security with network access controls (like firewalls or NSGs) or client-side encryption (like Always Encrypted), failing to recognize that enforcing TLS 1.2 is the direct method to secure the communication channel itself.

Full explanation →
859
MCQmedium

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

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

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

Why this answer

Enabling forced parameterization causes the database to treat literal values in queries as parameters, which promotes plan reuse and reduces plan cache bloat. This is ideal for stored procedures like usp_getProducts that have multiple query variants with different plans. Option B is incorrect because plan guides target specific queries, not the entire stored procedure, and are complex to manage.

Option C is incorrect because Query Store hints are used to force a specific plan for a query, but they require manual intervention and don't address the root cause of plan variability. Option D is incorrect because automatic tuning with 'FORCE LAST GOOD PLAN' is reactive and may not provide consistent performance for a procedure with many variants.

Full explanation →
860
MCQeasy

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

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

Zone redundancy uses synchronous replicas across zones for automatic failover.

Why this answer

Zone redundancy for Azure SQL Database Business Critical tier maintains multiple synchronous replicas in different availability zones within the same region. If one zone fails, another replica automatically becomes the primary, ensuring minimal downtime. Option B is incorrect because read-only replicas are not promoted to primary; all replicas are read/write capable and failover is automatic.

Option C is incorrect as it describes geo-replication to a secondary region, not zone redundancy. Option D is incorrect because replicas are kept synchronous, not asynchronous.

Full explanation →
861
MCQmedium

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

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

Automatic tuning can optimize queries to reduce resource usage.

Why this answer

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

Full explanation →
862
Multi-Selectmedium

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

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

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

Why this answer

Options A, B, and C are correct. Option A is correct because active geo-replication uses asynchronous replication with a configurable RPO; a low RPO target minimizes potential data loss. Option B is correct because automatic seed type ensures continuous synchronization of the secondary database.

Option C is correct because locating the secondary in a paired region with low network latency reduces replication lag, thereby minimizing data loss. Option D is wrong because making the secondary readable does not affect data loss; it is designed for read-scale workloads. Option E is wrong because the failover group name does not influence data loss; a forced failover policy would actually cause data loss.

Full explanation →
863
Multi-Selecthard

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

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

Encrypts data at rest with customer-controlled keys.

Why this answer

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

Exam trap

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

Full explanation →
864
MCQmedium

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
865
MCQhard

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

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

Automatic failover switches endpoints.

Why this answer

Auto-failover groups automatically fail over to the secondary region with automatic read-write endpoint switch. Option B is incorrect because automatic failover does not require manual initiation; it occurs after the grace period. Option C is incorrect because the read-only listener is also updated to point to the new secondary (formerly primary) after failover.

Option D is incorrect because the failover group will fail over automatically, not remain in the primary region.

Full explanation →
866
MCQmedium

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

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

Key Vault enables seamless rotation without application changes.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
867
MCQhard

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

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

Columnstore indexes improve read performance for analytical queries by compressing data and using batch execution, reducing I/O and latency without changing compute size or tier.

Why this answer

Creating columnstore indexes can improve read performance by compressing data and enabling batch processing for analytical queries, which reduces I/O and latency without requiring a change in compute size or service tier. Read scale-out (option B) is not available on the General Purpose tier, so it cannot be implemented without upgrading to a higher tier, which might be considered a change beyond compute size. Hyperscale (option A) changes the service tier and may not directly address read latency.

In-memory OLTP (option D) is optimized for transactional workloads, not general read queries.

Exam trap

Candidates may assume read scale-out is available in General Purpose or confuse 'compute size' with 'service tier'. While the question only forbids changing compute size, read scale-out is not supported on General Purpose, making it an invalid choice. Columnstore indexes are a viable option that does not require tier changes.

How to eliminate wrong answers

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

Full explanation →
868
MCQhard

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
869
MCQeasy

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
870
Multi-Selecthard

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

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

DTU pools are cost-effective for multi-tenant.

Why this answer

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

Exam trap

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

Full explanation →
871
MCQeasy

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

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

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

Why this answer

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

Option C is wrong because failover groups use geo-replication but the RPO is still asynchronous; the question asks for the feature, and geo-replication is the core.

Full explanation →
872
MCQhard

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
873
Multi-Selectmedium

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

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

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

Why this answer

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

Exam trap

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

Full explanation →
874
MCQeasy

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

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

Provides historical metrics including storage.

Why this answer

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

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

Full explanation →
875
MCQmedium

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

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

Enclaves allow computations on encrypted data within the enclave.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
876
MCQmedium

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

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

Provides synchronous replication and automatic failover.

Why this answer

The Business Critical service tier uses Always On availability groups with synchronous replication, providing automatic and fast failover within the same region. Option B (Zone-redundant configuration) adds zone resilience but does not provide faster failover within a zone. Option C (Read scale-out) is for read-only workloads and does not provide failover.

Option D (Active geo-replication) is for cross-region failover and has a higher RTO.

Full explanation →
877
MCQhard

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

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

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

Why this answer

Adding tempdb data files equal to the number of vCores (16) is the recommended best practice to reduce allocation contention. With 16 vCores, 16 files minimize PAGELATCH_EX waits. Option A is incorrect because memory-optimized tempdb metadata helps only with system metadata contention, not data page allocation.

Option B is incorrect because adding only 4 files is insufficient; the recommended number is equal to the number of vCores. Option D is incorrect because accelerated database recovery does not address tempdb contention.

Full explanation →
878
Multi-Selectmedium

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

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

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

Why this answer

Options A and C are correct. For zero data loss (RPO=0), synchronous replication is required. Business Critical (option A) with zone redundancy provides synchronous replicas within the same region and automatic failover, meeting both RPO=0 and RTO<30 seconds.

Hyperscale (option C) also offers zone redundancy with synchronous log replication, enabling rapid failover without data loss. Option B (auto-failover group with same-region secondary) uses asynchronous replication, so data loss can occur. Option D (General Purpose) uses asynchronous replication as well and cannot guarantee zero data loss.

Option E (active geo-replication) is also asynchronous, so it does not meet the RPO requirement.

Full explanation →
879
MCQeasy

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

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

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

Why this answer

'Incremental' mode processes the template as an incremental update to the resource group, making it idempotent. Only resources specified in the template are added or modified; existing resources not in the template are left unchanged. 'Complete' mode deletes resources not in the template, which is not idempotent. 'Validate' mode only validates without deploying, and 'Automatic' is not a valid ARM deployment mode.

Full explanation →
880
Multi-Selecteasy

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

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

You can configure the secondary to be readable.

Why this answer

Failover groups in Azure SQL Database provide a single read/write listener endpoint for the primary database (option C) and allow the secondary database to be readable for reporting purposes (option A). Option B is incorrect because Transparent Data Encryption (TDE) is not a feature of failover groups; TDE can be configured independently. Option D is incorrect because failover groups do not automatically balance read queries; for read scaling, you would use a read-scale replica.

Option E is incorrect because failover groups use asynchronous replication, not synchronous.

Full explanation →
881
MCQeasy

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

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

Forces parameterization to reduce sniffing effects.

Why this answer

FORCE_PARAMETERIZATION = ON is a database-wide setting that forces parameterization of all ad-hoc queries, helping to mitigate parameter sniffing issues without requiring code changes. Option A (reorganizing indexes) addresses fragmentation, not parameter sniffing. Option B (Query Store plan forcing) is used to pin a specific plan after a regression, not to prevent sniffing proactively.

Option C (OPTIMIZE FOR UNKNOWN) is a query hint that requires modifying query code, not a code-less solution.

Full explanation →
882
MCQeasy

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

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

Enables Defender for SQL at the server level.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
883
MCQhard

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

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

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

Why this answer

Elastic Database Jobs (formerly Elastic Database Jobs) allows you to run T-SQL scripts across many databases with built-in retry logic, error handling, and scheduling. It is designed for such maintenance tasks over elastic pools. Option B (SQL Agent) is not available in Azure SQL Database because it is a PaaS service with no SQL Agent.

Option C (Azure Data Factory) is primarily for data movement and transformation, not for executing T-SQL maintenance scripts across databases. Option D (Azure Automation runbooks) can run PowerShell, but would require custom error handling and retry logic, and might be less efficient for large numbers of databases.

Full explanation →
884
MCQeasy

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

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

Correct. Firewall rules for on-premises IP ranges allow specific on-premises clients to connect.

Why this answer

To restrict access to specific on-premises IP addresses, you should configure firewall rules to allow those IP ranges. Setting a firewall rule ensures that only traffic from allowed IP addresses can reach the database. Option C directly addresses this requirement.

Exam trap

Candidates might consider enabling 'Allow Azure services' or using virtual network endpoints, but those are for Azure service access or private network integration, not for restricting on-premises IPs.

How to eliminate wrong answers

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

Full explanation →
885
Multi-Selectmedium

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

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

Failover groups provide automatic failover and readable secondary.

Why this answer

Options C and D are correct. Failover groups provide automatic failover to a secondary region, support readable secondary workloads, and allow manual failback by initiating a failover in the opposite direction. Option A (Active geo-replication) is not supported for Azure SQL Managed Instance.

Option B (Geo-restore) does not provide automatic failover. Option E (Log shipping) is not built-in for managed instances and does not meet the requirements.

Full explanation →
886
MCQeasy

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

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

Provides aggregated CPU statistics for cached query plans.

Why this answer

(sys.dm_exec_query_stats) is correct because this DMV returns aggregated performance statistics for cached query plans, including total CPU time. It can be filtered to look at queries over the last hour. Option A (sys.dm_exec_sessions) shows current session-level information, not query-level CPU.

Option B (sys.dm_exec_query_plan) returns the actual execution plan, not performance statistics. Option D (sys.dm_exec_requests) shows currently executing requests, not historical CPU consumption.

Full explanation →
887
MCQmedium

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

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

Masks sensitive data in query results for unauthorized users.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
888
MCQmedium

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
889
MCQmedium

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

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

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

Why this answer

PAGEIOLATCH_SH waits indicate I/O bottlenecks caused by excessive page reads from disk. Adding appropriate indexes reduces the number of pages read by enabling more efficient data access (e.g., index seeks instead of table scans), directly reducing I/O. Option A is incorrect because increasing max memory does not address the underlying query inefficiency driving I/O.

Option C is incorrect because page compression reduces storage but may increase CPU and does not primarily reduce I/O waits. Option D is incorrect because forcing parameterization improves plan reuse but does not target I/O reduction.

Full explanation →
890
MCQeasy

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
891
MCQmedium

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
892
Multi-Selectmedium

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

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

Auto-failover groups manage failover orchestration.

Why this answer

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

Exam trap

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

Full explanation →
893
MCQmedium

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
894
MCQeasy

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

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

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

Why this answer

The Business Critical tier with zone redundancy provides an SLA of 99.995% and automatic recovery from zone-level failures without data loss. The Basic tier does not support zone redundancy. The General Purpose tier with zone redundancy offers a 99.99% SLA.

The Hyperscale tier with zone redundancy offers a 99.95% SLA for zone-redundant configurations. Therefore, only the Business Critical tier meets the 99.995% uptime requirement with zone redundancy.

Full explanation →
895
MCQmedium

Refer to the exhibit. You are managing db1, which is currently in Standard tier. What is the maximum size you can set for db1 without changing the service objective?

A.107,374,182,400 bytes (100 GB)
B.1,073,741,824,000 bytes (1 TB)
C.268,435,456,000 bytes (250 GB)
D.536,870,912,000 bytes (500 GB)
AnswerC

Standard S2 supports up to 250 GB. The current max is 100 GB, so you can increase to 250 GB.

Why this answer

The first command shows that Standard S2 supports a max size of 268435456000 bytes (250 GB). The second command shows the current max size is 107374182400 bytes (100 GB). Since db1 is already on S2, you can increase the max size up to 250 GB within the same service objective.

Full explanation →
896
MCQeasy

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

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

Full explanation →
897
MCQhard

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

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

This provides additional read capacity and disaster recovery.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
898
MCQeasy

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

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

Only the two groups specified.

Why this answer

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

Option D is wrong because it includes all actions.

Full explanation →
899
MCQeasy

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
900
Multi-Selecthard

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

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

Columnstore indexes improve performance for large analytical queries.

Why this answer

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

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

Full explanation →
Page 11

Page 12 of 13

Page 13

All pages

12345678910111213

Practice DP-300 by domain

Target a specific domain to shore up weak areas.

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