Courseiva

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

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

Take a mock examExam hub
Page 10

Page 11 of 13

Page 12
751
MCQhard

You are responsible for automating backups of on-premises SQL Server databases to Azure Blob Storage. The solution must use the least administrative effort and provide point-in-time restore capability. What should you implement?

A.Configure SQL Server Managed Backup to Microsoft Azure.
B.Install Azure Backup Server on-premises and configure backup of SQL Server databases.
C.Use SQL Server Agent jobs to perform full, differential, and log backups to an Azure Blob Storage URL.
D.Use Azure Data Factory to copy database backups to Blob Storage.
AnswerA

Managed Backup automates backup scheduling and retention, and supports point-in-time restore.

Why this answer

SQL Server Managed Backup to Microsoft Azure (also known as Managed Backup) is the correct choice because it provides automated, policy-based backup management with minimal administrative effort. It natively supports point-in-time restore by automatically scheduling full, differential, and transaction log backups to Azure Blob Storage, and it handles backup retention and recovery point management without requiring custom scripts or additional infrastructure.

Exam trap

The trap here is that candidates often confuse Azure Backup Server (a general-purpose backup tool) with SQL Server Managed Backup, or they assume that manually scripting backups with SQL Server Agent jobs is the simplest approach, overlooking the built-in automation and point-in-time restore capabilities of Managed Backup.

How to eliminate wrong answers

Option B is wrong because Azure Backup Server requires installing and maintaining an on-premises server, which increases administrative effort and does not provide native point-in-time restore for SQL Server without additional configuration. Option C is wrong because using SQL Server Agent jobs to manually script full, differential, and log backups to Azure Blob Storage requires significant administrative effort to create, schedule, and maintain the jobs, and it does not offer the automated retention and recovery point management that Managed Backup provides. Option D is wrong because Azure Data Factory is an ETL and data orchestration service, not a backup solution; it cannot perform SQL Server transaction log backups or provide point-in-time restore capabilities.

Full explanation →
752
Multi-Selecthard

Which THREE factors should you consider when choosing between a Gen5 and a Premium-series hardware configuration for an Azure SQL Database? (Select three.)

Select 3 answers
A.Number of vCores available.
B.Memory-to-vCore ratio.
C.IOPS and throughput limits.
D.Compute generation and CPU architecture.
E.Support for Business Critical tier.
AnswersB, C, D

Premium-series often has higher memory per vCore.

Why this answer

The correct answers are B, C, and D. Gen5 and Premium-series hardware configurations for Azure SQL Database differ in memory-to-vCore ratio (B), IOPS and throughput limits (C), and compute generation/CPU architecture (D). Option A is incorrect because the number of vCores available is not a differentiating factor; both series offer a range of vCore counts.

Option E is incorrect because both Gen5 and Premium-series support the Business Critical service tier.

Full explanation →
753
MCQhard

You need to design a business continuity plan for an Azure SQL Database that must meet an RPO of 5 seconds and an RTO of 1 hour. The database is used by a global application with users in North America and Europe. Which configuration should you implement?

A.Configure auto-failover groups between two Azure regions.
B.Configure Active Geo-Replication with a readable secondary in a different Azure region and add to a failover group.
C.Deploy the database as zone-redundant within a single region.
D.Use geo-restore of automated backups to recover in another region.
AnswerB

Active Geo-Replication with failover group meets RPO of 5 seconds and RTO of 1 hour.

Why this answer

Active Geo-Replication with a readable secondary in a different Azure region, added to a failover group, meets the RPO of 5 seconds and RTO of 1 hour. Active Geo-Replication provides asynchronous replication with an RPO of up to 5 seconds, and failover groups enable automatic or manual failover with an RTO typically under 1 hour. The readable secondary supports read-only workloads in Europe, optimizing global application performance.

Exam trap

The trap here is that candidates may confuse auto-failover groups (Option A) with Active Geo-Replication, not realizing that failover groups alone do not enforce the low RPO; they must be combined with Active Geo-Replication to achieve the 5-second RPO, making Option B the precise configuration.

How to eliminate wrong answers

Option A is wrong because auto-failover groups alone do not guarantee an RPO of 5 seconds; they rely on the underlying replication method, which defaults to asynchronous replication but can be configured with Active Geo-Replication for tighter RPO, but the option does not specify Active Geo-Replication, so it may use standard geo-replication with higher RPO. Option C is wrong because zone-redundancy within a single region protects only against zonal failures, not regional outages, and cannot meet the RPO/RTO for a global application requiring cross-region failover. Option D is wrong because geo-restore of automated backups has an RPO of 5-10 minutes (backup frequency) and an RTO of hours (restore time), failing the 5-second RPO and 1-hour RTO requirements.

Full explanation →
754
MCQhard

You run the above Kusto query and see that a database named 'OrdersDB' has high wait times for 'FAILOVER_GROUP_WAIT' every hour. What does this indicate?

A.The secondary database is experiencing high connection requests
B.The primary database is not receiving any write transactions
C.The failover group is experiencing frequent automatic failovers
D.Replication lag between primary and secondary is high
AnswerD

FAILOVER_GROUP_WAIT measures time spent waiting for replication to complete.

Why this answer

High wait times for 'FAILOVER_GROUP_WAIT' indicate significant replication lag between the primary and secondary databases, as this wait type measures the time the primary spends waiting for the secondary to apply changes. Option A is incorrect because the query does not measure connection requests. Option B is incorrect because write transactions may still occur; the wait is about replication, not transaction absence.

Option C is incorrect because frequent automatic failovers are not indicated by this wait stat; it measures replication lag, not failover frequency.

Full explanation →
755
MCQeasy

You need to automatically scale an Azure SQL Database based on workload patterns. The solution must use built-in Azure features and minimize manual intervention. Which feature should you configure?

A.Use Azure Data Factory to scale the database based on pipeline triggers.
B.Create an Azure Automation runbook that scales the database on a schedule.
C.Configure autoscale settings for the Azure SQL Database.
D.Use an elastic pool and manually adjust eDTUs.
AnswerC

Autoscale automatically adjusts resources based on workload.

Why this answer

Azure SQL Database supports built-in autoscale through the 'Autoscale' feature (serverless compute tier or DTU-based scaling policies), which automatically adjusts resources based on workload patterns without manual intervention. This is the only option that leverages a native Azure feature for dynamic, reactive scaling rather than scheduled or manual actions.

Exam trap

The trap here is that candidates confuse 'automation' (Azure Automation runbooks) with 'automatic scaling' (built-in autoscale), or mistakenly think Azure Data Factory can manage database scaling, when only the native autoscale feature provides dynamic, policy-driven scaling without manual intervention.

How to eliminate wrong answers

Option A is wrong because Azure Data Factory is an ETL/integration service, not a database scaling mechanism; pipeline triggers cannot directly modify Azure SQL Database service tier or compute resources. Option B is wrong because Azure Automation runbooks require custom scripting and scheduled execution, which is not 'built-in' automatic scaling and introduces manual maintenance overhead. Option D is wrong because manually adjusting eDTUs in an elastic pool contradicts the requirement to 'minimize manual intervention' and does not provide automatic scaling based on workload patterns.

Full explanation →
756
MCQmedium

Your Azure SQL Managed Instance is configured to allow connections only from a specific virtual network. You need to ensure that clients from on-premises can connect using a point-to-site VPN. What additional configuration is required?

A.Configure a point-to-site VPN connection on the virtual network gateway and ensure the gateway subnet is in the same VNet as the managed instance.
B.Configure a private endpoint for the managed instance.
C.Deploy Azure Bastion in the same VNet.
D.Add the on-premises public IP address to the managed instance firewall rules.
AnswerA

Point-to-site VPN allows on-premises clients to connect to the VNet, reaching the managed instance.

Why this answer

A point-to-site VPN connection on the virtual network gateway allows individual on-premises clients to securely connect to the Azure VNet over SSTP or IKEv2. Since the managed instance is deployed inside a subnet of that VNet and its endpoint is restricted to the VNet, the VPN tunnel provides the necessary network path for on-premises clients to reach the instance without exposing it to the public internet.

Exam trap

The trap here is that candidates confuse the point-to-site VPN requirement with the need for a private endpoint, not realizing that SQL Managed Instance is already natively integrated into the VNet and does not require an additional private endpoint for private connectivity.

How to eliminate wrong answers

Option B is wrong because a private endpoint is used for Azure PaaS services (like SQL Database or SQL Managed Instance) to provide a private IP address within a VNet, but SQL Managed Instance already has a native VNet-deployed endpoint; adding a private endpoint is redundant and not required for point-to-site VPN connectivity. Option C is wrong because Azure Bastion provides secure RDP/SSH access to VMs inside a VNet via the Azure portal, not client-to-service connectivity for database clients. Option D is wrong because the managed instance does not use server-level firewall rules like Azure SQL Database; its network security is enforced entirely through VNet integration and NSG rules, so adding a public IP address to firewall rules is not applicable.

Full explanation →
757
Multi-Selecteasy

Which TWO actions can be performed using Azure Automation runbooks for Azure SQL Database? (Choose two.)

Select 2 answers
A.Deploy Azure Resource Manager templates
B.Execute T-SQL scripts against Azure SQL Database
C.Create SQL Agent jobs on Azure SQL Database
D.Scale an Azure SQL Database up or down
E.Manage on-premises SQL Server instances directly
AnswersB, D

Using Invoke-SqlCmd module.

Why this answer

Azure Automation runbooks can execute T-SQL scripts and scale databases. They cannot manage on-premises SQL Server directly (requires hybrid worker). SQL Agent jobs cannot be created in Azure SQL Database.

Full explanation →
758
Multi-Selectmedium

Which TWO methods can you use to monitor Azure SQL Database wait statistics?

Select 2 answers
A.Query sys.dm_os_wait_stats
B.Azure Monitor Alerts
C.Query sys.dm_exec_query_stats
D.Dynamic Management Views
E.Query Store wait statistics reports
AnswersA, E

Direct wait stats DMV.

Why this answer

Options A and E are correct. Querying sys.dm_os_wait_stats (A) directly provides cumulative wait statistics at the instance level. Query Store wait statistics reports (E) offer a query-focused view of wait statistics over time.

Option B is incorrect because Azure Monitor Alerts are for setting notifications based on metrics or logs, not for monitoring wait statistics directly. Option C is incorrect because sys.dm_exec_query_stats shows execution statistics like CPU and duration, not wait types. Option D is incorrect because 'Dynamic Management Views' is a broad category, not a specific method; while some DMVs relate to waits, the generic term is not a valid answer.

Full explanation →
759
MCQhard

You are the database administrator for an Azure SQL Database used by a financial trading application. The database is in the Business Critical service tier with 16 vCores. The application executes thousands of small, high-frequency transactions per second. Recently, the application's response time has increased, and you observe high PAGELATCH_EX waits in sys.dm_os_waiting_tasks. The database is 500 GB with a single data file (tempdb.mdf) and a single log file (tempdb_log.ldf). TempDB is configured with the default settings. You need to reduce PAGELATCH_EX contention in TempDB. What should you do?

A.Add additional TempDB data files equal to the number of vCores (16).
B.Move TempDB to Azure Premium Storage for better IO latency.
C.Increase the database service tier to 24 vCores.
D.Increase the initial size of the TempDB data file to 100 GB.
AnswerA

Multiple data files reduce allocation contention on SGAM and PFS pages, which cause PAGELATCH_EX waits.

Why this answer

PAGELATCH_EX contention in TempDB is typically caused by allocation page contention when many concurrent transactions compete for the same system page (e.g., PFS, GAM, SGAM). In Azure SQL Database Business Critical tier, adding multiple TempDB data files equal to the number of vCores (16) reduces contention by distributing allocations across files, each with its own allocation structures. This is a proven best practice for high-concurrency workloads like financial trading applications.

Exam trap

