Hard Difficulty Questions

Create a secondary database in a paired region using active geo-replication and configure it as readable.

Why it fails: Active geo-replication is asynchronous, so it cannot guarantee zero data loss during an unplanned failover.

Deploy a failover group with a secondary database in a paired region using Business Critical, but do not make the secondary readable.

Why it fails: This meets the DR requirement but does not allow read traffic offloading.

Configure zone redundancy on the existing Business Critical database in East US.

Why it fails: Zone redundancy protects only within a region, not against regional disaster.

Use Azure App Service deployment slots for the database

Why it fails: Deployment slots are for App Service, not Azure SQL Database.

Use GitHub Actions deployment environments with approval gates

Why it fails: Approval gates prevent deployment but do not provide rollback.

Use Azure SQL Managed Instance point-in-time restore

Why it fails: PITR is for Managed Instance, and is not automatic on failure.

Increase the max log size using ALTER DATABASE.

Why it fails: Max log size sets a capacity ceiling, not throughput; it cannot raise the log write limit causing throttling. It is tempting because log-related settings appear relevant, and it would be correct when the database is genuinely running out of allocated log space rather than exceeding write throughput.

Enable accelerated database recovery to reduce log I/O.

Why it fails: Accelerated database recovery speeds crash recovery and reduces redo, not the log generation rate that exceeds the tier limit. It is tempting because it is a log-related performance feature, and it would be correct for shortening recovery time after failover, not for relieving sustained log write throttling.

Move to Business Critical tier.

Why it fails: Business Critical is explicitly excluded by the requirement, and it is the tier change that would lift the log throughput limit. It is tempting because it genuinely resolves the throttling, and it would be correct if the constraint against moving tiers were absent from the scenario.

CPU bottleneck

Why it fails: PAGEIOLATCH_SH waits occur when a thread waits for a data page to be read from storage into the buffer pool, so the bottleneck is I/O, not processor scheduling. CPU pressure instead manifests as SOS_SCHEDULER_YIELD or CXPACKET waits, making this tempting when slow queries are assumed to be compute-bound.

Blocking between concurrent transactions

Why it fails: PAGEIOLATCH_SH is a storage-read wait, so it points to slow I/O rather than lock contention. Blocking produces LCK_M_* waits, which is why this misleads anyone who equates long-running queries with concurrent-transaction contention; the correct diagnosis here is disk latency or insufficient memory.

Memory pressure

Why it fails: PAGEIOLATCH_SH measures time waiting for pages to be read from disk, which is an I/O symptom; memory pressure instead shows RESOURCE_SEMAPHORE or low page-life-expectancy. It tempts because insufficient buffer-pool memory does force extra physical reads, but the wait itself names storage latency, not memory shortage.

Configure the column master key to be stored in Azure Key Vault.

Why it fails: The column master key must be stored in the enclave, not in Azure Key Vault.

Enable the enclave attestation policy on the managed instance.

Why it fails: The attestation policy is configured in Azure Attestation, not on the instance itself.

Enable Virtualization-Based Security (VBS) enclave type.

Why it fails: VBS enclaves are not supported for SQL Managed Instance; Intel SGX is required.

Set up log shipping from West Europe to North Europe.

Why it fails: Log shipping copies transaction logs to a secondary that stays in standby, so it cannot provide automated failover or a readable replica. It is tempting because log shipping is a genuine SQL Server DR technique, and would suit manual failover to a warm standby where read access is not needed.

Configure active geo-replication between the instances.

Why it fails: Active geo-replication is unavailable on SQL Managed Instance; it applies to Azure SQL Database. The tempting fit is its readable secondary with automated failover, but Managed Instance achieves this through auto-failover groups, which also support readable replicas and backups.

Create a failover group, but note the secondary is not readable.

Why it fails: Failover groups for SQL Managed Instance do provide a readable secondary, so this stated limitation is incorrect and the configuration would actually meet the requirement. It is tempting because failover groups are the automated-failover mechanism, but the option wrongly disqualifies them on readability.

