Plan and implement data platform resources practice questions

Geo-replication

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

Snapshot replication

Why it fails: Snapshot replication is not supported in failover groups.

Asynchronous replication

Why it fails: Asynchronous replication may cause data loss during failover.

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.

Create a plan guide to force a specific plan

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

Use optimize for ad hoc workloads

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

Add query store and force the last good plan

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

Use elastic database query to reference external tables

Why it fails: 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 it fails: Azure SQL Database does not support linked servers.

Migrate to Azure SQL Managed Instance instead

Why it fails: Managed Instance supports cross-database queries.

Use a private endpoint only

Why it fails: Private endpoint requires additional configuration and is not necessary if VMs are in the same region.

Configure a point-to-site VPN

Why it fails: Point-to-site is for individual client connections, not VMs.

Set the firewall to allow Azure services and resources to access the server

Why it fails: This allows all Azure services, not just specific VMs.

Create a backup policy in Azure Backup Vault

Why it fails: Azure Backup Vault is not used for SQL Managed Instance backups.

Enable geo-redundant storage (GRS)

Why it fails: GRS is about storage redundancy, not retention.

Configure long-term retention (LTR) policy

Why it fails: LTR is for longer retention beyond 35 days.

Configure transactional replication to replicate changes to Azure SQL Database

Why it fails: Replication is not used for migration.

Use log shipping to continuously copy transaction logs

Why it fails: Log shipping is not supported.

Restore a native SQL Server backup to Azure SQL Database

Why it fails: Native backup restore is not supported on Azure SQL Database.

Transparent Data Encryption (TDE)

Why it fails: Transparent Data Encryption is available in both Azure SQL Database and Managed Instance, so it cannot be a Managed Instance-only feature. It encrypts data at rest using a database encryption key protected by a server or instance key. TDE would be the right answer when asked which feature both platforms share.

Elastic pools

Why it fails: Elastic pools are an Azure SQL Database deployment feature for sharing resources across multiple single databases; Managed Instance does not offer them. Managed Instance instead provides instance-scoped capabilities such as cross-database queries, SQL Agent and service broker. Elastic pools would be correct when consolidating many Azure SQL databases on a shared DTU or vCore budget.

Set collation to SQL_Latin1_General_CP1_CS_AS

Why it fails: Collation change does not affect availability.

Set zoneRedundant to true

Why it fails: Zone redundancy protects against zonal failures, not regional outages.

Change storageAccountType to RA-GRS

Why it fails: Storage account type does not affect instance availability.

Shrink the database

Why it fails: Shrinking may not free space if there is no unused space.

Scale up to a higher service objective (e.g., from GP to BC)

Why it fails: Scaling up does not increase storage; it increases performance.

Increase the log file size

Why it fails: The error is about data files, not log files.

Scale up the service tier to Hyperscale to offload writes

Why it fails: Hyperscale has different architecture but may increase cost and not directly solve write latency on secondaries.

Add more replicas to distribute the read load

Why it fails: Adding replicas increases cost and may increase write latency due to more replication.

Remove the secondary replicas and use only the primary

Why it fails: This reduces high availability and read capacity.

Switch to an offline migration using BACPAC export and import

Why it fails: Offline migration would cause downtime, which is not acceptable.

Request a higher bandwidth connection from the network team

Why it fails: Network bandwidth is a constraint but not the only factor; DMS performance also matters.

Change the target to a different Azure region that is closer to the source

Why it fails: Changing region may not be feasible and could increase latency.

Use auto-failover groups with read-write failover policy.

Why it fails: Auto-failover groups provide RPO less than 5 seconds but RTO may be up to 1 hour with automatic failover; however, the RTO requirement is less than 1 hour, but manual failover with active geo-replication is more predictable.

Use geo-restore from geo-redundant backups.

Why it fails: Geo-restore has RPO of 1 hour and RTO of several hours, not meeting requirements.

Use long-term retention (LTR) backups with daily frequency.

Why it fails: LTR backups have RPO of 1 day, not meeting the requirement.

Schedule an offline index rebuild during off-peak hours.

Why it fails: Offline rebuild locks the table, causing downtime.

Disable and rebuild indexes manually during maintenance window.

Why it fails: Disabling indexes degrades performance and is not a standard maintenance practice.

Use online index reorganize with low priority locks.

Why it fails: Reorganize is less effective for high fragmentation and may not fully resolve it.

Use SQL Server Integration Services (SSIS) to migrate schema and data.

Why it fails: SSIS is for ETL, not direct database migration.

Use the Azure portal's 'Import database' wizard directly from on-premises.

Why it fails: The Azure portal can import a BACPAC but not directly from on-premises without a file.

Switch to offline migration mode to avoid log growth issues.

Why it fails: Offline migration requires downtime and may not meet the minimal downtime requirement.

Pause the migration and request a higher network bandwidth from the ISP.

Why it fails: This may cause delays and is not under your control.

Reduce the Azure SQL Managed Instance storage performance tier to standard to reduce cost.

Why it fails: Storage tier does not affect log backup growth on the source.

Azure SQL Database offers a wider range of built-in instance-level features.

Why it fails: SQL Managed Instance provides more instance-level features, not Azure SQL Database.

Azure SQL Database allows databases up to 100 TB in size.

Why it fails: SQL Managed Instance can also support large databases, up to 16 TB per instance.

Azure SQL Database supports active geo-replication with read-scale secondaries.

Why it fails: SQL Managed Instance also supports geo-replication via Auto-Failover Groups.

Azure SQL Database in the General Purpose tier.

Why it fails: General Purpose offers local redundancy and failover groups, but its built-in high availability provides no readable secondary replica. It is tempting as a low-overhead managed option, yet the scenario explicitly requires readable secondaries, which this tier does not supply.

SQL Server on Azure VMs with Always On Availability Groups.

Why it fails: Always On Availability Groups deliver automatic failover and readable secondaries, but on Azure VMs the company patches, clusters and manages the instances itself, contradicting the least-administrative-overhead requirement. It is tempting because it fully satisfies the availability and read-scale needs.

Azure SQL Database in the Hyperscale tier.

Why it fails: Hyperscale provides high availability but readable secondaries are not available in all regions and require additional configuration.

Query Performance Insight: Automatically applies performance improvements like index creation and plan forcing.

Why it fails: Incorrect – this description belongs to Automatic Tuning, not Query Performance Insight.

Automatic Tuning: Provides proactive performance monitoring with root cause analysis and diagnostics.

Why it fails: Incorrect – this description belongs to Intelligent Insights, not Automatic Tuning.

Use the SQL Server Import/Export Wizard to copy data.

Why it fails: Import/Export Wizard performs a logical data copy, which cannot transfer the TDE certificate or preserve encryption at rest, and it incurs extended downtime for large databases. It suits small, non-encrypted datasets; preserving TDE with minimal downtime requires a native backup and restore, or the Azure Database Migration Service.

Export the database as a BACPAC file and import it into the managed instance.

Why it fails: BACPAC export omits the TDE certificate and its private key, so the target database cannot be encrypted with the same protector, and a full export/import also incurs downtime proportional to database size. BACPAC suits schema-and-data moves between tiers where TDE is not required or can be re-established.

Use transaction log shipping from the source to the target.

Why it fails: Log shipping replays transaction log backups but cannot transfer the TDE certificate, so the target database cannot be restored or brought online encrypted. Log shipping suits ongoing disaster-recovery copies between servers already sharing the same certificate, not certificate-preserving migrations.