The trap here is that candidates often confuse PAGELATCH_EX (allocation page contention) with PAGEIOLATCH_EX (IO-related waits) and incorrectly choose storage or tier upgrades instead of the file-count solution.

How to eliminate wrong answers

Option B is wrong because moving TempDB to Azure Premium Storage improves IO latency but does not resolve allocation page latch contention, which is a logical contention issue, not a physical IO bottleneck. Option C is wrong because increasing the service tier to 24 vCores adds more CPU and memory but does not address the root cause of PAGELATCH_EX waits, which are related to TempDB file structure, not compute resources. Option D is wrong because increasing the initial size of the single TempDB data file to 100 GB does not reduce contention; it only reduces auto-growth events but still leaves all allocations contending on the same system pages within a single file.

Full explanation →
760
MCQhard

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that during peak hours, the log rate is throttled frequently. You need to reduce log write throttling. What should you do?

A.Enable accelerated database recovery to reduce log generation.
B.Reduce the size of individual transactions to commit more frequently.
C.Partition large tables to spread log writes across files.
D.Increase the service level objective (SLO) to a higher vCore count.
AnswerD

Higher SLO provides higher log rate limit.

Why this answer

In Azure SQL Database Hyperscale, log write throttling occurs when the log generation rate exceeds the throughput capacity of the current service level objective (SLO). Increasing the vCore count raises the log write throughput limit, directly alleviating throttling. This is the correct approach because Hyperscale log rate limits are tied to the SLO, and higher tiers provide higher log I/O bandwidth.

Exam trap

The trap here is that candidates often assume reducing transaction size (Option B) will reduce log rate, but in Hyperscale, frequent small commits increase log flush frequency and can actually trigger throttling due to the per-second log rate limit.

How to eliminate wrong answers

Option A is wrong because Accelerated Database Recovery (ADR) reduces transaction rollback and recovery time, not the volume of log writes; it does not address log rate throttling. Option B is wrong because committing more frequently with smaller transactions actually increases the number of log flushes and can worsen log rate throttling, not reduce it. Option C is wrong because partitioning large tables distributes data across filegroups but does not affect the log write rate to the single transaction log; log writes are serialized and not spread across files.

Full explanation →
761
MCQeasy

You need to ensure that only specific Azure services can access your Azure SQL Database server. You want to allow traffic from Azure services but block all other traffic. What should you configure?

A.Set the firewall rule 'Allow Azure Services and resources to access this server' to ON and remove all other IP rules.
B.Set the firewall rule 'Allow Azure Services and resources to access this server' to OFF and add a rule for 0.0.0.0.
C.Set firewall rules to deny all IP addresses.
D.Set the firewall rule 'Allow Azure Services and resources to access this server' to ON and add a rule for 0.0.0.0.
AnswerA

This allows only Azure services.

Why this answer

Setting the 'Allow Azure Services and resources to access this server' firewall rule to ON enables a special rule that permits traffic from all Azure datacenter IP ranges, while removing all other IP rules ensures no other external traffic can reach the server. This configuration meets the requirement to allow only Azure services and block all other traffic, as the Azure services rule is a blanket allow for Azure-originated connections without needing specific IP addresses.

Exam trap

The trap here is confusing the 'Allow Azure Services' rule with a generic 0.0.0.0 rule, leading candidates to think they need to add 0.0.0.0 to allow Azure traffic, when in fact the Azure services rule is a distinct mechanism that does not require explicit IP entries.

How to eliminate wrong answers

Option B is wrong because setting the rule to OFF and adding a rule for 0.0.0.0 does not allow Azure services; the 0.0.0.0 rule is typically used to allow all IPs, which contradicts the requirement to block non-Azure traffic. Option C is wrong because denying all IP addresses would block all traffic, including Azure services, failing to meet the requirement to allow Azure services. Option D is wrong because adding a rule for 0.0.0.0 alongside the Azure services rule would allow all IP addresses (including non-Azure traffic), which violates the requirement to block all other traffic.

Full explanation →
762
MCQhard

You are troubleshooting a failed automated backup for an Azure SQL Database. The backup policy is configured for geo-redundant storage (RA-GRS). You notice that the last successful backup was 48 hours ago. The database is still online and accessible. What is the most likely cause of the backup failure?

A.The backup storage account has been deleted or has incorrect firewall rules.
B.The database is experiencing high transaction log generation, exceeding the backup throughput limit.
C.The geo-replication link is broken, causing backup failures.
D.The database is in a paused state due to serverless compute.
AnswerB

High log generation can cause backup jobs to time out or fail, especially if the log backup rate is insufficient.

Why this answer

Azure SQL Database automated backups can fail if the database generates a high volume of transaction logs, causing the backup process to exceed its throughput limit or timeout. This can happen even if the database is online and accessible. Option A (storage account issues) would likely cause persistent failures for all backups, not just a single one.

Option C (geo-replication link) is unrelated to backup failure. Option D (paused state) would prevent backups entirely. Therefore, B is the correct answer.

Full explanation →
763
MCQmedium

You need to monitor Azure SQL Database performance over time and receive alerts when CPU usage exceeds 80%. Which Azure service should you use?

A.Automatic tuning
B.Query Performance Insight
C.Azure Monitor Alerts
D.SQL Assessment
AnswerC

Azure Monitor Alerts can trigger on CPU metric thresholds.

Why this answer

Azure Monitor Alerts is the correct service because it allows you to create metric-based alert rules that trigger when the CPU percentage of an Azure SQL Database exceeds a defined threshold (e.g., 80%). It continuously monitors performance metrics over time and sends notifications (e.g., email, SMS, or webhook) when the condition is met, fulfilling the requirement for both monitoring and alerting.

Exam trap

The trap here is that candidates often confuse Query Performance Insight (which shows historical query performance data) with a monitoring/alerting tool, but it lacks the ability to set proactive threshold-based alerts like Azure Monitor Alerts provides.

How to eliminate wrong answers

Option A is wrong because Automatic tuning is a feature that automatically adjusts index creation, index dropping, and query plan choices to optimize performance; it does not provide monitoring or alerting capabilities. Option B is wrong because Query Performance Insight provides detailed analysis of query performance, including resource consumption and wait statistics, but it does not support proactive alerting based on CPU thresholds. Option D is wrong because SQL Assessment evaluates the configuration and best practices of Azure SQL Database (e.g., security, performance settings) and generates a report, but it does not monitor real-time performance or send alerts.

Full explanation →
764
Multi-Selectmedium

Your company uses Azure SQL Database and needs to comply with GDPR. You must implement data classification and protection. Which TWO actions should you take? (Choose two.)

Select 2 answers
A.Configure sensitivity labels using Microsoft Purview Information Protection.
B.Implement Always Encrypted for all columns containing personal data.
C.Install the Azure Information Protection client on all client machines.
D.Enable Microsoft Defender XDR for the database server.
E.Use SQL Data Discovery & Classification in the Azure portal to classify columns containing personal data.
AnswersA, E

Sensitivity labels can be applied to classified columns and are integrated with Microsoft Purview Information Protection.

Why this answer

Microsoft Purview Information Protection provides sensitivity labels that can be applied to columns in Azure SQL Database to classify and protect personal data, meeting GDPR requirements. These labels enforce encryption, access restrictions, and visual markings, integrating with Azure SQL's data classification capabilities.

Exam trap

The trap here is confusing data classification (labeling and identifying sensitive data) with data encryption (Always Encrypted) or threat detection (Defender XDR), leading candidates to pick security features that do not fulfill the GDPR requirement for classification and labeling.

Full explanation →
765
MCQhard

Refer to the exhibit. An administrator is configuring geo-replication for an Azure SQL Database. The admin runs the commands and sees the output shown. What is the current status of the geo-replication link?

A.The secondary database is fully synchronized and ready for failover.
B.The secondary database is lagging behind the primary by a significant amount.
C.The secondary database is in the process of initial seeding and is not yet fully synchronized.
D.The replication link is broken and needs to be re-created.
AnswerA

CATCH_UP with 100% indicates full synchronization.

Why this answer

The output shows replicationState as CATCH_UP and percentComplete as 100, indicating that all data changes on the primary have been synchronized to the secondary. This means the geo-replication link is healthy and data is fully synchronized.

Full explanation →
766
MCQmedium

You are reviewing an ARM template snippet that configures a long-term retention (LTR) policy for an Azure SQL Database. Based on the exhibit, how long will weekly backups be retained?

A.4 weeks. [CORRECT]
B.4 days. [wrong]
C.4 months. [wrong]
D.4 years. [wrong]
AnswerA

P4W in ISO 8601 duration means 4 weeks.

Why this answer

In the ARM template snippet, the weeklyRetention property is set to 'P4W' (ISO 8601 duration format), which means 4 weeks. Therefore, weekly backups are retained for exactly 4 weeks.

Exam trap

The trap is misinterpreting the ISO 8601 duration 'P4W'. 'P4W' represents 4 weeks, not 1 month (P1M), 4 days (P4D), or 4 years (P4Y). Many candidates mistakenly assume 'W' means something else or misread the number, leading to incorrect options like 4 days, 4 months, or 4 years.

How to eliminate wrong answers

Option B is wrong because '4 days' would correspond to a duration like 'P4D' in ISO 8601, not 'P1M'. Option C is wrong because '4 months' would be 'P4M', not 'P1M'. Option D is wrong because '4 years' would be 'P4Y', not 'P1M'.

The trap is misinterpreting the ISO 8601 duration format, where 'P1M' specifically means 1 month, not 4 of any unit.

Full explanation →
767
Multi-Selectmedium

Which THREE are valid methods to authenticate to Azure SQL Database using Microsoft Entra ID?

Select 3 answers
A.User-assigned managed identity without a contained database user
B.Microsoft Entra ID password authentication
C.Integrated Windows authentication (Kerberos)
D.Azure CLI authentication
E.Service principal authentication
AnswersB, C, E

Supports username/password authentication via Entra ID.

Why this answer

Microsoft Entra ID password authentication allows users to authenticate to Azure SQL Database using their Entra ID credentials directly, without requiring Windows domain join or integrated authentication. This method uses the Entra ID access token flow, where the user provides their username and password to obtain a token from the Microsoft Entra ID endpoint, which is then used to connect to the database. It is a straightforward, cloud-native authentication method supported for contained database users mapped to Entra ID identities.

Exam trap

The trap here is that candidates often confuse authentication methods with authentication tools or prerequisites, mistakenly thinking that a managed identity can authenticate without a contained database user or that Azure CLI is a direct authentication method for SQL Database rather than a token acquisition tool.

Full explanation →
768
MCQmedium

You deploy a new Azure SQL Database and need to ensure that all queries are logged for performance analysis. Which configuration should you enable?

A.Data classification
B.Server-level audit
C.Diagnostic settings for SQLInsights
D.Query Store
AnswerD

Query Store captures query runtime statistics and plans.

Why this answer

Query Store captures a history of query execution plans, runtime statistics, and wait statistics, enabling detailed performance analysis and troubleshooting. It is the correct choice because it is specifically designed to log query-level performance data for Azure SQL Database without requiring external storage or additional configuration.

Exam trap

The trap here is that candidates often confuse server-level audit or diagnostic settings with query-level logging, but Query Store is the only feature that natively logs query execution plans and runtime statistics for performance analysis in Azure SQL Database.

How to eliminate wrong answers

Option A is wrong because Data Classification is a security feature for identifying and labeling sensitive columns, not for logging query performance. Option B is wrong because Server-level audit logs database events for compliance and security auditing, not query execution details for performance analysis. Option C is wrong because Diagnostic settings for SQLInsights send telemetry to Azure Monitor for broader monitoring, but they do not capture per-query execution plans and runtime statistics like Query Store does.

Full explanation →
769
MCQmedium

Your organization uses Azure SQL Database with Microsoft Entra ID authentication. You need to ensure that a specific user can only read data from the Sales schema. The user should not be able to modify any data. What should you do?

