DP-900 Practice Test 30 — 15 Questions

Question 1

A company uses Azure SQL Database for its e-commerce platform. During a traffic spike, queries against the Orders table become slow. The table has 10 million rows and is clustered on OrderId. The most common query filters by CustomerId and OrderDate range. Which index change would most improve performance?

Accepted Answer

Create a nonclustered index on (CustomerId, OrderDate). Option D is correct because a nonclustered index on (CustomerId, OrderDate) includes the columns used in the WHERE clause, enabling a seek operation. Option A is wrong because a clustered index on OrderId does not help queries filtering by CustomerId. Option B is wrong because a nonclustered index on (OrderDate, CustomerId) is less selective if OrderDate is high-cardinality. Option C is wrong because partitioning by OrderId does not help queries filtering by CustomerId.

Answer

Create a clustered index on CustomerId

Answer

Partition the table by OrderId

Answer

Create a nonclustered index on (OrderDate, CustomerId)

Question 2

A company uses Azure SQL Database and needs to run complex analytical queries that scan large amounts of data. The queries are experiencing performance issues. Which Azure service should they use to offload the analytical workload?

Accepted Answer

Azure Synapse Analytics dedicated SQL pool. Option C is correct because Azure SQL Data Warehouse (now Synapse Analytics dedicated SQL pool) is optimized for analytical workloads. Option A is wrong because Azure Analysis Services is for semantic models, not direct query offloading. Option B is wrong because Azure SQL Database is transactional, not analytical. Option D is wrong because Azure Data Lake Storage is for data lakes, not relational analytics.

Answer

Azure SQL Database (Hyperscale tier)

Answer

Azure Analysis Services

Answer

Azure Data Lake Storage

Question 3

You are designing a relational database for an IoT application that ingests high volumes of time-stamped sensor data. The queries frequently filter by device ID and time range. Which index strategy would optimize query performance?

Accepted Answer

Create a composite index on (DeviceID, Timestamp). Option A is correct because a composite index on (DeviceID, Timestamp) supports both filters efficiently. Option B is wrong because a single index on Timestamp does not help with DeviceID filtering. Option C is wrong because a clustered index on DeviceID only helps if queries filter solely by DeviceID. Option D is wrong because indexes on non-key columns like SensorType are not helpful for the given query pattern.

Answer

Create a non-clustered index on Timestamp only

Answer

Create a clustered index on DeviceID only

Answer

Create a non-clustered index on SensorType

Question 4

Which TWO are benefits of using Azure SQL Database elastic pools?

Accepted Answer

Predictable pricing for a group of databases. Option B is correct because elastic pools allow sharing of resources across multiple databases. Option C is correct because elastic pools provide predictable pricing for a group of databases. Option A is wrong because elastic pools do not provide unlimited storage. Option D is wrong because elastic pools are not for single large databases. Option E is wrong because elastic pools are for multiple databases, not a single database.

Answer

Unlimited storage per database

Answer

Isolated performance for each database

Answer

Support for databases over 1 TB each

Question 5

Which TWO of the following are valid relational database services in Azure?

Accepted Answer

Azure SQL Database. Options A and D are correct. Azure SQL Database is a relational database as a service. Azure Database for MySQL is a relational database service. Option B is wrong because Azure Cosmos DB is a NoSQL database. Option C is wrong because Azure Data Lake Storage is a storage service. Option E is wrong because Azure Cache for Redis is an in-memory cache.

Answer

Azure Data Lake Storage

Answer

Azure Cosmos DB

Answer

Azure Cache for Redis

Question 6

A financial services company needs to store transaction data in a relational database that must comply with strict regulatory requirements. The data must be encrypted at rest and in transit, and the database must support automatic failover to a secondary region. Which Azure solution meets these requirements?

Accepted Answer

Azure SQL Database with Active Geo-Replication. Option D is correct because Azure SQL Database with Active Geo-Replication provides encryption at rest (TDE), encryption in transit (SSL), and automatic failover to a secondary region. Option A (Azure Cosmos DB) is NoSQL. Option B (Azure Database for MySQL) supports encryption but not automatic geo-failover by default. Option C (SQL Server on Azure VM) requires manual configuration for all these features.

Answer

Azure Database for MySQL

Answer

Azure Cosmos DB

Answer

SQL Server on Azure Virtual Machines

Question 7

A company is designing a relational database solution on Azure for an e-commerce platform. They need to ensure high availability and automatic failover in case of a regional outage. Which Azure service should they use?

Accepted Answer

Azure SQL Database with active geo-replication. Option C is correct because Azure SQL Database with active geo-replication provides automatic failover to a secondary region. Option A is wrong because Azure SQL Database single database does not automatically failover across regions. Option B is wrong because Azure Database for PostgreSQL with read replicas does not provide automatic failover. Option D is wrong because Azure SQL Managed Instance with local redundancy does not provide regional failover.

Answer

Azure SQL Managed Instance with local redundancy

Answer

Azure Database for PostgreSQL with read replicas

Answer

Azure SQL Database (single database)

Question 8

