CCNA Plan and implement data platform resources Questions — Page 3 of 3

151

MCQmedium

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

A.sys.dm_exec_requests

B.sys.dm_tran_locks and sys.dm_os_waiting_tasks

C.sys.dm_exec_query_stats

D.sys.dm_tran_active_snapshot_database_transactions

AnswerB

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

152

MCQhard

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

A.Use the Log Replay Service to continuously replicate transaction logs.

B.Set up transactional replication from on-premises to Azure SQL Managed Instance.

C.Use Azure Database Migration Service with online mode.

D.Export the databases as BACPAC files and import them into Azure SQL Managed Instance.

AnswerC

DMS online mode provides minimal downtime and automated validation.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

153

MCQhard

You are deploying an Azure SQL Managed Instance for a sensitive healthcare application. The compliance requirement mandates that all data at rest be encrypted using a customer-managed key stored in Azure Key Vault. The key must be automatically rotated every 90 days. Which configuration should you implement?

A.Use dynamic data masking with a masking function that encrypts sensitive data.

B.Implement row-level security to restrict access to sensitive data.

C.Enable Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault and set a key rotation policy.

D.Enable Always Encrypted with column master key stored in Azure Key Vault and configure automatic key rotation.

AnswerC

TDE with customer-managed keys supports automatic rotation via Azure Key Vault.

Why this answer

Transparent Data Encryption (TDE) with a customer-managed key (CMK) stored in Azure Key Vault is the correct choice because it encrypts the entire database at rest using a key that the customer controls and rotates. Azure SQL Managed Instance supports TDE with CMK, and you can configure automatic key rotation by setting a key rotation policy in Azure Key Vault (e.g., every 90 days). This satisfies the compliance requirement for customer-managed encryption and automated rotation.

Exam trap

The trap here is that candidates confuse Always Encrypted (column-level encryption with client-side key management) with TDE (database-level encryption at rest), and assume automatic key rotation applies to Always Encrypted, whereas Azure Key Vault's automatic rotation only applies to TDE protectors, not Always Encrypted column master keys.

How to eliminate wrong answers

Option A is wrong because dynamic data masking does not encrypt data; it only obfuscates it in query results for unauthorized users, leaving the underlying data in plaintext at rest. Option B is wrong because row-level security controls access to rows based on user predicates but does not encrypt data at rest. Option D is wrong because Always Encrypted encrypts data at the column level in client applications, not the entire database at rest, and its key rotation is manual or application-driven, not automatically enforced by Azure Key Vault policies for TDE.

Practice this question →

154

MCQhard

You are reviewing an ARM template for creating a new Azure SQL Database. The template uses the above JSON to create a database named 'db2' from 'db1'. The source database 'db1' is currently in a failed state due to a storage issue. What will be the result of deploying this template?

A.The deployment will fail because db1 is not in a recoverable state.

B.It will create an empty database because the source is not accessible.

C.It will delete db1 and create db2 as a replacement.

D.It will create a new database by recovering db1 to its last known good state.

AnswerA

Recovery mode requires the source to be dropped or after geo-failover.

Why this answer

The ARM template creates a new database by copying from a source database. Azure SQL Database requires the source database to be in an online and healthy state to perform a copy operation. Since db1 is in a failed state due to a storage issue, it is not accessible for copying, so the deployment will fail.

Exam trap

The trap here is that candidates may confuse a database copy with a point-in-time restore, assuming that a failed source can still be used to create a new database via recovery, but the copy operation explicitly requires an online source.

How to eliminate wrong answers

Option B is wrong because Azure SQL Database does not create an empty database when the source is inaccessible; the copy operation requires a valid, online source. Option C is wrong because the ARM template does not include a delete operation; it only creates a new database from a source, and Azure SQL Database does not automatically delete the source during a copy. Option D is wrong because the template specifies a copy operation, not a point-in-time restore; recovering to a last known good state would require a different ARM template or a restore command, not a database copy.

Practice this question →

155

MCQeasy

You are deploying Azure SQL Database for a new application that requires predictable performance and minimal latency. You need to choose a service tier that provides dedicated resources and allows scaling independently for compute and storage. Which service tier should you select?

