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

Question 1

A retail company runs an e-commerce platform on a single SQL Server database. The database experiences unpredictable spikes in read-only reporting queries that degrade transactional performance. They want to migrate to Azure SQL Database and isolate the reporting workload while ensuring it sees the most current data. Which feature should they enable?

Accepted Answer

Read Scale-out (using a readable secondary). Read Scale-out allows you to offload read-only reporting queries to a readable secondary replica, isolating them from the primary transactional workload. This ensures reporting queries see the most current data because the secondary replica is kept transactionally consistent with the primary via synchronous replication. It directly addresses the unpredictable spikes in read-only queries without degrading transactional performance.

Answer

Elastic Pool

Answer

Geo-replication

Answer

SQL Server Agent

Question 2

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?

Accepted Answer

Elastic pool. C is correct because an elastic pool allows multiple Azure SQL databases with variable and unpredictable usage patterns to share a fixed pool of resources (eDTUs or vCores), maximizing resource utilization and minimizing cost. The pool provides a predictable performance per database through per-database min/max resource limits, which is ideal for the described multi-tenant SaaS scenario with small databases and highly variable loads.

Answer

Single database with DTU purchasing model

Answer

Single database with vCore purchasing model

Answer

Azure SQL Managed Instance

Question 3

A retail company uses Azure SQL Database to store a large fact table of sales transactions with millions of rows. They run complex aggregate queries (SUM, COUNT, AVG) across many rows for monthly reports. These queries take too long. Which index type should they add to the table to improve performance?

Accepted Answer

Clustered columnstore index. Clustered columnstore indexes are optimized for large fact tables and analytical workloads because they store data in a columnar format, which significantly reduces the amount of data read from disk for aggregate queries like SUM, COUNT, and AVG. This index type also uses batch processing and compression to accelerate query performance on millions of rows, making it ideal for monthly reporting queries.

Answer

Clustered B-tree index

Answer

Nonclustered rowstore index

Answer

Nonclustered columnstore index

Question 4

A company plans to migrate a 500 GB SQL Server database from on-premises to Azure SQL Database. They require minimal downtime during the migration. Which approach should they use?

Accepted Answer

Use Azure Database Migration Service with online migration mode. Azure Database Migration Service (DMS) with online migration mode is the correct approach because it supports minimal-downtime migrations by continuously replicating ongoing changes from the source SQL Server to Azure SQL Database using the transactional replication technology. This allows the source database to remain operational during the migration, and only a brief cutover is needed at the end to switch applications to the target.

Answer

Export a BACPAC file and import to Azure SQL Database

Answer

Use Azure Site Recovery to replicate the database

Answer

Create a full database backup and restore to Azure SQL Database

Question 5

A hospital uses Azure SQL Database to store patient appointment records. The 'Appointments' table has columns: AppointmentID (primary key), PatientID, DoctorID, AppointmentDate, and Status. Queries frequently filter by DoctorID (equality) and AppointmentDate (range) to retrieve a doctor's schedule. Currently, these queries are slow. Which index strategy will most improve performance for these queries?

Accepted Answer

Add a nonclustered index on (DoctorID, AppointmentDate).. Option B is correct because a nonclustered index on (DoctorID, AppointmentDate) supports both equality filtering on DoctorID and range filtering on AppointmentDate. This index structure allows SQL Server to perform a single index seek for the doctor, then a range scan within that doctor's appointments, avoiding a full table scan. The order of columns matters: the leading column (DoctorID) handles the equality predicate, and the second column (AppointmentDate) handles the range predicate efficiently.

Answer

Add a clustered index on AppointmentID.

Answer

Add a columnstore index on the Status column.

Answer

Add a nonclustered index on (AppointmentDate, DoctorID).

Question 6

A company has an Azure SQL Database that supports a critical business application in the West US region. They want to ensure that if the primary region becomes unavailable, the database can automatically fail over to a secondary replica in the East US region with minimal data loss. The secondary replica must also be readable to offload some reporting queries when the primary is healthy. Which Azure SQL Database feature should they enable?

Accepted Answer

Active Geo-Replication. Active Geo-Replication creates a readable secondary replica in a different Azure region (East US) that stays synchronized asynchronously with the primary database in West US. In the event of a regional outage, the secondary can be manually or automatically promoted to become the new primary, with minimal data loss (typically a few seconds of transactions). The secondary replica is also readable, allowing reporting queries to be offloaded from the primary while it is healthy.

Answer

Long-term retention backups

Answer

Transparent Data Encryption (TDE)

Answer

Point-in-time restore

Question 7

A company uses Azure SQL Database for an e-commerce platform. The 'Orders' table has millions of rows with columns OrderID (primary key), CustomerID, OrderDate, and TotalAmount. Queries often filter by CustomerID (equality) and OrderDate (range). Currently, these queries are slow. Which index should be created to improve performance?

Accepted Answer

A nonclustered index on (CustomerID, OrderDate). The query pattern filters by CustomerID (equality) and OrderDate (range). A composite nonclustered index on (CustomerID, OrderDate) allows SQL Database to seek directly to the matching CustomerID rows and then efficiently scan the ordered OrderDate range within that partition, avoiding a full table scan or key lookup. This index order leverages the index's B-tree structure for both equality and range predicates.

Answer

A nonclustered index on OrderID

Answer

A nonclustered index on OrderDate

Answer

A clustered index on CustomerID

Question 8

A company runs a critical OLTP application on a single Azure SQL Database in the West US region. They need to ensure high availability with automatic failover to a secondary region in case of a regional outage. The solution must minimize data loss and allow the secondary database to be readable for reporting queries when the primary is healthy. Which feature should they enable?

