Knowledge + Practice

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

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

Take a mock exam Exam hub

Page 11 of 13

751

MCQhard

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

A.Configure an auto-failover group with a grace period of 1 hour.

B.Set up a PowerShell script that checks primary database health and initiates failover.

C.Use a ScheduledExecutorService in a Java application to monitor and failover.

D.Enable geo-replication and manually trigger failover when needed.

AnswerA

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

Why this answer

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

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

Full explanation →

752

MCQeasy

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

A.FORCE LAST GOOD PLAN

B.CREATE INDEX

C.DROP INDEX

D.FORCE LAST GOOD PLAN and DROP INDEX

AnswerA, B

This is a valid automatic tuning option.

Why this answer

Automatic tuning in Azure SQL Database includes FORCE LAST GOOD PLAN and CREATE INDEX. DROP INDEX is not an automatic tuning option. Option A and B are the correct ones.

Option C is wrong because automatic tuning does not include DROP INDEX. Option D is wrong because it includes DROP INDEX.

Full explanation →

753

MCQhard

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

A.Use Azure SQL Database backup and restore across regions with a recovery point objective (RPO) of 1 hour.

B.Configure an auto-failover group across two Azure regions with a grace period of 0 and include only the most critical database.

C.Use active geo-replication for each critical database and configure a custom health check to trigger failover.

D.Configure an auto-failover group across two Azure regions with a grace period of 1 hour and include all critical databases in the group.

AnswerD

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

754

MCQmedium

You have an Azure SQL Database that experiences performance degradation due to high concurrency and blocking. You need to implement a solution that offloads reporting queries to a read-only copy of the database without impacting the primary workload. What should you configure?

A.Add the database to an auto-failover group and use the readable secondary for reporting

B.Create a database snapshot and use it for reporting

C.Configure a geo-replica and use it for reporting

D.Enable read scale-out on the database by adding a secondary replica

AnswerA

Readable secondary can offload read queries.

Why this answer

Option A is correct because adding the database to an auto-failover group and using the readable secondary for reporting offloads read-only workloads to a synchronized replica without impacting the primary. The readable secondary is a built-in feature of Azure SQL Database that provides a read-only endpoint, and it is automatically kept in sync via transactional replication, ensuring near-real-time data for reporting while the primary handles write-heavy transactions.

Exam trap

The trap here is that candidates often confuse geo-replication (Option C) with the readable secondary in an auto-failover group, not realizing that geo-replication is for cross-region DR and not optimized for read scale-out within the same region, while the auto-failover group's secondary is the correct low-latency read offload solution.

How to eliminate wrong answers

Option B is wrong because a database snapshot is a point-in-time, static copy that does not reflect ongoing changes, making it unsuitable for real-time reporting and requiring manual refresh. Option C is wrong because a geo-replica is designed for disaster recovery across regions, not for offloading reporting within the same region; it introduces latency and additional cost without the same low-latency read scale-out benefits. Option D is wrong because enabling read scale-out on the database by adding a secondary replica is the same concept as the readable secondary in an auto-failover group, but the phrase 'adding a secondary replica' is ambiguous and not a standalone configuration; the correct implementation is through an auto-failover group, which provides the readable secondary endpoint.

Full explanation →

755

Multi-Selecteasy

Which TWO of the following are valid methods to authenticate to Azure SQL Database using Microsoft Entra ID? (Choose two.)

Select 2 answers

A.Microsoft Entra ID managed identity

B.Active Directory Federation Services (AD FS) token

C.Microsoft Entra ID integrated authentication

D.Microsoft Entra ID certificate-based authentication

E.Microsoft Entra ID password authentication

AnswersA, C

Supported for Azure resources.

Why this answer

Option A is correct because Microsoft Entra ID managed identity allows Azure resources, such as a virtual machine or an App Service, to authenticate to Azure SQL Database without storing credentials. The managed identity is automatically managed by Azure and can be assigned to a resource, which then uses OAuth 2.0 token-based authentication to access the database. This method is secure and eliminates the need for manual credential rotation.

Exam trap

The trap here is that candidates often confuse 'supported authentication methods' with 'identity providers' or 'credential types,' leading them to select password or certificate-based authentication as direct methods, when in fact Azure SQL Database only accepts token-based authentication via Entra ID.

Full explanation →

756

MCQeasy

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

A.The user is created but not granted any permissions.

B.The commands fail because Entra ID users cannot be created in Azure SQL Database.

C.A SQL Server authentication user is created and granted read access.

D.A Microsoft Entra ID user is created and granted read access to the database.

AnswerD

The user is from Entra ID and granted db_datareader.

Why this answer

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

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

Exam trap

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

How to eliminate wrong answers

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

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

Full explanation →

757

Multi-Selectmedium

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

Select 3 answers

A.Enable Microsoft Defender for Azure SQL to detect vulnerabilities.

B.Use Query Store to track query performance over time.

C.Query dynamic management views to identify blocking and resource waits.

D.Enable automatic tuning to automatically implement performance improvements.

E.Configure Microsoft Sentinel to monitor database activity.

AnswersB, C, D

Query Store provides historical query performance data.

Why this answer

Options A, C, and D are correct. Query Store, automatic tuning, and dynamic management views are core monitoring and optimization tools. Option B is for security.

Option E is for threat protection.

Full explanation →

758

Multi-Selecthard

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

Select 2 answers

A.Configure client applications to use TLS 1.2 in their connection strings.

B.Create a network security group rule to block non-TLS traffic.

