Identify considerations for relational data on Azure practice questions

Azure SQL Database

Why wrong: Azure SQL Database is a fully managed relational database built on the SQL Server engine, not PostgreSQL. Although it offers similar managed capabilities, it speaks the T-SQL dialect and uses a different query processor, so PostgreSQL-specific SQL, extensions, and client drivers would not work without a full migration. The requirement is for PostgreSQL compatibility, so this option is incorrect.

Azure Database for MySQL

Why wrong: Azure Database for MySQL is a fully managed OSS relational database, but it uses the MySQL engine and wire protocol, not PostgreSQL's. It cannot run PostgreSQL-specific objects such as JSONB columns, PL/pgSQL stored procedures, or PostgreSQL extensions like PostGIS. Because the application explicitly requires a PostgreSQL database, this option falls short despite its managed SLA and backup features.

Azure Cosmos DB for PostgreSQL

Why wrong: Azure Cosmos DB for PostgreSQL is a distributed, horizontally scalable PostgreSQL-compatible database. However, it is designed for large-scale workloads and may not offer the simple fully managed experience with 99.99% SLA that the standard Azure Database for PostgreSQL flexible server provides. The question specifies automatic backups, patching, and high availability with 99.99% SLA, which aligns with Azure Database for PostgreSQL.

Elastic Pool

Why wrong: An elastic pool allows multiple databases to share resources but does not create a separate read-only replica; it does not isolate read queries from a single database.

Geo-replication

Why wrong: Geo-replication creates a secondary in another region for disaster recovery. While the secondary is readable, it is not designed to offload read queries with low latency, and it requires redirecting queries manually.

SQL Server Agent

Why wrong: SQL Server Agent is used for scheduling maintenance jobs, not for read workload isolation.

Long-term retention backups

Why wrong: Long-term retention backups are for archival backup retention, not for real-time replication or automatic failover.

Transparent Data Encryption (TDE)

Why wrong: Transparent Data Encryption (TDE) protects data at rest but does not provide geo-replication or failover capabilities.

Point-in-time restore

Why wrong: Point-in-time restore allows recovery from backups within a retention period but does not involve a secondary replica or automatic failover.

Transparent Data Encryption (TDE)

Why wrong: Transparent Data Encryption (TDE) encrypts the database files, backups, and transaction logs at rest using an AES-256 encryption key, protecting data against theft of physical media or unauthorized file access. While TDE is essential for security and compliance (e.g., GDPR, HIPAA), it does nothing to keep the database available during a regional outage or to replicate data to another location. TDE operates entirely on the storage layer; it does not affect the database's availability model, replication topology, or failover behavior. Therefore, enabling TDE would not satisfy the requirement for high availability or automatic regional failover for this critical OLTP application.

Long-term backup retention (LTR)

Why wrong: Long-term backup retention (LTR) allows you to retain full database backups for up to 10 years in Azure Blob Storage, which is useful for regulatory compliance, auditing, or point-in-time recovery to an arbitrary date beyond the 35-day default retention. These backups are only available as a recovery source; they are not a live, readable secondary replica and cannot be used for automatic failover. To restore from an LTR backup, you must manually initiate a restore operation, which takes time and inevitably results in data loss up to the last backup. Thus, LTR provides disaster recovery in the sense of being able to recreate a database after a catastrophic loss, but it does not meet the low-RTO/low-RPO requirement of a critical OLTP application that needs automatic, seamless failover.

Auto-tuning

Why wrong: Auto-tuning in Azure SQL Database uses machine learning to continuously monitor query execution and automatically create, drop, or alter indexes and plan choices to improve performance. It also provides features like force last good plan to prevent regressions. However, auto-tuning is an intelligent performance optimization feature—it operates within a single database or managed instance and has no concept of geo-replication, secondary replicas, or failover. Even if auto-tuning improves query speed and reduces latency for the OLTP workload, it cannot protect against a regional failure or maintain business continuity, so it is entirely inadequate for the stated high availability and regional failover requirements.

SQL Server on Azure Virtual Machines

Why wrong: This is an IaaS option that requires manual configuration of high availability and read replicas. It does not offer built-in read scale-out as a managed feature.

Azure SQL Managed Instance

Why wrong: While it supports read scale-out in the Business Critical tier, it is designed for near-100% compatibility with on-premises SQL Server, often for larger databases. It incurs more administrative overhead than a single database, and the question emphasizes minimizing overhead.

