Select Two (Multi-Select) Questions

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

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

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

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

Disable parallelism for the query using the MAXDOP 1 hint.

Why wrong: Disabling parallelism might reduce the spool's cost but does not eliminate it and could slow down other operations.

Increase the DTU or vCore count of the database.

Why wrong: Adding resources may reduce the impact but does not address the root cause of the spool.

Rewrite the query using table hints to force a specific join order.

Why wrong: Forcing a join order may not eliminate the spool and could cause other performance issues.

Recovery Point Objective (RPO) of 1 second

Why wrong: Both have RPO of 5 seconds.

Support for SQL Server Authentication

Why wrong: Both support SQL Server Authentication.

Configure transactional replication to replicate changes to Azure SQL Database

Why wrong: Replication is not used for migration.

Use log shipping to continuously copy transaction logs

Why wrong: Log shipping is not supported.

Restore a native SQL Server backup to Azure SQL Database

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

Deploy a read-scale replica in a different Azure region.

Why wrong: Read-scale replicas are for offloading read-only queries and do not provide backup retention.

Set up a long-term retention policy with a retention of 30 days.

Why wrong: LTR is intended for retaining backups beyond 35 days (e.g., monthly, yearly), not for daily backups within 30 days.

Configure active geo-replication to a secondary region.

Why wrong: Active geo-replication replicates the live database, not backups; it doesn't provide backup retention.

Enable SQL Server authentication only.

Why wrong: SQL Server authentication is separate and does not enable Entra ID authentication.

Assign the SQL Server Contributor role to the Entra ID users.

Why wrong: This role is for managing the server, not for database authentication.

Enable Azure AD integration on the SQL server.

Why wrong: Integration is automatically enabled when you set an admin; no separate step.

Rebuild the clustered index to reduce fragmentation.

Why wrong: Fragmentation is not the primary issue; a scan is due to missing indexes.

Force a different query plan using Query Store hints.

Why wrong: Forcing a plan without analysis can cause performance regression.

Increase the DTU service tier of the database.

Why wrong: DTU model is not applicable to vCore-based Azure SQL Database.

Connect using a shared access key from Azure Storage.

Why wrong: Shared access keys are not used for SQL Database connections.

Connect directly using the server's public IP address without encryption.

Why wrong: Unencrypted connections are insecure.

Connect using SQL authentication with a simple password.

Why wrong: Simple passwords are weak and SQL auth lacks MFA.

Always use SORT_IN_TEMPDB = ON to reduce tempdb contention.

Why wrong: SORT_IN_TEMPDB may increase tempdb load.

Set MAXDOP to 0 to use all CPUs for faster execution.

Why wrong: Using all CPUs can starve other workloads.

Azure Files share for witness

Why wrong: Optional, not required.

Azure SQL Database

Why wrong: Not relevant for SQL Server on VMs.

VPN gateway between regions

Why wrong: Not required.

Transparent Data Encryption (TDE)

Why wrong: Both support TDE.

Elastic pools

Why wrong: Both support elastic pools.

Reduce MAXDOP to 1.

Why wrong: Reducing MAXDOP may increase serial I/O.

Increase the buffer pool size by adding more memory.

Why wrong: Memory helps page reads, not log writes.

avg_io_stall_ms

Why wrong: I/O stall is an I/O metric.

log_bytes_used

Why wrong: Log bytes used is a log throughput metric.

Create a new Azure SQL Database.

Why wrong: Creating a new database is not a job task; jobs are for running scripts on existing databases.

Change the service tier objective (SLO) of a database.

Why wrong: Changing SLO requires ALTER DATABASE with appropriate permissions, but it is not a typical job task and is better done via Azure Automation or PowerShell.

Enable Transparent Data Encryption (TDE).

Why wrong: TDE increases log writes due to encryption.

Enable Query Store.

Why wrong: Query Store writes to the database, increasing log I/O.

Increase the frequency of database backups.

Why wrong: More backups increase log activity.

Only vCore supports Azure Hybrid Benefit.

Why wrong: Both support Azure Hybrid Benefit.

Only DTU supports elastic pools.

Why wrong: Both support elastic pools.

SQL Server Agent

Why wrong: SQL Server Agent is not available in Azure SQL Database; it is only available in SQL Server on-premises and Azure SQL Managed Instance.

Azure Functions

Why wrong: Azure Functions can run code on a timer, but they are not a dedicated service for database automation; they are event-driven compute.

Azure Logic Apps

Why wrong: Logic Apps can automate workflows, but they are not the primary service for scheduling database tasks; they are more for integration.

Backup compression.

Why wrong: Compression is applied automatically and cannot be configured.

Backup frequency (full, differential, log).

Why wrong: Backup frequency is managed automatically by Azure.

Point-in-time restore interval.

Why wrong: The restore interval is determined by the retention period, not directly configured.

Automatically rebuild fragmented indexes.

Why wrong: Index rebuilding is not an Azure Monitor feature.

Automatically scale the database to a higher service tier.

Why wrong: Auto-scaling is not available through Azure Monitor; it requires autoscale settings.

Enable read-scale out on the secondary database.

Why wrong: Read-scale out is optional and not required for automatic failover.

Set the secondary database to have the same service tier as the primary.

Why wrong: Service tiers can differ; not a requirement for automatic failover.

Configure zone redundancy on the primary database.

Why wrong: Zone redundancy provides HA within a region, not for geo-failover.