The failover group will not fail over automatically because the grace period is too long.

Why it fails: Automatic failover triggers once the outage exceeds the grace period; 60 minutes is a valid configured value, not a blocker. It is tempting because a long grace period delays failover, and a shorter interval would be chosen when faster recovery is required.

The failover group will immediately fail over to West Europe.

Why it fails: Automatic failover waits out the 60-minute grace period before switching to West Europe, so it is not immediate. It is tempting because automatic failover is enabled, and immediate failover would occur if the grace period were set to zero.

The failover group requires manual failover because readOnlyEndpoint is disabled.

Why it fails: The readOnlyEndpoint setting governs read-only listener routing, not failover initiation; automatic failover on readWriteEndpoint still applies. It is tempting because a disabled endpoint appears to limit functionality, and manual failover would be correct if automatic failover were not configured.

Store the Key Vault in the same resource group as the SQL server.

Why it fails: Best practice is to use a separate resource group.

Disable Key Vault auditing to reduce costs.

Why it fails: Auditing is important for security monitoring.

Business Critical tier with 8 vCores

Why it fails: Business Critical is more expensive than needed.

Serverless tier with auto-pause

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

General Purpose tier with 2 vCores

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

Implement dynamic data masking to hide sensitive data from unauthorized users.

Why it fails: Dynamic data masking hides column values in query results but does not stop injected SQL from executing or altering data. It is tempting because it is a genuine security feature, and it would be correct when the requirement is limiting exposure of sensitive data to non-privileged users rather than preventing injection.

Enable Transparent Data Encryption (TDE) to encrypt the database at rest.

Why it fails: TDE encrypts data and backups at rest; it does not inspect or block malicious SQL statements, so injection still executes. It is tempting because it is a core database security control, and it would be correct when the requirement is compliance-driven encryption of stored data rather than input validation.

Configure a service endpoint on the subnet of the managed instance.

Why it fails: Service endpoints are for PaaS services outside the VNet; the instance is inside the VNet.

Deploy the managed instance outside a virtual network.

Why it fails: Azure SQL Managed Instance must be deployed inside a virtual network.

Attach a private endpoint to the managed instance.

Why it fails: The managed instance is already inside the VNet; a private endpoint is redundant.

Perform an ALTER INDEX REBUILD on the affected columnstore indexes after business hours.

Why it fails: REBUILD is an offline operation that locks the table, impacting availability.

Perform an ALTER INDEX REORGANIZE on the affected columnstore indexes.

Why it fails: REORGANIZE alone does not compress delta row groups and may not fully reclaim space from deleted rows.

Rebuild the entire database by creating a new database and copying data.

Why it fails: This is excessive and causes significant downtime.

Azure SQL Database (single database)

Why it fails: 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 it fails: 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 it fails: 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.

The secondary region is not in the same geo as the primary.

Why it fails: ASR requires the target region to be within the same geopolitical boundary as the source, so a cross-geo pairing cannot be configured and is not the cause of inconsistency. Geo pairing matters for compliance and data-residency planning, not for the crash-consistency behaviour observed during the drill.

The backup retention period is too short.

Why it fails: Backup retention governs how long copies are kept, not the consistency of an ASR-replicated VM; the cause is application-consistent versus crash-consistent snapshots. It is tempting because retention settings do affect recoverability, and would be correct if the scenario described backups expiring before restoration.

The database is not part of an Always On availability group.

Why it fails: ASR replicates the VM at the hypervisor layer, capturing crash-consistent snapshots unless VSS is coordinated; Always On membership is irrelevant to that mechanism. Availability groups provide database-level replication and automatic failover, which is the right solution when application-consistent database recovery is required.

The geoBackupPolicy state is Enabled.

Why it fails: This policy may be enabled but without geo-redundant storage, cross-region restore is not possible.

The geoBackupPolicy location is set to eastus2.

Why it fails: The location indicates where the backup is stored, but the storage redundancy is the critical factor.