Azure SQL Database Elastic Pool

Why wrong: Elastic pools are meant for managing multiple databases with fluctuating resource demands, not for scaling read workloads of a single database. They do not provide read replicas.

Azure SQL Database Single Database

Why wrong: Azure SQL Database Single Database is a PaaS offering that provides a single database with isolated, per-database resource limits, but it does not support instance-level features such as SQL Server Agent or Service Broker. To schedule jobs, you'd have to use Elastic Job Agent, and cross-database queries require external workarounds like elastic query, which adds complexity. While you can scale compute up to a certain size, the maximum vCore limits are lower than Managed Instance, and it's not designed for a single, extremely resource-intensive workload that requires full SQL Server instance compatibility.

Azure SQL Database Elastic Pool

Why wrong: Elastic Pool is a PaaS resource-sharing model where multiple databases share a fixed pool of CPU and memory, so it's intended for fleets of small databases with unpredictable usage patterns, not for a single high-resource workload. It also lacks SQL Agent and Service Broker, and each database in the pool is capped by per-database limits, making it impossible to allocate all pooled resources to one database. For a migration of a single large SQL Server instance, an elastic pool would force application changes and still fail to meet the required compute and feature set.

B) Change the clustered index to (CustomerID, OrderDate) and create a non-clustered index on OrderID

Why wrong: Changing the clustered index to (CustomerID, OrderDate) would improve the range query but degrade point lookups by OrderID because the clustered index would not be on OrderID, requiring a non-clustered index seek plus a key lookup.

C) Keep the clustered index on OrderID and create a non-clustered index on (OrderDate, CustomerID)

Why wrong: While this may help, the column order (OrderDate first) is less optimal for the typical query pattern which filters on CustomerID first, then OrderDate. The index would need to scan multiple date ranges for each customer, reducing efficiency.

D) Keep the clustered index on OrderID and create two separate non-clustered indexes on CustomerID and OrderDate

Why wrong: Creating separate non-clustered indexes on CustomerID and OrderDate means each index can independently speed up equality or range predicates on a single column, but the database must intersect the results from both indexes (via an index intersection) to satisfy the combined predicate CustomerID = @cust AND OrderDate BETWEEN @start AND @end. Even if the optimizer uses only the CustomerID index, it must perform a key lookup into the clustered index for every row that passes the customer filter to retrieve OrderDate, then an additional range filter in the lookup, causing numerous random I/Os. This approach may be statistically beneficial for workloads with independent predicates, but it is less efficient than a single covering composite index keyed on (CustomerID, OrderDate), which can evaluate both conditions in one contiguous index seek without touching the underlying table.

A. Create a clustered index on (CustomerID, OrderDate) and a non-clustered index on OrderID.

Why wrong: Incorrect. A clustered index on (CustomerID, OrderDate) would make single order lookups by OrderID slower because the clustered index determines the physical order, and OrderID lookups would require a key lookup or a non-clustered index scan.

C. Create a non-clustered index on each column used in WHERE clauses: OrderID, CustomerID, and OrderDate.

Why wrong: The problem with individual non-clustered indexes on each column is that a composite range query like 'WHERE CustomerID = ? AND OrderDate BETWEEN ? AND ?' cannot be satisfied efficiently by any single one of those indexes. The optimizer may be forced to use an index intersection—combining row pointers from two separate indexes and then joining them—or it might only use one index and apply a residual predicate, leading to extra key lookups, higher CPU, and more logical I/O. A single composite index on (CustomerID, OrderDate) provides both the equality seek and the ordered range scan in one structure, which is far more efficient than letting the engine piece together data from three narrow indexes.

D. Create a clustered columnstore index on the entire table and no other indexes.

Why wrong: A clustered columnstore index is designed for analytical workloads that scan large volumes of data and aggregate columns, not for the frequent point lookups and small-range queries typical of an order management system. In a columnstore, each column is compressed and stored separately, which makes finding a single row by OrderID require reading column segments across all columns and reassembling them, resulting in much higher per-query latency than a rowstore clustered index. Additionally, columnstore indexes are optimized for append-only or bulk operations and have higher overhead for single-row inserts and updates, so using one here would severely degrade transactional responsiveness.

Auto-failover groups

Why wrong: Auto-failover groups bundle a set of databases into a group and manage failover from a primary server to a secondary server as a single unit, using active geo-replication to maintain replicas. Although the secondary is readable, the group's purpose is to orchestrate recovery during an outage, not to serve as a general-purpose read scaling solution. The read-only listener offered by auto-failover groups is a convenient way to route read traffic to the secondary during normal operation, but the feature itself is fundamentally about high availability, making it a less direct choice for the stated need.

