Knowledge + Practice

CCNA Identify considerations for relational data on Azure Questions

75 of 239 questions · Page 3/4 · Identify considerations for relational data on Azure · Answers revealed

Practice these questions Domain overview All questions

151

MCQeasy

A company plans to migrate an on-premises SQL Server database to Azure. The database uses SQL Server Agent to run scheduled jobs and performs cross-database queries within the same instance. The company wants a fully managed PaaS solution that requires minimal application changes. Which Azure SQL deployment option should they choose?

A.Azure SQL Database (single database)

B.Azure SQL Managed Instance

C.Azure SQL Database elastic pool

D.SQL Server on Azure Virtual Machines

AnswerB

Azure SQL Managed Instance offers high compatibility with on-premises SQL Server, including SQL Server Agent, cross-database queries, and linked servers. It is fully managed and requires minimal changes to existing applications, making it the best fit for this migration.

Why this answer

Azure SQL Managed Instance is the correct choice because it provides full SQL Server Agent support for scheduled jobs and enables cross-database queries within the same instance, while being a fully managed PaaS service. It offers near 100% compatibility with on-premises SQL Server, minimizing application changes during migration.

Exam trap

The trap here is that candidates often choose Azure SQL Database (single database) or elastic pool because they are more commonly discussed as PaaS, but they overlook the specific requirements for SQL Server Agent and cross-database queries, which only Managed Instance supports.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database (single database) does not support SQL Server Agent for scheduled jobs and cannot perform cross-database queries within the same instance (it uses elastic query or external tables instead). Option C is wrong because Azure SQL Database elastic pool is a resource-sharing model for multiple single databases, inheriting the same limitations as single databases (no SQL Server Agent, no cross-database queries). Option D is wrong because SQL Server on Azure Virtual Machines is an IaaS solution, not a fully managed PaaS, requiring the customer to manage the VM and SQL Server, which contradicts the requirement for minimal management overhead.

Practice this question →

152

MCQmedium

A development team is designing a relational database for a hospital patient management system. They need to ensure that each patient's medical record number is unique and not null. Which database constraint should they use?

A.FOREIGN KEY

B.CHECK

C.UNIQUE

D.PRIMARY KEY

AnswerD

Correct. A PRIMARY KEY constraint enforces both uniqueness and non-null, making it ideal for the medical record number.

Why this answer

The PRIMARY KEY constraint enforces both uniqueness and non-nullability on the column(s) it is applied to. In a relational database, the medical record number is the natural candidate for the primary key of the Patient table because it uniquely identifies each patient and must always have a value. This directly meets the requirement that each patient's medical record number is unique and not null.

Exam trap

The trap here is that candidates often confuse UNIQUE with PRIMARY KEY, forgetting that UNIQUE allows NULL values (in most RDBMS implementations) and therefore does not satisfy the 'not null' requirement without an additional NOT NULL constraint.

How to eliminate wrong answers

Option A is wrong because a FOREIGN KEY constraint enforces referential integrity between two tables, not uniqueness or non-nullability on a single column. Option B is wrong because a CHECK constraint validates that column values meet a specified condition (e.g., age > 0) but does not enforce uniqueness or non-nullability by itself. Option C is wrong because a UNIQUE constraint ensures all values in a column are distinct but allows NULL values (unless combined with a NOT NULL constraint), so it does not guarantee the 'not null' requirement on its own.

Practice this question →

153

MCQmedium

A startup is deploying a new application on Azure SQL Database. They expect the database to start at 10 GB but grow to 500 GB over time. They want to be able to scale compute independently of storage and only pay for the compute resources they use. They also want to avoid over-provisioning and automatically pause during idle periods. Which purchasing model and service tier should they choose?

A.DTU-based model with Basic tier

B.vCore-based model with General Purpose serverless tier

C.vCore-based model with Business Critical provisioned tier

D.DTU-based model with Standard tier

AnswerB

vCore model separates compute and storage, offering independent scaling. The serverless tier auto-pauses during idle periods and scales automatically, aligning with the startup's need to only pay for used compute.

Why this answer

The vCore-based General Purpose serverless tier is correct because it allows independent scaling of compute and storage, automatically pauses the database during idle periods to eliminate compute costs, and supports growth from 10 GB to 500 GB without manual intervention. This model aligns with the startup's need to pay only for consumed compute resources and avoid over-provisioning.

Exam trap

The trap here is that candidates often confuse the DTU model's 'auto-pause' feature (which does not exist) with the vCore serverless tier's auto-pause, or assume the Basic tier's low cost and simplicity fit a growing database without checking its 2 GB storage limit.

How to eliminate wrong answers

Option A is wrong because the DTU-based Basic tier is limited to 2 GB of storage and lacks independent compute-storage scaling, making it unsuitable for growth to 500 GB. Option C is wrong because the vCore-based Business Critical provisioned tier does not support auto-pause and is designed for high-performance, always-on workloads, not cost-efficient idle management. Option D is wrong because the DTU-based Standard tier bundles compute and storage together, preventing independent scaling, and does not offer an auto-pause feature.

Practice this question →

154

Multi-Selecthard

Which THREE are valid deployment options for Azure SQL Database?

Select 3 answers

A.Managed instance

B.Single database

C.SQL Server on Azure Virtual Machine

D.Elastic pool

E.SQL Data Warehouse

AnswersA, B, D

Managed instance is a deployment option near 100% compatibility.

Why this answer

Options B, C, and E are correct. Single database, elastic pool, and managed instance are all deployment options for Azure SQL Database. Option A is wrong because virtual machine is not a deployment option for Azure SQL Database; it's SQL Server on VM.

Option D is wrong because SQL Data Warehouse is a separate service (now Synapse).

Practice this question →

155

MCQeasy

A startup is building a new application and expects rapid growth in data volume. They want a fully managed relational database service with built-in high availability and automatic backups. Which Azure service should they choose?

A.SQL Server on Azure Virtual Machines

B.Azure Database for MySQL

C.Azure SQL Database

D.Azure Cosmos DB

AnswerC

Azure SQL Database is a fully managed PaaS offering.

Why this answer

Azure SQL Database is a fully managed Platform-as-a-Service (PaaS) relational database that provides built-in high availability with a 99.99% SLA and automatic backups with point-in-time restore. It meets the startup's need for rapid growth by offering elastic scaling and minimal administrative overhead, unlike Infrastructure-as-a-Service (IaaS) options.

Exam trap

The trap here is that candidates often confuse 'fully managed' with 'IaaS' (Option A) or pick a specific database engine (Option B) without recognizing that Azure SQL Database is the default PaaS relational service that satisfies all stated requirements.

How to eliminate wrong answers

Option A is wrong because SQL Server on Azure Virtual Machines is an IaaS offering that requires manual configuration of high availability (e.g., Always On Availability Groups) and backups, contradicting the 'fully managed' requirement. Option B is wrong because Azure Database for MySQL is a fully managed relational database, but the question does not specify MySQL as the required engine; Azure SQL Database is the more general and correct choice for a relational database service with built-in high availability and automatic backups. Option D is wrong because Azure Cosmos DB is a NoSQL database service, not a relational database, and does not support SQL-based relational schemas or ACID transactions in the same way.

Practice this question →

156

MCQmedium

A company uses Azure SQL Database for a customer relationship management (CRM) system. The database is deployed in the East US region. The company has customers worldwide who access the CRM via a web application. Currently, all read and write traffic goes to the primary database in East US, causing high latency for users in Europe and Asia. The company needs to improve read performance for global users while maintaining strong consistency for writes. They also want to minimize cost. Which solution should you recommend?

A.Implement Azure Redis Cache to cache frequently accessed data

B.Configure active geo-replication with readable secondary replicas in West Europe and Southeast Asia

C.Use Azure SQL Database with geo-redundant storage and enable read-scale

D.Create read replicas in the same region and use Azure Traffic Manager to route read traffic

AnswerB

Readable secondaries in user regions reduce read latency, and writes still go to the primary for consistency.

Why this answer

Option B is correct because active geo-replication provides readable secondaries in other regions for read traffic, while writes go to the primary, maintaining consistency. Option A is wrong because read replicas in the same region do not help global users. Option C is wrong because Azure Traffic Manager does not improve database read latency.

Option D is wrong because Azure Redis Cache adds cost and complexity and does not solve the read latency for all queries.

Practice this question →

157

MCQhard

You are deploying a high-availability solution for an Azure SQL Database that must have a Recovery Point Objective (RPO) of less than 5 seconds. Which deployment option meets this requirement?

A.Azure SQL Database Hyperscale tier

B.Azure SQL Managed Instance General Purpose tier

C.Azure SQL Database Business Critical tier with zone redundancy

D.Azure SQL Database with active geo-replication

AnswerC

Synchronous replication with low RPO.

Why this answer

Option C is correct because Azure SQL Database Business Critical tier with zone redundancy provides synchronous replication achieving RPO of 0-5 seconds. Option A is wrong because active geo-replication has asynchronous replication. Option B is wrong because Azure SQL Database Hyperscale has asynchronous replication.

Option D is wrong because Azure SQL Managed Instance General Purpose has asynchronous replication.

Practice this question →

158

Multi-Selecthard

Which THREE factors should you consider when choosing between Azure SQL Database and Azure SQL Managed Instance?

Select 3 answers

A.Support for Microsoft Entra ID authentication

B.Need for SQL Server Agent jobs

C.Need for cross-database queries

D.Automatic scaling of compute resources

E.Requirement for VNet integration

AnswersB, C, D

SQL Managed Instance supports SQL Agent.

Why this answer

Option A is correct because SQL Managed Instance supports instance-level features like SQL Agent jobs. Option C is correct because SQL Managed Instance supports cross-database queries. Option D is correct because SQL Database has auto-scaling options like serverless.

Option B is wrong because both support VNet integration. Option E is wrong because both support Microsoft Entra ID authentication.

Practice this question →

159

Multi-Selectmedium

Which TWO of the following are true about Azure Database for PostgreSQL? (Select TWO.)

Select 2 answers

A.It only supports the open-source community edition of PostgreSQL.

B.It is a NoSQL database service.

C.It supports read replicas to offload read traffic.

D.It allows cross-database queries across multiple servers.

E.It provides automated backups with point-in-time restore.

AnswersC, E

Read replicas can be created in the same or different region.

Why this answer

Options B and D are correct. Azure Database for PostgreSQL is a managed service that provides automated backups and supports read replicas. Option A is incorrect because it is not a NoSQL database.

Option C is incorrect because it supports both the community and enterprise editions. Option E is incorrect because it does not support cross-database queries across different servers.

Practice this question →

160

MCQeasy

A small business wants to migrate their on-premises SQL Server database to Azure. They have limited budget and want to minimize ongoing management overhead. The database is less than 50 GB and is used by a single application with low concurrent users. The application requires compatibility with SQL Server features such as T-SQL, stored procedures, and functions. The business does not require high availability or disaster recovery. Which Azure relational database service should they choose?

A.SQL Server on Azure Virtual Machines

B.Azure SQL Database (serverless tier)

C.Azure SQL Managed Instance

D.Azure Database for MySQL

AnswerB

Serverless tier automatically pauses during inactivity, reducing cost, and requires minimal management.

Why this answer

Azure SQL Database serverless tier is the best choice because it provides a fully managed, PaaS relational database service that supports T-SQL, stored procedures, and functions, while automatically pausing during idle periods to reduce costs. With a database under 50 GB, low concurrency, and no HA/DR requirements, the serverless tier minimizes both management overhead and cost, as it charges only for compute used per second and storage consumed.

Exam trap

The trap here is that candidates often choose Azure SQL Managed Instance because of its full SQL Server compatibility, overlooking that the serverless tier of Azure SQL Database also supports T-SQL, stored procedures, and functions, and is far more cost-effective for small, low-usage workloads without HA/DR needs.

How to eliminate wrong answers

Option A is wrong because SQL Server on Azure Virtual Machines is an IaaS solution that requires ongoing management of the OS, SQL Server patches, and backups, increasing overhead and cost, which contradicts the business goal of minimizing management. Option C is wrong because Azure SQL Managed Instance is a PaaS service with near-100% SQL Server compatibility but includes built-in high availability and a higher base cost, making it overkill for a small, low-concurrency database that does not need HA/DR. Option D is wrong because Azure Database for MySQL does not support SQL Server-specific features like T-SQL, stored procedures, and functions; it uses a different SQL dialect, so the application would require significant code changes.