A.Create a contained user mapped to the Microsoft Entra identity, then grant SELECT on the Sales schema to the user, and deny SELECT on all other schemas.
B.Create a contained user mapped to the Microsoft Entra identity and add the user to the db_datareader role.
C.Create a contained user mapped to the Microsoft Entra identity and add the user to the db_denydatawriter role.
D.Create a contained user mapped to the Microsoft Entra identity and add the user to the db_datareader role, then deny SELECT on all schemas except Sales.
AnswerA

This explicitly grants SELECT on Sales schema and denies on others, achieving the requirement.

Why this answer

It creates a contained database user mapped to the Microsoft Entra identity, then explicitly grants SELECT on the Sales schema. This ensures the user can read data only from that schema. Denying SELECT on all other schemas removes any implicit permissions (such as those from the public role) and enforces the least-privilege principle.

This approach is schema-level permission control, which is more granular than fixed database roles.

Exam trap

The trap here is that candidates often assume fixed database roles like db_datareader or db_denydatawriter are sufficient, but they either grant too much access or fail to grant the required read access, whereas schema-level permissions with explicit DENY provide precise control.

How to eliminate wrong answers

Option B is wrong because adding the user to the db_datareader role grants SELECT on all tables and views in the entire database, not just the Sales schema, violating the requirement to restrict access to only the Sales schema. Option C is wrong because the db_denydatawriter role only prevents INSERT, UPDATE, DELETE, and MERGE operations but does not grant any read access; the user would have no permission to read data from the Sales schema. Option D is wrong because adding the user to db_datareader first grants SELECT on all objects, and then denying SELECT on all schemas except Sales would create a conflict: the deny overrides the grant (deny takes precedence in SQL Server permission precedence), effectively blocking read access to the Sales schema as well.

Full explanation →
770
Multi-Selectmedium

You are designing a secure environment for Azure SQL Database. Which TWO of the following are recommended practices for network security?

Select 2 answers
A.Enable the 'Allow Azure services and resources to access this server' firewall setting.
B.Use VNet service endpoints instead of Private Link to reduce costs.
C.Use Azure Private Link to connect to the database from a virtual network.
D.Disable public network access on the SQL server.
E.Add firewall rules that allow all IP addresses from your organization's IP range.
AnswersC, D

Private Link provides a private IP address within the VNet, eliminating exposure to the internet.

Why this answer

Options C and D are correct. Azure Private Link (C) ensures traffic between the virtual network and Azure SQL Database stays within the Microsoft backbone network, avoiding the public internet. Disabling public network access (D) at the server level blocks all internet traffic, enhancing security.

Option A is incorrect because enabling 'Allow Azure services...' is a broad rule that permits traffic from any Azure service, which may be insecure. Option B is incorrect because VNet service endpoints are legacy and less secure compared to Private Link. Option E is incorrect because allowing a large IP range increases the attack surface.

Full explanation →
771
MCQeasy

A company plans to deploy a new application on Azure VMs that requires a highly available SQL Server database with automatic failover and readable secondaries. The database size is 1 TB. Which deployment option meets these requirements with the least administrative overhead?

A.Azure SQL Database in the General Purpose tier.
B.SQL Server on Azure VMs with Always On Availability Groups.
C.Azure SQL Managed Instance in the Business Critical tier.
D.Azure SQL Database in the Hyperscale tier.
AnswerC

Built-in high availability with automatic failover and readable secondaries; minimal admin overhead.

Why this answer

Azure SQL Managed Instance in the Business Critical tier provides built-in high availability with automatic failover and readable secondaries via Always On Availability Groups, while requiring minimal administrative overhead. The 1 TB database size is supported in the Business Critical tier (up to 4 TB), and the managed platform handles patching, backups, and failover orchestration automatically, unlike IaaS-based solutions.

Exam trap

The trap here is that candidates often choose SQL Server on Azure VMs (Option B) thinking they need full control for high availability, but they overlook the 'least administrative overhead' requirement, which favors a fully managed PaaS offering like Azure SQL Managed Instance Business Critical.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database in the General Purpose tier does not provide readable secondaries; it uses local SSD storage for tempdb but relies on remote storage (Azure Premium Blob) for data and log, and its high availability model does not include readable replicas. Option B is wrong because SQL Server on Azure VMs with Always On Availability Groups requires significant administrative overhead to configure, maintain, and monitor the Windows Server Failover Cluster, listener, and replica synchronization, which contradicts the 'least administrative overhead' requirement. Option D is wrong because Azure SQL Database in the Hyperscale tier, while offering readable secondaries and high availability, is designed for very large databases (up to 100 TB) and may introduce higher latency for write-heavy workloads due to its log-based page server architecture, and it is not the optimal choice for a 1 TB database when Business Critical provides a simpler, fully managed solution with lower overhead.

Full explanation →
772
MCQmedium

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. You need to ensure that the database is protected by Microsoft Defender for Cloud (formerly Azure Security Center) with advanced threat protection. What should you enable?

A.Deploy Microsoft Sentinel and connect the SQL Managed Instance
B.Enable Microsoft Defender for Cloud on the subscription or resource
C.Configure Microsoft Purview Data Map
D.Enable Azure SQL Database auditing
AnswerB

Defender for Cloud includes advanced threat protection for SQL.

Why this answer

Microsoft Defender for Cloud provides advanced threat protection for Azure SQL Managed Instance at the subscription or resource level. Enabling it on the subscription or the specific resource activates threat detection capabilities, including alerts for SQL injection, brute-force attacks, and anomalous access patterns, without requiring additional services.

Exam trap

The trap here is that candidates often confuse auditing (which logs events) with threat protection (which actively detects and alerts on suspicious activity), leading them to select auditing as the answer, or they mistakenly think Microsoft Sentinel is required to enable threat detection when it is actually an optional SIEM integration.

How to eliminate wrong answers

Option A is wrong because Microsoft Sentinel is a SIEM (Security Information and Event Management) solution that ingests security logs from various sources, including Defender for Cloud, but it does not directly enable advanced threat protection for SQL Managed Instance; it is an additional layer for centralized security monitoring, not the mechanism to enable threat protection. Option C is wrong because Microsoft Purview Data Map is a data governance and cataloging service for managing data lineage, classification, and discovery, not a security tool for threat detection or protection against database attacks. Option D is wrong because enabling Azure SQL Database auditing captures and logs database events for compliance and forensic analysis, but it does not provide real-time threat detection or advanced protection against malicious activities like SQL injection or anomalous access patterns.

Full explanation →
773
MCQeasy

You need to design a high availability solution for an Azure SQL Database that supports an online transaction processing (OLTP) application. The solution must provide automatic failover within seconds in case of a node failure and guarantee zero data loss. Which deployment 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 provides synchronous replication and zero data loss.

Why this answer

(Business Critical tier with zone redundancy) is correct because the Business Critical tier uses synchronous replication and a quorum-based commit to replicas, ensuring zero data loss on failover and failover within seconds. Option A (Basic tier) does not support zone redundancy or automatic failover with zero data loss. Option B (General Purpose tier) uses asynchronous replication, which may result in data loss during failover.

Option C (Hyperscale tier) uses a log-based service with a buffer, which can lead to data loss on failover.

Full explanation →
774
MCQeasy

You have an Azure SQL Database in the Business Critical tier with zone redundancy enabled. The database experiences a brief outage due to a zone failure. How does the platform automatically recover?

A.You must perform a manual failover to a secondary replica.
B.A replica in another availability zone is automatically promoted to primary.
C.The database is restored from the latest backup.
D.The database becomes read-only until the zone is restored.
AnswerB

Zone redundancy provides automatic failover to a replica in another zone.

Why this answer

The Business Critical tier with zone redundancy automatically fails over to a replica in another zone without data loss. Option A is wrong because the service heals itself without manual intervention. Option C is wrong because no data loss occurs (zero data loss is guaranteed).

Option D is wrong because automatic failover happens within seconds.

Full explanation →
775
Multi-Selecthard

You have an Azure SQL Database that is configured with geo-replication. You need to optimize read performance for reporting queries that run on the secondary replica. Which three actions should you take? (Choose three.)

Select 3 answers
A.Enable automatic tuning on the secondary.
B.Configure the secondary as a readable secondary.
C.Create nonclustered indexes on the secondary replica.
D.Force query plans on the primary replica to benefit the secondary.
E.Use read-only routing to direct reporting queries to the secondary.
AnswersA, C, E

Can optimize query performance automatically.

Why this answer

Enabling automatic tuning on the secondary replica allows the database to automatically create, drop, and force index recommendations based on the workload running on that replica. This is particularly beneficial for reporting queries, as the secondary can independently optimize its own index strategy without affecting the primary. Automatic tuning on a readable secondary helps maintain query performance by adapting to the read-heavy workload patterns typical of reporting scenarios.

Exam trap

The trap here is that candidates often assume that enabling automatic tuning on the primary automatically benefits the secondary, or that forcing query plans on the primary will propagate to the secondary, when in fact each replica maintains its own query store and plan cache.

Full explanation →
776
MCQeasy

You need to audit all successful and failed login attempts to an Azure SQL Database. Which feature should you enable?

A.Azure SQL Auditing
B.Advanced Threat Protection
C.Transparent Data Encryption (TDE)
D.SQL Vulnerability Assessment
AnswerA

Auditing tracks database events and writes them to an audit log.

Why this answer

Azure SQL Auditing is the correct feature because it tracks database events, including both successful and failed login attempts, and writes them to an audit log in your Azure Storage account, Log Analytics workspace, or Event Hubs. This allows you to monitor and review authentication activity for compliance and security analysis. Other features like Advanced Threat Protection, TDE, and Vulnerability Assessment do not capture login event logs.

Exam trap

The trap here is that candidates often confuse Advanced Threat Protection's alerting on suspicious logins with the comprehensive logging of all login attempts provided by Azure SQL Auditing, leading them to select ATP instead.

How to eliminate wrong answers

Option B (Advanced Threat Protection) is wrong because it detects anomalous activities indicating potential threats (e.g., SQL injection, brute force attacks) but does not provide a configurable audit log of all successful and failed login attempts; it alerts on suspicious patterns rather than recording every login event. Option C (Transparent Data Encryption) is wrong because it encrypts the database at rest and in transit but has no capability to log authentication events; it protects data confidentiality, not audit trails. Option D (SQL Vulnerability Assessment) is wrong because it scans for security misconfigurations and vulnerabilities (e.g., missing firewall rules, weak passwords) but does not capture or store login attempt logs; it is a periodic assessment tool, not an ongoing audit mechanism.

Full explanation →
777
MCQhard

You manage an Azure SQL Database that is part of a failover group. You need to automate the failover to the secondary region in the event of a disaster. Which approach should you use?

A.Configure the auto-failover group to automatically fail over.
B.Schedule a failover using elastic jobs.
C.Create an Azure Automation runbook that initiates the failover.
D.Use a SQL Server Agent job to trigger failover.
AnswerA

Auto-failover groups automatically handle failover to the secondary region in the event of a disaster, providing built-in automation without additional setup.

Why this answer

Auto-failover groups are designed to automatically fail over to the secondary region in the event of a disaster, providing built-in automation. Option C is incorrect because while an Azure Automation runbook could be used to initiate a failover manually, it is redundant since the auto-failover group already handles automatic failover. Options B and D are incorrect because elastic jobs are for management tasks like data consistency, and SQL Server Agent is not available in Azure SQL Database.

Full explanation →
778
Multi-Selectmedium

You have an Azure SQL Managed Instance. You need to implement a disaster recovery solution that provides automatic failover with an RTO of less than 1 hour. Which THREE actions should you take?

