Courseiva

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

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

Take a mock examExam hub
Page 8

Page 9 of 13

Page 10
601
MCQeasy

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

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

Dynamic Data Masking can be applied directly to sensitive columns, automatically masking data for all users except those with unmask permission, meeting all requirements.

Why this answer

Dynamic Data Masking (DDM) can be configured on sensitive columns to automatically mask data in query results without modifying application code or the data model. The database administrator can be added to the unmask permission to see the actual data. Option A is incorrect because creating views would require changes to the data model and application queries.

Option C is incorrect because Always Encrypted requires application code changes to handle encryption/decryption. Option D is incorrect because Row-Level Security filters rows based on predicates, not columns, and does not mask data.

Exam trap

Candidates often confuse Dynamic Data Masking with other security features like Always Encrypted or Row-Level Security. DDM masks data in query results at the database level without altering the underlying data or requiring application changes.

Full explanation →
602
MCQeasy

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

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

Query Store helps find queries consuming CPU and IO.

Why this answer

High average CPU (80%) and data IO (70%) suggest that the database is under sustained load from inefficient or resource-intensive queries. Query Store captures query execution plans, runtime statistics, and resource consumption per query, making it the fastest way to pinpoint the top resource consumers. Checking Query Store first allows you to identify the specific queries driving CPU and IO, which is the most direct diagnostic step.

Exam trap

The trap here is that candidates often jump to 'blocking and deadlocks' (Option C) because they associate high resource usage with concurrency issues, but sustained CPU and IO are far more commonly driven by inefficient queries rather than blocking.

How to eliminate wrong answers

Option A is wrong because long-running maintenance tasks (e.g., index rebuilds, statistics updates) typically cause periodic spikes rather than sustained average usage at 80% CPU and 70% IO, and they would be visible in job history or sys.dm_os_wait_stats. Option B is wrong because connection pooling issues (e.g., orphaned connections, pool exhaustion) manifest as connection timeouts or login failures, not as sustained high CPU and IO percentages. Option C is wrong because blocking and deadlocks primarily cause waits and timeouts, not consistently high CPU and IO; they would show high wait stats for locks but not necessarily the resource consumption levels described.

Full explanation →
603
MCQeasy

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

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

Private Link uses private IP addresses in your VNet.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

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

Full explanation →
604
MCQmedium

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

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

Logic Apps provide built-in scheduling and retry policies.

Why this answer

The most appropriate solution because Azure Logic Apps provides a recurrence trigger that can be scheduled to run at specific times (e.g., every Sunday at 2:00 AM UTC) and includes built-in retry policies to handle failures, ensuring resilience without custom code. Option B (Azure Automation Runbook) can be scheduled and can include custom error handling for retries, but it requires more manual implementation and is less integrated for simple retry logic. Option C (T-SQL Agent job) is not available in Azure SQL Database; Azure SQL Database does not support SQL Agent jobs natively.

Option D (Azure Function with timer trigger) requires custom retry logic in code, which is more complex than using Logic Apps' built-in retry policy.

Full explanation →
605
MCQeasy

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

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

Logic Apps can automate scaling without additional infrastructure.

Why this answer

Azure Logic Apps can be scheduled to run a workflow that uses the Azure SQL Database REST API (or Azure Resource Manager REST API) to update the service objective (DTU or vCore) of an Azure SQL Database. This enables automated scaling without custom infrastructure. Option A is incorrect because SQL Agent jobs run within the SQL Server context and cannot directly change the service objective; the ALTER DATABASE MODIFY (SERVICE_OBJECTIVE) command is not valid.

Option C is incorrect because Elastic Job agents are designed for executing T-SQL scripts across multiple databases, not for changing service tiers. Option D is incorrect because Azure SQL Database does not support autoscale; scaling must be performed manually or programmatically.

Full explanation →
606
MCQmedium

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
607
Multi-Selectmedium

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

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

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

Why this answer

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

Exam trap

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

Full explanation →
608
MCQmedium

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

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

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

Why this answer

You can create a T-SQL script that updates the job steps to remove linked server references and execute it via Azure Automation runbook. This allows automated execution against the SQL Managed Instance. Option A is incorrect because elastic jobs are used for scheduling tasks across multiple databases, not for modifying SQL Agent jobs directly.

Option C is incorrect because Azure CLI does not have native commands to manage SQL Agent jobs. Option D is incorrect because while Azure Automation can run PowerShell scripts, the T-SQL script approach is more direct and reliable for modifying job steps.

Full explanation →
609
MCQhard

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

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

Refactoring or using elastic query resolves the issue.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
610
Matchingmedium

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

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

Concepts
Matches

Asynchronous replication to a secondary region

Group of databases that fail over together

Replicas across different availability zones

Data replicated within a single datacenter

Why these pairings

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

Full explanation →
611
MCQhard

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

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

Azure Policy enforces compliance across all subscriptions automatically.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
612
MCQhard

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

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

Correct. A manual forced failover is immediate and reduces downtime without waiting for the grace period.

Why this answer

Initiating a manual forced failover immediately fails over to the secondary, minimizing downtime. Option A is incorrect because dropping and recreating the failover group introduces additional steps and downtime, and is unnecessary since a manual failover can be performed directly. Option B is incorrect because waiting for automatic failover would take up to 1 hour due to the grace period, causing longer downtime.

Option C is incorrect because altering the grace period to 0 and then waiting for automatic failover is slower than performing a manual forced failover, which provides the fastest recovery.

Full explanation →
613
MCQeasy

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

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

DMS online mode minimizes downtime by continuously replicating changes.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
614
MCQhard

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

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

Shows cause: workload increase or query regression.

Why this answer

(Impact) is correct because the Impact dimension in Intelligent Insights indicates whether the high resource utilization is caused by an increase in user workload or a degradation in query performance. Option A (Metric) is incorrect because Metric shows the metric name (e.g., 'Resource utilization') but not the root cause. Option B (Severity) is incorrect because Severity indicates the alert level (e.g., high, medium) and does not provide insight into the cause.

Option D (Resource type) is incorrect because Resource type shows the Azure resource type (e.g., SQL Database) and is not relevant to the cause of the alert.

Full explanation →
615
MCQeasy

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

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

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

Why this answer

The correct feature is a readable secondary in a failover group. This allows read-only queries to be routed to the secondary replica, offloading the reporting workload while maintaining high availability. SSIS (Option A) is an ETL tool, not for offloading read queries.

Transaction replication (Option C) requires complex configuration and is not native to Managed Instance. Option D is incorrect because an auto-failover group does not support a read-write secondary; the secondary is always read-only.

Full explanation →
616
Multi-Selecthard

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

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

Helps balance plan choice.

Why this answer

Options A, C, and D are correct. Option A (Optimize for ad hoc workloads or OPTIMIZE FOR UNKNOWN) helps by using average distribution instead of sniffing parameter values, reducing parameter-sensitive plan (PSP) problems. Option C (RECOMPILE hint) forces a new plan per execution, avoiding stale plans caused by parameter sniffing.

Option D (forced parameterization) creates multiple cached plans for different parameter values, directly addressing PSP. Option B (disabling parameter sniffing) is not a recommended approach and can lead to suboptimal plans. Option E (Query Store) is a monitoring tool that captures query data but does not directly reduce PSP frequency.

Full explanation →
617
MCQmedium

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
618
MCQhard

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

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

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

Why this answer

Deploying two Azure VMs in an availability set and configuring SQL Server Always On availability group with synchronous commit and automatic failover meets the requirements of high availability with automatic failover and no data loss during planned patching (by performing a manual failover). This solution minimizes cost compared to Azure SQL Database Managed Instance or geo-replication. Option A is incorrect because migrating to Azure SQL Database Managed Instance is a different platform, not SQL Server on Azure VM, and geo-replication is for disaster recovery, not planned patching.

Option B is incorrect because log shipping is not automatic and can result in data loss. Option C is incorrect because a single VM with FCI using Storage Spaces Direct does not provide automatic failover for planned patching; it requires a secondary node.

Full explanation →
619
MCQhard

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

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

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

Why this answer

In Azure Automation runbooks, there is no interactive desktop environment, so cmdlets that require a graphical user interface, such as Out-GridView, are not supported. When the script runs in a runbook, Out-GridView fails silently or does not produce any output, causing the subsequent alert logic (which likely depends on the output) to never trigger. Option A is incorrect because the -OutputAs parameter is used with Invoke-SqlCmd to specify the output format, not to return results for alerting.