Practice this question →

161

Multi-Selectmedium

Which TWO of the following are benefits of using Azure SQL Database over SQL Server on Azure Virtual Machines?

Select 2 answers

A.Built-in high availability with automatic failover

B.Automated patching and updates

C.Lower cost because you can choose any number of vCores

D.Ability to install custom software on the database server

E.Full control over the operating system

AnswersA, B

Azure SQL Database provides built-in high availability without manual configuration.

Why this answer

Options B and D are correct. Azure SQL Database automates patching and provides built-in high availability. Option A is incorrect because Azure SQL Database does not provide full control over the OS.

Option C is incorrect because you cannot install custom software. Option E is incorrect because you cannot configure the exact number of vCores in some tiers, but that is not a benefit over VMs.

Practice this question →

162

MCQmedium

A company uses Azure SQL Database and wants to ensure that a specific query always uses a particular index. What should they do?

A.Update statistics on the table

B.Use a query hint to force the index

C.Rebuild the index

D.Enable Query Store

AnswerB

Query hints like WITH (INDEX(index_name)) force the optimizer to use a specific index.

Why this answer

Option B is correct because query hints, specifically the INDEX hint, allow you to force the query optimizer to use a particular index for a specific query. In Azure SQL Database, this is done by adding `OPTION (TABLE HINT (table_name, INDEX (index_name)))` to the query, overriding the optimizer's default index selection.

Exam trap

The trap here is that candidates confuse index maintenance (rebuilding, updating stats) with query plan control, thinking that a well-maintained index will automatically be used, when in fact the optimizer may still choose a different index based on cost estimates.

How to eliminate wrong answers

Option A is wrong because updating statistics helps the optimizer make better decisions but does not force a specific index; it only improves the accuracy of cardinality estimates. Option C is wrong because rebuilding an index defragments it and updates statistics, but it does not guarantee the query will use that index; the optimizer may still choose a different index. Option D is wrong because Query Store is a monitoring and troubleshooting feature that tracks query performance and plan changes, but it cannot force a specific index for a query; it can only force a specific query plan, not an index within that plan.

Practice this question →

163

MCQmedium

A financial application requires strict consistency and transaction support (ACID). Which Azure data service is most appropriate for storing its core transactional data?

A.Azure Cosmos DB

B.Azure SQL Database

C.Azure Table Storage

D.Azure Data Lake Storage

AnswerB

Azure SQL Database is a relational database engine that fully supports ACID properties for transactional workloads.

Why this answer

Azure SQL Database is a fully managed relational database service that provides full ACID (Atomicity, Consistency, Isolation, Durability) transaction support through its SQL Server engine. It is the correct choice for a financial application requiring strict consistency and transactional integrity, as it guarantees that all transactions are processed reliably and adhere to the ACID properties.

Exam trap

The trap here is that candidates often confuse Azure Cosmos DB's 'consistency levels' with full ACID transaction support, not realizing that Cosmos DB sacrifices strict transactional guarantees for global scalability and low latency.

How to eliminate wrong answers

Option A is wrong because Azure Cosmos DB is a NoSQL database that offers multiple consistency models (e.g., eventual, session, bounded staleness) but does not provide full ACID transaction support across multiple documents or partitions; it is optimized for global distribution and low latency, not strict transactional consistency. Option C is wrong because Azure Table Storage is a NoSQL key-value store that does not support ACID transactions; it offers only eventual consistency and lacks the relational integrity and transaction management required for core financial data. Option D is wrong because Azure Data Lake Storage is a massively scalable data lake for big data analytics, not a transactional database; it does not support ACID transactions or provide the relational query capabilities needed for core transactional data.

Practice this question →

164

MCQmedium

A company uses Azure SQL Database for an e-commerce application. The Orders table has millions of rows. Queries frequently filter on OrderDate and OrderStatus, and sort by OrderDate descending. Which indexing strategy will most improve query performance?

A.Create a clustered index on OrderDate and a non-clustered index on OrderStatus

B.Create a non-clustered index on (OrderDate, OrderStatus) and keep the existing clustered index on OrderID

C.Create a clustered index on OrderID and a non-clustered index on (OrderStatus, OrderDate)

D.Create a non-clustered index on (OrderDate DESC, OrderStatus) and keep the existing clustered index on OrderID

AnswerD

This index is a covering index that supports the filter on both columns and the sort order, allowing the query to retrieve data without additional sorting.

Why this answer

Option D creates a covering index for the most common query pattern: filtering on OrderDate and OrderStatus, and sorting by OrderDate descending. By specifying DESC in the index key, the index is ordered in the same direction as the sort, allowing SQL Server to avoid a sort operation and retrieve rows in order directly from the index. This non-clustered index can satisfy the query entirely without touching the clustered index (OrderID), reducing I/O and improving performance.

Exam trap

The trap here is that candidates assume any index on the filtered columns will help, but they overlook the importance of index key order matching the sort direction (DESC) to avoid a sort operation, which is a common performance pitfall in Azure SQL Database.

How to eliminate wrong answers

Option A is wrong because creating a clustered index on OrderDate would physically reorder the table by OrderDate, which can cause page splits and fragmentation due to frequent inserts, and it does not include OrderStatus for filtering, so queries would still need to look up rows. Option B is wrong because the non-clustered index on (OrderDate, OrderStatus) is not ordered descending, so queries sorting by OrderDate DESC would require an expensive sort operation; also, the clustered index on OrderID is fine, but the index order does not match the query sort. Option C is wrong because a non-clustered index on (OrderStatus, OrderDate) does not support the sort by OrderDate DESC efficiently (the leading column is OrderStatus, not OrderDate), and the clustered index on OrderID offers no benefit for date-range filtering.

Practice this question →

165

Multi-Selectmedium

Which TWO features are available in Azure SQL Database to help protect data at rest?

Select 2 answers

A.Transparent Data Encryption (TDE)

B.Dynamic Data Masking

C.Always Encrypted

D.Auditing

E.Row-Level Security

AnswersA, C

TDE encrypts the entire database at rest.

Why this answer

Option A and Option C are correct. Transparent Data Encryption encrypts the database files at rest. Always Encrypted encrypts sensitive columns at rest and in use.

Option B is wrong because Dynamic Data Masking does not encrypt data; it masks it in query results. Option D is wrong because Auditing is for tracking, not encryption. Option E is wrong because Row-Level Security controls access, not encryption.

Practice this question →

166

MCQmedium

A company uses Azure SQL Database for an order management system. The 'Orders' table has millions of rows and is queried frequently with filters on OrderDate and CustomerID. The table currently has a clustered index on OrderID. Which action will most improve query performance for these frequent filters?

A.Create a non-clustered index on OrderDate and CustomerID

B.Create a clustered index on OrderDate

C.Create a non-clustered index on OrderID

D.Partition the table by CustomerID

AnswerA

Correct. A non-clustered index on the columns used in WHERE clauses (OrderDate, CustomerID) allows the database engine to quickly locate rows without scanning the entire table.

Why this answer

The frequent filters on OrderDate and CustomerID require a covering index that includes both columns. A non-clustered index on (OrderDate, CustomerID) allows SQL Server to perform an index seek for queries filtering on those columns, avoiding full clustered index scans on the existing clustered index on OrderID. This directly reduces I/O and improves query response times.

Exam trap

The trap here is that candidates often assume partitioning alone solves query performance issues, but without an appropriate index, partitioning only helps with data management and partition elimination, not with efficient row-level filtering for specific column combinations.

How to eliminate wrong answers

Option B is wrong because changing the clustered index to OrderDate would reorganize the entire table's physical order, which could slow down other queries that rely on the current OrderID ordering and would not directly benefit the specific filter on CustomerID. Option C is wrong because creating a non-clustered index on OrderID duplicates the existing clustered index's key column, offering no performance gain for filters on OrderDate and CustomerID. Option D is wrong because partitioning the table by CustomerID improves manageability and partition elimination for range scans, but it does not create a seekable index structure for the specific combination of OrderDate and CustomerID; queries would still require scanning all partitions unless an appropriate index exists.

Practice this question →

167

MCQmedium

A company runs an e-commerce application on Azure SQL Database. The application experiences unpredictable traffic spikes during flash sales and promotional events. The company wants to automatically scale compute resources based on actual demand and pay only for the resources consumed. Which Azure SQL Database deployment option best meets these requirements?

A.Serverless

B.Provisioned DTU

C.Provisioned vCore

D.Hyperscale

AnswerA

Correct. Serverless compute automatically scales and pauses, ideal for intermittent, unpredictable workloads.

Why this answer

The Serverless deployment option for Azure SQL Database automatically scales compute resources (vCores) based on actual demand, pausing the database during idle periods and resuming on the first connection. This model charges per second for the compute used, making it ideal for unpredictable traffic spikes like flash sales, as it eliminates the need to over-provision and ensures you pay only for consumed resources.

Exam trap

The trap here is that candidates confuse Hyperscale's storage scalability with compute auto-scaling, or assume Provisioned tiers can automatically scale without manual intervention, when in fact only Serverless provides automatic compute scaling and per-second billing for intermittent workloads.

How to eliminate wrong answers

Option B is wrong because Provisioned DTU uses a fixed, pre-allocated compute and storage bundle that cannot automatically scale based on demand; you must manually change the service tier or use elastic pools, which still require upfront sizing. Option C is wrong because Provisioned vCore also uses a fixed number of vCores that must be manually scaled up or down, and it charges for the provisioned compute even when idle, not per-second consumption. Option D is wrong because Hyperscale is designed for very large databases (up to 100 TB) with fast scaling of storage and read replicas, but its compute tier is provisioned (not serverless) and does not auto-pause or charge per-second for compute; it targets high-throughput workloads, not intermittent bursty traffic.

Practice this question →

168

MCQmedium

A company is migrating a 500 GB financial database to Azure. The database requires low read/write latency, supports a high number of concurrent transactions, and must have a Recovery Point Objective (RPO) of less than 5 seconds and a Recovery Time Objective (RTO) of less than 30 minutes. The company is willing to pay more for these guarantees. Which Azure SQL Database service tier should they choose?

A.General Purpose

B.Business Critical

C.Hyperscale

D.Serverless (General Purpose)

AnswerB

Business Critical uses multiple synchronous replicas to achieve low latency, an RPO of less than 5 seconds, and an RTO of approximately 30 minutes, meeting the requirements.

Why this answer

Business Critical is the correct choice because it provides the lowest read/write latency through always-on secondary replicas and uses local SSD storage, which is essential for high-concurrency transactional workloads. It also guarantees an RPO of less than 5 seconds via synchronous data replication and an RTO of under 30 minutes, meeting the strict recovery requirements.

Exam trap

The trap here is that candidates often choose Hyperscale because it supports large databases and fast scaling, but they overlook that its RPO is not as tight as Business Critical's synchronous replication, and its read/write latency can be higher due to the page server architecture.

How to eliminate wrong answers

Option A is wrong because General Purpose uses remote blob storage with higher latency and asynchronous replication, resulting in an RPO of up to 5 minutes and RTO of 5-10 minutes, which fails the sub-5-second RPO requirement. Option C is wrong because Hyperscale, while offering fast scaling and low latency for reads, has an RPO of up to 5 seconds (not guaranteed under 5 seconds) and an RTO of up to 10 minutes, but its architecture is optimized for large databases with high throughput rather than ultra-low latency for high-concurrency OLTP; also, its RPO is not as tight as Business Critical's synchronous commit. Option D is wrong because Serverless (General Purpose) inherits the same latency and recovery limitations as General Purpose, with an RPO of up to 5 minutes and RTO of 5-10 minutes, and its auto-pause feature can introduce additional cold-start delays, making it unsuitable for strict RPO/RTO guarantees.

Practice this question →

169

MCQhard

You are designing a multi-tenant SaaS application using Azure SQL Database. Each tenant has its own database. You need to perform maintenance across all databases efficiently. Which feature should you use?

A.Elastic pools

B.Failover groups

C.SQL Server Agent

D.Elastic Jobs

AnswerD

Elastic Jobs automate administrative tasks across multiple databases.

Why this answer

Option C is correct because Elastic Jobs allows executing T-SQL scripts across multiple databases. Option A is wrong because elastic pools are for resource sharing, not for maintenance. Option B is wrong because failover groups are for high availability.

Option D is wrong because SQL Agent is not available in Azure SQL Database.

Practice this question →

170

MCQhard