Accepted Answer

Failover groups with automatic failover policy. Failover groups with an automatic failover policy enable Azure SQL Database to replicate data synchronously to a secondary region, ensuring zero data loss during a planned failover and minimal data loss during an unplanned failover. The secondary database is readable for reporting queries when the primary is healthy, meeting both the high availability and read-scale requirements. This feature is specifically designed for cross-region disaster recovery with automatic failover, unlike other options that address security, backup retention, or performance tuning.

Answer

Transparent Data Encryption (TDE)

Answer

Long-term backup retention (LTR)

Answer

Auto-tuning

Question 9

A company has an on-premises SQL Server database with a 1 TB 'Sales' table containing historical data. They want to move this table to Azure SQL Database with minimal downtime. The table is actively written to during business hours. Which approach should they use?

Accepted Answer

Use Azure Data Migration Service with continuous sync from on-premises to Azure SQL Database, then cut over. Azure Data Migration Service (DMS) with continuous sync is the correct approach because it supports online migration with minimal downtime. It uses transactional replication to keep the on-premises SQL Server database synchronized with Azure SQL Database while the source remains fully operational, allowing a controlled cutover with only seconds of downtime.

Answer

Use the Azure SQL Database migration wizard to perform an offline migration over the weekend

Answer

Export the table as a .bacpac file and import it into Azure SQL Database during off-hours

Answer

Use SQL Server Management Studio's 'Deploy Database to Azure SQL Database' wizard

Question 10

A SaaS company hosts a multi-tenant application. Each tenant has a separate Azure SQL Database. The databases are small (1-3 GB) and their workloads vary significantly over time, with some tenants active during business hours and others at night. The company wants to maximize resource utilization and minimize costs by pooling compute resources across tenants while maintaining predictable performance per database. 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 (tenants) to share a fixed pool of compute and storage resources, enabling cost efficiency through resource pooling while providing predictable performance via per-database resource limits (min/max DTU or vCore). This matches the scenario of small databases with variable, non-overlapping workloads across tenants.

Answer

Azure SQL Database Single Database

Answer

Azure SQL Managed Instance

Answer

SQL Server on Azure Virtual Machine

Question 11

A logistics company uses Azure SQL Database to store millions of shipment records. The table has columns: ShipmentID (primary key), CustomerID, ShipDate, and Destination. Queries frequently filter by CustomerID and ShipDate to retrieve shipments for a specific customer over a date range. Which indexing strategy will most improve query performance?

Accepted Answer

Create a nonclustered index on CustomerID and ShipDate. A nonclustered index on CustomerID and ShipDate is the best choice because it directly supports the frequent query pattern filtering by both columns. This composite index allows SQL Database to perform an index seek rather than a full table scan, drastically reducing I/O for selective queries over millions of rows.

Answer

Create a clustered index on ShipmentID

Answer

Partition the table by ShipmentID

Answer

Create a full-text index on Destination

Question 12

A company wants to migrate an on-premises SQL Server database to Azure. The database uses SQL Agent jobs to run nightly ETL processes and relies on Service Broker for asynchronous messaging between applications. They want to minimize changes to the application and database code. 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 on-premises SQL Server, including support for SQL Agent jobs and Service Broker. This allows the company to migrate the database with minimal code changes, as these features are not available in Azure SQL Database (single or elastic pool). SQL Server on Azure VMs would also support these features but requires more management overhead and is not a fully managed PaaS option.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database elastic pool

Answer

SQL Server on Azure Virtual Machines

Question 13

A company uses Azure SQL Database to store a large table of sales transactions with columns: TransactionID (primary key), CustomerID, ProductID, SaleDate, Amount. Queries frequently filter by both CustomerID and SaleDate to retrieve sales for a specific customer over a date range. Which indexing strategy will most improve query performance?

Accepted Answer

Create a nonclustered index on (CustomerID, SaleDate). Option C is correct because creating a nonclustered index on (CustomerID, SaleDate) as a composite index directly supports the query predicate that filters by both CustomerID and SaleDate. The index is ordered by CustomerID first, enabling efficient seeks for a specific customer, and then by SaleDate within each customer, allowing the query engine to perform a range scan for the date range without scanning the entire table or sorting. This index is a covering index for this query, as it contains all columns needed for the filter, avoiding key lookups.

Answer

Create a clustered index on SaleDate

Answer

Create a nonclustered index on CustomerID and include SaleDate

Answer

Create a nonclustered index on (SaleDate, CustomerID)

Question 14

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 15

A company maintains a large 'Transactions' table in Azure SQL Database. The table has a clustered index on a GUID column (TransactionID). Over time, they observe slow insert performance due to index fragmentation and page splits. They also need fast point lookups by TransactionID. Which approach should they take to improve insert performance while still supporting fast lookups?

Accepted Answer

Change the clustered key to an integer IDENTITY column and keep a nonclustered index on TransactionID. Option B is correct because using an integer IDENTITY column as the clustered key eliminates the random insertion order and page splits caused by a GUID clustered index, while the nonclustered index on TransactionID provides fast point lookups. In Azure SQL Database, a clustered index determines the physical order of data; a monotonically increasing integer avoids fragmentation and improves insert throughput.

Answer

Change the clustered index to a nonclustered index on TransactionID and make the table a heap

Answer

Keep the clustered index on TransactionID but rebuild it daily

Answer

Remove the clustered index entirely and create a nonclustered index on TransactionID