CCNA Identify Considerations For Relational Data On Azure Questions

Azure SQL Database is a PaaS service that handles patching, supports elastic query for cross-database queries, and allows collation settings on a per-database level. It does not include SQL Agent or linked servers, which are not required here.

Azure SQL Database auditing tracks database events and writes them to an audit log in Azure Storage, Azure Monitor, or Log Analytics, capturing actions like INSERT, UPDATE, and DELETE along with user, time, and affected data. This feature retains the logs for a configurable period, which can be years, enabling the company to prove and review exactly what data was modified and by whom. It directly meets the need to audit all data modifications.

Normalization is the formal process of decomposing large tables into smaller, related tables based on functional dependencies, with the goal of eliminating duplicate data and ensuring each fact is stored only once. By applying normal forms such as 1NF, 2NF, and 3NF, the designer removes update anomalies and ensures consistency, directly satisfying the requirement to reduce data redundancy.

In-Memory OLTP creates memory-optimized tables and can use natively compiled stored procedures, which avoid the latch and lock overhead typical of disk-based tables by using optimistic concurrency and row-versioning. For a high-volume order processing system, this directly attacks the primary bottlenecks—latch contention and blocking on hot b-tree pages—so more transactions can commit concurrently with fewer retries and lower wait times. This makes it the appropriate choice from the listed options for improving write performance under heavy concurrent transaction load.

Partitioning the table by month and creating aligned indexes is correct because it enables partition elimination for queries that filter on months, directly improving performance for monthly reporting. Aligned indexes—where every index is partitioned on the same partition column—allow partition switching to be fast and atomic: you can move an entire month of historical data to an archive table in seconds without touching the rest of the table. This both simplifies archiving and keeps indexes consistent, solving both stated requirements.

A dedicated database per tenant, often called the silo model, provides the strongest possible isolation because each tenant's data resides in its own independent database with its own security credentials, backup schedule, and performance tier. This eliminates the risk of cross-tenant data leakage from application errors or misconfigured queries, and it allows you to restore, scale, or upgrade a single tenant without affecting any other tenant. It also simplifies compliance and audit because data is physically separated at the server level, making it the clear choice for strict multi-tenant isolation requirements.

Azure SQL Database serverless provides automatic compute scaling that responds to workload demand, and it can pause the database entirely during idle periods. Billing is per-second based on actual compute usage, not provisioned capacity, making it cost-efficient for intermittent or unpredictable application traffic. The startup's web application with variable load fits this model perfectly.

Elastic pools allow multiple databases to share a pool of resources, providing cost savings for multi-tenant SaaS applications while maintaining predictable performance per database.

Creating a read-only replica and directing the weekly aggregation to it is the correct approach because it physically separates the heavy read workload from the primary write workload. Azure SQL Database read scale-out (Premium and Business Critical) or a geo-replicated secondary can serve read-only connections with ApplicationIntent=ReadOnly, allowing the aggregation to run on a separate compute and storage footprint. This offloading ensures the primary remains dedicated to production OLTP transactions, minimizing contention and preserving performance.

Azure SQL Database serverless automatically scales the compute capacity between a configured minimum and maximum number of vCores based on actual workload demand, and it pauses the database when idle to eliminate compute billing. This makes it ideal for intermittent or unpredictable workloads, as it requires no manual intervention and computes billing per second. Note that serverless does not auto-scale storage; storage is billed separately and remains available even while paused.

Active Geo-Replication asynchronously replicates committed transactions from the primary Azure SQL database to a readable secondary in a different region, keeping the copy continuously synchronized with a lag usually measured in seconds. This provides a warm, readable target that can serve reporting workloads without impacting the primary, and because Azure manages the replication process, administrative overhead remains minimal. It also supports manual failover for disaster recovery, but for a persistent reporting secondary it is the simplest and most direct fit.

Azure Policy can enforce data residency by applying a policy definition to deny or audit the creation of Azure SQL Database resources outside an approved region. You can target the resource types Microsoft.Sql/servers and Microsoft.Sql/servers/databases, and include parameters for allowed locations so both server and database must be in the required geography. The Deny effect blocks any non-compliant deployment before it happens, while Audit logs violations, making this a governance and compliance control rather than a data-protection feature.

