Scenario practice questions

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 unavailable in Azure SQL Database, so no Agent job can be created there. It is the correct scheduler for index maintenance on Azure SQL Managed Instance or a SQL Server VM, where the Agent service actually exists.

Trap 2: Azure Automation Runbook

Azure Automation Runbooks execute PowerShell or Python workflows, not T-SQL directly against Azure SQL Database on a schedule; they lack a native T-SQL job step. They suit orchestrating cross-service automation, such as scaling or VM operations, where no database-native scheduler exists.

Trap 3: Azure Logic Apps

Logic Apps orchestrate connectors and workflows triggered by events or schedules, but they do not natively execute T-SQL index maintenance scripts as scheduled jobs. They fit integrating systems, for example reacting to a file upload, rather than database-internal maintenance.

A
SQL Server Agent
Why it fails: SQL Server Agent is unavailable in Azure SQL Database, so no Agent job can be created there. It is the correct scheduler for index maintenance on Azure SQL Managed Instance or a SQL Server VM, where the Agent service actually exists.
B
Elastic Database Jobs
Elastic Database Jobs run T-SQL against Azure SQL Database on a defined recurrence, satisfying the scheduled index-maintenance requirement. Unlike SQL Agent, which is unavailable in Azure SQL Database, elastic jobs target logical servers and databases directly, executing scripts such as index rebuilds without external orchestration.
C
Azure Automation Runbook
Why it fails: Azure Automation Runbooks execute PowerShell or Python workflows, not T-SQL directly against Azure SQL Database on a schedule; they lack a native T-SQL job step. They suit orchestrating cross-service automation, such as scaling or VM operations, where no database-native scheduler exists.
D
Azure Logic Apps
Why it fails: Logic Apps orchestrate connectors and workflows triggered by events or schedules, but they do not natively execute T-SQL index maintenance scripts as scheduled jobs. They fit integrating systems, for example reacting to a file upload, rather than database-internal maintenance.

Question 2easymultiple choice

A junior developer at your company connects to an Azure SQL Database using the SQL login 'appuser'. You need to grant 'appuser' the ability to read from a table named dbo.Orders in the Sales schema, but nothing else in the database. You also want to follow the principle of least privilege. What should you do?

Trap 1: Add 'appuser' to the db_datareader role.

The db_datareader role grants SELECT on all user tables and views in the database, which is broader than the requirement. The developer would be able to read every table, not just dbo.Orders, so this violates least privilege even though it does provide the needed read access.

Trap 2: Add 'appuser' to the db_owner role.

db_owner grants full control over the database, including the ability to drop tables and manage other users. It far exceeds the requirement to read a single table and would allow the developer to alter or destroy data, which directly contradicts the least privilege goal.

Trap 3: Grant CONTROL on the Sales schema to 'appuser'.

CONTROL on a schema grants ownership-like permissions over all objects in that schema, including the ability to alter or drop them. The developer only needs to read one table, so granting schema-wide control is excessive and does not meet the least privilege requirement.

A
Grant SELECT on the dbo.Orders table to 'appuser'.
A table-level GRANT SELECT gives exactly the read permission required on dbo.Orders and nothing more. It follows least privilege because the user receives no access to other tables, views, or schemas, and it is the most granular standard permission available for this scenario.
B
Add 'appuser' to the db_datareader role.
Why it fails: The db_datareader role grants SELECT on all user tables and views in the database, which is broader than the requirement. The developer would be able to read every table, not just dbo.Orders, so this violates least privilege even though it does provide the needed read access.
C
Add 'appuser' to the db_owner role.
Why it fails: db_owner grants full control over the database, including the ability to drop tables and manage other users. It far exceeds the requirement to read a single table and would allow the developer to alter or destroy data, which directly contradicts the least privilege goal.
D
Grant CONTROL on the Sales schema to 'appuser'.
Why it fails: CONTROL on a schema grants ownership-like permissions over all objects in that schema, including the ability to alter or drop them. The developer only needs to read one table, so granting schema-wide control is excessive and does not meet the least privilege requirement.

Question 3mediummulti select

Which TWO of the following are supported high availability features in Azure SQL Managed Instance?

Trap 1: Database mirroring

Database mirroring is a deprecated SQL Server feature and is not offered as a high availability mechanism in Azure SQL Managed Instance, which uses Always On availability groups instead. It would be tempting when migrating legacy on-premises SQL Server estates that still rely on mirroring for failover.

Trap 2: Log shipping to a secondary instance

Log shipping provides disaster recovery to a secondary instance with manual or scheduled failover, not automatic high availability within Azure SQL Managed Instance. It would be the right choice for cross-region DR with a defined recovery point objective, not for HA.

Trap 3: Windows Server Failover Clustering

Windows Server Failover Clustering is an operating-system-level feature unavailable to customers because Azure SQL Managed Instance abstracts the underlying platform. It would be tempting when migrating on-premises SQL Server failover cluster instances, whose HA was built on WSFC.

A
Zone-redundant deployment for Business Critical tier
Zone-redundant deployment spreads Business Critical replicas across availability zones, protecting against datacentre-level failure with automatic failover. This is a supported high availability feature in Azure SQL Managed Instance, unlike General Purpose, which relies on local redundancy only.
B
Database mirroring
Why it fails: Database mirroring is a deprecated SQL Server feature and is not offered as a high availability mechanism in Azure SQL Managed Instance, which uses Always On availability groups instead. It would be tempting when migrating legacy on-premises SQL Server estates that still rely on mirroring for failover.
C
Always On Availability Groups (built-in)
Built-in Always On Availability Groups give Azure SQL Managed Instance automatic failover and synchronous replicas without manual clustering, satisfying the high availability requirement. Unlike Azure SQL Database, Managed Instance exposes the AG infrastructure directly, so this is a supported HA feature.
D
Log shipping to a secondary instance
Why it fails: Log shipping provides disaster recovery to a secondary instance with manual or scheduled failover, not automatic high availability within Azure SQL Managed Instance. It would be the right choice for cross-region DR with a defined recovery point objective, not for HA.
E
Windows Server Failover Clustering
Why it fails: Windows Server Failover Clustering is an operating-system-level feature unavailable to customers because Azure SQL Managed Instance abstracts the underlying platform. It would be tempting when migrating on-premises SQL Server failover cluster instances, whose HA was built on WSFC.