A.Business Critical

B.General Purpose

C.Basic

D.Hyperscale

AnswerA

Business Critical provides dedicated resources and low latency, suitable for mission-critical applications.

Why this answer

The Business Critical service tier is designed for applications requiring predictable performance and minimal latency, as it provides dedicated resources (CPU and memory) and uses local SSD storage for the database. It also supports independent scaling of compute and storage, allowing you to adjust DTUs or vCores without affecting storage capacity, which aligns with the requirement for independent scaling.

Exam trap

The trap here is that candidates often confuse 'dedicated resources' with 'General Purpose' because it also offers isolated compute, but they overlook that Business Critical uses local SSD for minimal latency, whereas General Purpose relies on remote storage with higher latency.

How to eliminate wrong answers

Option B (General Purpose) is wrong because it uses remote blob storage (Azure Premium Blob) with a page server architecture, which introduces higher latency compared to local SSD, and while it offers independent scaling of compute and storage, it does not provide the dedicated resource isolation and low-latency guarantees needed for minimal latency. Option C (Basic) is wrong because it is a low-cost tier with limited performance, no dedicated resources, and does not support independent scaling of compute and storage; it is intended for small databases with low concurrency. Option D (Hyperscale) is wrong because, although it offers independent scaling of compute and storage and uses a distributed architecture with local buffer pool extension, it is optimized for very large databases and high throughput, not necessarily for minimal latency in a predictable performance scenario, and it introduces additional complexity with its multiple compute nodes and page servers.

Practice this question →

156

MCQhard

Your company is planning to deploy a new application that uses Azure SQL Database with a Hyperscale service tier. The application requires near-instantaneous scaling of compute resources without downtime. The database will handle large data volumes (over 10 TB). Additionally, the application team wants to use the latest database features, including JSON support and graph queries. Which considerations should you evaluate before choosing Hyperscale?

A.Hyperscale does not support graph data features such as node and edge tables.

B.Hyperscale does not support zone-redundant configuration for high availability.

C.Hyperscale does not support JSON functions like JSON_VALUE and JSON_QUERY.

D.Hyperscale does not support cross-database queries or elastic queries.

AnswerD

Correct: Hyperscale does not support cross-database queries or elastic query features.

Why this answer

Option D is correct because Azure SQL Database Hyperscale does not support cross-database queries or elastic queries. This limitation is critical for the application team if they need to query across multiple databases in a single query, which is a common requirement for distributed applications. The Hyperscale architecture separates compute from storage, making cross-database query capabilities unavailable, unlike other service tiers such as General Purpose or Business Critical.

Exam trap

The trap here is that candidates often assume Hyperscale supports all features of other tiers due to its advanced capabilities, but Microsoft specifically excludes cross-database and elastic queries in Hyperscale, making it a common oversight in exam questions.

How to eliminate wrong answers

Option A is wrong because Hyperscale fully supports graph data features, including node and edge tables, as part of the SQL Server graph database capabilities available in Azure SQL Database. Option B is wrong because Hyperscale does support zone-redundant configuration for high availability, which provides resilience within an Azure region by replicating across availability zones. Option C is wrong because Hyperscale supports all JSON functions, including JSON_VALUE and JSON_QUERY, as these are standard T-SQL features available across all service tiers.

Practice this question →

157

Multi-Selectmedium

You are planning a migration of on-premises SQL Server databases to Azure SQL Managed Instance. Which TWO tools or services can you use to assess compatibility and identify potential migration blockers? (Select TWO.)

Select 2 answers

A.Data Migration Assistant (DMA)

B.SQL Server Management Studio (SSMS) – Upgrade Data Tier Application wizard

C.Azure Database Migration Service (DMS)

D.Azure Migrate

E.Azure Data Studio – Migration Wizard

AnswersA, D

DMA assesses SQL Server compatibility with Azure SQL Managed Instance.

Why this answer

Data Migration Assistant (DMA) is the correct tool because it performs a detailed assessment of on-premises SQL Server databases, identifying compatibility issues and migration blockers specifically for Azure SQL Managed Instance. It generates a report of feature parity, breaking changes, and behavior differences, enabling you to remediate issues before migration.