Your company has an Azure SQL Database that stores customer orders. You notice that long-running reports are causing blocking on the transactional tables. Which approach would minimize impact on transaction processing while still allowing reporting?

A.Use the READ UNCOMMITTED isolation level for reporting queries

B.Increase the service tier to Business Critical

C.Shard the database by customer region

D.Create a read-only replica in Azure SQL Database Hyperscale

AnswerD

Offloads reporting to a separate replica.

Why this answer

Option C is correct because creating a read-only replica offloads reporting queries. Option A is wrong because increasing database throughput does not eliminate blocking. Option B is wrong because READ UNCOMMITTED might cause dirty reads.

Option D is wrong because distributing data across databases (sharding) adds complexity and may not solve blocking.

Practice this question →

171

MCQhard

A company uses Azure SQL Database with the Business Critical service tier. They notice increased latency during peak hours. They need to improve performance without changing the application code. Which action should they take?

A.Increase the number of vCores

B.Add a read replica

C.Enable auto-pause

D.Change the service tier to General Purpose

AnswerA

Scaling up provides more CPU/memory to handle peak load.

Why this answer

Option B is correct because increasing the number of vCores (scale up) provides more resources to handle peak load without code changes. Option A is wrong because adding read replicas helps read scalability but not write latency. Option C is wrong because changing to General Purpose might reduce performance.

Option D is wrong because enabling auto-pause is for serverless, not for performance.

Practice this question →

172

Multi-Selecthard

Which THREE are benefits of using Azure SQL Database serverless compute tier?

Select 3 answers

A.Guaranteed high availability with 99.99% SLA

B.Billing per second for compute usage

C.Auto-scaling compute based on workload

D.Auto-pause during periods of inactivity

E.Ideal for high-throughput, latency-sensitive applications

AnswersB, C, D

Billed per second of compute usage.

Why this answer

Options A, B, and E are correct. Serverless auto-scales, pauses during inactivity, and charges per second. Option C is wrong because serverless does not provide a fixed SLA for compute.

Option D is wrong because serverless is not designed for high throughput workloads; it may have cold start delays.

Practice this question →

173

Matchingmedium

Match each Azure data consistency model to its description.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts

Matches

Reads always see the latest write

Reads may lag behind writes by up to K versions or T time

Consistent reads within a client session

Reads never see out-of-order writes

No ordering guarantee, eventually consistent

Why these pairings

Azure Cosmos DB offers five consistency levels.

Practice this question →

174

MCQmedium

A company has multiple independent databases for different business units, each with low to moderate usage and varying workload patterns. They want to consolidate these databases into a single Azure SQL Database deployment option to share resources and reduce costs, while ensuring that databases do not starve each other of resources. Which Azure SQL Database deployment option should they choose?

A.Elastic pool

B.Single database (DTU model)

C.Managed Instance

D.Database per server (hyperscale)

AnswerA

Correct. Elastic pools provide a set of shared resources for multiple databases, optimizing cost and performance for databases with varying load patterns.

Why this answer

Elastic pools are designed for exactly this scenario: multiple databases with low to moderate usage and varying workload patterns. They allow databases to share a fixed pool of resources (eDTUs or vCores) while using built-in resource governance to prevent any single database from starving others, thus optimizing cost and performance.

Exam trap

The trap here is that candidates often choose Single database (DTU model) thinking it is the simplest option, but they overlook the cost and resource-sharing benefits of elastic pools for consolidating multiple low-usage databases with varying workloads.

How to eliminate wrong answers

Option B (Single database DTU model) is wrong because it allocates dedicated resources per database, which would be more expensive and wasteful for low-usage databases, and does not provide resource sharing or isolation across databases. Option C (Managed Instance) is wrong because it is a full SQL Server instance with dedicated resources, designed for lift-and-shift migrations, not for sharing resources across multiple independent databases with varying patterns. Option D (Database per server hyperscale) is wrong because hyperscale is a single-database tier for very large databases (up to 100 TB) with high throughput needs, not for consolidating multiple small databases, and it does not offer resource pooling across databases.

Practice this question →

175

MCQeasy

You need to run a complex T-SQL query that joins tables from Azure SQL Database and Azure SQL Managed Instance in a single query. Which feature should you use?

A.Database Mail

B.PolyBase

C.Linked server

D.Elastic Query

AnswerD

Elastic Query enables querying multiple databases in Azure SQL Database and Managed Instance.

Why this answer

Elastic Query (Option D) is the correct feature because it enables running complex T-SQL queries that span multiple databases in Azure SQL Database and Azure SQL Managed Instance, allowing you to join tables across these services in a single query. It uses a logical database as a shard map manager to distribute queries across remote databases, supporting cross-instance and cross-database queries without data movement.

Exam trap

The trap here is that candidates often confuse PolyBase with Elastic Query because both involve querying external data, but PolyBase is for non-relational or file-based sources, while Elastic Query is specifically for cross-database queries within Azure SQL Database and Azure SQL Managed Instance.

How to eliminate wrong answers

Option A is wrong because Database Mail is a feature for sending email messages from SQL Server, not for querying across databases or instances. Option B is wrong because PolyBase is designed for querying external data sources like Hadoop or Azure Blob Storage using T-SQL, not for joining tables across Azure SQL Database and Azure SQL Managed Instance. Option C is wrong because Linked Server is a SQL Server feature for connecting to remote data sources, but it is not supported for cross-service queries between Azure SQL Database and Azure SQL Managed Instance; it works only within on-premises or SQL Server VMs, and Azure SQL Database does not support linked servers to external instances.

Practice this question →

176

MCQmedium

A company uses Azure SQL Database to store customer order data. They need to automatically track changes to the 'OrderStatus' column in the 'Orders' table. They want to be able to query the current status and also easily retrieve historical status changes for a given order without writing custom triggers or history tables. Which feature should they enable?

A.Change Data Capture (CDC)

B.Temporal tables

C.Automatic tuning

D.Geo-replication

AnswerB

Temporal tables are a built-in feature that automatically keeps a full history of changes within the same table using period columns. You can query both the current state and historical states without custom logic.

Why this answer

Temporal tables (system-versioned) automatically track full row history, including changes to the 'OrderStatus' column, by maintaining a paired history table. This allows querying both current and historical states with simple T-SQL clauses like FOR SYSTEM_TIME, without custom triggers or manual history tables.

Exam trap

The trap here is confusing Change Data Capture (CDC) with temporal tables, as both involve change tracking, but CDC is for streaming changes to downstream systems, not for querying historical row states per key with point-in-time accuracy.

How to eliminate wrong answers

Option A is wrong because Change Data Capture (CDC) captures changes at the transaction log level for incremental data loading or replication, not for querying historical status per row with point-in-time accuracy. Option C is wrong because Automatic tuning optimizes query performance (e.g., index recommendations, plan forcing) and does not track historical data changes. Option D is wrong because Geo-replication provides disaster recovery and read-scale by replicating the database to a secondary region, with no capability to track or query historical row changes.

Practice this question →

177

MCQmedium

An administrator runs an Azure CLI command to update an Azure SQL Database's service objective. The output shows the above properties. The database is currently at S2 and the administrator wants to scale to S3. What is the issue?

A.The command did not specify the new service objective

B.The property names are incorrect

C.The maxSizeBytes value is invalid

D.The Azure CLI does not support scaling Azure SQL Database

AnswerA

The output shows the current state; the requested objective remains S2, so no change was made.

Why this answer

Option A is correct because the properties show the current service objective but the command likely did not change it. The requestedServiceObjectiveName is still S2, meaning the update was not applied. Option B is wrong because maxSizeBytes is within range for S2.

Option C is wrong because the property names are correct. Option D is wrong because the CLI command can update service objective.

Practice this question →

178

MCQmedium

A retail company runs its legacy order management application on an on-premises SQL Server. They plan to migrate to Azure with minimal application changes and need high availability with automatic failover to a secondary Azure region. They also require full database-level isolation and the ability to use SQL Server Agent jobs. Which Azure deployment option should they choose?

A.Azure SQL Database single database

B.Azure SQL Database elastic pool

C.Azure SQL Managed Instance

D.SQL Server on Azure Virtual Machine

AnswerC

Correct. SQL Managed Instance offers high compatibility with SQL Server, supports SQL Server Agent, and enables auto-failover groups for disaster recovery.

Why this answer

Azure SQL Managed Instance (C) is correct because it provides near-100% compatibility with on-premises SQL Server, including full database-level isolation (a dedicated instance) and full support for SQL Server Agent jobs. It also supports auto-failover groups for high availability with automatic failover to a secondary Azure region, meeting the migration requirement with minimal application changes.

Exam trap

The trap here is that candidates often confuse Azure SQL Database (single or elastic pool) with SQL Managed Instance, overlooking that SQL Agent jobs and full instance-level isolation are exclusive to Managed Instance, while also mistakenly thinking that SQL Server on Azure VM is the only option for high availability with automatic failover.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database single database does not provide full database-level isolation (it runs in a shared logical server) and does not support SQL Server Agent jobs. Option B is wrong because Azure SQL Database elastic pool is a multi-tenant resource-sharing model that also lacks SQL Server Agent job support and full instance-level isolation. Option D is wrong because SQL Server on Azure Virtual Machine requires manual configuration of Always On Availability Groups for automatic failover to a secondary region, and it does not offer the same managed experience with minimal application changes as Azure SQL Managed Instance.

Practice this question →

179

MCQmedium

A company wants to analyze data from multiple Azure SQL Databases using a single query. Which Azure service should they use?

A.Azure SQL Database elastic query

B.Azure SQL Database failover groups

C.Azure Synapse Link

D.Azure Stream Analytics

AnswerA

Enables querying across multiple databases using external tables.

Why this answer

Option C is correct because Azure SQL Database elastic query allows querying across multiple databases from a single connection point. Option A is wrong because Azure Synapse Link is for near-real-time analytics on operational data, but not specifically for querying multiple databases with a single query. Option B is wrong because Azure SQL Database failover groups are for high availability, not cross-database queries.

Option D is wrong because Azure Stream Analytics is for real-time stream processing, not ad-hoc queries across databases.

Practice this question →

180

MCQeasy

A company needs to migrate a large on-premises SQL Server database to Azure. The database uses features like SQL Server Agent jobs, cross-database queries, and common language runtime (CLR) assemblies. Which Azure service supports these features?

A.SQL Server on Azure Virtual Machines

B.Azure SQL Database

C.Azure Database for PostgreSQL

D.Azure SQL Managed Instance

AnswerD

Managed Instance supports SQL Agent, cross-database queries, and CLR.

Why this answer

Option B is correct because Azure SQL Managed Instance supports SQL Agent, cross-database queries, and CLR. Option A (Azure SQL Database) does not support SQL Agent or cross-database queries. Option C (SQL Server on Azure VM) supports all features but is not a managed service.

Option D (Azure Database for PostgreSQL) is a different engine.

Practice this question →

181

MCQmedium

A company uses Azure SQL Database for an inventory management system. The Inventory table has millions of rows. Queries frequently filter on WarehouseID and then sort by LastUpdatedDate. The table currently has a clustered index on InventoryID (primary key). Which action will most improve query performance for these frequent filters?

A.Create a non-clustered index on (WarehouseID, LastUpdatedDate) INCLUDE (Quantity)

B.Add a clustered index on WarehouseID

C.Create a non-clustered index on LastUpdatedDate only

D.Partition the table by InventoryID

AnswerA

This composite index covers the filter and sort conditions. Including Quantity as an included column makes the index covering for this query, avoiding expensive key lookups.

Why this answer

A non-clustered index on (WarehouseID, LastUpdatedDate) allows the database engine to efficiently locate rows matching a specific WarehouseID and return them already sorted by LastUpdatedDate without accessing the clustered index (or with minimal lookup). Including the Quantity column as a non-key included column avoids key lookups for that column, further improving performance. Changing the clustered index to WarehouseID could cause fragmentation and is not ideal for uniqueness.

A single-column index on LastUpdatedDate does not support the filter on WarehouseID. Partitioning by InventoryID does not help this query pattern.

Practice this question →

182

MCQmedium

An e-commerce company runs a product inventory database on Azure SQL Database. During a flash sale, write transactions are slow because many read queries are running simultaneously and consuming resources. The company wants to isolate read workloads without modifying application code or database schemas. Which Azure SQL Database feature should they implement?

A.Active geo-replication

B.Read scale-out