Question 4mediummultiple choice

You are designing a new Azure SQL Database for a critical OLTP workload. The database will be used by a global application with users in North America, Europe, and Asia. The primary requirement is low-latency reads for all regions. You need to choose a deployment option that supports geo-distributed reads and provides a single write endpoint. Which option should you select?

Trap 1: Azure SQL Database with active geo-replication

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.

Trap 2: Azure SQL Managed Instance with failover groups

Failover groups for Managed Instance do not provide readable secondaries.

Trap 3: Azure SQL Database Hyperscale with geo-replication

Hyperscale geo-replication supports multiple readable secondaries across regions.

A
Azure SQL Database with active geo-replication
Why it fails: 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.
B
Azure SQL Managed Instance with failover groups
Why it fails: Failover groups for Managed Instance do not provide readable secondaries.
C
Azure SQL Database with failover groups
Failover groups only support one readable secondary region.
D
Azure SQL Database Hyperscale with geo-replication
Why it fails: Hyperscale geo-replication supports multiple readable secondaries across regions.

Question 5mediummultiple choice

You have an Azure SQL Database configured with active geo-replication to a secondary region. The primary region experiences a full outage. You need to fail over with minimal data loss. What should you do?

Trap 1: Delete the secondary database and create a new one in the primary…

Deleting the secondary removes the only replica, leaving no failover target and destroying the recovery path. It is tempting because recreating databases is routine when reconfiguring replication topology, and would be correct when retiring an obsolete replica after the primary is healthy again.

Trap 2: Initiate a planned failover from the primary to the secondary.

A planned failover requires both primary and secondary online, so it cannot run during a full regional outage. It is tempting because planned failover guarantees zero data loss by synchronising before switching, and would be correct for scheduled maintenance or drills while the primary remains reachable.

Trap 3: Create a new secondary database in the same region as the primary.

Creating a secondary in the failed primary region provides no failover target and cannot restore service. It is tempting because adding a replica is the normal way to increase redundancy, and would be correct when provisioning geo-replication initially or rebuilding after the outage is resolved.

A
Initiate an unplanned failover from the primary to the secondary.
Unplanned failover immediately promotes the geo-secondary to primary without waiting for the primary to recover, accepting the small replication lag as potential data loss. This is the only option that restores write availability during a full regional outage with minimal loss.
B
Delete the secondary database and create a new one in the primary region.
Why it fails: Deleting the secondary removes the only replica, leaving no failover target and destroying the recovery path. It is tempting because recreating databases is routine when reconfiguring replication topology, and would be correct when retiring an obsolete replica after the primary is healthy again.
C
Initiate a planned failover from the primary to the secondary.
Why it fails: A planned failover requires both primary and secondary online, so it cannot run during a full regional outage. It is tempting because planned failover guarantees zero data loss by synchronising before switching, and would be correct for scheduled maintenance or drills while the primary remains reachable.
D
Create a new secondary database in the same region as the primary.
Why it fails: Creating a secondary in the failed primary region provides no failover target and cannot restore service. It is tempting because adding a replica is the normal way to increase redundancy, and would be correct when provisioning geo-replication initially or rebuilding after the outage is resolved.

Question 6mediummulti select

You need to automate the deployment of schema changes to an Azure SQL Database using Azure DevOps. Which THREE components are required? (Choose three.)

Trap 1: Elastic job agent

Elastic Job Agent runs T-SQL across many databases on a schedule; it does not deploy schema changes from a repository. Deployment requires a DACPAC or SQL script, a pipeline task and a target connection. Elastic Job Agent would be correct for executing recurring maintenance queries across a fleet of databases.

Trap 2: Azure Automation runbook

Azure Automation runbooks execute PowerShell or Python on a schedule or webhook; they do not perform DACPAC-based schema deployment through a build pipeline. The required components are the SQL project artefact, a deployment task and a connection. Azure Automation would be correct for orchestrating scheduled operational tasks outside Azure DevOps.

A
Build pipeline
A build pipeline compiles and packages the database project into a DACPAC artefact, which the release stage later deploys. It is a required component because it produces the deployable schema artefact consumed by subsequent pipeline stages.
B
Release pipeline
A release pipeline orchestrates the deployment of schema changes to Azure SQL Database, satisfying the automation requirement in Azure DevOps. It consumes build artefacts and executes tasks against the target database, providing the controlled, repeatable deployment mechanism the scenario demands.
C
Elastic job agent
Why it fails: Elastic Job Agent runs T-SQL across many databases on a schedule; it does not deploy schema changes from a repository. Deployment requires a DACPAC or SQL script, a pipeline task and a target connection. Elastic Job Agent would be correct for executing recurring maintenance queries across a fleet of databases.
D
Azure Automation runbook
Why it fails: Azure Automation runbooks execute PowerShell or Python on a schedule or webhook; they do not perform DACPAC-based schema deployment through a build pipeline. The required components are the SQL project artefact, a deployment task and a connection. Azure Automation would be correct for orchestrating scheduled operational tasks outside Azure DevOps.
E
Variable group
A variable group stores the connection strings, credentials and target server names that the pipeline tasks consume, keeping deployment parameters out of the YAML definition. It satisfies the requirement to automate schema deployment by supplying reusable, centrally managed values across pipeline stages.

Question 7mediummultiple choice

