CCNA Identify considerations for relational data on Azure Questions

Azure SQL Database Elastic Pool is the correct choice because it lets all 20 databases share a single pool of eDTUs or vCores, with adjustable per-database minimum and maximum limits. This avoids over-provisioning each app separately and smooths out intermittent usage spikes across tenants. You pay only for the pooled resources actually allocated, not for 20 individually sized databases, which is exactly what unpredictable e-commerce workloads need.

Azure SQL Database serverless is a fully managed PaaS offering with vCore-based compute that automatically scales to match the workload and pauses during periods of inactivity. While paused, you are billed only for storage, not compute, which makes it the most cost-effective choice for a small business with light, intermittent database usage. It remains a relational database with no administrative overhead, and the auto-pause delay can be configured to suit the application's needs.

Azure SQL Managed Instance is a fully managed PaaS service that delivers near 100% SQL Server compatibility, including native support for SQL Server Agent, which the company needs for scheduled maintenance. It supports storage capacities up to 16 TB for a 2 TB migration, and its auto-failover groups enable cross-region high availability with automatic replication and failover, making it the appropriate target.

Serverless compute tier automatically scales compute resources based on actual demand, scaling down or even pausing the database during periods of inactivity to control costs. It bills per-second for compute and storage separately, making it ideal for workloads with unpredictable, intermittent spikes where manual provisioning or pre-scaling would be wasteful. This meets the requirement because it handles those spikes without intervention.

Azure SQL Managed Instance is a fully managed PaaS offering that maintains near-complete SQL Server engine compatibility, including linked servers and native cross-database queries. It also provides built-in high availability, automated backups, and automatic patching, which eliminates the operational overhead of managing virtual machines. For a company moving an existing SQL Server workload with multiple interdependent databases, this option gives the required functionality while minimizing management burden.

Business Critical provides built-in high availability and failover groups enable automatic failover with readable secondaries.

A non-clustered index on LastLoginDate creates a separate B-tree structure ordered by that column, allowing SQL Server to perform an index seek directly for the date-range predicate. For a query such as WHERE LastLoginDate BETWEEN '2023-01-01' AND '2023-12-31', the engine navigates the index to the starting date and reads only the matching index entries, then uses bookmark lookups to fetch the full customer rows. This drastically reduces I/O compared to scanning every row in the table, making it the ideal choice for this filtering pattern.

Elastic Jobs allow scheduling T-SQL jobs across databases, similar to SQL Agent.

An elastic pool lets multiple Azure SQL databases share a common pool of eDTUs (or vCores), with each database assigned a minimum and maximum DTU limit. Databases that are idle automatically release resources for those under load, so the pool's total capacity can be far less than the sum of individual peak requirements. This makes it the most cost-efficient and operationally simple choice for a large number of databases with fluctuating usage, such as in a multi-tenant SaaS scenario.

Always Encrypted is the only option here that provides true client-side encryption at the column level. The client driver encrypts data before sending it to Azure SQL Database, and the server never sees the plaintext value; decryption keys are held by the client application or Azure Key Vault, not by the database. This protects sensitive columns even from database administrators and system administrators, and it also encrypts data in transit, at rest, and during client operations. However, it introduces limitations on query operations, such as equality comparisons only for deterministic encryption.

The vCore-based purchasing model meters compute and storage separately, so you can scale database storage independently of the number of allocated vCores. For a migration, this means you can increase or decrease storage capacity without purchasing additional compute, giving granular cost control and flexibility that a bundled model cannot provide.

Performing a planned failover correctly fails back to the original primary and re-establishes geo-replication.

An Azure SQL Database elastic pool bills a single fixed price for a shared pool of eDTUs or vCores, regardless of how much each contained database consumes. This makes monthly costs predictable because you pay for the pool capacity, not per-database usage spikes.