Select 3 answers
A.Enable zone redundancy on the primary instance.
B.Create a secondary Managed Instance in a different Azure region.
C.Configure a failover group between the primary and secondary.
D.Enable geo-replication on the instance.
E.Use the Business Critical service tier for both instances.
AnswersB, C, E

Required for DR.

Why this answer

To achieve automatic failover with an RTO under 1 hour for Azure SQL Managed Instance, you need a secondary instance in a different region (B), a failover group for automatic failover (C), and the Business Critical service tier which supports failover groups (E). Zone redundancy (A) provides high availability within a region, not disaster recovery across regions. Geo-replication (D) is not supported on Managed Instance; failover groups are the correct mechanism.

Full explanation →
779
MCQhard

You are the database administrator for a healthcare organization that uses Azure SQL Database. You need to implement column-level encryption for sensitive patient data (e.g., Social Security numbers) using Always Encrypted. The application connecting to the database is a .NET application running on Azure Virtual Machines. The application should be able to perform parameterized queries on encrypted columns without revealing the plaintext to the database. Which configuration is essential for this setup?

A.Use Always Encrypted with secure enclaves and configure the enclave attestation URL.
B.Store the column master key in Azure Key Vault and configure the application to retrieve it.
C.Implement dynamic data masking on the columns containing Social Security numbers.
D.Enable Transparent Data Encryption (TDE) on the database.
AnswerA

Secure enclaves enable rich computations on encrypted data, meeting the requirement.

Why this answer

The scenario requires the application to perform parameterized queries on encrypted columns without revealing plaintext to the database. Always Encrypted with secure enclaves allows rich computations (e.g., equality, pattern matching) on encrypted data inside a hardware-based enclave, and the enclave attestation URL is essential to verify the enclave's integrity. Without the enclave, Always Encrypted only supports equality comparisons and cannot handle parameterized queries that require operations like LIKE or range checks on encrypted columns.

Exam trap

The trap here is that candidates often confuse Always Encrypted without enclaves (which only supports equality) with Always Encrypted with enclaves (which supports richer queries), and they may incorrectly choose key management (Option B) as the essential configuration instead of the enclave attestation URL.

How to eliminate wrong answers

Option B is wrong because storing the column master key in Azure Key Vault is a best practice for key management but is not the essential configuration for enabling parameterized queries on encrypted columns; the key retrieval is handled by the client driver, not by the database or enclave setup. Option C is wrong because dynamic data masking only hides data from unauthorized users at the query result level but does not encrypt the data at rest or prevent the database engine from seeing plaintext, which fails the requirement that the database never sees plaintext. Option D is wrong because Transparent Data Encryption (TDE) encrypts the database at rest (storage-level) but does not protect data from being visible to the database engine during query processing, so it cannot prevent the database from seeing plaintext Social Security numbers.

Full explanation →
780
Multi-Selectmedium

Which TWO actions are required to enable Microsoft Entra ID authentication for an Azure SQL Database?

Select 2 answers
A.Enable SQL Server authentication only.
B.Set an Microsoft Entra ID admin for the Azure SQL Server.
C.Create contained database users mapped to Microsoft Entra ID identities.
D.Assign the SQL Server Contributor role to the Entra ID users.
E.Enable Azure AD integration on the SQL server.
AnswersB, C

An Entra ID admin must be set to allow Entra ID authentication.

Why this answer

To enable Entra ID authentication, you must set an Entra ID admin for the SQL server and create contained database users mapped to Entra ID identities. Enabling Azure AD (Entra ID) integration is automatic. Assigning Azure RBAC roles is for management plane, not database authentication.

The SQL Server authentication mode is always enabled.

Full explanation →
781
MCQeasy

Your Azure SQL Database is protected by a failover group. You need to ensure that during a failover to the secondary region, only authenticated applications can connect. What should you configure?

A.Set up a private endpoint for each region and update connection strings manually.
B.Deploy an Always On availability group listener.
C.Use the failover group listener endpoint with Microsoft Entra ID authentication.
D.Configure geo-replication and use the secondary server's public endpoint.
AnswerC

The listener ensures continuous connectivity with authentication.

Why this answer

The failover group listener endpoint provides a DNS name that automatically points to the current primary region. By configuring Microsoft Entra ID authentication on the logical server, you ensure that only authenticated applications (using Microsoft Entra tokens) can connect, even after a failover. This eliminates reliance on SQL authentication passwords, which could be compromised or misconfigured across regions.

Exam trap

The trap here is that candidates confuse the failover group listener with a simple DNS alias, forgetting that it also integrates with Microsoft Entra ID to enforce authentication, not just connectivity.

How to eliminate wrong answers

Option A is wrong because private endpoints are per-region and require manual connection string updates after failover, which does not automatically ensure only authenticated applications connect. Option B is wrong because an Always On availability group listener is a SQL Server on-premises or IaaS feature, not applicable to Azure SQL Database failover groups. Option D is wrong because geo-replication with a public endpoint does not enforce authentication requirements; it exposes the secondary server to public network access without the automatic failover and authentication control provided by a failover group listener.

Full explanation →
782
Multi-Selectmedium

You are responsible for securing Azure SQL Managed Instance. You need to ensure that only encrypted connections are allowed and that all client connections use the latest TLS protocol. Which TWO configurations should you implement?

Select 2 answers
A.Set the 'Minimal TLS Version' to 1.2.
B.Configure a firewall rule to allow only specific IP addresses.
C.Enable Microsoft Defender for SQL.
D.Enable 'Force SSL' on the server.
E.Enable Transparent Data Encryption (TDE).
AnswersA, D

Forces clients to use TLS 1.2 or higher.

Why this answer

Options A and D are correct. Setting 'Minimal TLS Version' to 1.2 ensures that only encrypted connections using TLS 1.2 or higher are accepted, enforcing the latest protocol. Enabling 'Force SSL' ensures that all client connections are encrypted.

Option B is incorrect because firewall rules control network access, not encryption. Option C is incorrect because Microsoft Defender for SQL provides security threat detection, not encryption enforcement. Option E is incorrect because Transparent Data Encryption (TDE) encrypts data at rest, not in transit.

Full explanation →
783
MCQeasy

You are planning to migrate several on-premises SQL Server databases to Azure SQL Managed Instance. The databases use SQL Server Agent jobs, cross-database queries, and CLR assemblies. Which migration tool should you use to assess compatibility?

A.Azure Database Migration Service (DMS)
B.Azure Data Studio with SQL Server migration extension
C.Data Migration Assistant (DMA)
D.SQL Server Management Studio (SSMS)
AnswerC

DMA assesses compatibility with SQL Managed Instance.

Why this answer

The Data Migration Assistant (DMA) is specifically designed to assess compatibility of on-premises SQL Server databases with Azure SQL Managed Instance, including features like SQL Server Agent jobs, cross-database queries, and CLR assemblies. DMA performs a detailed feature parity check and identifies blocking issues, such as unsupported CLR permissions or cross-database dependencies, before migration. It provides a comprehensive report with remediation guidance, making it the ideal tool for this pre-migration assessment phase.

Exam trap

The trap here is that candidates often confuse the assessment tool (DMA) with the migration execution tool (DMS), or assume that SSMS or Azure Data Studio alone can perform a deep compatibility check, but DMA is the dedicated tool for identifying feature-level incompatibilities in complex workloads like those with Agent jobs and CLR assemblies.

How to eliminate wrong answers

Option A is wrong because Azure Database Migration Service (DMS) is a tool for performing the actual migration (online or offline), not for assessing compatibility; it assumes the source is already compatible. Option B is wrong because Azure Data Studio with the SQL Server migration extension is a newer tool that combines assessment and migration, but it relies on DMA under the hood for assessment and is not the primary standalone assessment tool for complex features like CLR and cross-database queries. Option D is wrong because SQL Server Management Studio (SSMS) lacks a built-in compatibility assessment feature for Azure SQL Managed Instance; it can be used to run scripts but does not provide automated analysis of Agent jobs, cross-database queries, or CLR assemblies.

Full explanation →
784
MCQhard

You are designing a secure environment for Azure SQL Managed Instance. The company requires that all database backups be encrypted using customer-managed keys stored in Azure Key Vault. Which combination of actions should you take?

A.Configure Always Encrypted with keys stored in Key Vault.
B.Enable Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault.
C.Use Azure Storage Service Encryption to encrypt the backup files.
D.Enable backup encryption using a certificate stored in the managed instance.
AnswerB

Correct: TDE with customer-managed key encrypts backups.

Why this answer

Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault allows you to encrypt the database backup files using a key that you control. When TDE is enabled and configured with a customer-managed key (CMK) stored in Azure Key Vault, Azure SQL Managed Instance automatically encrypts backups with the same TDE protector key, meeting the requirement for customer-managed backup encryption.

Exam trap

The trap here is that candidates often confuse Always Encrypted (which protects specific columns) with TDE (which encrypts the entire database and its backups), or they assume that Azure Storage Service Encryption (SSE) can be used to meet customer-managed key requirements, when in fact SSE uses platform-managed keys by default and does not apply to backup files in the same way as TDE with CMK.

How to eliminate wrong answers

Option A is wrong because Always Encrypted is a client-side encryption technology that protects sensitive data in transit and at rest within the database, but it does not encrypt the entire database backup files; backup encryption is handled separately by TDE. Option C is wrong because Azure Storage Service Encryption (SSE) encrypts data at rest in Azure Blob Storage using platform-managed keys, not customer-managed keys, and it applies to the storage layer, not to the backup files themselves in a way that satisfies the requirement for customer-managed key control. Option D is wrong because backup encryption using a certificate stored in the managed instance would use a service-managed certificate, not a customer-managed key from Azure Key Vault, and this approach is deprecated in favor of TDE with CMK.

Full explanation →
785
MCQmedium

Your Azure SQL Database is hitting the DTU limit frequently during business hours. You need to identify which queries are consuming the most DTU. What should you do?

A.Query sys.dm_db_resource_stats to see DTU usage per minute.
B.Use Azure Metrics for SQL Database to view DTU consumption per database.
C.Enable Query Store and review the Wait Statistics report.
D.Query sys.dm_exec_query_stats and correlate with sys.dm_db_resource_stats.
AnswerD

Querying sys.dm_exec_query_stats and correlating with sys.dm_db_resource_stats enables per-query DTU analysis.

Why this answer

Querying sys.dm_exec_query_stats and correlating with sys.dm_db_resource_stats allows you to identify which specific queries are consuming the most DTU. sys.dm_db_resource_stats provides DTU consumption data at the database level, while sys.dm_exec_query_stats gives query-level performance metrics. By joining these two DMVs, you can pinpoint the top DTU-consuming queries during business hours.

Full explanation →
786
MCQmedium

A company uses Azure SQL Database and wants to automate the process of refreshing a development database from production backups weekly. Which Azure service should be used to orchestrate this process including restore and post-restore scripts?

A.Elastic Database Jobs
B.Azure Logic Apps
C.Azure Automation with PowerShell runbooks
D.Azure Data Factory
AnswerC

PowerShell runbooks can call Restore-AzSqlDatabase and run post-restore scripts.

Why this answer

Azure Data Factory supports copy activity but not native restore. Azure Automation with PowerShell runbooks can orchestrate the entire workflow. Azure SQL Database elastic jobs are for T-SQL tasks across databases but not for restore operations.

Azure Logic Apps can also orchestrate but is less suited for complex scripting.

Full explanation →
787
MCQhard

Refer to the exhibit. You are configuring a backup retention policy for an Azure SQL Database via ARM template. You need to ensure that point-in-time restore (PITR) is available for the maximum supported period. What is the issue with the current configuration?

A.The retentionDays value is set below the minimum requirement
B.The retentionDays value exceeds the maximum supported retention period
C.The diffBackupIntervalInHours value is not a supported value
D.The backup policy does not specify a geo-redundant storage option
AnswerB

Maximum PITR retention is 35 days; 40 days is not supported.

Why this answer