Your company is deploying a multi-tenant application using Azure SQL Database. Each tenant gets its own database. You need to manage resources efficiently while ensuring performance isolation between tenants. The number of tenants fluctuates, and you want to minimize cost. What is the best strategy?

Trap 1: Deploy each tenant's database as a single database with reserved…

A single database with reserved capacity provisions fixed vCores per tenant, so fluctuating tenant counts waste spend and cannot scale elastically. It is tempting because reserved capacity suits stable, predictable single-database workloads, but the stem's requirement for cost minimisation under fluctuating tenants points to elastic pools sharing resources.

Trap 2: Use a single Hyperscale database with schema per tenant

Schema-per-tenant in one Hyperscale database shares its compute and storage, so a noisy tenant degrades every other tenant; performance isolation is lost. Hyperscale suits a single very large database needing rapid scale-out, not fluctuating multi-tenant estates where elastic pools isolate workloads per database at lower cost.

Trap 3: Use a single Azure SQL Managed Instance with multiple databases

A single managed instance pools all tenant databases onto shared compute, so one tenant's load consumes resources others need, defeating performance isolation. It is tempting because instance-level consolidation reduces licensing and management overhead when isolation is not required.

A
Deploy each tenant's database as a single database with reserved capacity
Why it fails: A single database with reserved capacity provisions fixed vCores per tenant, so fluctuating tenant counts waste spend and cannot scale elastically. It is tempting because reserved capacity suits stable, predictable single-database workloads, but the stem's requirement for cost minimisation under fluctuating tenants points to elastic pools sharing resources.
B
Use a single Hyperscale database with schema per tenant
Why it fails: Schema-per-tenant in one Hyperscale database shares its compute and storage, so a noisy tenant degrades every other tenant; performance isolation is lost. Hyperscale suits a single very large database needing rapid scale-out, not fluctuating multi-tenant estates where elastic pools isolate workloads per database at lower cost.
C
Use a single Azure SQL Managed Instance with multiple databases
Why it fails: A single managed instance pools all tenant databases onto shared compute, so one tenant's load consumes resources others need, defeating performance isolation. It is tempting because instance-level consolidation reduces licensing and management overhead when isolation is not required.
D
Use an elastic pool and add databases as needed
Elastic pools share provisioned eDTUs or vCores across databases, so fluctuating tenant counts consume pooled resources rather than per-database minimums. This satisfies cost minimisation while performance isolation is maintained because each tenant retains its own database.

Question 8easymultiple choice

You have an Azure SQL Managed Instance and notice that automatic tuning is not enabled. You want to automatically force a plan that performed better than the existing plan. What should you enable?

Trap 1: Automatic index management (DROP INDEX)

Dropping indexes removes unused or duplicate indexes; it never forces a better execution plan. The scenario needs FORCE_LAST_GOOD_PLAN. Automatic index management is tempting because it is part of the same automatic-tuning family and can improve performance indirectly, but its DROP INDEX action targets index bloat, not plan regression.

Trap 2: Automatic index management (CREATE INDEX)

Automatic index management creates indexes in response to workload analysis; it does not address plan regression. FORCE_LAST_GOOD_PLAN under automatic tuning is required. Index creation is tempting because it is an automatic-tuning feature that improves query performance, but it changes index structures, not the execution plan chosen for a query.

Trap 3: Intelligent Insights

Intelligent Insights diagnoses performance regressions and reports root cause; it does not force a plan. Automatic tuning's FORCE_LAST_GOOD_PLAN option performs that action. Intelligent Insights is tempting because it detects the same plan-change problems, but it only surfaces findings for review rather than applying the correction automatically.

A
Automatic index management (DROP INDEX)
Why it fails: Dropping indexes removes unused or duplicate indexes; it never forces a better execution plan. The scenario needs FORCE_LAST_GOOD_PLAN. Automatic index management is tempting because it is part of the same automatic-tuning family and can improve performance indirectly, but its DROP INDEX action targets index bloat, not plan regression.
B
Automatic index management (CREATE INDEX)
Why it fails: Automatic index management creates indexes in response to workload analysis; it does not address plan regression. FORCE_LAST_GOOD_PLAN under automatic tuning is required. Index creation is tempting because it is an automatic-tuning feature that improves query performance, but it changes index structures, not the execution plan chosen for a query.
C
Automatic plan correction (FORCE_LAST_GOOD_PLAN)
Automatic plan correction detects regressions where a previously good plan is replaced by a worse one, then forces the last known good plan using FORCE_LAST_GOOD_PLAN. This directly satisfies the requirement to automatically force a better-performing plan, which plain automatic tuning options such as CREATE INDEX or DROP INDEX do not provide.
D
Intelligent Insights
Why it fails: Intelligent Insights diagnoses performance regressions and reports root cause; it does not force a plan. Automatic tuning's FORCE_LAST_GOOD_PLAN option performs that action. Intelligent Insights is tempting because it detects the same plan-change problems, but it only surfaces findings for review rather than applying the correction automatically.

Question 9mediummultiple choice

You are configuring Azure SQL Database for a new e-commerce application that must support high read throughput for product catalog queries. The application uses Entity Framework Core and requires that read-only queries be offloaded to a secondary replica to reduce load on the primary. Which feature should you enable?

Trap 1: Enable Query Performance Insights and create indexes for frequent…

Query Performance Insights and indexing tune individual query plans; neither creates a secondary replica, so read-only Entity Framework Core queries still hit the primary. It is tempting because indexing genuinely raises read throughput, and would be the right answer if the requirement were query tuning rather than offloading reads.

Trap 2: Configure Active Geo-Replication with a readable secondary in a…

Active geo-replication's readable secondary serves failover and cross-region disaster recovery, and its secondary is not the same-region read-scale replica that the application's read-only connection string targets. It is tempting because it does provide a readable secondary, and would be correct for regional outage resilience.

Trap 3: Enable automatic tuning to force parameterization of queries.