Exam trap

The trap here is confusing the assessment tool (DMA) with the migration execution tool (DMS) or assuming that SSMS or Azure Data Studio wizards provide the same depth of compatibility analysis, when in fact DMA is the dedicated assessment tool for Azure SQL Managed Instance migrations.

Practice this question →

158

MCQmedium

You are configuring Azure SQL Database for an e-commerce application that experiences variable traffic. You need to ensure that the database can automatically scale resources based on demand without manual intervention. The solution must also support scaling to zero compute when not in use to save costs. Which Azure SQL Database offering should you use?

A.Azure SQL Database serverless.

B.Azure SQL Database elastic pool.

C.Azure SQL Database Hyperscale.

D.Azure SQL Database Business Critical tier.

AnswerA

Serverless auto-scales and pauses to zero compute during inactivity.

Why this answer

Azure SQL Database serverless is the correct choice because it automatically scales compute resources based on demand and supports pausing the database when idle, effectively scaling to zero compute to save costs. This aligns perfectly with the requirements of variable traffic and cost optimization without manual intervention.

Exam trap

The trap here is that candidates may confuse the auto-scaling of serverless with the resource pooling of elastic pools, or assume Hyperscale's high performance includes cost-saving idle scaling, but only serverless offers the specific 'scale to zero' capability.

How to eliminate wrong answers

Option B is wrong because Azure SQL Database elastic pool provides resource sharing among multiple databases but does not support scaling to zero compute; it maintains a minimum resource allocation. Option C is wrong because Azure SQL Database Hyperscale is designed for large databases with high storage and throughput needs, not for automatic scaling to zero compute or cost savings through pausing. Option D is wrong because the Business Critical tier offers high availability and performance with fixed compute resources, lacking the ability to scale to zero or pause automatically.

Practice this question →

159

MCQhard

Your Azure SQL Database is running in the General Purpose tier. You notice that read queries are experiencing high latency. You need to improve read performance without changing the compute size. What should you implement?

A.Upgrade to the Hyperscale service tier

B.Enable read scale-out

C.Create columnstore indexes on the tables

D.Use in-memory OLTP for the tables

AnswerB

Read scale-out directs read-only queries to a readable secondary, reducing load on the primary.

Why this answer

Read scale-out in Azure SQL Database allows you to offload read-only queries to a secondary replica, reducing latency on the primary replica without changing the compute size. This feature is available in the Premium and Business Critical service tiers, but not in General Purpose. Since the question specifies the General Purpose tier, enabling read scale-out is not actually supported, making this a trick scenario.

However, among the given options, enabling read scale-out is the only one that directly addresses read latency by leveraging a read-only replica, even though it requires a tier change to Premium or Business Critical.

Exam trap

The trap here is that candidates may assume read scale-out is available in all service tiers, but it is only supported in Premium and Business Critical, not in General Purpose, making the correct answer technically unfeasible under the given constraints.

How to eliminate wrong answers

Option A is wrong because upgrading to the Hyperscale service tier changes the service tier, which violates the constraint of not changing the compute size; Hyperscale also uses a different architecture that may not directly improve read latency without additional configuration. Option C is wrong because creating columnstore indexes improves performance for analytical and aggregation queries, not for general read queries experiencing high latency; they are not a direct solution for read latency on transactional workloads. Option D is wrong because in-memory OLTP is designed to accelerate transaction processing and reduce latency for write-heavy workloads, not to improve read query performance; it also requires specific table and stored procedure modifications.

Practice this question →

160

MCQhard

You are designing a disaster recovery strategy for an Azure SQL Database that uses the Business Critical tier. The primary region is East US, and you need to ensure automatic failover with zero data loss in case of a regional outage. The secondary region must be West US. You also need to minimize latency for read-only workloads in the secondary region. What should you implement?

A.Create a failover group with the primary in East US and a readable secondary in West US.

B.Create a failover group with the primary in East US and a non-readable secondary in West US using Basic tier.

C.Configure active geo-replication with a readable secondary in West US.

D.Configure active geo-replication with a readable secondary in West US using Standard tier.

AnswerA