Option C is incorrect because Invoke-SqlCmd is fully compatible with Azure SQL Database when proper authentication is used. Option D is incorrect because the script runs successfully, indicating the query syntax is valid.

Full explanation →
620
MCQhard

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

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

Metric alerts can trigger runbooks to scale the database.

Why this answer

Azure Monitor metric alerts can be configured to trigger an Azure Automation runbook that scales the Azure SQL Database when DTU consumption exceeds 80% for 5 minutes. This is a common pattern for autoscaling based on metrics. Option A (Azure SQL Analytics) is a monitoring solution that does not trigger actions.

Option B (Azure Automation Update Management) is for managing updates on virtual machines, not for scaling databases. Option C (SQL Server Agent) is not available in Azure SQL Database; it is used for on-premises SQL Server instances.

Full explanation →
621
MCQhard

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

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

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

Why this answer

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

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

Full explanation →
622
Multi-Selectmedium

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

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

Correct: Azure AD authentication is supported.

Why this answer

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

Exam trap

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

Full explanation →
623
MCQmedium

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

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

Synchronous replication ensures zero data loss.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
624
MCQhard

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

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

Synchronous commit ensures no data loss on failover.

Why this answer

To ensure no data loss during a failover in a distributed availability group (AG), you must configure synchronous commit mode on the distributed AG. In this mode, a transaction is committed only after it has been hardened on both the primary replica and the primary forwarder (the primary of the local AG). This guarantees that all committed data is available on the secondary side.

Option A is incorrect because backup preference does not affect data loss. Option B is incorrect because asynchronous commit can allow data loss if the primary fails before the data is replicated. Option D is incorrect because automatic seeding is only used for initial data synchronization, not for ongoing data protection.

Full explanation →
625
MCQhard

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

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

This completely removes public access and forces private connections.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
626
MCQmedium

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
627
MCQeasy

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

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

Automatically deletes blobs older than 90 days.

Why this answer

Azure SQL Database audit logs are stored in Azure Storage. A lifecycle management policy on the storage account can automatically delete blobs older than a specified number of days, such as 90 days, reducing storage costs. Option A is incorrect because Extended Events are not designed for automated deletion of audit logs.

Option B is incorrect because backup retention is unrelated to audit logs. Option C is incorrect because the audit retention policy in Azure SQL Database's server audit settings only controls how long audit records are retained in the database, not the actual deletion of stored logs.

Full explanation →
628
MCQeasy

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

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

Provides low RPO and fast RTO.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
629
MCQmedium

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

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

Elastic pools provide resource isolation and predictable performance per database.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
630
MCQeasy

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

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

RESOURCE_SEMAPHORE waits indicate memory grant pressure.

Why this answer

RESOURCE_SEMAPHORE waits indicate memory grant pressure, typically caused by queries requesting large memory grants. Option D is correct. Option A is incorrect because blocking and deadlocks produce LCK_M_* waits, not RESOURCE_SEMAPHORE.

Option B is incorrect because high CPU usage leads to SOS_SCHEDULER_YIELD waits. Option C is incorrect because disk I/O bottlenecks cause PAGEIOLATCH waits.

Full explanation →
631
MCQhard

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
632
MCQmedium

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

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

Zone-redundant deployment provides automatic failover for zone failures.

Why this answer

Deploying the VM in an availability zone and configuring an Always On availability group with a secondary replica in another zone ensures automatic failover if the primary zone fails. Option B is incorrect because an availability set only protects against failures within a datacenter, not zone-level failures. Option C is incorrect because geo-replication is designed for regional failure recovery, not zonal failover.

Option D is incorrect because automated backups with long-term retention do not provide failover capabilities.

Full explanation →
633
MCQmedium

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

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

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

Why this answer

Azure SQL Database active geo-replication supports manual failover only; automatic failover is not available. To automate the process, you can use Azure Automation runbooks that invoke the Start-AzSqlDatabaseFailover cmdlet. This allows for scheduled or monitored failover, supports testing by targeting the secondary replica, and minimizes data loss by ensuring the primary is synchronized before failover.

Option A is incorrect because Azure Traffic Manager performs DNS-level routing, not database failover. Option B is incorrect because Azure Load Balancer is for network traffic distribution, not SQL database failover. Option D is incorrect because geo-replication does not provide automatic failover; failover must be initiated manually or via automation.

Full explanation →
634
MCQeasy

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

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

Provides more CPU resources to handle the load.

Why this answer

Scaling up to a higher service tier (e.g., S4) provides more CPU resources, which directly addresses the high CPU usage. Option D is correct. Option A is incorrect because enabling read scale-out does not increase CPU capacity.

Option B is incorrect because automatic tuning may provide recommendations, but it is not the first action to take for immediate CPU pressure. Option C is incorrect because scaling down to a lower tier would reduce CPU resources, making the problem worse.

Full explanation →
635
Multi-Selecthard

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

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

Provides low latency and high availability.

Why this answer

The correct combination is Business Critical service tier (B) and failover groups with active geo-replication (C). Business Critical provides multiple read-write replicas and automatic failover within a region, enabling low RTO. Active geo-replication with failover groups extends this across regions, achieving the required RPO of 5 seconds and RTO of 30 seconds.

Zone-redundant configuration (E) only provides intra-region resilience, not cross-region disaster recovery. Geo-restore from GRS backups (D) has an RTO measured in hours. General Purpose (A) lacks the performance and replica architecture to meet the strict RTO/RPO.

Full explanation →
636
Multi-Selecthard

You are deploying Azure SQL Database and need to comply with regulatory requirements that mandate separation of duties for database administration. Specifically, the security team should manage access policies, and the database administrators should manage the data. Which THREE features should you use to implement this?

Select 3 answers
A.Firewall rules to restrict administrative access to specific IP addresses.
B.Transparent Data Encryption (TDE) with customer-managed keys.
C.Dynamic Data Masking to hide sensitive data from non-privileged users.
D.Azure Role-Based Access Control (RBAC) to assign distinct roles.
E.Database-level roles such as db_securityadmin and db_owner.
AnswersC, D, E

Can be used to restrict DBAs from viewing sensitive data.

Why this answer

Dynamic Data Masking (C) is correct because it allows database administrators to manage data while hiding sensitive information from non-privileged users, supporting separation of duties by preventing security team members from viewing actual data. Azure RBAC (D) is correct because it enables distinct role assignments (e.g., SQL Security Manager vs. SQL DB Contributor) to separate who manages access policies from who manages data.

Database-level roles (E) are correct because they provide granular permissions within the database, such as db_securityadmin for managing security policies and db_owner for full data management, enforcing separation at the database level.

Exam trap

The trap here is that candidates often confuse network-level controls (firewall rules) or encryption features (TDE) with access control and role separation mechanisms, failing to recognize that separation of duties requires distinct role assignments and permission boundaries, not just data protection or network restrictions.

Full explanation →
637
Multi-Selecteasy

Which TWO of the following are valid options for migrating an on-premises SQL Server database to Azure SQL Database with minimal downtime?

Select 2 answers
A.Use BULK INSERT to export data from on-premises and import to Azure SQL Database.
B.Configure transactional replication between on-premises SQL Server and Azure SQL Database.
C.Use Azure Database Migration Service with online migration mode.
D.Use SQL Server backup to URL and restore to Azure SQL Database.
E.Use the Generate Scripts wizard in SQL Server Management Studio.
AnswersB, C

Correct: Transactional replication provides continuous data synchronization with minimal downtime.

Why this answer

Transactional replication allows continuous synchronization of changes from the on-premises SQL Server publisher to the Azure SQL Database subscriber, enabling migration with minimal downtime by cutting over only after data is in sync. This is a supported hybrid scenario for Azure SQL Database.

Exam trap

The trap here is that candidates confuse Azure SQL Database with Azure SQL Managed Instance or SQL Server on Azure VM, where backup/restore and BULK INSERT are valid; Azure SQL Database does not support native backup/restore or transactional replication as a publisher, only as a subscriber.

Full explanation →
638
MCQeasy

You need to configure Azure SQL Database to allow connections only from Azure services and from a specific on-premises IP range. Which firewall rule configuration should you apply at the server level?

A.Create a private endpoint for the server.
B.Create a virtual network service endpoint and add a VNet firewall rule.
C.Set 'Allow Azure Services and resources to access this server' to ON and add a firewall rule for the on-premises IP range.
D.Set 'Allow Azure Services and resources to access this server' to OFF and add a firewall rule for the on-premises IP range.
AnswerC