Partitioning enables partition elimination for queries filtering on TransactionDate, reducing scan size. Old partitions can be switched out for easy archiving without impacting the live table.

Active geo-replication is the correct choice because it creates readable secondary replicas of an Azure SQL database in different regions, allowing applications to direct read-only queries to the nearest secondary for low-latency access. These secondaries are maintained through asynchronous replication, so they are near-real-time but not guaranteed to be transaction-consistent with the primary. This directly addresses the need for global read performance while keeping the primary as the sole write endpoint, which is simpler and more predictable than multi-master.

Atomicity guarantees that a transaction is treated as a single, indivisible unit: either all its operations (e.g., debit and credit in a fund transfer) execute successfully and commit, or none take effect. If any step fails, the database management system rolls back all completed steps to the original state, using undo logs or shadow paging. This all-or-nothing property prevents partial updates, making it the correct ACID property for a multi-step transfer.

Enabling the serverless compute tier for Azure SQL Database is the correct choice because it automatically scales compute capacity (measured in vCores) based on actual workload demand, scaling up or down within a configured range. It even pauses the database automatically during periods of inactivity, so you are billed only for storage, and resumes quickly when a request arrives. This fits an intermittent order-insert workload without manual intervention.

Active geo-replication creates a readable secondary database in a different Azure region. It allows failover and offloads read-heavy workloads to the secondary. The secondary is readable and can be used for reporting.

A composite nonclustered index on (UserID, LastActivityTime) is precisely tailored for queries that filter on those two columns, often with UserID as an equality predicate and LastActivityTime as a range predicate. The index's B-tree structure lets SQL Server perform an index seek directly to the relevant rows, significantly reducing logical I/O compared to a full table scan. Because UserID is the leading column, it supports point lookups, while LastActivityTime handles ordering or upper/lower bound filters, making it the optimal, low-cost choice for these access patterns.

Azure SQL Managed Instance is the correct choice because it offers near-total parity with on-premises SQL Server, preserving critical features like SQL Server Agent and three-part cross-database queries, which are essential for a seamless lift-and-shift of a 3 TB transactional database. Its instance-level scope allows multiple databases to reside on the same logical server, enabling in-database queries across those databases without application rewrites. Being fully managed, it handles patching, backups, and high availability, making it the ideal target for this migration.

Synapse Link provides real-time replication without ETL.

This composite index is optimal because the leading key column CustomerID enables a precise equality seek to exactly the rows for the specified customer. The OrderDate DESC key column then provides both an efficient range scan for the date condition and returns rows already in the required ORDER BY OrderDate DESC order, eliminating a sort operator. Adding TotalAmount as an included column makes the index fully covering: all columns referenced in the SELECT, WHERE, and ORDER BY are present in the index, so SQL Server can satisfy the query entirely from the nonclustered index without costly key lookups into the clustered index or heap.

Azure SQL Managed Instance is the recommended destination for a 2 TB on-premises SQL Server database because it provides near-100% T-SQL surface compatibility, including SQL Server Agent, linked servers, Service Broker, and cross-database queries. Unlike Azure SQL Database, it supports instance-scoped features without requiring application changes, and it also handles backups, patching, and high availability automatically as a fully managed PaaS service.

A non-clustered index with leading key CustomerID and second key OrderDate directly supports the query's equality filter on CustomerID and range filter on OrderDate. The optimizer can perform an index seek on CustomerID and then a range seek on OrderDate within that customer's rows, avoiding a full table scan and minimizing page reads. If the SELECTed columns are included or the query is otherwise covered, this index can be particularly efficient, but even as a key-only index it reduces lookups compared to single-column alternatives.

Adding appropriate indexes, such as covering or composite indexes, lets queries locate and modify only the necessary rows without scanning large tables. This shortens the time that locks are held and reduces the number of locks acquired, lowering the chance of lock escalation and blocking between concurrent inventory updates. Proper index design is a direct, proactive way to reduce lock duration and is the best-practice response to update conflicts and transaction contention.

