DP-900 Identify considerations for relational data on Azure — Timed 50 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?

50 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

Question 11

A company runs an e-commerce application on Azure SQL Database. The application experiences heavy read traffic from reporting dashboards that query the same tables as the transactional workload. This causes performance degradation for the application. The company needs a solution that offloads reporting queries to a read-only copy that stays synchronized within minutes, without impacting transactional performance. Which Azure SQL Database feature should they use?

Accepted Answer

Active geo-replication. Active geo-replication creates a readable secondary replica of the Azure SQL Database that can be used for read-only workloads like reporting dashboards. The secondary replica stays synchronized with the primary database using asynchronous replication, typically within seconds to minutes, ensuring near-real-time data without impacting transactional performance on the primary.

Answer

Auto-failover groups

Answer

Elastic pools

Answer

Query Performance Insight

Question 12

A financial application uses Azure SQL Database. The workload consists of a high volume of small, frequent insert operations (OLTP) and periodic complex analytical queries that scan large portions of the same table (OLAP). The table currently has a clustered columnstore index. The inserts are suffering from performance degradation. What should the company do to improve insert performance while still enabling efficient analytical queries?

Accepted Answer

Replace the clustered columnstore index with a clustered rowstore index and add a nonclustered columnstore index. A clustered rowstore index is optimized for high-volume OLTP inserts, while adding a nonclustered columnstore index allows the same table to support efficient analytical queries by providing a separate columnar structure. This hybrid approach avoids the insert overhead of columnstore indexes, which are designed for bulk operations and can suffer from small, frequent insert performance degradation due to delta store management and tuple mover processes.

Answer

Use memory-optimized tables for the entire table

Answer

Partition the table by date and move older partitions to columnstore

Answer

Keep the clustered columnstore index and use batch inserts

Question 13

Which of the following statements correctly match an Azure data security feature to its description? (Select all that apply)

Accepted Answer

Azure Key Vault: Securely stores secrets, keys, and certificates.. Correct matches: Azure Key Vault stores secrets, keys, and certificates; Azure Security Center provides unified security management; Azure Sentinel is a SIEM/SOAR solution; Azure DDoS Protection mitigates DDoS attacks. Common confusions include swapping Key Vault with Sentinel and Security Center with DDoS Protection.

Answer

Azure Key Vault: Cloud-native SIEM and SOAR solution.

Answer

Azure Security Center: Protects against distributed denial-of-service attacks.

Question 14

A company uses Azure SQL Database for a mission-critical application. They need to ensure that in the event of a regional outage, the database can be failed over to a secondary region with minimal data loss. The recovery point objective (RPO) is 5 seconds. Which deployment option should they choose?

Accepted Answer

Azure SQL Database with active geo-replication and a secondary in a paired region. Azure SQL Database active geo-replication with a secondary in a paired region supports synchronous replication, enabling a configurable RPO of 5 seconds. Option A is incorrect because failover groups use asynchronous replication by default, resulting in higher RPO (typically up to 30 seconds or more). Option C is incorrect because a single database with locally redundant storage provides no geo-redundancy and cannot protect against a regional outage. Option D is incorrect because auto-failover groups also use asynchronous replication and cannot meet the 5-second RPO requirement.

Answer

Azure SQL Database with failover group enabled

Answer

Azure SQL Database single instance with locally redundant storage

Answer

Azure SQL Database with auto-failover group

Question 15

A company has an on-premises SQL Server database that they want to migrate to Azure with minimal changes to the application. They need to use the least amount of administrative effort for patching and backups. Which Azure service should they choose?

Accepted Answer

Azure SQL Database. Azure SQL Database (Option B) is the correct choice because it is a fully managed Platform-as-a-Service (PaaS) offering that automates patching and backups, requiring the least administrative effort. Option A, Azure SQL Managed Instance, also provides automated patching and backups, but it is more suited for scenarios requiring full SQL Server instance-level compatibility and may involve slightly more administrative overhead due to instance management. Option C, SQL Server on Azure Virtual Machines, is an Infrastructure-as-a-Service (IaaS) solution that requires manual patching and backup management, resulting in higher administrative effort. Option D, Azure Database for MySQL, is a different database engine and would require application changes, contradicting the requirement for minimal changes.