C.Auto-failover groups

D.Elastic pools

AnswerB

Read scale-out uses a built-in readable secondary replica in the same region to serve read-only queries, reducing contention on the primary. This can be enabled without code changes.

Why this answer

Read scale-out (B) is correct because it allows read-only queries to be offloaded to a read-only replica of the database, freeing up the primary replica for write transactions. This feature is built into Azure SQL Database at the Business Critical and Premium service tiers, and it requires no application code changes—just a connection string modification to use the `ApplicationIntent=ReadOnly` parameter. It directly addresses the performance bottleneck caused by concurrent read queries during the flash sale.

Exam trap

The trap here is that candidates confuse read scale-out with geo-replication or failover groups, assuming any replica can offload reads, but only read scale-out provides a local read-only replica without requiring application code changes or cross-region latency.

How to eliminate wrong answers

Option A is wrong because active geo-replication creates readable replicas in a different Azure region for disaster recovery, not for offloading read workloads from the same region, and it requires application code changes to redirect read queries. Option C is wrong because auto-failover groups manage automatic failover between primary and secondary databases for high availability, not for isolating read workloads; they also require modifying the connection string or application logic. Option D is wrong because elastic pools are used to manage and share resources among multiple databases, not to isolate read workloads within a single database, and they do not provide a read-only replica.

Practice this question →

183

MCQhard

Your company runs a global e-commerce platform on Azure SQL Database. The platform experiences heavy read traffic on product catalog and inventory tables. You need to reduce read latency for users in different geographic regions while keeping write latency low. The solution must be cost-effective and require minimal application changes. Current architecture: a single Azure SQL Database in West US. You have budget for additional Azure resources. What should you implement?

A.Configure Active Geo-Replication to create readable secondaries in regions where users are located.

B.Migrate the database to Azure Cosmos DB with multi-region writes.

C.Use Azure Traffic Manager to route read requests to the primary database in West US.

D.Enable Read Scale-Out on the existing database and configure application to use the read-only endpoint.

AnswerA

Readable secondaries reduce read latency globally; writes go to primary.

Why this answer

Option B is correct because Active Geo-Replication creates readable secondaries in multiple regions, allowing local reads and single-region writes. Option A (Read Scale-Out) works only in the same region. Option C (Cosmos DB) would require application rewrite.

Option D (Traffic Manager) does not replicate data.

Practice this question →

184

MCQmedium

A company runs an e-commerce platform on Azure SQL Database. The database handles many concurrent transactions (OLTP). The business team runs complex reporting queries on the same database during business hours, which slows down the transactional workload. The company wants to offload the reporting queries to a separate read-only copy of the database to avoid performance impact. Which Azure SQL Database feature should they enable?

A.Hyperscale service tier

B.Geo-replication

C.Read scale-out

D.Elastic query

AnswerC

Correct. Read scale-out, also called Active Geo-Replication with readable secondaries, allows offloading read-only workloads to a secondary replica in the same region, improving OLTP performance.

Why this answer

Read scale-out (C) is the correct feature because it allows Azure SQL Database to offload read-only workloads, such as complex reporting queries, to a separate read-only replica. This is achieved by using the `ApplicationIntent=ReadOnly` connection string parameter, which routes queries to a secondary replica, thereby preventing performance impact on the primary transactional (OLTP) workload. This feature is specifically designed for scenarios where you need to isolate reporting from high-concurrency OLTP operations without requiring a separate database copy.

Exam trap

The trap here is that candidates often confuse Geo-replication with read scale-out because both provide readable secondaries, but Geo-replication is for disaster recovery and requires a separate database in a different region, while read scale-out is for performance isolation within the same region and uses the existing high-availability replicas.

How to eliminate wrong answers

Option A is wrong because Hyperscale is a service tier that provides high scalability and fast backup/restore, but it does not inherently create a separate read-only replica for offloading reporting queries; it focuses on storage and compute elasticity, not read workload isolation. Option B is wrong because Geo-replication creates a readable secondary replica for disaster recovery and geographic redundancy, but it is not designed for offloading reporting queries during business hours—it requires manual failover and is primarily for availability, not performance isolation. Option D is wrong because Elastic query enables querying across multiple Azure SQL databases or external data sources (e.g., Azure SQL Database, Azure SQL Data Warehouse) using T-SQL, but it does not provide a read-only replica for offloading reporting from a single database.

Practice this question →

185

MCQmedium

A company runs an online booking system on Azure SQL Database. The system handles many concurrent transactions (OLTP). The business team runs complex reporting queries on the same database during business hours, which slows down the booking transactions. The company needs a solution to separate the analytical workload from the transactional workload without duplicating data manually. Which Azure SQL Database feature should they use?

A.Read Scale-out (readable secondary replica)

B.Active Geo-Replication

C.Elastic pools

D.Hyperscale service tier

AnswerA

Correct. Read Scale-out allows you to offload read-only queries to a secondary replica, reducing load on the primary.

Why this answer

Read Scale-out (readable secondary replica) is the correct choice because it allows the company to offload complex reporting queries to a read-only replica of the primary database, thereby isolating the analytical workload from the OLTP transactions. This feature is built into Azure SQL Database and does not require manual data duplication or ETL processes, directly addressing the requirement to separate workloads without manual effort.

Exam trap

The trap here is that candidates often confuse Active Geo-Replication (which also provides readable secondaries) with Read Scale-out, but Geo-Replication is regionally separated and intended for disaster recovery, not for local workload isolation within the same region.

How to eliminate wrong answers

Option B (Active Geo-Replication) is wrong because it is designed for disaster recovery and business continuity by maintaining readable secondary replicas in a different Azure region, not for offloading read-only analytical workloads within the same region during business hours. Option C (Elastic pools) is wrong because they are a resource management model for sharing resources among multiple databases, not a feature for separating analytical and transactional workloads on a single database. Option D (Hyperscale service tier) is wrong because, while it offers high scalability and fast backup/restore, it does not inherently provide a built-in mechanism to separate analytical queries from transactional ones without additional configuration like Read Scale-out.

Practice this question →

186

Multi-Selectmedium

Which TWO are valid deployment options for Azure SQL?

Select 2 answers

A.Azure SQL Managed Instance

B.Azure SQL Database

C.Azure Cosmos DB

D.Azure Database for MariaDB

E.Azure Synapse Analytics Dedicated SQL Pool

AnswersA, B

Managed instance with full SQL Server compatibility.

Why this answer

Options A and D are correct. Azure SQL Database and Azure SQL Managed Instance are deployment options. Options B and C are wrong because they are other Azure services.

Option E is wrong because Azure Synapse Dedicated SQL Pool is a separate service for data warehousing.

Practice this question →

187

MCQmedium

A retail company uses Azure SQL Database for an order management system. The Orders table has columns: OrderID (primary key, clustered), CustomerID, OrderDate, TotalAmount. Queries frequently filter on CustomerID and OrderDate, and sort results by OrderDate in descending order. The queries also return the TotalAmount. Which indexing strategy will most improve query performance for these operations?

A.Maintain the existing clustered index on OrderID only.

B.Create a nonclustered index on (CustomerID, OrderDate DESC) INCLUDE (TotalAmount).

C.Create a nonclustered index on (OrderDate DESC) INCLUDE (CustomerID, TotalAmount).

D.Create a clustered columnstore index on the entire table.

AnswerB

This index is ordered by CustomerID then OrderDate descending, allowing efficient seeks for a specific CustomerID and range scans over OrderDate in descending order. Including TotalAmount covers the SELECT clause without needing to access the base table.

Why this answer

Option B is correct because it creates a covering nonclustered index that supports both the filter predicates (CustomerID and OrderDate) and the sort order (OrderDate DESC) while including TotalAmount as an included column to avoid key lookups. This index allows SQL Server to satisfy the query entirely from the index pages, minimizing I/O and improving performance.

Exam trap

Microsoft often tests the distinction between covering indexes and columnstore indexes, and the trap here is assuming a columnstore index is appropriate for transactional queries with filtering and sorting, when it is actually designed for large-scale analytics and data warehousing workloads.

How to eliminate wrong answers

Option A is wrong because the existing clustered index on OrderID does not support filtering on CustomerID or OrderDate, forcing a full clustered index scan. Option C is wrong because while it supports sorting on OrderDate, it does not include CustomerID as a leading key column, so filtering on CustomerID would require a scan or additional lookups. Option D is wrong because a clustered columnstore index is optimized for large-scale analytical workloads and batch processing, not for point lookups or range queries with sorting on a single table; it would degrade performance for the described transactional queries.

Practice this question →

188

Multi-Selectmedium

Which TWO are valid ways to secure data in transit for an Azure SQL Database?

Select 2 answers

A.Use the 'Encrypt connection' setting in connection strings

B.Require TLS 1.2 for client connections

C.Enable Transparent Data Encryption (TDE)

D.Configure firewall rules to allow only specific IPs

E.Use Always Encrypted

AnswersA, B

This enforces TLS encryption for the connection.

Why this answer

Options A and B are correct. TLS ensures encryption between client and server. Azure SQL Database always enforces encryption (TLS) for connections.

Option C is wrong because transparent data encryption (TDE) encrypts data at rest, not in transit. Option D is wrong because always encrypted is for column-level encryption at rest and in transit. Option E is wrong because firewall rules control access, not encryption.

Practice this question →

189

MCQmedium

A company is migrating an on-premises SQL Server database to Azure. The database uses SQL Server Integration Services (SSIS) packages for daily ETL processes. The company wants to minimize administrative overhead for patching and backup management, but needs to retain full control over instance-level configurations and support for SSIS. Which Azure SQL service should they choose?

A.Azure SQL Database

B.Azure SQL Managed Instance

C.Azure Synapse Analytics

D.Azure SQL Server on Azure Virtual Machines

AnswerB

Azure SQL Managed Instance supports SSIS and provides instance-level control with automated patching and backups, minimizing overhead.

Why this answer

Azure SQL Managed Instance is correct because it provides near 100% compatibility with on-premises SQL Server, including full support for SQL Server Integration Services (SSIS) via Azure-SSIS Integration Runtime, while offloading patching and backup management to the platform. It also allows full control over instance-level configurations such as collation, CLR, and SQL Agent jobs, which are not available in Azure SQL Database.

Exam trap

The trap here is that candidates often confuse Azure SQL Database's PaaS benefits with full SQL Server compatibility, overlooking that SSIS and instance-level configurations require Managed Instance, not the more restrictive Azure SQL Database.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database is a platform-as-a-service (PaaS) offering that does not support instance-level configurations (e.g., SQL Agent jobs, cross-database queries) and has limited SSIS support (only via elastic jobs, not native SSIS packages). Option C is wrong because Azure Synapse Analytics is a massively parallel processing (MPP) data warehouse optimized for large-scale analytics, not for transactional workloads or SSIS package execution; it lacks instance-level control and native SSIS support. Option D is wrong because Azure SQL Server on Azure Virtual Machines is an infrastructure-as-a-service (IaaS) option that requires the customer to manage patching, backups, and high availability, contradicting the requirement to minimize administrative overhead.

Practice this question →

190

MCQmedium

A company uses Azure SQL Database to store order data. The Orders table has millions of rows with columns: OrderID (primary key, clustered), CustomerID, OrderDate, Status, TotalAmount. Queries frequently filter on OrderDate and Status, and sort results by OrderDate descending. Which indexing strategy will most improve query performance for these filters and sort?

A.Create a clustered index on OrderDate

B.Create a nonclustered index on (OrderDate DESC, Status) and include TotalAmount

C.Create a nonclustered index on Status alone

D.Create a clustered columnstore index on the table

AnswerB

This composite index covers both filter columns in the correct sort order and includes the TotalAmount column, making the query fully covered without needing to access the table. This yields the best performance for the described queries.

Why this answer

Option B creates a covering nonclustered index on (OrderDate DESC, Status) that directly supports the frequent filter on OrderDate and Status and the ORDER BY OrderDate DESC. Including TotalAmount as a non-key column makes the index covering, so all needed columns come from the index without key lookups, maximizing query performance.

Exam trap

The trap here is that candidates often think a clustered index on the filter column is always best, but they overlook that the existing clustered index on OrderID is needed for primary key enforcement and that a covering nonclustered index is the optimal way to support specific query patterns without disrupting the table's physical design.

How to eliminate wrong answers