Allows Azure services and on-premises traffic.

Why this answer

Enabling 'Allow Azure Services and resources to access this server' permits connections from all Azure services (including those from other subscriptions) by adding a special firewall rule that allows Azure IP ranges. Adding a separate firewall rule for the specific on-premises IP range then restricts non-Azure external traffic to only that range. This combination meets the requirement to allow only Azure services and the specified on-premises range.

Exam trap

The trap here is that candidates often think 'Allow Azure Services' must be OFF to secure the database, but they miss that the requirement explicitly asks to allow connections from Azure services, making ON necessary, and then they forget to add the on-premises IP rule separately.

How to eliminate wrong answers

Option A is wrong because a private endpoint assigns a private IP from a virtual network to the database, which does not inherently allow connections from all Azure services or from an on-premises IP range; it requires DNS configuration and VPN/ExpressRoute for on-premises access. Option B is wrong because a virtual network service endpoint and VNet firewall rule allow traffic only from a specific VNet/subnet, not from all Azure services, and it does not directly support on-premises IP ranges without additional VPN/ExpressRoute. Option D is wrong because setting 'Allow Azure Services and resources to access this server' to OFF blocks all Azure service connections, including those from other Azure services, which contradicts the requirement to allow connections from Azure services.

Full explanation →
639
MCQeasy

You need to monitor the long-running queries in an Azure SQL Database. Which dynamic management view should you query to see queries that have been running for more than 30 seconds?

A.sys.dm_exec_requests
B.sys.dm_db_resource_stats
C.sys.dm_exec_sessions
D.sys.dm_exec_query_stats
AnswerA

Includes start_time and total_elapsed_time for each request.

Why this answer

Sys.dm_exec_requests returns currently executing requests, including their start time and elapsed time, which can be used to identify queries running longer than 30 seconds. Option B (sys.dm_db_resource_stats) is incorrect because it provides resource consumption metrics (CPU, IO, memory) over time, not currently running queries. Option C (sys.dm_exec_sessions) is incorrect because it shows active sessions but does not include query execution details like elapsed time.

Option D (sys.dm_exec_query_stats) is incorrect because it contains aggregated query performance statistics, not currently running queries.

Full explanation →
640
Multi-Selectmedium

Which TWO of the following are benefits of using Azure SQL Database failover groups compared to active geo-replication? (Choose Two.)

Select 2 answers
A.Failover groups allow you to fail over multiple databases simultaneously.
B.Failover groups provide a read-write listener endpoint that remains unchanged after a failover.
C.Failover groups automatically fail over without any manual intervention.
D.Failover groups provide a readable secondary replica for read-only workloads.
E.Failover groups eliminate the need for geo-redundant storage.
AnswersA, B

Correct.

Why this answer

Options A and B are correct. Failover groups allow multiple databases to fail over together as a unit (A), which is not possible with active geo-replication where each database must be failed over individually. Failover groups also provide a read-write listener endpoint that remains unchanged after a failover (B), simplifying application connection strings.

Option C is incorrect because failover groups can be configured for automatic failover but do not automatically fail over without any manual intervention or a defined auto-failover policy. Option D is incorrect because both failover groups and active geo-replication provide readable secondary replicas, so this is not a distinct benefit. Option E is incorrect because failover groups still rely on geo-redundant storage; they do not eliminate the need for it.

Full explanation →
641
MCQhard

You are migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database is 2 TB in size and has a high transaction rate. You need to minimize downtime during migration. The source server is running SQL Server 2016 Enterprise Edition. Which migration approach should you recommend?

A.Use Azure Database Migration Service with online mode.
B.Export the database as a BACPAC file and import it into the managed instance.
C.Perform a full backup and restore to the managed instance, then apply log backups.
D.Set up transactional replication from on-premises to the managed instance.
AnswerA

DMS online mode provides near-zero downtime and handles large databases.

Why this answer

Azure Database Migration Service (DMS) with online mode is the correct choice because it supports minimal-downtime migrations for large databases with high transaction rates. It uses continuous data synchronization via change tracking or log shipping to keep the target nearly in sync, allowing a brief cutover window. For a 2 TB database, this avoids the lengthy downtime required by offline methods.

Exam trap

The trap here is that candidates often confuse 'online migration' with 'minimal downtime' and incorrectly assume that transactional replication (Option D) is the best approach for a full database migration, when in fact DMS online mode is the recommended service for large, high-transaction databases to Azure SQL Managed Instance.

How to eliminate wrong answers

Option B is wrong because exporting a 2 TB database as a BACPAC file is an offline process that would cause extended downtime, and BACPAC is not designed for high-transaction workloads due to performance bottlenecks and lack of incremental sync. Option C is wrong because performing a full backup and restore followed by log backups is an offline migration method; while log backups can reduce data loss, the initial restore requires the database to be offline, and applying subsequent log backups still incurs downtime during the final restore. Option D is wrong because transactional replication requires schema and data modifications to be replicated, adds complexity for a full database migration, and is not optimized for a one-time bulk migration; it is better suited for ongoing synchronization of specific objects rather than a complete database move.

Full explanation →
642
MCQeasy

You are reviewing an Azure CLI command that creates an elastic job step. The job step is intended to rebuild all indexes on the Sales.Orders table, but the job fails. What is the error in the JSON configuration?

A.The command is missing a semicolon at the end.
B.The target group should specify 'SqlServer' type instead of 'SqlDatabase'.
C.The command should be 'REBUILD INDEX' instead of 'ALTER INDEX'.
D.The job agent version is incompatible with the command.
AnswerA

T-SQL statements should end with a semicolon in elastic job steps.

Why this answer

The T-SQL command 'ALTER INDEX ALL ON Sales.Orders REBUILD' must end with a semicolon to be valid in an Azure Elastic Job step. Without the semicolon, the job fails. Option B is incorrect because the target group type for a single database is 'SqlDatabase'.

Option C is incorrect because 'ALTER INDEX ... REBUILD' is the correct syntax. Option D is incorrect because there is no version incompatibility.

Full explanation →
643
Multi-Selectmedium

Your organization has a compliance requirement to automatically classify and protect sensitive data in Azure SQL Database. You need to configure Microsoft Purview to scan and classify the database. Which THREE actions are required?

Select 3 answers
A.Run a scan to discover and classify sensitive data.
B.Create a scan rule set that includes the classification rules for sensitive data types.
C.Register the Azure SQL Database as a data source in Microsoft Purview.
D.Create a Microsoft Entra ID application and grant it access to the database.
E.Enable Microsoft Defender for SQL to automatically classify data.
AnswersA, B, C

The scan performs the classification.

Why this answer

Running a scan is the primary action that discovers and classifies sensitive data in Azure SQL Database using Microsoft Purview. The scan applies the classification rules from the scan rule set to identify patterns like credit card numbers or social security numbers, and then labels the data accordingly for compliance.

Exam trap

The trap here is that candidates often confuse Microsoft Defender for SQL's data classification feature (which is a separate, simpler classification) with Microsoft Purview's full scanning and governance pipeline, leading them to incorrectly select Option E as a required action.

Full explanation →
644
Multi-Selecthard

Which TWO methods can be used to automate index maintenance in Azure SQL Database?

Select 2 answers
A.Azure Automation runbooks executing T-SQL scripts
B.SQL Server Agent jobs
C.Azure Data Factory pipelines
D.Elastic jobs with T-SQL steps
E.Automatic tuning
AnswersA, D

Runbooks can connect to Azure SQL Database and run maintenance scripts.

Why this answer

Azure Automation runbooks can execute T-SQL scripts against Azure SQL Database using the Invoke-SqlCmd cmdlet or similar, enabling scheduled index maintenance tasks such as rebuilding or reorganizing indexes. This method is fully supported in Azure SQL Database, which lacks SQL Server Agent, and allows for flexible, cloud-native automation.

Exam trap

The trap here is that candidates often assume SQL Server Agent (Option B) is available in Azure SQL Database, but it is only supported in Azure SQL Managed Instance, not the single database or elastic pool service tiers.

Full explanation →
645
MCQeasy

You need to create a new Azure SQL Database for a development environment. The development team requires the ability to restore the database to any point in time within the last 7 days. Which backup policy configuration should you choose?

