Scenario practice questions

Which THREE factors should be considered when choosing between Azure SQL Database active geo-replication and auto-failover groups for disaster recovery?

Trap 1: Recovery Point Objective (RPO) of 1 second

Both have RPO of 5 seconds.

Trap 2: Support for SQL Server Authentication

Both support SQL Server Authentication.

A
Automatic failover capability
Auto-failover groups provide automatic failover; active geo-replication does not.
B
Recovery Point Objective (RPO) of 1 second
Why wrong: Both have RPO of 5 seconds.
C
Number of readable secondary replicas required
Active geo-replication supports up to 4, auto-failover groups support only 1.
D
Support for SQL Server Authentication
Why wrong: Both support SQL Server Authentication.
E
Granular control over individual database failover
Active geo-replication allows per-database failover; auto-failover groups fail over all databases in the group.

Question 2easymultiple choice

You are tasked with automating index maintenance for an Azure SQL Database. Which Azure service should you use to run T-SQL scripts on a recurring schedule?

Trap 1: SQL Server Agent

SQL Server Agent is not available in Azure SQL Database; it is only available in SQL Server on-premises or Azure SQL Managed Instance.

Trap 2: Azure Automation Runbook

Automation runbooks run PowerShell or Python scripts, not T-SQL directly, unless you use Invoke-SqlCmd which adds complexity.

Trap 3: Azure Logic Apps

Logic Apps can execute T-SQL via the SQL connector, but it is more of an integration tool and not the standard for scheduled database maintenance.

A
SQL Server Agent
Why wrong: SQL Server Agent is not available in Azure SQL Database; it is only available in SQL Server on-premises or Azure SQL Managed Instance.
B
Elastic Database Jobs
Elastic Database Jobs are specifically designed to run T-SQL scripts on a schedule across one or more Azure SQL databases.
C
Azure Automation Runbook
Why wrong: Automation runbooks run PowerShell or Python scripts, not T-SQL directly, unless you use Invoke-SqlCmd which adds complexity.
D
Azure Logic Apps
Why wrong: Logic Apps can execute T-SQL via the SQL connector, but it is more of an integration tool and not the standard for scheduled database maintenance.

Question 3easymulti select

Which TWO of the following are valid options for migrating an on-premises SQL Server database to Azure SQL Database with minimal downtime?

Trap 1: Use BULK INSERT to export data from on-premises and import to Azure…

Incorrect: BULK INSERT is a one-time operation, causing downtime.

Trap 2: Use SQL Server backup to URL and restore to Azure SQL Database.

Incorrect: Restore from backup is offline and requires downtime.

Trap 3: Use the Generate Scripts wizard in SQL Server Management Studio.

Incorrect: Script generation is offline and not suitable for minimal downtime.

A
Use BULK INSERT to export data from on-premises and import to Azure SQL Database.
Why wrong: Incorrect: BULK INSERT is a one-time operation, causing downtime.
B
Configure transactional replication between on-premises SQL Server and Azure SQL Database.
Correct: Transactional replication provides continuous data synchronization with minimal downtime.
C
Use Azure Database Migration Service with online migration mode.
Correct: DMS online mode performs continuous data sync, minimizing downtime.
D
Use SQL Server backup to URL and restore to Azure SQL Database.
Why wrong: Incorrect: Restore from backup is offline and requires downtime.
E
Use the Generate Scripts wizard in SQL Server Management Studio.
Why wrong: Incorrect: Script generation is offline and not suitable for minimal downtime.

Question 4mediummultiple choice

You are deploying Azure SQL Database for a multi-tenant SaaS application. Each tenant has its own database. You need to ensure that tenant data is isolated and that performance is predictable. Cost efficiency is important. Which deployment model should you use?

Trap 1: Deploy a single Azure SQL Database per tenant

Single databases are more expensive for many small tenants.

Trap 2: Use Azure SQL Managed Instance with multiple databases

Managed Instance is more expensive and not optimized for multi-tenant SaaS.

Trap 3: Use a single large database with row-level security

Shared database risks performance interference and tenant isolation.

A
Deploy a single Azure SQL Database per tenant
Why wrong: Single databases are more expensive for many small tenants.
B
Use Azure SQL Managed Instance with multiple databases
Why wrong: Managed Instance is more expensive and not optimized for multi-tenant SaaS.
C
Use a single large database with row-level security
Why wrong: Shared database risks performance interference and tenant isolation.
D
Use an elastic pool with one database per tenant
Elastic pool isolates tenants while sharing resources efficiently.

Question 5easymultiple choice

You are deploying an Azure SQL Database for a development environment. The database will be used for testing and will be deleted after three months. You want to minimize costs while ensuring the database can be restored to any point within the retention period. Which backup retention policy should you configure?

Trap 1: Configure geo-redundant backup storage.

Geo-redundancy adds cost and is not required for dev.