Option A is wrong because changing the clustered index from OrderID to OrderDate would break the primary key constraint and could cause fragmentation and performance issues for other queries that rely on the clustered key. Option C is wrong because an index on Status alone does not help with the OrderDate filter or the ORDER BY OrderDate DESC, requiring a separate sort operation. Option D is wrong because a clustered columnstore index is optimized for large-scale analytical scans and aggregations, not for point lookups or ordered retrieval of specific rows, and would perform poorly for this filtered, sorted query.

Practice this question →

191

MCQhard

A multinational corporation needs to deploy a relational database in Azure that supports global distribution and low-latency reads and writes across multiple regions. The data model is primarily key-value with occasional relational queries. Which Azure service should they choose?

A.Azure SQL Database with active geo-replication

B.Azure SQL Managed Instance

C.Azure Database for PostgreSQL – Hyperscale (Citus)

D.Azure Cosmos DB with PostgreSQL-compatible API

AnswerD

Supports global distribution, multi-region writes, and relational queries via the PostgreSQL API.

Why this answer

Option D is correct because Azure Cosmos DB offers multiple consistency levels and global distribution with multi-region writes, and its API for NoSQL can handle key-value data. However, it also provides a PostgreSQL-compatible API (Citus) for relational scenarios, making it the best fit for global distribution. Option A is wrong because Azure SQL Database's geo-replication supports only one writable region.

Option B is wrong because Azure SQL Managed Instance is region-bound. Option C is wrong because Azure Database for PostgreSQL with Hyperscale (Citus) is a good option but does not offer the same multi-region write capabilities as Cosmos DB, and the question emphasizes global distribution.

Practice this question →

192

MCQmedium

A company has a suite of 20 e-commerce applications, each with its own SQL Server database. The databases vary in size from 5 GB to 100 GB and have unpredictable usage patterns with bursty peaks. The company wants to migrate to Azure SQL Database to benefit from built-in high availability and automatic backups. They need to minimize costs by only paying for the resources each database actually uses, and they want to avoid over-provisioning for peak loads. Which Azure SQL Database deployment option should they choose?

A.Azure SQL Database Elastic Pool

B.Azure SQL Database (single database) with Serverless compute tier

C.Azure SQL Managed Instance

D.Azure SQL Database Hyperscale

AnswerA

Correct. Elastic pools allow multiple databases to share a set of resources, providing cost-effective performance management for databases with varying and unpredictable usage.

Why this answer

Azure SQL Database Elastic Pool is the correct choice because it allows multiple databases to share a fixed pool of resources (DTUs or vCores), enabling cost efficiency by pooling and reallocating resources across databases with unpredictable, bursty usage patterns. This avoids over-provisioning for peak loads while still providing built-in high availability and automatic backups, as each database in the pool benefits from these features without needing individual resource reservations.

Exam trap

The trap here is that candidates confuse the Serverless compute tier (which auto-pauses for cost savings on a single database) with the Elastic Pool (which shares resources across multiple databases), leading them to choose Serverless for cost minimization without recognizing the need for resource pooling across 20 databases.

How to eliminate wrong answers

Option B is wrong because the Serverless compute tier is designed for a single database with intermittent usage, not for pooling resources across 20 databases; it would require separate provisioning per database, leading to higher costs and no resource sharing. Option C is wrong because Azure SQL Managed Instance is a full SQL Server instance intended for lift-and-shift migrations with instance-level features like SQL Agent, not for cost-optimized multi-database scenarios with bursty peaks. Option D is wrong because Hyperscale is built for very large databases (over 1 TB) with high throughput and rapid scaling, not for pooling multiple smaller databases (5-100 GB) to minimize costs.

Practice this question →

193

MCQeasy

A small business needs a cost-effective relational database for a new web application. The workload is light and predictable. They want to minimize administrative overhead. Which Azure service should they choose?

A.Azure Database for PostgreSQL

B.SQL Server on Azure Virtual Machines

C.Azure SQL Database (provisioned DTU)

D.Azure SQL Database serverless

AnswerD

Serverless automatically scales and pauses, ideal for light workloads.

Why this answer

Option A is correct because Azure SQL Database serverless is designed for intermittent, unpredictable workloads and automatically pauses when idle, reducing cost. Option B is wrong because Azure SQL Database provisioned is for steady-state workloads. Option C is wrong because SQL Server on Azure VMs is IaaS and requires management.

Option D is wrong because Azure Database for PostgreSQL is fully managed but PostgreSQL-specific; Azure SQL Database serverless is more cost-effective for light relational workloads.

Practice this question →

194

MCQmedium

A company plans to migrate a 2-TB on-premises SQL Server database to Azure. The database uses SQL Server Agent jobs for scheduled maintenance and requires automatic failover across Azure regions. The company wants a fully managed service with minimal application changes. Which Azure SQL service should they choose?

A.Azure SQL Database

B.Azure SQL Managed Instance

C.SQL Server on Azure Virtual Machines

D.Azure Synapse Analytics

AnswerB

SQL Managed Instance supports SQL Server Agent, can scale up to 16 TB, and provides auto-failover groups for cross-region high availability.

Why this answer

Azure SQL Managed Instance is correct because it provides near 100% compatibility with SQL Server, including support for SQL Server Agent jobs, and offers automatic failover across Azure regions via failover groups. It is a fully managed service that requires minimal application changes, unlike Azure SQL Database which lacks SQL Server Agent and has limited cross-region failover capabilities.

Exam trap

The trap here is that candidates often choose Azure SQL Database because it is the most well-known fully managed service, overlooking the specific requirement for SQL Server Agent jobs and automatic cross-region failover, which Managed Instance uniquely supports.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database does not support SQL Server Agent jobs and has limited compatibility with on-premises SQL Server features, requiring application changes. Option C is wrong because SQL Server on Azure Virtual Machines is not a fully managed service; it requires manual management of the OS and SQL Server, including high availability setup. Option D is wrong because Azure Synapse Analytics is designed for large-scale data warehousing and analytics, not for transactional OLTP workloads with SQL Server Agent jobs and automatic failover.

Practice this question →

195

Drag & Dropmedium

Drag and drop the steps to load data into Azure Synapse Analytics using PolyBase in the correct order.

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

Steps

Order

Why this order

PolyBase loading involves defining external data source, file format, external table, then using CTAS to move data into the warehouse.

Practice this question →

196

MCQmedium

A company runs a web application on Azure SQL Database that experiences unpredictable spikes in traffic. They want to automatically adjust compute resources based on demand without manual intervention and without over-provisioning. Which Azure SQL Database feature should they use?

A.Serverless compute tier

B.Active geo-replication

C.Hyperscale service tier

D.Read scale-out

AnswerA

Correct - Serverless automatically scales compute on-demand and pauses during inactivity, matching the need for handling unpredictable spikes without manual intervention.

Why this answer

The Serverless compute tier for Azure SQL Database automatically scales compute resources based on workload demand, pausing databases during idle periods and resuming them when traffic spikes occur. This eliminates the need for manual intervention and prevents over-provisioning by charging only for the compute used per second, making it ideal for unpredictable traffic patterns.

Exam trap

The trap here is that candidates confuse the Hyperscale service tier's storage scalability with compute auto-scaling, but Hyperscale requires manual vCore adjustment and does not support auto-pause, whereas Serverless is specifically designed for unpredictable, intermittent workloads with automatic compute scaling.

How to eliminate wrong answers

Option B (Active geo-replication) is wrong because it focuses on disaster recovery and read-scale availability by replicating data to a secondary region, not on dynamic compute scaling based on demand. Option C (Hyperscale service tier) is wrong because it provides high scalability for storage and fast backup/restore but requires manual scaling of compute resources (vCores) and does not auto-pause or auto-scale compute like Serverless. Option D (Read scale-out) is wrong because it offloads read-only queries to a secondary replica for performance, but it does not automatically adjust compute resources or handle unpredictable traffic spikes without manual configuration.

Practice this question →

197

MCQmedium

A company has an on-premises SQL Server 2016 database that uses SQL Server Agent jobs and cross-database queries across multiple databases. They want to migrate to Azure with minimal application changes, requiring native VNet integration and near 100% compatibility with the on-premises SQL Server instance. Which Azure deployment option should they choose?

A.Azure SQL Database single database

B.Azure SQL Database elastic pool

C.Azure SQL Managed Instance

D.SQL Server on Azure Virtual Machines

AnswerC

Correct. Azure SQL Managed Instance is designed for lift-and-shift migrations, offering high compatibility, SQL Server Agent, cross-database queries, and native VNet integration, all while being a platform-as-a-service (PaaS) offering.

Why this answer

Azure SQL Managed Instance is correct because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs and cross-database queries, while offering native VNet integration. This minimizes application changes during migration, unlike other Azure SQL options that lack these features.

Exam trap

The trap here is that candidates often choose SQL Server on Azure Virtual Machines (Option D) thinking it offers the highest compatibility, but they overlook that Azure SQL Managed Instance provides the same compatibility with less management overhead and native VNet integration, making it the optimal choice for minimal application changes.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database single database does not support SQL Server Agent jobs or cross-database queries across multiple databases, requiring significant application rewrites. Option B is wrong because Azure SQL Database elastic pool also lacks SQL Server Agent and cross-database query support, and is designed for resource sharing among single databases, not for compatibility with on-premises features. Option D is wrong because SQL Server on Azure Virtual Machines requires manual management of VNet integration and does not provide the same level of managed service or near 100% compatibility without additional configuration, though it can support these features if properly set up.

Practice this question →

198

MCQhard

A company's application uses Microsoft SQL Server with multiple databases that need to run complex queries joining tables across databases. They are migrating to Azure and need a fully managed relational database service with high availability, automated backups, and minimal management overhead. They do not need a separate SQL Server installation and want to avoid managing VMs. Which Azure deployment option should they choose?

A.Azure SQL Database single database

B.Azure SQL Managed Instance

C.SQL Server on Azure Virtual Machines

D.Azure SQL Database elastic pool

AnswerB

Azure SQL Managed Instance supports cross-database queries using linked servers, provides built-in high availability, automatic backups, and is fully managed. It offers the broadest SQL Server compatibility without requiring VM management.

Why this answer

Azure SQL Managed Instance is the correct choice because it provides near-100% compatibility with on-premises SQL Server, including support for cross-database queries and linked servers, while being a fully managed platform-as-a-service (PaaS) offering. It eliminates the need to manage VMs or a separate SQL Server installation, and it includes built-in high availability (99.99% SLA) and automated backups, meeting all stated requirements.

Exam trap

The trap here is that candidates often confuse Azure SQL Database elastic pool with Managed Instance, assuming elastic pools support cross-database queries, but elastic pools only manage resource allocation for single databases and do not provide the instance-level features needed for cross-database joins.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database single database does not support cross-database queries or linked servers; it is designed for isolated databases and requires elastic query or external tools for cross-database joins, which adds complexity. Option C is wrong because SQL Server on Azure Virtual Machines is an infrastructure-as-a-service (IaaS) option that requires managing VMs, patching, and SQL Server installation, contradicting the need for minimal management overhead and a fully managed service. Option D is wrong because Azure SQL Database elastic pool is a resource-sharing model for multiple single databases within the same logical server, but it inherits the same cross-database query limitations as single databases and does not enable native cross-database joins.

Practice this question →

199

MCQmedium

A company is migrating a large on-premises SQL Server database to Azure. They require high availability with automatic failover and read-scale for reporting workloads. Which Azure service should they use?

A.Azure SQL Database Hyperscale tier

B.Azure Database for PostgreSQL with geo-redundant backup

C.SQL Server on Azure Virtual Machines with Always On availability groups

D.Azure SQL Database Business Critical tier with failover groups

AnswerD

Business Critical provides built-in high availability and failover groups enable automatic failover with readable secondaries.

Why this answer

Option B is correct because Azure SQL Database with Business Critical tier and failover group provides automatic failover and read-scale. Option A is wrong because Hyperscale is for very large databases but not primarily for high availability with read-scale. Option C is wrong because SQL Server on Azure VMs requires manual configuration for high availability.

Option D is wrong because Azure Database for PostgreSQL is not a SQL Server migration target.

Practice this question →

200

Multi-Selecthard

Which THREE considerations are important when choosing between Azure SQL Database and Azure SQL Managed Instance?

Select 3 answers

A.Need for SQL Server Agent

B.Database size greater than 4 TB

C.Requirement for elastic pools

D.Need for automated backups

E.Lift-and-shift migration with minimal changes

