DP-900 Practice Test 3 — 10 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