The exhibit shows retentionDays set to 40 days, but the maximum supported retention period for point-in-time restore (PITR) in Azure SQL Database is 35 days. Therefore, the configuration is invalid because it exceeds the maximum. Option A is incorrect because the minimum retention is 7 days, so 40 days is above the minimum.

Option C is incorrect: diffBackupIntervalInHours of 12 is a supported value (12 or 24). Option D is incorrect because geo-redundant storage is not required for PITR; it is a separate backup option.

Full explanation →
788
MCQmedium

Your company has an Azure SQL Database with active geo-replication to a secondary region. During a regional outage, the secondary database becomes the primary. After the outage is resolved, you need to bring the original primary back online with minimal data loss and without creating a new secondary. What should you do?

A.Automatically re-establish geo-replication by waiting for the outage resolution.
B.Restore the original primary from a backup and then re-establish geo-replication.
C.Delete the original primary database and create a new secondary from the new primary.
D.Re-establish geo-replication from the new primary to the original primary.
AnswerD

Correct: The original primary becomes a secondary and can be re-synchronized.

Why this answer

After a failover using active geo-replication, the original primary becomes a secondary database in the replication relationship. To bring it back online with minimal data loss, you re-establish geo-replication from the new primary to the original primary. This allows the original primary to synchronize and become a readable secondary again.

Option D is correct. Option A is incorrect because geo-replication is not automatically re-established; you must manually configure it. Option B is incorrect because restoring from backup would cause data loss and require creating a new secondary.

Option C is incorrect because deleting the original primary is unnecessary; it can be reused as a secondary.

Full explanation →
789
MCQmedium

You are responsible for an Azure SQL Database that stores customer personally identifiable information (PII). You need to ensure that users with the 'CustomerService' role can see only the last four digits of Social Security numbers. Which feature should you use?

A.Row-Level Security
B.Column-level security with DENY permission on the column
C.Dynamic Data Masking with a partial mask function
D.Always Encrypted with deterministic encryption
AnswerC

Masks the column to show only last four digits.

Why this answer

Dynamic Data Masking (DDM) with a partial mask function is the correct choice because it allows you to obfuscate sensitive data at query time without altering the underlying storage. For a Social Security number, you can apply a mask like 'partial(0, "XXX-XX-", 4)' to expose only the last four digits to users in the 'CustomerService' role, while the full value remains intact in the database.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking with column-level permissions or Always Encrypted, mistakenly thinking that hiding the column entirely or encrypting it meets the requirement of showing a partial value, when in fact DDM is the only feature designed to expose a masked version of data without altering the underlying storage or access permissions.

How to eliminate wrong answers

Option A is wrong because Row-Level Security (RLS) restricts which rows a user can read based on a predicate function, but it does not mask or redact individual column values; it would either show the entire row or hide it entirely. Option B is wrong because column-level security with DENY permission prevents users from seeing the column at all, which is too restrictive—the requirement is to see a masked version, not to hide the column completely. Option D is wrong because Always Encrypted with deterministic encryption encrypts data at rest and in transit, but it does not provide a way to expose only a portion of the data; clients with access to the column encryption key see the full plaintext, and those without see only ciphertext, not a partial mask.

Full explanation →
790
MCQmedium

You are analyzing the exhibit KQL query that queries Azure Diagnostics logs for Query Store runtime statistics. The query is intended to show average CPU time per hour for each database. However, the result shows no data for the last 24 hours, although Query Store is enabled on all databases. What is the most likely reason?

A.Query Store is not enabled on the databases.
B.The diagnostic settings are not configured to send QueryStoreRuntimeStatistics to Log Analytics.
C.The time range in the query is too narrow and excludes the last 24 hours.
D.The query syntax is incorrect and needs to use 'project' before 'summarize'.
AnswerB

Without that category, no data is collected.

Why this answer

Query Store runtime statistics are sent to Azure Diagnostics only if diagnostic settings are configured to collect that category. Option A is wrong because Query Store is enabled. Option C is wrong because the query is correct.

Option D is wrong because the KQL query syntax does not require 'project' before 'summarize'; placement does not affect results.

Full explanation →
791
MCQmedium

You are troubleshooting a connection issue from Azure SQL Database to Azure Storage using a managed identity. The above credential was created. What is missing from this configuration?

A.The credential is missing the SECRET parameter.
B.The credential should be a server-level credential.
C.The credential lacks a valid identity.
D.The credential needs to include the storage account name.
AnswerA

Correct: For managed identity, SECRET must be specified, even if empty string.

Why this answer

The credential is missing the SECRET parameter because when using a managed identity to access Azure Storage from Azure SQL Database, the credential must specify the identity's client ID or object ID as the SECRET. Without this, the credential cannot authenticate to the storage account, causing the connection to fail. The SECRET parameter is required to map the managed identity to the credential for Azure AD authentication.

Exam trap

The trap here is that candidates often assume the SECRET parameter is optional or only needed for passwords, but in managed identity scenarios, it is mandatory to specify the identity's client ID as the SECRET to enable token-based authentication.

How to eliminate wrong answers

Option B is wrong because server-level credentials are used for SQL Server authentication or external data sources like Azure Blob Storage, but the issue here is not about scope; the credential type is correct for a database-scoped credential, and the missing SECRET is the problem. Option C is wrong because the credential does have a valid identity (the managed identity is specified via the IDENTITY parameter), but the SECRET parameter is missing to complete the authentication. Option D is wrong because the storage account name is not required in the credential; it is specified in the external data source definition, not in the credential itself.

Full explanation →
792
MCQeasy

Your organization requires that all Azure SQL Database backups be retained for 10 years to meet compliance requirements. Which backup retention policy should you configure?

A.Configure point-in-time restore (PITR) retention to 10 years.
B.Enable automatic tuning to optimize backups.
C.Configure long-term retention (LTR) policy.
D.Enable geo-redundant backup storage (GRS).
AnswerC

LTR allows up to 10 years of backup retention.

Why this answer

Long-term retention (LTR) in Azure SQL Database allows you to retain full database backups for up to 10 years, which meets the compliance requirement for 10-year backup retention. LTR policies are configured separately from point-in-time restore (PITR) and store backups in isolated containers for extended periods, ensuring regulatory compliance.

Exam trap

The trap here is that candidates often confuse point-in-time restore (PITR) retention with long-term retention (LTR), assuming PITR can be extended to years, but Azure SQL Database caps PITR at 35 days, making LTR the only option for multi-year compliance.

How to eliminate wrong answers

Option A is wrong because point-in-time restore (PITR) retention is limited to a maximum of 35 days for Azure SQL Database, not 10 years, so it cannot satisfy the 10-year compliance requirement. Option B is wrong because automatic tuning optimizes query performance and index management, not backup retention, and has no impact on backup duration or compliance. Option D is wrong because geo-redundant backup storage (GRS) provides geographic redundancy for backups but does not extend the retention period beyond the default PITR or LTR limits; it is a storage option, not a retention policy.

Full explanation →
793
MCQhard

You are a database administrator for a global e-commerce company. The company uses Azure SQL Database for its product catalog, which is a mission-critical OLTP workload. The database is currently deployed in the West US region using the Business Critical service tier with zone redundancy enabled. The database size is 200 GB and grows at 10 GB per month. The company has a disaster recovery requirement: in the event of a regional outage, the database must be failed over to a secondary region with an RPO of less than 5 seconds and an RTO of less than 1 minute. Additionally, the secondary database must be readable to support read-heavy reporting workloads. The solution must minimize additional compute costs. You need to recommend a configuration. Which option should you choose?

A.Configure active geo-replication to a secondary database in a paired region using Business Critical tier with a readable secondary.
B.Create a failover group within the same region using Business Critical tier with a readable secondary.
C.Add a second zone-redundant replica in the same region and configure a failover group.
D.Upgrade to Hyperscale tier with zone redundancy and configure a named replica in a secondary region.
AnswerA

Active geo-replication provides low RPO and a readable secondary, meeting all requirements.

Why this answer

Active geo-replication to a secondary database in a paired region using Business Critical tier with a readable secondary meets all requirements: it provides cross-region DR with an RPO of less than 5 seconds (synchronous replication within the primary region, asynchronous to secondary), RTO of less than 1 minute (failover is fast), and the secondary is readable for reporting. Zone redundancy within the primary region does not provide cross-region DR, so options B and C are incorrect. Hyperscale tier (option D) is more expensive and does not inherently provide cross-region DR with the required RPO; named replicas add cost.

Full explanation →
794
MCQmedium

You are deploying an Azure SQL Managed Instance for a financial application. The compliance requirements dictate that all data at rest must be encrypted using TDE with a customer-managed key stored in Azure Key Vault. Additionally, the key must be accessible only from the managed instance's virtual network. How should you configure the Key Vault firewall and managed instance?

A.Enable the Key Vault firewall, select 'Allow trusted Microsoft services', and configure the managed instance with a system-assigned managed identity
B.Disable the Key Vault firewall and assign the managed instance a service principal
C.Enable the Key Vault firewall and add the managed instance's public IP address to the firewall rules
D.Disable the Key Vault firewall and use a user-assigned managed identity for the managed instance
AnswerA

This limits access to trusted services and uses managed identity.

Why this answer

The correct configuration. Enabling the Key Vault firewall and selecting 'Allow trusted Microsoft services' ensures that only trusted Azure services, including Azure SQL Managed Instance with a system-assigned managed identity, can access the key. This satisfies the requirement that the key be accessible only from the managed instance's virtual network, as the managed instance is a trusted service within that network.

Option B is incorrect because disabling the Key Vault firewall would allow public network access to the vault, violating the network restriction. Option C is incorrect because Azure SQL Managed Instance does not have a public IP address; it is deployed inside a virtual network and cannot be added as a public IP firewall rule. Option D is incorrect because disabling the firewall again allows public access, and using a user-assigned managed identity is unnecessary; a system-assigned managed identity is the recommended approach when using 'Allow trusted Microsoft services'.

Full explanation →
795
MCQhard

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database is 2 TB in size and has multiple logins, jobs, and SQL Server Agent alerts. You need to choose a migration method that minimizes downtime and preserves all server-level objects. Which approach should you recommend?

A.Export to BACPAC and import to Managed Instance
B.Use Azure Database Migration Service with online migration
C.Use transactional replication
D.Back up to URL and restore to Managed Instance
AnswerB

DMS online migration minimizes downtime and can migrate server-level objects.

Why this answer

Azure Database Migration Service (DMS) with online migration is the correct choice because it supports minimal downtime by continuously synchronizing changes from the source SQL Server to the target Azure SQL Managed Instance until cutover. It also automatically migrates server-level objects such as logins, jobs, and SQL Server Agent alerts, which are critical for preserving the full environment. This method is designed for large databases (2 TB) and provides a managed, resilient migration pipeline.

Exam trap

The trap here is that candidates often assume backup/restore (Option D) is sufficient for a full migration, overlooking that server-level objects like logins and jobs are not included in the backup file and must be migrated separately, leading to incomplete environment preservation.

How to eliminate wrong answers

Option A is wrong because exporting to BACPAC and importing to Managed Instance does not preserve server-level objects like logins, jobs, or SQL Server Agent alerts, and it requires significant downtime as the database must be offline during export/import. Option C is wrong because transactional replication can replicate data changes but does not migrate server-level objects (logins, jobs, alerts) and requires complex setup for schema and object synchronization, making it unsuitable for a full migration with minimal downtime. Option D is wrong because backing up to URL and restoring to Managed Instance preserves the database but does not migrate server-level objects such as logins, jobs, or SQL Server Agent alerts; additionally, the restore process incurs downtime as the database must be offline during the restore.

Full explanation →
796
MCQmedium

You are configuring security for an Azure SQL Database that will be accessed by multiple applications. You need to implement a solution that allows applications to connect using their own managed identities without storing credentials in connection strings. What should you configure?