Azure SQL Managed Instance is the correct choice because it provides near-complete SQL Server engine compatibility, including instance-scoped features such as SQL Server Agent, linked servers, CLR, Service Broker, and distributed transactions, while being a fully managed PaaS offering. It offloads patching, backups, and high availability, allowing a lift-and-shift migration of a 1.5 TB on-premises SQL Server database with minimal application changes.

A nonclustered index on (CustomerId, OrderDate) is the optimal choice because it lets the query optimizer seek directly to the index entry for a specific CustomerId and then perform a range scan on OrderDate within that customer's contiguous block of index rows. Since both columns are key columns, the index can also act as a covering index for the query if only these columns are needed, avoiding costly lookups to the clustered index. This arrangement is ideal for equality on the leading column and inequality/range on the second, which is exactly the pattern of the slow query.

A nonclustered columnstore index on a rowstore table allows efficient OLTP inserts into the rowstore while the columnstore index periodically processes batches for analytical performance, achieving a balanced hybrid workload.

The value 268,435,456,000 bytes divides exactly by 1,073,741,824 (the number of bytes in one GiB) to give 250, meaning the exhibit represents 250 GiB. In an ARM template, the disk size property is specified as an integer number of GiB, so the correct entry is 250. This binary interpretation matches Azure's capacity behavior, where the displayed 'GB' actually refers to GiB for managed disks.

A composite nonclustered index on (OrderDate, OrderStatus) allows the query to filter on both columns efficiently and provides the data already sorted by OrderDate (the leading key). Including other columns avoids key lookups, making the query even faster.

Azure SQL Managed Instance offers the highest compatibility with on-premises SQL Server, supporting SQL Agent, cross-database queries within the instance, and Query Store. It provides up to 16 TB of storage, meeting the size requirements.

A composite nonclustered index with Author as the leading key column lets Azure SQL Database seek directly to the rows for the specified author. Adding PublishedYear DESC as the second key column means rows are already stored in the required sort order, eliminating a sort operator. Including Title and CopiesAvailable makes the index covering, so the query engine returns the result using only index pages, avoiding costly bookmark lookups into the clustered index. This design addresses the filter, ordering, and projection in one pass.

SQL Server Agent job scheduling and CLR (common language runtime) stored procedures are part of the broader SQL Server engine surface area that is supported in Azure SQL Managed Instance but unavailable in Azure SQL Database single databases. If your workloads rely on T-SQL agent jobs that run nightly maintenance or managed code that executes inside the database, those features simply cannot be used with a single database. That compatibility gap is a concrete, technical reason to pick Managed Instance.

Failover groups with automatic failover and readable secondary disabled meet the requirements.

Elastic pools in Azure SQL Database allocate a shared set of eDTUs or vCores across multiple databases, so the 12 ~30 GB databases can collectively use resources without each being provisioned for peak demand. Because workload spikes are typically unsynchronized, the pool absorbs them with a small total compute footprint, and per-database min/max DTU or vCore settings let you control resource sharing. This shared billing makes it the most cost-effective and administratively simple choice for many similarly sized databases.

An elastic pool allocates a shared set of DTUs or vCores across a group of Azure SQL databases, automatically distributing capacity based on aggregate demand. For a multi-tenant SaaS app with many low-average, bursty databases, pooling smooths out peaks and reduces cost by paying for a pooled resource budget instead of provisioning each database to its maximum need. Per-database min and max settings let you guard individual tenants from consuming the entire pool, making it the ideal choice for variable usage.

Azure Synapse Analytics dedicated SQL pool is a purpose-built, massively parallel processing (MPP) data warehouse service that distributes each table across 60 compute distributions and uses clustered columnstore indexes to scan and aggregate large relational datasets efficiently. Unlike Azure SQL Database, it separates compute and storage and uses a control node to create and parallelize a distributed execution plan across compute nodes, making it ideal for complex analytical queries that would overwhelm an OLTP database. It is the correct choice when an organization needs to consolidate data from a source like Azure SQL Database into a scalable warehouse optimized for reporting and analytics.

Azure SQL Database's read scale-out uses a built-in read-only replica in the same region as the primary; when clients set ApplicationIntent=ReadOnly, read-only queries are routed to that replica. This offloads reporting/BI/analytics queries from the primary replica, preventing them from competing for CPU, I/O, and memory with the e-commerce transaction workload. The replica is continuously updated and is intended for exactly this same-region read offload scenario.