In Azure SQL Managed Instance, table partitioning is fully supported using the same T-SQL syntax as on-premises SQL Server: you create a partition function to map rows to partitions based on boundary values, then a partition scheme to assign those partitions to filegroups. When migrating, the restored database retains its partition metadata, so existing queries, partition switches, and partition-aligned indexes continue to work without redesign. This is the only option that preserves the original partition design rather than replacing it with a different architecture.

Hyperscale tier supports databases up to 100 TB, allows fast scaling of compute resources with minimal downtime, and is optimized for read-heavy workloads with high concurrency. It is the best fit for databases that exceed 4 TB and require rapid scaling.

Active Geo-Replication lets you create up to four readable secondary replicas of an Azure SQL Database in different regions, each asynchronously updated from the primary. By placing secondaries in Europe and Asia, you can route read-only connections to the nearest replica using a connection string with ApplicationIntent=ReadOnly, dramatically lowering read latency for local users. This preserves the relational engine, supports primary-region writes, and also yields incidental disaster recovery if the primary becomes unavailable, making it the precise fit for the global read scenario.

DMS supports online migrations with minimal downtime.

Azure SQL Managed Instance is the correct target for this migration because it supports cross-database queries using three-part naming, SQL Server Agent, and linked servers, just like on-prem SQL Server. It also allows multiple databases (10, ranging from 50–200 GB) to reside in a single instance, sharing resources efficiently while preserving existing database dependencies. As a fully managed PaaS service, it offers automatic patching, backups, and high availability, making it ideal for a lift-and-shift migration with minimal application changes.

Azure SQL Database Hyperscale is the only service tier that can store a 6 TB database, with a maximum data size of 100 TB, while also decoupling compute from storage so read-only replicas can be added independently. It supports up to four readable replicas, allowing the financial services company to offload reporting and analytics queries from the primary transactional workload without degrading performance. The tier also uses snapshot-based backups, making backup and restore near-instantaneous even at large scale.

A composite index on both columns allows the database to find rows matching both filter conditions in a single index seek. This is the most efficient strategy for queries that filter on multiple columns together.

Failover groups are the correct choice because they automatically replicate databases or elastic pools to a secondary region and provide an automatic failover mechanism that requires no application code changes. The group exposes a single readable/writable listener endpoint that remains identical after failover, so clients keep connecting to the same fully qualified domain name. You can also set a graceful data-loss boundary (RPO) and configure a read-only listener for offloading reporting traffic, making it a fully managed, PaaS-native DR solution.

Correct. Online migration via DMS synchronizes changes in near real-time, allowing the source to stay online until cutover, minimizing downtime.

A nonclustered index on (DepartmentID, LastName) INCLUDE (Salary) is a covering index specifically designed for this query: it contains every column needed in the WHERE, SELECT, and ORDER BY clauses within the index leaf level. Because the index is sorted by DepartmentID first and LastName second, the SQL Server query optimizer can perform an index seek for the specific department, then navigate to the exact last names, and retrieve Salary directly from the included column. This completely avoids the need for expensive key lookups into the clustered index, making it the most efficient access path for this workload.

Azure SQL Database is a fully managed relational database service that enforces a fixed schema and guarantees ACID transactions. For e-commerce order data, this means foreign keys can maintain referential integrity between orders, customers, and line items, while row-level locking supports high concurrency without lost updates. Its built-in indexing and transaction logging make it the appropriate choice for transactional workloads where consistency is non-negotiable.

Azure SQL Database is a fully managed relational database engine built on SQL Server, offering T-SQL, enforced schemas, relationships via primary and foreign keys, and rich querying with joins. It also includes built-in graph table features (node and edge tables) that allow you to model many-to-many relationships, and JSON query support for semi-structured data. This makes it the most appropriate service when you need a relational store that can also handle graph-style relationships and flexible data formats.

Azure SQL Managed Instance is the correct choice because it provides near-100% compatibility with on-premises SQL Server, including SQL Server Agent, linked servers, and other instance-scoped features, all in a fully managed platform. It supports lift-and-shift migrations without rearchitecting applications, and its built-in high availability and patching make it ideal for production workloads that rely on these advanced capabilities.

