CCNA Data Platform Resources Questions — Page 2 of 3

MCQmedium

You need to migrate an on-premises SQL Server 2019 database to Azure SQL Database with minimal downtime. The database is 500 GB and uses some features not supported in Azure SQL Database, such as FileTables. What is the best migration strategy?

A.Set up log shipping to an Azure SQL Database

B.Perform an offline migration using Azure Database Migration Service

C.Export a BACPAC file from the source and import it to Azure SQL Database

D.Use Azure Database Migration Service with online mode after removing FileTables

AnswerD

Online migration minimizes downtime. Remove unsupported features first, then migrate using DMS.

Why this answer

Option D is correct because Azure Database Migration Service (DMS) with online mode supports minimal-downtime migrations by continuously replicating ongoing changes from the source SQL Server to Azure SQL Database. However, FileTables are not supported in Azure SQL Database, so they must be removed from the source database before migration. This approach ensures near-zero downtime while addressing the unsupported feature.

Exam trap

The trap here is that candidates may assume log shipping (Option A) is viable for Azure SQL Database, but log shipping is not supported for Azure SQL Database as it requires SQL Server Agent and file-level restore operations, which are not available in that PaaS offering.

How to eliminate wrong answers

Option A is wrong because log shipping is not supported to Azure SQL Database; it only works between on-premises SQL Server instances or to SQL Server on Azure VMs, not to Azure SQL Database. Option B is wrong because an offline migration using Azure Database Migration Service would require taking the source database offline, causing significant downtime, which contradicts the requirement for minimal downtime. Option C is wrong because exporting a BACPAC file and importing it to Azure SQL Database is an offline process that does not support ongoing replication, leading to downtime, and it also does not handle unsupported features like FileTables automatically.

Practice this question →

MCQeasy

You are designing a disaster recovery strategy for an Azure SQL Database that requires a Recovery Point Objective (RPO) of less than 5 seconds and a Recovery Time Objective (RTO) of less than 1 hour. Which option should you choose?

A.Use auto-failover groups with read-write failover policy.

B.Use geo-restore from geo-redundant backups.

C.Use active geo-replication and plan manual failover.

D.Use long-term retention (LTR) backups with daily frequency.

AnswerC

Active geo-replication provides RPO less than 5 seconds and manual failover can achieve RTO of less than 1 hour.

Why this answer

Active geo-replication provides a continuous replication stream with an RPO of less than 5 seconds and supports manual failover, which can achieve an RTO of less than 1 hour when the failover is initiated promptly. This option meets the strict RPO and RTO requirements by maintaining a readable secondary in a different Azure region that is kept nearly synchronized with the primary.

Exam trap

The trap here is that candidates confuse auto-failover groups (Option A) with active geo-replication, assuming automatic failover always provides a better RTO, but the question's strict RPO of less than 5 seconds and the need for manual control make active geo-replication the correct choice, as auto-failover groups introduce additional latency and automatic detection delays that can push RTO beyond 1 hour.

How to eliminate wrong answers

Option A is wrong because auto-failover groups with read-write failover policy are designed for group-level failover of multiple databases and have a default RPO of up to 5 seconds, but the failover is automatic and may not guarantee an RTO under 1 hour due to potential delays in detection and DNS propagation; more critically, the question specifies a manual failover requirement. Option B is wrong because geo-restore from geo-redundant backups has an RPO measured in hours (typically 1-12 hours) and an RTO that can exceed several hours, making it unsuitable for sub-5-second RPO. Option D is wrong because long-term retention backups are taken daily, resulting in an RPO of up to 24 hours, and restoring from them can take hours, failing both the RPO and RTO targets.

Practice this question →

MCQhard

You are planning a deployment of Azure SQL Database for a mission-critical application. The application requires a high level of resilience to regional failures. You need to ensure that data is replicated synchronously across two Azure regions and that automatic failover occurs with zero data loss. Which deployment option should you choose?

A.Azure SQL Database on General Purpose tier with active geo-replication.

B.Azure SQL Database on Hyperscale tier with named replicas.

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

D.Azure SQL Managed Instance on Business Critical tier with failover groups.

AnswerD

Business Critical provides synchronous replication and automatic failover with zero data loss.

Why this answer

Option D is correct because Azure SQL Managed Instance on the Business Critical tier supports failover groups with synchronous replication, ensuring zero data loss (RPO=0) and automatic failover across two Azure regions. The Business Critical tier uses a quorum-based commit mechanism and high-speed local SSDs to guarantee data consistency during regional failures.

Exam trap

The trap here is that candidates often confuse active geo-replication (asynchronous, RPO > 0) with failover groups (synchronous, RPO = 0), or assume that any tier with geo-replication guarantees zero data loss, ignoring the critical difference in replication mode.

How to eliminate wrong answers

Option A is wrong because the General Purpose tier uses asynchronous replication with active geo-replication, which cannot guarantee zero data loss (RPO > 0) during a regional failure. Option B is wrong because Hyperscale tier named replicas are read-only and do not support synchronous replication or automatic failover for write workloads. Option C is wrong because SQL Server on Azure VMs with Always On Availability Groups requires manual configuration and management of the underlying infrastructure, and while it can achieve synchronous replication, it is not a fully managed PaaS service like Azure SQL Managed Instance, and automatic failover with zero data loss is not guaranteed without additional setup.

Practice this question →

MCQmedium

A company is designing a new Azure SQL Database for an e-commerce application. The database will experience variable workloads with frequent read-heavy operations. To optimize performance and cost, the company wants to use a serverless compute tier. However, they also need to ensure the database can handle sudden bursts of user activity without significant latency. Which configuration should the team implement?

A.Use a provisioned General Purpose tier with read scale-out enabled to offload read traffic to a secondary replica.

B.Set the serverless auto-pause delay to 0 (disabled) and configure a maximum vCore count equal to the expected burst peak.

C.Enable auto-pause with a 1-minute delay and configure the maximum vCore count to handle bursts.

D.Set the serverless auto-pause delay to a high value (e.g., 24 hours) and configure a minimum vCore count that matches the expected baseline load.

AnswerD

Correct: High auto-pause delay prevents frequent cold starts, and minimum vCores guarantee baseline performance for bursts.

Why this answer

Option D is correct because setting a high auto-pause delay (e.g., 24 hours) prevents the serverless database from pausing during idle periods, avoiding cold-start latency. Configuring a minimum vCore count that matches the expected baseline load ensures the database always has sufficient resources for steady-state read-heavy operations, while the maximum vCore count can scale up to handle sudden bursts without significant latency.

Exam trap

The trap here is that candidates assume a short auto-pause delay (e.g., 1 minute) saves more cost, but they overlook the cold-start latency penalty that makes it unsuitable for bursty workloads requiring immediate responsiveness.

How to eliminate wrong answers

Option A is wrong because the serverless compute tier does not support read scale-out; read scale-out is a feature of the provisioned Hyperscale and Business Critical tiers, not General Purpose. Option B is wrong because setting auto-pause delay to 0 (disabled) is not a valid configuration; the minimum auto-pause delay is 60 minutes, and disabling auto-pause entirely would prevent the cost-saving benefits of serverless. Option C is wrong because a 1-minute auto-pause delay would cause the database to pause frequently during idle periods, leading to cold-start latency when bursts occur, which contradicts the requirement to handle sudden activity without significant latency.

Practice this question →

MCQmedium

You are designing a disaster recovery strategy for an Azure SQL Database in the West US region. The database is used by a mission-critical application with an RPO of 5 seconds and an RTO of 30 seconds. Which deployment option should you choose?

A.Use active geo-replication with a readable secondary in another Azure region

B.Use a failover group with a secondary in a paired region

C.Deploy the database with zone-redundant configuration in the primary region

D.Deploy the database with an auto-failover group to a secondary region

AnswerC

Zone-redundant configuration provides automatic failover within the same region with RPO of 5 seconds and RTO of 30 seconds.

Why this answer

Option C is correct because zone-redundant configuration for Azure SQL Database replicates the database across three Azure Availability Zones within the same region, providing automatic failover with an RPO of 0 seconds and an RTO of typically 10-30 seconds. This meets the stringent RPO of 5 seconds and RTO of 30 seconds without relying on cross-region replication, which would introduce higher latency and potential data loss.