Failover group provides automatic failover and readable secondary with synchronous replication for zero data loss.

Why this answer

A failover group with a readable secondary in the Business Critical tier provides automatic failover with zero data loss because it uses synchronous replication. The readable secondary in West US minimizes latency for read-only workloads by allowing direct connections to the secondary replica. This meets all requirements: automatic failover, zero data loss, and low-latency reads in the secondary region.

Exam trap

The trap here is that candidates often confuse active geo-replication (asynchronous, manual failover) with failover groups (synchronous for Business Critical, automatic failover), and assume any readable secondary guarantees zero data loss regardless of replication mode.

How to eliminate wrong answers

Option B is wrong because a Basic tier secondary cannot be used in a failover group; failover groups require the same service tier as the primary, and Basic tier does not support readable secondaries or synchronous replication. Option C is wrong because active geo-replication does not provide automatic failover; it requires manual initiation or custom scripting, and it does not guarantee zero data loss as it uses asynchronous replication. Option D is wrong because active geo-replication with Standard tier uses asynchronous replication, which cannot guarantee zero data loss, and it also lacks automatic failover capability.

Practice this question →

161

Multi-Selecthard

You are designing an Azure SQL Database solution for a SaaS application that will host multiple tenants. Each tenant has a dedicated database. You need to ensure that tenants are isolated and resource usage is optimized. Which two features should you use? (Choose TWO.)

Select 2 answers

A.Serverless compute tier

B.DTU-based purchasing model

C.Elastic pool

D.Hyperscale service tier

E.vCore-based purchasing model

AnswersB, C

DTU pools are cost-effective for multi-tenant.

Why this answer

Option B (DTU-based purchasing model) is correct because it provides a simple, pre-configured bundle of compute, storage, and I/O resources, which is ideal for SaaS environments with multiple dedicated databases where predictable performance and isolation are needed. Option C (Elastic pool) is correct because it allows multiple databases to share a fixed pool of DTU resources, optimizing cost and resource utilization across tenants while maintaining logical isolation.

Exam trap

The trap here is that candidates often confuse the vCore-based model with better isolation, but for multi-tenant SaaS with dedicated databases, DTU-based elastic pools provide the optimal balance of isolation and resource optimization.

Practice this question →

162

MCQmedium

You are designing a data archiving solution for an Azure SQL Database. The database contains a table with over 100 million rows of historical sales data that is rarely accessed. You need to reduce storage costs while keeping the data available for occasional queries. What should you do?

A.Partition the table by date and compress each partition

B.Use page compression on the table

C.Delete old data and store it in Azure Blob Storage as CSV files

D.Use Stretch Database to migrate cold data to Azure Blob Storage

AnswerD

Stretch Database moves cold data to Azure Blob Storage transparently, reducing storage costs while keeping data queryable via SQL Server.

Why this answer

Stretch Database (now deprecated but still tested in DP-300) transparently migrates cold data from an Azure SQL Database table to Azure Blob Storage while keeping the data online and queryable via T-SQL. This reduces storage costs for rarely accessed historical data without requiring application changes, as the database engine automatically routes queries to the appropriate storage tier.

Exam trap

The trap here is that candidates often choose partitioning or compression (A or B) because they reduce storage size, but they fail to recognize that the question explicitly asks for cost reduction by moving data to cheaper storage, not just shrinking it within the same expensive tier.

How to eliminate wrong answers

Option A is wrong because partitioning by date and compressing each partition reduces storage size but does not offload data to cheaper storage; the data remains in the premium-priced SQL Database storage. Option B is wrong because page compression only reduces the storage footprint within the database, not moving data to a lower-cost tier like Blob Storage. Option C is wrong because deleting data and storing it as CSV files in Blob Storage breaks the ability to query the data with T-SQL without manual import, and it loses relational integrity and indexing.

Practice this question →

163

Multi-Selectmedium

Which TWO actions are required to implement Azure SQL Database automatic failover with zero data loss?

Select 2 answers

A.Create an auto-failover group.

B.Configure active geo-replication.

C.Enable read scale-out.

D.Use Premium or Business Critical service tier.

E.Configure multiple readable secondaries.

