CCNA Identify considerations for relational data on Azure Questions — Page 4 of 4

226

MCQmedium

A retail company uses Azure SQL Database to store a large fact table of sales transactions with millions of rows. They run complex aggregate queries (SUM, COUNT, AVG) across many rows for monthly reports. These queries take too long. Which index type should they add to the table to improve performance?

A.Clustered B-tree index

B.Nonclustered rowstore index

C.Clustered columnstore index

D.Nonclustered columnstore index

AnswerC

Correct. A clustered columnstore index is ideal for data warehousing and analytical workloads, significantly improving aggregate query performance.

Why this answer

Clustered columnstore indexes are optimized for large fact tables and analytical workloads because they store data in a columnar format, which significantly reduces the amount of data read from disk for aggregate queries like SUM, COUNT, and AVG. This index type also uses batch processing and compression to accelerate query performance on millions of rows, making it ideal for monthly reporting queries.

Exam trap

The trap here is that candidates often confuse nonclustered columnstore indexes with clustered columnstore indexes, assuming any columnstore index will suffice, but only the clustered version is designed for large fact tables with heavy aggregation workloads and avoids the overhead of maintaining a separate rowstore index.

How to eliminate wrong answers

Option A is wrong because a clustered B-tree index stores data in row-based pages and is optimized for point lookups and range scans, not for large-scale aggregations across millions of rows. Option B is wrong because a nonclustered rowstore index also uses a row-based structure and, while it can cover some queries, it does not provide the columnar compression and batch processing needed for efficient aggregate operations. Option D is wrong because a nonclustered columnstore index is a secondary index that requires the table to have a separate clustered index, adding overhead for writes and not being as efficient as a clustered columnstore index for full-table scans and aggregations.

Practice this question →

227

MCQhard

A company uses Azure SQL Database with geo-replication for disaster recovery. During a regional outage, they manually failover to the secondary region. After the primary region is restored, they need to re-establish geo-replication with minimal downtime. What should they do?

A.Initiate a planned failover to switch back to the original region

B.Drop the secondary database and create a new one

C.Delete the geo-replication link and create a new one

D.Manually swap the roles of the primary and secondary

AnswerA

A planned failover will switch roles with no data loss and re-establish geo-replication.

Why this answer

After a manual failover to the secondary region, the original primary becomes a secondary database. To re-establish geo-replication with minimal downtime, you should initiate a planned failover (also called a graceful failover) to switch back to the original region. This operation reverses the roles without data loss and avoids the need to reseed the database, keeping downtime to seconds.

Exam trap

The trap here is that candidates confuse the initial failover (which may be forced) with the recovery process, assuming they must recreate the geo-replication link or drop the database, when in fact a planned failover cleanly reverses the roles with minimal downtime.

How to eliminate wrong answers

Option B is wrong because dropping the secondary database and creating a new one would require a full data reseed, causing significant downtime and data transfer. Option C is wrong because deleting the geo-replication link and creating a new one would also force a full reseed, which is unnecessary and introduces longer downtime. Option D is wrong because manually swapping roles is not a supported operation; Azure SQL Database uses the ALTER DATABASE ...

FAILOVER command to perform a controlled role swap, not a manual process.

Practice this question →

228

MCQmedium

A company has an Azure SQL Database that supports a critical business application in the West US region. They want to ensure that if the primary region becomes unavailable, the database can automatically fail over to a secondary replica in the East US region with minimal data loss. The secondary replica must also be readable to offload some reporting queries when the primary is healthy. Which Azure SQL Database feature should they enable?

A.Active Geo-Replication

B.Long-term retention backups

C.Transparent Data Encryption (TDE)

D.Point-in-time restore

AnswerA

Correct. Active Geo-Replication maintains a readable secondary replica in another region and enables automatic failover for disaster recovery with minimal data loss.

Why this answer

Active Geo-Replication creates a readable secondary replica in a different Azure region (East US) that stays synchronized asynchronously with the primary database in West US. In the event of a regional outage, the secondary can be manually or automatically promoted to become the new primary, with minimal data loss (typically a few seconds of transactions). The secondary replica is also readable, allowing reporting queries to be offloaded from the primary while it is healthy.

Exam trap

The trap here is that candidates often confuse Active Geo-Replication with failover groups, but failover groups provide automatic failover and a single endpoint, whereas Active Geo-Replication requires manual or scripted failover and separate connection strings for the secondary.

How to eliminate wrong answers