Automatic tuning's FORCE_LAST_GOOD_PLAN and parameterisation options stabilise plan choice and reduce compilation; they create no secondary replica, so reads remain on the primary. It is tempting because parameterisation does cut CPU on read-heavy workloads, and would be correct if plan regression, not read offloading, were the problem.

A
Enable read scale-out and configure the application to use read-only intent.
Read scale-out provisions a read-only secondary replica, and routing connections with ApplicationIntent=ReadOnly directs Entity Framework Core catalog queries there, offloading the primary. This satisfies the high read throughput requirement without manual replication, since Microsoft Entra ID authentication and the existing connection string remain unchanged.
B
Enable Query Performance Insights and create indexes for frequent queries.
Why it fails: Query Performance Insights and indexing tune individual query plans; neither creates a secondary replica, so read-only Entity Framework Core queries still hit the primary. It is tempting because indexing genuinely raises read throughput, and would be the right answer if the requirement were query tuning rather than offloading reads.
C
Configure Active Geo-Replication with a readable secondary in a different region.
Why it fails: Active geo-replication's readable secondary serves failover and cross-region disaster recovery, and its secondary is not the same-region read-scale replica that the application's read-only connection string targets. It is tempting because it does provide a readable secondary, and would be correct for regional outage resilience.
D
Enable automatic tuning to force parameterization of queries.
Why it fails: Automatic tuning's FORCE_LAST_GOOD_PLAN and parameterisation options stabilise plan choice and reduce compilation; they create no secondary replica, so reads remain on the primary. It is tempting because parameterisation does cut CPU on read-heavy workloads, and would be correct if plan regression, not read offloading, were the problem.

Question 10mediummulti select

You are designing a security strategy for Azure SQL Database. You need to ensure that database access is secured using Microsoft Entra ID (formerly Azure Active Directory) authentication. Which THREE actions should you take? (Choose THREE.)

Trap 1: Disable SQL Server authentication after migrating to Entra ID.

Disabling SQL authentication does not by itself secure access via Microsoft Entra ID; the required actions are creating an Entra administrator, connecting with Entra credentials, and granting contained database users. Disabling SQL logins suits hardened environments once Entra access is already configured and verified.

Trap 2: Enable Transparent Data Encryption (TDE).

TDE encrypts data at rest on disk; it does not govern authentication, so it cannot secure database access through Microsoft Entra ID. Enabling TDE is the right action when the requirement is encryption of data and log files, not identity-based sign-in.

A
Create a Microsoft Entra ID administrator for the Azure SQL logical server.
Creating a Microsoft Entra ID administrator for the logical server establishes the identity that can manage Entra-authenticated access. Without this server-level administrator, database users cannot be mapped to Microsoft Entra ID principals, so it is a required action.
B
Disable SQL Server authentication after migrating to Entra ID.
Why it fails: Disabling SQL authentication does not by itself secure access via Microsoft Entra ID; the required actions are creating an Entra administrator, connecting with Entra credentials, and granting contained database users. Disabling SQL logins suits hardened environments once Entra access is already configured and verified.
C
Configure applications to use Microsoft Entra ID with MFA.
Applications connecting to Azure SQL Database must authenticate with Microsoft Entra ID and enforce MFA, satisfying the stem's requirement for Entra ID-based access. This replaces SQL logins with token-based authentication, so clients must be configured to obtain and present Entra ID tokens with MFA completed.
D
Enable Transparent Data Encryption (TDE).
Why it fails: TDE encrypts data at rest on disk; it does not govern authentication, so it cannot secure database access through Microsoft Entra ID. Enabling TDE is the right action when the requirement is encryption of data and log files, not identity-based sign-in.
E
Create a contained database user mapped to a Microsoft Entra ID principal.
A contained database user mapped to a Microsoft Entra ID principal lets that identity authenticate directly against the database without a server-level login, which is precisely how Entra ID authentication is granted in Azure SQL Database. Without this mapping, the Entra principal cannot access the database.

Question 11easymulti select

Which TWO benefits does the Hyperscale service tier of Azure SQL Database provide?

Trap 1: Built-in in-memory OLTP support.

Hyperscale's architecture separates compute from page-blob storage; in-memory OLTP is a Business Critical and Premium tier feature. It tempts because Hyperscale targets high performance, but its gains come from rapid scale-out and snapshot backups, not memory-optimised tables.

Trap 2: Zone-redundant configuration by default.

Zone redundancy is an optional configuration available across tiers, not a Hyperscale default; enabling it requires explicit selection. It tempts because Hyperscale's replicated page servers suggest inherent resilience, yet its default replicas are local, and zone redundancy would be the answer if the stem asked about configurable high availability.

Trap 3: Zero data loss in all scenarios.

Hyperscale offers no zero-data-loss guarantee in every scenario; asynchronous replication to page servers permits some loss during failover. It tempts because Business Critical's local synchronous replicas do provide that, so it would be correct if the question concerned Business Critical rather than Hyperscale.

A
Built-in in-memory OLTP support.
Why it fails: Hyperscale's architecture separates compute from page-blob storage; in-memory OLTP is a Business Critical and Premium tier feature. It tempts because Hyperscale targets high performance, but its gains come from rapid scale-out and snapshot backups, not memory-optimised tables.
B
Zone-redundant configuration by default.
Why it fails: Zone redundancy is an optional configuration available across tiers, not a Hyperscale default; enabling it requires explicit selection. It tempts because Hyperscale's replicated page servers suggest inherent resilience, yet its default replicas are local, and zone redundancy would be the answer if the stem asked about configurable high availability.
C
Up to 100 TB of database storage.
Hyperscale's architecture separates compute from a log-based storage layer that scales to 100 TB, far exceeding the 4 TB limit of General Purpose and Business Critical. This satisfies the large-storage benefit the question asks about.
D
Fast provisioning of additional read replicas.
Hyperscale provisions named read replicas in minutes by attaching them to the same page servers and log service, rather than copying data. This satisfies the stem's requirement for rapid scale-out of read workloads, unlike other tiers where replica creation depends on seeding a full database copy.
E
Zero data loss in all scenarios.
Why it fails: Hyperscale offers no zero-data-loss guarantee in every scenario; asynchronous replication to page servers permits some loss during failover. It tempts because Business Critical's local synchronous replicas do provide that, so it would be correct if the question concerned Business Critical rather than Hyperscale.