AnswersA, B, E

SQL Managed Instance supports SQL Server Agent, while SQL Database uses elastic jobs.

Why this answer

Option A is correct because SQL Server Agent is fully supported in Azure SQL Managed Instance, enabling native job scheduling and automation, whereas Azure SQL Database does not include SQL Server Agent and requires alternative solutions like Elastic Jobs or Azure Automation. Option B is correct because Azure SQL Managed Instance supports databases up to 16 TB, while Azure SQL Database (single database) has a maximum size of 4 TB for the Hyperscale service tier, making Managed Instance necessary for databases exceeding 4 TB in non-Hyperscale tiers. Option E is correct because Azure SQL Managed Instance provides near-100% compatibility with on-premises SQL Server, allowing lift-and-shift migrations with minimal or no application changes, whereas Azure SQL Database often requires schema and code modifications due to differences in features like CLR, Service Broker, or cross-database queries.

Exam trap

The trap here is that candidates often assume automated backups are unique to one service, but both Azure SQL Database and Azure SQL Managed Instance include automated backups by default, so this is not a valid differentiator.

Practice this question →

201

MCQmedium

A company uses Azure SQL Database for a customer management system. The Customers table has columns: CustomerID (int, primary key), FullName (varchar(100)), Email (varchar(200)), SignUpDate (date), LastLoginDate (date). Queries frequently filter on LastLoginDate to find customers who have not logged in for over a year for a promotional campaign. The table has 10 million rows. Which type of index should they create to optimize these queries?

A.Clustered index on CustomerID

B.Non-clustered index on LastLoginDate

C.Non-clustered index on FullName

D.Columnstore index on SignUpDate and LastLoginDate

AnswerB

A non-clustered index on LastLoginDate enables the database to efficiently seek rows with a specific date range, significantly speeding up the query.

Why this answer

A non-clustered index on LastLoginDate allows the query to quickly locate rows where LastLoginDate is older than one year without scanning the entire 10-million-row table. Azure SQL Database uses B-tree structures for non-clustered indexes, enabling efficient range scans and key lookups for the filtered rows. This directly supports the promotional campaign query pattern.

Exam trap

The trap here is that candidates often choose a clustered index on the primary key by default, failing to recognize that the query predicate (LastLoginDate) is not the clustering key, so the index cannot be used to efficiently filter the data.

How to eliminate wrong answers

Option A is wrong because a clustered index on CustomerID organizes the table by CustomerID, which does not help filter on LastLoginDate; the query would still require a full table scan to evaluate the date condition. Option C is wrong because an index on FullName is irrelevant to filtering on LastLoginDate and would not improve query performance for this specific predicate. Option D is wrong because a columnstore index is optimized for large-scale analytical aggregations and data warehousing workloads, not for point lookups or narrow range queries on a single date column in an OLTP table.

Practice this question →

202

MCQmedium

A software company is migrating an on-premises SQL Server database to Azure SQL Database. The database currently uses SQL Server Agent jobs for regular maintenance tasks. The company wants to minimize code changes during migration. Which Azure SQL Database feature should they use to replace SQL Server Agent jobs?

A.Azure Functions

B.Azure Automation

C.Elastic Jobs

D.SQL Server Agent (available in Azure SQL Database)

AnswerC

Elastic Jobs allow scheduling T-SQL jobs across databases, similar to SQL Agent.

Why this answer

Option B is correct because Elastic Jobs (preview) in Azure SQL Database can replace SQL Server Agent jobs for scheduling tasks. Option A is wrong because Azure Automation can run scripts but is not a direct replacement. Option C is wrong because SQL Server Agent is not available in Azure SQL Database.

Option D is wrong because Azure Functions can run code but requires more development effort.

Practice this question →

203

MCQeasy

A company runs individual Azure SQL Databases for each of its departments. The databases experience varying usage patterns; sometimes one database is idle while another is heavily loaded. The company wants to pool resources to reduce cost while ensuring each database gets resources when needed. Which Azure feature should they use?

A.Azure SQL Database single database with provisioned DTUs

B.Azure SQL Database elastic pool

C.Azure SQL Managed Instance

D.SQL Server on Azure Virtual Machines

AnswerB

An elastic pool allows multiple databases to share a common pool of resources. This is cost-effective for databases with fluctuating usage, as idle databases free up resources for active ones.

Why this answer

Azure SQL Database elastic pools are designed to share resources (eDTUs or eVCores) across multiple databases with varying usage patterns. This allows idle databases to contribute their unused capacity to heavily loaded ones, reducing overall cost while ensuring each database gets resources when needed.

Exam trap

The trap here is that candidates might confuse elastic pools with single databases or managed instances, thinking that 'pooling' means using a single large instance rather than a shared resource model across multiple databases.

How to eliminate wrong answers

Option A is wrong because a single database with provisioned DTUs allocates fixed resources to one database, which cannot be shared across departments and would waste cost when idle. Option C is wrong because Azure SQL Managed Instance is a fully managed instance of SQL Server with fixed resources per instance, not designed for pooling resources across multiple databases with variable loads. Option D is wrong because SQL Server on Azure Virtual Machines requires manual management of resources and licensing, and does not offer built-in elastic pooling across databases.

Practice this question →

204

MCQmedium

A healthcare organization uses Azure SQL Database to store patient records. To comply with HIPAA regulations, they need to encrypt sensitive columns (e.g., Social Security numbers) at rest and control access to the encryption keys. Which feature should they use?

A.Dynamic Data Masking

B.Row-Level Security

C.Always Encrypted

D.Transparent Data Encryption (TDE)

AnswerC

Always Encrypted encrypts specific columns and allows client-side key management.

Why this answer

Option A is correct because Always Encrypted with secure enclaves protects sensitive columns at rest and allows client-side key management. Option B is wrong because Transparent Data Encryption encrypts the entire database but does not allow column-level key control. Option C is wrong because Dynamic Data Masking obfuscates data but does not encrypt it.

Option D is wrong because Row-Level Security restricts row access but does not encrypt columns.

Practice this question →

205

MCQeasy

A company is migrating a relational database to Azure SQL Database. They anticipate that the amount of stored data will grow significantly over time, but the compute requirements (CPU and memory) will remain relatively stable. Which purchasing model should they choose to allow independent scaling of storage and compute?

A.DTU-based purchasing model

B.vCore-based purchasing model

C.Serverless compute tier

D.Hyperscale service tier

AnswerB

vCore-based model separates compute and storage billing, allowing storage to scale independently of compute.

Why this answer

The vCore-based purchasing model separates compute and storage costs, allowing you to scale storage independently without changing compute resources. This matches the scenario where data grows but compute requirements remain stable, as you can increase storage capacity without upgrading CPU or memory.

Exam trap

The trap here is confusing purchasing models (DTU vs. vCore) with service tiers (Hyperscale) or compute options (Serverless), leading candidates to pick Hyperscale or Serverless when the question specifically asks for a purchasing model that allows independent scaling of storage and compute.

How to eliminate wrong answers

Option A is wrong because the DTU-based model bundles compute, storage, and I/O into a fixed package, so scaling storage also forces a change in compute resources. Option C is wrong because the Serverless compute tier is a compute scaling option within the vCore model, not a separate purchasing model; it auto-pauses compute but does not allow independent storage scaling. Option D is wrong because Hyperscale is a service tier (architecture) for large databases, not a purchasing model; it uses vCore pricing but is designed for high scalability and storage up to 100 TB, not for independent scaling of storage and compute in the general sense described.

Practice this question →

206

MCQmedium

A retail company runs an e-commerce platform on a single SQL Server database. The database experiences unpredictable spikes in read-only reporting queries that degrade transactional performance. They want to migrate to Azure SQL Database and isolate the reporting workload while ensuring it sees the most current data. Which feature should they enable?

A.Read Scale-out (using a readable secondary)

B.Elastic Pool

C.Geo-replication

D.SQL Server Agent

AnswerA

This feature creates a read-only replica that can serve reporting queries with minimal latency, isolating them from the primary's transactional workload.

Why this answer

Read Scale-out allows you to offload read-only reporting queries to a readable secondary replica, isolating them from the primary transactional workload. This ensures reporting queries see the most current data because the secondary replica is kept transactionally consistent with the primary via synchronous replication. It directly addresses the unpredictable spikes in read-only queries without degrading transactional performance.

Exam trap

The trap here is that candidates often confuse Geo-replication (which provides a readable secondary but with asynchronous replication and potential data lag) with Read Scale-out, which uses synchronous replication for current data, or mistakenly think Elastic Pools can isolate workloads within a single database.

How to eliminate wrong answers

Option B (Elastic Pool) is wrong because it is designed for managing and sharing resources among multiple databases with varying usage patterns, not for isolating read-only workloads from a single database. Option C (Geo-replication) is wrong because it provides asynchronous replication for disaster recovery and geographic distribution, not for real-time read-only workload isolation with current data visibility. Option D (SQL Server Agent) is wrong because it is a scheduling and automation service for jobs like backups and maintenance, not a feature for read-only workload isolation.

Practice this question →

207

MCQhard

A company uses Azure SQL Database with active geo-replication for disaster recovery. During a regional outage, the secondary database is promoted. After the primary region recovers, what is the best practice to re-establish geo-replication?

A.Delete the old primary database and create a new geo-replication

B.Add the old primary as a secondary to the new primary

C.Reverse the geo-replication direction automatically

D.Perform a planned failover to make the old primary the primary again

AnswerD

Best practice to restore original configuration.

Why this answer

Option D is correct because you should fail back to the original primary using planned failover, then reconfigure geo-replication. Option A is wrong because reversing geo-replication automatically is not supported. Option B is wrong because deleting the old primary loses data.

Option C is wrong because you cannot simply add the old primary as a secondary without resolution.

Practice this question →

208

Multi-Selecteasy

Which TWO are benefits of using Azure SQL Database elastic pools?

Select 2 answers

A.Predictable pricing for a group of databases

B.Resource sharing across multiple databases

C.Unlimited storage per database

D.Isolated performance for each database

E.Support for databases over 1 TB each

AnswersA, B

Fixed pool cost.

Why this answer

Option B is correct because elastic pools allow sharing of resources across multiple databases. Option C is correct because elastic pools provide predictable pricing for a group of databases. Option A is wrong because elastic pools do not provide unlimited storage.

Option D is wrong because elastic pools are not for single large databases. Option E is wrong because elastic pools are for multiple databases, not a single database.

Practice this question →

209

MCQhard

A company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database has a large fact table that is partitioned by date (monthly partitions) to improve query performance and simplify data archiving. The company wants to maintain the same partitioning strategy in Azure to avoid rewriting queries. Which feature in Azure SQL Managed Instance should they use to achieve this?

A.Table partitioning with partition functions and schemes

B.Sharding across multiple Azure SQL Managed Instances

C.Index partitioning only

D.Federated tables

AnswerA

Correct. Azure SQL Managed Instance supports the same table partitioning as SQL Server, allowing you to define partitions on a table using a partition function and scheme.

Why this answer

Azure SQL Managed Instance supports table partitioning using partition functions and partition schemes, which is the same feature available in SQL Server. This allows you to define monthly partitions on the fact table using a date column, preserving the existing partitioning strategy and query logic without modification. The partition function maps rows to partitions based on the date boundary values, and the partition scheme assigns those partitions to filegroups.

Exam trap

The trap here is that candidates confuse table partitioning with sharding or index partitioning, assuming any form of data distribution will work, but only table partitioning with partition functions and schemes preserves the exact same structure and query semantics in Azure SQL Managed Instance.

How to eliminate wrong answers

Option B is wrong because sharding distributes data across multiple databases or instances, which would require rewriting queries and does not maintain the same partitioning strategy within a single database. Option C is wrong because index partitioning only applies to indexes, not to the table itself, and cannot achieve the goal of partitioning the fact table by date for query performance and archiving. Option D is wrong because federated tables are a legacy feature in SQL Server (deprecated) and are not supported in Azure SQL Managed Instance; they involve distributed queries across remote servers, not native table partitioning.

Practice this question →

210

MCQeasy

The exhibit shows a firewall rule configuration for an Azure SQL Server. A developer reports that a client app running on IP 10.0.0.5 cannot connect to the database. What is the most likely cause?

A.The firewall rules are not in valid JSON format

B.The first rule only allows a single IP (10.0.0.0), not the entire subnet

C.The second rule overrides the first rule

D.The firewall rule uses IPv4 addresses, but the client is using IPv6

