Courseiva

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

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

Take a mock examExam hub
Page 9

Page 10 of 13

Page 11
676
MCQeasy

You need to run a complex T-SQL script on an Azure SQL Database every hour. The script performs data transformations that must be logged for auditing. Which native Azure service should you use?

A.SQL Agent job on the Azure SQL Database.
B.Elastic Database Jobs with a T-SQL job step.
C.Azure Data Factory with a stored procedure activity.
D.Azure Automation Account with a PowerShell runbook that uses Invoke-SqlCmd.
AnswerB

Elastic Jobs are purpose-built for scheduled T-SQL execution on Azure SQL DB with logging.

Why this answer

Elastic Database Jobs (option B) is the correct native Azure service for running complex T-SQL scripts on Azure SQL Database on a schedule with logging. It is designed for scheduled execution of T-SQL scripts across one or more databases and provides job logging and history. Option A is incorrect because SQL Agent is not available in Azure SQL Database single databases (only in SQL Server on VMs or Managed Instances).

Option C is incorrect because Azure Data Factory is primarily an ETL service, not optimized for simple scheduled script execution, though it can be used, it is not the native simple solution. Option D is incorrect because Azure Automation Runbooks execute PowerShell, not T-SQL directly; while Invoke-SqlCmd can be used, it is not the native T-SQL scheduling service.

Full explanation →
677
Multi-Selecteasy

Which TWO actions should you take to secure Azure SQL Database against SQL injection attacks?

Select 2 answers
A.Enable Transparent Data Encryption
B.Enable auditing for all database operations
C.Configure firewall rules to allow only trusted IP addresses
D.Use parameterized queries in application code
E.Use stored procedures with parameters
AnswersD, E

Prevents SQL injection by separating code from data.

Why this answer

Parameterized queries (Option D) and stored procedures with parameters (Option E) are both effective defenses against SQL injection because they separate SQL code from user input. When using parameterized queries, the database engine treats input as data only, never as executable code, which prevents attackers from injecting malicious SQL statements. This is a fundamental secure coding practice recommended by Microsoft for Azure SQL Database.

Exam trap

The trap here is that candidates often confuse security features like encryption (TDE) or network controls (firewall rules) with application-layer defenses, mistakenly thinking they prevent SQL injection when they only address different threat vectors.

Full explanation →
678
MCQeasy

You need to automate the deployment of database schema changes across multiple Azure SQL Databases in a development environment. Which Azure service is designed for this purpose?

A.Azure DevOps using database projects and release pipelines.
B.SQL Agent jobs on each database.
C.Azure Automation Account with PowerShell runbooks.
D.Azure Data Factory with a copy activity.
AnswerA

Azure DevOps provides CI/CD capabilities specifically for database deployments.

Why this answer

Azure DevOps with database projects and release pipelines provides a CI/CD solution specifically designed for deploying schema changes to Azure SQL Databases. Option B is incorrect because SQL Agent jobs are used for scheduled administrative tasks within a single database, not for multi-database schema deployment automation. Option C is incorrect because Azure Automation Account with PowerShell runbooks is suitable for general automation tasks but lacks native CI/CD capabilities for database schema updates.

Option D is incorrect because Azure Data Factory with copy activity focuses on data movement and transformation, not on executing schema changes.

Full explanation →
679
MCQeasy

You have an Azure SQL Database that is used by a reporting application. Reports run slowly during the day. You notice that the database is at the S1 tier. You need to improve report performance without changing application code. What should you do?

A.Rebuild fragmented indexes.
B.Scale up the database to a higher service tier.
C.Implement table partitioning.
D.Enable read scale-out to offload reports.
AnswerB

Higher DTU allocation directly improves throughput.

Why this answer

Scaling up to a higher service tier (e.g., S2 or S3) provides more DTUs, which directly addresses the resource bottleneck and improves report performance without changing application code. Option A is incorrect because while rebuilding fragmented indexes can help query performance, it may not be sufficient if the underlying issue is insufficient resources. Option C is incorrect because table partitioning can improve manageability and partition elimination but does not guarantee performance improvement for reports and often requires query changes.

Option D is incorrect because read scale-out is only available on the Premium and Business Critical service tiers, not on the S1 tier.

Full explanation →
680
MCQmedium

You are a database administrator for a healthcare company. You have an Azure SQL Database that stores patient records. The database is currently accessible from the public internet via firewall rules. You need to implement a secure environment that meets the following requirements: - All traffic to the database must be private and not traverse the internet. - The database must be accessible from an Azure Virtual Machine in a specific VNet. - The solution must minimize management overhead and cost. - You need to ensure that the database can be failed over to a secondary region in case of an outage. What should you do?

A.Restrict firewall rules to only the VM's public IP and enable active geo-replication.
B.Configure a point-to-site VPN from the VM to the database and set up geo-replication.
C.Create a private endpoint in the VNet, disable public network access, and configure a failover group with a private endpoint in the secondary region.
D.Create a VNet service endpoint and a failover group. Keep public access enabled for failover.
AnswerC

This ensures private connectivity and disaster recovery.

Why this answer

It meets all requirements: a private endpoint ensures all traffic to the database stays within the Microsoft backbone network and never traverses the internet. Disabling public network access enforces that no public traffic can reach the database, fulfilling the private traffic requirement. Configuring a failover group with a private endpoint in the secondary region provides geo-replication and automatic failover while maintaining private connectivity, with minimal management overhead and no additional cost for VPNs.

Option A is incorrect because restricting the firewall to the VM's public IP still allows traffic over the public internet, violating the private traffic requirement. Option B is incorrect because a point-to-site VPN adds complexity and cost, and still uses a VPN connection that may traverse the internet, and does not provide the same seamless private connectivity as a private endpoint. Option D is incorrect because a VNet service endpoint does not make traffic private; it still uses the public endpoint of the database, and keeping public access enabled fails the requirement to disable public internet access.

Full explanation →
681
MCQhard

The database 'mydb' is experiencing performance issues during peak hours. Based on the exhibit, what is the most likely cause?

A.Zone redundancy is disabled, causing failover delays.
B.The service tier S3 is not sufficient for the workload.
C.Read scale is disabled, increasing load on primary.
D.The database is auto-pausing frequently due to autoPauseDelay.
AnswerB

S3 has limited DTU which can cause performance issues.

Why this answer

The exhibit indicates the database is using the S3 service tier, which provides limited DTUs (100 DTU). During peak hours, this low tier is insufficient for the workload, leading to resource contention and performance degradation. Option A is incorrect because zone redundancy is related to high availability and failover, not peak-hour performance.

Option C is incorrect because read scale is used for offloading read-only workloads, not for mitigating primary performance issues. Option D is incorrect because auto-pause delay (3600 seconds) affects serverless databases in idle periods, not active peak-hour usage.

Full explanation →
682
MCQhard

Refer to the exhibit. Which action should you take to improve performance?

A.Enable automatic tuning for the query.
B.Increase the database's DTU/DTU level.
C.Force plan 2 using the query store.
D.Drop and recreate the query's indexes.
AnswerC

Plan 2 has better metrics and should be forced to improve performance.

Why this answer

The exhibit shows two plans for the same query. Plan 2 has lower CPU, reads, and duration. Plan 1 is being forced, causing poor performance.

The best action is to force plan 2.

Full explanation →
683
MCQmedium

You manage an Azure SQL Database that supports a critical financial application. The database is in the General Purpose tier and uses active geo-replication for disaster recovery. You need to automate the process of failing over to the secondary region in case of a regional outage, but only after confirming that the primary is unreachable for more than 5 minutes. Additionally, you need to send an alert to the operations team when the failover occurs. The solution should use Azure services and minimize manual steps. What should you implement?

A.Use Azure Logic Apps with a timer trigger to check the database status every minute and initiate failover if unreachable.
B.Schedule a SQL Agent job on the secondary to run a script that checks connectivity and fails over.
C.Configure Azure Site Recovery for the SQL database.
D.Configure an Azure Monitor metric alert on the 'Deadlock count' metric (or custom metric) with a threshold of 0 for 5 minutes, then use an action group to trigger an Azure Automation runbook that runs the failover PowerShell cmdlet.
AnswerA

Correct. Logic Apps can be configured with a timer trigger to periodically check the primary database's connectivity. If unreachable for more than 5 minutes (e.g., after 5 consecutive failed checks), it can trigger a failover and send alerts. This uses Azure-native services and minimizes manual steps.

Why this answer

Azure Logic Apps can automate the failover process by periodically checking the primary database's connectivity. If it remains unreachable for more than 5 minutes, it can initiate a geo-replication failover and send alerts to the operations team. This meets all requirements using Azure services with minimal manual steps.

Option B is incorrect because SQL Agent is not available in Azure SQL Database as a PaaS service. Option C is incorrect because Azure Site Recovery is designed for virtual machines, not for Azure SQL Database geo-replication. Option D is incorrect because the 'Deadlock count' metric is unrelated to database connectivity; it measures deadlocks, not reachability, and the described threshold does not correctly detect unreachability.

Full explanation →
684
Multi-Selectmedium

You are a database administrator for a company that uses Azure SQL Managed Instance. You need to automate the process of patching the operating system and SQL Server engine for all managed instances in a specific region. The automation must minimize downtime and ensure high availability. Which two actions should you include in your automation strategy?

Select 2 answers
A.Use Azure Policy to automatically scale up the instance before patching.
B.Create an Azure Automation runbook to manually apply OS and SQL patches.
C.Configure a maintenance window using Azure Portal or PowerShell.
D.Deploy a failover group to another region and enable read-scale replicas.
E.Enable Azure Update Manager for the managed instances.
AnswersC, D

Correct. Configuring a maintenance window allows you to control when patching occurs, minimizing impact.

Why this answer

Correct options are C and D. Configure a maintenance window (C) to control when patching occurs, minimizing disruption. Deploy a failover group to another region with read-scale replicas (D) to redirect traffic during patching, ensuring high availability.

Option A is incorrect because scaling up does not relate to patching; Azure Policy is for governance. Option B is incorrect because Microsoft manages patching for Azure SQL Managed Instance; manual patching is not supported. Option E is incorrect because Azure Update Manager is for IaaS VMs, not for Azure SQL Managed Instance.

Exam trap

Candidates may think that Azure Automation runbooks are necessary for patching automation, but patching is managed by Microsoft. They may also incorrectly believe that enabling Azure Update Manager applies to Azure SQL Managed Instance.

Full explanation →
685
MCQeasy

Your company is adopting Microsoft Defender XDR for enhanced security. You need to enable Microsoft Defender for SQL for your Azure SQL Database to receive security alerts and vulnerability assessments. What is the first step you must take?

A.Configure Microsoft Intune to manage access.
B.Run a vulnerability assessment scan from the Azure portal.
C.Configure Microsoft Purview to scan the database.
D.Enable Microsoft Defender for SQL on the Azure SQL logical server.
AnswerD

Enabling Defender for SQL activates security alerts and vulnerability assessments.

Why this answer

Microsoft Defender for SQL must be enabled at the logical server level before any security alerts or vulnerability assessments can be applied to the Azure SQL Database. This server-level enablement activates the Defender for SQL features (including vulnerability assessment and threat detection) for all databases under that server, making it the prerequisite step.

Exam trap

The trap here is that candidates may think they need to first run a vulnerability assessment scan (Option B) to see alerts, but the scan itself requires Defender for SQL to be enabled first, making the enablement the true first step.

How to eliminate wrong answers