Exam trap

The trap here is that candidates often assume cross-region disaster recovery (like geo-replication or failover groups) is always required for mission-critical apps, but they overlook that zone-redundant configuration within a single region can achieve lower RPO and RTO than any cross-region solution, and the question's extreme RPO/RTO targets force an intra-region choice.

How to eliminate wrong answers

Option A is wrong because active geo-replication with a readable secondary in another Azure region typically has an RPO of up to 5 seconds and an RTO of 1 hour or more, failing to meet the 30-second RTO requirement. Option B is wrong because a failover group with a secondary in a paired region also has an RPO of up to 5 seconds and an RTO of 1 hour, which exceeds the required 30-second RTO. Option D is wrong because an auto-failover group to a secondary region similarly has an RPO of up to 5 seconds and an RTO of 1 hour, and while it automates failover, it still cannot achieve the sub-minute RTO needed.

Practice this question →

MCQeasy

You are planning the migration of a large on-premises SQL Server database to Azure SQL Managed Instance. The database is 500 GB in size and has a high transaction rate. You need to minimize downtime during the migration. Which migration approach should you recommend?

A.Set up transactional replication to replicate data to SQL Managed Instance.

B.Perform an offline migration using Azure Database Migration Service.

C.Export the database as a BACPAC file and import to SQL Managed Instance.

D.Use Azure Database Migration Service with online migration mode.

AnswerD

Online mode uses log shipping to minimize downtime.

Why this answer

Option D is correct because Azure Database Migration Service (DMS) with online migration mode uses transactional log shipping to continuously synchronize changes from the source SQL Server to Azure SQL Managed Instance, enabling near-zero downtime. This is essential for a 500 GB database with a high transaction rate, as it allows the source to remain operational during the migration and only requires a final cutover with minimal interruption.

Exam trap

The trap here is that candidates often confuse 'online migration' with 'replication' (Option A), assuming transactional replication is the only way to achieve minimal downtime, but DMS online mode is the native, integrated service designed specifically for this scenario with simpler management and lower overhead.

How to eliminate wrong answers

Option A is wrong because transactional replication requires schema and data to be initially synchronized, and while it can support minimal downtime, it introduces additional complexity and overhead for a large, high-transaction database, and is not the recommended primary migration tool for SQL Managed Instance. Option B is wrong because offline migration using DMS requires the source database to be taken offline during the data copy, causing significant downtime, which contradicts the requirement to minimize downtime. Option C is wrong because exporting a 500 GB database as a BACPAC file is a slow, offline process that does not support ongoing replication, leading to extended downtime and potential data loss for a high-transaction workload.

Practice this question →

MCQeasy

A database administrator runs the PowerShell script shown in the exhibit against an Azure SQL Database. What does the script output?

A.Details of table schemas.

B.List of backup retention policies.

C.List of users and their permissions.

D.Service tier and performance level of the database.

AnswerD

sys.database_service_objectives provides service tier info.

Why this answer

The PowerShell script uses the Get-AzSqlDatabase cmdlet, which retrieves an Azure SQL Database object. The output includes properties such as Edition (service tier) and ServiceObjective (performance level), making option D correct.

Exam trap

The trap here is that candidates may confuse Get-AzSqlDatabase with other cmdlets that return schema, backup, or permission details, leading them to select a plausible but incorrect option based on the cmdlet's name alone.

How to eliminate wrong answers

Option A is wrong because Get-AzSqlDatabase does not return schema details; schema information is obtained via T-SQL queries or tools like Get-AzSqlDatabaseSchema. Option B is wrong because backup retention policies are managed with Get-AzSqlDatabaseBackupLongTermRetentionPolicy or Get-AzSqlDatabaseShortTermRetentionPolicy, not Get-AzSqlDatabase. Option C is wrong because user permissions are listed using T-SQL commands (e.g., sys.database_permissions) or Get-AzSqlDatabaseUser, not the database-level cmdlet.

Practice this question →

Matchingmedium

Match each Azure SQL Database performance tuning feature to its description.

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

Concepts

Matches

Provides analysis of query performance and resource consumption

Automatically adjusts indexes and query plans

Proactive diagnostics and performance recommendations

Provides recommendations for index and query optimization

Why these pairings

These features help monitor and optimize database performance.

Practice this question →

MCQmedium

You are deploying an Azure SQL Database that will be used by a global application. You need to ensure that read-intensive workloads are offloaded from the primary database to improve performance. Which feature should you enable?

A.Read scale-out.

B.Automatic tuning.

C.Connection pooling.

D.Active geo-replication.

AnswerA

Read scale-out provides a read-only replica for offloading reads.

Why this answer

Read scale-out (A) is the correct feature because it allows you to offload read-only workloads to a read-only replica of the Azure SQL Database. By enabling the 'Read scale-out' property, the database automatically routes connections with `ApplicationIntent=ReadOnly` to a secondary replica, freeing the primary from read-intensive queries and improving overall performance for write operations.

Exam trap

The trap here is that candidates often confuse Active geo-replication with read scale-out, but the key distinction is that read scale-out is a single-database feature within the same region that transparently routes read-only connections, whereas geo-replication requires explicit connection string changes and is designed for cross-region disaster recovery.

How to eliminate wrong answers

Option B is wrong because Automatic tuning is a feature that uses AI to optimize query performance (e.g., index creation, plan forcing), but it does not offload read workloads to a separate replica. Option C is wrong because Connection pooling is a client-side technique that reuses database connections to reduce latency, but it does not redirect read traffic to a secondary database. Option D is wrong because Active geo-replication creates readable secondary replicas in different regions for disaster recovery and geo-distributed reads, but it requires manual connection string changes or application logic to route read traffic, unlike the transparent, built-in read scale-out feature.

Practice this question →

MCQmedium

You are deploying Azure SQL Database for a critical application. The compliance policy requires that the database be configured for zone redundancy in the primary region and have automated failover to a secondary region with no data loss. What Azure SQL Database offering and feature should you choose?

A.Basic tier with geo-replication

B.Business Critical tier with zone redundancy and active geo-replication

C.General Purpose tier with geo-replication

D.Hyperscale tier with zone redundancy

AnswerB

Business Critical provides zone redundancy and active geo-replication with zero data loss.

Why this answer

The Business Critical tier is the only Azure SQL Database tier that supports both zone redundancy within a region and active geo-replication with synchronous data replication to a secondary region. Zone redundancy ensures high availability within the primary region by distributing replicas across availability zones, while active geo-replication provides automated failover to a secondary region with no data loss because it uses synchronous commit at the primary and asynchronous replication to the secondary, but with the ability to fail over without data loss if the secondary is fully synchronized. This combination meets the compliance requirement for zero data loss during a regional failover.

Exam trap

The trap here is that candidates often confuse the General Purpose tier's geo-replication (which is asynchronous and can lose data) with the Business Critical tier's active geo-replication (which supports synchronous replication for zero data loss), and they overlook that zone redundancy is only available in Business Critical and Hyperscale tiers, but Hyperscale lacks automated failover geo-replication.

How to eliminate wrong answers

Option A is wrong because the Basic tier does not support zone redundancy or geo-replication; it is designed for development and testing with no high availability or disaster recovery capabilities. Option C is wrong because the General Purpose tier uses asynchronous data replication and does not support zone redundancy; it can have geo-replication but cannot guarantee zero data loss during failover due to asynchronous replication. Option D is wrong because the Hyperscale tier supports zone redundancy but does not support active geo-replication for automated failover to a secondary region; its geo-replication is limited to named replicas with manual failover, not automated failover with no data loss.

Practice this question →

MCQhard

Your company has an Azure SQL Managed Instance that hosts multiple databases. You need to implement a solution to automatically detect and alert on potential SQL injection attacks. The solution must integrate with Microsoft Sentinel for incident response. What should you configure?

A.Enable Microsoft Purview Data Map for the Managed Instance

B.Configure Microsoft Defender XDR for SQL

C.Use Microsoft Intune to manage SQL security policies

D.Enable Microsoft Defender for SQL on the Managed Instance

AnswerD

Defender for SQL provides SQL injection alerts and integrates with Sentinel.

Why this answer