AnswerB

The first rule specifies start and end IP both 10.0.0.0, so only that exact IP is allowed; 10.0.0.5 is blocked.

Why this answer

Option B is correct because the first rule allows only IP 10.0.0.0 (single IP), not the subnet. Option A is wrong because Azure SQL supports both IPv4 and IPv6. Option C is wrong because the rules are valid JSON.

Option D is wrong because the client IP is within the 192.168.1.0/24 range but the first rule is more restrictive.

Practice this question →

211

MCQmedium

A company runs a mission-critical SQL Server database on-premises. They plan to migrate to Azure SQL Database and need to choose the appropriate service tier. The database is currently 500 GB and is expected to grow to 8 TB within two years. The workload is read-heavy with many concurrent users, and they require fast scaling of compute resources without significant downtime. Which Azure SQL Database service tier should they choose?

A.General Purpose

B.Business Critical

C.Hyperscale

D.Serverless

AnswerC

Hyperscale tier supports databases up to 100 TB, allows fast scaling of compute resources with minimal downtime, and is optimized for read-heavy workloads with high concurrency. It is the best fit for databases that exceed 4 TB and require rapid scaling.

Why this answer

Hyperscale is the correct choice because it supports databases up to 100 TB, far exceeding the expected 8 TB growth, and provides fast scaling of compute resources without downtime by using a distributed architecture with separate compute and storage nodes. Its read-heavy workload with many concurrent users benefits from Hyperscale's multiple readable replicas and buffer pool extension, ensuring high performance and availability.

Exam trap

The trap here is that candidates often confuse the 4 TB limit of General Purpose and Business Critical with the 100 TB limit of Hyperscale, or assume that Serverless is suitable for any workload that needs scaling, ignoring its auto-pausing behavior and lack of support for high concurrency and consistent performance.

How to eliminate wrong answers

Option A is wrong because General Purpose has a maximum database size of 4 TB, which cannot accommodate the expected growth to 8 TB, and its compute scaling requires downtime. Option B is wrong because Business Critical also has a 4 TB size limit and, while offering higher performance, does not support the required 8 TB growth or fast compute scaling without downtime. Option D is wrong because Serverless is designed for intermittent, unpredictable workloads with auto-pausing, not for a mission-critical, read-heavy, high-concurrency workload that requires consistent performance and fast scaling without downtime.

Practice this question →

212

MCQhard

Your company uses Azure SQL Database to power a global application. You need to ensure that users in Europe and Asia have low-latency read access to product data, while writes are synchronized across all regions. What should you configure?

A.Use Azure Traffic Manager to route users to the nearest Azure SQL Database instance.

B.Migrate to Azure Cosmos DB for multi-region writes.

C.Create a failover group that includes all regions.

D.Configure Active Geo-Replication with readable secondaries in Europe and Asia.

AnswerD

Active Geo-Replication provides readable secondaries for low-latency reads.

Why this answer

Active Geo-Replication for Azure SQL Database allows you to configure readable secondary replicas in different Azure regions. This provides low-latency read access for users in Europe and Asia by directing their read traffic to the nearest secondary, while writes are synchronized asynchronously to all secondaries, ensuring data consistency across regions.

Exam trap

The trap here is that candidates may confuse failover groups (which provide a single readable secondary for disaster recovery) with Active Geo-Replication (which supports multiple readable secondaries for distributed read scaling), or mistakenly think Traffic Manager alone can solve the read latency issue without database-level replication.

How to eliminate wrong answers

Option A is wrong because Azure Traffic Manager is a DNS-based traffic load balancer that routes users to endpoints, but it does not provide the underlying database replication or readable secondaries needed for low-latency reads and synchronized writes. Option B is wrong because migrating to Azure Cosmos DB is unnecessary; the requirement is for relational data (Azure SQL Database), and Cosmos DB is a NoSQL database, not a relational solution. Option C is wrong because a failover group is designed for high availability and disaster recovery, not for providing low-latency read access across multiple regions; it uses a single readable secondary and does not support multiple readable secondaries for distributed read workloads.

Practice this question →

213

MCQeasy

You need to migrate an on-premises SQL Server database to Azure SQL Managed Instance with minimal downtime. Which tool should you use?

A.Azure Data Factory

B.SQL Server Integration Services (SSIS)

C.BACPAC export and import

D.Azure Database Migration Service (DMS)

AnswerD

DMS supports online migrations with minimal downtime.

Why this answer

Option A is correct because Azure DMS supports online migration with minimal downtime. Option B is wrong because BACPAC is for offline export/import. Option C is wrong because SSIS is for integration.

Option D is wrong because Azure Data Factory is for data movement but not optimized for online migration.

Practice this question →

214

MCQhard

A company needs to migrate 10 on-premises SQL Server databases (each 50–200 GB) to Azure. The databases frequently run cross-database queries using three-part names (e.g., DB1.dbo.table) and rely on SQL Server Agent for maintenance tasks. They want to minimize management overhead and share resources across databases to reduce costs. Which Azure SQL deployment option should they choose?

A.Azure SQL Database elastic pool

B.Azure SQL Managed Instance

C.SQL Server on Azure Virtual Machine

D.Azure SQL Database single database

AnswerB

Managed Instance supports cross-database queries, SQL Server Agent, and allows multiple databases in one instance sharing resources.

Why this answer

Azure SQL Managed Instance is correct because it provides native support for cross-database queries using three-part names (e.g., DB1.dbo.table) and SQL Server Agent for maintenance tasks, which are critical requirements. It also offers a fully managed platform that minimizes management overhead while allowing resource sharing across databases within the instance, reducing costs compared to single databases or VMs.

Exam trap

The trap here is that candidates often choose Azure SQL Database elastic pool (Option A) because they think it supports cross-database queries and SQL Agent, but it actually lacks native three-part name support and SQL Agent, which are only available in Managed Instance.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database elastic pool supports cross-database queries only via elastic query (which requires external data sources and is not native three-part name support) and does not include SQL Server Agent, so it cannot meet the maintenance task requirement. Option C is wrong because SQL Server on Azure Virtual Machine requires full management of the OS and SQL Server (patching, backups, HA), increasing management overhead, and does not inherently share resources across databases to reduce costs like a managed instance does. Option D is wrong because Azure SQL Database single database does not support cross-database queries using three-part names (it requires elastic query or external tables) and lacks SQL Server Agent, making it unsuitable for the stated needs.

Practice this question →

215

MCQmedium

A financial services company runs a single SQL Server database that is 6 TB in size and handles a high volume of concurrent transactions. The database needs to support near real-time analytics without impacting OLTP performance. The company wants to migrate to Azure SQL Database and requires fast scale-out for read workloads, as well as the ability to independently scale compute and storage. Which Azure SQL Database service tier should they choose?

A.Serverless

B.Hyperscale

C.Business Critical

D.General Purpose

AnswerB

Hyperscale supports databases up to 100 TB, allows independent scaling of compute and storage, and provides up to four readable replicas to offload reporting and analytics workloads.

Why this answer

Hyperscale is the correct choice because it is designed for databases up to 100 TB, supports high-volume concurrent transactions, and provides near real-time read scale-out via named replicas that offload read workloads without affecting OLTP performance. It also allows independent scaling of compute (vCores) and storage (auto-scaled), meeting the company's requirements for fast scale-out and decoupled resources.

Exam trap

The trap here is that candidates may confuse Business Critical's high availability features with read scale-out, but Business Critical does not provide dedicated read replicas for analytics workloads, and its storage is not independently scalable from compute.

How to eliminate wrong answers

Option A is wrong because Serverless is for intermittent, unpredictable workloads with auto-pause and auto-scale compute, not for high-volume concurrent transactions requiring near real-time analytics and fast scale-out. Option C is wrong because Business Critical provides high availability and low latency via Always On Availability Groups but does not support independent scaling of compute and storage (storage is tied to compute) and lacks the read scale-out architecture of Hyperscale. Option D is wrong because General Purpose is a balanced tier for typical workloads but does not support fast scale-out for read workloads (no named replicas) and has a maximum database size of 4 TB, which is insufficient for a 6 TB database.

Practice this question →

216

Multi-Selecthard

A company plans to migrate an on-premises SQL Server database to Azure. The database relies on SQL Agent jobs for nightly maintenance, uses Service Broker for asynchronous messaging, and requires high CPU and memory resources. The company wants to minimize application code changes. Which two Azure SQL deployment options meet all these requirements? (Choose two.)

Select 2 answers

A.Azure SQL Database Single Database

B.Azure SQL Managed Instance

C.SQL Server on Azure Virtual Machine

D.Azure SQL Database Elastic Pool

AnswersB, C

Managed Instance provides built-in support for SQL Agent and Service Broker, and allows scaling to meet high resource requirements with minimal code changes.

Why this answer

Azure SQL Managed Instance is correct because it provides near-100% compatibility with on-premises SQL Server, including SQL Agent jobs and Service Broker, while offering high CPU and memory resources. This minimizes application code changes by preserving existing features and configurations.

Exam trap

The trap here is that candidates assume Azure SQL Database Single Database or Elastic Pool can support all SQL Server features, but they lack SQL Agent and Service Broker, forcing code changes that the question explicitly wants to minimize.

Practice this question →

217

MCQmedium

A company uses Azure SQL Database for an e-commerce system. The Orders table has millions of rows with a clustered index on OrderID (the primary key). Queries that filter on OrderDate and CustomerID to find recent orders for a specific customer are very slow. Which indexing strategy will most improve the performance of these queries?

A.Create a nonclustered index on OrderID only

B.Create separate nonclustered indexes on OrderDate and CustomerID

C.Create a nonclustered composite index on (OrderDate, CustomerID)

D.Create a clustered index on CustomerID instead of OrderID

AnswerC

A composite index on both columns allows the database to find rows matching both filter conditions in a single index seek. This is the most efficient strategy for queries that filter on multiple columns together.

Why this answer

The query filters on both OrderDate and CustomerID, so a composite nonclustered index on (OrderDate, CustomerID) allows SQL Server to perform a single index seek to locate the matching rows without touching the clustered index until the final key lookup. This dramatically reduces I/O compared to scanning the entire clustered index or using multiple separate indexes.

Exam trap

The trap here is that candidates often think separate indexes on each filter column are sufficient, not realizing that a composite index is far more efficient for queries that filter on multiple columns together, because it avoids the need for index intersection or multiple lookups.

How to eliminate wrong answers

Option A is wrong because creating a nonclustered index on OrderID only does not help filter on OrderDate or CustomerID; the query would still need to scan the clustered index. Option B is wrong because separate indexes on OrderDate and CustomerID would require SQL Server to choose one index for a seek and then filter the other column in a bookmark lookup, or perform an index intersection, both of which are less efficient than a single composite index that covers both filter columns. Option D is wrong because changing the clustered index to CustomerID would reorder the entire table by CustomerID, which could improve queries filtering by CustomerID alone but would not help the OrderDate filter and would disrupt the primary key's order, potentially harming other queries that rely on OrderID ordering.

Practice this question →

218

MCQmedium

A company runs an e-commerce application backed by an on-premises SQL Server database. They plan to migrate to Azure SQL Database and require automatic failover across two Azure regions for disaster recovery. The application must continue to connect using the same connection string after a failover, with no code changes. Which feature should they implement?

A.Active Geo-Replication

B.Elastic pools

C.Failover groups

D.SQL Server on Azure Virtual Machine with Always On Availability Groups

AnswerC

Failover groups enable automatic asynchronous replication and automatic failover across regions. The application connects to a listener endpoint that remains unchanged after failover, requiring no code changes.

Why this answer

Failover groups (Option C) enable automatic, geo-redundant failover across two Azure regions while providing a single read-write listener endpoint that remains unchanged after failover. This ensures the application can continue using the same connection string without any code modifications, meeting the stated requirement for disaster recovery with zero application changes.

Exam trap

The trap here is that candidates often confuse Active Geo-Replication with Failover groups, not realizing that only Failover groups provide a single, unchanged connection string endpoint for automatic failover, while Active Geo-Replication requires manual connection string updates.

How to eliminate wrong answers

Option A is wrong because Active Geo-Replication provides asynchronous replication but does not offer a single, unchanged connection string endpoint; the application would need to manually update the connection string to point to the new primary after failover. Option B is wrong because Elastic pools are a resource management feature for scaling multiple databases within a single server, not a disaster recovery or failover mechanism. Option D is wrong because SQL Server on Azure Virtual Machine with Always On Availability Groups requires managing a listener endpoint and typically involves more complex configuration; it does not provide the same seamless, automatic connection string continuity as Failover groups in Azure SQL Database.