Option A is wrong because Microsoft Intune is a mobile device management (MDM) and mobile application management (MAM) service, not related to enabling security features for Azure SQL Database. Option B is wrong because running a vulnerability assessment scan requires Defender for SQL to already be enabled; attempting to run it first would fail or be unavailable. Option C is wrong because Microsoft Purview is a data governance and catalog service used for data discovery and classification, not for enabling SQL security alerts or vulnerability assessments.

Full explanation →
686
MCQmedium

You have an Azure SQL Database that must be automatically restarted every night to clear the procedure cache. You plan to use elastic jobs in Azure SQL Database. What should you create first?

A.A job database
B.An elastic job agent
C.A target group
D.A job credential
AnswerB

The job agent is required before creating jobs, targets, and credentials.

Why this answer

An elastic job agent is the top-level resource that orchestrates jobs. Before creating any jobs, you must create the elastic job agent. The job database (A) stores job definitions but is created as part of agent setup or after.

Target groups (C) and job credentials (D) are created after the agent exists.

Full explanation →
687
MCQhard

You are the database administrator for a company that uses Azure SQL Managed Instance. The security team has detected unusual query activity from a specific client IP address. You need to immediately block all connections from that IP address while maintaining connectivity for all other users. Which solution should you implement?

A.Create a private endpoint for the managed instance and disable public network access.
B.Add a server-level firewall rule at the logical server level to deny the IP address.
C.Add a firewall rule at the managed instance level that denies the specific IP address.
D.Modify the network security group (NSG) associated with the managed instance's subnet to deny the IP address.
AnswerC

Instance-level firewall rules can allow or deny specific IP addresses.

Why this answer

Azure SQL Managed Instance supports instance-level firewall rules that can explicitly deny a specific client IP address while allowing all other traffic. Unlike Azure SQL Database, which uses server-level firewall rules, Managed Instance firewall rules are configured directly on the instance and can include both allow and deny entries, enabling granular access control without affecting other users.

Exam trap

The trap here is that candidates confuse Azure SQL Database's server-level firewall rules with Managed Instance's instance-level firewall rules, or incorrectly assume that NSG modifications are the correct way to block a single client IP without understanding that NSGs affect the entire subnet.

How to eliminate wrong answers

Option A is wrong because creating a private endpoint and disabling public network access would block all external connections, not just the specific IP, and is a permanent architectural change rather than an immediate targeted block. Option B is wrong because Azure SQL Managed Instance does not have a logical server-level firewall; server-level firewall rules apply only to Azure SQL Database and Azure Synapse, not to Managed Instance. Option D is wrong because modifying the NSG associated with the Managed Instance's subnet would block traffic at the network layer for all instances in that subnet, potentially disrupting other services, and Managed Instance does not expose direct NSG control for individual IP filtering without impacting the entire subnet.

Full explanation →
688
MCQeasy

You are reviewing the audit configuration of an Azure SQL Database using the above JSON from Azure Resource Manager. Based on the exhibit, which of the following is true?

A.Audit logs are written using the secondary storage key
B.Auditing is currently disabled
C.Audit logs are retained for 30 days
D.Audit logs are sent to Azure Monitor
AnswerD

isAzureMonitorTargetEnabled is true.

Why this answer

The JSON shows 'isAzureMonitorTargetEnabled' is true, indicating audit logs are sent to Azure Monitor. Option B is incorrect because 'state' is 'Enabled', meaning auditing is enabled. Option A is incorrect because 'isStorageSecondaryKeyInUse' is false.

Option C is incorrect because 'retentionDays' is 90, not 30.

Full explanation →
689
MCQmedium

A developer reports that they cannot connect to an Azure SQL Database using Azure AD authentication. The developer is a member of an Azure AD group that has been granted db_datareader role in the database. The connection string uses Active Directory Password authentication. What is the most likely issue?

A.The Azure AD group has not been created as a database user.
B.The server firewall is blocking the connection.
C.The developer's IP address is not allowed.
D.Multi-factor authentication is required but not configured.
AnswerA

Azure AD groups must be mapped to a database user using CREATE USER [group] FROM EXTERNAL PROVIDER.

Why this answer

The most likely issue is that the Azure AD group has not been created as a database user in the SQL Database. Even though the group has been granted the db_datareader role at the Azure AD level, Azure SQL Database requires that the group be explicitly mapped to a database user via the CREATE USER [group name] FROM EXTERNAL PROVIDER statement. Without this mapping, the group's membership does not translate into database-level permissions, and the developer's authentication will fail.

Exam trap

The trap here is that candidates often confuse Azure AD group membership with database user creation, assuming that granting a role to an Azure AD group automatically grants database access, when in fact a separate database user must be created for the group.

How to eliminate wrong answers

Option B is wrong because the server firewall blocking the connection would result in a network-level error (e.g., cannot connect to server), not an authentication failure. Option C is wrong because the developer's IP address not being allowed would also cause a network-level error, not an Azure AD authentication issue. Option D is wrong because Multi-Factor Authentication (MFA) is not required for Active Directory Password authentication; MFA is only enforced when using Active Directory Interactive authentication or when conditional access policies are applied, and the connection string here uses Password authentication, which does not support MFA.

Full explanation →
690
Multi-Selectmedium

Which TWO options are valid services for scheduling automated tasks for Azure SQL Database? (Choose two.)

Select 2 answers
A.SQL Server Agent
B.Azure Functions
C.Azure Automation
D.Azure Logic Apps
E.Elastic Database Jobs
AnswersC, E

Azure Automation can run PowerShell or Python runbooks on a schedule to perform database tasks.

Why this answer

Azure Automation is correct because it provides a cloud-based automation and configuration service that supports scheduling PowerShell or Python runbooks to execute tasks against Azure SQL Database, such as running T-SQL scripts or performing maintenance operations. It integrates natively with Azure SQL via the Az module and can be triggered on a recurring schedule, making it a valid service for scheduling automated tasks.

Exam trap

The trap here is that candidates often confuse SQL Server Agent (available in SQL Server and Azure SQL Managed Instance) with Elastic Database Jobs (the equivalent for Azure SQL Database), or they assume Azure Functions or Logic Apps are the correct scheduling services when the question specifically asks for services designed for scheduling automated tasks for Azure SQL Database.

Full explanation →
691
MCQeasy

You have an Azure SQL Database that uses the General Purpose service tier. The database is critical and you need to protect against a regional outage with an RPO of 1 hour and an RTO of 12 hours. What is the most cost-effective solution?

A.Use geo-restore with the default backup retention.
B.Enable active geo-replication.
C.Configure auto-failover group with a secondary in a different region.
D.Deploy a zone-redundant configuration.
AnswerA

Geo-restore is included with backups and meets the RPO/RTO requirements.

Why this answer

Geo-restore uses automated backups stored in geo-redundant storage (RA-GRS) at no additional cost beyond backup storage. With default backup retention (7 days), geo-restore typically achieves an RPO of 1 hour and an RTO of 12 hours, meeting the requirements cost-effectively. Option B (active geo-replication) and Option C (auto-failover group) both incur extra compute and storage costs for the secondary replica, making them more expensive.

Option D (zone-redundant) protects against zonal failures within a region, not regional outages.

Exam trap

Candidates often choose active geo-replication or auto-failover groups because they provide faster RPO/RTO, but they overlook that geo-restore is free and meets the specified RPO/RTO at no additional cost.

Full explanation →
692
MCQmedium

You are managing an Azure SQL Database that runs a critical business application. The database experiences a predictable surge in read-only queries every night at 2:00 AM. You need to configure automatic scaling to handle this surge without manual intervention. What should you do?

A.Create a read replica and redirect read queries to it during the surge.
B.Manually scale up the database service tier before 2:00 AM each day.
C.Move the database to an Elastic Database Pool and rely on its built-in autoscaling.
D.Configure autoscale settings on the Azure SQL Database using Azure Automation runbooks triggered by a metric alert.
AnswerD

This enables automatic scaling based on load.

Why this answer

Azure SQL Database does not natively support automatic scaling based on load. To achieve this, you must use Azure Automation runbooks triggered by a metric alert (e.g., DTU or CPU percentage) to programmatically scale the database's service tier up or down. This approach allows you to handle the predictable nightly surge without manual intervention, as the runbook can be scheduled or triggered by a threshold alert.

Exam trap

The trap here is that candidates often assume Azure SQL Database has built-in autoscaling like Azure SQL Database serverless (which only pauses/resumes, not scales), or they confuse elastic pool autoscaling with per-database scaling, leading them to select option C incorrectly.

How to eliminate wrong answers

Option A is wrong because creating a read replica and redirecting read queries does not scale the primary database; it only offloads read traffic, and the replica itself is not automatically scaled to handle the surge. Option B is wrong because manually scaling the database before 2:00 AM each day requires ongoing manual intervention, which contradicts the requirement to configure automatic scaling without manual intervention. Option C is wrong because Elastic Database Pools provide resource sharing and autoscaling at the pool level (adding/removing eDTUs), but they do not automatically scale individual databases within the pool; the pool's autoscaling is based on aggregate pool metrics, not per-database surge patterns.

Full explanation →
693
MCQeasy

You need to automate the process of scaling an Azure SQL Database to a higher service tier when CPU usage exceeds 80% for 5 consecutive minutes. Which Azure service is best suited for this automation?

A.Azure Logic App triggered by an Azure Monitor metric alert, calling the Azure SQL Database REST API to update the tier.
B.Elastic Database Job that monitors sys.dm_db_resource_stats and executes ALTER DATABASE.
C.Azure Automation Runbook triggered by an Azure Monitor alert, using PowerShell to scale the database.
D.Azure Function triggered by an Azure Monitor alert, using the Azure SDK to scale.
AnswerA

Logic Apps have native integration with Azure Monitor alerts and REST APIs.

Why this answer

Azure Logic Apps can be easily triggered by an Azure Monitor metric alert and can call the Azure SQL Database REST API to update the service tier. This provides a simple, low-code solution for automating scaling based on CPU thresholds. Option B is incorrect because Elastic Database Jobs are designed for executing T-SQL scripts across multiple databases, not for scaling operations.

Option C is incorrect: although Azure Automation Runbooks can be triggered by alerts and use PowerShell, they require more setup and overhead compared to Logic Apps. Option D is also less optimal: Azure Functions can work but Logic Apps offer a more straightforward integration with Azure Monitor alerts and REST APIs for this specific use case.

Full explanation →
694
MCQmedium

After running the above Azure CLI command, you notice that the secondary database is not readable. What is the most likely reason?

A.Geo-replication does not support readable secondaries by default
B.The secondary database capacity is set to 2 DTUs, which is too low
C.The '--secondary-type' parameter is set to 'Geo' instead of 'Readable'
D.The hardware generation is Gen5, which does not support readable secondaries
AnswerC

Setting secondary-type to 'Geo' disables read access; use 'Readable' to allow reads.

Why this answer

The '--secondary-type' parameter controls the secondary's readability. Setting it to 'Geo' creates a geo-replicated secondary that is not readable. To enable readable secondaries, you must specify '--secondary-type Readable' (or 'Named').

Option A is incorrect because Azure SQL Database geo-replication does support readable secondaries when configured correctly. Option B is incorrect: while a low DTU might affect performance, it does not prevent readability; the secondary can still be read even with 2 DTUs. Option D is incorrect because Gen5 hardware generation supports readable secondaries.

Full explanation →
695
MCQeasy

Your company plans to migrate several on-premises SQL Server databases to Azure SQL Database. The databases are used by different applications, each with varying performance requirements. You need to choose a deployment option that provides the most cost-effective solution while allowing for independent scaling of each database. Which option should you choose?