A.Configure long-term retention (LTR) for 90 days
B.Configure point-in-time restore for 35 days
C.Use the default backup retention of 7 days
D.Configure point-in-time restore for 1 day
AnswerC

Default PITR is 7 days, which meets the requirement.

Why this answer

The default backup retention for Azure SQL Database is 7 days, which meets the requirement to restore to any point in time within the last 7 days without additional configuration. Point-in-time restore (PITR) is enabled by default with this retention period, allowing automatic backups and restores within that window. No further backup policy changes are needed for this development environment.

Exam trap

The trap here is that candidates often overthink and select a longer retention period (like 35 days) or a different feature (like LTR), not realizing the default 7-day retention already satisfies the exact requirement without extra configuration.

How to eliminate wrong answers

Option A is wrong because long-term retention (LTR) for 90 days is used for archival or compliance purposes beyond the default retention, not for a 7-day point-in-time restore requirement. Option B is wrong because configuring point-in-time restore for 35 days exceeds the required 7-day window and incurs additional storage costs unnecessarily. Option D is wrong because point-in-time restore for 1 day provides only a 1-day recovery window, which is insufficient for the 7-day requirement.

Full explanation →
646
MCQmedium

Refer to the exhibit. You execute this PowerShell script to automate database configuration. The script runs without errors, but the database remains in the 'Standard' edition with S2 performance level. What is the most likely reason?

A.The elastic pool does not exist
B.The -RequestedServiceObjectiveName parameter is misspelled
C.The database is being moved into an elastic pool, which overrides the edition and SLO
D.The script requires administrative privileges that the user does not have
AnswerC

When specifying ElasticPoolName, the database inherits the pool's service tier.

Why this answer

When the -ElasticPoolName parameter is specified, the database is moved into the elastic pool, and the -Edition and -RequestedServiceObjectiveName parameters are ignored because the elastic pool's service tier and performance level determine the database's settings. The script runs without errors, but the database remains at Standard S2 because the pool's tier overrides the specified edition and SLO. Option A is incorrect because the command succeeded, indicating the pool exists.

Option B is incorrect because the parameter is correctly spelled. Option D is incorrect because the script succeeded without privilege errors.

Full explanation →
647
MCQhard

Your company uses Azure SQL Managed Instance for a critical OLTP workload. You need to automate index maintenance for all databases in the instance without downtime. The solution must minimize performance impact during business hours. Which approach should you use?

A.Use Elastic Database Jobs to run index maintenance scripts on all databases during off-peak hours, with parallel execution throttled.
B.Use Azure Logic Apps with a SQL connector to run index maintenance on each database, with retry policies.
C.Create an Azure Automation Runbook that connects to each database and runs index maintenance sequentially.
D.Deploy Ola Hallengren’s IndexOptimize stored procedure in each database and schedule it via SQL Server Agent.
AnswerD

Ola Hallengren's IndexOptimize stored procedure is a widely used, efficient solution for index maintenance. SQL Server Agent is fully supported on Azure SQL Managed Instance and can be scheduled to run during off-peak hours with custom configuration to minimize performance impact.

Why this answer

Azure SQL Managed Instance fully supports SQL Server Agent, which can schedule Ola Hallengren's IndexOptimize stored procedure for index maintenance across all databases. This approach allows centralized management, scheduling during off-peak hours, and granular control over parallelism and throttling to minimize performance impact. Option A is incorrect because Elastic Database Jobs are not available on Azure SQL Managed Instance; they are designed for Azure SQL Database.

Option B is incorrect because Azure Logic Apps are not suited for executing T-SQL scripts across multiple databases with performance-sensitive throttling. Option C is incorrect because Azure Automation Runbooks would require complex orchestration and lack native scheduling and throttling capabilities compared to SQL Agent.

Full explanation →
648
MCQmedium

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert that 'Query performance degradation' was detected. After reviewing the details, you find that a specific query now has a higher duration and is using a different execution plan. What is the recommended first step to troubleshoot?

A.Restart the database to clear the plan cache
B.Increase the service tier of the database
C.Query the Query Store to compare the previous and current plans
D.Run the Database Engine Tuning Advisor
AnswerC

Query Store allows plan comparison and identification of regression.

Why this answer

Query Store captures execution plans and runtime statistics, allowing you to compare the previous plan with the current plan to identify plan regression. Option A is wrong because restarting the database is disruptive and doesn't address the root cause. Option B is wrong because increasing the service tier may not fix plan regression and is not the first step.

Option D is wrong because Database Engine Tuning Advisor focuses on index and query design, not on comparing plan changes.

Full explanation →
649
Multi-Selecteasy

You need to configure monitoring for an Azure SQL Database to meet the following requirements: - Alert when average DTU consumption exceeds 90% for 10 minutes. - Track failed logins. - Analyze query performance over the last 30 days. Which THREE Azure services or features should you use? (Choose three.)

Select 3 answers
A.SQL auditing
B.Automatic tuning
C.Query Store
D.Azure Monitor metric alerts
E.Azure SQL Assessment
AnswersA, C, D

Audit logs capture failed logins.

Why this answer

(SQL auditing) is correct because it logs failed login attempts. Option C (Query Store) is correct as it retains query performance data for historical analysis (e.g., 30 days). Option D (Azure Monitor metric alerts) is correct because it can monitor metrics like DTU and trigger alerts when thresholds are exceeded.

Option B (Automatic tuning) is wrong because it optimizes query execution plans, not monitoring. Option E (Azure SQL Assessment) is wrong; it provides best-practice recommendations, not monitoring.

Exam trap

Candidates often confuse the purpose of SQL auditing (security logging) with Query Store (performance tracking) or metric alerts. Remember: auditing tracks logins, Query Store tracks query performance, and metric alerts monitor resource utilization.

Full explanation →
650
MCQmedium

Your company uses Azure SQL Database with Microsoft Entra ID authentication. You need to ensure that only users with specific Azure AD roles can access the database. What should you configure?

A.Configure an Azure SQL Database firewall rule to allow only Azure AD IP addresses.
B.Assign the SQL Server Contributor role to Azure AD users.
C.Create SQL authenticated users with strong passwords.
D.Create contained database users mapped to Azure AD identities and grant appropriate permissions.
AnswerD

Contained database users allow mapping Azure AD users or groups to SQL permissions.

Why this answer

Azure SQL Database supports contained database users mapped to Azure AD identities, which allows you to grant granular permissions (e.g., db_datareader, db_owner) to specific Azure AD users or groups. This ensures that only those with the appropriate Azure AD roles (via group membership or direct assignment) can access the database, without relying on SQL Server logins or firewall rules.

Exam trap

The trap here is that candidates often confuse Azure RBAC roles (like SQL Server Contributor) with database-level permissions, mistakenly thinking that assigning a management role grants data access, when in fact it only controls the ability to manage the server resource.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database firewall rules control network access based on IP addresses, not Azure AD roles; they cannot restrict access to specific Azure AD identities. Option B is wrong because the SQL Server Contributor role is an Azure RBAC role that grants management access to the logical server resource, not data-level access to the database; it does not control who can query or authenticate to the database. Option C is wrong because SQL authenticated users bypass Azure AD authentication entirely, which contradicts the requirement to use Azure AD roles for access control.

Full explanation →
651
Multi-Selecthard

Which THREE factors should you consider when configuring automatic tuning for an Azure SQL Database? (Choose three.)

Select 3 answers
A.Whether to enable automatic plan guide creation.
B.Whether to allow automatic index dropping.
C.Whether to enable automatic statistics update.
D.Whether to force the last good plan for queries with plan regressions.
E.Whether to allow automatic index creation.
AnswersB, D, E

Automatic tuning can drop unused or duplicate indexes.

Why this answer

The correct answers are B, D, and E. Automatic tuning for Azure SQL Database includes the options 'CREATE INDEX' (option E), 'DROP INDEX' (option B), and 'FORCE LAST GOOD PLAN' (option D). Option A (automatically creating plan guides) is not part of automatic tuning; plan guides are a manual feature.

Option C (automatic statistics update) is a separate maintenance operation and not an automatic tuning option. Therefore, when configuring automatic tuning, you should consider enabling or disabling index creation, index dropping, and forcing the last good plan to address query plan regressions.

Full explanation →
652
MCQmedium

You have an Azure SQL Database that uses active geo-replication with a readable secondary in another region. You need to ensure that if a failover occurs, the application can automatically reconnect to the new primary with minimal code changes. What should you configure?