Elastic Database Transactions

Why wrong: Elastic Database Transactions provide ACID guarantees for distributed transactions that span multiple databases in Azure SQL Database, via a client-side pattern similar to distributed transactions coordinator. This capability coordinates commit, rollback, and locking across databases, but it does not create or maintain any replica of data for reading purposes. Using it to offload read queries would be conceptually incorrect because it neither replicates data nor exposes a separate endpoint for analytical workloads; it only ensures write consistency across a horizontal shard or application partition.

Database Copy

Why wrong: Database Copy creates a new database as a transactionally consistent snapshot of the source database at the moment the copy operation completes, using a storage-level copy that applies the transaction log to bring it to a specific point in time. After the copy finishes, the two databases are fully independent; any subsequent changes to the source are not propagated to the copy. This static nature makes it unsuitable for offloading continuously-updated read workloads, since the copied data would immediately become stale and would require repeated manual copies to remain useful.

Azure SQL Database single database

Why wrong: Azure SQL Database single database is a pure PaaS offering, but it is not a like-for-like replacement for an on-premises SQL Server 2016 instance because it lacks SQL Server Agent, denies cross-database queries except through limited elastic query features, and forces a higher compatibility level. It also does not provide native VNet integration, relying instead on private endpoints, so an application depending on instance-level features would need significant rework.

Azure SQL Database elastic pool

Why wrong: An elastic pool is merely a shared-resource container for multiple single databases, so it inherits every functional limit of the single database model: no SQL Server Agent, no native cross-database or linked-server support, and no instance-scoped metadata or CLR assemblies. Pools add resource governance and cost savings but do nothing to close the compatibility gap, meaning an app using SQL Agent jobs or cross-database queries would still require rewriting.

SQL Server on Azure Virtual Machines

Why wrong: Incorrect. SQL Server on Azure VMs provides full control and compatibility, but it is an IaaS option requiring management of the VM and SQL Server. The company wants minimal application changes and a managed service, making Managed Instance a better fit.

Auto-failover groups

Why wrong: Failover groups enable automatic failover of multiple databases, but they do not primarily focus on offloading read traffic; the secondary in a failover group is not readable unless you also enable geo-replication.

Elastic pools

Why wrong: Elastic pools allow multiple databases to share resources but do not create a separate read-only copy for offloading queries.

Query Performance Insight

Why wrong: Query Performance Insight is a monitoring tool that helps identify performance bottlenecks, but it does not offload queries to a replica.

Use memory-optimized tables for the entire table

Why wrong: Memory-optimized tables excel at high-volume OLTP insert operations by reducing locking and using in-memory storage, directly addressing the insert performance issue. However, they are not optimised for complex analytical queries that scan large datasets, lacking the data compression and batch processing capabilities that a columnstore index provides for OLAP workloads. This option would therefore degrade the analytical query performance.

Partition the table by date and move older partitions to columnstore

Why wrong: Incorrect. This still requires a columnstore on most data and does not solve the insert performance issue on the active partition.

Keep the clustered columnstore index and use batch inserts

Why wrong: Incorrect. Batch inserts help but columnstore indexes still have overhead for small, frequent operations; the degradation remains.

Azure Key Vault: Cloud-native SIEM and SOAR solution.

Why wrong: This statement is incorrect because it confuses Azure Key Vault with Azure Sentinel. Key Vault is a secret and key management service, storing certificates, passwords, and cryptographic keys with centralized access control and audit capabilities. Sentinel, on the other hand, is the cloud-native SIEM/SOAR engine that aggregates and analyzes security logs; Key Vault does not ingest security events or offer detection/response playbooks.

Azure Security Center: Protects against distributed denial-of-service attacks.

Why wrong: This is a mismatched pairing because DDoS protection is delivered by a dedicated Azure service, not by Security Center. Azure DDoS Protection operates at the network layer, continuously analyzing and scrubbing attack traffic before it reaches your applications, whereas Security Center (now Defender for Cloud) focuses on configuration hardening, threat protection, and compliance assessments. Security Center can alert to the risk of an attack and recommend enabling DDoS Protection, but it does not perform traffic mitigation itself.

Azure SQL Database with failover group enabled

