Plan and implement data platform resources practice questions

Standard geo-replication

Why wrong: Standard geo-replication creates an asynchronous secondary database on another server and region, but it does not provide any automatic failover: the DBA must manually issue ALTER DATABASE ... FAILOVER or use the portal to promote the secondary. Because the stated requirement is zero manual intervention, this offering is disqualified, and it also lacks the listener/endpoint abstraction that failover groups provide, so connection strings cannot automatically switch to the new primary.

Local redundancy with automatic failover

Why wrong: Local redundancy with automatic failover, such as Azure SQL Database's built-in high availability within a single region, detects local replica failures and reroutes connections to a healthy replica, but it does not replicate data to a different geographic location. In a regional outage, the entire Azure SQL Database becomes unavailable because all replicas reside in the same region, so this option cannot satisfy the requirement for cross-region disaster recovery.

Read-scale out

Why wrong: Read-scale out creates a read-only replica in the Premium or Business Critical service tier to offload reporting and analytics traffic, but the replica is never writeable and has no failover or promotion mechanism. It cannot act as a disaster recovery target because it does not maintain an independently replicated copy of the database in another region and cannot take over the primary role under any circumstance.

Azure SQL Database on General Purpose tier with active geo-replication.

Why wrong: Active geo-replication for Azure SQL Database creates a readable secondary database in a paired region, but the underlying replication mode is asynchronous: the primary commits a transaction first and then ships the log to the secondary. As a result, an unplanned failover can lose transactions that were committed on the primary but not yet hardened on the secondary, with an RPO that can approach five seconds. Failover groups only automate the role-change orchestration and connection redirection; they do not switch the replication mode to synchronous. The General Purpose tier also lacks the synchronous replica infrastructure needed to guarantee zero data loss across datacenters.

Azure SQL Database on Hyperscale tier with named replicas.

Why wrong: Named replicas on the Hyperscale tier are separate, read-only compute nodes that share the same page servers and log service as the primary; their purpose is to scale out read workloads, not to act as a failover target. During a regional outage, a Hyperscale database can only be failed over to a geo-secondary that is populated via asynchronous geo-replication, which means committed transactions can be lost. Because named replicas do not support synchronous log shipping and are not eligible for role change, they cannot deliver a zero-data-loss RPO across regions.

Azure SQL Managed Instance on Business Critical tier with failover groups.

Why wrong: The Business Critical tier for Azure SQL Managed Instance does include a local synchronous replica for intra-region high availability, but its cross-region failover groups rely on asynchronous replication, with an RPO of up to five seconds. Therefore, a regional outage could lose recent transactions, so it cannot satisfy the zero-data-loss requirement. In addition, the question explicitly asks about Azure SQL Database, not Azure SQL Managed Instance, which has different management, networking, and failover semantics. Even though the Business Critical tier has synchronous local replicas, that does not extend to a synchronous cross-region Disaster Recovery commitment.

Use a failover group with a secondary in a paired region

Why wrong: Incorrect. Failover groups provide an RPO of about 5 seconds but an RTO of approximately 1 hour, which exceeds the 30-second RTO. Also, they require a secondary in a paired region and do not guarantee the low RTO needed.

Deploy the database with zone-redundant configuration in the primary region

Why wrong: Incorrect. Zone-redundant configuration provides high availability within a single region by replicating across availability zones. It does not provide disaster recovery across regions, so it cannot protect against a regional outage.

Deploy the database with an auto-failover group to a secondary region

Why wrong: Incorrect. An auto-failover group is essentially a failover group with automatic failover policies. The RTO is still around 1 hour, not meeting the 30-second requirement.

Hyperscale tier with 4 vCores

Why wrong: Hyperscale tier with 4 vCores is a poor fit because Hyperscale is a service tier designed for large, highly scalable single databases, not for elastic pools; Microsoft does not offer Hyperscale for elastic pools. Even if it were possible, 4 vCores in a pool shared by 50 databases would likely be overwhelmed during load spikes, and Hyperscale's architecture (separate compute nodes, log service, and automatic storage scale) introduces costs that are impractical for many small databases with varying workloads. A DTU pool is specifically built for this use case, whereas Hyperscale targets terabyte-scale databases with high throughput and storage elasticity.

Provisioned tier with General Purpose and 2 vCores

Why wrong: A Provisioned vCore-based elastic pool with General Purpose and only 2 vCores provides too little aggregate CPU and memory for 50 databases, likely causing throttling and latency even if individual workloads are light. Furthermore, the vCore purchasing model charges per-core and includes separate storage and backup costs, making it significantly more expensive than an equivalent DTU-based pool for a mixed, variable workload where many databases idle at different times. With 2 vCores, the pool cannot effectively absorb simultaneous bursty demand across the 50 databases, so this configuration is both underpowered and cost-inefficient for the scenario.

Serverless tier with General Purpose and auto-pause enabled

Why wrong: The serverless tier with General Purpose and auto-pause enabled is not supported for elastic pools; serverless compute is exclusively available for single databases in Azure SQL Database. Elastic pools have multiple databases sharing the same resource boundary, and auto-pause would attempt to pause the entire pool, which defeats the purpose of continuous availability for all databases. Because the serverless model's cost savings rely on idle time for a single database, it cannot be applied to a pooled environment, making this option invalid for the scenario.