A.Manually update the DNS record to point to the new primary after failover.
B.Configure a failover group listener endpoint and use that in the connection string.
C.Use Azure Traffic Manager with priority routing to the secondary server.
D.Implement retry logic in the application to reconnect to the secondary server after failover.
AnswerD

This is correct because implementing retry logic in the application allows it to automatically detect the failover and reconnect to the new primary with minimal code changes, typically by using a connection string with 'ApplicationIntent=ReadWrite' and retry policies.

Why this answer

With active geo-replication, there is no automatic listener endpoint. To achieve automatic reconnection with minimal code changes, the application should implement retry logic in the connection string that includes the failover partner (secondary) and set ApplicationIntent=ReadWrite. When the primary is unavailable, the driver will automatically attempt to connect to the secondary.

Option A is incorrect because manually updating DNS is not automatic and requires manual intervention, which violates the 'minimal code changes' requirement. Option B is incorrect because a failover group listener endpoint is specific to failover groups, not active geo-replication; configuring a failover group would be a different setup. Option C is incorrect because using Azure Traffic Manager introduces additional complexity and is not considered minimal code change; it requires managing a separate DNS service.

Full explanation →
653
MCQhard

A company runs SQL Server 2019 on Azure Virtual Machines in an availability set. They need to achieve high availability for a critical database with automatic failover and no shared storage. The solution must minimize downtime during planned maintenance. What should they implement?

A.Configure Log Shipping to a secondary VM
B.Deploy a Failover Cluster Instance using Azure Shared Disks
C.Create an Always On Availability Group with an availability group listener
D.Use Database Mirroring with automatic failover
AnswerC

Always On AG provides automatic failover and no shared storage.

Why this answer

An Always On Availability Group (AG) with a listener provides high availability with automatic failover at the database level without requiring shared storage. This solution meets the requirement for automatic failover and minimizes downtime during planned maintenance by allowing manual failover to a synchronized secondary replica with minimal disruption.

Exam trap

The trap here is that candidates often confuse Failover Cluster Instances (which require shared storage) with Always On Availability Groups (which do not), or they overlook that Database Mirroring is deprecated and lacks the listener feature for seamless client redirection.

How to eliminate wrong answers

Option A is wrong because Log Shipping provides only manual failover and does not support automatic failover; it also typically results in longer downtime during failover. Option B is wrong because a Failover Cluster Instance using Azure Shared Disks requires shared storage, which is explicitly not allowed by the requirement for no shared storage. Option D is wrong because Database Mirroring with automatic failover requires a witness server and is deprecated in SQL Server 2019; it also does not support an availability group listener for client redirection, making it less suitable for minimizing downtime during planned maintenance.

Full explanation →
654
Multi-Selectmedium

Which TWO actions should you take to implement a secure environment for Azure SQL Database that meets the principle of least privilege?

Select 2 answers
A.Assign database roles instead of individual permissions.
B.Enable all database features for maximum functionality.
C.Use contained database users with Azure AD authentication.
D.Use server-level logins for all users.
E.Grant the db_owner role to all application users.
AnswersA, C

Roles simplify permission management and follow least privilege.

Why this answer

Assigning database roles instead of individual permissions (Option A) aligns with the principle of least privilege by grouping necessary permissions into predefined roles, reducing the risk of over-privileging and simplifying permission management. Contained database users with Azure AD authentication (Option C) eliminate the dependency on server-level logins, allowing authentication at the database level and enabling fine-grained access control without granting server-wide privileges.

Exam trap

The trap here is that candidates often confuse server-level logins with contained database users, assuming that server-level logins are required for all Azure SQL Database scenarios, when in fact contained users with Azure AD authentication provide a more secure, least-privilege-compliant alternative.

Full explanation →
655
MCQhard

You are reviewing an ARM template for Azure SQL Database. The exhibit shows a resource definition for Transparent Data Encryption (TDE). You need to ensure that the database uses customer-managed keys (CMK) stored in Azure Key Vault instead of service-managed keys. What additional configuration is required?

A.Modify the database resource to include a 'keyVaultUri' property.
B.Enable Always Encrypted on the database.
C.Set the TDE state to 'Disabled' and then re-enable with a customer key.
D.Add a resource of type 'Microsoft.Sql/servers/encryptionProtector' and reference the key vault key.
AnswerD

The encryption protector resource specifies the customer-managed key.

Why this answer

To use customer-managed keys (CMK) for Transparent Data Encryption (TDE) in Azure SQL Database, you must configure an encryption protector that points to the key in Azure Key Vault. This is done by adding a resource of type 'Microsoft.Sql/servers/encryptionProtector' to the ARM template, which sets the server-level encryption protector to use the specified key vault key. Option D correctly identifies this required resource, as TDE with CMK is managed at the server level, not directly on the database resource.

Exam trap

The trap here is that candidates mistakenly think TDE key configuration is a property of the database resource (like a 'keyVaultUri' property) or that toggling TDE state is required, when in fact the encryption protector is a separate server-level resource that must be explicitly defined in the ARM template.

How to eliminate wrong answers

Option A is wrong because the 'keyVaultUri' property is not a valid property on the database resource; the key vault reference is configured at the server level via the encryption protector resource. Option B is wrong because Always Encrypted is a separate feature for column-level encryption and does not affect TDE key management; it uses its own keys and is unrelated to switching TDE from service-managed to customer-managed keys. Option C is wrong because you cannot disable and re-enable TDE to switch to a customer key; the transition is done by setting the encryption protector to the key vault key without toggling the TDE state, and disabling TDE would expose data at rest.

Full explanation →
656
MCQmedium

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is consistently high. You need to reduce the log write latency. What should you do?

A.Enable data compression on the tables
B.Increase the log rate limit in the Hyperscale service tier
C.Enable Accelerated Database Recovery
D.Change the service tier to General Purpose
AnswerB

Increasing log rate limit improves log write throughput.

Why this answer

In the Hyperscale service tier, log write latency is directly influenced by the log rate limit. Increasing this limit allocates more I/O bandwidth to the log write path, reducing latency under high log generation. This is the correct action because the log rate limit is a configurable parameter in Hyperscale that controls how fast log records can be written to the log service.

Exam trap

The trap here is that candidates often confuse log write latency with data I/O latency and incorrectly choose data compression or ADR, not realizing that the Hyperscale tier has a dedicated log rate limit setting that directly controls log write performance.

How to eliminate wrong answers

Option A is wrong because data compression reduces storage size and I/O for data pages, but it does not affect the log write path or log rate limit; log writes are sequential and compression does not reduce log write latency. Option C is wrong because Accelerated Database Recovery (ADR) improves database recovery time and reduces transaction log space usage by versioning, but it does not directly reduce log write latency under a consistently high log rate. Option D is wrong because changing to General Purpose would move the database to a different architecture with lower I/O throughput and higher latency for log writes, worsening the problem.

Full explanation →
657
Multi-Selectmedium

You are tuning an Azure SQL Database workload. Which TWO actions can help reduce PAGELATCH_EX contention?

Select 2 answers
A.Increase MAXDOP
B.Add multiple data files to the filegroup
C.Use a columnstore index
D.Configure a readable secondary replica
E.Use hash partitioning on a table
AnswersB, E

Distributes allocation pages.

Why this answer

Adding multiple data files to the filegroup helps reduce PAGELATCH_EX contention by distributing page allocation activity across multiple files. When a single data file is used, all concurrent insert operations compete for the same allocation page (e.g., PFS, GAM, SGAM), causing exclusive latches. With multiple files, each file has its own set of allocation structures, allowing parallel allocation operations to proceed with less contention.

Exam trap

The trap here is that candidates often confuse PAGELATCH_EX contention with PAGEIOLATCH_EX (I/O-related waits) and incorrectly assume that adding indexes or changing parallelism settings will resolve the issue, when in fact the solution involves reducing contention on in-memory allocation structures through filegroup or partitioning strategies.

Full explanation →
658
Multi-Selecteasy

Which TWO Azure services can be used to automate the execution of T-SQL scripts on a schedule against Azure SQL Database?

Select 2 answers
A.Azure Logic Apps with SQL connector to execute stored procedures.
B.Azure Automation Hybrid Runbook Worker with PowerShell Invoke-SqlCmd.
C.Azure Functions with timer trigger and SqlConnection.
D.Elastic Database Jobs with T-SQL script execution.
E.Azure Data Factory with a SQL Server Stored Procedure activity.
AnswersB, D