Why wrong: Failover groups use asynchronous replication by default, so the RPO is typically higher (e.g., seconds to minutes), not meeting the 5-second requirement.

Azure SQL Database single instance with locally redundant storage

Why wrong: Locally redundant storage only protects against a single datacenter failure within the same region, not a regional outage.

Azure SQL Database with auto-failover group

Why wrong: Auto-failover groups are essentially failover groups and use asynchronous replication, so they cannot achieve a 5-second RPO.

Azure SQL Managed Instance

Why wrong: Managed Instance also automates patching but has more overhead than SQL Database.

SQL Server on Azure Virtual Machines

Why wrong: IaaS requires manual patching and backup management.

Azure Database for MySQL

Why wrong: MySQL is a different database engine, would require application changes.

Requirement for elastic pools

Why wrong: Both Azure SQL Database and Azure SQL Managed Instance support pooling mechanisms: SQL Database provides elastic pools to share resources across multiple databases, while SQL Managed Instance offers instance pools that share resources across multiple managed instances. Since both services deliver a form of pooling, the requirement for elastic pools does not help differentiate between them and is therefore not a valid selection criterion.

Need for automated backups

Why wrong: Automated backups are built into both Azure SQL Database and Azure SQL Managed Instance, including full, differential, and transaction log backups with configurable retention policies. Because this feature is identical in both services, the need for automated backups does not influence the choice and is not a distinguishing consideration.

The read-only endpoint will allow connections during the outage

Why wrong: The read-only endpoint is disabled, so no read-only connections.

The database will remain unavailable until manual action is taken

Why wrong: Automatic failover is enabled, so after the grace period it will failover automatically.

The failover group will immediately failover to server2 without data loss

Why wrong: Automatic failover waits for the grace period and may cause data loss.

Identify users with the most failed login attempts

Why wrong: The query's event filter explicitly selects DATABASE_LOGOUT, which is a normal session-ending event and carries no indication of authentication success or failure. Failed login attempts would appear under separate event types such as DATABASE_LOGIN_FAILED or would require a failure status column, and they would never be classified as logout events. Therefore the query cannot surface failed-login counts for any user.

Determine which users are currently connected to the database

Why wrong: A logout event is a historical record that a session has ended, so it says nothing about whether a user is still connected at query runtime. To list currently connected users, you would need to compare login events against logout events to compute open sessions, or query an active-sessions table. The given aggregation per user per hour only yields past logout frequencies, not a live roster of active database sessions.

Monitor the total number of database connections over time

Why wrong: The query restricts rows to DATABASE_LOGOUT events, so it only sees session terminations, not the complete set of connect and disconnect events needed to measure total connections. A total-connection monitor would either count each connection attempt or accumulate active sessions, whereas this query summarizes logout counts per user and hourly time bucket. Thus it cannot produce a meaningful time-series of overall database connections.

Database Mail

Why wrong: Database Mail is a SQL Server feature for sending e-mail messages from the database engine, typically for job alerts or notification workflows. It does not provide any relational data access, query processing, or ability to connect to other databases and join their tables. In Azure SQL Database, Database Mail is not even available as a service, making it entirely unsuitable for executing a T-SQL query that spans multiple databases.

PolyBase

Why wrong: PolyBase allows T-SQL queries to read and write external data stored in Hadoop, Azure Blob Storage, or Azure Data Lake using external tables and the 'polybase' pushdown computation. In Azure SQL Database, PolyBase is not supported as a feature—it is primarily available in SQL Server and Azure Synapse—but even where it exists, it is for heterogeneous data sources, not for joining relational tables across multiple Azure SQL databases in the same logical server. Since the requirement is to join tables from databases within Azure SQL, PolyBase would not be the correct solution.

Elastic Query

Why wrong: Elastic Query is the native Azure SQL Database feature that supports T-SQL queries joining tables from multiple databases. It works by defining external data sources and external tables that map to remote tables, allowing the query optimizer to push down filters and joins to the target database when possible. This capability is specifically designed for cross-database querying on the same logical server, so it directly satisfies the requirement for a complex T-SQL query.

Point-in-time restore

Why wrong: Point-in-time restore is for recovering from user errors, not region failures.

Auto-failover groups

Why wrong: Auto-failover groups use geo-replication but add automatic failover; the question asks about the feature for minimal data loss, which is geo-replication itself.

Read-access geo-redundant storage (RA-GRS)

Why wrong: RA-GRS is for storage accounts, not Azure SQL Database.