DP-900 Identify considerations for relational data on Azure — Timed 10 Questions

Question 1

A startup is developing a web application that requires a relational database with PostgreSQL compatibility. They want a fully managed service that automatically handles backups, patching, and provides high availability with a 99.99% SLA. Which Azure service should they choose?

10 minute time limit — choose an answer to begin.

Accepted Answer

Azure Database for PostgreSQL. Azure Database for PostgreSQL is the correct choice because it is a fully managed relational database service that offers PostgreSQL compatibility, automatic backups, patching, and high availability with a 99.99% SLA. This meets the startup's requirements for a managed PostgreSQL solution without the need for manual administration.

Answer

Azure SQL Database

Answer

Azure Database for MySQL

Answer

Azure Cosmos DB for PostgreSQL

Question 2

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 offloads read-only reporting queries to a readable secondary replica, which is kept up-to-date via asynchronous replication. While there is a small replication lag, the secondary provides transactionally consistent data with minimal delay, effectively isolating reporting workload from the primary without degrading transactional performance.

Answer

Elastic Pool

Answer

Geo-replication

Answer

SQL Server Agent

Question 3

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. The scenario requires automatic failover with minimal data loss and a readable secondary. Active Geo-Replication provides a readable secondary but requires manual or scripted failover, not automatic. Long-term retention backups, TDE, and Point-in-time restore do not provide geo-replication or automatic failover. The correct Azure feature is Auto-Failover Groups, which offers automatic failover, a single endpoint, and readable secondaries. However, this feature is not listed among the options.

Answer

Long-term retention backups

Answer

Transparent Data Encryption (TDE)

Answer

Point-in-time restore

Question 4

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 asynchronously to a secondary region, ensuring zero data loss during a planned failover (by waiting for synchronization) 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 5

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 6

A company plans to migrate an on-premises SQL Server database to Azure. The database relies on SQL Agent jobs for nightly maintenance, uses Service Broker for asynchronous messaging, and requires high CPU and memory resources. The company wants to minimize application code changes. Which two Azure SQL deployment options meet all these requirements? (Choose two.)

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance offers near-100% compatibility with on-premises SQL Server, fully supporting SQL Agent jobs and Service Broker, and provides scalable resources to meet high CPU and memory demands, thus minimizing application code changes. SQL Server on Azure Virtual Machine also meets all requirements as it is a full SQL Server instance running on an IaaS VM, providing complete feature compatibility (including SQL Agent and Service Broker) and allowing for custom VM sizing to meet any resource needs with virtually no application code changes.

Answer

Azure SQL Database Single Database

Answer

Azure SQL Database Elastic Pool

Question 7

A company uses Azure SQL Database for an e-commerce application. The Orders table contains columns: OrderID (int, primary key), CustomerID (int), OrderDate (datetime), TotalAmount (decimal). Queries frequently filter by CustomerID and OrderDate to retrieve orders for a specific customer within a date range. Queries also need to retrieve a single order by OrderID quickly. Which indexing strategy will most improve the performance of these queries?

Accepted Answer

A) Keep the clustered index on OrderID and create a non-clustered index on (CustomerID, OrderDate). The existing clustered index on OrderID efficiently supports the single-order lookup by primary key. Adding a non-clustered index on (CustomerID, OrderDate) provides a composite index seek for the range queries filtering by CustomerID and OrderDate, with CustomerID (equality) as the leading column. This index significantly improves performance compared to separate single-column indexes or a composite index with the range column first. To also avoid key lookups for queries returning TotalAmount, you would need to include TotalAmount in the index (e.g., INCLUDE (TotalAmount)), but the fundamental strategy of keeping the clustered index on OrderID and adding the composite index is still the best among the options.

Answer

B) Change the clustered index to (CustomerID, OrderDate) and create a non-clustered index on OrderID

Answer

C) Keep the clustered index on OrderID and create a non-clustered index on (OrderDate, CustomerID)

Answer

D) Keep the clustered index on OrderID and create two separate non-clustered indexes on CustomerID and OrderDate

Question 8

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).. A clustered index on OrderID ensures fast single-row lookups by the primary key. A non-clustered index on (CustomerID, OrderDate) efficiently supports the range query by filtering on CustomerID and returning rows in OrderDate order without a sort. However, this index is not covering because the query also needs Status and TotalAmount; those require key lookups. It would be more accurate to say it 'covers' the WHERE and ORDER BY logic, not all columns. This design is still the best among the options: it separates the point-lookup and range-query concerns.

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 9

A company runs a customer-facing application on an Azure SQL Database. The application experiences high read traffic from reporting queries that cause performance degradation on the primary database. The company needs a solution that offloads reporting queries without impacting the transactional workload, and the solution must keep the reporting data synchronized within seconds. Which feature should they use?

Accepted Answer

Active Geo-Replication. Active Geo-Replication creates a readable secondary replica of the Azure SQL Database in a different Azure region. This secondary replica can handle read-only reporting queries, offloading them from the primary database, and data is synchronized asynchronously with a typical lag of seconds, meeting the near-real-time synchronization requirement without impacting the transactional workload.

Answer

Auto-failover groups

Answer

Elastic Database Transactions

Answer

Database Copy

Question 10

A company has an on-premises SQL Server 2016 database that uses SQL Server Agent jobs and cross-database queries across multiple databases. They want to migrate to Azure with minimal application changes, requiring native VNet integration and near 100% compatibility with the on-premises SQL Server instance. Which Azure 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 SQL Server Agent jobs and cross-database queries, while offering native VNet integration. This minimizes application changes during migration. Azure SQL Database (single or elastic pool) does not support SQL Server Agent or cross-database queries across multiple databases, making them unsuitable. SQL Server on Azure Virtual Machines also supports these features, but it requires manual management of the VM and SQL Server, so it is a less managed option. In this scenario, Managed Instance is the best choice for a fully managed service with minimal changes.

Answer

Azure SQL Database single database

Answer

Azure SQL Database elastic pool

Answer

SQL Server on Azure Virtual Machines