Question 12mediummultiple 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

A database per tenant gives isolation but multiplies minimum compute cost across tenants, undermining the cost-efficiency requirement. It is tempting because per-tenant databases provide strong data isolation and predictable performance, and would be correct when tenant count is small and budgets are generous.

Trap 2: Use Azure SQL Managed Instance with multiple databases

Azure SQL Managed Instance runs a dedicated instance billed continuously, so multi-tenant cost efficiency suffers compared with elastic pools. It is tempting because Managed Instance supports near-full SQL Server compatibility and instance-scoped isolation, and would be correct for lift-and-shift workloads needing those features.

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

Row-level security filters rows within one database, so all tenants share its compute and storage; noisy-neighbour contention prevents predictable per-tenant performance. It is tempting because RLS genuinely isolates rows cheaply when tenants are few and workloads uniform, but the stem requires separate databases with independent, predictable resources.

A
Deploy a single Azure SQL Database per tenant
Why it fails: A database per tenant gives isolation but multiplies minimum compute cost across tenants, undermining the cost-efficiency requirement. It is tempting because per-tenant databases provide strong data isolation and predictable performance, and would be correct when tenant count is small and budgets are generous.
B
Use Azure SQL Managed Instance with multiple databases
Why it fails: Azure SQL Managed Instance runs a dedicated instance billed continuously, so multi-tenant cost efficiency suffers compared with elastic pools. It is tempting because Managed Instance supports near-full SQL Server compatibility and instance-scoped isolation, and would be correct for lift-and-shift workloads needing those features.
C
Use a single large database with row-level security
Why it fails: Row-level security filters rows within one database, so all tenants share its compute and storage; noisy-neighbour contention prevents predictable per-tenant performance. It is tempting because RLS genuinely isolates rows cheaply when tenants are few and workloads uniform, but the stem requires separate databases with independent, predictable resources.
D
Use an elastic pool with one database per tenant
An elastic pool shares provisioned eDTUs or vCores across many single-tenant databases, so each tenant keeps its own database for isolation while aggregate resources absorb unpredictable per-tenant load spikes. This satisfies the predictable-performance and cost-efficiency constraints, since you pay for pooled capacity rather than peak provisioning per database.

Question 13mediummultiple choice

You are a database administrator for a company that runs a SaaS application on Azure SQL Database. The application's workload is unpredictable, with rapid bursts of activity that last only a few minutes. You need to ensure that the database can handle these bursts without manual intervention, while minimizing cost during idle periods. What should you do?

Trap 1: Configure the database to use the Hyperscale service tier with a…

Hyperscale is designed for large databases (up to 100 TB) and provides fast scale-out for read workloads, but it does not automatically adjust compute resources based on workload demand. A fixed number of vCores would not handle bursts without manual scaling, and it would incur cost during idle periods. This option does not meet the requirement for automatic scaling.

Trap 2: Create a read scale-out replica and route all write operations to…

Read scale-out replicas are used to offload read-only workloads from the primary database, improving read performance. However, they do not automatically scale compute resources for write-heavy bursts. The primary replica would still need to handle all writes, and manual scaling would be required to manage bursts, which does not meet the requirement for automatic handling.

Trap 3: Implement elastic database pool with multiple databases sharing…

Elastic pools are designed for multiple databases with varying usage patterns, allowing resource sharing among them. However, for a single database with unpredictable bursts, an elastic pool does not provide automatic scaling of compute resources per database. It requires manual configuration of pool resources and does not pause during idle periods, so it may not minimize cost effectively.

A
Configure the database to use the Hyperscale service tier with a fixed number of vCores.
Why it fails: Hyperscale is designed for large databases (up to 100 TB) and provides fast scale-out for read workloads, but it does not automatically adjust compute resources based on workload demand. A fixed number of vCores would not handle bursts without manual scaling, and it would incur cost during idle periods. This option does not meet the requirement for automatic scaling.
B
Enable the serverless compute tier for the database.
The serverless compute tier automatically scales compute resources based on workload demand and pauses the database during inactive periods, billing only for storage. This directly addresses unpredictable bursts by scaling up during activity and scaling down or pausing when idle, minimizing cost. It requires no manual intervention and is ideal for intermittent, unpredictable workloads.
C
Create a read scale-out replica and route all write operations to the primary replica.
Why it fails: Read scale-out replicas are used to offload read-only workloads from the primary database, improving read performance. However, they do not automatically scale compute resources for write-heavy bursts. The primary replica would still need to handle all writes, and manual scaling would be required to manage bursts, which does not meet the requirement for automatic handling.
D
Implement elastic database pool with multiple databases sharing resources.
Why it fails: Elastic pools are designed for multiple databases with varying usage patterns, allowing resource sharing among them. However, for a single database with unpredictable bursts, an elastic pool does not provide automatic scaling of compute resources per database. It requires manual configuration of pool resources and does not pause during idle periods, so it may not minimize cost effectively.

Question 14hardmultiple choice

You are planning the deployment of an Azure SQL Managed Instance to support a lift-and-shift migration of an on-premises SQL Server 2019 workload. The workload requires the ability to run cross-database queries, use SQL Server Agent, and support Service Broker. You need to choose a service tier that provides the highest availability and lowest latency for I/O-intensive operations. The budget allows for premium storage performance. Which service tier should you select?