C.Set 'DenyPublicNetworkAccess' to 'Yes' on the server.

D.Set the server's 'minimalTlsVersion' property to '1.2'.

E.Enable 'ForceEncryption' on the SQL Server instance.

AnswersA, D

Clients must also support TLS 1.2.

Why this answer

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

Exam trap

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

Full explanation →

759

Multi-Selectmedium

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

Select 2 answers

A.Use columnstore indexes on large tables

B.Set database compatibility level to 150

C.Increase the maximum storage size

D.Enable Query Store and monitor regressions

E.Enable Transparent Data Encryption (TDE)

AnswersA, D

Columnstore indexes improve analytics and large scan queries.

Why this answer

Options B and C are correct. Columnstore indexes (B) improve analytical query performance. Query Store (C) helps identify regressions.

Option A is wrong because enabling TDE does not improve performance. Option D is wrong because it increases storage but not performance. Option E is wrong because it's for compatibility, not optimization.

Full explanation →

760

MCQhard

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

A.Manually rotate the key in Key Vault every 90 days and update the database

B.Use Azure SQL Database server-side TDE with a service-managed key

C.Store the key in the database and use a custom rotation script

D.Use Azure Key Vault key rotation policy to automatically rotate the key every 90 days

AnswerD

Automatic rotation reduces administrative overhead.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

761

MCQhard

Refer to the exhibit. You review the configuration of an Azure Database for PostgreSQL flexible server. Which statement about this server is true?

A.The server can automatically increase storage.

B.Geo-redundant backups are enabled.

C.The server uses same-zone high availability.

D.The server is running PostgreSQL version 15.

AnswerA

autogrow is Enabled.

Why this answer

Option D is correct because the storage profile has autogrow enabled and storage size is 65536 MB (64 GB). Option A is wrong because version is 16, not 15. Option B is wrong because geoRedundantBackup is Disabled.

Option C is wrong because highAvailability mode is ZoneRedundant, meaning it uses a standby in another zone, not same zone.

Full explanation →

762

MCQeasy

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

A.Enable auditing on the database and set the audit log destination to an Azure Storage account.

B.Configure diagnostic settings to stream query store data to an event hub.

C.Enable Microsoft Defender for SQL and configure security alerts to be sent to a storage account.

D.Enable SQL Vulnerability Assessment and export the results to a storage account.

AnswerA

Auditing captures data modifications and can be stored in storage.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

763

Drag & Dropmedium

Drag and drop the steps to configure transparent data encryption (TDE) for an Azure SQL Database using a customer-managed key in Azure Key Vault in the correct order.

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

Steps

Order

Why this order

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

Full explanation →

764

MCQeasy

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

A.Concurrent inserts into a table with a clustered index causing last-page contention.

B.Insufficient buffer pool size leading to frequent reads from disk.

C.High CPU usage due to inefficient queries.

D.Long-running transactions blocking other queries.

AnswerA

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

Why this answer

Option A is correct because PAGELATCH waits indicate contention on data pages, often due to concurrent inserts to the same page. Option B is wrong because buffer pool insufficient is PAGEIOLATCH. Option C is wrong because blocking is related to lock waits.

Option D is wrong because CPU pressure is not directly related to page latches.

Full explanation →

765

MCQmedium

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

A.Enable geo-redundant backup storage and configure geo-restore.

B.Configure active geo-replication to a secondary instance in another region.

C.Set up log shipping to a secondary instance in another region.

D.Create an auto-failover group with a secondary instance in a paired region using synchronous replication.

AnswerD

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

Why this answer

Option B is correct because auto-failover groups for Managed Instance provide automatic failover and can achieve low RPO with synchronous replication. Option A is wrong because geo-redundant backup does not provide automatic failover. Option C is wrong because log shipping is not supported.

Option D is wrong because active geo-replication is not supported for Managed Instance.

Full explanation →

766

Multi-Selecthard

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

Select 3 answers

A.A job database

B.Target databases (members of the job group)

C.A job agent

D.SQL Agent

E.Azure Automation account

AnswersA, B, C

Stores job definitions and execution history.

Why this answer

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

Azure Automation is not required.

Full explanation →

767

MCQhard

You manage a critical Azure SQL Database that requires automated index maintenance. You need to ensure that index rebuilds only occur during low-usage periods defined in a schedule, while index reorganizations run more frequently. What is the most efficient way to implement this using Azure Automation?

A.Create an Elastic Database Job that runs a T-SQL script to rebuild and reorganize indexes based on fragmentation thresholds, scheduled via job schedule.

B.Configure SQL Agent on the Azure SQL Database logical server to run index maintenance jobs.

C.Use Azure Automation runbooks with the Invoke-Sqlcmd cmdlet to run index maintenance scripts on a schedule.

D.Deploy an Azure Function with a timer trigger that connects to the database and runs index maintenance commands.

AnswerA

Elastic Database Jobs are the recommended service for automating T-SQL tasks across one or many Azure SQL databases, with built-in scheduling and retry.

Why this answer

Azure Automation runbooks can execute T-SQL scripts, but they require maintaining a schedule and handling errors manually. Elastic Database Jobs are purpose-built for scheduling T-SQL against Azure SQL Databases, providing simple scheduling, retry logic, and built-in monitoring. Option A is correct.

Option B is less efficient because it requires more manual setup. Option C is not designed for this. Option D lacks scheduling.

Full explanation →

768

MCQmedium

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

A.Configure a point-to-site VPN connection for the developer.

B.Add a new firewall rule at the server level that allows the developer's IP address.