A.Create an Azure SQL Database elastic pool and add databases to the pool.
B.Install SQL Server on Azure Virtual Machines and host all databases on a single instance.
C.Migrate all databases to a single Azure SQL Managed Instance.
D.Deploy each database as a single Azure SQL Database with its own DTU-based tier.
AnswerA

Elastic pools provide cost-effective resource sharing with independent scaling.

Why this answer

Azure SQL Database elastic pools allow multiple databases to share a fixed set of resources (eDTUs or vCores), enabling cost savings by pooling underutilized databases while providing the ability to independently scale each database's resource consumption within the pool. This meets the requirement for independent scaling and cost-effectiveness, as opposed to single databases that each require their own dedicated resources.

Exam trap

The trap here is that candidates often choose single databases (Option D) thinking they offer the most straightforward independent scaling, but they overlook the cost inefficiency compared to elastic pools when databases have variable or low utilization.

How to eliminate wrong answers

Option B is wrong because hosting all databases on a single SQL Server on Azure Virtual Machines does not allow independent scaling of each database; all databases share the same instance resources, and scaling requires resizing the VM. Option C is wrong because a single Azure SQL Managed Instance also shares resources across all databases in that instance, preventing independent scaling per database and often incurring higher costs for mixed workloads. Option D is wrong because deploying each database as a single Azure SQL Database with its own DTU-based tier is less cost-effective than an elastic pool when databases have varying and often low utilization, as each database pays for its peak capacity independently.

Full explanation →
696
MCQmedium

You are planning a disaster recovery strategy for an Azure SQL Database that supports a critical financial application. The database is deployed in the Brazil South region. Because of data sovereignty requirements, the secondary database must also be located in Brazil, but in a different availability zone. The application requires an RPO of 10 seconds and an RTO of 30 minutes. The database size is 2 TB and you need to minimize egress costs. What should you do?

A.Deploy the database using the Business Critical service tier with zone redundancy and a failover group.
B.Deploy the database using the Hyperscale service tier with zone-redundant configuration.
C.Use the General Purpose service tier with a zone-redundant backup configuration.
D.Configure active geo-replication to a secondary database in a different Brazilian region (e.g., Brazil Southeast).
AnswerB

Hyperscale supports zone redundancy, provides fast failover, and meets the RPO/RTO with asynchronous replication across zones.

Why this answer

Hyperscale service tier with zone-redundant configuration meets the requirements: it supports zone redundancy within the same region (satisfying data sovereignty), provides fast failover with RPO of 10 seconds and RTO under 30 minutes, and is suitable for large databases (2 TB). Option A is incorrect because Business Critical with zone redundancy and a failover group is designed for high availability within a region, but the mention of a failover group implies geo-replication, which is unnecessary and may increase costs; also, Business Critical has a maximum size of 4 TB but Hyperscale is more cost-effective for large databases. Option C is incorrect because General Purpose does not support zone redundancy and cannot meet the RPO/RTO.

Option D is incorrect because geo-replication to another region violates data sovereignty and incurs higher egress costs.

Full explanation →
697
MCQeasy

You are configuring Azure SQL Database firewall rules. You need to allow a range of IP addresses (192.168.1.0 to 192.168.1.255) to connect to the database. Which firewall rule should you create?

A.Start IP: 192.168.0.0, End IP: 192.168.2.255
B.Start IP: 192.168.1.0, End IP: 192.168.1.255
C.Start IP: 192.168.1.255, End IP: 192.168.1.0
D.Start IP: 192.168.1.0, End IP: 192.168.1.0
AnswerB

Correct: This covers the entire /24 subnet.

Why this answer

Azure SQL Database firewall rules require a contiguous range of IP addresses defined by a start and end IP. The range 192.168.1.0 to 192.168.1.255 exactly covers the specified /24 subnet, allowing all hosts in that block to connect. This is the standard method for permitting a subnet in Azure SQL firewall configuration.

Exam trap

The trap here is that candidates may confuse Azure SQL firewall rules with on-premises firewall or network ACLs, where reversed ranges or single-IP entries might be accepted, but Azure SQL strictly requires a valid start ≤ end IP and does not support CIDR notation, leading to errors if you try to use a subnet mask or reversed order.

How to eliminate wrong answers

Option A is wrong because it defines a range from 192.168.0.0 to 192.168.2.255, which is a /22 subnet (192.168.0.0/22) and includes addresses outside the required range (e.g., 192.168.0.1 and 192.168.2.1), granting excessive access. Option C is wrong because it reverses the start and end IPs (start 192.168.1.255, end 192.168.1.0), which is invalid; Azure SQL firewall rules require the start IP to be less than or equal to the end IP, and such a rule would be rejected or behave incorrectly. Option D is wrong because it sets both start and end IP to 192.168.1.0, which only allows a single host (192.168.1.0) rather than the full /24 range, thus blocking all other addresses in the subnet.

Full explanation →
698
MCQeasy

You need to design a disaster recovery plan for an Azure SQL Managed Instance. The recovery time objective (RTO) is 1 hour, and the recovery point objective (RPO) is 15 minutes. What is the most cost-effective solution?

A.Configure active geo-replication between instances in different regions.
B.Set up log shipping to a secondary instance.
C.Create an auto-failover group with a secondary instance in a paired region.
D.Use point-in-time restore to a secondary region.
AnswerC

Auto-failover groups provide low RTO and RPO for Managed Instance.

Why this answer

Auto-failover groups (Option C) are the most cost-effective solution for Azure SQL Managed Instance with an RTO of 1 hour and RPO of 15 minutes. They provide an RTO of ~1 hour and an RPO of 5 seconds to 5 minutes, meeting the requirements at low cost. Option A is incorrect because active geo-replication is not supported for SQL Managed Instance.

Option B is incorrect because log shipping is not natively supported for Managed Instance. Option D is incorrect because point-in-time restore has a longer RTO and RPO, typically not meeting the 15-minute RPO requirement.

Full explanation →
699
MCQeasy

You are designing a new Azure SQL Database deployment for a mission-critical application. The application requires 99.99% availability SLA, automated failover within 30 seconds, and read-scale capability for reporting. Which tier and configuration should you choose?

A.Business Critical with zone redundancy and a failover group
B.General Purpose serverless with geo-replication
C.General Purpose with active geo-replication and read-scale
D.Hyperscale with zone redundancy enabled
AnswerA

Business Critical provides zone-redundant high availability, automatic failover, and readable secondaries for reporting.

Why this answer

Business Critical with zone redundancy and a failover group meets all requirements: 99.99% SLA (supported by zone-redundant Business Critical), automated failover within 30 seconds (failover groups provide automatic, fast failover across regions), and read-scale capability (Business Critical includes a readable secondary replica).

Exam trap

The trap here is that candidates confuse 'read-scale' with 'geo-replication read-only secondary' — only Business Critical and Hyperscale offer a built-in readable secondary replica, while General Purpose requires a separate geo-replicated secondary for read-only access, which does not meet the 30-second failover requirement.

How to eliminate wrong answers

Option B is wrong because General Purpose serverless does not support zone redundancy or read-scale replicas, and geo-replication alone does not guarantee 30-second failover or 99.99% SLA. Option C is wrong because General Purpose does not offer read-scale replicas (only Business Critical and Hyperscale do), and active geo-replication failover is manual or requires custom automation, not automated within 30 seconds. Option D is wrong because Hyperscale with zone redundancy does not include a readable secondary replica for read-scale (it uses page servers and a log service, not a dedicated readable secondary), and its failover behavior is not designed for sub-30-second automated failover across regions.

Full explanation →
700
MCQhard

A company has an Azure SQL Managed Instance that is experiencing high CPU usage. The DBA observes that a specific query is causing high compile time due to parameter sniffing. The query is executed frequently with varying parameter values. Which approach should the DBA use to reduce CPU usage without changing the T-SQL code?

A.Create a plan guide to force a specific plan
B.Use optimize for ad hoc workloads
C.Add query store and force the last good plan
D.Enable forced parameterization for the database
AnswerD

Forced parameterization treats literals as parameters, reducing recompilations.

Why this answer

Forced parameterization (Option D) converts literal values in the query to parameters, allowing SQL Server to reuse cached execution plans across different parameter values. This reduces CPU usage by eliminating frequent recompilations caused by parameter sniffing, without requiring any changes to the T-SQL code itself.

Exam trap

The trap here is that candidates often confuse 'forced parameterization' with 'plan guides' or 'Query Store forcing,' not realizing that forced parameterization directly reduces compile time by enabling plan reuse, while the other options only manage plan selection without addressing the root cause of frequent recompilations.

How to eliminate wrong answers

Option A is wrong because creating a plan guide to force a specific plan would lock in one plan for all parameter values, which could lead to suboptimal performance for other values and does not address the root cause of high compile time. Option B is wrong because 'optimize for ad hoc workloads' only defers the cost of storing single-use plans, but does not prevent recompilation due to parameter sniffing; it does not reduce compile time for frequently executed queries. Option C is wrong because adding Query Store and forcing the last good plan still relies on a single plan for all executions, which may not be optimal for varying parameter values and does not reduce compile time; it only ensures plan stability after a regression.

Full explanation →
701
MCQmedium

You are managing an Azure SQL Managed Instance that hosts a critical database. You need to automate the export of daily backups to a storage account for long-term retention. The solution must minimize administrative overhead and support point-in-time restore within the retention period. What should you use?

A.Configure a long-term retention (LTR) backup policy for the Managed Instance.
B.Configure an Azure Backup policy for the Managed Instance.
C.Schedule a BACPAC export of the database to the storage account.
D.Create a SQL Agent job to perform backups using BACKUP DATABASE TO URL.
AnswerA

LTR policies automate backup exports to storage accounts and support point-in-time restore.

Why this answer

Azure SQL Managed Instance supports configuring a long-term retention (LTR) backup policy that automatically stores backups in a geo-redundant storage account for up to 10 years, minimizing administrative overhead while supporting point-in-time restore within the retention period. Option B is incorrect because Azure Backup is designed for IaaS VMs and other workloads, not for SQL Managed Instance native backups. Option C is incorrect because scheduling BACPAC exports is not a backup solution — it does not support point-in-time restore and requires manual setup.

Option D is incorrect because using SQL Agent jobs with BACKUP DATABASE TO URL adds complexity and does not integrate with Azure's automated backup management.

Full explanation →
702
MCQmedium

You manage an Azure SQL Database that has automatic tuning enabled. You receive an alert that the database is experiencing plan regression. The automatic tuning has forced a plan, but performance is still poor. What should you do first?

A.Disable automatic tuning and create a plan guide.
B.Review the Query Store to identify the root cause of regression.
C.Manually revert to the previous plan using Query Store.
D.Scale up the database to reduce resource pressure.
AnswerB

Query Store can show plan history and regression details, helping analyze why the forced plan is not optimal.

Why this answer

Query Store can show plan history and regression details, helping analyze why the forced plan is not optimal. Option A is wrong because disabling automatic tuning and creating a plan guide may be premature without first identifying the root cause. Option C is wrong because manually reverting to a previous plan using Query Store should not be the first action; you need to understand the regression first.

Option D is wrong because scaling up the database does not directly address plan quality or regression.

Full explanation →
703
MCQhard

You have an Azure SQL Database that uses a SQL Agent job to run a critical ETL process every night. The job recently started failing intermittently. You need to automate the monitoring and alerting of job failures, and automatically retry the job twice with a 10-minute interval between retries. What should you configure?