Hybrid Runbook Worker can run scripts on schedule.

Why this answer

Options B and D are correct. Azure Automation Hybrid Runbook Worker can execute T-SQL scripts using PowerShell Invoke-SqlCmd on a schedule, making it suitable for automating script execution against Azure SQL Database. Elastic Database Jobs are specifically designed to run T-SQL scripts on a schedule across one or more Azure SQL databases.

Option A is incorrect because Azure Logic Apps with SQL connector is better suited for workflows and not primarily for scheduled T-SQL script execution. Option C is incorrect because Azure Functions, while capable of running code with timer triggers, is not the optimal service for scheduled T-SQL scripts; Elastic Database Jobs is the dedicated solution. Option E is incorrect because Azure Data Factory is focused on data movement and orchestration, not direct scheduled execution of T-SQL scripts.

Full explanation →
659
MCQhard

Your company uses Azure SQL Database with active geo-replication. You notice that the secondary database in a different region has a high log write latency. Users report that the primary database performance is normal. What is the most likely cause?

A.Insufficient log IOPS on the secondary database
B.Network latency between the primary and secondary regions
C.High CPU usage on the primary database
D.Excessive read workload on the secondary database
AnswerB

Geo-replication log shipping is affected by network latency.

Why this answer

Active geo-replication asynchronously replicates transaction log records from the primary to the secondary database. High log write latency on the secondary is most commonly caused by network latency between the regions. Option A is incorrect because the secondary database's log IOPS are not directly the issue; the log is applied from the primary.

Option C is incorrect because primary CPU usage does not directly impact secondary log write latency. Option D is incorrect because read workload on the secondary does not affect log write latency; the secondary applies log regardless of read activity.

Full explanation →
660
Multi-Selectmedium

You are designing an automation solution to deploy Azure SQL Database schema changes using CI/CD pipelines. The solution must support rollback if a deployment fails and must integrate with Azure DevOps. Which two components should you include in your pipeline?

Select 2 answers
A.Azure Automation runbook to execute T-SQL scripts.
B.Azure Data Factory pipeline to copy schema changes.
C.A PowerShell script that uses Invoke-SqlCmd to deploy to Azure SQL Managed Instance.
D.Azure SQL Database project with DACPAC deployment.
E.Azure SQL Database deployment task in Azure Pipelines.
AnswersD, E

using an Azure SQL Database project with DACPAC (data-tier application package) allows version-controlled, repeatable schema deployments and supports rollback by redeploying a previous DACPAC.

Why this answer

Using an Azure SQL Database project with DACPAC (data-tier application package) allows version-controlled, repeatable schema deployments and supports rollback by redeploying a previous DACPAC. Option E is correct because the Azure SQL Database deployment task in Azure Pipelines can deploy DACPACs or execute SQL scripts as part of a CI/CD pipeline, enabling rollback through previous pipeline runs. Option A is incorrect: Azure Automation runbooks are designed for operational tasks like incident remediation, not for CI/CD schema deployment.

Option B is incorrect: Azure Data Factory is an ETL service, not for schema change management. Option C is incorrect: although PowerShell with Invoke-SqlCmd can run scripts, it lacks built-in rollback support and is not a CI/CD component; also, Azure SQL Managed Instance is a different deployment option, but the question is about Azure SQL Database.

Full explanation →
661
MCQhard

Your organization is implementing Microsoft Purview for data governance. You need to scan an Azure SQL Database to automatically classify sensitive data. The security team requires that the scan use a managed identity and that the data classification be stored in Microsoft Purview. What should you configure on the Azure SQL Database?

A.Enable system-assigned managed identity on the database and grant it permissions to access Purview
B.Use SQL authentication with a dedicated login and firewall rule
C.Assign the Microsoft Purview managed identity the db_datareader role and configure the firewall to allow Purview IP ranges
D.Create a user-assigned managed identity and assign it to the database
AnswerC

Managed identity and firewall rules enable Purview to scan.

Why this answer

Microsoft Purview uses its own managed identity to authenticate to data sources when scanning. To scan an Azure SQL Database, you must grant Purview's managed identity the `db_datareader` role in the database to allow read access for classification, and you must configure the Azure SQL firewall to allow inbound connections from the Purview IP ranges (or enable 'Allow Azure services and resources to access this server'). This satisfies the security team's requirement to use a managed identity and store classification in Purview.

Exam trap

The trap here is that candidates mistakenly think they need to assign a managed identity to the Azure SQL Database itself (Option A or D), when in fact the scan uses Purview's own managed identity to authenticate to the database, requiring only role assignment and firewall configuration on the database side.

How to eliminate wrong answers

Option A is wrong because enabling a system-assigned managed identity on the Azure SQL Database itself is not required; Purview uses its own managed identity to connect, not the database's identity. Option B is wrong because SQL authentication with a dedicated login and firewall rule does not use a managed identity, violating the security team's requirement. Option D is wrong because creating a user-assigned managed identity and assigning it to the database is unnecessary; Purview's own managed identity (system-assigned or user-assigned) is used for authentication, and the database does not need its own identity for this scan.

Full explanation →
662
MCQmedium

You run the KQL query shown in the exhibit. The query returns a list of query IDs. What is the purpose of this query?

A.Identify queries consuming the most CPU.
B.Identify the top 10 queries by average duration.
C.Identify queries with the highest total wait time.
D.Identify queries with the highest execution count.
AnswerB

Averages avg_duration per query and returns top 10.

Why this answer

The KQL query in the exhibit uses `summarize` with `avg(duration)` and then `top 10 by avg_duration`, which explicitly calculates the average duration per query_id and returns the top 10 queries with the highest average duration. This directly matches the purpose of identifying the top 10 queries by average duration.

Exam trap

The trap here is that candidates often confuse 'average duration' with 'total CPU consumption' or 'total wait time,' but the query explicitly uses `avg(duration)` and not `sum(cpu_time)` or `sum(wait_time)`, making the correct answer solely about average duration.

How to eliminate wrong answers

Option A is wrong because the query does not include any CPU-related metrics such as `cpu_time` or `avg_cpu_time`; it only aggregates `duration`. Option C is wrong because the query does not reference `wait_time`, `wait_stats`, or any wait-related columns; it focuses solely on `duration`. Option D is wrong because the query uses `avg(duration)` and not `count()` or `dcount()` to measure execution count; it ranks by average duration, not by how often a query ran.

Full explanation →
663
MCQhard

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 using TLS 1.2 or higher and that the server certificate be validated to prevent man-in-the-middle attacks. Additionally, the company wants to ensure that only applications running on Azure virtual machines (VMs) in a specific virtual network (VNet) can access the database. The VMs use a subnet named 'AppSubnet'. You have configured the following: 1. The server 'Allow Azure Services' setting is OFF. 2. A virtual network rule is added for 'AppSubnet' with the service endpoint for Microsoft.Sql enabled. 3. The server firewall has no other rules. 4. The 'Minimum TLS version' is set to 1.2. 5. All client applications are configured to use 'Encrypt=True' and 'TrustServerCertificate=False' in their connection strings. After deployment, you discover that connections from the VMs are failing with error: 'The certificate chain was issued by an authority that is not trusted'. What is the most likely cause of this issue?

A.The client applications are using an older version of .NET that does not support TLS 1.2.
B.The virtual network rule is missing the service endpoint for Microsoft.Sql on the subnet.
C.The server firewall is blocking traffic from the VNet because 'Allow Azure Services' is OFF.
D.The VMs do not have the necessary root CA certificates installed to validate the Azure SQL Database server certificate.
AnswerD

Correct: The certificate chain validation fails if root CAs are missing.

Why this answer

The error 'The certificate chain was issued by an authority that is not trusted' indicates that the client cannot validate the Azure SQL Database server certificate. With 'TrustServerCertificate=False', the client must trust the server's certificate chain, which requires the root CA certificates (e.g., Baltimore CyberTrust Root) to be installed in the VM's trusted root store. Since the VMs are in Azure, they typically have these certificates, but custom images or hardened VMs may lack them, causing the validation failure.

Exam trap

The trap here is that candidates confuse a certificate trust error with network connectivity or TLS version issues, assuming the problem is with the firewall or TLS configuration rather than the client's certificate store.

How to eliminate wrong answers