Elastic pools place multiple tenant databases into a single shared resource pool with per-database minimum and maximum DTU settings, so the pool's aggregate capacity absorbs each tenant's spikes while idle databases contribute unused capacity back to the pool. This matches the SaaS pattern of many small databases with low average usage: you pay for the pooled DTUs actually needed rather than a fixed amount per tenant, and the built-in performance isolation prevents one tenant from starving others.

Always Encrypted is correct because it encrypts sensitive data client-side, meaning the plaintext values are never transmitted to or stored in the SQL database. The database engine and DBAs only see ciphertext; the column master key that would permit decryption is held outside the database, typically in Azure Key Vault or the client's key store. Without that key, even a DBA with full server privileges cannot read the original data, which directly satisfies the requirement.

Fully managed SQL Server database engine.

Azure SQL Database auditing can be configured to write audit logs directly to Azure Blob Storage, which supports configurable retention policies through the 'Retention days' setting. This destination is ideal for long-term compliance because blob storage is cost-effective, and the .xel audit log files can be queried with tools like sys.fn_get_audit_file when needed. A five-year retention policy is fully supported, making this the correct choice for the stated requirement.

A clustered columnstore index is the optimal choice for an analytical warehouse table because it physically stores each column separately, allowing the query engine to read only the columns needed for aggregations (e.g., ProductID, Month, SalesAmount). This columnar layout enables higher compression ratios (often 5-10x) and uses vectorized batch mode execution, which accelerates scans and SUM/COUNT/GROUP BY operations dramatically compared to row-based storage. The fact that it is clustered means the entire table is organized as a columnstore, eliminating rowstore lookups and making full-scan analytical queries extremely efficient.

Dynamic data masking (DDM) is a column-level security feature that applies masking rules (e.g., partial, default, random, email) to specified sensitive columns. For non-privileged users lacking the UNMASK permission, the database engine transforms the query result set to show obfuscated values, while privileged users with UNMASK see the original data. Because DDM operates at query time without altering stored data, it directly addresses the need to hide sensitive information from specific users and is the correct answer.

TDE encrypts the underlying database file, transaction log, and backups so data at rest cannot be read if the physical media is stolen, but it does not protect data from users who have valid access to the database. Always Encrypted elevates protection by keeping encryption keys entirely on the client side, so for selected columns SQL Server only stores and processes ciphertext; only the authorized client application can decrypt the values, and even high-privilege database admins cannot see the plaintext. Together, TDE secures the entire database at rest while Always Encrypted provides column-level confidentiality and controlled decryption by the application, making this the correct pair for end-to-end encryption of sensitive customer data.

Serverless is the correct choice because it decouples compute billing from database uptime: you are billed per vCore-second only while actively processing queries, and the database auto-pauses after a configurable idle period (default 60 minutes). This means a workload with unpredictable spikes and long quiet stretches only pays for compute during those spikes, while storage (separately billed) remains intact. The resume-from-pause typically takes tens of seconds, making it practical for sporadic app usage.

Azure SQL Managed Instance offers high compatibility with on-premises SQL Server, including SQL Server Agent, cross-database queries, and linked servers. It is fully managed and requires minimal changes to existing applications, making it the best fit for this migration.

A PRIMARY KEY constraint enforces both uniqueness and non-nullness in a single declaration: the column cannot contain any NULL values, and every value must be unique across the table. This makes it the ideal choice for a medical record number, which must always be present and uniquely identify each patient. Additionally, a primary key automatically creates an index (typically clustered) to support fast lookups and enforce data integrity.

The vCore-based General Purpose serverless tier decouples compute and storage, letting the database independently scale compute from 1 to 16 vCores and automatically pause when the session is idle. This means compute is billed only when active, while storage remains separately billed and durable, precisely matching an intermittent new application's desire to avoid paying for unused capacity.

SQL Managed Instance supports SQL Server Agent jobs, while Azure SQL Database does not. This is a key factor when choosing between them.

This statement is true. Azure Database for PostgreSQL supports creating read replicas that can be used to offload read-only traffic from the primary instance. These replicas are updated asynchronously, and they can be created in the same region or even in a different region, providing options for read scaling, improved performance for read-heavy workloads, and regional availability.