C.Update the existing firewall rule to include the developer's IP address.

D.Modify the database-level firewall rule to include the developer's IP.

AnswerB

New rule allows the specific IP without affecting existing rules.

Why this answer

Option C is correct because adding a new firewall rule for the specific IP address allows access without modifying existing rules. Option A is wrong because updating the existing range would change access for other IPs. Option B is wrong because modifying the server-level firewall rules is the correct approach but you should add, not modify.

Option D is wrong because VPN is overkill and not a temporary solution.

Full explanation →

769

MCQmedium

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

A.The failover policy is set to Automatic, but the grace period is too long.

B.The backup storage redundancy is Local, which does not provide geo-redundancy for backups.

C.The secondary managed instance mi2 is in the same region as the primary.

D.The read-only endpoint failover policy is Disabled, which prevents read-only traffic after failover.

AnswerB

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

Why this answer

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

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

Full explanation →

770

MCQmedium

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

A.Get, Decrypt, and Unwrap Key.

B.Get, Wrap Key, and Unwrap Key.

C.Get and Decrypt.

D.Get, Sign, and Verify.

AnswerB

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

Why this answer

To encrypt data with Always Encrypted, the developer's application must be able to retrieve the column master key metadata (Get) and then use the key to encrypt a column encryption key. This requires the Wrap Key operation, which generates a new encrypted column encryption key. Unwrap Key is needed only for decryption, not encryption.

Therefore, Get and Wrap Key are the minimum permissions for encryption, making Option B correct.

Exam trap

The trap here is that candidates often confuse the permissions needed for encryption versus decryption, mistakenly thinking Decrypt or Unwrap Key are required for encrypting data, when in fact only Wrap Key is needed for encryption.

How to eliminate wrong answers

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

Full explanation →

771

Multi-Selectmedium

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

Select 2 answers

A.Zone-redundant deployment for Business Critical tier

B.Database mirroring

C.Always On Availability Groups (built-in)

D.Log shipping to a secondary instance

E.Windows Server Failover Clustering

AnswersA, C

Correct: Managed Instance Business Critical tier supports zone redundancy.

Why this answer

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

Exam trap

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

Full explanation →

772

MCQmedium

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

A.Deploy a zone-redundant General Purpose instance in UK South only.

B.Keep the current instance and rely on geo-restore to UK West.

C.Upgrade both instances to Business Critical and configure a failover group.

D.Deploy a secondary General Purpose instance in UK West and configure a failover group.

AnswerD

General Purpose instances with failover group meet the RPO/RTO at lower cost.

Why this answer

Option B is correct because a failover group with General Purpose instances meets the RPO/RTO at minimal cost. Option A is wrong because zone-redundancy doesn't protect regionally. Option C is wrong because Business Critical is more expensive than needed.

Option D is wrong because manual restore cannot meet RTO.

Full explanation →

773

MCQmedium

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

A.Create a virtual network rule for the database.

B.Create a private endpoint for the database.

C.Create a server-level firewall rule with the application's IP.

D.Create a database-level firewall rule with the application's IP.

AnswerC

Server-level firewall rules allow specific IP addresses.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

774

MCQeasy

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

A.To enable geo-replication for the managed instance.

B.To configure automated backups of the managed instance to Azure Blob Storage.

C.To set up disaster recovery across Azure regions.

D.To configure point-in-time restore for the managed instance.

AnswerB

Managed backup stores backups in Azure Blob Storage.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

775

MCQmedium

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

A.Column-level security

B.Row-level security

C.Dynamic Data Masking

D.Always Encrypted

AnswerC

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

776

MCQeasy

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

A.The source database must have geo-redundant backup storage configured.

B.The source database must have point-in-time restore enabled.

C.The source database must be online at the time of restoration.

D.The target server must be in the same region as the source server.

AnswerA

Required for geo-restore.

Why this answer

Option B is correct because Restore-AzSqlDatabase with -FromGeoBackup requires that the source database has geo-redundant backups (geo-redundant storage) enabled. Option A is incorrect because the source database does not need to be online; geo-backups are taken automatically. Option C is incorrect because the command uses -FromGeoBackup, not -FromPointInTimeBackup.

Option D is incorrect because the target server can be different.

Full explanation →

777

MCQmedium

Refer to the exhibit. You retrieve the configuration of a SQL Server 2022 instance running in an Azure VM. Based on the JSON output, which feature is enabled?

A.Ledger tables

B.Always On availability groups

C.Intelligent Insights

D.Always Encrypted with secure enclaves

AnswerC

isIntelligentInsightsOn is true.

Why this answer