Microsoft Defender for SQL on Azure SQL Managed Instance provides built-in SQL injection detection and alerting, which can be integrated directly with Microsoft Sentinel for automated incident response and investigation. This is the correct solution because it offers native vulnerability assessment and threat detection tailored to SQL databases, meeting the requirement for both detection and Sentinel integration.

Exam trap

The trap here is that candidates may confuse Microsoft Defender XDR (a broader security suite) with Microsoft Defender for SQL (the specific service for SQL threat detection), leading them to select Option B instead of the correct D.

How to eliminate wrong answers

Option A is wrong because Microsoft Purview Data Map is a data governance and cataloging service, not a security detection or alerting tool for SQL injection. Option B is wrong because Microsoft Defender XDR (Extended Detection and Response) is a unified security platform for endpoints, identities, and cloud apps, but it does not directly provide SQL injection detection for Azure SQL Managed Instance; that capability is part of Defender for SQL. Option C is wrong because Microsoft Intune is a mobile device management (MDM) and mobile application management (MAM) service, not a tool for configuring SQL security policies or detecting SQL injection attacks.

Practice this question →

MCQhard

Your company is migrating a mission-critical application from SQL Server 2019 on-premises to Azure SQL Managed Instance. The database is 1.5 TB and has 50 databases that need to be migrated. The application uses SQL Server Agent jobs, Service Broker, and cross-database queries. The security requirements mandate that the migration must use Microsoft Entra ID authentication, and all data must be encrypted at rest using customer-managed keys in Azure Key Vault. The migration window is limited to 4 hours of downtime. You need to choose the migration method and configure the target environment. What should you do?

A.Use Azure Database Migration Service offline migration with automatic cutover.

B.Deploy a new Managed Instance, configure TDE with customer-managed keys, and use transactional replication to synchronize data.

C.Use Azure Database Migration Service online migration, configure Managed Instance with customer-managed TDE, and enable Entra ID authentication.

D.Use Azure backup to URL and restore to Managed Instance, then configure TDE with customer-managed keys.

AnswerC

Online migration minimizes downtime, and Managed Instance supports all required features.

Why this answer

Option C is correct because Azure Database Migration Service (DMS) online migration supports minimal downtime for large databases (1.5 TB) and 50 databases, while maintaining transactional consistency for cross-database queries and Service Broker. Configuring the Managed Instance with customer-managed TDE (Transparent Data Encryption) using Azure Key Vault meets the encryption-at-rest requirement, and enabling Microsoft Entra ID authentication satisfies the security mandate. The online migration mode allows continuous synchronization during the migration window, with a final cutover within the 4-hour downtime limit.

Exam trap

The trap here is that candidates often assume offline migration (Option A or D) is acceptable for large databases, but the 4-hour downtime window makes online migration (Option C) the only viable choice, and they may overlook that transactional replication (Option B) cannot handle cross-database dependencies and Service Broker messages during migration.

How to eliminate wrong answers

Option A is wrong because offline migration with automatic cutover requires the source database to be taken offline for the entire duration of data copy, which would exceed the 4-hour downtime window for a 1.5 TB database. Option B is wrong because transactional replication does not natively support Service Broker or cross-database queries as a migration method; it is designed for ongoing replication of specific tables, not full database migration with schema and dependencies. Option D is wrong because Azure backup to URL and restore to Managed Instance is an offline method that would require the database to be offline for the entire backup and restore process, exceeding the 4-hour window, and it does not inherently configure TDE with customer-managed keys or Entra ID authentication.

Practice this question →

MCQmedium

You are monitoring an Azure SQL Database and notice a pattern of high CPU usage during business hours. You need to identify the queries consuming the most CPU over the last 24 hours. Which dynamic management view should you query?

A.sys.dm_exec_requests

B.sys.dm_exec_sessions

C.sys.dm_exec_query_stats

D.sys.dm_os_performance_counters

AnswerC

Provides aggregated CPU time for queries over time.

Why this answer

sys.dm_exec_query_stats (Option C) is the correct DMV because it returns aggregate performance statistics for cached query plans, including total CPU time (total_worker_time), execution count, and last execution time. By querying this view and ordering by total_worker_time descending, you can identify the queries that have consumed the most CPU over the last 24 hours, directly addressing the pattern of high CPU usage during business hours.

Exam trap

The trap here is that candidates confuse sys.dm_exec_requests (current activity) with sys.dm_exec_query_stats (historical aggregated stats), leading them to choose Option A because they think 'requests' implies all recent queries, but it only shows currently running queries.

How to eliminate wrong answers

Option A is wrong because sys.dm_exec_requests shows currently executing requests at the moment of querying, not historical CPU consumption over the last 24 hours. Option B is wrong because sys.dm_exec_sessions provides session-level information such as login time and host details, but does not contain per-query CPU usage statistics. Option D is wrong because sys.dm_os_performance_counters returns operating system performance counter values (e.g., CPU utilization percentage), not the specific queries responsible for high CPU usage.

Practice this question →

MCQeasy

You are planning to deploy Azure SQL Database for a new application. The application requires a predictable performance with reserved resources and the ability to scale up during peak season. You want to minimize costs. Which purchasing model should you choose?

A.vCore-based purchasing model with reserved capacity

B.DTU-based purchasing model

C.Hyperscale service tier

D.Serverless compute tier

AnswerA

vCore model allows manual scaling and reserved instances reduce costs.

Why this answer

The vCore-based purchasing model with reserved capacity is correct because it allows you to reserve compute resources for a one- or three-year term, providing a significant discount (up to 40-60%) compared to pay-as-you-go pricing. This model also supports predictable performance with dedicated resources and the ability to scale up (e.g., increase vCores or storage) during peak seasons, while the reserved capacity commitment minimizes costs for the baseline workload.

Exam trap

The trap here is that candidates often confuse the DTU model as the default cost-saving option, but the vCore model with reserved capacity actually provides deeper discounts for predictable workloads, while the DTU model lacks such reservation flexibility.

How to eliminate wrong answers

Option B is wrong because the DTU-based purchasing model bundles compute, storage, and I/O into fixed tiers, offering less granular control over resources and no reserved capacity option, making it less cost-effective for predictable workloads that need to scale. Option C is wrong because the Hyperscale service tier is designed for large databases with high storage and rapid scaling needs, but it uses a pay-as-you-go model without reserved capacity discounts, leading to higher costs for predictable, moderate workloads. Option D is wrong because the Serverless compute tier auto-pauses and scales based on demand, which is ideal for intermittent workloads but not for predictable performance with reserved resources, and it does not offer reserved capacity pricing.

Practice this question →

Drag & Dropmedium

Drag and drop the steps to configure geo-replication for an Azure SQL Database in the correct order.

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

Steps

Order

Why this order

Geo-replication requires a secondary server in another region, then configuring the secondary database, initiating replication, and verifying the link. Failover is a separate step.

Practice this question →

MCQmedium

You are migrating an on-premises SQL Server database to Azure SQL Managed Instance. The source database uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. Which step is required to enable TDE in the target Managed Instance?

A.Export the certificate to a PFX file, import it to Azure Key Vault, and configure TDE with a key vault key

B.Create a self-signed certificate in the Managed Instance and restore the database with KEEP_TDE

C.Use the same certificate file by uploading it to the Managed Instance via SQL Server Management Studio

D.Disable TDE on the source database before migration, then enable it after migration

AnswerA

Managed Instance supports customer-managed keys in Azure Key Vault.

Why this answer

Azure SQL Managed Instance does not support importing user-provided certificates for TDE. Instead, it relies on Azure Key Vault as the external key store. Therefore, you must export the on-premises TDE certificate to a PFX file, import it into Azure Key Vault, and then configure the Managed Instance to use that key vault key for TDE.

This ensures the database encryption key can be unwrapped and the database remains encrypted during and after migration.

Exam trap

The trap here is that candidates assume on-premises TDE certificates can be directly reused in Azure SQL Managed Instance, but Azure requires all TDE keys to be managed through Azure Key Vault, not via certificate files or self-signed certificates.

How to eliminate wrong answers