Azure SQL Database's serverless tier automatically pauses the database after a period of inactivity and resumes when traffic returns, so you are only billed for compute when it's actually in use. It is a fully managed PaaS offering that includes automatic backups, high availability, and built-in security, while remaining compatible with SQL Server T-SQL. For a small business, this eliminates most administrative work and aligns compute cost with sporadic usage.

Azure SQL Database's built-in high availability uses multiple synchronous replicas and automatic failover at the database level, so if a node fails, connections are transparently redirected to a healthy replica. This is provisioned and managed by Microsoft without you having to configure Always On Availability Groups or deploy additional VMs. Consequently, you receive a 99.99% uptime SLA while avoiding the operational burden of designing your own HA stack.

Using a query hint such as WITH (INDEX(index_name)) or OPTION (TABLE HINT(...)) explicitly instructs the Azure SQL Database query optimizer to use that index for the specific query, effectively overriding the cost-based decision. This is the only listed technique that directly forces the index at execution time. Apply it sparingly because it overrides the optimizer and may become stale as data distributions change.

Azure SQL Database is the correct choice because it is a fully relational database engine that enforces ACID (Atomicity, Consistency, Isolation, Durability) properties for every transaction. Using T-SQL, it provides multi-statement transactions with commit/rollback semantics, row-level locking, and configurable isolation levels to guarantee strict consistency even under concurrent access. This makes it suitable for financial applications where data integrity must be preserved across related tables.

This is correct because the non-clustered index uses OrderDate as the leading column with DESC, which exactly matches the ORDER BY OrderDate DESC requirement and allows the query engine to read rows in the correct order without a sort. Including OrderStatus as a second key column lets the index efficiently handle filtering on both columns, and because OrderID is the clustered key it is automatically appended to non-clustered index entries, making the index covering for a query selecting OrderID, OrderDate, and OrderStatus. Keeping the existing clustered index on OrderID preserves the primary key's uniqueness and avoids unnecessary physical table reorganization, so this design balances performance for the query with minimal impact on other operations.

Transparent Data Encryption (TDE) in Azure SQL Database automatically encrypts database files, log files, and backups at rest without requiring changes to the application schema or queries. The database engine performs real-time I/O encryption and decryption, but plaintext data remains in memory and is visible to users with proper access. This makes TDE a strong baseline for regulatory compliance, though it protects only against physical theft of storage, not against unauthorized application queries.

A non-clustered index on OrderDate and CustomerID directly supports the WHERE clauses that filter orders by date ranges and customer lookups. The index structure allows the query engine to perform an index seek rather than a full table scan, dramatically reducing I/O. Including both columns lets the engine satisfy equality on CustomerID and range on OrderDate efficiently, and the index can even cover some queries if only those columns are needed.

Serverless is the correct compute tier for intermittent, unpredictable workloads because it automatically scales compute resources (in vCores) based on actual demand and can pause the database during idle periods, billing only for storage while paused. With a configurable auto-pause delay (1 to 60 minutes) and per-second billing during active use, it eliminates the need to manually resize compute. This makes it far more cost-effective than maintaining fixed compute capacity that would sit idle most of the time, which is exactly why it suits an e-commerce application with spikes in traffic.

Business Critical provides a local, synchronous Always On Availability Group replica set within the cluster, so every committed transaction is acknowledged on multiple replicas before commit, giving an RPO near zero (SLA of less than 5 seconds) and automatic failover typically around 30 minutes. For a 500 GB financial database, the tier's SSD-backed local storage and high IOPS also minimize latency during normal operations. This combination is exactly why it satisfies the stated RPO/RTO requirements.

Elastic Jobs is correct because it is the Azure SQL Database service purpose-built for orchestrating T-SQL scripts, index rebuilds, or schema migrations across a large set of databases. You create an Elastic Job Agent, define target groups that can include all tenant databases, and schedule jobs that run against each member in parallel. This makes it the appropriate tool for cross-database maintenance in a multi-tenant SaaS.

