DP-900 Practice Exam 2 — 20 Questions

Question 1

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?

Accepted Answer

Azure SQL Managed Instance. 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.

Answer

Azure SQL Database

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Synapse Analytics

Question 2

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?

Accepted Answer

B. Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs, linked servers, and cross-database queries within the same instance. It is a fully managed PaaS service that offers automated backups and patching, minimizing application changes during migration.

Answer

A. Azure SQL Database (single database)

Answer

C. SQL Server on Azure Virtual Machines

Answer

D. Azure SQL Database elastic pool

Question 3

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?

Accepted Answer

Business Critical. 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.

Answer

General Purpose

Answer

Hyperscale

Answer

Serverless (General Purpose)

Question 4

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?

Accepted Answer

In-Memory OLTP. In-Memory OLTP is correct because it uses memory-optimized tables and natively compiled stored procedures to reduce latch contention by eliminating the need for page latches entirely. Transactions operate directly on in-memory data structures, using optimistic multi-version concurrency control (MVCC) to detect conflicts without blocking, which preserves ACID properties while allowing high concurrency.

Answer

Read Scale-out

Answer

Elastic Database Query

Answer

Transparent Data Encryption (TDE)

Question 5

A smart home company stores device telemetry in Azure Cosmos DB using the NoSQL API. Each document contains: deviceId (string), timestamp (datetime), temperature (float), humidity (float). The most common query retrieves all documents for a specific deviceId within a time range, ordered by timestamp descending. This query performs well. However, a new query that finds devices with temperature > 50 in the last hour (without specifying deviceId) is extremely slow and consumes many request units (RUs). What is the most likely cause?

Accepted Answer

The query does not specify the partition key (deviceId), causing a cross-partition query that scans every physical partition.. In Azure Cosmos DB NoSQL API, the partition key (deviceId) determines data distribution across physical partitions. Queries that do not include the partition key in the filter become cross-partition queries, which must fan out to every physical partition, scanning all documents. This is extremely slow and consumes many RUs, especially in large containers. The original query specifying deviceId performs well because it targets a single partition.

Answer

The temperature field is not indexed by default, so the query forces a full scan of all documents.

Answer

The time range filter on timestamp cannot be combined with the temperature filter efficiently.

Answer

The default indexing policy only indexes strings and numbers as range indexes, but temperature is stored as a number and is indexed.

Question 6

A company stores large video files in Azure Blob Storage. The files are accessed frequently for the first 30 days after upload, then rarely for the next 180 days, and after that they are only needed for compliance but never accessed. The company wants to minimize storage costs while ensuring the files remain durable and accessible. Which strategy should they implement?

Accepted Answer

Store files initially in the Hot tier, then use lifecycle management to move files to Cool after 30 days and to Archive after 210 days.. Option B is correct because it aligns the access patterns with the appropriate Azure Blob Storage access tiers: Hot for frequent initial access, Cool for reduced-cost infrequent access after 30 days, and Archive for the lowest-cost long-term retention after 210 days. Azure lifecycle management policies automate these transitions, ensuring durability and accessibility while minimizing costs.

Answer

Store all files in the Cool access tier and apply lifecycle management to move files to the Archive tier after 30 days.

Answer

Store files in the Archive tier from the beginning to maximize cost savings.

Answer

Store files in the Premium tier for fast access, then manually delete files after 30 days.

Question 7

A startup develops a mobile application that stores user preferences as simple key-value pairs. The app is only used in North America, and the team needs low-latency reads and writes with minimal cost. They do not require global distribution or complex querying. Which Azure data store should they choose?

Accepted Answer

Azure Table Storage. Azure Table Storage is the correct choice because it provides a cost-effective, low-latency key-value store for simple data like user preferences, with no need for global distribution or complex querying. It offers single-digit millisecond latency for reads and writes within a single region, and its pay-per-request pricing model minimizes cost for a startup. The Table API in Azure Cosmos DB would be overkill and more expensive for this North America-only, non-distributed scenario.

Answer

Azure Cosmos DB (SQL API)

Answer

Azure Cosmos DB (Table API)

Answer

Azure SQL Database

Question 8

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?

Accepted Answer

Azure SQL Managed Instance. 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.

Answer

Azure SQL Database

Answer

Azure Synapse Analytics

Answer

Azure SQL Server on Azure Virtual Machines

Question 9

A company runs a customer-facing web application that uses an Azure SQL Database. The database experiences highly variable workloads: high traffic during business hours and low traffic at night and on weekends. The company wants to pay only for the compute resources consumed and automatically scale compute capacity based on demand, while maintaining the ability to pause during inactivity. Which Azure SQL Database service tier should they choose?

Accepted Answer

Serverless. The Serverless tier is designed for workloads with variable traffic and idle periods, as it automatically scales compute resources based on demand and can pause the database during inactivity, charging only for consumed compute and storage. This matches the requirement to pay only for resources used and to pause when there is no traffic, such as at night and weekends.

Answer

Hyperscale

Answer

Provisioned (General Purpose)

Answer

Business Critical

Question 10

A social media application stores user sessions as JSON documents. Each session document has fields like sessionId, userId, startTime, endTime, and a list of pageviews. The application needs to quickly retrieve a session by its sessionId and also run queries like 'find all sessions for a user in the last 24 hours' using SQL-like syntax. The data has no fixed schema; different sessions may include additional optional fields like 'deviceType' or 'promotionCode'. Which Azure data store should the company use?

Accepted Answer