A.Modify the job to use a T-SQL loop that checks job history and re-runs the job step.
B.Use Microsoft Power Automate to poll the job history and re-run the job if failed.
C.Create an Azure Monitor alert on the job failure event and use a webhook to trigger a PowerShell script that retries the job.
D.Configure the job step's 'Retry attempts' and 'Retry interval (minutes)' settings in the SQL Agent job step properties.
AnswerD

SQL Agent job steps have built-in retry configuration.

Why this answer

SQL Agent job steps have built-in 'Retry attempts' and 'Retry interval (minutes)' settings that allow you to configure automatic retries directly in the job step properties. This is the simplest and most native way to retry a failed job step. Option A is incorrect because a T-SQL loop checking job history is complex and not an automated built-in feature.

Option B is incorrect because Power Automate polling is an external solution that adds unnecessary complexity. Option C is incorrect because Azure Monitor alerts can notify but do not automatically retry the job; they would require additional logic (e.g., webhook to PowerShell) which is not native or as reliable as the built-in retry settings.

Full explanation →
704
MCQeasy

Your organization requires that all Azure SQL Database administrators use multi-factor authentication (MFA) when connecting. Which authentication method must be used?

A.SQL Server authentication
B.Microsoft Entra ID authentication with Conditional Access policy
C.Certificate-based authentication
D.Windows authentication
AnswerB

Entra ID can require MFA via Conditional Access.

Why this answer

Microsoft Entra ID authentication combined with a Conditional Access policy is required to enforce multi-factor authentication (MFA) for Azure SQL Database administrators. Conditional Access policies can mandate MFA as a condition for authentication, which is not possible with SQL Server authentication, certificate-based authentication, or Windows authentication alone. This method integrates with Microsoft Entra ID (formerly Azure AD) to provide the necessary security controls.

Exam trap

The trap here is that candidates often assume certificate-based authentication (Option C) can enforce MFA, but certificates alone do not require a second factor; MFA must be explicitly enforced via a Conditional Access policy with Microsoft Entra ID authentication.

How to eliminate wrong answers

Option A is wrong because SQL Server authentication uses a username and password stored in the database and does not support MFA or integration with Microsoft Entra ID. Option C is wrong because certificate-based authentication relies on client certificates for identity verification and does not inherently enforce MFA; it can be used with Entra ID but requires additional configuration like Conditional Access to require MFA. Option D is wrong because Windows authentication is used for on-premises SQL Server and is not supported for Azure SQL Database; it cannot enforce MFA through Conditional Access policies.

Full explanation →
705
MCQmedium

Your company has an Azure SQL Managed Instance in the UK South region. You need to implement a disaster recovery solution that provides automatic failover to a secondary region and allows read-only workloads to use the secondary replica. The solution must minimize data loss. What should you configure?

A.Deploy a second managed instance and use auto-failover groups
B.Create an availability group in the secondary region and configure automatic failover
C.Configure a failover group between two managed instances
D.Enable active geo-replication and configure auto-failover using PowerShell
AnswerA

Failover groups for Managed Instance provide automatic failover and readable secondary.

Why this answer

For Azure SQL Managed Instance, failover groups provide automatic failover to a secondary region and allow read-only workloads to use the secondary replica, minimizing data loss. Option B is incorrect because availability groups are not supported for Azure SQL Managed Instance; they are for SQL Server on Azure VMs. Option C is essentially the same as A but less precise; the standard solution is to deploy a second managed instance in the secondary region and configure a failover group.

Option D is incorrect because active geo-replication is a feature of Azure SQL Database, not Azure SQL Managed Instance.

Exam trap

Option C is tempting because it appears to describe the same solution as A, but it omits the critical step of deploying the second managed instance. The question asks what to configure; you must first deploy the secondary instance before configuring the failover group.

Full explanation →
706
MCQeasy

You are the database administrator for a company that uses Azure SQL Database. The company has a strict security policy requiring that all database connections be encrypted and that the server's firewall only allows connections from a list of approved IP addresses. You have configured the server-level firewall rules accordingly and enabled the 'Force encryption' setting on the server. However, after deployment, you notice that an application running on an Azure virtual machine is able to connect to the database even though its public IP address is not in the approved list. The virtual machine is in the same region as the database. What is the most likely cause?

A.The virtual machine is using a private IP address that is automatically allowed.
B.The virtual machine is using a service endpoint that bypasses the firewall.
C.The 'Force encryption' setting is misconfigured; it should be disabled to allow non-encrypted connections.
D.The 'Allow Azure services and resources to access this server' setting is enabled, which allows all Azure IPs to connect.
AnswerD

This setting bypasses firewall rules for Azure services.

Why this answer

The 'Allow Azure services and resources to access this server' setting, when enabled, adds a firewall rule that permits connections from all Azure IP addresses, including those not in the approved list. This overrides the specific IP-based firewall rules, allowing the Azure VM (even with a public IP not on the approved list) to connect. The setting is designed to allow Azure services like Azure App Service or Azure VMs to bypass IP restrictions, which violates the strict security policy in this scenario.

Exam trap

The trap here is that candidates often assume private IPs or service endpoints automatically bypass firewall rules, but the actual cause is the default 'Allow Azure services' setting, which is frequently overlooked as a blanket permission that overrides specific IP restrictions.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database does not automatically allow private IP addresses; firewall rules are based on public IP addresses, and private IPs are not routable over the internet. Option B is wrong because a service endpoint does not bypass the firewall; it routes traffic over the Azure backbone but still requires the source public IP to be allowed in the firewall rules. Option C is wrong because disabling 'Force encryption' would allow non-encrypted connections, which violates the encryption policy, and does not address the firewall bypass issue.

Full explanation →
707
MCQhard

Refer to the exhibit. You are reviewing the configuration of an Azure SQL Database hyperscale service tier. The database 'SalesDB' has read scale enabled with 2 read replicas. You need to ensure that if the primary region fails, the database can be failed over to a secondary region with minimal data loss and that the read replicas in the secondary region are also available. What should you configure?

A.Configure zone redundancy on the Hyperscale database.
B.Create a named replica in a secondary region and configure it as a geo-secondary.
C.Enable active geo-replication to a secondary server in a different region.
D.Create a failover group with a secondary Hyperscale database in another region.
AnswerB

Named replicas can be created in a secondary region and promoted during failover, providing DR and read scalability.

Why this answer

For Azure SQL Database Hyperscale, named replicas can be created in a secondary region. These replicas provide both read scale-out and can be promoted to primary during a regional failover, ensuring minimal data loss. Option A is incorrect because zone redundancy provides high availability within a single region, not disaster recovery across regions.

Option C is incorrect because active geo-replication is not supported for Hyperscale; named replicas serve the geo-replication purpose. Option D is incorrect because failover groups are not supported for Hyperscale; named replicas are the mechanism for cross-region failover and read scaling.

Full explanation →
708
MCQmedium

Your company has an Azure SQL Database that uses a failover group with a secondary in a different region. You need to ensure that read-only queries are directed to the secondary database to offload the primary. What should you configure?

A.Add ApplicationIntent=ReadOnly to the connection string and use the failover group listener.
B.Use a Traffic Manager profile to route read traffic.
C.Enable read-scale for the database.
D.Configure a separate connection string pointing to the secondary server.
AnswerA

The read-only listener routes to the secondary when ApplicationIntent=ReadOnly is specified.

Why this answer

Add ApplicationIntent=ReadOnly to the connection string and use the failover group listener. Azure SQL Database failover groups provide a read-only listener endpoint. When a client connects to the failover group listener with ApplicationIntent=ReadOnly in the connection string, the connection is automatically routed to the secondary database.

This offloads read-only queries from the primary. Option B is incorrect because Traffic Manager is not needed; the failover group listener already handles routing. Option C is incorrect because read-scale is a feature for querying readable secondaries in a Business Critical tier, but it does not automatically route read-only queries to a geo-secondary.

Option D is incorrect because using a separate connection string pointing directly to the secondary server defeats the purpose of automatic failover and redirection; the listener provides automatic routing and failover.

Full explanation →
709
MCQhard

You have an Azure SQL Database configured with active geo-replication. You need to automate the failover process in the event of a regional outage, ensuring minimal data loss and automatic failback when the primary region recovers. What should you implement?

A.Configure an auto-failover group with a grace period of 1 hour.
B.Set up a PowerShell script that checks primary database health and initiates failover.
C.Use a ScheduledExecutorService in a Java application to monitor and failover.
D.Enable geo-replication and manually trigger failover when needed.
AnswerA

Auto-failover groups automate failover and failback, and the grace period allows for data loss tolerance.

Why this answer

Azure SQL Database auto-failover groups provide automated failover and failback with a grace period for data loss tolerance. Option B is wrong because it does not automate failover. Option C is wrong because manual failover does not provide automatic failback.

Option D is wrong because ScheduledExecutorService is a manual workaround that does not integrate with Azure failover groups.

Full explanation →
710
MCQmedium

Refer to the exhibit. An administrator configures an Azure Cache for Redis instance with zone redundancy and a replica in a different region. What is the current state of the geo-replication setup?

A.The zone redundancy is enabled but geo-replication is not configured.
B.The geo-replication is partially configured; the East US replica is still being provisioned.
C.The geo-replication failed because the SKU does not support it.
D.The geo-replication is fully functional and ready for failover.
AnswerB

The East US replica is in 'Provisioning' state, meaning it's not ready.

Why this answer

One replica shows 'Provisioning' status, indicating it is still being set up. The geo-replication link is not yet fully established until both replicas are 'Ready'.

Full explanation →
711
MCQeasy

You need to configure automatic tuning for an Azure SQL Database to automatically identify and fix performance issues. Which two tuning options can be enabled?

A.FORCE LAST GOOD PLAN
B.CREATE INDEX
C.DROP INDEX
D.FORCE LAST GOOD PLAN and DROP INDEX
AnswerA, B

This is a valid automatic tuning option.

Why this answer

Automatic tuning in Azure SQL Database includes two main options: FORCE LAST GOOD PLAN and CREATE INDEX. FORCE LAST GOOD PLAN automatically identifies and forces the last known good query plan when a regression is detected, while CREATE INDEX automatically creates indexes to improve query performance based on workload patterns. Both options help identify and fix performance issues without manual intervention.

Exam trap

The trap here is that candidates may assume DROP INDEX is a valid automatic tuning option because they think automatic tuning includes both creating and dropping indexes, but Azure SQL Database only supports creating indexes automatically to avoid accidental performance degradation.

How to eliminate wrong answers

Option C is wrong because DROP INDEX is not an automatic tuning option; automatic tuning only creates indexes, never drops them, as dropping indexes could degrade performance. Option D is wrong because it combines FORCE LAST GOOD PLAN with DROP INDEX, but DROP INDEX is not a valid automatic tuning option in Azure SQL Database.

Full explanation →
712
MCQhard

You are a database administrator for a global SaaS provider. You have multiple Azure SQL Databases in a single logical server. You need to implement a disaster recovery strategy that provides automatic failover across Azure regions with the lowest possible RPO and RTO for critical databases. The solution must not require any application code changes. What should you implement?

A.Use Azure SQL Database backup and restore across regions with a recovery point objective (RPO) of 1 hour.
B.Configure an auto-failover group across two Azure regions with a grace period of 0 and include only the most critical database.
C.Use active geo-replication for each critical database and configure a custom health check to trigger failover.
D.Configure an auto-failover group across two Azure regions with a grace period of 1 hour and include all critical databases in the group.
AnswerD

Correct: Auto-failover groups provide automatic failover, low RPO (as low as 5 seconds), and application-transparent connection string.

Why this answer