In Azure SQL Database Hyperscale, you can provision one or more readable secondary replicas, each with its own compute resource and a separate connection string. The primary replica continuously sends log records to these secondaries, which apply them asynchronously and serve read-only workloads such as reporting. Because reporting queries are directed to a secondary replica, they no longer acquire locks on the primary, eliminating the blocking caused by those queries. This read scale-out pattern isolates the reporting traffic from the transactional workload while keeping data nearly current, with only negligible redo lag.

Scaling up provides more CPU/memory to handle peak load.

Billed per second of compute usage.

An elastic pool is a collection of shared resources (eDTUs or vCores) on a single logical SQL Database server, with the pool's total compute and storage billed once rather than per database. Each business-unit database consumes from the shared pool only when active, so peaks in one unit don't require over-provisioning every database. For multiple independent databases with intermittent usage, this gives predictable aggregate cost and allows per-database min/max resource caps; therefore it is the correct choice here.

System-versioned temporal tables are a built-in Azure SQL Database feature that automatically stores every historical version of a row in a parallel history table, using two datetime2 period columns (ValidFrom/ValidTo) that the engine maintains on every insert, update, and delete. You can query the current fact table for live data and use T-SQL clauses like FOR SYSTEM_TIME AS OF, BETWEEN, or CONTAINED IN to see the rows exactly as they existed at a given point in time. No custom code, triggers, or jobs are required; the database automatically appends the prior versions and lets you query history directly from the base table.

Azure SQL Managed Instance is correct because it provides near-complete SQL Server instance compatibility, including SQL Server Agent for scheduled maintenance, instance-level features, and cross-database queries, all within a fully managed PaaS environment. It supports auto-failover groups to deliver automated disaster recovery without the manual configuration required by infrastructure-as-a-service deployments. This makes it the lowest-effort managed option for modernizing a legacy order management system with minimal application changes.

This composite index covers the filter and sort conditions. Including Quantity as an included column makes the index covering for this query, avoiding expensive key lookups.

Read scale-out uses the built-in readable secondary replica of Azure SQL Database, which resides in the same region as the primary and is kept transactionally consistent through asynchronous replication. By setting the connection string's ApplicationIntent parameter to ReadOnly, read-only queries are automatically routed to this secondary, offloading CPU, IO, and memory pressure from the primary without requiring schema changes or application rewrites. This directly satisfies the scenario's requirements of isolating read workloads in the same region while leaving the existing database schema untouched.

Read scale-out is the correct feature because it provisions a transparent, read-only replica on a separate compute resource within the same region as the primary database. When enabled on Premium, Business Critical, or Hyperscale service tiers, clients can use ApplicationIntent=ReadOnly in their connection string to have their queries automatically routed to that replica, offloading reporting workloads and reducing contention on the primary. This directly addresses the requirement of improving OLTP performance by separating read-only queries from the transactional workload, with low lag and minimal configuration overhead.

Read Scale-out in Azure SQL Database leverages a built-in readable secondary replica in the same region. When a connection string specifies ApplicationIntent=ReadOnly, connections are automatically routed to that replica, allowing reporting and analytical queries to execute without consuming primary CPU and I/O. For an online booking system, this directly separates read-only load from transactional write traffic while maintaining the same logical database endpoint.

Azure SQL Managed Instance is a full PaaS deployment of the SQL Server database engine that offers nearly 100% surface-area compatibility with on-premises SQL Server. It supports SQL Server Agent, linked servers, cross-database queries, and CLR, while relieving you of patching and backup management. It runs inside your Azure virtual network to support private IP addresses, making it the preferred target for lift-and-shift migrations without redesigning applications.

This index is ordered by CustomerID then OrderDate descending, allowing efficient seeks for a specific CustomerID and range scans over OrderDate in descending order. Including TotalAmount covers the SELECT clause without needing to access the base table.

Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Integration Services (SSIS) via Azure-SSIS Integration Runtime. As a Platform as a Service (PaaS) offering, it automates critical maintenance tasks such as patching, backups, and high availability, while preserving instance-scoped features like SQL Agent, linked servers, and CLR. This minimizes administrative overhead while supporting SSIS, making it the ideal target for a direct migration of a SQL Server database with integration services workloads.

This composite index covers both filter columns in the correct sort order and includes the TotalAmount column, making the query fully covered without needing to access the table. This yields the best performance for the described queries.