Azure Cosmos DB with SQL API. Azure Cosmos DB with SQL API is the correct choice because it natively supports storing JSON documents with flexible schemas, allows fast point reads by sessionId using a unique identifier, and enables SQL-like queries (e.g., filtering by userId and startTime) with automatic indexing. Its schema-agnostic design handles optional fields like deviceType or promotionCode without requiring schema changes, and it provides low-latency reads essential for real-time session retrieval.

Answer

Azure Table Storage

Answer

Azure SQL Database

Answer

Azure Blob Storage

Question 11

A company is migrating a 500 GB on-premises SQL Server database to Azure. The database uses SQL Server Agent for scheduled maintenance jobs and requires the ability to run cross-database queries within the same logical server. The company wants a PaaS service that minimizes management overhead for patching and backups while preserving these SQL Server features. Which Azure SQL service should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including SQL Server Agent and cross-database queries within the same instance. As a PaaS service, it handles patching and backups automatically, minimizing management overhead while preserving these required features.

Answer

Azure SQL Database

Answer

SQL Server on Azure Virtual Machine

Answer

Azure Database for MySQL

Question 12

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?

Accepted Answer

Failover groups. 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.

Answer

Active Geo-Replication

Answer

Elastic pools

Answer

SQL Server on Azure Virtual Machine with Always On Availability Groups

Question 13

A company is evaluating deployment options for a new business application that requires a fully managed relational database. The application must support high availability with automatic failover and horizontal scaling for read-heavy workloads. The development team wants to minimize administrative overhead and prefers a PaaS solution that offers built-in read scale-out. Which Azure SQL deployment option should they choose?

Accepted Answer

Azure SQL Database (single database). Azure SQL Database (single database) is the correct choice because it is a fully managed PaaS relational database that supports high availability with automatic failover (99.99% SLA) and built-in read scale-out via read-only replicas (Active Geo-Replication and failover groups). It minimizes administrative overhead by handling patching, backups, and replication automatically, and it allows horizontal scaling for read-heavy workloads by offloading read traffic to secondary replicas without manual configuration.

Answer

SQL Server on Azure Virtual Machines

Answer

Azure SQL Managed Instance

Answer

Azure SQL Database Elastic Pool

Question 14

A social media company stores user posts as JSON documents in Azure Cosmos DB. Each post may have a different number of fields and nested objects. Which type of data model does this represent?

Accepted Answer

Document. The scenario describes user posts stored as JSON documents with varying fields and nested objects. Azure Cosmos DB's Document data model (using the SQL API or MongoDB API) is designed for semi-structured, schema-agnostic data where each document can have a different structure, making it the correct choice.

Answer

Key-value

Answer

Column-family

Answer

Graph

Question 15

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?

Accepted Answer

Non-clustered index on LastLoginDate. 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.

Answer

Clustered index on CustomerID

Answer

Non-clustered index on FullName

Answer

Columnstore index on SignUpDate and LastLoginDate

Question 16

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?

Accepted Answer

Hyperscale. 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.

Answer

General Purpose

Answer

Business Critical

Answer

Serverless

Question 17

A social media platform stores user profiles as JSON documents where each profile can have different attributes (e.g., education, work history, interests). The platform also needs to traverse friend connections to recommend new connections using graph queries. The development team wants to use a single Azure Cosmos DB account for both workloads while minimizing complexity. Which combination of Azure Cosmos DB APIs should they choose?

Accepted Answer

SQL API and Gremlin API. The correct choice is A because the SQL API provides native JSON document storage and querying for user profiles, while the Gremlin API supports graph traversal for friend connections. Using both APIs in a single Azure Cosmos DB account minimizes complexity by allowing each workload to use the most appropriate data model without managing separate databases.

Answer

MongoDB API and Table API

Answer

Cassandra API and SQL API

Answer

Gremlin API only

Question 18

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?

Accepted Answer

Azure SQL Database elastic pool. 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.

Answer

Azure SQL Database single database with provisioned DTUs

Answer

Azure SQL Managed Instance

Answer

SQL Server on Azure Virtual Machines

Question 19

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), and TotalAmount (decimal). The most frequent queries retrieve orders for a specific CustomerID within a date range and order the results by OrderDate. Additionally, single order lookups by OrderID must remain fast. Which indexing strategy best satisfies both requirements?

Accepted Answer

B. Create a clustered index on OrderID and a non-clustered index on (CustomerID, OrderDate).. Option B is correct because a clustered index on OrderID ensures fast single-row lookups by the primary key, while a non-clustered index on (CustomerID, OrderDate) efficiently covers the range query filtering by CustomerID and ordering by OrderDate. This design leverages the clustered index for point lookups and the non-clustered index as a covering index for the most frequent query pattern.

Answer

A. Create a clustered index on (CustomerID, OrderDate) and a non-clustered index on OrderID.

Answer

C. Create a non-clustered index on each column used in WHERE clauses: OrderID, CustomerID, and OrderDate.

Answer

D. Create a clustered columnstore index on the entire table and no other indexes.

Question 20

An e-commerce company has 20 SQL Server databases that each range from 10 GB to 50 GB and experience unpredictable usage patterns with occasional spikes in user activity. The company wants to migrate to Azure SQL Database to reduce management overhead and minimize costs by allowing databases to share resources. Which Azure SQL Database deployment option should they choose?

Accepted Answer

B: Elastic pool. Elastic pools allow multiple SQL databases to share a fixed set of resources (eDTUs or vCores), which is ideal for databases with unpredictable usage patterns and occasional spikes. By pooling resources, the company can reduce management overhead and minimize costs compared to provisioning each database individually, as the pool's total resources are shared among all databases, smoothing out peak demands.

Answer

A: Single database with provisioned throughput

Answer

C: Managed Instance

Answer

D: SQL Server on Azure VM