Auto-failover groups provide automatic failover across Azure regions with the lowest possible RPO (typically 5 seconds) and RTO (around 1 minute) for critical databases, without requiring any application code changes. By setting the grace period to 1 hour, you allow the system to tolerate transient outages before triggering failover, which balances availability and data loss tolerance. Including all critical databases in the group ensures consistent failover behavior and prevents split-brain scenarios.

Exam trap

The trap here is that candidates often confuse active geo-replication (which requires manual or custom failover logic and code changes) with auto-failover groups (which provide automatic failover without code changes), and they mistakenly think a zero grace period is always better for RPO, ignoring the risk of flapping and unnecessary failovers.

How to eliminate wrong answers

Option A is wrong because backup and restore across regions has an RPO of at least 1 hour (due to the 1-hour backup frequency) and requires manual or scripted restore, which cannot achieve automatic failover or the lowest possible RPO/RTO. Option B is wrong because setting a grace period of 0 would cause immediate failover on any connectivity issue, leading to unnecessary failovers and potential data loss; also, including only one database ignores the requirement to cover all critical databases. Option C is wrong because active geo-replication requires application code changes to handle failover (e.g., updating connection strings) and does not provide automatic failover without custom health checks, which adds complexity and violates the 'no application code changes' requirement.

Full explanation →
713
MCQeasy

Refer to the exhibit. You run these commands in an Azure SQL Database. What is the result?

A.The user is created but not granted any permissions.
B.The commands fail because Entra ID users cannot be created in Azure SQL Database.
C.A SQL Server authentication user is created and granted read access.
D.A Microsoft Entra ID user is created and granted read access to the database.
AnswerD

The user is from Entra ID and granted db_datareader.

Why this answer

The commands create a user in an Azure SQL Database mapped to a Microsoft Entra ID (formerly Azure AD) identity. The CREATE USER statement with FROM EXTERNAL PROVIDER creates a user that corresponds to an Entra ID user or group. The ALTER ROLE statement then adds this user to the db_datareader database role, granting read access to all tables and views.

Therefore, option D correctly describes the outcome: a Microsoft Entra ID user is created and granted read access.

Exam trap

The trap here is that candidates often confuse the FROM EXTERNAL PROVIDER syntax with creating a contained database user for SQL authentication, leading them to incorrectly choose option C or A, when in fact the command explicitly maps to an Entra ID identity.

How to eliminate wrong answers

Option A is wrong because the user is not only created but also explicitly granted read permissions via the ALTER ROLE statement. Option B is wrong because Entra ID users can be created in Azure SQL Database using the FROM EXTERNAL PROVIDER syntax; this is a supported feature. Option C is wrong because the CREATE USER ...

FROM EXTERNAL PROVIDER syntax creates a user mapped to an Entra ID identity, not a SQL Server authentication user; SQL authentication users are created with CREATE USER ... WITH PASSWORD or CREATE LOGIN.

Full explanation →
714
Multi-Selectmedium

Which THREE actions can you take to monitor and optimize database resources in Azure SQL Database? (Choose three.)

Select 3 answers
A.Enable Microsoft Defender for Azure SQL to detect vulnerabilities.
B.Use Query Store to track query performance over time.
C.Query dynamic management views to identify blocking and resource waits.
D.Enable automatic tuning to automatically implement performance improvements.
E.Configure Microsoft Sentinel to monitor database activity.
AnswersB, C, D

Query Store provides historical query performance data.

Why this answer

Options B, C, and D are correct. Query Store tracks query performance over time, dynamic management views help identify blocking and resource waits, and automatic tuning automatically implements performance improvements. Option A (Microsoft Defender for SQL) is a security feature for vulnerability detection, not directly for monitoring and optimizing database resources.

Option E (Microsoft Sentinel) is a security information and event management (SIEM) tool, not specifically for database monitoring and optimization.

Full explanation →
715
Multi-Selecthard

Your Azure SQL Database is accessed by multiple applications. You need to ensure that all connections use Transport Layer Security (TLS) 1.2 or higher. Which TWO configurations should you verify or enable?

Select 2 answers
A.Configure client applications to use TLS 1.2 in their connection strings.
B.Create a network security group rule to block non-TLS traffic.
C.Set 'DenyPublicNetworkAccess' to 'Yes' on the server.
D.Set the server's 'minimalTlsVersion' property to '1.2'.
E.Enable 'ForceEncryption' on the SQL Server instance.
AnswersA, D

Clients must also support TLS 1.2.

Why this answer

Client applications must explicitly request TLS 1.2 in their connection strings (e.g., by adding 'Encrypt=True;TrustServerCertificate=False;' and specifying the minimum TLS version in the underlying driver configuration). Without this, the client may negotiate a lower TLS version, even if the server supports higher versions. Option D is correct because setting the server's 'minimalTlsVersion' property to '1.2' enforces that the Azure SQL Database server rejects any connection attempt using TLS 1.0 or 1.1, ensuring only TLS 1.2+ connections are accepted at the server level.

Exam trap

The trap here is that candidates often confuse network-level controls (like NSGs) with TLS-level enforcement, or mistakenly apply on-premises SQL Server settings (like 'ForceEncryption') to Azure SQL Database, which has different configuration mechanisms and defaults.

Full explanation →
716
Multi-Selectmedium

Which TWO options are valid methods to optimize query performance in Azure SQL Managed Instance?

Select 2 answers
A.Use columnstore indexes on large tables
B.Set database compatibility level to 150
C.Increase the maximum storage size
D.Enable Query Store and monitor regressions
E.Enable Transparent Data Encryption (TDE)
AnswersA, D

Columnstore indexes improve analytics and large scan queries.

Why this answer

Options A and D are correct. Columnstore indexes (A) improve analytical query performance by reducing I/O and using batch processing. Query Store (D) helps identify regressions by tracking execution plans and performance metrics.

Option B is incorrect; setting database compatibility level to 150 may enable new features but is not a direct query optimization method. Option C is incorrect; increasing storage size does not improve query performance. Option E is incorrect; Transparent Data Encryption (TDE) secures data but does not enhance performance.

Full explanation →
717
MCQhard

Your company requires that all Azure SQL Databases use Transparent Data Encryption (TDE) with customer-managed keys (CMK) stored in Azure Key Vault. The security policy mandates that the key must be rotated every 90 days. You need to implement this requirement with minimal administrative overhead. What should you do?

A.Manually rotate the key in Key Vault every 90 days and update the database
B.Use Azure SQL Database server-side TDE with a service-managed key
C.Store the key in the database and use a custom rotation script
D.Use Azure Key Vault key rotation policy to automatically rotate the key every 90 days
AnswerD

Automatic rotation reduces administrative overhead.

Why this answer

Azure Key Vault supports automatic key rotation policies that can be configured to rotate a customer-managed key (CMK) every 90 days without any manual intervention. When this key is used for TDE in Azure SQL Database, the database automatically uses the latest version of the key from Key Vault, so no additional steps are needed to update the database after rotation. This satisfies the security policy with minimal administrative overhead.

Exam trap

The trap here is that candidates may confuse automatic key rotation in Key Vault with manual rotation or service-managed keys, failing to recognize that Azure Key Vault's built-in rotation policy directly satisfies the CMK and rotation requirements with zero administrative overhead.

How to eliminate wrong answers

Option A is wrong because manually rotating the key every 90 days and updating the database introduces significant administrative overhead, contradicting the requirement for minimal overhead. Option B is wrong because service-managed keys do not meet the requirement for customer-managed keys (CMK); they are managed by Microsoft, not the customer. Option C is wrong because storing the key in the database is not supported for TDE in Azure SQL Database; TDE keys must be stored in Azure Key Vault or managed by the service, and a custom rotation script would add unnecessary complexity and overhead.

Full explanation →
718
MCQhard

Refer to the exhibit. You review the configuration of an Azure SQL Database. Which statement about this database is true?

A.The database can automatically increase storage.
B.Geo-redundant backup storage is enabled.
C.The database uses zone-redundant high availability.
D.The database is using the Hyperscale service tier.
AnswerA

The storage profile shows autogrow enabled, allowing the database to automatically increase storage when needed.

Why this answer

The storage profile shows autogrow enabled, allowing the database to automatically increase storage when needed. Option B is wrong because geo-redundant backup storage is not enabled; it is configured as locally redundant. Option C is wrong because the high availability configuration is not zone-redundant; it uses locally redundant storage.

Option D is wrong because the service tier shown is General Purpose, not Hyperscale.

Full explanation →
719
MCQeasy

Your Azure SQL Database contains sensitive financial data. You need to audit all data modifications (INSERT, UPDATE, DELETE) and store the audit logs in a central Azure Storage account for compliance. What should you configure?

A.Enable auditing on the database and set the audit log destination to an Azure Storage account.
B.Configure diagnostic settings to stream query store data to an event hub.
C.Enable Microsoft Defender for SQL and configure security alerts to be sent to a storage account.
D.Enable SQL Vulnerability Assessment and export the results to a storage account.
AnswerA

Auditing captures data modifications and can be stored in storage.

Why this answer

Azure SQL Database's built-in auditing feature can be configured to capture all data modifications (INSERT, UPDATE, DELETE) and write audit logs directly to an Azure Storage account. This meets the compliance requirement for centralized, durable storage of audit records without additional services or complex pipelines.

Exam trap

The trap here is confusing security monitoring tools (Defender for SQL, Vulnerability Assessment) or performance diagnostics (query store) with the specific auditing feature required for capturing data modification logs for compliance.

How to eliminate wrong answers

Option B is wrong because diagnostic settings streaming query store data to an event hub captures performance and query metrics, not data modification audit logs; it is designed for real-time monitoring, not compliance auditing. Option C is wrong because Microsoft Defender for SQL provides security alerts and threat detection, not granular audit logs of INSERT/UPDATE/DELETE operations; its alerts are sent to security teams, not stored as a compliance audit trail. Option D is wrong because SQL Vulnerability Assessment scans for security misconfigurations and exports assessment results, not data modification audit logs; it is a security posture tool, not an auditing solution.

Full explanation →
720
Drag & Dropmedium

Drag and drop the steps to configure transparent data encryption (TDE) for an Azure SQL Database using a customer-managed key in Azure Key Vault 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

First set up Key Vault with a key, grant permissions, then enable TDE with customer-managed key, select the key, and save.

Full explanation →
721
MCQeasy

You are monitoring an Azure SQL Database and notice high PAGELATCH waits. What is the most likely cause?

A.Concurrent inserts into a table with a clustered index causing last-page contention.
B.Insufficient buffer pool size leading to frequent reads from disk.
C.High CPU usage due to inefficient queries.
D.Long-running transactions blocking other queries.
AnswerA

High PAGELATCH waits commonly result from hotspot contention on the last page of an index.

Why this answer

PAGELATCH waits indicate contention on data pages, often due to concurrent inserts to the same page, such as the last page of a clustered index. Option B is wrong because buffer pool insufficient leads to PAGEIOLATCH waits, not PAGELATCH. Option C is wrong because high CPU usage due to inefficient queries causes CPU pressure, not directly PAGELATCH waits.

Option D is wrong because long-running transactions blocking other queries results in lock waits, not page latch contention.

Full explanation →
722
MCQmedium

You are migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database is 500 GB and requires a disaster recovery solution with an RPO of 15 seconds and automatic failover to a secondary region. Which configuration meets these requirements?

A.Enable geo-redundant backup storage and configure geo-restore.
B.Configure active geo-replication to a secondary instance in another region.
C.Set up log shipping to a secondary instance in another region.
D.Create an auto-failover group with a secondary instance in a paired region using synchronous replication.
AnswerD