A.Enable Microsoft Entra ID authentication and assign managed identities to the applications.
B.Use Always Encrypted with column master key in Azure Key Vault.
C.Configure firewall rules to allow application IP addresses.
D.Enable SQL Server authentication and create a login for each application.
AnswerA

Managed identities provide passwordless authentication.

Why this answer

Microsoft Entra ID authentication allows Azure SQL Database to trust tokens issued by Entra ID for managed identities. By assigning a managed identity to each application, the application can acquire an access token from Azure Managed Identity endpoints and present it to the database without ever storing credentials in connection strings. This eliminates the need for passwords or connection string secrets.

Exam trap

The trap here is that candidates often confuse authentication mechanisms (like Always Encrypted or firewall rules) with identity-based access control, mistakenly thinking they eliminate credential storage when they only address encryption or network filtering.

How to eliminate wrong answers

Option B is wrong because Always Encrypted with a column master key in Azure Key Vault protects data at rest and in transit by encrypting specific columns, but it does not address authentication or eliminate the need for credentials in connection strings. Option C is wrong because configuring firewall rules to allow application IP addresses controls network access but still requires a username and password (or other authentication) in the connection string; it does not remove credential storage. Option D is wrong because enabling SQL Server authentication and creating a login for each application still requires storing a username and password in the connection string, which violates the requirement to avoid credential storage.

Full explanation →
797
Multi-Selecthard

You are configuring security for an Azure SQL Managed Instance. The instance will host a critical application that requires always encrypted with secure enclaves. Which TWO actions must you take to support this feature? (Choose two.)

Select 2 answers
A.Select the Intel Software Guard Extensions (Intel SGX) enclave type.
B.Configure the column master key to be stored in Azure Key Vault.
C.Configure a column master key that is enclave-enabled.
D.Enable the enclave attestation policy on the managed instance.
E.Enable Virtualization-Based Security (VBS) enclave type.
AnswersA, C

Intel SGX is the required enclave type for Always Encrypted with secure enclaves on SQL Managed Instance.

Why this answer

Always Encrypted with secure enclaves on Azure SQL Managed Instance requires the Intel Software Guard Extensions (Intel SGX) enclave type. Intel SGX is the only supported enclave technology for this feature on managed instances, providing a trusted execution environment that protects sensitive data in memory during cryptographic operations.

Exam trap

The trap here is that candidates often confuse the requirement for an enclave-enabled column master key (option C) with the need to store the key in Azure Key Vault (option B), but the key location is not a prerequisite for enclave support.

Full explanation →
798
MCQeasy

You are configuring performance monitoring for Azure SQL Managed Instance. You need to collect and analyze query performance data with minimal overhead. Which solution should you use?

A.Query Store
B.Azure Monitor metrics
C.Extended Events
D.SQL Server Profiler
AnswerA

Query Store provides built-in, low-overhead query performance monitoring and analysis.

Why this answer

Query Store is built-in and designed for low overhead query performance monitoring. Option B is wrong because Azure Monitor metrics provide resource-level metrics, not query-level details. Option C is wrong because Extended Events can have higher overhead and is more for custom event collection.

Option D is wrong because SQL Server Profiler is deprecated and has high overhead.

Full explanation →
799
MCQmedium

You are configuring security for an Azure SQL Database that will be accessed by multiple applications. Each application uses a separate service principal managed in Microsoft Entra ID. You need to ensure that each service principal has the minimum required permissions to access only its own set of tables. What should you implement?

A.Create a contained database user for each service principal and grant the db_owner role.
B.Create a contained database user for each service principal and grant SELECT, INSERT, UPDATE, DELETE on specific tables.
C.Create a server-level login for each service principal and assign db_datareader role in the database.
D.Configure a server-level firewall rule for each service principal IP address.
AnswerB

Correct: Contained database users allow granular permissions per table, meeting the minimum required access.

Why this answer

It creates a contained database user for each service principal (mapped to the Microsoft Entra ID identity) and grants only the specific table-level permissions (SELECT, INSERT, UPDATE, DELETE) required for that application. This follows the principle of least privilege by avoiding broad database roles and ensuring each service principal can only access its own set of tables.

Exam trap

The trap here is that candidates often confuse server-level logins with contained database users for Microsoft Entra ID principals, or mistakenly think that broad roles like db_datareader satisfy the 'minimum required permissions' requirement when the question explicitly demands table-level scoping.

How to eliminate wrong answers

Option A is wrong because granting the db_owner role provides full administrative control over the entire database, far exceeding the minimum required permissions and violating least privilege. Option C is wrong because server-level logins are not supported for Microsoft Entra ID service principals; you must use contained database users, and db_datareader grants read access to all tables, not just specific ones. Option D is wrong because firewall rules control network access at the server level, not permissions to specific tables, and service principals authenticate via Microsoft Entra ID tokens, not IP addresses.

Full explanation →
800
MCQmedium

Your company uses Azure SQL Database Hyperscale tier for a large data warehouse. You need to implement disaster recovery with the ability to perform point-in-time restore in the secondary region. What is the best approach?

A.Configure geo-replication to a secondary Hyperscale database in the secondary region.
B.Configure auto-failover groups between regions.
C.Use geo-restore of automated backups.
D.Create a failover group with manual failover.
AnswerA

Geo-replication provides a readable secondary and allows point-in-time restore in the secondary region.

Why this answer

Azure SQL Database Hyperscale tier supports geo-replication (currently in preview), which creates a readable secondary database in a different region. This secondary database also supports point-in-time restore within the secondary region, meeting the requirement for disaster recovery with point-in-time restore capability. Option B is incorrect because auto-failover groups are not supported for Hyperscale databases.

Option C is incorrect because geo-restore of automated backups has a higher recovery point objective (RPO) compared to geo-replication. Option D is incorrect as failover groups are not supported for Hyperscale.

Full explanation →
801
Multi-Selectmedium

Which THREE factors should you consider when choosing between Azure SQL Database single database and Azure SQL Managed Instance for a new application?

Select 3 answers
A.Requirement for elastic pool scalability.
B.Need for cross-database queries and transactions.
C.Need for automated backups with point-in-time restore.
D.Requirement for SQL Server Agent with job scheduling.
E.Need for a fixed virtual network with private IP addresses.
AnswersB, D, E

Managed Instance supports cross-database queries; single database does not.

Why this answer

Azure SQL Database single database does not support cross-database queries and transactions, whereas Azure SQL Managed Instance provides full SQL Server instance-level features, including cross-database queries and transactions via linked servers or within the same instance. This makes Managed Instance the appropriate choice when the application requires querying or updating multiple databases in a single transaction.

Exam trap

The trap here is that candidates often assume automated backups with point-in-time restore are exclusive to one service, but both Azure SQL Database and SQL Managed Instance provide this feature, making it a distractor that tests your knowledge of shared vs. differentiated capabilities.

Full explanation →
802
MCQeasy

You have an Azure SQL Managed Instance that is the primary in a failover group. You need to perform a manual failover for planned maintenance with zero data loss. Which PowerShell cmdlet should you use?

A.Set-AzSqlDatabaseFailoverGroup
B.Switch-AzSqlDatabaseFailoverGroup -AllowDataLoss:$false
C.Start-AzSqlDatabaseFailoverGroup
D.Invoke-AzSqlDatabaseFailoverGroup
AnswerB

This performs a planned failover with zero data loss.

Why this answer

Switch-AzSqlDatabaseFailoverGroup with -AllowDataLoss:$false performs a manual failover with zero data loss for planned maintenance. Option A is wrong because Set-AzSqlDatabaseFailoverGroup modifies configuration, not failover. Option C is wrong because Start-AzSqlDatabaseFailoverGroup does not exist.

Option D is wrong because Invoke-AzSqlDatabaseFailoverGroup does not exist.

Full explanation →
803
MCQmedium

You are monitoring an Azure SQL Database that hosts a financial application. You notice that the average DTU consumption is 20%, but occasionally spikes to 95% for 5-minute intervals. Users report slow response times during these spikes. You need to ensure consistent performance without over-provisioning resources. What should you do?

A.Migrate the database to the Hyperscale service tier.
B.Scale the database to a higher service tier to absorb the spikes.
C.Enable Query Store and use the Regressed Queries feature to find slow queries.
D.Identify and optimize the queries running during the spike periods, possibly rescheduling a heavy ETL job.
AnswerD

Rescheduling or tuning the ETL job can eliminate the spikes, ensuring consistent performance.

Why this answer

The issue is not a consistent resource shortage but periodic spikes caused by specific queries, likely from a heavy ETL job. By identifying and optimizing those queries or rescheduling the job, you can eliminate the spikes without permanently scaling up resources, which would waste cost and capacity. This aligns with the DP-300 focus on performance tuning and resource optimization rather than blind scaling.

Exam trap

The trap here is that candidates assume spikes always require scaling up (Option B) or migrating to a higher tier (Option A), but the DP-300 exam emphasizes that optimization and scheduling are often more cost-effective than over-provisioning.

How to eliminate wrong answers

Option A is wrong because Hyperscale is designed for large, highly scalable databases with fast recovery and read scale-out, not for handling occasional DTU spikes; it would over-provision and increase cost unnecessarily. Option B is wrong because scaling to a higher service tier permanently increases DTU capacity to absorb spikes that occur only 5 minutes at a time, leading to over-provisioning and wasted cost for the 80% of time when DTU is at 20%. Option C is wrong because Query Store and Regressed Queries help identify performance regressions over time, but the question already indicates the spikes are caused by known periodic heavy workloads (e.g., ETL), so the immediate action is to optimize or reschedule those queries, not just monitor them.

Full explanation →
804
MCQeasy

You are designing a disaster recovery plan for an Azure SQL Database that supports an e-commerce application. The application requires an RPO of 15 seconds and an RTO of 1 hour during a regional outage. Which solution should you recommend?

A.Create a failover group with automatic failover policy
B.Deploy active geo-replication to a secondary region and automate failover
C.Use long-term backup retention and restore in another region
D.Configure zone-redundant availability for the database
AnswerB

Active geo-replication provides low RPO and manual failover that can be automated to meet RTO.

Why this answer

Deploy active geo-replication to a secondary region and automate failover. Active geo-replication provides an asynchronous secondary replica in a different region with an RPO of up to 5 seconds, meeting the 15-second requirement. By automating failover (e.g., via Azure PowerShell, CLI, or Azure Automation), the RTO can be minimized to under 1 hour.

Option A, creating a failover group with automatic failover, also uses geo-replication but the automatic failover policy may introduce additional detection time, making it harder to guarantee an RTO of exactly 1 hour. Additionally, failover groups are designed for managing multiple databases, adding unnecessary complexity for a single database. Option C, long-term backup retention and restore, has an RPO of hours, not seconds.

Option D, zone-redundant availability, protects against zone failures within a single region, not a regional outage.

Full explanation →
805
Multi-Selecteasy

Which TWO methods can be used to automatically restart an Azure SQL Database after a maintenance operation?

Select 2 answers
A.Azure CLI az sql db pause/resume
B.Azure Portal stop/start
C.SQL Server Management Studio (SSMS) restart command
D.Elastic Database Job with ALTER DATABASE SET ONLINE
E.Azure Automation runbook with Start-AzSqlDatabase
AnswersA, E

Correct. The Azure CLI pause/resume commands can be scripted in automated workflows.

Why this answer

The Azure CLI `az sql db pause/resume` can be scripted and automated, effectively restarting the database. Option E is also correct because an Azure Automation runbook can use the `Start-AzSqlDatabase` cmdlet (and corresponding `Stop-AzSqlDatabase`) to stop and start the database, which achieves an automated restart. Options B, C, and D are incorrect: B is manual via the portal, C is manual via SSMS, and D's `ALTER DATABASE SET ONLINE` does not restart the database.

Exam trap