Trap 2: Configure short-term backup retention to 14 days.

14-day retention adds cost unnecessarily.

Trap 3: Configure long-term backup retention (LTR) for 1 year.

LTR is not needed for a short-lived dev database.

A
Configure geo-redundant backup storage.
Why wrong: Geo-redundancy adds cost and is not required for dev.
B
Configure short-term backup retention to 7 days.
7-day retention is the default and meets the need for point-in-time restore within retention.
C
Configure short-term backup retention to 14 days.
Why wrong: 14-day retention adds cost unnecessarily.
D
Configure long-term backup retention (LTR) for 1 year.
Why wrong: LTR is not needed for a short-lived dev database.

Question 6hardmultiple choice

A company uses Azure SQL Database with elastic pools. They need to automatically scale up the pool DTU when CPU usage exceeds 80% for 5 minutes and scale down when below 20% for 10 minutes. Which solution should they implement?

Trap 1: Use Elastic Database Transactions to handle scaling

Elastic Database Transactions are for distributed transactions, not scaling.

Trap 2: Configure autoscale settings on the elastic pool in the Azure portal

Azure SQL Database elastic pools do not have built-in autoscale.

Trap 3: Create a SQL Agent job to monitor and alter the pool

SQL Agent is not available in Azure SQL Database.

A
Use Elastic Database Transactions to handle scaling
Why wrong: Elastic Database Transactions are for distributed transactions, not scaling.
B
Configure autoscale settings on the elastic pool in the Azure portal
Why wrong: Azure SQL Database elastic pools do not have built-in autoscale.
C
Use Azure Automation runbook with PowerShell to check metrics and scale
Azure Automation runbooks can use Get-AzMetric and Set-AzSqlElasticPool to implement custom autoscaling.
D
Create a SQL Agent job to monitor and alter the pool
Why wrong: SQL Agent is not available in Azure SQL Database.

Question 7hardmultiple choice

You are designing an automated backup strategy for Azure SQL Managed Instance. The solution must ensure point-in-time restore (PITR) within 2 hours for the last 7 days and long-term retention (LTR) for 5 years. Which configuration should you use?

Trap 1: Use Azure Backup for SQL Server in Azure VM to back up the managed…

Managed Instance is PaaS; Azure Backup is for IaaS.

Trap 2: Set PITR retention to 7 days and use geo-redundant backup storage…

Backup storage redundancy does not provide LTR.

Trap 3: Set PITR retention to 2 hours and configure a custom backup job…

PITR retention cannot be set to 2 hours; minimum is 1 day.

A
Use Azure Backup for SQL Server in Azure VM to back up the managed instance.
Why wrong: Managed Instance is PaaS; Azure Backup is for IaaS.
B
Set PITR retention to 7 days and use geo-redundant backup storage for LTR.
Why wrong: Backup storage redundancy does not provide LTR.
C
Set PITR retention to 2 hours and configure a custom backup job using Elastic Database Jobs.
Why wrong: PITR retention cannot be set to 2 hours; minimum is 1 day.
D
Set PITR retention to 7 days (default) and configure LTR backup policy with yearly backups for 5 years.
Managed Instance supports both PITR and LTR.

Question 8mediummulti select

You are implementing automation for an Azure SQL Database. Which TWO scenarios are appropriate for using elastic database jobs? (Choose two.)

Trap 1: Scaling up a database when CPU usage is high

Scaling is not an elastic job; it's a management operation.

Trap 2: Creating logins and users across multiple databases

User management is better done with contained database users or Entra ID.

Trap 3: Performing a full database backup

Backups are automated by Azure; no need for elastic jobs.

A
Scaling up a database when CPU usage is high
Why wrong: Scaling is not an elastic job; it's a management operation.
B
Checking data consistency across multiple databases
Data consistency checks can be run across databases.
C
Creating logins and users across multiple databases
Why wrong: User management is better done with contained database users or Entra ID.
D
Rebuilding indexes across all databases in an elastic pool
Index maintenance is a typical elastic job.
E
Performing a full database backup
Why wrong: Backups are automated by Azure; no need for elastic jobs.

Question 9easymultiple choice

You are designing a disaster recovery plan for a SQL Server 2022 on Azure VM. The solution must provide automatic failover within seconds and zero data loss. Which SQL Server feature should you use?

Trap 1: Backup and restore to another region

Not a HA solution.

Trap 2: Always On Availability Group with synchronous commit

Synchronous commit can have data loss if not all replicas acknowledge.

Trap 3: Log shipping

Log shipping has manual failover and data loss.

A
Backup and restore to another region
Why wrong: Not a HA solution.
B
Failover cluster instance (FCI) with shared storage
FCI provides automatic failover and zero data loss.
C
Always On Availability Group with synchronous commit
Why wrong: Synchronous commit can have data loss if not all replicas acknowledge.
D
Log shipping
Why wrong: Log shipping has manual failover and data loss.