Option B (Long-term retention backups) is wrong because it provides archival backups for compliance or recovery beyond the automated backup retention period, not real-time replication or automatic failover. Option C (Transparent Data Encryption) is wrong because it encrypts data at rest and in transit but does not provide any replication, failover, or read-scale capability. Option D (Point-in-time restore) is wrong because it allows restoring a database to a specific past time from automated backups, but it does not maintain a continuously synchronized secondary replica or support automatic failover.

Practice this question →

229

MCQeasy

You are creating an Azure SQL Database and need to connect using Microsoft Entra ID authentication. Which user type must you create in the database to represent the authenticated Microsoft Entra ID identity?

A.SQL login with password

B.Contained database user mapped to a Microsoft Entra ID identity

C.External user from Microsoft Entra ID

D.Database user without login

AnswerB

Enables Microsoft Entra ID authentication.

Why this answer

Option C is correct because a contained database user mapped to a Microsoft Entra ID identity is required. Option A is wrong because SQL authentication uses a SQL login. Option B is wrong because a database user without login is for SQL authentication.

Option D is wrong because an external user is not a valid concept.

Practice this question →

230

MCQhard

A company uses Azure Synapse Analytics dedicated SQL pool to store a large fact table with billions of rows. The table is distributed using hash distribution on the SaleDate column. Queries that join this fact table with a small dimension table (Product) on ProductID are slow because the join requires shuffling data across distributions. Which design change would most improve the performance of these join queries?

A.Change the distribution of the fact table to round-robin.

B.Replicate the Product dimension table to all distributions.

C.Partition the fact table by SaleDate.

D.Create a nonclustered index on ProductID in the fact table.

AnswerB

Correct. Replicating small dimension tables ensures that each distribution has a local copy of the entire table, avoiding data movement during joins.

Why this answer

Replicating the Product dimension table to all distributions eliminates the need to shuffle data across distributions during the join. In Azure Synapse dedicated SQL pool, hash distribution distributes rows across 60 distributions based on the hash of the distribution column (SaleDate). When joining on ProductID, which is not the distribution column, data must be moved between distributions.

Replicating the small dimension table ensures each distribution has a local copy, allowing the join to be performed without data movement, significantly improving performance.

Exam trap

The trap here is that candidates often confuse partitioning with distribution, thinking partitioning on SaleDate will help the join on ProductID, but partitioning only segments data within a distribution and does not reduce cross-distribution data movement for joins on a different column.

How to eliminate wrong answers

Option A is wrong because changing the distribution to round-robin would distribute data evenly but without any hash alignment, causing even more data movement for all joins, not just this one. Option C is wrong because partitioning by SaleDate organizes data within each distribution but does not reduce data shuffling across distributions for joins on ProductID; partitioning is primarily for partition elimination and maintenance operations. Option D is wrong because a nonclustered index on ProductID within each distribution can speed up local lookups but does not address the cross-distribution data movement required when the join key does not match the distribution key.

Practice this question →

231

MCQmedium

A company plans to migrate an on-premises SQL Server database to Azure. The database uses SQL Server Agent jobs for scheduled maintenance and relies on linked servers to query data from another SQL Server instance. It also performs cross-database queries within the same instance. The company wants a fully managed PaaS service that requires minimal application changes and provides automated backups and patching. Which Azure SQL service should they choose?

A.A. Azure SQL Database (single database)

B.B. Azure SQL Managed Instance

C.C. SQL Server on Azure Virtual Machines

D.D. Azure SQL Database elastic pool

AnswerB

Azure SQL Managed Instance provides high compatibility with on-premises SQL Server, including Agent jobs, linked servers, and cross-database queries, while being fully managed.

Why this answer

Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs, linked servers, and cross-database queries within the same instance. It is a fully managed PaaS service that offers automated backups and patching, minimizing application changes during migration.

Exam trap

The trap here is that candidates often confuse Azure SQL Database's limited feature set with the full SQL Server engine compatibility of Azure SQL Managed Instance, assuming all PaaS offerings support agent jobs and linked servers when only Managed Instance does.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database (single database) does not support SQL Server Agent jobs, linked servers, or cross-database queries within the same instance; it is designed for modern cloud-native applications and requires significant application changes. Option C is wrong because SQL Server on Azure Virtual Machines is an IaaS solution, not a fully managed PaaS service; it requires manual patching and backup management, contradicting the requirement for automated operations. Option D is wrong because Azure SQL Database elastic pool is a resource-sharing model for multiple single databases and inherits the same limitations as Azure SQL Database, lacking support for SQL Server Agent jobs, linked servers, and cross-database queries.

Practice this question →

232

MCQeasy

A company needs to create a relational database in Azure that is compatible with existing SQL Server applications and provides built-in high availability without requiring configuration. Which service should they choose?

A.SQL Server on Azure Virtual Machines