Option B is wrong because Managed Instance does not support the KEEP_TDE option; TDE must be configured using Azure Key Vault, not a self-signed certificate. Option C is wrong because you cannot upload a certificate file directly to a Managed Instance via SSMS; TDE in Azure SQL Managed Instance is managed exclusively through Azure Key Vault. Option D is wrong because disabling TDE on the source database would require decrypting the entire database, which is unnecessary and introduces significant downtime and risk; the correct approach is to migrate the TDE-protected database and re-enable TDE using a key vault key.

Practice this question →

MCQeasy

You are deploying an Azure SQL Database for a new application that requires high availability with automatic failover within the same region. The database must be able to recover from a zonal failure without data loss. Which deployment option should you use?

A.Hyperscale tier with zone redundancy

B.Standard tier with failover group

C.Business Critical tier with zone redundancy enabled

D.Basic tier with active geo-replication

AnswerC

Business Critical tier with zone redundancy provides zonal failover with zero data loss.

Why this answer

The Business Critical tier with zone redundancy enabled provides the highest level of availability within a single Azure region by synchronously replicating data across three different availability zones. This ensures that if one zone fails, automatic failover occurs with no data loss because all transactions are committed to at least two replicas before being acknowledged. This meets the requirement for zonal failure recovery without data loss.

Exam trap

The trap here is that candidates often confuse zone redundancy with geo-replication, assuming that any tier with failover groups or geo-replication can handle zonal failures, but only zone-redundant configurations within the same region guarantee zero data loss during a zonal outage.

How to eliminate wrong answers

Option A is wrong because the Hyperscale tier, while offering high performance and fast scaling, does not support zone redundancy for the primary replica; it only supports zone redundancy for secondary replicas, so a zonal failure could cause data loss or downtime for the primary. Option B is wrong because the Standard tier does not offer zone redundancy; it uses local redundant storage (LRS) and can only provide availability within a single zone, so a zonal failure would result in data loss and downtime. Option D is wrong because the Basic tier does not support zone redundancy or geo-replication; it is designed for development and testing with no SLA for high availability, and active geo-replication is not available on the Basic tier.

Practice this question →

MCQmedium

You manage an Azure SQL Database that is experiencing performance degradation during peak hours. You suspect that the current pricing tier is insufficient. You need to increase performance with minimal downtime. Which action should you take?

A.Use the Azure portal to scale up the DTU or vCore service tier.

B.Create a new database on a higher tier and copy data manually.

C.Enable read scale-out to offload read workloads.

D.Implement horizontal sharding across multiple databases.

AnswerA

Scaling up is a dynamic operation with minimal downtime.

Why this answer

Scaling up the DTU or vCore service tier in the Azure portal is a dynamic scaling operation that typically completes within minutes and does not require application downtime. Azure SQL Database supports online scaling, meaning the database remains available during the transition, with only a brief connection failover at the end. This directly addresses the need to increase performance during peak hours with minimal disruption.

Exam trap

The trap here is that candidates might confuse scaling up (vertical scaling) with scaling out (horizontal scaling) or offloading reads, and choose a more complex or disruptive option instead of the straightforward, supported online scaling operation.

How to eliminate wrong answers

Option B is wrong because manually creating a new database and copying data introduces significant downtime and complexity, and is not necessary when Azure SQL Database supports online scaling. Option C is wrong because enabling read scale-out only offloads read-only workloads to a readable secondary replica; it does not increase the compute or storage capacity of the primary database to handle peak-hour performance degradation. Option D is wrong because horizontal sharding is a design pattern for distributing data across multiple databases to handle massive scale, not a quick operational fix for a single database experiencing performance issues during peak hours.

Practice this question →

MCQhard

You are a database administrator for a company that runs a large e-commerce platform on Azure SQL Database. The platform experiences unpredictable traffic spikes during flash sales, causing performance degradation. You need to implement a solution that automatically scales the database resources based on demand without manual intervention. Which feature should you use?

A.Elastic pool

B.Manual scaling using Azure CLI

C.Serverless compute tier

D.Hyperscale service tier

AnswerD

Hyperscale automatically adjusts compute resources and supports rapid scaling.

Why this answer

The Hyperscale service tier (Option D) is correct because it provides near-instantaneous, automatic scaling of compute and storage resources in response to workload demand, without requiring manual intervention. For unpredictable traffic spikes during flash sales, Hyperscale's architecture decouples compute from storage, allowing compute nodes to scale up or down independently and transparently, while its buffer pool and page server design handle high concurrency and large data volumes efficiently.

Exam trap

The trap here is that candidates often confuse the Serverless compute tier (Option C) with automatic scaling, but Serverless is designed for intermittent workloads with auto-pause, not for handling unpredictable high-traffic spikes like flash sales, whereas Hyperscale provides true elastic scaling for large, demanding workloads.

How to eliminate wrong answers

Option A is wrong because Elastic pools are designed for managing multiple databases with variable but predictable resource needs, not for automatically scaling a single large database in response to unpredictable spikes; they require manual configuration of eDTU or vCore limits and do not auto-scale individual databases. Option B is wrong because manual scaling using Azure CLI requires human intervention to execute commands, which defeats the requirement for automatic scaling without manual intervention. Option C is wrong because the Serverless compute tier auto-pauses during inactivity and is intended for intermittent, low-traffic workloads, not for handling unpredictable high-traffic spikes; it has a maximum vCore limit and does not provide the rapid, elastic scaling needed for flash sales.

Practice this question →

MCQmedium

You are a database administrator for a healthcare company that uses Azure SQL Database for its patient management system. The database is currently 500 GB and uses the General Purpose tier in the West US region. The application experiences high read traffic during business hours, and you need to improve read performance without increasing costs significantly. You also need to ensure that read queries are isolated from write workloads. The application supports read-only routing using connection strings. You have the following options: A. Create a secondary replica in the same region using active geo-replication and configure the application to use the secondary for read-only queries. B. Upgrade the database to the Business Critical tier and use the built-in readable secondary. C. Implement read scale-out by adding a readable secondary replica in the same region using the built-in read scale-out feature of the General Purpose tier. D. Create an elastic pool with multiple databases and distribute read queries across them. Which option should you choose?

A.Implement read scale-out in General Purpose tier.

B.Create a secondary replica using active geo-replication and configure read-only routing.

C.Upgrade to Business Critical tier and use its readable secondary.

D.Create an elastic pool and distribute read queries.

AnswerB

Active geo-replication provides a readable secondary without changing the tier, isolating reads.

Why this answer

Option B is correct because active geo-replication allows you to create a readable secondary replica in the same region, which can be used to offload read-only queries from the primary. The application can use connection strings with ApplicationIntent=ReadOnly to automatically route read queries to the secondary, isolating them from write workloads. This improves read performance without upgrading to a higher-cost tier, as the General Purpose tier does not natively support readable secondaries within the same region.

Exam trap

The trap here is that candidates often assume the General Purpose tier supports readable secondaries natively (like the Business Critical tier), but in reality, you must use active geo-replication to create a readable secondary in the same region for the General Purpose tier.

How to eliminate wrong answers

Option A is wrong because the General Purpose tier does not have a built-in read scale-out feature; readable secondaries are only available in the Business Critical tier or via active geo-replication. Option C is wrong because upgrading to the Business Critical tier would significantly increase costs, which contradicts the requirement to avoid increasing costs significantly. Option D is wrong because creating an elastic pool does not provide a readable secondary for read isolation; it distributes databases but does not offload read queries from a single database's primary replica.

Practice this question →

MCQmedium

You are reviewing the firewall and virtual network rules for an Azure SQL Database server as shown in the exhibit. The server has public network access disabled. A client application in a different virtual network (vnet2) needs to connect to the database. What must you do to allow the connection?

A.Add a virtual network rule that references subnet2 in vnet2.

B.Add the IP address of the client to the ipRules array.

C.Set publicNetworkAccess to 'Enabled' and add an IP firewall rule.

D.Create a server-level firewall rule to allow all Azure services.

AnswerA

Virtual network rules allow traffic from specific subnets.

Why this answer

Since public network access is disabled, the server rejects all public IP-based connections. To allow a client in a different virtual network (vnet2) to connect, you must create a virtual network rule that references subnet2 in vnet2. This rule establishes a private endpoint-like connection through the Azure backbone, bypassing the public internet and honoring the disabled public network access setting.

Exam trap