AnswersA, D

Auto-failover groups manage failover orchestration.

Why this answer

An auto-failover group is required because it provides automatic failover capability across Azure SQL Database instances, which is essential for achieving zero data loss during a regional outage. The Premium or Business Critical service tier is required because these tiers use locally redundant storage with synchronous replication to ensure that transactions are committed without data loss, unlike the Standard or General Purpose tiers that use asynchronous replication.

Exam trap

The trap here is that candidates often confuse active geo-replication (which is asynchronous and can lose data) with auto-failover groups (which can achieve zero data loss only when used with Premium or Business Critical tiers), leading them to select active geo-replication as a correct answer.

Practice this question →

164

MCQmedium

You are managing an Azure SQL Managed Instance that hosts multiple databases for a financial application. You need to implement a security solution that meets compliance requirements by auditing all database activity and sending the audit logs to a centralized Log Analytics workspace for analysis. The solution must also support real-time alerts on suspicious activities. What should you configure?

A.Enable Microsoft Defender for Cloud and configure SQL vulnerability assessment.

B.Enable auditing to a storage account and use Microsoft Intune for monitoring.

C.Enable auditing to a Log Analytics workspace and integrate with Microsoft Sentinel.

D.Enable Microsoft Purview Data Map for the managed instance.

AnswerC

Auditing to LA workspace and Sentinel enables real-time detection and alerting.

Why this answer

Option C is correct because auditing to a Log Analytics workspace allows centralized collection of audit logs, which can then be integrated with Microsoft Sentinel for real-time analytics, threat detection, and automated alerting on suspicious activities. This meets both the compliance requirement for auditing and the operational need for real-time alerts.

Exam trap

The trap here is that candidates may confuse Microsoft Defender for Cloud (which provides vulnerability assessment and security recommendations) with a full auditing and SIEM solution, or mistakenly think that a storage account plus Intune can provide real-time alerting, when in fact only Log Analytics with Sentinel delivers both centralized auditing and real-time threat detection.

How to eliminate wrong answers

Option A is wrong because Microsoft Defender for Cloud and SQL vulnerability assessment focus on security posture and vulnerability scanning, not on auditing all database activity or sending logs to a Log Analytics workspace for real-time alerts. Option B is wrong because while auditing to a storage account captures logs, Microsoft Intune is a mobile device management (MDM) and endpoint management tool, not a monitoring or alerting solution for database audit logs. Option D is wrong because Microsoft Purview Data Map is designed for data governance, cataloging, and lineage, not for auditing database activity or providing real-time security alerts.

Practice this question →

165

MCQeasy

You are deploying an Azure SQL Database for a development environment. The database will be used for testing and will be deleted after three months. You want to minimize costs while ensuring the database can be restored to any point within the retention period. Which backup retention policy should you configure?

A.Configure geo-redundant backup storage.

B.Configure short-term backup retention to 7 days.

C.Configure short-term backup retention to 14 days.

D.Configure long-term backup retention (LTR) for 1 year.

AnswerB

7-day retention is the default and meets the need for point-in-time restore within retention.

Why this answer

Option B is correct because the scenario specifies a development database that will be deleted after three months, with the requirement to restore to any point within the retention period. The default short-term backup retention for Azure SQL Database is 7 days, which provides point-in-time restore (PITR) capability within that window. Since the database is temporary and cost minimization is key, 7 days meets the requirement without incurring extra storage costs for longer retention or geo-redundancy.

Exam trap

The trap here is that candidates may assume a longer retention period (14 days or 1 year) is always better, but the question explicitly asks to minimize costs while meeting the restore requirement, making the default 7-day retention the most cost-effective choice.

How to eliminate wrong answers

Option A is wrong because geo-redundant backup storage (RA-GRS) is designed for disaster recovery across regions, which is unnecessary for a temporary development database and increases cost. Option C is wrong because configuring short-term retention to 14 days would double the backup storage cost compared to 7 days, and the scenario does not require a retention period longer than the default. Option D is wrong because long-term retention (LTR) for 1 year is intended for compliance or archival needs, not for a database that will be deleted in three months, and it incurs significant additional cost.

Practice this question →