B.Azure Database for MariaDB

C.Azure Cosmos DB

D.Azure SQL Database

AnswerD

PaaS with built-in high availability and SQL Server compatibility.

Why this answer

Azure SQL Database is a fully managed Platform-as-a-Service (PaaS) relational database that is built on the latest stable version of the Microsoft SQL Server engine, ensuring compatibility with existing SQL Server applications. It provides built-in high availability with a 99.99% SLA through automatic failover groups and zone-redundant configurations, requiring no manual setup or configuration from the user.

Exam trap

The trap here is that candidates often confuse IaaS (SQL Server on VMs) with PaaS (Azure SQL Database) and assume both require manual HA setup, or they mistakenly think MariaDB or Cosmos DB can be used as drop-in replacements for SQL Server applications.

How to eliminate wrong answers

Option A is wrong because SQL Server on Azure Virtual Machines is an Infrastructure-as-a-Service (IaaS) offering that requires manual configuration of SQL Server Always On Availability Groups or failover clustering to achieve high availability, not built-in. Option B is wrong because Azure Database for MariaDB is a fork of MySQL and is not compatible with SQL Server applications, which rely on T-SQL and SQL Server-specific features. Option C is wrong because Azure Cosmos DB is a NoSQL multi-model database service that does not support the relational model or T-SQL, making it incompatible with existing SQL Server applications.

Practice this question →

233

MCQeasy

A startup wants to build a new web application with a relational database. They expect variable traffic and want to minimize costs by paying only for the compute resources they use. Which Azure SQL Database deployment option should they choose?

A.Provisioned compute tier

B.Elastic pool

C.SQL Server on Azure Virtual Machines

D.Serverless compute tier

AnswerD

Serverless automatically pauses and scales, billing per second of use.

Why this answer

Option C is correct because Azure SQL Database serverless automatically pauses during inactivity and bills per second for compute. Option A is wrong because provisioned computing charges for allocated resources regardless of usage. Option B is wrong because elastic pools are for multiple databases with pooled resources, not serverless.

Option D is wrong because SQL on VMs is IaaS, costs for VMs always on.

Practice this question →

234

MCQmedium

A university's enrollment system stores data in a single table with columns: EnrollmentID, StudentID, StudentName, CourseID, CourseName, and Grade. Students can take multiple courses, and each course has multiple students. The team notices data redundancy: StudentName is repeated for each enrollment of the same student, and CourseName is repeated for each enrollment in the same course. They want to reduce redundancy while preserving the ability to query all enrollments with student and course details. What is the most appropriate design approach?

A.Keep the single table but use compression to reduce storage

B.Create a view that mirrors the single table but physically store data in separate normalized tables

C.Normalize the schema by creating separate Students, Courses, and Enrollments tables with foreign keys

D.Denormalize by adding more columns to the single table

AnswerC

Normalization partitions data into entities (Students, Courses) and the relationship (Enrollments). This removes duplication of StudentName and CourseName and enforces referential integrity.

Why this answer

Option C is correct because normalizing the schema into separate Students, Courses, and Enrollments tables eliminates data redundancy by storing each student's name and each course's name only once, while using foreign keys to maintain relationships. This preserves the ability to query all enrollments with student and course details via JOIN operations, which is the standard relational database design principle for reducing anomalies and storage overhead.

Exam trap

The trap here is that candidates confuse views with physical schema changes, thinking a view can magically eliminate redundancy without altering table structure, or they mistakenly believe compression is a substitute for proper normalization.

How to eliminate wrong answers

Option A is wrong because compression reduces storage size but does not eliminate logical data redundancy; repeated StudentName and CourseName values remain, leading to update anomalies and inconsistency risks. Option B is wrong because a view is a virtual table that does not physically store data; creating a view over a single table does not reduce redundancy, and physically storing data in separate normalized tables would require changing the underlying schema, not just adding a view. Option D is wrong because denormalization adds more columns, which increases redundancy and storage waste, contradicting the goal of reducing redundancy.

Practice this question →

235

MCQeasy

Refer to the exhibit. A database administrator runs the KQL query shown to monitor failed logouts? Actually, the query filters for 'DATABASE_LOGOUT' events. What is the primary purpose of this query?

A.Identify users with the most failed login attempts

B.Determine which users are currently connected to the database

C.Identify users with the highest number of logout events per hour

D.Monitor the total number of database connections over time

AnswerC

The query summarizes logout events by user and hour, then shows top 10.

Why this answer

Option D is correct because the query counts logout events per user per hour. Option A is wrong because it counts logouts, not failed logins. Option B is wrong because it counts events, not active logins.