Use an auto-failover group with a readable secondary in a different region.

Why wrong: Auto-failover groups can be configured with a readable secondary, but they do not meet the RTO of less than 1 minute because failover can take up to an hour. Additionally, by default, the secondary in an auto-failover group is not readable unless explicitly configured for read-scale, which still does not meet the RTO requirement.

Use Azure SQL Database backup to a second region and restore on demand.

Why wrong: Azure SQL Database backup to a second region and restore on demand is incorrect because backups are not a live replica; point-in-time restore typically incurs an RPO of up to 1 hour because transaction log backups are taken every 5–10 minutes, but restoring from the latest full/differential and log backups requires replaying logs, and the restore operation itself can take hours, especially for larger databases. This does not meet the requirement of an RTO under one minute. Additionally, you have no readable copy until restore completes, and the RPO is not as tight as 5 seconds.

Use an auto-failover group with a read-scale secondary in the same region.

Why wrong: An auto-failover group with a read-scale secondary in the same region is wrong because the secondary is deployed in the same data center region, so any region-wide outage—such as a natural disaster or large-scale network failure—will also take down the secondary, leaving you without a failover target. A valid disaster recovery configuration requires at least two geographically isolated regions. While read-scale secondaries allow connection pooling and can improve performance, they do not provide cross-region resilience, and the failover group's automatic failover cannot protect against a whole-region failure when both replicas reside in that region.

Enable Active Geo-Replication with a readable secondary in West US.

Why wrong: Active Geo-Replication replicates data asynchronously but does not provide automatic failover. A manual failover is required, so it does not meet the automatic failover requirement.

Deploy a failover group with manual failover.

Why wrong: A failover group with manual failover requires administrator action to failover, which is not automatic.

Use geo-restore to recover the database in West US.

Why wrong: Geo-restore recovers from backups with an RPO of up to 1 hour, which does not ensure minimal data loss.

Azure SQL Database with active geo-replication

Why wrong: Active geo-replication fails to meet the low-latency read requirement for all regions because it does not automatically route read traffic to the nearest replica. While it provides readable secondaries and a single write endpoint, the application would still need to manage connections to specific regional replicas, or all reads would default to the primary. This option is tempting as it offers geo-distributed readable replicas and a single write endpoint, making it ideal for disaster recovery scenarios and offloading read-only reporting workloads from the primary database.

Azure SQL Managed Instance with failover groups

Why wrong: Failover groups for Managed Instance do not provide readable secondaries.

Azure SQL Database with failover groups

Why wrong: Failover groups only support one readable secondary region.

Elastic pool

Why wrong: Elastic pool provides shared resources but does not auto-scale compute for individual databases during traffic spikes.

Manual scaling using Azure CLI

Why wrong: Manual scaling using Azure CLI requires human intervention, which is not automatic.

Serverless compute tier

Why wrong: The Serverless compute tier is tempting as it offers automatic compute scaling and pausing, aligning with the need for demand-based resource adjustments without manual intervention. However, it is primarily designed for intermittent, unpredictable workloads with periods of inactivity, optimising costs by billing per second and pausing when idle. For a large e-commerce platform experiencing significant, unpredictable traffic spikes, its maximum compute limits and scaling behaviour may not provide the sustained high performance and extensive scaling range required to prevent degradation during peak loads, unlike other provisioned tiers specifically built for high-throughput, continuously active systems.

Configure active geo-replication to East US

Why wrong: Active geo-replication replicates to a readable secondary in another region asynchronously, so you might lose a few seconds of transactions on a regional outage. Unlike an auto-failover group, it does not provide automatic failover—you must manually initiate the promotion of the secondary, which adds human latency to RTO and allows transaction loss to grow while you are detecting the outage and deciding to fail over. Auto-failover groups with a grace period can trigger automatically and minimize data loss, making them superior for a DR plan.

Enable zone redundancy for the database

Why wrong: Zone redundancy for Azure SQL Database copies database replicas across availability zones in the same region, providing high availability against datacenter-level failures. However, it does nothing for a complete regional outage because all zones reside within the same geography and share common regional boundaries. This is an HA feature, not a DR feature, so it cannot meet the requirement of failing over to a different region like East US.

Configure geo-restore from the West US database

Why wrong: Geo-restore leverages geo-redundant backups stored in Azure paired regions, but backups are captured periodically—typically every 5 to 10 minutes for transaction logs and hourly for full/differential backups. This means the recovery point objective (RPO) can be as high as several hours or even days, and the restore is entirely manual, requiring you to create a new database from the backup. It is a last-resort DR option for regional outages, not a solution for minimizing data loss or achieving automatic recovery.

Add the database to an auto-failover group and use the readable secondary for reporting

Why wrong: Correct. Auto-failover groups provide a readable secondary that can be used to offload reporting queries, reducing load on the primary database.

Create a database snapshot and use it for reporting