Auto-failover groups support automatic failover and synchronous replication for low RPO.

Why this answer

Auto-failover groups with a secondary instance in a paired region using synchronous replication meet the requirements for automatic failover and an RPO of 15 seconds for Azure SQL Managed Instance. Option A is incorrect because geo-redundant backup storage and geo-restore do not provide automatic failover or a low RPO. Option B is incorrect because active geo-replication is not supported for SQL Managed Instance; it is used for Azure SQL Database.

Option C is incorrect because log shipping is not supported for SQL Managed Instance.

Full explanation →
723
Multi-Selecthard

Which THREE components are required to run Elastic Database Jobs for Azure SQL Database? (Choose three.)

Select 3 answers
A.A job database
B.Target databases (members of the job group)
C.A job agent
D.SQL Agent
E.Azure Automation account
AnswersA, B, C

Stores job definitions and execution history.

Why this answer

Elastic Database Jobs require a job database to store metadata, a job agent to orchestrate, and target databases. SQL Agent is not required. Elastic pool is optional.

Azure Automation is not required.

Full explanation →
724
MCQhard

Refer to the exhibit. An administrator is trying to perform a point-in-time restore for a managed instance database. The output shows the state of the instance and databases. What is the most likely reason db1 shows 'Restoring' status?

A.The database is being upgraded to a new service tier.
B.The database is corrupted and Azure is automatically repairing it.
C.The database is being restored from a geo-redundant backup.
D.The database is in the process of replication to a secondary region.
AnswerC

The status 'Restoring' and presence of a geo-backup suggest a restore operation is happening.

Why this answer

The 'Restoring' status on db1 indicates that a restore operation is currently in progress on that database. The geo-backup list shows an available backup, which might be used for a geo-restore or point-in-time restore that is ongoing.

Full explanation →
725
MCQmedium

You have an Azure SQL Database that uses a firewall rule allowing access from a specific range of IP addresses. A developer reports that they cannot connect from a new IP address that falls outside the allowed range. You need to temporarily allow the developer's IP address for 24 hours without affecting existing rules. What should you do?

A.Configure a point-to-site VPN connection for the developer.
B.Add a new firewall rule at the server level that allows the developer's IP address.
C.Update the existing firewall rule to include the developer's IP address.
D.Modify the database-level firewall rule to include the developer's IP.
AnswerB

New rule allows the specific IP without affecting existing rules.

Why this answer

Azure SQL Database firewall rules are configured at the server level (the logical server) to control inbound access. Adding a new server-level firewall rule for the developer's specific IP address allows temporary access without modifying or removing the existing range-based rule. This approach is the standard method for granting time-limited access to a single IP while preserving all other firewall configurations.

Exam trap

The trap here is that candidates confuse server-level firewall rules with database-level firewall rules, incorrectly assuming that database-level rules exist in Azure SQL Database (they do not), or they think updating the existing range is acceptable, missing the requirement to leave existing rules unchanged.

How to eliminate wrong answers

Option A is wrong because a point-to-site VPN connection is an over-engineered solution that introduces unnecessary complexity and latency; it is not designed for simple IP-based access control to Azure SQL Database and would require additional networking components (e.g., VPN gateway, certificates). Option C is wrong because updating the existing firewall rule to include the developer's IP would expand the allowed IP range permanently, which contradicts the requirement to temporarily allow access for only 24 hours without affecting existing rules. Option D is wrong because database-level firewall rules are a legacy feature and are not supported for Azure SQL Database; all firewall rules must be configured at the server level or via virtual network rules.

Full explanation →
726
MCQmedium

Refer to the exhibit. An administrator wants to ensure that during a regional outage, the failover group automatically fails over without data loss if possible. What is the current configuration gap?

A.The failover policy is set to Automatic, but the grace period is too long.
B.The backup storage redundancy is Local, which does not provide geo-redundancy for backups.
C.The secondary managed instance mi2 is in the same region as the primary.
D.The read-only endpoint failover policy is Disabled, which prevents read-only traffic after failover.
AnswerB

Geo-redundant backup storage (GRS) is needed to protect backups across regions.

Why this answer

Automatic failover with data loss grace period is set to 60 minutes. The backup redundancy is Local, which means backups are stored only in the primary region. If a regional disaster occurs, backups might be lost.

Geo-redundant backups would be needed to recover in the secondary region without data loss.

Full explanation →
727
MCQmedium

You have an Azure SQL Database with Always Encrypted enabled for sensitive columns. You need to grant a developer the ability to encrypt data using a column master key stored in Azure Key Vault. What permissions must be assigned to the developer's Microsoft Entra identity in the key vault?

A.Get, Decrypt, and Unwrap Key.
B.Get, Wrap Key, and Unwrap Key.
C.Get and Decrypt.
D.Get, Sign, and Verify.
AnswerB

Wrap Key is needed to encrypt, and Unwrap Key is needed to decrypt. Get is required to retrieve the key metadata.

Why this answer

To encrypt data with Always Encrypted, the client driver must retrieve the column encryption key (which is stored encrypted) and unwrap it using the column master key. It may also need to wrap newly generated column encryption keys during key rotation or initial setup. Therefore, the developer's Microsoft Entra identity requires Get, Unwrap Key, and Wrap Key permissions on the column master key in Azure Key Vault.

Option B correctly includes all three.

Exam trap

The trap is that candidates often assume only Wrap Key is needed for encryption, but in Always Encrypted, the client driver must also unwrap the existing column encryption key from its encrypted form to use it for encrypting data. Both Unwrap Key and Wrap Key are required operations during encryption tasks.

How to eliminate wrong answers

Option A is wrong because it includes Decrypt and Unwrap Key, which are required for decryption, not encryption; granting these would over-permission the developer for the stated task. Option C is wrong because it only provides Get and Decrypt, missing the Wrap Key permission necessary to encrypt a column encryption key. Option D is wrong because Sign and Verify are used for digital signatures and key attestation, not for encrypting data with Always Encrypted.

Full explanation →
728
Multi-Selectmedium

Which TWO of the following are supported high availability features in Azure SQL Managed Instance?

Select 2 answers
A.Zone-redundant deployment for Business Critical tier
B.Database mirroring
C.Always On Availability Groups (built-in)
D.Log shipping to a secondary instance
E.Windows Server Failover Clustering
AnswersA, C

Correct: Managed Instance Business Critical tier supports zone redundancy.

Why this answer

Azure SQL Managed Instance supports zone-redundant deployment for the Business Critical tier, which replicates the database across multiple Azure availability zones within the same region to protect against datacenter-level failures. Option C is correct because Azure SQL Managed Instance has built-in Always On Availability Groups technology that provides automatic failover and high availability at no additional cost, leveraging a quorum-based cluster under the hood.

Exam trap

The trap here is that candidates often confuse on-premises SQL Server high availability features (like database mirroring, log shipping, or manual Windows clustering) with the fully managed, built-in capabilities of Azure SQL Managed Instance, leading them to select unsupported legacy options.

Full explanation →
729
MCQmedium

You manage an Azure SQL Managed Instance that hosts a line-of-business application. The instance is currently deployed in the UK South region using the General Purpose service tier. The business has a new requirement for disaster recovery with an RPO of 30 minutes and an RTO of 2 hours. You need to implement a solution that minimizes ongoing costs. The secondary region should be UK West. The application can tolerate a brief outage during failover. What should you do?

A.Deploy a zone-redundant General Purpose instance in UK South only.
B.Keep the current instance and rely on geo-restore to UK West.
C.Upgrade both instances to Business Critical and configure a failover group.
D.Deploy a secondary General Purpose instance in UK West and configure a failover group.
AnswerD

Correct. A failover group with a secondary General Purpose instance in UK West provides automatic replication with an RPO of 30 seconds to 5 minutes (within the 30-minute requirement) and failover within 1-2 minutes (within 2-hour RTO). This is the most cost-effective solution.

Why this answer

Deploying a secondary General Purpose instance in UK West and configuring a failover group meets the RPO of 30 minutes and RTO of 2 hours with minimal cost. Option A is wrong because zone redundancy only protects against zonal failures within a single region, not regional disasters. Option B is wrong because geo-restore has an RPO of up to 1 hour and an RTO of hours to days, failing the requirements.

Option C is wrong because Business Critical is more expensive and not necessary for the RPO/RTO targets; General Purpose can achieve them using a failover group.

Full explanation →
730
MCQmedium

You have an Azure SQL Database that needs to be accessed by an application with a static public IP address. You want to allow only that IP address to connect. What should you configure?

A.Create a virtual network rule for the database.
B.Create a private endpoint for the database.
C.Create a server-level firewall rule with the application's IP.
D.Create a database-level firewall rule with the application's IP.
AnswerC

Server-level firewall rules allow specific IP addresses.

Why this answer

A server-level firewall rule explicitly allows inbound connections from a specific client IP address to the Azure SQL Database logical server. Since the application has a static public IP, configuring a server-level firewall rule with that IP is the simplest and most direct method to restrict access to only that address, while still using the public endpoint.

Exam trap

The trap here is that candidates often confuse database-level firewall rules (Option D) as the correct answer, not realizing that server-level rules are the standard for IP-based access and that database-level rules require contained database users, which is an additional prerequisite not mentioned in the scenario.

How to eliminate wrong answers

Option A is wrong because a virtual network rule allows traffic from a specific Azure virtual network subnet, not from a static public IP address; it requires the client to be inside that VNet. Option B is wrong because a private endpoint assigns the database a private IP within a VNet, which is used for private connectivity and does not filter by a specific public IP; it also requires the client to be in the same or peered VNet. Option D is wrong because a database-level firewall rule is scoped to a single database and is supported only for contained database users, but the question does not specify that the application uses contained database authentication; server-level rules are the standard for IP-based access control and apply to all databases on the server.

Full explanation →
731
MCQeasy

You are configuring managed backup for an Azure SQL Managed Instance as shown in the exhibit. What is the purpose of this configuration?

A.To enable geo-replication for the managed instance.
B.To configure automated backups of the managed instance to Azure Blob Storage.
C.To set up disaster recovery across Azure regions.
D.To configure point-in-time restore for the managed instance.
AnswerB

Managed backup stores backups in Azure Blob Storage.

Why this answer

The configuration shown in the exhibit is for managed backup, which automatically schedules and stores full, differential, and transaction log backups of the Azure SQL Managed Instance to Azure Blob Storage. This ensures that backups are retained and available for restore operations without manual intervention, fulfilling the core purpose of automated backups to Azure Blob Storage.

Exam trap

The trap here is that candidates often confuse the purpose of configuring automated backups (which creates the backup chain) with the point-in-time restore feature (which uses that chain), leading them to select Option D instead of the correct Option B.

How to eliminate wrong answers

Option A is wrong because geo-replication for Azure SQL Managed Instance is configured via failover groups, not through managed backup settings; managed backup does not replicate data to another region. Option C is wrong because disaster recovery across Azure regions is achieved by setting up a failover group with a secondary managed instance in a paired region, not by enabling managed backup. Option D is wrong because point-in-time restore is a feature that relies on the backup chain created by managed backup, but the configuration itself is for creating those backups, not for performing the restore operation.

Full explanation →
732
MCQmedium

You have an Azure SQL Database with sensitive customer data. You need to mask the credit card numbers so that only users with the 'Unmask' permission can see the full number. Non-privileged users should see only the last four digits. Which feature should you implement?

A.Column-level security
B.Row-level security
C.Dynamic Data Masking
D.Always Encrypted
AnswerC

Dynamic Data Masking can mask sensitive data while allowing privileged users to see full values.

Why this answer