Trap 1: Hyperscale

Hyperscale is designed for very large databases and provides rapid scale-out of storage and compute, but it is not available for Azure SQL Managed Instance. Hyperscale is a feature of Azure SQL Database only. Therefore, it cannot be selected for a Managed Instance deployment, regardless of performance requirements.

Trap 2: General Purpose

The General Purpose service tier uses remote Azure Premium Storage for data and log files, which introduces higher I/O latency compared to locally attached storage. While it supports cross-database queries and SQL Server Agent, it does not provide the lowest latency required for I/O-intensive operations. It is a cost-effective choice but not optimal for the stated performance needs.

Trap 3: Premium

Premium is a service tier in the DTU purchasing model for Azure SQL Database, not for Azure SQL Managed Instance. Managed Instance uses the vCore purchasing model with General Purpose and Business Critical tiers. Selecting Premium is not an option for Managed Instance and does not meet the requirement for the highest availability and lowest latency.

A
Hyperscale
Why it fails: Hyperscale is designed for very large databases and provides rapid scale-out of storage and compute, but it is not available for Azure SQL Managed Instance. Hyperscale is a feature of Azure SQL Database only. Therefore, it cannot be selected for a Managed Instance deployment, regardless of performance requirements.
B
Business Critical
The Business Critical service tier in Azure SQL Managed Instance uses locally attached SSD storage and includes a built-in secondary replica for high availability. This provides the lowest I/O latency and highest availability, making it suitable for I/O-intensive workloads. It also supports all the required features such as cross-database queries, SQL Server Agent, and Service Broker.
C
General Purpose
Why it fails: The General Purpose service tier uses remote Azure Premium Storage for data and log files, which introduces higher I/O latency compared to locally attached storage. While it supports cross-database queries and SQL Server Agent, it does not provide the lowest latency required for I/O-intensive operations. It is a cost-effective choice but not optimal for the stated performance needs.
D
Premium
Why it fails: Premium is a service tier in the DTU purchasing model for Azure SQL Database, not for Azure SQL Managed Instance. Managed Instance uses the vCore purchasing model with General Purpose and Business Critical tiers. Selecting Premium is not an option for Managed Instance and does not meet the requirement for the highest availability and lowest latency.

Question 15easymultiple choice

You are the DBA for a company that uses Azure SQL Database. You need to ensure that only authorized users can view sensitive columns (e.g., salary) in the Employees table. You want to obfuscate the data for certain users but allow full access to HR managers. Which feature should you use?

Trap 1: Always Encrypted

Always Encrypted encrypts column data end-to-end so authorised HR managers cannot read plaintext either, and it requires client-side key access rather than role-based obfuscation. It is tempting because it is the correct choice when the requirement is protecting sensitive columns from database administrators and Azure itself.

Trap 2: Row-Level Security (RLS)

RLS filters rows, not columns.

Trap 3: Transparent Data Encryption (TDE)

TDE encrypts data at rest at the database file level, so every user with SELECT permission still sees plaintext salary values; it cannot obfuscate columns per user. It is tempting because TDE protects against stolen media, which is the right choice when the requirement is encryption at rest rather than column masking.

A
Always Encrypted
Why it fails: Always Encrypted encrypts column data end-to-end so authorised HR managers cannot read plaintext either, and it requires client-side key access rather than role-based obfuscation. It is tempting because it is the correct choice when the requirement is protecting sensitive columns from database administrators and Azure itself.
B
Dynamic Data Masking
Dynamic Data Masking applies masking rules at query time, returning obfuscated values to unauthorised users while privileged roles such as HR managers see the underlying salary data. It satisfies the requirement to hide sensitive columns without altering stored data or application code.
C
Row-Level Security (RLS)
Why it fails: RLS filters rows, not columns.
D
Transparent Data Encryption (TDE)
Why it fails: TDE encrypts data at rest at the database file level, so every user with SELECT permission still sees plaintext salary values; it cannot obfuscate columns per user. It is tempting because TDE protects against stolen media, which is the right choice when the requirement is encryption at rest rather than column masking.

Question 16mediummultiple choice

You are a database administrator for a financial services company that uses Azure SQL Database. The company requires that all administrative tasks, such as index maintenance and statistics updates, be automated and run on a schedule. You need to implement a solution that uses T-SQL scripts and runs them on a schedule without requiring an external server. What should you use?

Trap 1: Azure Functions with timer trigger

Azure Functions can run code on a schedule using a timer trigger, and you could use it to execute T-SQL scripts. However, this requires writing and maintaining function code, managing dependencies, and handling connections. It is not a built-in database feature and adds complexity compared to Elastic Database jobs, which are purpose-built for scheduled T-SQL execution.

Trap 2: SQL Server Agent

SQL Server Agent is not available in Azure SQL Database. It is a component of SQL Server and Azure SQL Managed Instance. Azure SQL Database does not support running SQL Server Agent jobs, so you cannot use it to schedule T-SQL scripts directly within the database service. You would need an alternative like Elastic Database jobs.

Trap 3: Azure Automation runbooks

Azure Automation runbooks can run PowerShell or Python scripts to manage Azure resources, but they are not designed to execute T-SQL scripts directly against Azure SQL Database without additional setup, such as using the SqlServer module and credentials. They are more suited for orchestration and management tasks, not for running T-SQL on a schedule as a native database feature.