Option C is wrong because it's not about total connections but logout events.

Practice this question →

236

Multi-Selectmedium

Which TWO of the following are valid relational database services in Azure?

Select 2 answers

A.Azure Data Lake Storage

B.Azure SQL Database

C.Azure Cosmos DB

D.Azure Cache for Redis

E.Azure Database for MySQL

AnswersB, E

Relational database service for SQL Server workloads.

Why this answer

Options A and D are correct. Azure SQL Database is a relational database as a service. Azure Database for MySQL is a relational database service.

Option B is wrong because Azure Cosmos DB is a NoSQL database. Option C is wrong because Azure Data Lake Storage is a storage service. Option E is wrong because Azure Cache for Redis is an in-memory cache.

Practice this question →

237

MCQhard

An e-commerce application uses Azure SQL Database. During flash sales, the database experiences high CPU usage and query timeouts. The team needs a solution that can handle sudden increases in demand without downtime. Which scaling option should they choose?

A.Read scale-out

B.Hyperscale service tier

C.Elastic Pool

D.Geo-replication

AnswerB

Correct. Hyperscale can scale compute resources up to 100 vCores within seconds without downtime, ideal for handling sudden demand spikes.

Why this answer

The Hyperscale service tier is designed for high-performance, rapidly growing workloads that require instant scalability. It separates compute from storage, allowing compute nodes to be added or scaled up in seconds without downtime, making it ideal for handling sudden spikes in demand like flash sales.

Exam trap

The trap here is confusing 'scaling for demand spikes' with 'scaling for read-heavy workloads' or 'managing multiple databases,' leading candidates to incorrectly choose Read scale-out or Elastic Pool instead of the compute-scalable Hyperscale tier.

How to eliminate wrong answers

Option A is wrong because Read scale-out is a feature for offloading read-only queries to a replica, not for handling high CPU usage or write-heavy transactional spikes. Option C is wrong because Elastic Pools are designed for managing multiple databases with varying, predictable usage patterns, not for a single database experiencing sudden, extreme spikes. Option D is wrong because Geo-replication provides disaster recovery and read-scale capabilities, but does not directly address compute scaling or CPU bottlenecks during a demand surge.

Practice this question →

238

MCQmedium

An e-commerce company uses Azure SQL Database for its product catalog. During promotional events, the database experiences unpredictable spikes in traffic. The company wants a solution that automatically adjusts compute resources based on demand without manual intervention. Which Azure SQL Database option should they use?

A.A) Read replicas

B.B) Active geo-replication

C.C) Serverless compute tier

D.D) Elastic pool

AnswerC

The serverless tier auto-scales compute and bills per second, perfect for unpredictable workloads.

Why this answer

The Serverless compute tier for Azure SQL Database automatically scales compute resources based on workload demand and pauses the database during idle periods, charging only for storage and used compute. This matches the requirement for handling unpredictable traffic spikes without manual intervention, as it provides instant scaling and cost efficiency for intermittent workloads.

Exam trap

The trap here is that candidates confuse the Serverless compute tier with elastic pools, assuming both handle scaling, but elastic pools scale resources across multiple databases, not automatically for a single database's unpredictable spikes.

How to eliminate wrong answers

Option A is wrong because read replicas are designed to offload read-only queries for reporting or analytics, not to automatically scale compute resources for write-heavy or unpredictable transactional spikes. Option B is wrong because active geo-replication provides disaster recovery and read-scale capabilities by maintaining synchronized replicas in different regions, but it does not dynamically adjust compute resources based on demand. Option D is wrong because elastic pools are used to share resources among multiple databases with predictable, aggregated usage patterns, not to automatically scale a single database's compute in response to unpredictable spikes.

Practice this question →

239

MCQmedium

You are the database administrator for a large financial institution migrating their core banking system to Azure. The system uses SQL Server with many stored procedures, triggers, and CLR assemblies. The database is 2 TB and growing. The migration must minimize application changes and support high availability with automatic failover. You need to select an Azure relational database service. What should you choose?

A.Azure SQL Database

B.Azure Database for PostgreSQL

C.SQL Server on Azure Virtual Machines

D.Azure SQL Managed Instance

AnswerD

Offers high compatibility, supports CLR, and provides built-in high availability.

Why this answer

Option C is correct because Azure SQL Managed Instance provides near 100% compatibility with SQL Server, supports CLR, and offers high availability with automatic failover. Option A (Azure SQL Database) lacks support for CLR and cross-database queries. Option B (SQL Server on Azure VM) requires manual high availability setup.

Option D (Azure Database for PostgreSQL) requires significant application changes.

Practice this question →