Answer

Azure SQL Managed Instance

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Database for MySQL

Question 16

Which THREE considerations are important when choosing between Azure SQL Database and Azure SQL Managed Instance?

Accepted Answer

Need for SQL Server Agent. SQL Server Agent is fully supported in Azure SQL Managed Instance, enabling native job scheduling and automation, whereas Azure SQL Database does not include SQL Server Agent and requires alternative solutions like Elastic Jobs or Azure Automation. Option B is correct because Azure SQL Managed Instance supports databases up to 16 TB. Azure SQL Database (single database) has a maximum size of 4 TB for the General Purpose and Business Critical service tiers. Therefore, for databases exceeding 4 TB where the Hyperscale service tier of Azure SQL Database is not an option, Azure SQL Managed Instance is a necessary consideration. Option E is correct because Azure SQL Managed Instance provides near-100% compatibility with on-premises SQL Server, allowing lift-and-shift migrations with minimal or no application changes, whereas Azure SQL Database often requires schema and code modifications due to differences in features like CLR, Service Broker, or cross-database queries.

Answer

Requirement for elastic pools

Answer

Need for automated backups

Question 17

Refer to the exhibit. A failover group configuration is displayed. The database 'mydb' is currently on server1 in westus. What will happen if a regional outage occurs in westus?

Accepted Answer

After 60 minutes, automatic failover will occur to server2 in eastus. The failover group is configured with an automatic failover policy and a grace period of 60 minutes. In the event of a regional outage in westus, after the 60-minute grace period expires, an automatic failover will occur to the secondary server (server2) in eastus, making it the new primary. Option B is incorrect because the read-only endpoint is disabled in the configuration, and even if enabled, it would only allow read-only connections during normal operation, not during an outage. Option C is incorrect because the automatic failover will occur after the grace period without manual intervention. Option D is incorrect because the failover is not immediate; it waits the 60-minute grace period, and asynchronous replication may cause data loss.

Answer

The read-only endpoint will allow connections during the outage

Answer

The database will remain unavailable until manual action is taken

Answer

The failover group will immediately failover to server2 without data loss

Question 18

Refer to the exhibit. A database administrator runs the KQL query shown to monitor failed logouts? Actually, the query filters for 'DATABASE_LOGOUT' events. What is the primary purpose of this query?

Accepted Answer

Identify users with the highest number of logout events per hour. The provided explanation correctly justifies option C, but it relies on and introduces critical information about the KQL query's behavior ('counts DATABASE_LOGOUT events per user per hour') that should have been present in the STEM or the exhibit for the question to be self-contained and answerable.

Answer

Identify users with the most failed login attempts

Answer

Determine which users are currently connected to the database

Answer

Monitor the total number of database connections over time

Question 19

You need to run a complex T-SQL query that joins tables from Azure SQL Database and Azure SQL Managed Instance in a single query. Which feature should you use?

Accepted Answer

Linked server. Linked server (Option C) is the correct feature. Azure SQL Managed Instance fully supports linked servers, allowing you to connect to and query other SQL Server instances, including Azure SQL Database, in a single T-SQL query. This enables joining tables across these different Azure SQL services. Azure SQL Database does not support linked servers, but Azure SQL Managed Instance can initiate a linked server connection to an Azure SQL Database.

Answer

Database Mail

Answer

PolyBase

Answer

Elastic Query

Question 20

A company uses Azure SQL Database for an e-commerce application. They need to ensure that if the primary region fails, the database can be failed over to a secondary region with minimal data loss. Which feature should they enable?

Accepted Answer

Active geo-replication. Active geo-replication creates readable secondary replicas in a different region and supports failover with a configurable recovery point objective (RPO) of up to 5 seconds, minimizing data loss. Option B (point-in-time restore) is wrong because it restores a database to a specific point in time, not for region failover. Option C (auto-failover groups) is wrong because while it builds on active geo-replication, it automates failover but does not by itself reduce data loss; the core feature for minimal data loss is geo-replication. Option D (RA-GRS) is wrong because it provides geo-redundant storage for backups, not for live database failover.

Answer

Point-in-time restore

Answer

Auto-failover groups

Answer

Read-access geo-redundant storage (RA-GRS)