Correct. This index matches the filter columns in the correct order and includes the ORDER BY direction. Included columns cover additional columns needed, making it a covering index for many queries.

As the table's primary storage structure, a clustered columnstore index organizes data column-wise, so an aggregation query reads only the needed column segments. This design delivers high compression and batch-mode processing, which drastically reduces I/O and CPU for full-table scans and GROUP BY operations on large fact tables. It is the default recommended indexing strategy for analytical and data warehousing workloads in Azure SQL Database.

A planned failover is the only supported way to reverse geo-replication roles without data loss. It synchronizes the secondary to the primary's latest committed transaction, then promotes the secondary to primary, and automatically re-establishes the replication link in the opposite direction. This is the correct failback procedure when the original region is healthy, because it preserves transactional consistency and avoids re-seeding.

A contained database user mapped to a Microsoft Entra ID identity is created with CREATE USER [user] FROM EXTERNAL PROVIDER. This provisions a database-level principal that is directly tied to a user or group in Microsoft Entra ID, enabling authentication with an Entra ID access token. This is the required approach for using Microsoft Entra ID authentication with Azure SQL Database.

Replication stores a full copy of the Product dimension table on every distribution in the dedicated SQL pool. When the fact table joins Product on ProductID, each distribution can perform the join locally using its own copy, eliminating all data movement and shuffle across distributions. This is the recommended approach for small-to-medium dimension tables in a star schema and directly resolves the join performance problem described.

Azure SQL Managed Instance is the correct PaaS choice because it provides near-complete compatibility with on-premises SQL Server, including SQL Server Agent jobs, linked servers, Service Broker, and cross-database queries. These instance-scoped features are preserved while Azure automatically handles patching, backups, and high availability, making it a fully managed lift-and-shift target with minimal application changes.

Azure SQL Database is a PaaS offering that is built on the SQL Server engine, so it is directly compatible with SQL Server features like T-SQL, stored procedures, and transparent data encryption. It provides built-in high availability with a 99.99% SLA, automated backups, patching, and monitoring, removing the need for manual infrastructure management. This matches the requirement for a relational database in Azure that is both managed and SQL Server-compatible.

In the serverless compute tier of Azure SQL Database, compute capacity automatically scales between a configurable minimum and maximum vCore range and can pause the database entirely after a user-defined period of inactivity. While running, billing is per second for the vCores actually consumed, and while paused no compute charges accrue, although storage and backups continue to incur cost. That makes it the best choice for startup traffic patterns, because an idle database can stop incurring compute costs and resume automatically when a request arrives.

Normalization decomposes the unnormalized enrollment table into Student, Course, and Enrollment relations, moving StudentName into Students and CourseName into Courses so each value is stored only once. The Enrollments table then holds only foreign keys (StudentID, CourseID) plus enrollment-specific attributes, eliminating the partial dependencies on composite keys and the transitive dependency of CourseName on CourseID. This design enforces referential integrity via foreign key constraints, preventing orphaned records and reducing update anomalies to a single-row change.

The Hyperscale service tier is built on a distributed architecture with separate compute nodes and page servers, allowing compute to be scaled up to 100 vCores in seconds with no downtime. Because e-commerce demand spikes are often sudden and unpredictable, Hyperscale lets you add compute power—and optionally additional read replicas—dynamically without re-provisioning storage, making it the only listed option that directly and rapidly increases write-transaction capacity.

The serverless compute tier for Azure SQL Database is engineered precisely for unpredictable workloads: it automatically scales the compute resources between a configured minimum and maximum vCores based on actual demand, and it can pause the database entirely during prolonged inactivity while continuing to store data. This tier bills per-second for the compute actually used, so a sudden surge in write or read activity triggers immediate scale-up without manual intervention, then scales back down when demand subsides. This is the only listed option that directly provides automatic, demand-driven compute scaling for the primary workload.