Option A is wrong because the error is about certificate trust, not TLS version support; the 'Minimum TLS version' is set to 1.2 and modern .NET versions support it, so an older .NET version would cause a different error (e.g., 'could not establish trust relationship'). Option B is wrong because the scenario states the service endpoint for Microsoft.Sql is already enabled on the subnet, and the virtual network rule is added; missing the endpoint would cause a connectivity timeout or access denied, not a certificate trust error. Option C is wrong because 'Allow Azure Services' OFF only affects connections from outside the VNet (e.g., Azure services not in the VNet); the VNet rule explicitly allows traffic from AppSubnet, so the firewall is not blocking it.

Full explanation →
664
MCQhard

Your company runs a mission-critical database on Azure SQL Managed Instance in the East US region. To comply with a new regulatory requirement, you must ensure that the database can be recovered within 15 minutes in the event of a regional disaster, with a maximum data loss of 10 seconds. You also need to minimize compute costs during normal operations. What should you configure?

A.Use a General Purpose instance with a failover group to a secondary instance in a paired region.
B.Configure a zone-redundant Business Critical instance in East US only, without a geo-secondary.
C.Deploy the primary instance as Business Critical with a zone-redundant secondary replica, and configure a failover group to a secondary instance in a paired region using Business Critical with a non-readable secondary.
D.Deploy the primary instance as Business Critical with a readable secondary replica, and configure a failover group to a secondary instance in a paired region using General Purpose.
AnswerC

This meets the RPO and RTO with minimal cost by avoiding an extra readable replica.

Why this answer

It combines synchronous data redundancy within the primary region via zone-redundant Business Critical, which provides automatic failover within the region for high availability, with an asynchronous geo-failover group to a paired region using a non-readable secondary replica. The Business Critical tier offers synchronous replication within the region, meeting the 10-second RPO, and the failover group enables geo-failover within 15 minutes (RTO). Using a non-readable secondary (no read scale-out) minimizes compute costs during normal operations.

Option A is wrong because General Purpose uses asynchronous replication, which cannot achieve a 10-second RPO. Option B is wrong because zone-redundancy alone does not protect against a full regional disaster. Option D is wrong because pairing a Business Critical primary with a General Purpose secondary would result in asynchronous replication between regions, risking higher RPO, and a readable secondary adds unnecessary cost.

Full explanation →
665
MCQmedium

You are configuring Azure SQL Database for a financial application that must meet PCI DSS compliance. The database contains credit card numbers stored in a column encrypted with Always Encrypted. You need to ensure that database administrators cannot view the plaintext credit card numbers while allowing application users with the proper credentials to access them. What should you implement?

A.Use column-level encryption with a certificate stored in the database
B.Enable Transparent Data Encryption (TDE)
C.Use Always Encrypted with secure enclaves and store the column master key in Azure Key Vault with access granted only to the application
D.Implement Dynamic Data Masking (DDM)
AnswerC

Always Encrypted ensures that only authorized clients can decrypt data, and secure enclaves allow computations without exposing data to DBAs.

Why this answer

Always Encrypted with secure enclaves ensures that credit card numbers are encrypted at all times, including during computation, and that the column master key is stored in Azure Key Vault with access granted only to the application. This prevents database administrators (DBAs) from viewing plaintext data because they never have access to the column master key, while authorized application users can decrypt the data via the client driver. This meets PCI DSS requirements for protecting cardholder data at rest and in use.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking (DDM) with encryption, mistakenly believing that masking prevents administrators from seeing data, when in fact DDM can be bypassed by users with db_owner or CONTROL permissions, whereas Always Encrypted cryptographically enforces separation of duties.

How to eliminate wrong answers

Option A is wrong because column-level encryption with a certificate stored in the database allows DBAs with access to the database to retrieve the certificate and decrypt the data, violating the requirement that administrators cannot view plaintext credit card numbers. Option B is wrong because Transparent Data Encryption (TDE) encrypts data at rest but does not prevent DBAs or users with database access from viewing plaintext data when queried; it protects against physical theft of storage, not unauthorized access by administrators. Option D is wrong because Dynamic Data Masking (DDM) only obfuscates data at the query result level and does not encrypt the underlying data; DBAs with elevated permissions can bypass the mask and view plaintext values.

Full explanation →
666
MCQhard

You have configured a failover group using the JSON template shown. The primary server is server1 in East US, and the secondary server is server2 in West US. During a test, you notice that read-only queries against the secondary endpoint are failing. What is the most likely cause?

A.The read-only endpoint failover policy is disabled.
B.The database is not included in the failover group databases list.
C.The failover grace period is set too high.
D.The read-write endpoint failover policy is set to Automatic.
AnswerA

Disabled policy prevents read-only routing to secondary.

Why this answer

The read-only endpoint failover policy is set to Disabled, which prevents read-only queries from being routed to the secondary endpoint. Option B is incorrect because the database is included in the failover group databases list. Option C is incorrect because the failover grace period only affects automatic failover, not read-only routing.

Option D is incorrect because the read-write endpoint failover policy is Automatic, which is not related to the read-only query failure.

Full explanation →
667
MCQhard

You are a database administrator for an Azure SQL Database. You need to allow a user to restore a database from a backup to a new database, but the user should not have permission to delete the original database. What is the minimum permission required?

A.db_backupoperator database role
B.db_owner database role
C.CREATE DATABASE permission on the logical server
D.SQL Server Contributor role
AnswerC

CREATE DATABASE permission allows restoring a backup as a new database without delete permissions.

Why this answer

The minimum permission required to restore a database from a backup to a new database in Azure SQL Database is the CREATE DATABASE permission on the logical server. This is because restoring a backup to a new database is essentially creating a new database, and the user does not need any additional permissions on the original database to perform this action. The db_backupoperator and db_owner roles grant excessive permissions on the original database, while the SQL Server Contributor role is an Azure RBAC role that provides broader management capabilities beyond what is needed.

Exam trap

The trap here is that candidates often assume database-level roles like db_backupoperator are sufficient for restore operations, but in Azure SQL Database, restoring to a new database requires server-level CREATE DATABASE permission, not just backup-related roles.

How to eliminate wrong answers

Option A is wrong because the db_backupoperator database role only allows backing up a database, not restoring it to a new database; restoring requires CREATE DATABASE permission at the server level. Option B is wrong because the db_owner database role provides full control over the database, including the ability to delete it, which exceeds the minimum required permission and violates the requirement to prevent deletion. Option D is wrong because the SQL Server Contributor role is an Azure RBAC role that grants permissions to manage the entire logical server, including deleting databases, and is not the minimum permission needed for a single restore operation.

Full explanation →
668
MCQeasy

You are reviewing a JSON representation of an Azure SQL Database firewall rule. What is the effect of this rule?

A.Blocks all IP addresses from 10.0.0.0 to 10.0.0.255.
B.Allows all IP addresses except 10.0.0.0 to 10.0.0.255.
C.Allows all IP addresses from 10.0.0.0 to 10.0.0.255.
D.Allows only the IP address 10.0.0.0.
AnswerC

Correct: This is the range defined.

Why this answer

The JSON representation of the Azure SQL Database firewall rule with startIpAddress '10.0.0.0' and endIpAddress '10.0.0.255' defines a range that allows all IP addresses from 10.0.0.0 to 10.0.0.255 inclusive. Azure SQL Database firewall rules use inclusive IP range matching, so any client with an IP in that range is permitted to connect, provided the rule is enabled.

Exam trap

The trap here is that candidates often confuse the inclusive range behavior with a single IP or assume that a range implies blocking, when in fact Azure SQL Database firewall only supports allow rules and the range is inclusive of both endpoints.

How to eliminate wrong answers

Option A is wrong because the rule allows, not blocks, the specified IP range; blocking would require a deny rule, which Azure SQL Database firewall does not support—only allow rules exist. Option B is wrong because the rule explicitly allows the range 10.0.0.0 to 10.0.0.255, not all IPs except that range; that behavior would require a default allow with a separate deny, which is not how Azure SQL firewall works. Option D is wrong because the rule specifies a range (start and end IP), not a single IP; a single IP rule would have identical start and end values (e.g., '10.0.0.0' for both).

Full explanation →
669
MCQmedium

Your Azure SQL Database is configured with the Hyperscale service tier. You observe increased redo log latency. Which resource is most likely the bottleneck?

A.Compute node CPU
B.Log service throughput
C.Page server IO
D.Remote storage IOPS
AnswerB

Log service is responsible for redo; latency there causes redo lag.

Why this answer