The trap here is that candidates assume IP firewall rules can still work when public network access is disabled, but in reality, disabling public network access completely overrides all IP-based rules, making virtual network rules the only viable option for cross-VNet connectivity.

How to eliminate wrong answers

Option B is wrong because adding the client's IP address to the ipRules array requires public network access to be enabled; with publicNetworkAccess set to 'Disabled', all IP firewall rules are ignored. Option C is wrong because setting publicNetworkAccess to 'Enabled' and adding an IP firewall rule would expose the server to the public internet, which is unnecessary and less secure when a virtual network rule can provide private connectivity. Option D is wrong because creating a server-level firewall rule to allow all Azure services (0.0.0.0/0) also requires public network access to be enabled and does not restrict traffic to a specific virtual network; it would allow any Azure service, not just the client in vnet2.

Practice this question →

MCQeasy

Your organization requires that all Azure SQL Database deployments use the most recent version of SQL Server engine to ensure security and performance. Which deployment option automatically provides the latest engine version without requiring manual upgrades?

A.SQL Server on Azure Virtual Machines.

B.Azure SQL Database elastic pool.

C.Azure SQL Database (single database).

D.Azure SQL Managed Instance.

AnswerC

Azure SQL Database is always updated to the latest engine version.

Why this answer

Azure SQL Database (single database) is a Platform-as-a-Service (PaaS) offering where Microsoft manages the underlying SQL Server engine. Microsoft automatically applies the latest engine version updates during the service's maintenance windows, ensuring you always run a supported and current version without any manual upgrade effort. This aligns directly with the requirement for automatic, hands-off version management.

Exam trap

The trap here is that candidates confuse 'automatic updates' with 'latest engine version' and assume that all PaaS options (like Azure SQL Managed Instance) provide the exact same latest engine version immediately, but in reality, Azure SQL Managed Instance may run a slightly older stable version unless you explicitly opt into newer preview versions, whereas single databases are always on the most current generally available engine.

How to eliminate wrong answers

Option A is wrong because SQL Server on Azure Virtual Machines is an Infrastructure-as-a-Service (IaaS) offering where you are responsible for manually applying SQL Server updates and version upgrades, including major version changes. Option B is wrong because Azure SQL Database elastic pool is built on the same PaaS engine as single databases, but it is a deployment model for pooling resources across multiple databases, not a separate engine version; it also receives automatic updates, but the question asks for a deployment option that 'automatically provides the latest engine version,' and elastic pools share the same automatic update mechanism as single databases, so the distinction is that the question's correct answer is the single database as the most direct and common example, though elastic pools also qualify; however, the exam expects single database as the canonical answer. Option D is wrong because Azure SQL Managed Instance is also a PaaS offering with automatic updates, but it runs a near-100% compatible SQL Server engine that may lag slightly behind the latest engine version available in single databases, and it requires a manual decision to opt into preview or newer engine versions via configuration, so it does not 'automatically provide the latest engine version' without any action.

Practice this question →

MCQeasy

You need to create an Azure SQL Database that automatically scales storage up to 4 TB. Which service tier should you select?

A.Standard

B.Hyperscale

C.Basic

D.Business Critical

AnswerD

Business Critical supports up to 4 TB (with provisioned compute).

Why this answer

Business Critical is the correct service tier because it supports up to 4 TB of storage with provisioned compute, high-performance local SSD storage, and built-in high availability. Standard tier maxes out at 1 TB, Basic at 2 GB, and Hyperscale is designed for up to 100 TB but uses a different architecture with page servers and log services, not the fixed 4 TB limit.

Exam trap

The trap here is that candidates often assume Hyperscale is the only tier for large storage, but the question specifies 'automatically scales storage up to 4 TB'—a fixed cap that matches Business Critical, not Hyperscale's unbounded scaling model.

How to eliminate wrong answers

Option A is wrong because Standard tier has a maximum storage limit of 1 TB, not 4 TB. Option B is wrong because Hyperscale supports up to 100 TB, which exceeds the 4 TB requirement, but its architecture (with page servers and buffer pool extension) is not designed for a fixed 4 TB limit and is intended for very large databases, not the specific 4 TB cap. Option C is wrong because Basic tier is limited to 2 GB of storage, far below the 4 TB requirement.

Practice this question →

MCQeasy

You need to ensure that all queries executed against an Azure SQL Database are audited and logged to a Log Analytics workspace for security analysis. Which feature should you enable?

A.SQL Vulnerability Assessment

B.Advanced Threat Protection

C.Microsoft Defender for Cloud

D.Azure SQL Auditing

AnswerD

Auditing captures database events and can be configured to send logs to Log Analytics workspace.

Why this answer

Azure SQL Auditing is the feature specifically designed to track database events and write them to an audit log destination, including a Log Analytics workspace. This enables security analysis by capturing all queries executed against the database, which can then be queried and analyzed within the workspace. The other options focus on vulnerability scanning, threat detection, or security posture management, not on recording query execution logs.

Exam trap

The trap here is that candidates often confuse 'threat detection' (Advanced Threat Protection) or 'security posture management' (Microsoft Defender for Cloud) with the specific need for a full query audit log, but only Azure SQL Auditing provides the granular, configurable logging of all executed queries to a Log Analytics workspace.

How to eliminate wrong answers

Option A is wrong because SQL Vulnerability Assessment is a service that scans for potential database vulnerabilities and misconfigurations, not a feature that logs query execution. Option B is wrong because Advanced Threat Protection detects anomalous activities and potential threats, but it does not provide a full audit trail of all queries. Option C is wrong because Microsoft Defender for Cloud is a unified security management platform that provides security recommendations and threat protection across cloud workloads, but it does not itself enable per-query auditing to a Log Analytics workspace.

Practice this question →

100

MCQhard

You are evaluating the configuration of an Azure SQL Database as shown in the exhibit. You need to ensure that the database remains available during a zonal failure without data loss. Which feature contributes to this requirement?

A.General Purpose service tier.

B.Read scale-out.

C.Geo-redundant backup storage.

D.Zone redundancy enabled on Business Critical tier.

AnswerD

Zone redundancy provides synchronous replication across zones.

Why this answer

Zone redundancy on the Business Critical tier ensures that database replicas are placed in different availability zones within the same Azure region. During a zonal failure, the service automatically fails over to a synchronous replica in another zone, guaranteeing zero data loss because all transactions are synchronously committed across replicas. This directly meets the requirement of remaining available without data loss during a zonal outage.

Exam trap

The trap here is that candidates often confuse zone redundancy (which protects within a region) with geo-redundancy (which protects across regions), or assume that any service tier with high availability features (like General Purpose) can guarantee zero data loss during a zonal failure, when only the Business Critical tier with zone redundancy provides synchronous replication and automatic failover without data loss.

How to eliminate wrong answers

Option A is wrong because the General Purpose service tier uses remote storage and asynchronous replication, which cannot guarantee zero data loss during a zonal failure; it relies on page blob replication that may lose recent writes. Option B is wrong because Read scale-out provides a read-only replica for offloading read workloads, but it does not protect against zonal failures or ensure availability with zero data loss for write operations. Option C is wrong because Geo-redundant backup storage (RA-GRS) protects against regional disasters by storing backups in a paired region, but it does not provide real-time failover or zero data loss during a zonal failure within the primary region.

Practice this question →

101

MCQeasy

You are responsible for cost optimization of a non-production Azure SQL Database that is used for development testing. The database is only active during business hours (9 AM to 5 PM) on weekdays. Which compute tier and configuration would minimize cost while ensuring the database is available during working hours?

A.Use the serverless compute tier with auto-pause enabled and a 1-hour auto-pause delay.

B.Use the Hyperscale tier with a minimum of 1 vCore and disable auto-pause.

C.Use the provisioned General Purpose tier with 2 vCores and disable auto-pause.

D.Use the provisioned Business Critical tier with 1 vCore and enable auto-pause.

AnswerA

Correct: Serverless tier with auto-pause pauses the database during inactivity, saving costs outside business hours.

Why this answer

The serverless compute tier with auto-pause enabled and a 1-hour auto-pause delay is the most cost-effective choice for a non-production database used only during business hours. Serverless automatically pauses the database after 1 hour of inactivity (e.g., overnight and weekends), charging only for compute during active periods and storage at all times. This aligns perfectly with the 9 AM–5 PM weekday usage pattern, minimizing cost while ensuring the database is available when needed.