Why wrong: Incorrect. A database snapshot is a point-in-time static copy and does not reflect ongoing changes, making it unsuitable for continuous reporting offloading.

Configure a geo-replica and use it for reporting

Why wrong: Incorrect. Geo-replication creates a secondary replica in a different region for disaster recovery, but it is not optimized for local read offloading and typically has higher latency.

Database sharding

Why wrong: Sharding requires application changes to route queries.

In-memory OLTP

Why wrong: In-memory OLTP improves performance but does not scale reads.

Elastic pool

Why wrong: Elastic pools share resources among databases, not for read scaling.

Standard

Why wrong: The Standard tier has a maximum storage limit of 1 TB and does not support automatic storage scaling.

Basic

Why wrong: The Basic tier is limited to 2 GB of storage and does not support automatic scaling.

Business Critical

Why wrong: The Business Critical tier supports up to 4 TB of storage but uses fixed provisioned storage, not automatic scaling.

Create contained database users mapped to Microsoft Entra ID identities

Why wrong: This is for granting access, not enabling authentication.

Set the database's authentication mode to 'Azure AD Only Authentication'

Why wrong: Setting the authentication mode to 'Azure AD Only Authentication' is optional. It enforces that only Azure AD identities can connect, but it is not a requirement to enable Microsoft Entra ID authentication. The authentication mode can remain as 'SQL and Azure AD authentication'.

Deploy the database in Azure SQL Managed Instance

Why wrong: Not required; any Azure SQL deployment supports Entra ID.

Register the application in Microsoft Entra ID

Why wrong: Not required for basic authentication.

Azure SQL Database (single database)

Why wrong: Azure SQL Database (single database) is a Platform-as-a-Service offering that lacks native SQL Server Agent support—you would need to implement Elastic Jobs or an external scheduler—and it restricts cross-database queries, requiring Elastic Query or manual federation. It also couples compute and storage scaling in ways that do not match the independent scaling needed for an existing on-premises workload, and many advanced engine features such as CLR, Service Broker, and database mail are unavailable or limited. These gaps would force significant application and operational changes, making it unsuitable for a migration requiring near-zero compatibility.

Azure SQL Database elastic pool

Why wrong: Azure SQL Database elastic pool is primarily a cost-management feature for multiple databases with varying utilization, sharing a fixed set of resources, but it inherits all the platform limitations of single databases. It still does not provide native SQL Server Agent—you would need to orchestrate jobs externally—and cross-database queries between pooled databases are not transparent; they require elastic query or cross-database joins with external data sources. Furthermore, elastic pools do not support independent storage scaling per database, and the shared resource model adds contention that is inappropriate for migrating a single, self-contained SQL Server instance that expects full control over job scheduling and database interactions.

Azure Synapse Analytics (dedicated SQL pool)

Why wrong: Azure Synapse Analytics (dedicated SQL pool) is a data warehouse solution, not designed for OLTP workloads. It does not support SQL Server Agent jobs, cross-database queries in the same manner, and would require significant application changes. Incorrect.

Create a secondary replica using active geo-replication and configure read-only routing.

Why wrong: Incorrect. Upgrading to Business Critical is more costly and not necessary when active geo-replication can achieve the goal at lower cost.

Upgrade to Business Critical tier and use its readable secondary.

Why wrong: Incorrect. General Purpose tier does not have a built-in read scale-out feature; that's only available in Business Critical and Hyperscale tiers.

Create an elastic pool and distribute read queries.

Why wrong: Incorrect. Elastic pools are for managing multiple databases, not for providing a readable secondary for read isolation within a single database.

Geo-replication

Why wrong: Geo-replication is not a replication mode; it's a feature.

Snapshot replication

Why wrong: Snapshot replication is not supported in failover groups.

Asynchronous replication

Why wrong: Asynchronous replication may cause data loss during failover.

Business Critical tier with 8 vCores

Why wrong: Business Critical is more expensive than needed.

Serverless tier with auto-pause

Why wrong: Serverless may not sustain 10,000 INSERTs/sec.

General Purpose tier with 2 vCores

Why wrong: General Purpose may not handle the throughput and RCSI is not enabled by default.

Create a plan guide to force a specific plan

Why wrong: Plan guides require modifying the query or creating an object, which is changing T-SQL.

Use optimize for ad hoc workloads

Why wrong: This reduces plan cache bloat but doesn't address parameter sniffing.

Add query store and force the last good plan

Why wrong: Query store doesn't prevent parameter sniffing; it only captures plans.

Dynamic data masking and row-level security

Why wrong: Neither provides encryption.

Azure Active Directory authentication and firewall rules

Why wrong: Authentication and firewall do not encrypt data.

Always Encrypted and transparent data encryption (TDE)

Why wrong: Always Encrypted is for specific columns, not all data at rest.

Use elastic database query to reference external tables

Why wrong: Elastic query is for querying other Azure SQL DBs, but it's not a direct migration.

Create a linked server in Azure SQL Database

Why wrong: Azure SQL Database does not support linked servers.

Migrate to Azure SQL Managed Instance instead

Why wrong: Managed Instance supports cross-database queries.