Dynamic Data Masking (DDM) is the correct feature because it allows you to obfuscate sensitive data in query results without changing the underlying database. You can define a mask on the credit card column that shows only the last four digits by default, and grant the UNMASK permission to privileged users so they see the full value. This directly meets the requirement of role-based partial masking without altering the stored data.

Exam trap

The trap here is that candidates confuse Dynamic Data Masking with Always Encrypted, thinking encryption is required for masking, but DDM is purely a presentation-layer obfuscation that does not encrypt the underlying data.

How to eliminate wrong answers

Option A is wrong because Column-level security controls read access at the column level (e.g., denying SELECT on a column), but it cannot partially mask data within a column; it either grants or denies full visibility. Option B is wrong because Row-level security filters entire rows based on a predicate function, but it cannot mask individual column values within a row. Option D is wrong because Always Encrypt encrypts data at the client side and never exposes plaintext to the database engine, making it impossible to show partial values (like last four digits) to non-privileged users without complex client-side logic.

Full explanation →
733
MCQeasy

You are preparing a disaster recovery runbook. You plan to use the PowerShell command shown in the exhibit to restore a database to a different region. What must be true for this command to succeed?

A.The source database must have geo-redundant backup storage configured.
B.The source database must have point-in-time restore enabled.
C.The source database must be online at the time of restoration.
D.The target server must be in the same region as the source server.
AnswerA

Required for geo-restore.

Why this answer

The Restore-AzSqlDatabase cmdlet with the -FromGeoBackup parameter requires the source database to have geo-redundant backup storage (also known as geo-redundant storage) enabled. This allows restoration from geographically replicated backups. Option B is incorrect because point-in-time restore is not required for a geo-restore; geo-restore uses the geo-redundant backup independently.

Option C is incorrect because the source database does not need to be online at the time of restoration; geo-backups are stored in Azure storage and are available even if the source database is offline. Option D is incorrect because the purpose of geo-restore is to restore to a different region; therefore, the target server can be in a different region than the source.

Full explanation →
734
MCQmedium

Refer to the exhibit. You retrieve the configuration of an Azure SQL Database. Based on the JSON output, which feature is enabled?

A.Ledger tables
B.Always On availability groups
C.Intelligent Insights
D.Always Encrypted with secure enclaves
AnswerC

isIntelligentInsightsOn is true.

Why this answer

The JSON output shows "isIntelligentInsightsOn": true, indicating that the Intelligent Insights feature is enabled for the Azure SQL Database. Option A is incorrect because "isLedgerOn" is false. Option B is incorrect because there is no mention of availability groups in the JSON.

Option D is incorrect because even though "preferredEnclaveType" is set to VBS, Always Encrypted with secure enclaves requires explicit configuration on columns, and the setting alone does not mean the feature is enabled.

Full explanation →
735
Multi-Selectmedium

You are planning to migrate a 500 GB on-premises SQL Server database to Azure SQL Managed Instance. The database has high transaction volume and cannot tolerate more than 15 minutes of downtime. Which migration methods should you consider? (Choose TWO.)

Select 2 answers
A.Azure Database Migration Service (online mode)
B.Back up to URL and restore
C.Export to BACPAC and import
D.Log shipping
E.Transactional replication
AnswersA, E

Online mode supports minimal downtime.

Why this answer

Azure Database Migration Service (DMS) online mode is correct because it uses continuous change data capture (CDC) to replicate ongoing transactions from the source to the target, allowing the source database to remain operational during migration. This minimizes downtime to a final cutover window, which can be under 15 minutes when properly orchestrated.

Exam trap

The trap here is that candidates often confuse offline methods (backup/restore, BACPAC) as acceptable for low-downtime scenarios, failing to recognize that only online replication-based methods (DMS online, transactional replication) can meet a strict 15-minute downtime SLA for a high-volume database.

Full explanation →
736
MCQmedium

You are designing a solution for storing audit logs from Azure SQL Database. The logs must be retained for 7 years and must be immutable to prevent tampering. Which Azure service should you use?

A.Use Azure Files share with read-only permissions
B.Send logs to Azure Log Analytics workspace
C.Store logs in an audit table in Azure SQL Database
D.Azure Blob Storage with immutable storage policy
AnswerD

Immutable blob storage prevents deletion and modification.

Why this answer

Azure Blob Storage with an immutable storage policy (WORM – Write Once, Read Many) is the correct choice because it ensures that audit logs cannot be modified or deleted for a specified retention period (7 years). This meets the immutability and retention requirements for compliance with regulations such as SOX or HIPAA. Azure SQL Database audit logs can be directly streamed to Azure Blob Storage, making it a seamless and secure storage solution.

Exam trap

The trap here is that candidates often confuse 'immutable' with 'read-only permissions' (Option A) or assume that a database table (Option C) can be made immutable by restricting permissions, but true immutability requires storage-level WORM enforcement that cannot be bypassed by any user or process.

How to eliminate wrong answers

Option A is wrong because Azure Files share with read-only permissions does not provide true immutability; permissions can be changed by an administrator, and the underlying data can be modified or deleted, failing the tamper-proof requirement. Option B is wrong because Azure Log Analytics workspace is designed for real-time monitoring and analysis, not long-term immutable storage; it has a maximum retention period of 730 days (2 years) and does not support WORM policies. Option C is wrong because storing logs in an audit table in Azure SQL Database does not guarantee immutability; data can be altered or deleted by users with appropriate permissions, and the database itself is not designed for write-once, read-many compliance storage.

Full explanation →
737
MCQeasy

You need to ensure that Azure SQL Database can only be accessed from a specific virtual network in Azure. Which configuration should you apply?

A.Add the public IP address range of the VNet to the firewall rules
B.Configure a virtual network service endpoint and add a firewall rule for the VNet subnet
C.Create a private endpoint and disable public network access
D.Set 'Deny public network access' to Yes
AnswerB

Service endpoints allow VNet traffic to be identified and permitted via firewall rules.

Why this answer

Configuring a virtual network service endpoint for Azure SQL Database and then adding a firewall rule for the specific VNet subnet restricts access to traffic originating from that subnet only. This ensures that the database is not reachable over the public internet, but only from the designated virtual network, meeting the requirement precisely.

Exam trap

The trap here is that candidates often confuse service endpoints (which filter by VNet subnet identity) with firewall IP rules (which filter by public IP address), leading them to incorrectly select Option A or assume that denying public access alone (Option D) is sufficient.

How to eliminate wrong answers

Option A is wrong because adding the public IP address range of the VNet to the firewall rules does not restrict access to the VNet; VNet IP ranges are private and not routable over the internet, so this would not filter traffic by VNet origin. Option C is wrong because creating a private endpoint and disabling public network access provides a private IP address for the database within the VNet, but it does not restrict access to a specific VNet—it allows access from any network that can reach the private endpoint, including peered VNets or on-premises via VPN. Option D is wrong because setting 'Deny public network access' to Yes blocks all public internet traffic but does not by itself allow access from a specific VNet; it must be combined with a private endpoint or service endpoint to grant access.

Full explanation →
738
Multi-Selectmedium

Which TWO of the following are benefits of using Azure SQL Database failover groups compared to active geo-replication alone?

Select 2 answers
A.Support for manual failover.
B.Failover of multiple databases in a single group.
C.Readable secondary replicas for read-scale workloads.
D.Automatic failover based on the grace period.
E.Synchronous data replication to the secondary.
AnswersB, D

Correct: Failover groups allow coordinated failover of multiple databases.

Why this answer

Failover groups extend active geo-replication by allowing you to manage failover for a group of databases as a single unit. This simplifies the failover process when you have multiple databases that must be failed over together to maintain application consistency. Option B is correct because failover groups support the coordinated failover of multiple databases, which active geo-replication alone does not.

Exam trap

The trap here is that candidates confuse the features of active geo-replication (like readable secondaries and manual failover) with the unique benefits of failover groups, which are specifically the ability to fail over multiple databases as a group and the automatic failover based on a grace period.

Full explanation →
739
MCQmedium

You manage an Azure SQL Database that runs a customer-facing application. You notice that during peak hours, the DTU consumption reaches 100% and queries experience timeouts. You need to ensure consistent performance without manual intervention. What should you implement?

A.Enable Query Store to identify costly queries.
B.Implement read scale-out to offload read queries.
C.Configure automatic tuning to force plan regression fixes.
D.Scale up the database to a higher service tier (e.g., from S2 to S3).
AnswerD

Scaling up increases DTU limits, alleviating resource contention.

Why this answer

Scaling up to a higher service tier (e.g., from S2 to S3) increases DTU limits, providing more CPU, memory, and IO resources to handle peak demand, thus preventing timeouts without manual intervention. Option A is incorrect because Query Store helps monitor and troubleshoot performance but does not automatically resolve resource shortages. Option B is incorrect because read scale-out offloads read-only queries to a read-only replica, but the issue is DTU consumption reaching 100%, which affects all queries, not just reads.

Option C is incorrect because automatic tuning can fix plan regression but does not add resources; it only optimizes query plans.

Full explanation →
740
MCQmedium

You observe that the average of Maximum DTU consumption over the last hour is consistently above 90%. What should you do next?

A.Scale up the database to a higher service tier or increase DTU.
B.Enable Query Store to analyze top queries.
C.Rebuild all indexes in the database.
D.Do nothing; it's normal for DTU to be high.
AnswerA

High DTU consumption indicates need for more resources.

Why this answer

When average DTU consumption is consistently above 90%, it indicates high resource usage. Scaling up the database to a higher service tier or increasing DTUs directly addresses the resource pressure and improves performance. Option B is incorrect because enabling Query Store is a diagnostic tool to identify performance issues, but it does not provide immediate relief.

Option C is incorrect because rebuilding indexes can help with fragmentation but may not address the underlying resource limitation. Option D is incorrect because sustained high DTU can lead to performance degradation and should be addressed.

Full explanation →
741
Multi-Selecthard

Which TWO actions are required to automate the export of an Azure SQL Database to a BACPAC file on a monthly basis? (Choose two.)

Select 2 answers
A.Configure long-term retention (LTR) policy for the database.
B.Use Azure Automation or a scheduled Azure Function to call the Export-AzSqlDatabase cmdlet.
C.Deploy a SQL Server on Azure VM to run the export command.
D.Install SQL Server Integration Services (SSIS) on a virtual machine.
E.Create an Azure Storage account with a container to store the BACPAC file.
AnswersB, E

Automation is needed to run the export on a recurring schedule.

Why this answer

To automate the export of an Azure SQL Database to a BACPAC file on a monthly basis, you need a storage location for the file and an automation method. Option B is correct because Azure Automation or an Azure Function can trigger the Export-AzSqlDatabase cmdlet or REST API to perform the export on a schedule. Option E is correct because an Azure Storage account with a container is required to store the resulting BACPAC file.

Option A is incorrect because LTR policy is for automated backups, not export to BACPAC. Option C is incorrect because a SQL Server on Azure VM is not needed; the export is initiated directly against the Azure SQL Database logical server. Option D is incorrect because SSIS is an ETL tool that is not necessary for a simple export operation; the export can be done via PowerShell, CLI, or REST API without SSIS.

Full explanation →
742
MCQmedium

Your company runs a mission-critical application on Azure SQL Database in the East US region. You need to ensure automatic failover with zero data loss in case of a regional outage. Which deployment option should you use?

A.Deploy a Business Critical tier database and configure an auto-failover group with a readable secondary in a paired region.
B.Deploy a General Purpose tier database with geo-replication.
C.Deploy a Hyperscale tier database with zone redundancy.
D.Deploy a Serverless tier database with active geo-replication.
AnswerA