Practice this question →

219

MCQmedium

A company plans to migrate a 500 GB SQL Server database from on-premises to Azure SQL Database. They require minimal downtime during the migration. Which approach should they use?

A.Export a BACPAC file and import to Azure SQL Database

B.Use Azure Database Migration Service with online migration mode

C.Use Azure Site Recovery to replicate the database

D.Create a full database backup and restore to Azure SQL Database

AnswerB

Correct. Online migration via DMS synchronizes changes in near real-time, allowing the source to stay online until cutover, minimizing downtime.

Why this answer

Azure Database Migration Service (DMS) with online migration mode is the correct approach because it supports minimal-downtime migrations by continuously replicating ongoing changes from the source SQL Server to Azure SQL Database using the transactional replication technology. This allows the source database to remain operational during the migration, and only a brief cutover is needed at the end to switch applications to the target.

Exam trap

The trap here is that candidates often confuse offline backup/restore or BACPAC methods (which are simpler but cause downtime) with the online migration capability of DMS, assuming any Azure tool can achieve minimal downtime without understanding the underlying replication mechanism.

How to eliminate wrong answers

Option A is wrong because exporting a BACPAC file is an offline process that requires the database to be idle or in read-only mode, causing significant downtime for a 500 GB database. Option C is wrong because Azure Site Recovery is designed for disaster recovery of entire VMs or workloads, not for granular database migration with schema and data consistency, and it cannot perform a live migration of a SQL Server database to Azure SQL Database. Option D is wrong because restoring a full database backup to Azure SQL Database is an offline operation that requires the source database to be taken offline or in read-only mode during the backup and restore, resulting in substantial downtime.

Practice this question →

220

MCQmedium

A company runs a critical OLTP application on a single Azure SQL Database in the West US region. They need to ensure high availability with automatic failover to a secondary region in case of a regional outage. The solution must minimize data loss and allow the secondary database to be readable for reporting queries when the primary is healthy. Which feature should they enable?

A.Failover groups with automatic failover policy

B.Transparent Data Encryption (TDE)

C.Long-term backup retention (LTR)

D.Auto-tuning

AnswerA

Correct. Failover groups provide geo-replication with a readable secondary and can be configured for automatic failover, meeting all requirements.

Why this answer

Failover groups with an automatic failover policy enable Azure SQL Database to replicate data synchronously to a secondary region, ensuring zero data loss during a planned failover and minimal data loss during an unplanned failover. The secondary database is readable for reporting queries when the primary is healthy, meeting both the high availability and read-scale requirements. This feature is specifically designed for cross-region disaster recovery with automatic failover, unlike other options that address security, backup retention, or performance tuning.

Exam trap

The trap here is that candidates may confuse high-availability features within a single region (like zone-redundant configuration) with cross-region disaster recovery, or assume that backup retention or encryption can provide failover capabilities.

How to eliminate wrong answers

Option B is wrong because Transparent Data Encryption (TDE) provides encryption at rest for the database but does not offer any replication, failover, or read-scale capabilities. Option C is wrong because Long-term backup retention (LTR) allows you to keep full backups for up to 10 years for compliance, but it does not provide automatic failover or a readable secondary database for reporting. Option D is wrong because Auto-tuning uses machine learning to optimize query performance and index management, but it has no role in high availability or disaster recovery.

Practice this question →

221

MCQmedium

A company uses Azure SQL Database for an HR system. The Employees table has a clustered index on EmployeeID. Queries frequently filter on DepartmentID and LastName and also retrieve the Salary column. The table contains over a million rows. Which index strategy will most improve query performance for these filters?

A.A: Create a nonclustered index on (DepartmentID, LastName) INCLUDE (Salary)

B.B: Create a nonclustered index on LastName only

C.C: Create a nonclustered index on DepartmentID and another nonclustered index on LastName

D.D: Change the clustered index to (DepartmentID, LastName)

AnswerA

This composite covering index includes all columns needed for the query, avoiding expensive key lookups into the clustered index.

Why this answer

Option A is correct because it creates a covering nonclustered index on the filter columns (DepartmentID, LastName) and includes the Salary column as an included column. This allows the query to be fully satisfied by scanning only the nonclustered index pages, avoiding key lookups to the clustered index. The order of columns in the index key matches the query filter pattern, maximizing seek efficiency.

Exam trap

The trap here is that candidates often think separate single-column indexes are sufficient for multi-column filters, but they overlook the need for a covering composite index to avoid expensive key lookups or index intersection operations.

How to eliminate wrong answers

Option B is wrong because an index on LastName only does not help with filtering on DepartmentID, forcing a residual predicate or full scan of the clustered index for DepartmentID lookups. Option C is wrong because separate indexes on DepartmentID and LastName would require SQL Server to either use one index and then perform key lookups for the other filter, or use both with an expensive index intersection operation, neither of which is as efficient as a single composite index. Option D is wrong because changing the clustered index to (DepartmentID, LastName) would reorder the entire table physically, which could degrade performance for the existing EmployeeID-based lookups and other queries that rely on the current clustered key order.

Practice this question →

222

MCQmedium

A company needs to store order data for an e-commerce platform. The system requires high concurrency, fast inserts, and the ability to enforce referential integrity between tables (e.g., Customers and Orders). Which Azure service should they use?

A.Azure SQL Database

B.Azure Cosmos DB

C.Azure Blob Storage

D.Azure Data Lake Storage Gen2

AnswerA

Correct. Azure SQL Database is a fully managed relational database that supports ACID transactions, foreign keys, and high concurrency, making it ideal for transactional e-commerce order data.

Why this answer

Azure SQL Database is a fully managed relational database service that supports high concurrency, fast inserts, and enforces referential integrity through foreign key constraints. It provides ACID transactions and row-level locking to handle concurrent writes efficiently, making it ideal for e-commerce order processing where data consistency between Customers and Orders tables is critical.

Exam trap

The trap here is that candidates confuse high concurrency and fast inserts with NoSQL solutions like Cosmos DB, overlooking the explicit requirement for referential integrity which only a relational database like Azure SQL Database can enforce.

How to eliminate wrong answers

Option B is wrong because Azure Cosmos DB is a NoSQL database that does not enforce referential integrity between tables (it uses flexible schemas and lacks foreign key constraints). Option C is wrong because Azure Blob Storage is an object storage service for unstructured data (e.g., images, backups) and cannot enforce relational integrity or support SQL joins. Option D is wrong because Azure Data Lake Storage Gen2 is a hierarchical file system for big data analytics, not a transactional database, and it lacks support for referential integrity and high-concurrency row-level inserts.

Practice this question →

223

MCQeasy

You are designing a solution to store relational data that requires support for graph relationships and JSON queries. Which Azure service should you choose?

A.Azure Cosmos DB

B.Azure Table Storage

C.Azure SQL Database

D.Azure Database for PostgreSQL

AnswerC

Azure SQL Database supports graph tables and JSON queries.

Why this answer

Azure SQL Database is the correct choice because it natively supports graph relationships through graph tables (NODE and EDGE tables) and JSON queries via built-in JSON functions like JSON_VALUE, JSON_QUERY, and OPENJSON. This makes it ideal for storing relational data that also needs to handle graph traversals and semi-structured JSON data without requiring a separate service.

Exam trap

The trap here is that candidates often assume graph and JSON support require a NoSQL database like Cosmos DB, but Azure SQL Database provides both features within a relational model, which is the key distinction tested in DP-900.

How to eliminate wrong answers

Option A is wrong because Azure Cosmos DB is a NoSQL database that, while supporting graph APIs (Gremlin) and JSON natively, is not designed for strict relational data with ACID transactions across multiple tables. Option B is wrong because Azure Table Storage is a key-value store that lacks support for relational schemas, graph relationships, and JSON query capabilities. Option D is wrong because Azure Database for PostgreSQL, while supporting JSONB and graph extensions like Apache AGE, is not the primary Azure service for relational data with built-in graph and JSON support; Azure SQL Database offers tighter integration with Azure ecosystem features like elastic pools and built-in graph tables.

Practice this question →

224

MCQhard

A company is migrating an on-premises SQL Server database to Azure. The database is 800 GB, uses SQL Server Agent jobs for scheduled tasks, and needs to link to another on-premises SQL Server instance via linked servers. The company wants a fully managed service with minimal application changes. Which Azure SQL service should they choose?

A.Azure SQL Database elastic pool

B.Azure SQL Database single database

C.Azure SQL Managed Instance

D.Azure Synapse Analytics dedicated SQL pool

AnswerC

Correct because it supports SQL Agent, linked servers, and is fully managed with high compatibility.

Why this answer

Azure SQL Managed Instance is correct because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs and linked servers, while being a fully managed platform-as-a-service (PaaS) offering. This allows the company to migrate the 800 GB database with minimal application changes, as it preserves the existing instance-level features without requiring a rearchitecture.

Exam trap

The trap here is that candidates often confuse Azure SQL Database (single or elastic pool) with Azure SQL Managed Instance, assuming all PaaS offerings support SQL Server Agent and linked servers, when in fact only Managed Instance provides these instance-scoped features.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database elastic pool is a multi-tenant resource-sharing model for single databases, which does not support SQL Server Agent jobs or linked servers, and would require significant application changes to remove these dependencies. Option B is wrong because Azure SQL Database single database also lacks support for SQL Server Agent jobs and linked servers, forcing the company to refactor scheduled tasks and cross-instance queries. Option D is wrong because Azure Synapse Analytics dedicated SQL pool is a massively parallel processing (MPP) data warehouse designed for large-scale analytics, not for OLTP workloads, and it does not support SQL Server Agent jobs or linked servers, making it incompatible with the migration requirements.

Practice this question →

225

MCQmedium

A company uses Azure SQL Database for an order management system. The Orders table has millions of rows with columns: OrderID (primary key, clustered), CustomerID, OrderDate, Status (e.g., 'Shipped', 'Pending'), TotalAmount. Queries frequently filter on OrderDate and Status, and sort results by OrderDate in descending order. They return several columns including TotalAmount. Which indexing strategy will most improve query performance?

A.Create a nonclustered index on (OrderDate DESC, Status) INCLUDE (CustomerID, TotalAmount)

B.Create a clustered index on (OrderDate, Status)

C.Create a nonclustered index on (Status) only

D.Create a columnstore index on the entire table

AnswerA

Correct. This index matches the filter columns in the correct order and includes the ORDER BY direction. Included columns cover additional columns needed, making it a covering index for many queries.

Why this answer

Option A is correct because it creates a covering nonclustered index that matches the query's filter and sort order exactly. The index on (OrderDate DESC, Status) allows SQL Server to seek directly on OrderDate and Status, and the descending order avoids a sort operation for the ORDER BY OrderDate DESC clause. Including CustomerID and TotalAmount as included columns makes this a covering index, so the query can be satisfied entirely from the index without key lookups to the clustered index, which is critical for performance on a table with millions of rows.

Exam trap

The trap here is that candidates often think a clustered index on the filter columns is always best, but they forget that the clustered index already exists on OrderID and that a covering nonclustered index with included columns is more efficient for specific query patterns without disrupting the existing table structure.

How to eliminate wrong answers

Option B is wrong because a clustered index defines the physical order of the table, and changing it to (OrderDate, Status) would require rebuilding the entire table, potentially causing fragmentation and performance degradation for other queries that rely on the OrderID primary key. Additionally, a clustered index on (OrderDate, Status) would not be as efficient for the specific filter and sort pattern because it does not include the descending order and would still require a sort operation. Option C is wrong because a nonclustered index on (Status) only would allow seeking on Status, but it would not help with the OrderDate filter or the ORDER BY OrderDate DESC clause, leading to residual predicate filtering and a sort operation, and it would not cover the query, requiring expensive key lookups for the other columns.

Option D is wrong because a columnstore index is designed for large-scale analytical queries and data warehousing workloads, not for high-frequency transactional queries that filter and sort on specific columns; it would introduce overhead for point lookups and small-range scans, and the query pattern described is OLTP, not analytical.

Practice this question →

← PreviousPage 3 of 4 · 239 questions totalNext →

Ready to test yourself?

Try a timed practice session using only Identify considerations for relational data on Azure questions.

Start 20-question session