Exam trap

The trap here is that candidates may assume any tier with auto-pause enabled is sufficient, but they overlook that the underlying tier (e.g., Business Critical or Hyperscale) has a much higher base cost and is not designed for cost-optimized dev/test scenarios, making serverless the only truly cost-effective choice for intermittent usage.

How to eliminate wrong answers

Option B is wrong because the Hyperscale tier is designed for large, high-throughput production workloads with rapid scaling and read scale-out, not for cost optimization of a small dev/test database; it incurs higher base costs even at 1 vCore and disabling auto-pause means compute runs 24/7. Option C is wrong because the provisioned General Purpose tier with 2 vCores and auto-pause disabled runs compute continuously, incurring charges for idle hours overnight and weekends, which is wasteful for a non-production database. Option D is wrong because the Business Critical tier is a premium tier with high availability and local SSD storage, intended for mission-critical workloads; even with 1 vCore and auto-pause enabled, it is significantly more expensive than serverless and over-provisioned for development testing.

Practice this question →

102

Multi-Selecthard

Which THREE of the following are features available in Azure SQL Managed Instance that are not available in Azure SQL Database (single database)?

Select 3 answers

A.Transparent Data Encryption (TDE)

B.Cross-database queries (three- and four-part names)

C.Elastic pools

D.Service Broker

E.SQL Agent jobs

AnswersB, D, E

Managed Instance supports cross-database queries, single DB does not.

Why this answer

Cross-database queries using three- and four-part names (e.g., `[DB1].[schema].[table]`) are supported in Azure SQL Managed Instance because it provides full SQL Server engine compatibility, including the ability to query across databases within the same instance. Azure SQL Database single databases are isolated and do not support cross-database queries, as each database is a separate logical boundary without instance-level context.

Exam trap

The trap here is that candidates often confuse features available in both services (like TDE) with those exclusive to Managed Instance, or they incorrectly assume Elastic pools are a Managed Instance feature when they are actually a single-database scaling option.

Practice this question →

103

MCQeasy

You need to provision an Azure SQL Database that supports a high-availability SLA of 99.995% and provides automatic failover to a secondary region in case of a regional outage. Which deployment option should you choose?

A.Azure SQL Database in Hyperscale tier

B.Azure SQL Database with zone-redundant configuration

C.Azure SQL Database with active geo-replication and failover group

D.Azure SQL Managed Instance with failover group

AnswerC

This combination provides up to 99.995% SLA and cross-region failover.

Why this answer

Active geo-replication with a failover group provides an SLA of 99.995% by replicating data asynchronously to a secondary region and enabling automatic failover during a regional outage. This is the only option that meets both the high-availability SLA and cross-region disaster recovery requirement.

Exam trap

The trap here is that candidates confuse zone-redundant configuration (which only protects within a region) with cross-region disaster recovery, or assume that any high-SLA tier automatically includes cross-region failover.

How to eliminate wrong answers

Option A is wrong because Hyperscale tier supports rapid scaling and read scale-out but does not provide a 99.995% SLA or automatic cross-region failover; its high-availability is zone-redundant within a single region. Option B is wrong because zone-redundant configuration protects against datacenter failures within a region, not against a full regional outage, and its SLA is 99.995% only for the Business Critical tier, but it lacks cross-region failover. Option D is wrong because Azure SQL Managed Instance with a failover group provides disaster recovery but its SLA is 99.99%, not 99.995%, and it is not a deployment option for Azure SQL Database.

Practice this question →

104

MCQmedium

You are configuring Azure SQL Database for a new application. The security policy requires that all connections use Microsoft Entra authentication and that the database blocks IP addresses from outside your corporate network. You also need to ensure that the application can connect without storing credentials in code. Which combination of features should you implement?

A.Always Encrypted, VNet service endpoints, and SQL authentication

B.Microsoft Entra authentication, firewall rules, and managed identity

C.Transparent Data Encryption, IP firewall rules, and connection strings

D.Azure Defender for SQL, firewall rules, and service principal

AnswerB

Managed identity allows application to authenticate without credentials.

Why this answer

Option B is correct because it satisfies all three requirements: Microsoft Entra authentication enforces identity-based access, firewall rules block IP addresses outside the corporate network, and a managed identity allows the application to connect without storing credentials in code by using a system-assigned or user-assigned identity to obtain an access token from Microsoft Entra ID.

Exam trap

The trap here is that candidates often confuse managed identity with a service principal, not realizing that a service principal still requires a secret or certificate to be stored, whereas a managed identity eliminates credential storage entirely.

How to eliminate wrong answers

Option A is wrong because Always Encrypted protects data at rest and in transit but does not enforce authentication or IP-based blocking, and SQL authentication does not meet the Microsoft Entra authentication requirement. Option C is wrong because Transparent Data Encryption (TDE) encrypts data at rest but does not control authentication or credentialless connections, and connection strings typically contain credentials. Option D is wrong because Azure Defender for SQL provides security monitoring and threat detection, not authentication or credentialless connectivity, and a service principal still requires credential management (e.g., client secret or certificate) unless combined with managed identity.

Practice this question →

105

MCQmedium

You are deploying an Azure SQL Database for a new application that requires consistent performance and low latency. The database will be accessed by users across multiple Azure regions. Which deployment option should you use?

A.Managed Instance with failover groups

B.Single database with Active Geo-Replication

C.Single database in one region

D.Elastic pool in one region

AnswerB

Readable secondaries in multiple regions reduce read latency.

Why this answer

Active Geo-Replication for a single database enables continuous data synchronization to readable secondary replicas in different Azure regions, providing low-latency read access for globally distributed users and supporting manual failover for write availability. This meets the requirement for consistent performance and low latency across multiple regions without the orchestration overhead of failover groups.

Exam trap

The trap here is that candidates often choose failover groups (Option A) thinking they provide readable secondaries, but in a Managed Instance, failover groups do not support readable secondaries until failover occurs, whereas Active Geo-Replication for single databases does.

How to eliminate wrong answers

Option A is wrong because Managed Instance with failover groups provides automated failover but does not support readable secondaries for global read-scale; secondaries are not readable until failover occurs, so it does not address low-latency reads across regions. Option C is wrong because a single database in one region cannot serve users in other regions with low latency, as all traffic must traverse the network to that single region, increasing latency. Option D is wrong because an elastic pool in one region similarly confines all databases to a single region, failing to provide geo-distributed read access or low latency for multi-region users.

Practice this question →

106

Multi-Selecteasy

Which TWO actions are required to implement transparent data encryption (TDE) with customer-managed keys for an Azure SQL Database?

Select 2 answers

A.Create an Azure Key Vault and a key.

B.Assign the key to the Azure SQL logical server and enable TDE.

C.Set the backup encryption level to 'Encrypted'.

D.Create a server certificate in the database.

E.Configure column encryption keys in the database.

AnswersA, B

Required for customer-managed TDE.

Why this answer

Option A is correct because TDE with customer-managed keys requires an Azure Key Vault to store the key, which provides centralized key management and control. The key in the vault is used to protect the database encryption key (DEK), ensuring that the customer retains ownership and control over the encryption material.

Exam trap

The trap here is that candidates confuse TDE with Always Encrypted or on-premises certificate-based TDE, leading them to select options about column encryption keys or server certificates, which are not part of Azure SQL Database TDE implementation.

Practice this question →

107

Multi-Selectmedium

You are designing a security strategy for Azure SQL Database. You need to ensure that database access is secured using Microsoft Entra ID (formerly Azure Active Directory) authentication. Which THREE actions should you take? (Choose THREE.)

Select 3 answers

A.Create a Microsoft Entra ID administrator for the Azure SQL logical server.

B.Disable SQL Server authentication after migrating to Entra ID.

C.Configure applications to use Microsoft Entra ID with MFA.

D.Enable Transparent Data Encryption (TDE).

E.Create a contained database user mapped to a Microsoft Entra ID principal.

AnswersA, C, E

An Entra ID admin is required to enable Entra ID authentication.

Why this answer

