DP-900 Identify considerations for relational data on Azure Practice Test 2 — 15 Questions

Question 1

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 2

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?

Accepted Answer

Azure SQL Database Elastic Pool. 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.

Answer

Azure SQL Database (single database) with Serverless compute tier

Answer

Azure SQL Managed Instance

Answer

Azure SQL Database Hyperscale

Question 3

A company has 15 SQL Server databases, ranging from 50 GB to 200 GB each. The databases experience unpredictable load spikes during the day. They want to migrate to Azure SQL Database to minimize management overhead and reduce costs by allowing databases to share resources, while ensuring each database can burst to higher performance when needed. Which deployment option should they choose?

Accepted Answer

B) Elastic pool. Elastic pools allow multiple databases to share a fixed pool of resources (DTUs or vCores), which reduces costs by pooling unused capacity and enables each database to automatically burst to higher performance when needed. This matches the company's need to minimize management overhead while handling unpredictable load spikes across 15 databases ranging from 50 GB to 200 GB.

Answer

A) Single database with Provisioned throughput tier

Answer

C) SQL Managed Instance

Answer

D) SQL Server on Azure Virtual Machine

Question 4

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 5

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?

Accepted Answer

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

Answer

Azure SQL Database elastic pool

Answer

SQL Server on Azure Virtual Machine

Answer

Azure SQL Database single database

Question 6

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?

Accepted Answer

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

Answer

Serverless

Answer

Business Critical

Answer

General Purpose

Question 7

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?

Accepted Answer

vCore-based purchasing model. 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.

Answer

DTU-based purchasing model

Answer

Serverless compute tier

Answer

Hyperscale service tier

Question 8

A company uses Azure SQL Database for its e-commerce platform. The reporting team runs complex, long-running queries that join multiple tables and would degrade performance of the transactional workload if executed on the primary database. Which Azure SQL Database feature should the company enable to isolate the reporting queries while ensuring read-only access to the most current data?

Accepted Answer

Read scale-out (read-only replicas). Read scale-out (read-only replicas) allows you to offload reporting queries to a read-only replica of the Azure SQL Database, ensuring that complex, long-running queries do not degrade the performance of the primary transactional workload. The replica provides access to the most current data because it uses snapshot isolation and is transactionally consistent with the primary database.

Answer

Automatic tuning

Answer

Geo-replication

Answer

Elastic query

Question 9

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?

Accepted Answer

Auto-failover groups. Auto-failover groups (Option B) are the correct choice because they provide automatic, orchestrated failover of a primary Azure SQL Database to a secondary region (East US) during an outage, meeting the RPO of less than 5 seconds (typically 5–10 seconds for active geo-replication) and RTO of less than 30 minutes (usually under 1 hour). The secondary database can be used for read-only queries, and the failover group ensures the entire group of databases fails over as a unit, maintaining the same connection string.

Answer

Active geo-replication

Answer

Geo-restore

Answer

Transactional replication

Question 10

A company has 12 SQL Server databases, each about 30 GB. The databases experience unpredictable load spikes during the day. The company wants to migrate to Azure SQL Database to reduce administrative overhead and optimize costs by sharing resources among the databases. Which deployment option should they choose?

Accepted Answer

Elastic pool. Elastic pools are designed to share resources (eDTUs or eVCores) among multiple databases with unpredictable, overlapping load spikes. By pooling resources, the company can optimize costs because databases do not all peak simultaneously, and the pool’s total resource allocation is lower than the sum of individual peak requirements. This reduces administrative overhead by providing a single management point for scaling and monitoring all databases in the pool.

Answer

Single database with provisioned DTU

Answer

Managed Instance

Answer

SQL Server on Azure Virtual Machine

Question 11

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

Question 12

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?

Accepted Answer

vCore-based model with General Purpose serverless tier. 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.

Answer

DTU-based model with Basic tier

Answer

vCore-based model with Business Critical provisioned tier

Answer

DTU-based model with Standard tier

Question 13

A company is migrating a SQL Server database to Azure SQL Database. The database uses CLR (Common Language Runtime) integration for business logic and has database mail configured. The company needs full instance-level functionality while still benefiting from the platform-as-a-service model. Which Azure SQL deployment option should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with SQL Server on-premises, including support for CLR integration and Database Mail, while still offering a platform-as-a-service (PaaS) model. Single databases and elastic pools lack these instance-scoped features, and Hyperscale is a scaling option for single databases, not a separate deployment type that adds instance-level functionality.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database elastic pool

Answer

Azure SQL Database Hyperscale

Question 14

A company has 15 on-premises SQL Server databases, each 20–40 GB, running on a single instance. They rely on cross-database queries using three-part names (e.g., DB1.dbo.table) and SQL Server Agent for maintenance. They want to migrate to Azure with minimal application changes and reduce administrative overhead. Which Azure SQL deployment option 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 support for cross-database queries using three-part names and SQL Server Agent. This allows the company to migrate with minimal application changes while offloading administrative overhead like patching and backups, which are handled by Azure.

Answer

Azure SQL Database elastic pool

Answer

Azure SQL Database single database

Answer

SQL Server on Azure Virtual Machines

Question 15

A company has an Azure SQL Database with an 'Orders' table containing millions of rows. The table has a clustered index on OrderID (primary key). Queries frequently filter by CustomerID (equality) and OrderDate (range). These queries are slow and cause high logical reads. Which index strategy will most improve performance for these specific queries?

Accepted Answer

Create a non-clustered index on (CustomerID, OrderDate).. A non-clustered index on (CustomerID, OrderDate) is a covering index for queries filtering by CustomerID (equality) and OrderDate (range). It allows SQL Server to perform an index seek on CustomerID, then a range scan on OrderDate, retrieving all needed columns without touching the clustered index (if the query is covered). This dramatically reduces logical reads compared to a full clustered index scan or a key lookup.

Answer

Rebuild the clustered index on (OrderDate, CustomerID).

Answer

Create a non-clustered index on OrderDate.

Answer

Create a filtered index on OrderDate for recent dates.