DP-900 Practice Test 4 — 10 Questions

Question 1

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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