CCNA Identify considerations for relational data on Azure Questions

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 provide a T-SQL-based scheduler that runs across Azure SQL Database, closely mirroring SQL Server Agent job semantics such as steps, schedules and retry logic. This satisfies the stem's constraint of minimising code changes, since existing Agent job scripts transfer with minimal rewriting, unlike Logic Apps or Automation runbooks.

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.

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.

Azure Database Migration Service performs online migrations, continuously replicating ongoing transaction log changes from the source SQL Server to Azure SQL Managed Instance until you cut over. This satisfies the minimal-downtime constraint, since the database stays available during migration rather than requiring an offline backup-and-restore window.

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.

Online migration mode keeps the source SQL Server operational and continuously replicates changes to Azure SQL Database until cutover, so the 500 GB database syncs with only a brief final switchover, satisfying the minimal-downtime requirement.

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.

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.

Azure SQL Database is a fully managed Platform-as-a-Service relational database service that hosts the SQL Server engine in the cloud. It supports core relational features such as tables, primary and foreign keys, joins, and ACID-compliant transactions, and you interact with it using T-SQL. As a true relational database service, it is a valid answer for this question.

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.

Azure SQL Managed Instance provides near-full SQL Server surface area, including stored procedures, triggers and CLR assemblies, so application changes are minimal. It also supports built-in high availability with automatic failover, meeting the 2 TB core banking requirement.