The weeklyRetention is set to P4W.

Why it fails: Retention settings do not affect cross-region restore capability.

Use Azure Blueprints to deploy a predefined ARM template that enables Defender for SQL.

Why it fails: Azure Blueprints is being retired and assigns artefacts at definition time, so it cannot continuously enforce Defender for SQL on newly created databases across subscriptions. It is tempting as a governance packaging tool, but Azure Policy with a DeployIfNotExists effect provides the ongoing compliance enforcement required.

Create a script that runs periodically to check and enable Defender for SQL on all databases.

Why it fails: A periodic script only detects and remediates drift after the fact, leaving gaps between runs, so it cannot guarantee continuous enforcement. It is tempting because scripting is familiar and automatable, but Azure Policy's DeployIfNotExists effect enforces Defender for SQL at resource creation and remediates non-compliant databases.

Assign the 'SQL Security Manager' role to a central team to manually enable Defender for SQL.

Why it fails: Manual role assignment relies on human action, so it cannot scale across all subscriptions or guarantee every database is covered. It is tempting because RBAC delegation is a recognised governance pattern, but the requirement demands automated enforcement, which Azure Policy with a DeployIfNotExists effect supplies.

The database has one high-availability replica.

Why it fails: A high-availability replica is provided by the Business Critical tier, not General Purpose, which relies on remote storage; the exhibit shows General Purpose. The statement tempts because Business Critical does include such a replica, but that tier is not configured here.

The configuration is invalid because General Purpose tier cannot be zone-redundant.

Why it fails: General Purpose supports zone-redundant configuration in supported regions, so the configuration is valid; the claim of invalidity is false. It tempts because zone redundancy was historically limited to Business Critical, but that restriction no longer applies.

The database uses locally-redundant backup storage.

Why it fails: Locally-redundant storage keeps three copies within one datacentre; the exhibit's backup storage redundancy value determines this, and it specifies zone-redundant instead. LRS tempts as the default cheap option, but the configuration shown does not select it.

Configure per-database max eDTU for the critical database

Why it fails: Per-database max eDTU caps consumption; it sets no floor, so the critical database can still be starved when the pool is contended. It is tempting because it does govern resource allocation within a pool, and would be correct to prevent one database from monopolising shared eDTUs.

Increase the eDTU of the elastic pool

Why it fails: Raising pool eDTUs lifts the ceiling for every database collectively, but contention persists because no per-database minimum is enforced. It is tempting because scaling the pool genuinely relieves aggregate pressure when all members are uniformly busy; it would be correct if the pool, rather than one database, needed more total capacity.

Move the critical database to a dedicated service tier

Why it fails: A dedicated tier removes the database from the pool entirely, so it no longer shares eDTUs, but the requirement is a guaranteed minimum within the pool, which per-database minimum eDTUs deliver. Dedicated tiers suit workloads needing isolated, predictable compute independent of pool neighbours.

SQL Server Integration Services (SSIS) packages.

Why it fails: SSIS can be migrated with some planning.

Use of contained databases.

Why it fails: Contained databases matter for logins and users without server-level dependencies, but they are not a migration-planning consideration for Azure SQL Managed Instance, which supports instance-level logins and cross-database queries within the instance. This option would be relevant when planning Azure SQL Database, where contained users are required because server-level principals are unavailable.

Change the service tier to Business Critical

Why it fails: Business Critical adds a local secondary replica for in-region failover, not a cross-region secondary, so it cannot meet the different-region requirement. It is tempting because it raises the SLA and provides faster local failover, and would be correct when only zone-level or same-region high availability is needed.

Enable zone redundancy on the primary database

Why it fails: Zone redundancy spreads replicas across availability zones within one region, so it cannot deliver failover to a database in a different Azure region. It is tempting because it removes single-datacentre risk and is the right choice when the requirement is in-region resilience rather than geographic failover.

Create an auto-failover group

Why it fails: Failover group alone does not set up replication; it requires geo-replication.