Candidates may mistakenly think only the Azure CLI pause/resume is automated, but Azure Automation runbooks with PowerShell cmdlets like Start-AzSqlDatabase also provide an automated restart method.

Full explanation →
806
Multi-Selecthard

You are designing a secure Azure SQL Database environment for a financial services application. You need to meet the following requirements: - All network traffic to the database must be private and not traverse the public internet. - Only specific Azure resources in a virtual network should be able to connect. - The solution must minimize administrative overhead. Which TWO configurations should you implement? (Choose two.)

Select 2 answers
A.Deploy Azure Bastion in the virtual network for secure connectivity.
B.Add a firewall rule to allow all Azure services to access the server.
C.Enable service endpoints for Azure SQL Database on the virtual network subnets.
D.Configure the server firewall to deny all public network access.
E.Create a private endpoint for Azure SQL Database in the virtual network.
AnswersD, E

Denying public access ensures that only connections through the private endpoint are allowed.

Why this answer

Configuring the server firewall to deny all public network access ensures that no traffic can reach the database over the public internet, which is required for private connectivity. Option E is correct because creating a private endpoint for Azure SQL Database in the virtual network assigns a private IP address from the VNet, allowing traffic to stay entirely within the Microsoft backbone network and never traverse the public internet. Together, these two configurations enforce private-only access and meet the requirement for specific Azure resources in the VNet to connect.

Exam trap

The trap here is that candidates often confuse service endpoints with private endpoints, assuming both provide the same level of private connectivity, but service endpoints still use the public endpoint of the database and do not prevent traffic from potentially being routed over the internet if the Microsoft backbone is not used end-to-end.

Full explanation →
807
MCQhard

You have an Azure SQL Database that uses automatic tuning. You notice that a forced plan regression is causing performance degradation. You need to revert to the previous plan and prevent the automatic tuning from forcing the same plan again. What should you do?

A.Reindex the tables involved in the query.
B.Create a plan guide for the previous plan and then disable the automatic tuning recommendation for that query.
C.Disable automatic tuning for the database.
D.Run DBCC FREEPROCCACHE to clear the plan cache.
AnswerB

A plan guide forces the previous plan, and disabling the recommendation prevents automatic tuning from reverting it.

Why this answer

Creating a plan guide for the previous plan manually enforces that plan for the query, and disabling the automatic tuning recommendation for that specific query prevents the automatic tuning engine from forcing the same plan again. Option A is incorrect because reindexing may not resolve the regression and does not stop automatic tuning from forcing the bad plan. Option C is incorrect because disabling automatic tuning globally affects all queries unnecessarily.

Option D is incorrect because clearing the plan cache is only temporary and automatic tuning would likely force the same plan again.

Full explanation →
808
MCQhard

Refer to the exhibit. An Azure SQL Database administrator sees the error in the SQL Server error log. The database is in the General Purpose tier with 100 GB of storage. Which action should the DBA take to resolve the issue?

A.Shrink the database
B.Scale up to a higher service objective (e.g., from GP to BC)
C.Increase the log file size
D.Increase the max size of the database
AnswerD

Increasing max size adds more space to the primary filegroup.

Why this answer

The error indicates the database has reached its maximum size limit of 100 GB. In Azure SQL Database General Purpose tier, the max size is set at the database level, not the log file level. Increasing the max size of the database (Option D) directly resolves the 'could not allocate space' error by allowing more data to be stored, without requiring a tier change or manual log file manipulation.

Exam trap

The trap here is that candidates confuse a full transaction log with a full database, leading them to choose 'Increase the log file size' (Option C) when the error is actually about the overall database storage limit.

How to eliminate wrong answers

Option A is wrong because shrinking the database is a temporary fix that does not address the root cause of reaching the storage limit; it may also cause fragmentation and performance degradation. Option B is wrong because scaling up to a higher service objective (e.g., Business Critical) is unnecessary and more expensive; the issue is storage capacity, not performance or I/O limits, and the General Purpose tier supports up to 4 TB with appropriate max size settings. Option C is wrong because the error is about overall database size, not specifically the transaction log; increasing log file size would not help if the data file has reached its max size, and Azure SQL Database manages log file growth automatically within the allocated storage.

Full explanation →
809
MCQhard

You manage an Azure SQL Database that uses elastic jobs to automate index maintenance. The job fails intermittently with the error 'Cannot open server 'myserver' requested by the login.' The job agent uses a database-scoped credential. What is the most likely cause?

A.The credential's user does not have ALTER ANY DATABASE permission.
B.The target database's firewall is blocking the job agent's IP address.
C.The job agent itself needs a server-level login.
D.The credential is not mapped to a server login in the master database.
AnswerD

The credential must be mapped to a server login that can access the target server.

Why this answer

The error 'Cannot open server...' indicates that the login used by the credential is not recognized at the server level. For elastic jobs with database-scoped credentials, the credential must be associated with a login in the master database of the target server. If the credential is not mapped to a server login, the login cannot access the server, causing this error.

Option D correctly identifies this. Option A is incorrect because ALTER ANY DATABASE permission is not required for server access. Option B is incorrect because firewall blocks cause a different error (e.g., connection timeout).

Option C is incorrect because the job agent itself does not require a server-level login; it uses the credential.

Full explanation →
810
MCQhard

You are a database administrator for a gaming company that uses Azure SQL Database with multiple databases in a single logical server. The databases experience varying load patterns. You need to automate the process of scaling up or down the DTU/ vCore purchasing model based on performance metrics. Specifically, if average DTU consumption exceeds 80% for 10 minutes, you want to scale up the database tier. If consumption drops below 20% for 30 minutes, you want to scale down. The automation must use Azure native services and should not require custom scripting. What should you do?

A.Create an Azure Automation runbook with PowerShell that queries performance metrics and scales the database accordingly.
B.Set up an Azure Logic App with a trigger on Azure Monitor metrics (e.g., when average DTU > 80% for 10 minutes) and an action to update the database tier via Azure Resource Manager connector.
C.Use Elastic Database Jobs to run T-SQL that alters the database service tier based on scheduled times.
D.Create an Azure Function app that uses the SQL Server metrics API to scale the database.
AnswerB

No custom scripting needed, uses native connectors.

Why this answer

Azure Logic Apps natively integrate with Azure Monitor metrics (e.g., average DTU consumption) and can trigger an action to update the database tier via the Azure Resource Manager connector. This provides a no-code / low-code solution that meets the requirement of not requiring custom scripting. Option A is incorrect because Azure Automation runbooks require PowerShell scripting.

Option C is incorrect because Elastic Database Jobs cannot directly change service tiers based on metrics; they are for executing T-SQL scripts. Option D is incorrect because Azure Functions require custom code.

Full explanation →
811
MCQhard

Your Azure SQL Database is experiencing high DTU consumption. You need to identify the top resource-consuming queries. What should you do?

A.Use the Query Store reports in the Azure portal
B.Use SQL Server Profiler
C.Create an Extended Events session to capture query events
D.Query sys.dm_exec_query_stats
AnswerA

Built-in, persistent, and easy to use.

Why this answer

Query Store in Azure SQL Database provides built-in reports to identify top resource-consuming queries by CPU, IO, and duration, making it the easiest and most direct method. Option B (SQL Server Profiler) is not supported in Azure SQL Database. Option C (Extended Events) is more complex and not the simplest approach for this task.

Option D (sys.dm_exec_query_stats) can be used but lacks persistent historical data and is more effort than using Query Store reports.

Full explanation →
812
MCQhard

You are designing a data platform for a global SaaS company. The application requires a relational database that can handle up to 50 TB of data and supports high-frequency inserts. The database must be able to scale compute independently from storage and provide fast restores (within minutes) for large databases. Which Azure SQL offering should you choose?

A.Azure SQL Database Business Critical tier
B.Azure SQL Database Standard tier
C.Azure SQL Database Hyperscale tier
D.Azure SQL Managed Instance Business Critical
AnswerC

Supports up to 100 TB, independent compute scaling, and fast restores.

Why this answer

The Hyperscale tier of Azure SQL Database is designed for workloads up to 100 TB, decouples compute from storage (allowing independent scaling), and uses a log-based architecture with page servers to enable fast restores (typically within minutes, regardless of database size). This directly matches the requirements for 50 TB data, high-frequency inserts, independent compute/storage scaling, and rapid restore times.

Exam trap

The trap here is that candidates often confuse the Business Critical tier's high availability and performance features with the ability to handle large data volumes and fast restores, overlooking the strict 4 TB size limit and lack of compute/storage decoupling.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database Business Critical tier has a maximum size of 4 TB, far below the required 50 TB, and does not support independent compute/storage scaling. Option B is wrong because Azure SQL Database Standard tier is limited to 1 TB and is designed for lower performance workloads, not high-frequency inserts or fast restores. Option D is wrong because Azure SQL Managed Instance Business Critical has a maximum size of 16 TB, insufficient for 50 TB, and while it offers some scaling, it does not provide the same level of compute/storage decoupling or sub-minute restore capabilities as Hyperscale.

Full explanation →
813
Multi-Selecteasy

Which TWO database properties can be configured to optimize performance for a read-heavy workload in Azure SQL Database?

Select 2 answers
A.Query Store
B.Data masking
C.Long-term retention policy
D.Automatic tuning (CREATE INDEX)
E.Read scale-out
AnswersA, E

Monitors query performance.

Why this answer

For a read-heavy workload in Azure SQL Database, enabling Query Store (option A) helps monitor query performance, identify regressions, and optimize query plans. Read scale-out (option E) offloads read-only queries to a readable secondary replica, reducing load on the primary and improving performance. Options B (Data masking) is a security feature, C (Long-term retention policy) is for backup retention, and D (Automatic tuning - CREATE INDEX) creates indexes but is not specifically for read-heavy workloads; it can help but is not a primary property for read offload.

Full explanation →
814
MCQmedium

Refer to the exhibit. An administrator configures an auto-failover group for Azure SQL Database. The primary server is in East US and the secondary in West US. What will happen if the primary region becomes unavailable?

A.The administrator must manually trigger the failover.
B.The databases will become read-only until the primary recovers.
C.The secondary will immediately take over as the primary.
D.The failover will occur automatically after 60 minutes.
AnswerD

The grace period is 60 minutes.

Why this answer

With automatic failover policy and grace period of 60 minutes, the system will wait 60 minutes before automatically failing over to the secondary server. During that time, read-write endpoints will not be available.

Full explanation →
815
Drag & Dropmedium

Drag and drop the steps to troubleshoot a high CPU usage issue in Azure SQL Database in the correct order.

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

Steps
Order
1Step 1
2Step 2
3Step 3
4Step 4

Why this order

Start by identifying high CPU queries, analyze plans, check for missing indexes, implement fixes, then monitor.

Full explanation →
816
Multi-Selecteasy

Which TWO of the following are native options to automate index maintenance on Azure SQL Database? (Select exactly two.)

Select 2 answers
A.Create Elastic Database Jobs that run index maintenance T-SQL scripts.
B.Use Azure Automation PowerShell runbooks to invoke index rebuilds.
C.Enable automatic tuning with 'CREATE INDEX' and 'DROP INDEX' options.
D.Schedule SQL Agent jobs with ALTER INDEX statements.
E.Use Azure Data Factory to copy data and rebuild indexes.
AnswersA, C

Elastic Jobs can automate T-SQL index maintenance across databases.

Why this answer

Options A and C are correct. Elastic Database Jobs (Option A) allow running custom T-SQL scripts for index maintenance across databases. Automatic tuning (Option C) can automatically create and drop indexes based on workload patterns, which is a form of index maintenance.

Option B is incorrect because Azure Automation PowerShell runbooks are not a native option for Azure SQL Database index maintenance; they would require additional setup and do not directly execute T-SQL. Option D is incorrect because SQL Agent jobs are not available in Azure SQL Database (they are available in SQL Server on-premises or on Azure VMs). Option E is incorrect because Azure Data Factory is primarily for data movement and orchestration, not for direct index maintenance.