Option B is correct because isIntelligentInsightsOn is set to true. Option A is wrong because isLedgerOn is false. Option C is wrong because there is no indication of Always Encrypted with secure enclaves (preferredEnclaveType is VBS but that alone doesn't mean enabled; the feature must be configured on specific columns).

Option D is wrong because no availability group info is shown.

Full explanation →

778

Multi-Selectmedium

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

Select 2 answers

A.Azure Database Migration Service (online mode)

B.Back up to URL and restore

C.Export to BACPAC and import

D.Log shipping

E.Transactional replication

AnswersA, E

Online mode supports minimal downtime.

Why this answer

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

Exam trap

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

Full explanation →

779

MCQmedium

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

A.Use Azure Files share with read-only permissions

B.Send logs to Azure Log Analytics workspace

C.Store logs in an audit table in Azure SQL Database

D.Azure Blob Storage with immutable storage policy

AnswerD

Immutable blob storage prevents deletion and modification.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

780

MCQeasy

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

A.Add the public IP address range of the VNet to the firewall rules

B.Configure a virtual network service endpoint and add a firewall rule for the VNet subnet

C.Create a private endpoint and disable public network access

D.Set 'Deny public network access' to Yes

AnswerB

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

781

Multi-Selectmedium

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

Select 2 answers

A.Support for manual failover.

B.Failover of multiple databases in a single group.

C.Readable secondary replicas for read-scale workloads.

D.Automatic failover based on the grace period.

E.Synchronous data replication to the secondary.

AnswersB, D

Correct: Failover groups allow coordinated failover of multiple databases.

Why this answer

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

Exam trap

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

Full explanation →

782

MCQmedium

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

A.Enable Query Store to identify costly queries.

B.Implement read scale-out to offload read queries.

C.Configure automatic tuning to force plan regression fixes.

D.Scale up the database to a higher service tier (e.g., from S2 to S3).

AnswerD

Scaling up increases DTU limits, alleviating resource contention.

Why this answer

Option B is correct because scaling up the service tier to a higher DTU level provides more resources, reducing contention and timeouts. Option A is wrong because read scale-out only distributes read-only queries. Option C is wrong because Query Store is a monitoring tool, not a scaling solution.

Option D is wrong because automatic tuning can help with plan regression but not resource shortage.

Full explanation →

783

MCQmedium

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

A.Scale up the database to a higher service tier or increase DTU.

B.Enable Query Store to analyze top queries.

C.Rebuild all indexes in the database.

D.Do nothing; it's normal for DTU to be high.

AnswerA

High DTU consumption indicates need for more resources.

Why this answer

Option B is correct because high DTU indicates resource pressure; scaling up provides immediate relief. Option A is wrong because indexes may not be the cause. Option C is wrong because Query Store helps identify queries, but scaling is a direct solution.

Option D is wrong because waiting may cause further issues.

Full explanation →

784

Multi-Selecthard

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

Select 2 answers

A.Configure long-term retention (LTR) policy for the database.

B.Use Azure Automation or a scheduled Azure Function to call the Export-AzSqlDatabase cmdlet.

C.Deploy a SQL Server on Azure VM to run the export command.

D.Install SQL Server Integration Services (SSIS) on a virtual machine.

E.Create an Azure Storage account with a container to store the BACPAC file.

AnswersB, E

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

Why this answer

To export a database to BACPAC, you need an Azure Storage account to store the file. The export is initiated via the Azure portal, PowerShell, Azure CLI, or REST API. No SQL Server on VM is required.

Option B and Option D are correct. Option A is not required as the export is done by Azure. Option C is for SQL Server on VM.

Option E is a feature for automated backups, not exports.

Full explanation →

785

MCQmedium

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

A.Deploy a Business Critical tier database and configure an auto-failover group with a readable secondary in a paired region.

B.Deploy a General Purpose tier database with geo-replication.

C.Deploy a Hyperscale tier database with zone redundancy.

D.Deploy a Serverless tier database with active geo-replication.

AnswerA

Business Critical supports synchronous replication and auto-failover groups, ensuring zero data loss on failover.

Why this answer

Option C is correct because Azure SQL Database Business Critical tier with zone-redundant configuration provides the highest level of availability within a region, but for cross-region disaster recovery with zero data loss, you need auto-failover groups with a readable secondary in another region, which is supported in Business Critical and Premium tiers. However, the question asks for automatic failover with zero data loss; this is achieved by configuring auto-failover groups with synchronous replication, which is supported in Business Critical and Premium tiers. Option A (Hyperscale) supports zone redundancy but not synchronous replication for failover groups.

Option B (General Purpose) has asynchronous replication. Option D (Serverless) is not suitable for mission-critical workloads.

Full explanation →

786

MCQeasy

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

A.The key specified in the URI does not exist

B.The user-assigned managed identity is not assigned to the SQL Database server

C.The Key Vault firewall is enabled and does not allow Azure services

D.The managed identity lacks 'Get', 'Wrap Key', and 'Unwrap Key' permissions on the Key Vault key

AnswerD

These permissions are required for TDE with CMK.

Why this answer

Option A is correct because the managed identity needs to have 'Get, Wrap Key, Unwrap Key' permissions on the Key Vault key. Without those, the SQL Database cannot access the key. Option B is wrong because Key Vault firewall rules could block access, but the most common issue is missing permissions.

Option C is wrong because the key exists. Option D is wrong because the identity is assigned correctly.

Full explanation →

787

MCQhard

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

A.Create a failover group with automatic failover policy.

B.Create an auto-failover group using Azure CLI and set cross-geography replication.

C.Configure active geo-replication and set auto-failover policy.

D.Configure an always failover group with Microsoft Entra ID authentication.

AnswerA

Correct: Failover groups support automatic failover across regions.

Why this answer

Option B is correct because Azure SQL Database failover groups support automatic failover when paired with auto-failover policy. Active geo-replication alone does not provide automatic failover. Option A is incorrect because active geo-replication requires manual failover.

Option C is incorrect because always failover groups are not a feature. Option D is incorrect because auto-failover groups do not support cross-geography failover automatically without manual setup.

Full explanation →

788

MCQhard

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

A.Failover group with automatic failover and data loss threshold of 5 seconds.

B.Active geo-replication with manual failover.

C.Geo-restore of automated backups.

D.Zone-redundant database in the primary region.

AnswerA

Meets the required RPO and RTO.

Why this answer

Option D is correct because failover groups with automatic failover policy can achieve RPO of 5 seconds and RTO of 30 seconds when configured correctly. Option A is wrong because active geo-replication alone does not provide automatic failover. Option B is wrong because zone-redundant configuration only protects within a region, not from region-level disaster.

Option C is wrong because geo-restore has RPO of 1 hour and RTO of 12 hours.

Full explanation →

789

Multi-Selectmedium

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

Select 2 answers

A.Page Life Expectancy

B.Memory Grants Pending

C.Average CPU percent

D.Log IO

E.Data IO

AnswersA, B

Low PLE indicates memory pressure.

Why this answer

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

Exam trap

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

Full explanation →

790

MCQeasy

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

A.Create an ARM template that defines both the server firewall rule and the database.

B.Write a PowerShell script that uses New-AzSqlDatabase and New-AzSqlServerFirewallRule.

C.Use the Azure portal to create the database and firewall rule.

D.Use SQL Server Management Studio to script the creation.

AnswerA

ARM templates are ideal for repeatable deployments and version control.

Why this answer

Option B is correct because ARM templates are declarative and version-controlled. Option A is wrong because the Azure portal is manual. Option C is wrong because SSMS is manual.

Option D is wrong because PowerShell is procedural, not the best for version-controlled repeatable deployments.

Full explanation →

791

MCQeasy

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

A.Log IO percent

B.DTU used

C.CPU percent

D.Data IO percent

AnswerA

Measures log generation rate.

Why this answer

Option B is correct because 'Log IO percent' in Azure SQL Database reflects log generation rate. Option A is wrong because 'Data IO percent' measures data file I/O. Option C is wrong because 'CPU percent' measures processor usage.

Option D is wrong because 'DTU used' is for DTU-based tiers, not Hyperscale.

Full explanation →

792

MCQhard

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

A.Use Elastic Job agents to run a script that alters the service objective and configure backup retention using the Azure portal.

B.Enable autoscale on the database to automatically adjust service objective based on CPU usage.

C.Create a SQL Server Agent job that runs ALTER DATABASE MODIFY (SERVICE_OBJECTIVE = 'S9') and configure backup retention using T-SQL.

D.Use Azure Automation Runbooks scheduled to run before and after Black Friday to change the service objective, and set backup retention policies using Azure Policy.

AnswerD

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

Why this answer

Option B is correct because Azure Automation Runbooks can be scheduled to change the service objective of a database, and policies for backup retention are set at the server level but can be overridden per database using ARM templates or PowerShell. Option A is wrong because elastic jobs are for executing scripts across databases, not for scheduling scaling. Option C is wrong because autoscale is not available for Azure SQL Database.

Option D is wrong because SQL Agent cannot change service objectives or set backup retention.

Full explanation →

793

MCQmedium

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

A.Add a firewall rule allowing all Azure IP addresses.

B.Configure a virtual network service endpoint and a virtual network firewall rule.

C.Add a firewall rule for each VM's public IP address.

D.Add a firewall rule allowing all Azure services to access the database.

AnswerB

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

794

MCQhard

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

A.Configure SQL Server Managed Backup to Microsoft Azure.

B.Install Azure Backup Server on-premises and configure backup of SQL Server databases.

C.Use SQL Server Agent jobs to perform full, differential, and log backups to an Azure Blob Storage URL.

D.Use Azure Data Factory to copy database backups to Blob Storage.

AnswerA

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

795

Multi-Selecthard

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

Select 3 answers

A.Number of vCores available.

B.Memory-to-vCore ratio.

C.IOPS and throughput limits.

D.Compute generation and CPU architecture.

E.Support for Business Critical tier.

AnswersB, C, D

Premium-series often has higher memory per vCore.

Why this answer

Options A, B, and D are correct. Gen5 and Premium-series differ in IOPS, memory-to-vCore ratio, and compute generation. Option C is wrong because vCore count is not a factor; it's independent of hardware series.

Option E is wrong because both support the same service tiers.

Full explanation →

796

MCQhard

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

A.Configure auto-failover groups between two Azure regions.

B.Configure Active Geo-Replication with a readable secondary in a different Azure region and add to a failover group.

C.Deploy the database as zone-redundant within a single region.

D.Use geo-restore of automated backups to recover in another region.

AnswerB

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

797

MCQhard

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

A.The secondary database is experiencing high connection requests

B.The primary database is not receiving any write transactions

C.The failover group is experiencing frequent automatic failovers

D.Replication lag between primary and secondary is high

AnswerD

FAILOVER_GROUP_WAIT measures time spent waiting for replication to complete.

Why this answer

Option C is correct. 'FAILOVER_GROUP_WAIT' occurs when the secondary is behind due to replication lag. High wait times suggest significant replication lag. Option A is incorrect because the query does not indicate failover frequency.

Option B is incorrect because it measures wait, not transaction throughput. Option D is incorrect because it is not a connection wait.

Full explanation →

798

MCQeasy

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

A.Use Azure Data Factory to scale the database based on pipeline triggers.

B.Create an Azure Automation runbook that scales the database on a schedule.

C.Configure autoscale settings for the Azure SQL Database.

D.Use an elastic pool and manually adjust eDTUs.

AnswerC

Autoscale automatically adjusts resources based on workload.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

799

MCQmedium

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

A.Configure a point-to-site VPN connection on the virtual network gateway and ensure the gateway subnet is in the same VNet as the managed instance.

B.Configure a private endpoint for the managed instance.

C.Deploy Azure Bastion in the same VNet.

D.Add the on-premises public IP address to the managed instance firewall rules.

AnswerA

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

800

Multi-Selecteasy

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

Select 2 answers

A.Deploy Azure Resource Manager templates

B.Execute T-SQL scripts against Azure SQL Database

C.Create SQL Agent jobs on Azure SQL Database

D.Scale an Azure SQL Database up or down

E.Manage on-premises SQL Server instances directly

AnswersB, D

Using Invoke-SqlCmd module.

Why this answer

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

Full explanation →

801

Multi-Selectmedium

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

Select 2 answers

A.Query sys.dm_os_wait_stats

B.Azure Monitor Alerts

C.Query sys.dm_exec_query_stats

D.Dynamic Management Views

E.Query Store wait statistics reports

AnswersA, E

Direct wait stats DMV.

Why this answer

Option A and B are correct. sys.dm_os_wait_stats is a DMV that shows cumulative wait statistics. Query Store also captures wait statistics for queries. Option C is wrong because sys.dm_exec_query_stats shows query performance but not waits.

Option D is wrong because Azure Monitor metrics do not include wait statistics. Option E is wrong because Dynamic Management Views is a category, not a specific method.

Full explanation →

802

MCQhard

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

A.Add additional TempDB data files equal to the number of vCores (16).

B.Move TempDB to Azure Premium Storage for better IO latency.

C.Increase the database service tier to 24 vCores.

D.Increase the initial size of the TempDB data file to 100 GB.

AnswerA

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

803

MCQhard

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

A.Enable accelerated database recovery to reduce log generation.

B.Reduce the size of individual transactions to commit more frequently.

C.Partition large tables to spread log writes across files.

D.Increase the service level objective (SLO) to a higher vCore count.

AnswerD

Higher SLO provides higher log rate limit.

Why this answer

Option A is correct because increasing the service level objective (SLO) provides a higher log rate limit, reducing throttling. Option B is wrong because Hyperscale does not allow enabling accelerated database recovery to reduce log generation; it's always enabled. Option C is wrong because using larger transaction batches reduces log rate by reducing commit frequency.

Option D is wrong because partitioning large tables does not directly reduce log write throttling; it may even increase.

Full explanation →

804

MCQeasy

You are a database administrator for a startup that uses Azure SQL Database for its main application. The database is currently in the West US region using the General Purpose service tier. The company expects rapid growth and wants to ensure high availability within the region. They want to minimize downtime during planned maintenance. The application uses read-heavy workloads, and they want to offload read traffic to a secondary replica. The budget is flexible but should be justified. What should you recommend?

A.Enable zone redundancy on the current General Purpose database.

B.Configure a readable secondary on the current General Purpose database.

C.Create a secondary database in a different region and use active geo-replication.

D.Upgrade the database to the Business Critical service tier and enable read scale-out.

AnswerD

Business Critical provides automatic failover and multiple replicas, and read scale-out allows offloading read traffic.

Why this answer

Option D is correct because upgrading to Business Critical provides multiple replicas, automatic failover, and the ability to enable read scale-out to offload read traffic. Option A is wrong because General Purpose has only one readable replica (if configured) but does not provide automatic HA failover. Option B is wrong because zone-redundancy adds cost but does not provide read scale-out.

Option C is wrong because active geo-replication is overkill for intra-region HA.

Full explanation →

805

MCQeasy

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

A.Set the firewall rule 'Allow Azure Services and resources to access this server' to ON and remove all other IP rules.

B.Set the firewall rule 'Allow Azure Services and resources to access this server' to OFF and add a rule for 0.0.0.0.

C.Set firewall rules to deny all IP addresses.

D.Set the firewall rule 'Allow Azure Services and resources to access this server' to ON and add a rule for 0.0.0.0.

AnswerA

This allows only Azure services.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

806

MCQhard

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

A.The backup storage account has been deleted or has incorrect firewall rules.

B.The database is experiencing high transaction log generation, exceeding the backup throughput limit.

C.The geo-replication link is broken, causing backup failures.

D.The database is in a paused state due to serverless compute.

AnswerB

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

Why this answer

Azure SQL Database automated backups are managed by the platform. If backups fail, it could be due to high write activity or resource saturation causing a backup timeout. Network issues or storage problems are less likely because the database is online.

Option A is correct. Option B is incorrect because the database is online. Option C is for geo-replication.

Option D is a configuration that would affect all backups, not just one.

Full explanation →

807

MCQmedium

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

A.Automatic tuning

B.Query Performance Insight

C.Azure Monitor Alerts

D.SQL Assessment

AnswerC

Azure Monitor Alerts can trigger on CPU metric thresholds.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

808

Multi-Selectmedium

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

Select 2 answers

A.Configure sensitivity labels using Microsoft Purview Information Protection.

B.Implement Always Encrypted for all columns containing personal data.

C.Install the Azure Information Protection client on all client machines.

D.Enable Microsoft Defender XDR for the database server.

E.Use SQL Data Discovery & Classification in the Azure portal to classify columns containing personal data.

AnswersA, E

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

Why this answer

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

Exam trap

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

Full explanation →

809

MCQmedium

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

A.4 weeks.

B.4 days.

C.4 months.

D.4 years.

AnswerA

P4W in ISO 8601 duration means 4 weeks.

Why this answer

In the ARM template snippet, the weeklyRetention property is set to 'P1M' (ISO 8601 duration format), which means 1 month. Since a month is approximately 4 weeks, weekly backups are retained for 4 weeks. The LTR policy uses ISO 8601 durations, where 'P1M' explicitly specifies a monthly retention period.

Exam trap

The trap here is that candidates may confuse the ISO 8601 duration 'P1M' with a count of weeks, days, or years, leading them to select 4 weeks, 4 days, 4 months, or 4 years without recognizing that 'P1M' explicitly means 1 month, not 4 of any other unit.

How to eliminate wrong answers

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

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

Full explanation →

810

MCQmedium

You are the database administrator for a global e-commerce company. The company runs its production SQL Server on an Azure Virtual Machine (IaaS) in the West US region. The database is mission-critical and requires a Recovery Point Objective (RPO) of 5 minutes and a Recovery Time Objective (RTO) of 30 minutes in the event of a regional disaster. The VM uses premium SSDs and is backed up daily to a Recovery Services vault with geo-redundant storage. The current backup policy takes full backups weekly, differential backups daily, and transaction log backups every 15 minutes. The VM is in an availability set for high availability within the region. During a recent regional outage simulation, the database was unavailable for 4 hours because the backups needed to be restored to a different region, and the restore process took longer than expected. You need to recommend a solution to meet the RPO and RTO requirements. What should you do?

A.Implement Azure Site Recovery to replicate the VM to a secondary region.

B.Set up log shipping to a secondary SQL Server in a different region and perform manual failover.

C.Configure a SQL Server Always On availability group with a synchronous-commit replica in a secondary Azure region.

D.Increase the frequency of transaction log backups to every 5 minutes and use geo-restore.

AnswerC

Synchronous replication provides RPO of 0 (within 5 minutes) and automatic failover can achieve RTO in minutes.

Why this answer

Option C is correct because a SQL Server Always On availability group with a synchronous-commit replica in a secondary Azure region provides automatic failover with zero data loss (RPO of 0 seconds) and can meet the 30-minute RTO by enabling fast, automated failover to the secondary region. This solution eliminates the need for manual restore processes and ensures continuous data synchronization, directly addressing the 4-hour outage caused by slow geo-restore.

Exam trap

The trap here is that candidates confuse Azure Site Recovery (VM-level replication) with database-level replication, assuming it provides SQL Server transaction consistency, when in fact it only offers crash-consistent or app-consistent snapshots that may not meet strict RPO/RTO for SQL Server.

How to eliminate wrong answers

Option A is wrong because Azure Site Recovery replicates the entire VM at the hypervisor level, not the SQL Server database level, which can cause data inconsistency and does not guarantee SQL Server transaction-consistent failover, nor does it meet the 5-minute RPO without additional log shipping. Option B is wrong because log shipping requires manual failover and has a built-in delay (typically 15-60 minutes) between log backup and restore, making it impossible to achieve a 5-minute RPO, and manual failover cannot meet the 30-minute RTO reliably. Option D is wrong because increasing transaction log backup frequency to every 5 minutes still relies on geo-restore from a Recovery Services vault, which involves restoring from geo-redundant storage (GRS) that can take hours due to large data volumes and network latency, failing the 30-minute RTO.

Full explanation →

811

Multi-Selectmedium

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

Select 3 answers

A.User-assigned managed identity without a contained database user

B.Microsoft Entra ID password authentication

C.Integrated Windows authentication (Kerberos)

D.Azure CLI authentication

E.Service principal authentication

AnswersB, C, E

Supports username/password authentication via Entra ID.

Why this answer

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

Exam trap

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

Full explanation →

812

MCQmedium

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

A.Data classification

B.Server-level audit

C.Diagnostic settings for SQLInsights

D.Query Store

AnswerD

Query Store captures query runtime statistics and plans.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

813

MCQmedium

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

A.Create a contained user mapped to the Microsoft Entra identity, then grant SELECT on the Sales schema to the user, and deny SELECT on all other schemas.

B.Create a contained user mapped to the Microsoft Entra identity and add the user to the db_datareader role.

C.Create a contained user mapped to the Microsoft Entra identity and add the user to the db_denydatawriter role.

D.Create a contained user mapped to the Microsoft Entra identity and add the user to the db_datareader role, then deny SELECT on all schemas except Sales.

AnswerA

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

Why this answer

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

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

814

Multi-Selectmedium

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

Select 2 answers

A.Enable the 'Allow Azure services and resources to access this server' firewall setting.

B.Use VNet service endpoints instead of Private Link to reduce costs.

C.Use Azure Private Link to connect to the database from a virtual network.

D.Disable public network access on the SQL server.

E.Add firewall rules that allow all IP addresses from your organization's IP range.

AnswersC, D

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

Why this answer

Options A and C are correct. Azure Private Link ensures traffic never traverses the public internet. Disabling public network access at the server level blocks all internet traffic.

Option B is wrong because allowing all Azure services is a broad rule that may be insecure. Option D is wrong because VNet service endpoints are legacy and less secure than Private Link. Option E is wrong because firewall rules with large IP ranges are less secure.

Full explanation →

815

MCQeasy

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

A.Azure SQL Database in the General Purpose tier.

B.SQL Server on Azure VMs with Always On Availability Groups.

C.Azure SQL Managed Instance in the Business Critical tier.

D.Azure SQL Database in the Hyperscale tier.

AnswerC

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

816

MCQmedium

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

A.Deploy Microsoft Sentinel and connect the SQL Managed Instance

B.Enable Microsoft Defender for Cloud on the subscription or resource

C.Configure Microsoft Purview Data Map

D.Enable Azure SQL Database auditing

AnswerB

Defender for Cloud includes advanced threat protection for SQL.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

817

MCQeasy

You need to design a high availability solution for an Azure SQL Database that supports an online transaction processing (OLTP) application. The solution must provide automatic failover within seconds in case of a node failure and guarantee zero data loss. Which deployment option should you choose?

A.Basic tier with zone redundancy

B.General Purpose tier with zone redundancy

C.Hyperscale tier with zone redundancy

D.Business Critical tier with zone redundancy

AnswerD

Business Critical provides synchronous replication and zero data loss.

Why this answer

Option B is correct because the Business Critical tier uses a quorum-based commit and synchronous replication to replicas, ensuring zero data loss on failover. Option A (General Purpose) uses asynchronous replication and may lose data. Option C (Hyperscale) uses a log-based service with buffer and may lose data on failover.

Option D (Basic) does not provide automatic failover with zero data loss.

Full explanation →

818

MCQeasy

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

A.You must perform a manual failover to a secondary replica.

B.A replica in another availability zone is automatically promoted to primary.

C.The database is restored from the latest backup.

D.The database becomes read-only until the zone is restored.

AnswerB

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

Why this answer

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

Option D is wrong because automatic failover happens within seconds.

Full explanation →

819

Multi-Selecthard

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

Select 3 answers

A.Enable automatic tuning on the secondary.

B.Configure the secondary as a readable secondary.

C.Create nonclustered indexes on the secondary replica.

D.Force query plans on the primary replica to benefit the secondary.

E.Use read-only routing to direct reporting queries to the secondary.

AnswersA, C, E

Can optimize query performance automatically.

Why this answer

Options A, D, and E are correct. Creating nonclustered indexes on the secondary can improve read performance. Using read-only routing to direct queries to the secondary ensures they don't impact the primary.

Enabling automatic tuning on the secondary can help optimize queries. Option B is wrong because read-scale is automatically enabled for Business Critical and Premium tiers, but forcing query plans on the primary may not help on the secondary. Option C is wrong because configuring the secondary as a readable secondary is done by default when using geo-replication; it's not an additional action.

Full explanation →

820

MCQeasy

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

A.Azure SQL Auditing

B.Advanced Threat Protection

C.Transparent Data Encryption (TDE)

D.SQL Vulnerability Assessment

AnswerA

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

821

MCQhard

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

A.Configure the auto-failover group to automatically fail over.

B.Schedule a failover using elastic jobs.

C.Create an Azure Automation runbook that initiates the failover.

D.Use a SQL Server Agent job to trigger failover.

AnswerC

A runbook can be used to programmatically initiate a failover if needed.

Why this answer

Azure Automation with a runbook can monitor for disaster and initiate a failover using PowerShell or CLI. Option A is wrong because auto-failover groups handle automatic failover, but the question asks for automation in the event of disaster, which is already automatic. Option C is wrong because elastic jobs cannot initiate failover.

Option D is wrong because SQL Server Agent is not available in Azure SQL Database.

Full explanation →

822

Multi-Selectmedium

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

Select 3 answers

A.Enable zone redundancy on the primary instance.

B.Create a secondary Managed Instance in a different Azure region.

C.Configure a failover group between the primary and secondary.

D.Enable geo-replication on the instance.

E.Use the Business Critical service tier for both instances.

AnswersB, C, E

Required for DR.

Why this answer

Options A, C, and E are correct because creating a secondary instance in another region, configuring a failover group, and using Business Critical tier (which supports failover groups) meet the requirements. Option B is wrong because zone redundancy is within region. Option D is wrong because geo-replication is not supported on Managed Instance directly; failover groups are used.

Full explanation →

823

MCQhard

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

A.Use Always Encrypted with secure enclaves and configure the enclave attestation URL.

B.Store the column master key in Azure Key Vault and configure the application to retrieve it.

C.Implement dynamic data masking on the columns containing Social Security numbers.

D.Enable Transparent Data Encryption (TDE) on the database.

AnswerA

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

Why this answer

Option D is correct because Always Encrypted with secure enclaves (using Intel SGX) allows rich computations on encrypted data within the enclave. Option A is incorrect because column master key stored in Azure Key Vault is standard, but the enclave is needed for computations. Option B is incorrect because Transparent Data Encryption encrypts data at rest, not in use.

Option C is incorrect because dynamic data masking only obfuscates data from certain users, but the database can still see the plaintext.

Full explanation →

824

Multi-Selectmedium

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

Select 2 answers

A.Enable SQL Server authentication only.

B.Set an Microsoft Entra ID admin for the Azure SQL Server.

C.Create contained database users mapped to Microsoft Entra ID identities.

D.Assign the SQL Server Contributor role to the Entra ID users.

E.Enable Azure AD integration on the SQL server.

AnswersB, C

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

Why this answer

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

The SQL Server authentication mode is always enabled.

Full explanation →

825

MCQeasy

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

A.Set up a private endpoint for each region and update connection strings manually.

B.Deploy an Always On availability group listener.

C.Use the failover group listener endpoint with Microsoft Entra ID authentication.

D.Configure geo-replication and use the secondary server's public endpoint.

AnswerC

The listener ensures continuous connectivity with authentication.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →

Page 11 of 13

All pages

1 2 3 4 5 6 7 8 9 10 11 12 13

Practice DP-300 by domain

Target a specific domain to shore up weak areas.

Plan and configure a high availability and disaster recovery environment Plan and implement data platform resources Monitor, configure, and optimize database resources Configure and manage automation of tasks Plan and configure high availability and disaster recovery Implement a secure environment

See all domains with question counts →