Correct. Business Critical tier with auto-failover group and readable secondary in a paired region provides synchronous replication, ensuring zero data loss and automatic failover.

Why this answer

To achieve automatic failover with zero data loss during a regional outage, you need a solution that supports synchronous replication for a secondary replica in another region. Azure SQL Database Business Critical tier (or Premium tier) supports auto-failover groups with a readable secondary in a paired region, where the secondary is kept in sync synchronously, ensuring no data loss (RPO=0). Option A correctly describes this configuration.

General Purpose tier (Option B) uses asynchronous geo-replication, which has potential data loss. Hyperscale tier (Option C) supports zone redundancy but not synchronous replication across regions for auto-failover groups. Serverless tier (Option D) is not suitable for mission-critical workloads and does not provide the required availability guarantees.

Exam trap

Students often confuse zone redundancy (within region) with geo-redundancy (cross-region). Zone redundancy protects against datacenter failures within a region but does not cover regional outages. For regional disaster recovery with zero data loss, you must use auto-failover groups with synchronous replication, which is only available in Business Critical or Premium tiers.

Full explanation →
743
MCQeasy

Refer to the exhibit. You are configuring Azure SQL Database Transparent Data Encryption (TDE) with customer-managed keys (CMK) stored in Azure Key Vault. The deployment uses a user-assigned managed identity. However, after deployment, the TDE status shows 'Inaccessible'. What is the most likely cause?

A.The key specified in the URI does not exist
B.The user-assigned managed identity is not assigned to the SQL Database server
C.The Key Vault firewall is enabled and does not allow Azure services
D.The managed identity lacks 'Get', 'Wrap Key', and 'Unwrap Key' permissions on the Key Vault key
AnswerD

These permissions are required for TDE with CMK.

Why this answer

When using customer-managed keys (CMK) for TDE in Azure SQL Database, the managed identity assigned to the logical server must have 'Get', 'Wrap Key', and 'Unwrap Key' permissions on the key in Azure Key Vault. Without these specific permissions, the SQL Database service cannot retrieve or use the key to encrypt or decrypt the database encryption key, resulting in an 'Inaccessible' TDE status.

Exam trap

The trap here is that candidates often assume the issue is with the Key Vault firewall or the identity assignment, but the most common post-deployment cause of 'Inaccessible' TDE is missing cryptographic permissions on the managed identity, not network or identity existence issues.

How to eliminate wrong answers

Option A is wrong because if the key specified in the URI did not exist, the deployment would typically fail during configuration, not result in an 'Inaccessible' status after deployment. Option B is wrong because the user-assigned managed identity must be assigned to the logical SQL server, not the SQL Database server (which is a common misconception); the identity is assigned at the server level, and if it were missing, the deployment would likely fail earlier. Option C is wrong because the Key Vault firewall, when enabled, can block access even if 'Allow trusted Microsoft services' is not configured, but the most common and direct cause of 'Inaccessible' status after a successful deployment is missing key permissions on the managed identity.

Full explanation →
744
MCQhard

Your company uses Azure SQL Database with active geo-replication. You need to ensure that in the event of a regional disaster, the failover to the secondary region occurs automatically with minimal downtime. The secondary region must be in a different Azure geography for compliance. What should you configure?

A.Create a failover group with automatic failover policy.
B.Create an auto-failover group using Azure CLI and set cross-geography replication.
C.Configure active geo-replication and set auto-failover policy.
D.Configure an always failover group with Microsoft Entra ID authentication.
AnswerA

Correct: Failover groups support automatic failover across regions.

Why this answer

Failover groups with automatic failover policy enable automatic failover to a secondary region in the same or paired region, meeting the requirement for minimal downtime. Option B is incorrect because auto-failover groups are not a separate concept; failover groups already support automatic failover. Option C is incorrect because active geo-replication alone requires manual failover, not automatic.

Option D is incorrect because 'always failover groups' is not a valid Azure SQL Database feature, and Microsoft Entra ID authentication is unrelated to failover.

Full explanation →
745
MCQhard

You are designing a disaster recovery solution for a mission-critical Azure SQL Database that requires a Recovery Point Objective (RPO) of 5 seconds and a Recovery Time Objective (RTO) of 30 seconds. Which configuration should you recommend?

A.Failover group with automatic failover and data loss threshold of 5 seconds.
B.Active geo-replication with manual failover.
C.Geo-restore of automated backups.
D.Zone-redundant database in the primary region.
AnswerA

Meets the required RPO and RTO.

Why this answer

Failover groups with automatic failover policy can achieve an RPO of 5 seconds (when the data loss threshold is set to 5 seconds) and an RTO of approximately 30 seconds. Active geo-replication (Option B) requires manual failover, which cannot meet the 30-second RTO. Geo-restore (Option C) has an RPO of 1 hour and RTO of 12 hours.

Zone-redundant databases (Option D) only protect against within-region failures, not region-level disasters.

Full explanation →
746
Multi-Selectmedium

Which TWO metrics should you monitor in Azure SQL Database to detect a potential memory pressure issue?

Select 2 answers
A.Page Life Expectancy
B.Memory Grants Pending
C.Average CPU percent
D.Log IO
E.Data IO
AnswersA, B

Low PLE indicates memory pressure.

Why this answer

Page Life Expectancy (PLE) is a key metric that indicates how long a data page remains in the buffer pool before being evicted. A consistently low PLE (e.g., below 300 seconds) signals that pages are being flushed too quickly due to memory pressure, often from insufficient buffer pool memory. Memory Grants Pending tracks the number of queries waiting for a memory grant to execute; a non-zero value indicates that the server cannot allocate enough memory to satisfy query workspace requirements, directly pointing to memory pressure.

Exam trap

The trap here is that candidates often confuse high CPU or I/O metrics with memory pressure, but CPU and I/O metrics reflect different resource bottlenecks, while PLE and Memory Grants Pending are the direct indicators of memory contention in Azure SQL Database.

Full explanation →
747
MCQeasy

You need to automate the creation of an Azure SQL Database and a corresponding server-level firewall rule to allow access from a specific IP address. The deployment must be repeatable and version-controlled. What should you use?

A.Create an ARM template that defines both the server firewall rule and the database.
B.Write a PowerShell script that uses New-AzSqlDatabase and New-AzSqlServerFirewallRule.
C.Use the Azure portal to create the database and firewall rule.
D.Use SQL Server Management Studio to script the creation.
AnswerA

ARM templates are ideal for repeatable deployments and version control.

Why this answer

ARM templates provide a declarative, Infrastructure-as-Code approach that is repeatable and can be version-controlled. Option B is wrong because while PowerShell can automate, it is procedural and not as easily version-controlled or idempotent as ARM templates. Option C is wrong because the Azure portal is manual and not repeatable.

Option D is wrong because SSMS scripting is also manual and not suitable for automated deployment.

Full explanation →
748
MCQeasy

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is consistently high, causing performance issues. Which metric should you monitor to identify the log generation rate?

A.Log IO percent
B.DTU used
C.CPU percent
D.Data IO percent
AnswerA

Measures log generation rate.

Why this answer

'Log IO percent' in Azure SQL Database reflects the log generation rate, which is critical for monitoring in the Hyperscale tier. Option B is incorrect because 'DTU used' is specific to DTU-based tiers, not Hyperscale. Option C is incorrect because 'CPU percent' measures processor usage, not log rate.

Option D is incorrect because 'Data IO percent' measures data file I/O, not log I/O.

Full explanation →
749
MCQhard

You are a database administrator for a large e-commerce company that uses Azure SQL Database for its transactional systems. The environment consists of 50 databases across 10 logical servers, each with a mix of General Purpose and Business Critical service tiers. The company has a strict requirement to automatically scale databases based on workload patterns to optimize cost without manual intervention. Specifically, during Black Friday sales, one of the Business Critical databases (DB-Sales) experiences a surge in transactions, and you need to temporarily upgrade it to a higher service objective (S9 instead of S6) for 48 hours. After the sale, it should automatically revert to S6. Additionally, you need to ensure that all databases have automated backups with a 35-day point-in-time restore retention and that backup storage costs are minimized by using geo-redundant storage only for critical databases. You have been asked to design an automation solution using Azure native services. Which approach should you recommend?

A.Use Elastic Job agents to run a script that alters the service objective and configure backup retention using the Azure portal.
B.Enable autoscale on the database to automatically adjust service objective based on CPU usage.
C.Create a SQL Server Agent job that runs ALTER DATABASE MODIFY (SERVICE_OBJECTIVE = 'S9') and configure backup retention using T-SQL.
D.Use Azure Automation Runbooks scheduled to run before and after Black Friday to change the service objective, and set backup retention policies using Azure Policy.
AnswerD

Azure Automation Runbooks can be scheduled to change service objectives, and Azure Policy can enforce backup retention.

Why this answer

Azure Automation Runbooks can be scheduled to execute PowerShell scripts that change the service objective of an Azure SQL Database (e.g., Set-AzSqlDatabase with the -RequestedServiceObjectiveName parameter). This allows scheduling a scale-up before Black Friday and a scale-down after 48 hours, meeting the temporary upgrade requirement. Additionally, Azure Policy can enforce backup retention policies (e.g., 35-day PITR) and specify geo-redundant storage only for critical databases, ensuring compliance and cost minimization.

Option A is incorrect because Elastic Job agents are for executing T-SQL scripts across many databases, not for scheduling one-off scaling actions, and backup retention via portal is not automated. Option B is incorrect because Azure SQL Database's autoscale feature is only available in the serverless compute tier, not for provisioned service objectives like S6/S9, and it adjusts based on workload automatically, not on a scheduled temporary upgrade basis. Option C is incorrect because SQL Server Agent is not available in Azure SQL Database (it's used in SQL Server on-premises or Azure SQL Managed Instance), and ALTER DATABASE MODIFY SERVICE_OBJECTIVE is not supported in Azure SQL Database; scaling requires Azure PowerShell or REST API.

Full explanation →
750
MCQmedium

You are configuring Azure SQL Database firewall rules for a new application. The application runs on Azure VMs in the same region. To minimize latency and security risk, which approach should you use?

A.Add a firewall rule allowing all Azure IP addresses.
B.Configure a virtual network service endpoint and a virtual network firewall rule.
C.Add a firewall rule for each VM's public IP address.
D.Add a firewall rule allowing all Azure services to access the database.
AnswerB

Service endpoints provide secure, low-latency connectivity from the VNet to Azure SQL.

Why this answer

Using a virtual network service endpoint and a virtual network firewall rule allows Azure SQL Database to accept traffic only from the specific subnet hosting the application VMs, without exposing the database to the public internet. This minimizes latency by keeping traffic within the Azure backbone network and reduces the security risk by eliminating broad IP-based rules.

Exam trap

The trap here is that candidates often confuse 'allowing Azure services' (a broad, insecure setting) with the more secure virtual network service endpoint approach, or they mistakenly think adding individual VM public IPs is sufficient for security and latency.

How to eliminate wrong answers

Option A is wrong because allowing all Azure IP addresses opens the database to any Azure service in any region, vastly increasing the attack surface and violating the principle of least privilege. Option C is wrong because assigning a firewall rule for each VM's public IP address is impractical for dynamic IPs, does not leverage Azure's private network, and still exposes the database to internet-based traffic. Option D is wrong because 'allowing all Azure services' is a legacy setting that permits traffic from any Azure service (e.g., Azure Functions, Logic Apps) without subnet-level control, creating unnecessary exposure.

Full explanation →
Page 9

Page 10 of 13

Page 11

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 →