Full explanation →
817
Multi-Selecthard

Which THREE components are required to configure a failover group for Azure SQL Database? (Choose three.)

Select 3 answers
A.Failover group listener.
B.At least one database on the primary server.
C.Secondary server.
D.Primary server.
E.An elastic pool.
AnswersB, C, D

The failover group must contain at least one database.

Why this answer

Options B, C, and D are correct. A failover group requires a primary server, a secondary server, and at least one database on the primary server. Option A is incorrect because the failover group listener is automatically created and does not need to be separately configured.

Option E is incorrect because an elastic pool is optional; failover groups can contain individual databases or elastic pools.

Full explanation →
818
MCQeasy

You need to audit all schema changes in an Azure SQL Database and store the audit logs in a storage account for long-term retention. What should you enable?

A.Azure SQL Auditing with storage account destination.
B.Advanced Threat Protection with email alerts.
C.Query Store with 'Data Flush Interval' set to 1 minute.
D.SQL Vulnerability Assessment with recurring scans.
AnswerA

Auditing logs DDL operations and can write to storage.

Why this answer

Azure SQL Auditing with a storage account destination is the correct choice because it tracks database events, including schema changes (DDL operations), and writes audit logs to Azure Blob Storage for long-term retention. This meets the requirement to audit all schema changes and store logs durably, as storage accounts provide configurable retention policies.

Exam trap

The trap here is that candidates confuse Azure SQL Auditing with other security features like Advanced Threat Protection or Vulnerability Assessment, assuming they all capture schema changes, but only Auditing provides granular event logging with a storage destination for long-term retention.

How to eliminate wrong answers

Option B is wrong because Advanced Threat Protection (ATP) detects anomalous activities (e.g., SQL injection, brute-force attacks) and sends email alerts, but it does not log schema changes or provide long-term audit storage. Option C is wrong because Query Store captures query performance data (execution plans, runtime statistics) with a configurable data flush interval, not schema change events or audit logs. Option D is wrong because SQL Vulnerability Assessment performs periodic scans to identify security misconfigurations and vulnerabilities, but it does not audit schema changes or store logs in a storage account.

Full explanation →
819
Multi-Selecthard

You are troubleshooting a performance issue on an Azure SQL Database. The database is experiencing high PAGELATCH_EX waits. Which TWO measures can help reduce these waits?

Select 2 answers
A.Use a hash distribution or round-robin distribution in a table design
B.Increase MAXDOP for the queries
C.Partition the table to distribute inserts
D.Use OPTIMIZE_FOR_SEQUENTIAL_KEY index option
E.Enable snapshot isolation level
AnswersC, D

Correct. Partitioning a table spreads insert activity across multiple pages, reducing contention on the last page and decreasing PAGELATCH_EX waits.

Why this answer

PAGELATCH_EX waits are caused by contention on the last page of an index, often due to sequential inserts. Two effective measures in Azure SQL Database are partitioning the table (option C) to spread inserts across multiple pages and using OPTIMIZE_FOR_SEQUENTIAL_KEY index option (option D) to reduce page latch contention. Option A is incorrect because hash or round-robin distribution is a feature of Azure Synapse dedicated SQL pools, not Azure SQL Database.

Option B is incorrect because increasing MAXDOP can increase parallelism and potentially worsen latch contention. Option E is incorrect because snapshot isolation does not reduce page latch waits.

Full explanation →
820
MCQeasy

Refer to the exhibit. You are reviewing the configuration of a failover group for an Azure SQL Managed Instance. Which statement is correct about this configuration?

A.The read-write endpoint will not automatically redirect connections after failover.
B.The read-only endpoint will not automatically redirect connections after failover.
C.The failover group will automatically fail over with data loss if the primary is unavailable for more than 60 minutes.
D.The read-only endpoint is used for read-write connections after failover.
AnswerB

The readOnlyEndpoint failoverPolicy is Disabled.

Why this answer

The read-only endpoint's failover policy is set to Disabled, meaning it will not automatically redirect connections after a failover. Option A is incorrect because the read-write endpoint has its failover policy set to Automatic, so it will redirect connections automatically. Option C is incorrect because the grace period for data loss is set to 1 hour (3600 seconds), but the statement says 'more than 60 minutes' which is not exactly accurate; also the primary focus is on the endpoint behavior.

Option D is incorrect because the read-only endpoint is used for read-only connections, not read-write.

Full explanation →
821
MCQmedium

You are a database administrator for a multinational corporation that uses Azure SQL Managed Instance to host multiple databases for different business units. The security policy requires that all connections to the managed instance must use encrypted connections (TLS 1.2 or higher). Additionally, the company wants to minimize the attack surface by restricting network access. You need to configure the managed instance to enforce encrypted connections and block all public internet traffic. What should you do?

A.Set the 'Minimal TLS Version' property to 1.2 and set 'Public data endpoint' to 'Disabled'
B.Enable a private endpoint and set the 'Minimal TLS Version' property to 1.0
C.Disable the public endpoint and enable a service endpoint for the virtual network
D.Configure a server-level firewall rule to allow only specific IP addresses and set the 'Minimal TLS Version' property to 1.2
AnswerA

This enforces TLS 1.2 or higher and blocks public internet traffic.

Why this answer

Setting the 'Minimal TLS Version' property to 1.2 enforces that all connections use TLS 1.2 or higher, meeting the encryption requirement. Disabling the 'Public data endpoint' blocks all public internet traffic, ensuring that only traffic from within the virtual network can reach the managed instance. This combination directly satisfies both security policy goals without relying on additional components like private endpoints or firewall rules.

Exam trap

The trap here is that candidates often confuse disabling the public endpoint with using a private endpoint or firewall rules, failing to realize that both the TLS version enforcement and public endpoint disablement are required to fully meet the security policy.

How to eliminate wrong answers

Option B is wrong because setting 'Minimal TLS Version' to 1.0 allows connections using TLS 1.0, which is not compliant with the requirement for TLS 1.2 or higher, and enabling a private endpoint alone does not block public internet traffic unless the public endpoint is also disabled. Option C is wrong because disabling the public endpoint and enabling a service endpoint does not enforce TLS 1.2; service endpoints only secure traffic to Azure services within the virtual network but do not control the TLS version used. Option D is wrong because configuring a server-level firewall rule to allow only specific IP addresses still leaves the public endpoint enabled, which exposes the managed instance to the internet and does not minimize the attack surface as required.

Full explanation →
822
MCQmedium

You have an Azure SQL Managed Instance that is experiencing performance degradation. You suspect a query is causing excessive blocking. You need to identify the blocking chain and the resource holding the lock. Which DMV should you query?

A.sys.dm_exec_requests
B.sys.dm_tran_locks and sys.dm_os_waiting_tasks
C.sys.dm_exec_query_stats
D.sys.dm_tran_active_snapshot_database_transactions
AnswerB

These DMVs together provide lock information and waiting tasks to identify blocking.

Why this answer

To identify the blocking chain and the specific resource holding the lock, you need to combine lock metadata with wait information. sys.dm_tran_locks shows current locks and their resource types (e.g., RID, KEY, PAGE, OBJECT), while sys.dm_os_waiting_tasks reveals which sessions are waiting on those locks and the blocking session ID. Together, these DMVs allow you to trace the blocking chain from the blocked session back to the blocker and pinpoint the exact resource causing contention.

Exam trap

The trap here is that candidates often pick sys.dm_exec_requests (Option A) because it shows wait_type and blocking_session_id, but it lacks the granular lock resource information (e.g., RID, KEY) that sys.dm_tran_locks provides, which is essential for identifying the exact resource holding the lock.

How to eliminate wrong answers

Option A is wrong because sys.dm_exec_requests shows currently executing requests and their wait types, but it does not provide detailed lock resource information (e.g., which specific row or key is locked) needed to identify the exact resource holding the lock. Option C is wrong because sys.dm_exec_query_stats aggregates query performance metrics (CPU, I/O, duration) over time and does not contain real-time lock or blocking chain data. Option D is wrong because sys.dm_tran_active_snapshot_database_transactions is specific to snapshot isolation level transactions and tracks version store usage, not blocking chains or lock resources.

Full explanation →
823
Multi-Selecthard

Your organization has an Azure SQL Managed Instance that hosts a critical database. You need to configure disaster recovery with automatic failover to a secondary region. The solution must support readable secondary replicas for read-only workloads. Which TWO features should you implement?

Select 2 answers
A.Auto-failover groups with a secondary in the same region.
B.Readable secondary replicas within the failover group.
C.Failover group with a secondary managed instance in a different region.
D.Active geo-replication between managed instances.
E.Log shipping to a secondary managed instance.
AnswersB, C

Failover groups for SQL Managed Instance include a readable secondary replica by default.

Why this answer

The correct features are failover groups and readable secondary replicas. A failover group with a secondary managed instance in a different region provides automatic failover for disaster recovery. Within the failover group, you can configure the secondary replica to be readable, allowing read-only workloads to use it.

Option C (failover group with secondary in different region) enables the automatic failover, and option B (readable secondary replicas within the failover group) is a configuration option that meets the requirement for readable secondaries. Together, they satisfy the disaster recovery and readability requirements.

Full explanation →
824
MCQhard

Your company uses Azure SQL Database with active geo-replication for disaster recovery. During a drill, you need to verify that the secondary database is ready for failover and that data synchronization is current. Which query should you run on the secondary database?

A.SELECT * FROM sys.dm_continuous_copy_status;
B.SELECT * FROM sys.dm_geo_replication_link_status;
C.SELECT * FROM sys.dm_database_copies;
D.SELECT * FROM sys.dm_replication_status;
AnswerB

This DMV provides replication lag and status.

Why this answer

Sys.dm_geo_replication_link_status provides replication lag and the last replicated transaction for geo-replicated databases. Option A is wrong because sys.dm_continuous_copy_status is deprecated and no longer used for geo-replication monitoring. Option C is wrong because sys.dm_database_copies is for copy-only operations, not ongoing replication.

Option D is wrong because sys.dm_replication_status is for transactional replication, not geo-replication.

Full explanation →
825
MCQhard

Your company plans to migrate multiple on-premises SQL Server databases to Azure SQL Managed Instance. You need to select the appropriate migration method that minimizes downtime and supports automated schema and data validation. The source databases are running SQL Server 2019 Enterprise Edition. Which tool should you recommend?

A.Use the Log Replay Service to continuously replicate transaction logs.
B.Set up transactional replication from on-premises to Azure SQL Managed Instance.
C.Use Azure Database Migration Service with online mode.
D.Export the databases as BACPAC files and import them into Azure SQL Managed Instance.
AnswerC

DMS online mode provides minimal downtime and automated validation.

Why this answer

Azure Database Migration Service (DMS) with online mode is the correct choice because it supports minimal downtime migrations by continuously replicating ongoing changes from the source SQL Server to Azure SQL Managed Instance, and it provides built-in schema and data validation to ensure consistency before cutover. This aligns with the requirement for automated validation and minimal downtime.

Exam trap

The trap here is that candidates often confuse the Log Replay Service (which is a valid tool for SQL Managed Instance migrations) with a fully automated validation solution, but it lacks the built-in schema and data validation that DMS online mode provides.

How to eliminate wrong answers

Option A is wrong because the Log Replay Service is used for migrations to Azure SQL Managed Instance only when using the backup/restore method with full and log backups, but it does not provide automated schema and data validation; it is a manual process. Option B is wrong because transactional replication can replicate data changes but requires significant configuration overhead, does not natively support automated schema validation, and is not the recommended primary migration tool for minimizing downtime in this scenario. Option D is wrong because exporting databases as BACPAC files is an offline method that causes significant downtime and does not support automated schema and data validation during the migration process.

Full explanation →
Page 10

Page 11 of 13

Page 12

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 →