In Hyperscale, the log service is a separate component that handles redo; high latency there directly affects redo speed. Option A is wrong because compute node CPU affects query processing, not redo latency specifically. Option C is wrong because page servers handle data reads, not redo.

Option D is wrong because storage IO is distributed and rarely the bottleneck for redo in Hyperscale.

Full explanation →
670
MCQmedium

A company is planning to migrate their on-premises SQL Server databases to Azure SQL Managed Instance. They have a database that uses SQL Server Agent jobs with proxies and also uses cross-database queries extensively. What is the main consideration for this migration?

A.Migrate to Azure SQL Managed Instance as it supports SQL Agent and cross-database queries within the same instance.
B.Migrate to SQL Server on Azure Virtual Machines for full control.
C.Migrate to Azure SQL Database elastic query to handle cross-database queries.
D.Migrate to Azure SQL Database instead to reduce costs.
AnswerA

Azure SQL Managed Instance supports both SQL Agent with proxies and cross-database queries.

Why this answer

Azure SQL Managed Instance is the correct target because it provides full support for SQL Server Agent, including proxies, and enables cross-database queries within the same instance. Unlike Azure SQL Database, Managed Instance maintains instance-level scope, allowing queries that reference other databases in the same instance without requiring external data sources or elastic queries.

Exam trap

The trap here is that candidates may assume Azure SQL Database is always the cheaper or simpler option, overlooking that it lacks SQL Agent proxies and native cross-database query support, which are critical for this migration.

How to eliminate wrong answers

Option B is wrong because migrating to SQL Server on Azure Virtual Machines, while offering full control, is unnecessary when Managed Instance already supports the required features and reduces management overhead. Option C is wrong because Azure SQL Database elastic query is designed for querying remote databases across different servers or instances, not for native cross-database queries within the same instance, and it does not support SQL Agent proxies. Option D is wrong because Azure SQL Database does not support SQL Server Agent proxies and has limited cross-database query capabilities (only within elastic pools using elastic query), making it unsuitable for this workload.

Full explanation →
671
Multi-Selectmedium

You have an Azure SQL Database that runs a critical workload. You need to automate the monitoring of performance anomalies and receive notifications when certain thresholds are exceeded. Which TWO actions should you implement? (Choose two.)

Select 2 answers
A.Create a Power BI report that refreshes every minute.
B.Enable Query Store and set up email notifications for high query duration.
C.Configure a SQL Agent alert on performance counters.
D.Enable SQL Insights (preview) for intelligent performance monitoring.
E.Create an Azure Monitor alert rule on DTU/CPU and storage metrics.
AnswersD, E

SQL Insights provides advanced monitoring and anomaly detection.

Why this answer

Options D and E are correct. Enabling SQL Insights (preview) provides intelligent performance monitoring with built-in anomaly detection, and creating an Azure Monitor alert rule on DTU/CPU and storage metrics enables automated notifications when thresholds are exceeded. Option A is incorrect because Power BI is a reporting tool, not designed for real-time alerting on performance anomalies.

Option B is incorrect because Query Store tracks query performance but does not natively support email notifications. Option C is incorrect because SQL Agent alerts are for job scheduling events, not for monitoring performance counters like DTU or CPU.

Full explanation →
672
MCQhard

You manage a critical Azure SQL Database that supports a financial application. The database is configured with the Business Critical service tier (8 vCores) and uses Always On availability groups for high availability. Recently, the application team noticed that some transactions are experiencing high latency during writes. You investigate and find that the log write latency is averaging 10 ms, and there are occasional HADR_SYNC_COMMIT waits. The application requires durable writes with synchronous commit. You need to reduce write latency without compromising data durability. What should you do?

A.Switch to Premium SSD v2 storage
B.Disable automatic tuning to reduce overhead
C.Reduce the number of secondary replicas to minimize synchronization overhead
D.Increase the number of vCores to 16
AnswerD

More vCores increase log write throughput.

Why this answer

Increasing the number of vCores (Option D) provides additional CPU and memory resources for the log writer, which can process transactions faster, thereby reducing log write latency. In the Business Critical tier, the log write path is CPU-intensive, and scaling up directly improves throughput. Option A is incorrect because Premium SSD v2 is not applicable to Azure SQL Database; storage is managed by the service.

Option B is incorrect because disabling automatic tuning does not impact log write performance and may negatively affect overall query performance. Option C is incorrect because the number of secondary replicas is fixed at three in Business Critical, and reducing them is not possible; further, synchronous commit requires at least one secondary to ensure durability.

Full explanation →
673
Multi-Selectmedium

Which TWO of the following are valid methods to migrate an on-premises SQL Server database to Azure SQL Database?

Select 2 answers
A.Export the database as a BACPAC file and import into Azure SQL Database
B.Configure transactional replication to replicate changes to Azure SQL Database
C.Use log shipping to continuously copy transaction logs
D.Use the Data Migration Assistant (DMA) to perform an online migration
E.Restore a native SQL Server backup to Azure SQL Database
AnswersA, D

BACPAC is a supported migration method.

Why this answer

A BACPAC file is a logical backup that contains the database schema and data in a compressed format. Azure SQL Database supports importing BACPAC files directly via the Azure portal, PowerShell, or the SqlPackage utility, making this a straightforward and fully supported migration method.

Exam trap

The trap here is that candidates often assume native SQL Server backup restore (Option E) works in Azure SQL Database because it works in Azure SQL Managed Instance, but Azure SQL Database is a different PaaS tier that only supports BACPAC-based import/export.

Full explanation →
674
MCQhard

You are a database administrator for a financial services company. The company has multiple Azure SQL Managed Instances in different regions for disaster recovery. Each Managed Instance hosts several databases. You need to automate the process of backing up all databases and copying the backup files to a central Azure Blob Storage account for long-term retention. The backup must be taken daily at 10 PM local time for each region. The solution must be resilient to regional outages and must not use native backup retention more than 7 days. Additionally, you must ensure that backup files are encrypted at rest and in transit. What should you do?

A.Configure a SQL Agent job on each Managed Instance to perform backups to the central storage account directly using BACKUP TO URL.
B.Create an Azure Automation account in each region with a PowerShell runbook that connects to the local Managed Instance, performs a full database backup to a local blob container, and then copies the backup file to the central storage account using AzCopy. Schedule the runbook to run daily at 10 PM local time.
C.Use Elastic Database Jobs to schedule backups across all databases in all Managed Instances.
D.Enable Azure Backup for SQL Server in Azure Backup vault and configure backup policies for each Managed Instance.
AnswerB

Azure Automation runbooks can be scheduled per region and provide resiliency.

Why this answer

Azure Automation accounts in each region with PowerShell runbooks can be scheduled to run daily at 10 PM local time. The runbook connects to the local Managed Instance, performs a full database backup to a local blob container (using BACKUP TO URL or similar), and then uses AzCopy to copy the backup file to the central storage account. This approach is resilient to regional outages because each region has its own automation account.

Option A is incorrect because managing SQL Agent jobs manually across instances is less automated and less resilient; also, backing up directly to a central storage account may be affected by network issues. Option C is incorrect because Elastic Database Jobs are not available for Azure SQL Managed Instance. Option D is incorrect because Azure Backup for SQL Server is designed for SQL Server on Azure VMs, not for Managed Instance.

Full explanation →
675
MCQeasy

You are monitoring an Azure SQL Database using Intelligent Insights. The built-in intelligence detects a performance issue and suggests a specific index to create. The database is running the Business Critical service tier. You want to automatically implement this recommendation without manual intervention. What should you configure?

A.Enable automatic tuning for 'FORCE LAST GOOD PLAN' in the Azure portal.
B.Set up Query Store to capture the recommended index execution.
C.Configure Azure Advisor to email you the recommendation.
D.Enable automatic tuning for 'CREATE INDEX' in the Azure portal.
AnswerD

Automatic tuning can automatically create and drop indexes based on workload patterns.

Why this answer

Azure SQL Database's automatic tuning can automatically implement index recommendations when 'CREATE INDEX' is enabled. Option A is incorrect because 'FORCE LAST GOOD PLAN' is for plan correction, not index creation. Option B is incorrect because Query Store is a monitoring tool, not an automatic tuning feature.

Option C is incorrect because Azure Advisor only provides recommendations, not automatic implementation.

Full explanation →
Page 8

Page 9 of 13

Page 10

All pages

12345678910111213

Practice DP-300 by domain

Target a specific domain to shore up weak areas.

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