A
Azure Functions with timer trigger
Why it fails: Azure Functions can run code on a schedule using a timer trigger, and you could use it to execute T-SQL scripts. However, this requires writing and maintaining function code, managing dependencies, and handling connections. It is not a built-in database feature and adds complexity compared to Elastic Database jobs, which are purpose-built for scheduled T-SQL execution.
B
Elastic Database jobs
Elastic Database jobs in Azure SQL Database allow you to run T-SQL scripts against a target group of databases on a schedule. They are a built-in feature of Azure SQL Database and do not require an external server or agent. This makes them ideal for automating administrative tasks like index maintenance and statistics updates across one or many databases.
C
SQL Server Agent
Why it fails: SQL Server Agent is not available in Azure SQL Database. It is a component of SQL Server and Azure SQL Managed Instance. Azure SQL Database does not support running SQL Server Agent jobs, so you cannot use it to schedule T-SQL scripts directly within the database service. You would need an alternative like Elastic Database jobs.
D
Azure Automation runbooks
Why it fails: Azure Automation runbooks can run PowerShell or Python scripts to manage Azure resources, but they are not designed to execute T-SQL scripts directly against Azure SQL Database without additional setup, such as using the SqlServer module and credentials. They are more suited for orchestration and management tasks, not for running T-SQL on a schedule as a native database feature.

Question 17hardmultiple choice

You administer an Azure SQL Database that contains a table named dbo.Employees with columns for Social Security Number and salary. Company policy requires that support staff querying the table see only the last four digits of the Social Security Number and a masked salary value, while the payroll application, which connects with a different login, must see the actual values. You need to implement this with the least administrative effort and without changing the application queries. What should you do?

Trap 1: Encrypt the SSN and salary columns with Always Encrypted and…

Always Encrypted would prevent support staff from reading the values at all rather than showing masked versions, and it typically requires connection string changes and driver support. The requirement is partial visibility, not encryption, so this approach both over-restricts access and increases application complexity.

Trap 2: Create a view that returns masked values and grant support staff…

A view can return masked expressions, but it requires support staff to query the view rather than the table and requires you to manage grants carefully so they cannot bypass the view. It also changes the object name in queries, which conflicts with the requirement to avoid application changes and adds administrative overhead.

Trap 3: Create a row-level security policy that filters rows based on the…

Row-level security controls which rows a user can see, not which column values are displayed. Support staff would either see entire rows with the real SSN and salary or no rows at all. It cannot produce a partially masked value such as the last four digits, so it does not meet the requirement.

A
Apply Dynamic Data Masking rules to the SSN and salary columns and grant the support staff login SELECT on the table.
Dynamic Data Masking applies masking at query time for users without the UNMASK permission, while privileged logins retain full visibility. Because masking is transparent to the query text and enforced in the engine, support staff see masked values and the payroll login sees real values without any application changes.
B
Encrypt the SSN and salary columns with Always Encrypted and distribute the column master key only to the payroll application.
Why it fails: Always Encrypted would prevent support staff from reading the values at all rather than showing masked versions, and it typically requires connection string changes and driver support. The requirement is partial visibility, not encryption, so this approach both over-restricts access and increases application complexity.
C
Create a view that returns masked values and grant support staff SELECT on the view instead of the table.
Why it fails: A view can return masked expressions, but it requires support staff to query the view rather than the table and requires you to manage grants carefully so they cannot bypass the view. It also changes the object name in queries, which conflicts with the requirement to avoid application changes and adds administrative overhead.
D
Create a row-level security policy that filters rows based on the support staff login.
Why it fails: Row-level security controls which rows a user can see, not which column values are displayed. Support staff would either see entire rows with the real SSN and salary or no rows at all. It cannot produce a partially masked value such as the last four digits, so it does not meet the requirement.

Question 18mediummultiple choice

You are the database administrator for a SQL Server 2019 instance on Azure Virtual Machines. The instance hosts a database that must be available during a planned operating system update that requires a restart of the virtual machine. You need to ensure that the database remains online with minimal downtime. What should you do?

Trap 1: Enable Azure Site Recovery for the virtual machine.

Azure Site Recovery replicates the entire VM to another region for disaster recovery, but it does not provide automatic or manual failover for a database without significant downtime. Failover with ASR typically involves booting a new VM and may take minutes, not minimal downtime. It is not designed for planned OS updates with minimal database downtime.

Trap 2: Use the SQL Server IaaS Agent extension to apply the update without…

The SQL Server IaaS Agent extension provides automated backup and patching features, but it cannot apply an operating system update without restarting the VM. OS updates that require a restart cannot be avoided. This option does not provide a way to keep the database online during the restart.

Trap 3: Create a database snapshot and restore it after the update.

A database snapshot is a read-only, point-in-time copy of the database. It cannot be used to keep the database online during a VM restart; the primary database would still be offline during the restart. Restoring from a snapshot would cause additional downtime and potential data loss. This does not meet the minimal downtime requirement.

A
Enable Azure Site Recovery for the virtual machine.
Why it fails: Azure Site Recovery replicates the entire VM to another region for disaster recovery, but it does not provide automatic or manual failover for a database without significant downtime. Failover with ASR typically involves booting a new VM and may take minutes, not minimal downtime. It is not designed for planned OS updates with minimal database downtime.
B
Configure an Always On availability group with a synchronous secondary replica on a second VM.
An Always On availability group with a synchronous secondary replica allows you to perform a manual failover to the secondary before restarting the primary VM. The database remains online on the secondary, and after the primary VM restarts, you can fail back. This provides minimal downtime during the planned OS update, meeting the requirement.
C
Use the SQL Server IaaS Agent extension to apply the update without restarting.
Why it fails: The SQL Server IaaS Agent extension provides automated backup and patching features, but it cannot apply an operating system update without restarting the VM. OS updates that require a restart cannot be avoided. This option does not provide a way to keep the database online during the restart.
D
Create a database snapshot and restore it after the update.
Why it fails: A database snapshot is a read-only, point-in-time copy of the database. It cannot be used to keep the database online during a VM restart; the primary database would still be offline during the restart. Restoring from a snapshot would cause additional downtime and potential data loss. This does not meet the minimal downtime requirement.

Question 19mediummultiple choice