Creating a Microsoft Entra ID administrator for the Azure SQL logical server is required to enable Entra ID authentication at the server level. This administrator is a user or group from Entra ID that has permissions to manage all databases on the server, including creating contained database users mapped to Entra ID principals. Without this step, Entra ID authentication cannot be configured for the server or its databases.

Exam trap

The trap here is that candidates often confuse enabling Entra ID authentication with disabling SQL Server authentication (Option B) or with enabling TDE (Option D), both of which are independent security controls not required for Entra ID-based access.

Practice this question →

108

MCQmedium

You are a database administrator for a large e-commerce company. The company has an Azure SQL Database named 'OrdersDB' in the General Purpose service tier, with a 2 TB database. The database has a table named 'OrderDetails' that contains over 1 billion rows. The table is partitioned by date using a partition function that splits data by month. You notice that queries that filter on a specific date range are performing poorly, and the query plan shows a clustered index scan on the partition column. The table has a clustered columnstore index to support analytics. You need to improve query performance for date-range queries without affecting the analytics workload. What should you do?

A.Create a new partition scheme with a different partition function.

B.Create a nonclustered index on the partition key column (OrderDate) and include other columns as needed.

C.Rebuild the clustered columnstore index with a different compression setting.

D.Change the clustered index to a rowstore B-tree index on the partition key.

AnswerB

Nonclustered index supports seek for date range queries without affecting columnstore.

Why this answer

Option B is correct because creating a nonclustered index on the partition key (OrderDate) allows SQL Server to perform an index seek for date-range queries, avoiding the full clustered columnstore index scan. This index does not interfere with the existing columnstore index, which remains optimal for analytics workloads. The nonclustered index can include additional columns as included columns to cover the query without touching the base table.

Exam trap

The trap here is that candidates assume partitioning alone improves query performance, but without an appropriate index, the query still scans all partitions; they may also mistakenly think rebuilding or changing the clustered index is necessary, ignoring that a nonclustered index can coexist with a columnstore index.

How to eliminate wrong answers

Option A is wrong because creating a new partition scheme with a different partition function does not address the lack of a suitable index for point or range lookups; partitioning alone does not provide an index seek capability. Option C is wrong because rebuilding the clustered columnstore index with a different compression setting (e.g., COLUMNSTORE_ARCHIVE) would not change the scan-based access method for date-range queries and could degrade performance further. Option D is wrong because changing the clustered index to a rowstore B-tree on the partition key would eliminate the columnstore index, breaking the analytics workload that relies on columnstore compression and batch-mode processing.

Practice this question →

109

MCQeasy

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

A.Enable Virtual Network service endpoints for Azure SQL Database

B.Use Azure Private Link for Azure SQL Database

C.Configure the Azure SQL Database firewall to allow Azure services

D.Deploy Azure SQL Database inside a virtual network

AnswerB

Private Link uses private IP addresses in your VNet.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

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

Practice this question →

110

MCQeasy

A DBA needs to create a new Azure SQL Database and wants to ensure that the database automatically fails over to a secondary region without manual intervention. The recovery point objective (RPO) is 5 seconds. What should the DBA configure?

A.Active geo-replication with failover group

B.Standard geo-replication

C.Local redundancy with automatic failover

D.Read-scale out

AnswerA

Failover groups provide automatic failover and synchronous replication for RPO of 5 seconds.

Why this answer

Active geo-replication with a failover group is the correct choice because it provides automatic, customer-managed failover to a secondary region with an RPO of 5 seconds. The failover group orchestrates the failover of multiple databases simultaneously and supports automatic failover policies, meeting the requirement for zero manual intervention. Standard geo-replication does not support automatic failover, and other options do not provide cross-region disaster recovery.

Exam trap

The trap here is that candidates often confuse 'standard geo-replication' with 'active geo-replication with failover group,' assuming both support automatic failover, but only the latter provides the automatic, policy-driven failover required for zero manual intervention.

How to eliminate wrong answers

Option B is wrong because standard geo-replication requires manual initiation of failover and does not support automatic failover, failing the 'without manual intervention' requirement. Option C is wrong because local redundancy with automatic failover refers to zone-redundant configurations within a single region, not cross-region failover, and cannot meet the RPO of 5 seconds for regional disasters. Option D is wrong because read-scale out is a feature for offloading read-only workloads to a replica in the same region, not for disaster recovery or automatic failover to a secondary region.

Practice this question →

111

MCQhard

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

A.Use elastic database query to reference external tables

B.Create a linked server in Azure SQL Database

C.Migrate to Azure SQL Managed Instance instead

D.Refactor the application to eliminate cross-database queries or use elastic query

AnswerD

Refactoring or using elastic query resolves the issue.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

112

MCQeasy

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

A.Export to BACPAC and import using SqlPackage

B.Azure Database Migration Service with online migration mode

C.Backup and restore using URL with log shipping

D.Transactional replication from on-premises to Azure SQL Database

AnswerB

DMS online mode minimizes downtime by continuously replicating changes.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

113

MCQmedium

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

A.Synchronous replication

B.Geo-replication

C.Snapshot replication

D.Asynchronous replication

AnswerA

Synchronous replication ensures zero data loss.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

114

MCQmedium

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

A.Enable Transparent Data Encryption (TDE) and configure a server-level audit specification for schema changes.

B.Enable Dynamic Data Masking on the sensitive columns and use Azure SQL Database Auditing.

C.Enable SQL Server Audit on the database and create a database audit specification that captures SELECT and UPDATE on the sensitive columns.

D.Enable Azure SQL Database Auditing and configure the diagnostic settings to send logs to a Log Analytics workspace.

AnswerC

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

115

MCQeasy

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

A.Long-term backup retention

B.Automated backups with geo-restore

C.Zone-redundant configuration

D.Active geo-replication with failover group

AnswerD

Provides low RPO and fast RTO.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

116

MCQmedium

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

A.Azure SQL Database elastic pools with per-database min/max DTU or vCore settings.

B.Azure SQL Database serverless compute tier.

C.Azure SQL Database single databases with DTU-based tier.

D.Azure SQL Managed Instance with multiple databases.

AnswerA

Elastic pools provide resource isolation and predictable performance per database.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

117

MCQhard

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

A.Azure SQL Database offers a wider range of built-in instance-level features.

B.Azure SQL Database supports Hyperscale tier with sub-5 ms read latency on local SSDs.

C.Azure SQL Database allows databases up to 100 TB in size.

D.Azure SQL Database supports active geo-replication with read-scale secondaries.

AnswerB

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

118

Multi-Selecteasy

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

Select 2 answers

A.Use BULK INSERT to export data from on-premises and import to Azure SQL Database.

B.Configure transactional replication between on-premises SQL Server and Azure SQL Database.

C.Use Azure Database Migration Service with online migration mode.

D.Use SQL Server backup to URL and restore to Azure SQL Database.

E.Use the Generate Scripts wizard in SQL Server Management Studio.

AnswersB, C

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

Why this answer

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

Exam trap

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

Practice this question →

119

MCQhard

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

A.Use Azure Database Migration Service with online mode.

B.Export the database as a BACPAC file and import it into the managed instance.

C.Perform a full backup and restore to the managed instance, then apply log backups.

D.Set up transactional replication from on-premises to the managed instance.

AnswerA

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

120

MCQeasy

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

A.Configure long-term retention (LTR) for 90 days

B.Configure point-in-time restore for 35 days

C.Use the default backup retention of 7 days

D.Configure point-in-time restore for 1 day

AnswerC

Default PITR is 7 days, which meets the requirement.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

121

MCQhard

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

A.Enable system-assigned managed identity on the database and grant it permissions to access Purview

B.Use SQL authentication with a dedicated login and firewall rule

C.Assign the Microsoft Purview managed identity the db_datareader role and configure the firewall to allow Purview IP ranges

D.Create a user-assigned managed identity and assign it to the database

AnswerC

Managed identity and firewall rules enable Purview to scan.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

122

Multi-Selectmedium

Which TWO Azure SQL Database features can help you scale out read-intensive workloads without modifying application code?

Select 2 answers

A.Database sharding

B.Geo-replication with readable secondary

C.In-memory OLTP

D.Elastic pool

E.Read scale-out

AnswersB, E

