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

Question 1

A library management system uses Azure SQL Database. The Books table has 500,000 rows with columns: BookID (primary key, clustered), Title, Author, ISBN, PublishedYear, CopiesAvailable. Queries frequently filter by Author and then sort results by PublishedYear in descending order. Which indexing strategy will most improve query performance?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Create a nonclustered index on (Author, PublishedYear DESC) INCLUDE (CopiesAvailable, Title, ISBN).. It creates a covering index that matches the exact query pattern: filtering by Author and sorting by PublishedYear in descending order. By defining (Author, PublishedYear DESC) as the index key, SQL Server can perform an index seek on Author and an ordered scan on PublishedYear without a separate sort operation. Including the remaining columns (CopiesAvailable, Title, ISBN) as non-key columns makes the index covering, eliminating the need for key lookups to the clustered index.

Answer

Create a nonclustered index on (Author) INCLUDE (PublishedYear, CopiesAvailable, Title).

Answer

Create a nonclustered index on (PublishedYear) INCLUDE (Author).

Answer

Create a nonclustered columnstore index on (Author, PublishedYear, CopiesAvailable, Title, ISBN).

Question 2

A company uses Azure SQL Database for an order management system. The Orders table has millions of rows with columns: OrderID (primary key, clustered), CustomerID, OrderDate, Status (e.g., 'Shipped', 'Pending'), TotalAmount. Queries frequently filter on OrderDate and Status, and sort results by OrderDate in descending order. They return several columns including TotalAmount. Which indexing strategy will most improve query performance?

Accepted Answer

Create a nonclustered index on (OrderDate DESC, Status) INCLUDE (CustomerID, TotalAmount). It creates a covering nonclustered index that matches the query's filter and sort order exactly. The index on (OrderDate DESC, Status) allows SQL Server to seek directly on OrderDate and Status, and the descending order avoids a sort operation for the ORDER BY OrderDate DESC clause. Including CustomerID and TotalAmount as included columns makes this a covering index, so the query can be satisfied entirely from the index without key lookups to the clustered index, which is critical for performance on a table with millions of rows.

Answer

Create a clustered index on (OrderDate, Status)

Answer

Create a nonclustered index on (Status) only

Answer

Create a columnstore index on the entire table

Question 3

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 4

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. Option B is correct because active geo-replication for Azure SQL Database asynchronously replicates transactions to up to four readable secondaries in different regions, and its typical replication lag is under 5 seconds, which satisfies the 5-second RPO for a regional failover scenario. Option A is incorrect because a failover group is a grouping mechanism built on top of geo-replication that provides automatic failover for a set of databases, but the question asks for the deployment option that meets the RPO, and the underlying capability is active geo-replication with a secondary in a paired region. Option C is incorrect because locally redundant storage keeps all replicas within a single region and provides no cross-region failover capability. Option D is incorrect because auto-failover groups are the same feature as failover groups and are not the specific deployment option that establishes the cross-region secondary with sub-5-second replication lag.

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 5

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 PaaS database engine that provides automatic patching, backups, and high availability with minimal administrative effort, and it supports migrating on-premises SQL Server databases with minimal application changes via tools like Azure Database Migration Service or Data Migration Assistant. The other options do not fit as well: Azure SQL Managed Instance (A) is also PaaS but is typically chosen when near-100% SQL Server compatibility, instance-level features, or cross-database queries are required, and it involves more configuration and cost overhead than needed here. SQL Server on Azure Virtual Machines (C) is IaaS, so the customer remains responsible for OS and SQL Server patching, backups, and high availability, which contradicts the minimal-administration requirement. Azure Database for MySQL (D) is a different database engine and would require application and schema changes, so it is not suitable for migrating a SQL Server database.

Answer

Azure SQL Managed Instance

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Database for MySQL

Question 6

A company uses Azure SQL Database with the Business Critical service tier. They notice increased latency during peak hours. They need to improve performance without changing the application code. Which action should they take?

Accepted Answer

Increase the number of vCores. Increasing the number of vCores directly scales the compute resources (CPU, memory, and I/O) available to the Azure SQL Database, which reduces latency during peak load. The Business Critical tier already provides the lowest latency due to local SSD and in-memory OLTP, so scaling up vCores is the most effective no-code change. This is a vertical scaling operation that can be done online with minimal downtime.

Answer

Add a read replica

Answer

Enable auto-pause

Answer

Change the service tier to General Purpose

Question 7

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

Accepted Answer

Need for SQL Server Agent. Option A (Need for SQL Server Agent) is correct because SQL Server Agent job scheduling is natively available in Azure SQL Managed Instance, whereas Azure SQL Database does not include SQL Server Agent and instead requires Elastic Jobs or Azure Automation for scheduled tasks. Option B (Database size greater than 4 TB) is correct because Azure SQL Database supports a maximum database size of 4 TB (in the Hyperscale tier), while Azure SQL Managed Instance supports up to 16 TB of storage per instance, making it the appropriate choice when a single database exceeds 4 TB. Option E (Lift-and-shift migration with minimal changes) is correct because Azure SQL Managed Instance provides near-100% compatibility with on-premises SQL Server, including support for cross-database queries, CLR, linked servers, and SQL Server Agent, which enables lift-and-shift migrations with minimal code changes; Azure SQL Database requires application changes due to its more limited surface area. Option C (Requirement for elastic pools) is not a differentiator because elastic pools are a feature of Azure SQL Database and are not supported in Azure SQL Managed Instance, so it does not represent a consideration for choosing between the two in the way the correct options do. Option D (Need for automated backups) is not a differentiator because both Azure SQL Database and Azure SQL Managed Instance provide automated backups by default, so it is not a distinguishing consideration.