You are the database administrator for an Azure SQL Database that hosts a multi-tenant SaaS application. Each tenant has its own database user mapped to a Microsoft Entra ID group. The security team requires that every tenant user can see only rows belonging to their own tenant, and that no tenant can infer the existence of other tenants' data through error messages or row counts. You need to implement row-level filtering that enforces this requirement with the least administrative effort. What should you do?

Trap 1: Create a view for each tenant that filters rows by tenant ID, and…

Per-tenant views require creating and maintaining a separate object for every tenant, which does not scale and increases administrative effort as tenants are added. They also do not prevent a user with direct table permissions from bypassing the view. The scenario explicitly asks for the least administrative effort, so this approach fails the requirement even though it can filter rows.

Trap 2: Configure a database-scoped credential and use EXECUTE AS USER in…

EXECUTE AS USER inside stored procedures only enforces isolation when all access goes through those procedures. Ad hoc queries or direct table access would bypass the filter, and maintaining impersonation logic in every procedure is error-prone. It also does not provide the declarative, always-on filtering that the scenario demands, so it fails the least-effort and strict-isolation requirements.

Trap 3: Enable Dynamic Data Masking on the tenant ID column so tenants…

Dynamic Data Masking obscures values in query results but does not filter rows. A masked tenant ID still returns the row, and aggregate counts would still reveal other tenants' data, violating the requirement that tenants cannot infer the existence of other tenants' data. Masking is a presentation-layer obfuscation, not an access-control mechanism, so it cannot satisfy row isolation here.

A
Create a view for each tenant that filters rows by tenant ID, and grant tenants access only to their view.
Why it fails: Per-tenant views require creating and maintaining a separate object for every tenant, which does not scale and increases administrative effort as tenants are added. They also do not prevent a user with direct table permissions from bypassing the view. The scenario explicitly asks for the least administrative effort, so this approach fails the requirement even though it can filter rows.
B
Configure a database-scoped credential and use EXECUTE AS USER in every stored procedure that queries tenant data.
Why it fails: EXECUTE AS USER inside stored procedures only enforces isolation when all access goes through those procedures. Ad hoc queries or direct table access would bypass the filter, and maintaining impersonation logic in every procedure is error-prone. It also does not provide the declarative, always-on filtering that the scenario demands, so it fails the least-effort and strict-isolation requirements.
C
Create a security policy that uses a predicate function comparing the tenant ID column to the result of DATABASE_PRINCIPAL_ID().
A security policy with a predicate function that filters on the tenant ID column against the current database principal ID enforces row-level security directly in the database engine. Because the filter is applied to every query automatically, tenants cannot see or infer other tenants' rows, and no application changes are required. This matches the least-effort requirement while meeting the strict isolation goal.
D
Enable Dynamic Data Masking on the tenant ID column so tenants cannot see values belonging to other tenants.
Why it fails: Dynamic Data Masking obscures values in query results but does not filter rows. A masked tenant ID still returns the row, and aggregate counts would still reveal other tenants' data, violating the requirement that tenants cannot infer the existence of other tenants' data. Masking is a presentation-layer obfuscation, not an access-control mechanism, so it cannot satisfy row isolation here.

Question 20mediummultiple choice

You manage an Azure SQL Database that hosts a reporting workload. Users report that a monthly aggregation query sometimes completes in 2 seconds, but other times takes over 60 seconds, even though the underlying data volume is unchanged. Query Store shows the query has two distinct plans, and the faster plan is not always chosen. You need to force the faster plan for this query. What should you do?

Trap 1: Create a plan guide using sp_create_plan_guide with the OPTION (USE…

sp_create_plan_guide with USE PLAN is not supported in Azure SQL Database. While plan guides are available in some SQL Server and Azure SQL Managed Instance contexts, Azure SQL Database does not support the USE PLAN hint in plan guides. Therefore, this approach cannot be used to force the plan in this scenario.

Trap 2: Enable automatic tuning with the FORCE_LAST_GOOD_PLAN option.

Automatic tuning FORCE_LAST_GOOD_PLAN forces the last known good plan when a plan regression is detected, but it cannot target a specific fast plan that you have identified. It relies on regression detection heuristics and may not select the 2-second plan if the regression threshold is not met, so it does not guarantee the desired outcome.

Trap 3: Enable the LEGACY_CARDINALITY_ESTIMATION database scoped…

Changing the cardinality estimation model affects all queries in the database and may produce different plans, but it does not force a specific known-good plan for this query. It could even cause regressions elsewhere. The requirement is to force the faster plan for one query, not to globally alter the optimizer behavior.

A
Create a plan guide using sp_create_plan_guide with the OPTION (USE PLAN) hint.
Why it fails: sp_create_plan_guide with USE PLAN is not supported in Azure SQL Database. While plan guides are available in some SQL Server and Azure SQL Managed Instance contexts, Azure SQL Database does not support the USE PLAN hint in plan guides. Therefore, this approach cannot be used to force the plan in this scenario.
B
Enable automatic tuning with the FORCE_LAST_GOOD_PLAN option.
Why it fails: Automatic tuning FORCE_LAST_GOOD_PLAN forces the last known good plan when a plan regression is detected, but it cannot target a specific fast plan that you have identified. It relies on regression detection heuristics and may not select the 2-second plan if the regression threshold is not met, so it does not guarantee the desired outcome.
C
Enable the LEGACY_CARDINALITY_ESTIMATION database scoped configuration.
Why it fails: Changing the cardinality estimation model affects all queries in the database and may produce different plans, but it does not force a specific known-good plan for this query. It could even cause regressions elsewhere. The requirement is to force the faster plan for one query, not to globally alter the optimizer behavior.
D
Use Query Store to force the specific fast plan for the query.
Query Store captures all plans for a query and allows you to force a specific plan by plan_id. Forcing the fast plan ensures the optimizer uses that plan regardless of parameter values, eliminating the variability. This is the precise, supported method to stabilize performance for a known-good plan in Azure SQL Database.