A readable geo-replica can serve read-only queries.

Why this answer

Geo-replication with readable secondary (Option B) is correct because it creates a continuously synchronized read-only replica of your Azure SQL Database in a different Azure region. Applications can connect to this secondary endpoint using the same connection string with 'ApplicationIntent=ReadOnly', enabling read-intensive workloads to be offloaded without any code changes. This provides both scalability for reads and disaster recovery.

Exam trap

The trap here is that candidates often confuse 'Geo-replication with readable secondary' (which requires no code changes) with 'Database sharding' (which requires application logic to distribute data), or they mistakenly think 'In-memory OLTP' can scale out reads when it actually improves write/transaction performance on a single database.

Practice this question →

123

MCQmedium

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

A.Migrate to Azure SQL Managed Instance as it supports SQL Agent and cross-database queries within the same instance.

B.Migrate to SQL Server on Azure Virtual Machines for full control.

C.Migrate to Azure SQL Database elastic query to handle cross-database queries.

D.Migrate to Azure SQL Database instead to reduce costs.

AnswerA

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

124

Multi-Selectmedium

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

Select 2 answers

A.Export the database as a BACPAC file and import into Azure SQL Database

B.Configure transactional replication to replicate changes to Azure SQL Database

C.Use log shipping to continuously copy transaction logs

D.Use the Data Migration Assistant (DMA) to perform an online migration

E.Restore a native SQL Server backup to Azure SQL Database

AnswersA, D

BACPAC is a supported migration method.

Why this answer

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

Exam trap

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

Practice this question →

125

MCQeasy

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

A.Configure Microsoft Intune to manage access.

B.Run a vulnerability assessment scan from the Azure portal.

C.Configure Microsoft Purview to scan the database.

D.Enable Microsoft Defender for SQL on the Azure SQL logical server.

AnswerD

Enabling Defender for SQL activates security alerts and vulnerability assessments.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

126

MCQeasy

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

A.Create an Azure SQL Database elastic pool and add databases to the pool.

B.Install SQL Server on Azure Virtual Machines and host all databases on a single instance.

C.Migrate all databases to a single Azure SQL Managed Instance.

D.Deploy each database as a single Azure SQL Database with its own DTU-based tier.

AnswerA

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

127

MCQeasy

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

A.Business Critical with zone redundancy and a failover group

B.General Purpose serverless with geo-replication

C.General Purpose with active geo-replication and read-scale

D.Hyperscale with zone redundancy enabled

AnswerA

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

128

MCQhard

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

A.Create a plan guide to force a specific plan

B.Use optimize for ad hoc workloads

C.Add query store and force the last good plan

D.Enable forced parameterization for the database

AnswerD

Forced parameterization treats literals as parameters, reducing recompilations.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

129

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.

Practice this question →

130

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.

Practice this question →

131

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.

Practice this question →

132

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.

Practice this question →

133

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.

Practice this question →

134

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.

Practice this question →

135

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.

Practice this question →

136

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.

Practice this question →

137

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.

Practice this question →

138

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.

Practice this question →

139

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.

Practice this question →

140

MCQeasy

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

A.Azure Database Migration Service (DMS)

B.Azure Data Studio with SQL Server migration extension

C.Data Migration Assistant (DMA)

D.SQL Server Management Studio (SSMS)

AnswerC

DMA assesses compatibility with SQL Managed Instance.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

141

MCQeasy

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

A.Configure point-in-time restore (PITR) retention to 10 years.

B.Enable automatic tuning to optimize backups.

C.Configure long-term retention (LTR) policy.

D.Enable geo-redundant backup storage (GRS).

AnswerC

LTR allows up to 10 years of backup retention.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

142

MCQmedium

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

A.Enable the Key Vault firewall, select 'Allow trusted Microsoft services', and configure the managed instance with a system-assigned managed identity

B.Disable the Key Vault firewall and assign the managed instance a service principal

C.Enable the Key Vault firewall and add the managed instance's public IP address to the firewall rules

D.Disable the Key Vault firewall and use a user-assigned managed identity for the managed instance

AnswerA

This limits access to trusted services and uses managed identity.

Why this answer

Option C is correct because enabling the Key Vault firewall with 'Allow trusted Microsoft services' and configuring the managed instance with a managed identity ensures access only from trusted services. Option A is wrong because disabling the firewall would allow public access. Option B is wrong because granting the managed instance's public IP is not recommended and may not work.

Option D is wrong because disabling the firewall and using a service principal is less secure.

Practice this question →

143

MCQhard

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

A.Export to BACPAC and import to Managed Instance

B.Use Azure Database Migration Service with online migration

C.Use transactional replication

D.Back up to URL and restore to Managed Instance

AnswerB

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

144

MCQmedium

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

A.Enable Microsoft Entra ID authentication and assign managed identities to the applications.

B.Use Always Encrypted with column master key in Azure Key Vault.

C.Configure firewall rules to allow application IP addresses.

D.Enable SQL Server authentication and create a login for each application.

AnswerA

Managed identities provide passwordless authentication.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

145

MCQmedium

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

A.Create a contained database user for each service principal and grant the db_owner role.

B.Create a contained database user for each service principal and grant SELECT, INSERT, UPDATE, DELETE on specific tables.

C.Create a server-level login for each service principal and assign db_datareader role in the database.

D.Configure a server-level firewall rule for each service principal IP address.

AnswerB

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

146

Multi-Selectmedium

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

Select 3 answers

A.Requirement for elastic pool scalability.

B.Need for cross-database queries and transactions.

C.Need for automated backups with point-in-time restore.

D.Requirement for SQL Server Agent with job scheduling.

E.Need for a fixed virtual network with private IP addresses.

AnswersB, D, E

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

Why this answer

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

Exam trap

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

Practice this question →

147

MCQmedium

You are designing a disaster recovery strategy for an Azure SQL Database that supports a critical OLTP application. The database is currently deployed in the East US region. You need to ensure that failover occurs automatically with minimal data loss in the event of a regional outage. Which deployment option should you recommend?

A.Enable Active Geo-Replication with a readable secondary in West US.

B.Configure auto-failover groups between East US and West US.

C.Deploy a failover group with manual failover.

D.Use geo-restore to recover the database in West US.

AnswerA

Active Geo-Replication provides automatic failover with minimal data loss when used with failover groups.

Why this answer

Active Geo-Replication with a readable secondary in West US provides automatic, asynchronous replication of transactions to a secondary database in a paired region. In the event of a regional outage, you can initiate a manual or automated failover to the secondary with minimal data loss, as replication lag is typically seconds. This option meets the requirement for automatic failover and minimal data loss, whereas other options either lack automatic failover or introduce significant data loss.

Exam trap

The trap here is that candidates often confuse auto-failover groups (which require manual failover for cross-region scenarios) with Active Geo-Replication's automatic failover capability, or they mistakenly believe geo-restore provides minimal data loss when it actually has a 1-hour RPO.

How to eliminate wrong answers

Option B is wrong because auto-failover groups require the secondary to be in the same region or a paired region, but they do not support automatic failover with minimal data loss; they rely on asynchronous replication and can lose up to 5 seconds of data, but the key issue is that auto-failover groups are designed for manual or automatic failover with a defined replication policy, yet they still use asynchronous replication, which may not meet the 'minimal data loss' requirement as precisely as Active Geo-Replication with a readable secondary. Option C is wrong because deploying a failover group with manual failover requires human intervention to trigger the failover, which does not satisfy the 'automatic failover' requirement. Option D is wrong because geo-restore recovers the database from the most recent geo-replicated backup, which can result in up to one hour of data loss (RPO of 1 hour), far exceeding the 'minimal data loss' requirement.

Practice this question →

148

MCQhard

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

A.Shrink the database

B.Scale up to a higher service objective (e.g., from GP to BC)

C.Increase the log file size

D.Increase the max size of the database

AnswerD

Increasing max size adds more space to the primary filegroup.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

149

MCQhard

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

A.Azure SQL Database Business Critical tier

B.Azure SQL Database Standard tier

C.Azure SQL Database Hyperscale tier

D.Azure SQL Managed Instance Business Critical

AnswerC

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

150

Drag & Dropmedium

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

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

Steps

Order

Why this order

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

Practice this question →