Which THREE factors should you consider when choosing between Azure SQL Database and Azure SQL Managed Instance?

Accepted Answer

Need for SQL Server Agent jobs. Option A is correct because SQL Managed Instance supports instance-level features like SQL Agent jobs. Option C is correct because SQL Managed Instance supports cross-database queries. Option D is correct because SQL Database has auto-scaling options like serverless. Option B is wrong because both support VNet integration. Option E is wrong because both support Microsoft Entra ID authentication.

Answer

Support for Microsoft Entra ID authentication

Answer

Requirement for VNet integration

Question 9

A company uses Azure SQL Database and needs to audit all data modifications (INSERT, UPDATE, DELETE) for compliance purposes. The audit logs must be stored for 7 years. Which feature should they enable?

Accepted Answer

SQL Database auditing. Option B is correct because SQL Database auditing logs database events to Azure storage, Log Analytics, or Event Hubs, and can be retained for long periods. Option A is wrong because TDE encrypts data, does not log modifications. Option C is wrong because vulnerability assessment scans for security issues. Option D is wrong because threat detection alerts on suspicious activities.

Answer

Advanced Threat Protection

Answer

Vulnerability assessment

Answer

Transparent Data Encryption (TDE)

Question 10

Which TWO of the following are true about Azure Database for PostgreSQL? (Select TWO.)

Accepted Answer

It supports read replicas to offload read traffic.. Options B and D are correct. Azure Database for PostgreSQL is a managed service that provides automated backups and supports read replicas. Option A is incorrect because it is not a NoSQL database. Option C is incorrect because it supports both the community and enterprise editions. Option E is incorrect because it does not support cross-database queries across different servers.

Answer

It only supports the open-source community edition of PostgreSQL.

Answer

It is a NoSQL database service.

Answer

It allows cross-database queries across multiple servers.

Question 11

A company needs to migrate a large on-premises SQL Server database to Azure. The database uses features like SQL Server Agent jobs, cross-database queries, and common language runtime (CLR) assemblies. Which Azure service supports these features?

Accepted Answer

Azure SQL Managed Instance. Option B is correct because Azure SQL Managed Instance supports SQL Agent, cross-database queries, and CLR. Option A (Azure SQL Database) does not support SQL Agent or cross-database queries. Option C (SQL Server on Azure VM) supports all features but is not a managed service. Option D (Azure Database for PostgreSQL) is a different engine.

Answer

SQL Server on Azure Virtual Machines

Answer

Azure SQL Database

Answer

Azure Database for PostgreSQL

Question 12

Which TWO Azure services can be used to host a relational database that requires native support for JSON data and high availability with automatic failover?

Accepted Answer

Azure SQL Database. Azure SQL Database and Azure Database for PostgreSQL both support JSON and provide high availability with automatic failover. Azure Cosmos DB is NoSQL. Azure SQL Managed Instance supports JSON but is not serverless by default. Azure Database for MariaDB does not have native JSON support.

Answer

Azure Database for MariaDB

Answer

Azure SQL Managed Instance

Answer

Azure Cosmos DB

Question 13

Your organization uses Azure SQL Database and needs to audit all database operations for compliance. The audit logs must be stored for at least five years and be easily searchable. What should you configure?

Accepted Answer

Enable auditing and store logs in Azure Blob Storage with a retention policy of five years.. Option D is correct because Azure SQL Database auditing can store logs in Azure Storage or Log Analytics. Long-term retention is best achieved by sending logs to Azure Storage with a retention policy. Option A (Azure Blob Storage) is the target, but the configuration is auditing. Option B (Azure Monitor) can be used but may be costly for long-term retention. Option C (Azure Sentinel) is for SIEM, not primary audit storage.

Answer

Enable auditing and store logs in Azure Monitor Logs.

Answer

Use Azure Sentinel to collect and store audit logs.

Answer

Enable Transparent Data Encryption (TDE) to track changes.

Question 14

Which THREE factors should be considered when designing a relational database in Azure to minimize latency for globally distributed users?

Accepted Answer

Configure read replicas. Geo-replication provides read replicas in multiple regions. Read-replica configurations offload read traffic. Selecting a tier with higher IOPS ensures sufficient throughput. Horizontal partitioning (sharding) adds complexity and may increase latency for cross-shard queries. Columnstore indexes are for analytics, not latency reduction.

Answer

Implement horizontal partitioning (sharding)

Answer

Use columnstore indexes

Question 15

A multinational corporation runs a mission-critical relational database on Azure SQL Database. They require automatic failover to a secondary region in case of a regional outage, with no data loss. The secondary region must also be readable for reporting purposes. What should they implement?

Accepted Answer

Failover group with a readable secondary in a different region. Option B is correct because a failover group with a readable secondary (via Active Geo-Replication) provides automatic failover and read access. Option A (Active Geo-Replication alone) requires manual failover. Option C (Read Scale-Out) does not provide geo-replication. Option D (Azure Site Recovery) is for VM replication, not database.

Answer

Active Geo-Replication with manual failover

Answer

Read Scale-Out with a local secondary replica

Answer

Azure Site Recovery for the database server