Answer

Requirement for elastic pools

Answer

Need for automated backups

Question 8

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 correct option is A: after 60 minutes, automatic failover will occur to server2 in eastus, because the failover group is configured with automatic failover policy and a 1-hour grace period, so a regional outage in westus triggers automatic failover to the secondary server in eastus after that grace period. The read-only endpoint (B) is not the failover mechanism and does not by itself keep the database writable during a regional outage. Manual action (C) is not required when automatic failover is configured, and immediate failover without data loss (D) is incorrect because the configured grace period delays failover and potential data loss depends on the replication mode.

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 9

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 10

A company is migrating a large on-premises SQL Server database to Azure. They require high availability with automatic failover and read-scale for reporting workloads. Which Azure service should they use?

Accepted Answer

Azure SQL Database Business Critical tier with failover groups. Azure SQL Database Business Critical tier with failover groups is correct because it provides high availability with automatic failover and read-scale via readable secondary replicas. Failover groups enable geo-replication and automatic failover across regions. Hyperscale does not provide built-in read-scale with automatic failover in the same way. SQL Server on VMs requires manual configuration of Always On, and PostgreSQL does not meet the SQL Server requirement.

Answer

Azure SQL Database Hyperscale tier

Answer

Azure Database for PostgreSQL with geo-redundant backup

Answer

SQL Server on Azure Virtual Machines with Always On availability groups

Question 11

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 12

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 (option A) is correct because it lets you create up to four readable secondaries in different Azure regions and initiate a manual failover to any secondary, providing cross-region disaster recovery with minimal data loss for Azure SQL Database. It is designed for the scenario where the primary region fails and you need to fail over to a secondary region. Point-in-time restore (B) only restores a database to an earlier point in time within the same region and does not provide cross-region failover. Auto-failover groups (C) also support cross-region failover, but the question asks for the feature to enable, and active geo-replication is the specific Azure SQL Database feature for this purpose; auto-failover groups are a higher-level abstraction that can include geo-replicated databases. RA-GRS (D) is a storage redundancy option and does not apply to Azure SQL Database failover.

Answer

Point-in-time restore

Answer

Auto-failover groups

Answer

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

Question 13

A multinational corporation needs to deploy a relational database in Azure that supports global distribution and low-latency reads and writes across multiple regions. The data model is primarily key-value with occasional relational queries. Which Azure service should they choose?

Accepted Answer

Azure SQL Database with active geo-replication. Azure SQL Database with active geo-replication is the best choice among the options because it enables global distribution with readable replicas in multiple regions for low-latency reads, and a single writable primary that can be placed in the region with the highest write volume to minimize write latency. While it does not support multi-region writes, it meets the requirement of low-latency reads across regions and supports relational and key-value queries. Option D is incorrect because Azure Cosmos DB's PostgreSQL-compatible API is not a fully relational database, and its NoSQL API may not support relational queries efficiently. Options B and C are region-bound and do not provide the same global distribution capabilities.

Answer

Azure SQL Managed Instance

Answer

Azure Database for PostgreSQL – Hyperscale (Citus)

Answer

Azure Cosmos DB with PostgreSQL-compatible API

Question 14

Which TWO features are available in Azure SQL Database to improve performance without changing application code?

Accepted Answer

Automatic tuning. Options A and C are correct. Automatic tuning automatically adjusts indexes and query plans based on workload patterns, directly improving performance without requiring application code changes. Query Store collects detailed query runtime statistics and execution plans, enabling database administrators to identify and resolve performance regressions by forcing optimal plans or suggesting index changes, thereby improving performance without requiring modifications to the application's code. Option B (Columnstore indexes) requires altering table definitions, which is a schema change. Option D (Read replicas) requires application modifications to route read traffic to replicas. Option E (Elastic pools) is a resource management feature for scaling multiple databases, not a direct performance improvement feature for a single database.

Answer

Columnstore indexes

Answer

Read replicas

Answer

Elastic pools

Question 15

A company uses the above ARM template snippet to deploy an Azure SQL Database. The deployment fails with an error about invalid SKU. What is the most likely cause?

Accepted Answer

The capacity value must be a multiple of 4. The deployment would not fail because of a capacity multiple requirement. 'GP_Gen5_2' is a valid vCore SKU for the GeneralPurpose tier with 2 vCores, and Azure SQL Database supports 2 vCores in the GeneralPurpose tier. Therefore none of the listed options is correct; the question is invalid as written.

Answer

The SKU name 'GP_Gen5_2' is not valid for the 'GeneralPurpose' tier

Answer

The 'tier' property should be 'Standard' instead of 'GeneralPurpose'

Answer

The location 'eastus' does not support GeneralPurpose tier