Sample questions
Microsoft Azure Data Fundamentals DP-900 practice questions
An e-commerce application processes customer orders. When an order is placed, the system must decrement the inventory count and process the payment. The application ensures that either both operations complete successfully or both are rolled back if any error occurs. Which database property does this guarantee?
Trap 1: Consistency
Consistency ensures that a transaction brings the database from one valid state to another, maintaining data integrity rules. It does not directly guarantee the all-or-nothing execution of operations.
Trap 2: Isolation
Isolation ensures that concurrent transactions do not interfere with each other. It addresses visibility of intermediate transaction states, not the atomicity of operations within a single transaction.
Trap 3: Durability
Durability guarantees that once a transaction is committed, its changes persist even in the event of a system failure. It does not relate to the all-or-nothing execution of a transaction.
- A
Atomicity
Atomicity ensures that a transaction is an all-or-nothing operation. If any part of the transaction fails, the entire transaction is rolled back, preventing partial updates.
- B
Consistency
Why wrong: Consistency ensures that a transaction brings the database from one valid state to another, maintaining data integrity rules. It does not directly guarantee the all-or-nothing execution of operations.
- C
Isolation
Why wrong: Isolation ensures that concurrent transactions do not interfere with each other. It addresses visibility of intermediate transaction states, not the atomicity of operations within a single transaction.
- D
Durability
Why wrong: Durability guarantees that once a transaction is committed, its changes persist even in the event of a system failure. It does not relate to the all-or-nothing execution of a transaction.
A company runs an e-commerce application on Azure SQL Database. The application experiences heavy read traffic from reporting dashboards that query the same tables as the transactional workload. This causes performance degradation for the application. The company needs a solution that offloads reporting queries to a read-only copy that stays synchronized within minutes, without impacting transactional performance. Which Azure SQL Database feature should they use?
Trap 1: Auto-failover groups
Failover groups enable automatic failover of multiple databases, but they do not primarily focus on offloading read traffic; the secondary in a failover group is not readable unless you also enable geo-replication.
Trap 2: Elastic pools
Elastic pools allow multiple databases to share resources but do not create a separate read-only copy for offloading queries.
Trap 3: Query Performance Insight
Query Performance Insight is a monitoring tool that helps identify performance bottlenecks, but it does not offload queries to a replica.
- A
Auto-failover groups
Why wrong: Failover groups enable automatic failover of multiple databases, but they do not primarily focus on offloading read traffic; the secondary in a failover group is not readable unless you also enable geo-replication.
- B
Active geo-replication
Correct. Active geo-replication provides a readable secondary database that can serve reporting queries while the primary handles transactional workloads. The replication lag is typically within minutes.
- C
Elastic pools
Why wrong: Elastic pools allow multiple databases to share resources but do not create a separate read-only copy for offloading queries.
- D
Query Performance Insight
Why wrong: Query Performance Insight is a monitoring tool that helps identify performance bottlenecks, but it does not offload queries to a replica.
A company uses Azure SQL Database for an order management system. The Orders table has columns: OrderID (int, primary key), CustomerID (int), OrderDate (datetime), Status (varchar), TotalAmount (decimal). Queries frequently filter on CustomerID and OrderDate to find orders from a specific customer within a date range. Which index would most improve performance for these queries?
Trap 1: A clustered index on OrderID
This index optimizes lookups by OrderID but does not help queries filtering on CustomerID and OrderDate.
Trap 2: A non-clustered index on Status
Filtering on Status is not the frequent query pattern; this index does not support the CustomerID and OrderDate filters.
Trap 3: A non-clustered index on (OrderDate, TotalAmount)
Although OrderDate is used, the leading column is not CustomerID, so the index is less selective and may not be as effective for this query.
- A
A clustered index on OrderID
Why wrong: This index optimizes lookups by OrderID but does not help queries filtering on CustomerID and OrderDate.
- B
A non-clustered index on Status
Why wrong: Filtering on Status is not the frequent query pattern; this index does not support the CustomerID and OrderDate filters.
- C
A non-clustered index on (CustomerID, OrderDate) INCLUDE (TotalAmount)
This composite index directly supports the filter predicate (CustomerID and OrderDate) and includes TotalAmount to make it a covering index, reducing I/O.
- D
A non-clustered index on (OrderDate, TotalAmount)
Why wrong: Although OrderDate is used, the leading column is not CustomerID, so the index is less selective and may not be as effective for this query.
A gaming company stores player scores in Azure Cosmos DB using the NoSQL API. Each document contains fields: PlayerID (unique to the player), GameID, Score, Timestamp. The most common query is: 'Retrieve all scores for a specific GameID, ordered by Score descending.' Which property should be chosen as the partition key to minimize Request Unit (RU) consumption?
Trap 1: PlayerID
Using PlayerID would distribute data across many partitions. Queries by GameID would need to fan out to all partitions that contain Players for that game, increasing RU consumption.
Trap 2: Score
Score has high cardinality and is used for ordering, not filtering. Using Score as partition key would not narrow the query to a single partition; the query would need to read all partitions and then sort.
Trap 3: Timestamp
Timestamp is not used in the filter. A partition key on Timestamp would cause the query to scatter to many partitions (since scores for a given game could have many timestamps), leading to high RU consumption.
- A
PlayerID
Why wrong: Using PlayerID would distribute data across many partitions. Queries by GameID would need to fan out to all partitions that contain Players for that game, increasing RU consumption.
- B
GameID
GameID is the attribute used in the query filter. Choosing it as the partition key ensures that all scores for a given game are co-located in one partition, allowing a point query to that single partition and minimizing RU cost.
- C
Score
Why wrong: Score has high cardinality and is used for ordering, not filtering. Using Score as partition key would not narrow the query to a single partition; the query would need to read all partitions and then sort.
- D
Timestamp
Why wrong: Timestamp is not used in the filter. A partition key on Timestamp would cause the query to scatter to many partitions (since scores for a given game could have many timestamps), leading to high RU consumption.
A gaming company stores player profiles as JSON documents. Each profile includes standard fields like playerId, username, and email, as well as optional fields such as achievements, gamePreferences, and friendsList. The application needs to look up profiles by playerId with low latency (under 10 ms) and also run SQL-like queries to find players who have a specific achievement. Which Azure Cosmos DB API should they choose?
Trap 1: A
The Table API is designed for key-value storage with a fixed schema; it does not support querying nested JSON fields or flexible schema as effectively as the SQL API.
Trap 2: B
The Cassandra API uses a wide-column model and CQL (Cassandra Query Language), which is not the same as SQL and may require more complex modeling to handle optional fields.
Trap 3: C
The MongoDB API supports JSON documents and queries, but it uses MongoDB's query syntax, not standard SQL. While it can meet the requirement, the SQL API is a more natural fit when the team wants SQL-like queries.
- A
A. Table API
Why wrong: The Table API is designed for key-value storage with a fixed schema; it does not support querying nested JSON fields or flexible schema as effectively as the SQL API.
- B
B. Cassandra API
Why wrong: The Cassandra API uses a wide-column model and CQL (Cassandra Query Language), which is not the same as SQL and may require more complex modeling to handle optional fields.
- C
C. MongoDB API
Why wrong: The MongoDB API supports JSON documents and queries, but it uses MongoDB's query syntax, not standard SQL. While it can meet the requirement, the SQL API is a more natural fit when the team wants SQL-like queries.
- D
D. SQL (Core) API
The SQL API stores JSON documents and supports querying with a SQL dialect. Point reads by partition key (playerId) are low-latency, and SQL queries can easily filter on optional fields like achievements. This makes it the best choice.
A company is migrating an on-premises SQL Server database to Azure. They want to ensure that database administrators (DBAs) can perform administrative tasks but cannot view sensitive customer data in query results. Which Azure SQL feature should they implement?
Trap 1: Dynamic Data Masking
Dynamic Data Masking hides sensitive data in query results for non-privileged users, but DBAs (with the 'UNMASK' permission) can still see the original data.
Trap 2: Transparent Data Encryption
Transparent Data Encryption (TDE) encrypts the database at rest (on disk) but does not protect data from being viewed by users with query permissions, including DBAs.
Trap 3: Row-Level Security
Row-Level Security restricts row access based on user context, but a DBA with database owner privileges can bypass the security policy and see all rows.
- A
Dynamic Data Masking
Why wrong: Dynamic Data Masking hides sensitive data in query results for non-privileged users, but DBAs (with the 'UNMASK' permission) can still see the original data.
- B
Always Encrypted
Always Encrypted encrypts data on the client side, so the database never sees plaintext. DBAs cannot access the encryption keys and therefore cannot view the sensitive data.
- C
Transparent Data Encryption
Why wrong: Transparent Data Encryption (TDE) encrypts the database at rest (on disk) but does not protect data from being viewed by users with query permissions, including DBAs.
- D
Row-Level Security
Why wrong: Row-Level Security restricts row access based on user context, but a DBA with database owner privileges can bypass the security policy and see all rows.
A global e-commerce company uses Azure SQL Database for its product catalog. The application experiences high read traffic for product detail pages, often running the same queries for popular items. The database’s write workload is moderate. The company wants to improve read performance without increasing the cost of the primary database tier and without changing the application code. Which Azure SQL Database feature should they implement?
Trap 1: Active geo-replication
Incorrect. Active geo-replication provides readable secondary replicas in different regions for disaster recovery, but it is primarily for high availability and can incur higher cost. It is not the simplest solution for offloading reads within the same region.
Trap 2: Azure SQL Database elastic pool
Incorrect. An elastic pool is used to share resources among multiple databases, not to create read replicas for a single database.
Trap 3: Query Performance Insight
Incorrect. Query Performance Insight is a monitoring and diagnostic tool that helps identify performance issues, but it does not offload read traffic.
- A
Read scale-out
Correct. Read scale-out creates a read-only replica that can handle reporting and read workloads without impacting the primary's performance, and it can be used with a connection string that enables read-only routing.
- B
Active geo-replication
Why wrong: Incorrect. Active geo-replication provides readable secondary replicas in different regions for disaster recovery, but it is primarily for high availability and can incur higher cost. It is not the simplest solution for offloading reads within the same region.
- C
Azure SQL Database elastic pool
Why wrong: Incorrect. An elastic pool is used to share resources among multiple databases, not to create read replicas for a single database.
- D
Query Performance Insight
Why wrong: Incorrect. Query Performance Insight is a monitoring and diagnostic tool that helps identify performance issues, but it does not offload read traffic.
A global e-commerce company uses Azure SQL Database for its product catalog. The database is hosted in the West US region. To ensure the catalog remains available if West US experiences an outage, the company wants to configure a secondary database in East US that can be used for reads and can be automatically promoted to primary during a disaster. They require a Recovery Point Objective (RPO) of less than 5 seconds and a Recovery Time Objective (RTO) of less than 30 minutes. Which feature should they implement?
Trap 1: Active geo-replication
Active geo-replication creates readable secondaries, but failover is manual, not automatic, and does not provide an RTO of under 30 minutes in an automated manner.
Trap 2: Geo-restore
Geo-restore restores a database from a geo-replicated backup, but has a longer RTO (typically hours) and no automatic failover capability.
Trap 3: Transactional replication
Transactional replication is used to replicate data changes to other databases for distributed scenarios, not for automatic failover during a regional outage.
- A
Active geo-replication
Why wrong: Active geo-replication creates readable secondaries, but failover is manual, not automatic, and does not provide an RTO of under 30 minutes in an automated manner.
- B
Auto-failover groups
Auto-failover groups provide automatic failover to a secondary region, include a readable secondary, and meet the RPO of 5 seconds and RTO of 30 minutes.
- C
Geo-restore
Why wrong: Geo-restore restores a database from a geo-replicated backup, but has a longer RTO (typically hours) and no automatic failover capability.
- D
Transactional replication
Why wrong: Transactional replication is used to replicate data changes to other databases for distributed scenarios, not for automatic failover during a regional outage.
A manufacturing company stores IoT sensor data as JSON documents in Azure Cosmos DB. Each document has fields: deviceId (high cardinality, many unique values), timestamp, temperature, and humidity. The most frequent query is: 'Retrieve all readings for a specific deviceId from the last hour.' To minimize Request Unit (RU) consumption, which combination of partition key and indexing policy should be chosen?
Trap 1: Partition key: timestamp, Indexing: automatic on all properties
timestamp is a poor partition key because it can cause hot partitions (e.g., all recent data on one partition) and queries for a specific deviceId would be cross-partition.
Trap 2: Partition key: deviceId, Indexing: none
Without indexing, the query cannot use an index on timestamp, causing a full scan of the partition, which increases RU consumption.
Trap 3: Partition key: temperature, Indexing: automatic on all properties
temperature is not ideal as a partition key because it has low cardinality and uneven distribution; queries for a specific deviceId would be cross-partition.
- A
Partition key: deviceId, Indexing: automatic on all properties
Correct. deviceId as partition key targets a single partition; automatic indexing ensures the timestamp filter uses an index, minimizing RUs.
- B
Partition key: timestamp, Indexing: automatic on all properties
Why wrong: timestamp is a poor partition key because it can cause hot partitions (e.g., all recent data on one partition) and queries for a specific deviceId would be cross-partition.
- C
Partition key: deviceId, Indexing: none
Why wrong: Without indexing, the query cannot use an index on timestamp, causing a full scan of the partition, which increases RU consumption.
- D
Partition key: temperature, Indexing: automatic on all properties
Why wrong: temperature is not ideal as a partition key because it has low cardinality and uneven distribution; queries for a specific deviceId would be cross-partition.
A social media application stores user posts in Azure Cosmos DB using the NoSQL API. Each document includes: PostID (unique), UserID, Timestamp, Content. The most common query is: 'Get all posts for a specific UserID, sorted by Timestamp descending.' Which partition key should be chosen to distribute load evenly across physical partitions while also supporting this query efficiently?
Trap 1: PostID
Using PostID as the partition key would distribute posts evenly, but the query for all posts of a user would need to fan out across all partitions because posts for the same user would be scattered. This increases RU consumption and latency.
Trap 2: Timestamp
Using Timestamp as partition key often leads to hot partitions because many writes may happen at the same time (e.g., burst of posts), causing throttling. It also does not support the query efficiently because posts for a user would be scattered.
Trap 3: Content
Content is not suitable as a partition key because it is typically long, variable, and leads to high cardinality without logical grouping that matches the query pattern. It would cause inefficient queries.
- A
PostID
Why wrong: Using PostID as the partition key would distribute posts evenly, but the query for all posts of a user would need to fan out across all partitions because posts for the same user would be scattered. This increases RU consumption and latency.
- B
UserID
UserID groups all posts for a user into one logical partition, making the query efficient. With many users, the load is balanced across physical partitions, avoiding hot spots.
- C
Timestamp
Why wrong: Using Timestamp as partition key often leads to hot partitions because many writes may happen at the same time (e.g., burst of posts), causing throttling. It also does not support the query efficiently because posts for a user would be scattered.
- D
Content
Why wrong: Content is not suitable as a partition key because it is typically long, variable, and leads to high cardinality without logical grouping that matches the query pattern. It would cause inefficient queries.
A company uses Azure SQL Database for a financial system. The Transactions table contains millions of rows with a TransactionDate column. Queries frequently aggregate sales totals for the current month, but historical data must be retained for 7 years. Currently, queries scan the entire table, causing performance issues. The company also wants to simplify archiving of old data. Which design should they implement?
Trap 1: Create a non-clustered index on the TransactionDate column.
A non-clustered index speeds up searches for specific date ranges but does not help with large full-table scans for monthly aggregations, nor does it simplify archiving.
Trap 2: Create a materialized view for the current month's data.
A materialized view can pre-aggregate data for faster queries, but it does not reduce the scan on the base table and does not provide an easy archiving mechanism for old data.
Trap 3: Convert the table to use a clustered columnstore index.
A columnstore index improves performance for analytic scans but still requires scanning all partitions or the entire table. It does not simplify archiving of old data.
- A
Create a non-clustered index on the TransactionDate column.
Why wrong: A non-clustered index speeds up searches for specific date ranges but does not help with large full-table scans for monthly aggregations, nor does it simplify archiving.
- B
Implement table partitioning by month on TransactionDate.
Partitioning enables partition elimination for queries filtering on TransactionDate, reducing scan size. Old partitions can be switched out for easy archiving without impacting the live table.
- C
Create a materialized view for the current month's data.
Why wrong: A materialized view can pre-aggregate data for faster queries, but it does not reduce the scan on the base table and does not provide an easy archiving mechanism for old data.
- D
Convert the table to use a clustered columnstore index.
Why wrong: A columnstore index improves performance for analytic scans but still requires scanning all partitions or the entire table. It does not simplify archiving of old data.
A retail company wants to analyze customer clickstream data in real-time to detect patterns and trigger personalized offers. They also store the raw clickstream data in Azure Data Lake Storage for later batch analysis. Which Azure service should they use for the real-time processing component?
Trap 1: Azure Data Factory
Azure Data Factory is primarily an orchestration and data movement service for batch and scheduled pipelines, not for real-time stream processing.
Trap 2: Azure Batch
Azure Batch is for running large-scale batch computing workloads, not real-time stream processing.
Trap 3: Azure Data Lake Analytics
Azure Data Lake Analytics is a batch query service for data stored in Data Lake Storage; it does not process real-time streaming data.
- A
Azure Data Factory
Why wrong: Azure Data Factory is primarily an orchestration and data movement service for batch and scheduled pipelines, not for real-time stream processing.
- B
Azure Stream Analytics
Azure Stream Analytics processes streaming data in real time using SQL-like queries, making it suitable for real-time analytics and event-driven responses.
- C
Azure Batch
Why wrong: Azure Batch is for running large-scale batch computing workloads, not real-time stream processing.
- D
Azure Data Lake Analytics
Why wrong: Azure Data Lake Analytics is a batch query service for data stored in Data Lake Storage; it does not process real-time streaming data.
A smart building company stores IoT sensor data in Azure Cosmos DB using the NoSQL API. Each document contains fields: deviceId (partition key), timestamp, temperature, and humidity. The most common query is to retrieve all readings for a specific device within a time range, which runs efficiently. However, the analytics team occasionally runs a query to find all devices that reported a temperature above 50 degrees Celsius in the last hour, without specifying deviceId. This query is very slow and consumes a high number of request units (RUs). What is the most likely reason for the slow performance and high RU consumption?
Trap 1: The query is not using an index on the temperature field.
While indexes are important, Cosmos DB automatically indexes all fields by default. The absence of a partition key in the filter is the primary issue because it forces a full scan across partitions.
Trap 2: The time range filter is too large, causing a full table scan.
A large time range can increase the number of documents scanned, but the main issue is that the query is cross-partition. Even with a small time range, the query would still need to search each partition for matching documents.
Trap 3: The document size is too large, increasing RU per read.
Document size does affect RU consumption, but the dramatic increase in RUs from a query that omits the partition key is primarily due to cross-partition execution, not document size.
- A
The query does not use the partition key, causing a cross-partition scan.
When a query does not include the partition key, Cosmos DB must execute the query across all partitions, which increases latency and RU consumption. This is the most likely cause.
- B
The query is not using an index on the temperature field.
Why wrong: While indexes are important, Cosmos DB automatically indexes all fields by default. The absence of a partition key in the filter is the primary issue because it forces a full scan across partitions.
- C
The time range filter is too large, causing a full table scan.
Why wrong: A large time range can increase the number of documents scanned, but the main issue is that the query is cross-partition. Even with a small time range, the query would still need to search each partition for matching documents.
- D
The document size is too large, increasing RU per read.
Why wrong: Document size does affect RU consumption, but the dramatic increase in RUs from a query that omits the partition key is primarily due to cross-partition execution, not document size.
A social media company stores user-generated posts as JSON documents. Each post contains fields such as postId, userId, timestamp, and content. The application needs to query posts by userId and timestamp ranges with low latency, and also perform SQL-like queries across all posts. The data volume is growing rapidly and must scale globally. Which Azure data store should the company use?
Trap 1: A) Azure Table Storage
Incorrect. Azure Table Storage is a key-value store that does not support querying by arbitrary fields within JSON documents, nor does it provide SQL-like query capability. It is not suitable for complex queries on nested JSON data.
Trap 2: C) Azure Blob Storage
Incorrect. Azure Blob Storage is for unstructured binary data (e.g., images, videos) and does not provide a query interface for JSON content. It cannot support the required SQL-like queries or low-latency point reads by userId.
Trap 3: D) Azure Cache for Redis
Incorrect. Azure Cache for Redis is an in-memory data store best suited for caching and session management, not for persistent storage of JSON documents with complex querying capabilities. It lacks the durability and query features required.
- A
A) Azure Table Storage
Why wrong: Incorrect. Azure Table Storage is a key-value store that does not support querying by arbitrary fields within JSON documents, nor does it provide SQL-like query capability. It is not suitable for complex queries on nested JSON data.
- B
B) Azure Cosmos DB SQL API
Correct. The Cosmos DB SQL API natively stores JSON documents, supports indexing on any field, and allows rich SQL-like queries. It offers global distribution, low latency, and scalable throughput, making it ideal for this scenario.
- C
C) Azure Blob Storage
Why wrong: Incorrect. Azure Blob Storage is for unstructured binary data (e.g., images, videos) and does not provide a query interface for JSON content. It cannot support the required SQL-like queries or low-latency point reads by userId.
- D
D) Azure Cache for Redis
Why wrong: Incorrect. Azure Cache for Redis is an in-memory data store best suited for caching and session management, not for persistent storage of JSON documents with complex querying capabilities. It lacks the durability and query features required.
A smart building company stores sensor data from thousands of IoT devices as JSON documents in Azure Cosmos DB using the NoSQL API. Each document contains fields: deviceId (string), timestamp (datetime), temperature (float), humidity (float), and additional device-specific fields (e.g., motionDetected, CO2level). The most common query is: SELECT * FROM c WHERE c.deviceId = 'sensor-123' AND c.timestamp >= '2025-01-01' AND c.timestamp < '2025-02-01' ORDER BY c.timestamp DESC. Which indexing strategy will provide the best performance for this query?
Trap 1: Use the default indexing policy that automatically indexes all…
The default policy indexes each property individually, which can support equality filters but is not optimal for range queries combined with sorting by timestamp; it may result in a slower execution.
Trap 2: Disable indexing for all properties to speed up writes
Without any indexes, every query will scan the entire container, causing extremely poor read performance for the given workload.
Trap 3: Create a spatial index on the deviceId field
Spatial indexes are for geospatial queries (e.g., ST_DISTANCE, ST_WITHIN) and are not helpful for equality and range queries on deviceId and timestamp.
- A
Use the default indexing policy that automatically indexes all properties
Why wrong: The default policy indexes each property individually, which can support equality filters but is not optimal for range queries combined with sorting by timestamp; it may result in a slower execution.
- B
Create a composite index on (deviceId ASC, timestamp DESC)
This composite index matches the query predicates: first seeks on deviceId equality, then efficiently performs a range scan on timestamp in descending order, avoiding an in-memory sort.
- C
Disable indexing for all properties to speed up writes
Why wrong: Without any indexes, every query will scan the entire container, causing extremely poor read performance for the given workload.
- D
Create a spatial index on the deviceId field
Why wrong: Spatial indexes are for geospatial queries (e.g., ST_DISTANCE, ST_WITHIN) and are not helpful for equality and range queries on deviceId and timestamp.
A startup is developing a web application that requires a relational database with PostgreSQL compatibility. They want a fully managed service that automatically handles backups, patching, and provides high availability with a 99.99% SLA. Which Azure service should they choose?
Trap 1: Azure SQL Database
Azure SQL Database is a fully managed SQL Server relational database service, not compatible with PostgreSQL. It does not meet the PostgreSQL compatibility requirement.
Trap 2: Azure Database for MySQL
Azure Database for MySQL is a fully managed MySQL service, not compatible with PostgreSQL. The application requires PostgreSQL compatibility.
Trap 3: Azure Cosmos DB for PostgreSQL
Azure Cosmos DB for PostgreSQL is a distributed, horizontally scalable PostgreSQL-compatible database. However, it is designed for large-scale workloads and may not offer the simple fully managed experience with 99.99% SLA that the standard Azure Database for PostgreSQL flexible server provides. The question specifies automatic backups, patching, and high availability with 99.99% SLA, which aligns with Azure Database for PostgreSQL.
- A
Azure Database for PostgreSQL
Azure Database for PostgreSQL (Flexible Server) is a fully managed PostgreSQL service with automatic backups, patching, and zone-redundant high availability offering a 99.99% SLA. It is the ideal choice for a PostgreSQL-compatible relational database.
- B
Azure SQL Database
Why wrong: Azure SQL Database is a fully managed SQL Server relational database service, not compatible with PostgreSQL. It does not meet the PostgreSQL compatibility requirement.
- C
Azure Database for MySQL
Why wrong: Azure Database for MySQL is a fully managed MySQL service, not compatible with PostgreSQL. The application requires PostgreSQL compatibility.
- D
Azure Cosmos DB for PostgreSQL
Why wrong: Azure Cosmos DB for PostgreSQL is a distributed, horizontally scalable PostgreSQL-compatible database. However, it is designed for large-scale workloads and may not offer the simple fully managed experience with 99.99% SLA that the standard Azure Database for PostgreSQL flexible server provides. The question specifies automatic backups, patching, and high availability with 99.99% SLA, which aligns with Azure Database for PostgreSQL.
A small online retailer wants to migrate its single on-premises SQL Server database to Azure. They require a fully managed relational database service with built-in high availability, automated backups, and no need to manage virtual machines. They do not need features like multiple databases with cross-database queries or SQL Agent. Which Azure service should they choose?
Trap 1: Azure SQL Managed Instance
Incorrect. While also PaaS, SQL Managed Instance provides additional features like cross-database queries and SQL Agent, which are not needed here and would increase cost and complexity.
Trap 2: SQL Server on Azure Virtual Machines
Incorrect. This is IaaS; you must manage the VM and SQL Server, which contradicts the requirement to avoid managing VMs.
Trap 3: Azure Database for MySQL
Incorrect. This is a different database engine (MySQL) and does not meet the requirement to migrate a SQL Server database.
- A
Azure SQL Database
Correct. Azure SQL Database is a PaaS offering with built-in high availability and automated backups, requiring no VM management. It is ideal for a single database migration.
- B
Azure SQL Managed Instance
Why wrong: Incorrect. While also PaaS, SQL Managed Instance provides additional features like cross-database queries and SQL Agent, which are not needed here and would increase cost and complexity.
- C
SQL Server on Azure Virtual Machines
Why wrong: Incorrect. This is IaaS; you must manage the VM and SQL Server, which contradicts the requirement to avoid managing VMs.
- D
Azure Database for MySQL
Why wrong: Incorrect. This is a different database engine (MySQL) and does not meet the requirement to migrate a SQL Server database.
A software company develops a multi-tenant SaaS application. They deploy a separate Azure SQL Database for each tenant. The databases are small (2-5 GB) and have highly variable loads — some tenants use the app heavily during the day, others at night. The company wants to maximize resource utilization and minimize costs by allowing databases to share a pool of resources, while still maintaining a predictable performance per database. Which Azure SQL Database deployment option should they choose?
Trap 1: Single database with DTU purchasing model
Incorrect. A single database with DTU charges separately per database and does not share resources across the set of databases.
Trap 2: Single database with vCore purchasing model
Incorrect. Like DTU, vCore-based single databases are isolated and do not allow resource pooling across databases.
Trap 3: Azure SQL Managed Instance
Incorrect. Managed Instance is intended for instance-scoped features and is not designed for pooling resources among many small databases.
- A
Single database with DTU purchasing model
Why wrong: Incorrect. A single database with DTU charges separately per database and does not share resources across the set of databases.
- B
Single database with vCore purchasing model
Why wrong: Incorrect. Like DTU, vCore-based single databases are isolated and do not allow resource pooling across databases.
- C
Elastic pool
Correct. Elastic pools share resources across multiple databases, ideal for variable, low-average usage patterns, and provide cost savings.
- D
Azure SQL Managed Instance
Why wrong: Incorrect. Managed Instance is intended for instance-scoped features and is not designed for pooling resources among many small databases.
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?
Trap 1: Sharding across multiple Azure SQL Managed Instances
Sharding is a horizontal scaling pattern that distributes data across multiple databases, not the same as table partitioning within a single database.
Trap 2: Index partitioning only
Index partitioning is a part of table partitioning but not sufficient on its own; you need full table partitioning to maintain the existing design.
Trap 3: Federated tables
Federated tables are not a native feature of Azure SQL Managed Instance; they are used in SQL Server with linked servers and are not supported for partitioning.
- A
Table partitioning with partition functions and schemes
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.
- B
Sharding across multiple Azure SQL Managed Instances
Why wrong: Sharding is a horizontal scaling pattern that distributes data across multiple databases, not the same as table partitioning within a single database.
- C
Index partitioning only
Why wrong: Index partitioning is a part of table partitioning but not sufficient on its own; you need full table partitioning to maintain the existing design.
- D
Federated tables
Why wrong: Federated tables are not a native feature of Azure SQL Managed Instance; they are used in SQL Server with linked servers and are not supported for partitioning.
A company maintains a database of customer orders that are updated frequently. They also store aggregated monthly sales reports that are generated once and then only read. Which statement correctly distinguishes these two types of data workloads?
Trap 1: Transactional data must always be stored in non-relational…
This is false. Both transactional and analytical data can be stored in either type of database depending on requirements.
Trap 2: Analytical data always requires real-time processing, whereas…
This is false. Analytical workloads often use batch processing, and transactional workloads are typically real-time.
Trap 3: Transactional data is read-only and analytical data is frequently…
This is false. Transactional data is frequently updated, while analytical data is usually read-only after being loaded.
- A
Transactional data is optimized for write operations, and analytical data is optimized for read operations.
This is correct. OLTP systems are designed for efficient writes, while OLAP systems are designed for complex reads.
- B
Transactional data must always be stored in non-relational databases, and analytical data in relational databases.
Why wrong: This is false. Both transactional and analytical data can be stored in either type of database depending on requirements.
- C
Analytical data always requires real-time processing, whereas transactional data is batch-processed.
Why wrong: This is false. Analytical workloads often use batch processing, and transactional workloads are typically real-time.
- D
Transactional data is read-only and analytical data is frequently updated.
Why wrong: This is false. Transactional data is frequently updated, while analytical data is usually read-only after being loaded.
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?
Trap 1: Azure SQL Database
Azure SQL Database does not support SQL Server Agent jobs, which is a requirement for scheduled maintenance.
Trap 2: SQL Server on Azure Virtual Machines
While it supports SQL Server Agent, it requires manual patching and management, not a fully managed service.
Trap 3: Azure Synapse Analytics
Azure Synapse Analytics is a data warehouse solution not designed for transactional workloads with SQL Agent jobs.
- A
Azure SQL Database
Why wrong: Azure SQL Database does not support SQL Server Agent jobs, which is a requirement for scheduled maintenance.
- B
Azure SQL Managed Instance
SQL Managed Instance supports SQL Server Agent, can scale up to 16 TB, and provides auto-failover groups for cross-region high availability.
- C
SQL Server on Azure Virtual Machines
Why wrong: While it supports SQL Server Agent, it requires manual patching and management, not a fully managed service.
- D
Azure Synapse Analytics
Why wrong: Azure Synapse Analytics is a data warehouse solution not designed for transactional workloads with SQL Agent jobs.
A company plans to migrate an on-premises SQL Server database to Azure. The database uses SQL Server Agent jobs for scheduled maintenance and relies on linked servers to query data from another SQL Server instance. It also performs cross-database queries within the same instance. The company wants a fully managed PaaS service that requires minimal application changes and provides automated backups and patching. Which Azure SQL service should they choose?
Trap 1: A
Azure SQL Database does not support SQL Server Agent, linked servers, or cross-database queries natively; requires significant application changes.
Trap 2: C
SQL Server on Azure VMs is an IaaS solution; the company must manage patching, backups, and high availability, contradicting the 'fully managed' requirement.
Trap 3: D
An elastic pool is a collection of Azure SQL Databases; it inherits the same limitations as single databases regarding Agent jobs and linked servers.
- A
A. Azure SQL Database (single database)
Why wrong: Azure SQL Database does not support SQL Server Agent, linked servers, or cross-database queries natively; requires significant application changes.
- B
B. Azure SQL Managed Instance
Azure SQL Managed Instance provides high compatibility with on-premises SQL Server, including Agent jobs, linked servers, and cross-database queries, while being fully managed.
- C
C. SQL Server on Azure Virtual Machines
Why wrong: SQL Server on Azure VMs is an IaaS solution; the company must manage patching, backups, and high availability, contradicting the 'fully managed' requirement.
- D
D. Azure SQL Database elastic pool
Why wrong: An elastic pool is a collection of Azure SQL Databases; it inherits the same limitations as single databases regarding Agent jobs and linked servers.
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?
Trap 1: General Purpose
General Purpose provides balanced compute and storage but has a higher RPO (up to 1 hour) and does not guarantee sub-5-second RPO or sub-30-minute RTO.
Trap 2: Hyperscale
Hyperscale is designed for databases larger than 4 TB and offers fast scaling but has a slightly higher RPO (typically up to 1 minute) and is not primarily focused on the aggressive RPO/RTO needed here.
Trap 3: Serverless (General Purpose)
Serverless is a compute tier within General Purpose that auto-pauses, but it inherits General Purpose's RPO/RTO, which does not meet the sub-5-second RPO requirement.
- A
General Purpose
Why wrong: General Purpose provides balanced compute and storage but has a higher RPO (up to 1 hour) and does not guarantee sub-5-second RPO or sub-30-minute RTO.
- B
Business Critical
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.
- C
Hyperscale
Why wrong: Hyperscale is designed for databases larger than 4 TB and offers fast scaling but has a slightly higher RPO (typically up to 1 minute) and is not primarily focused on the aggressive RPO/RTO needed here.
- D
Serverless (General Purpose)
Why wrong: Serverless is a compute tier within General Purpose that auto-pauses, but it inherits General Purpose's RPO/RTO, which does not meet the sub-5-second RPO requirement.
A company operates a high-volume order processing system on Azure SQL Database. During peak hours, many concurrent transactions try to insert and update rows in the same table, causing contention on page latches. Indexing and query optimization are already tuned. Which feature should the company implement to reduce write contention while preserving ACID properties?
Trap 1: Read Scale-out
Read Scale-out creates read-only replicas to offload read queries but does not reduce write contention on the primary.
Trap 2: Elastic Database Query
Elastic Database Query allows querying across multiple databases but does not address contention within a single database.
Trap 3: Transparent Data Encryption (TDE)
TDE encrypts data at rest for security purposes and has no impact on write contention or transaction performance.
- A
Read Scale-out
Why wrong: Read Scale-out creates read-only replicas to offload read queries but does not reduce write contention on the primary.
- B
In-Memory OLTP
In-Memory OLTP improves write performance by eliminating latch contention through memory-optimized tables and optimistic concurrency, ideal for high-concurrency transactional workloads.
- C
Elastic Database Query
Why wrong: Elastic Database Query allows querying across multiple databases but does not address contention within a single database.
- D
Transparent Data Encryption (TDE)
Why wrong: TDE encrypts data at rest for security purposes and has no impact on write contention or transaction performance.
Question Discussion
Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.
Sign in to join the discussion.