DP-900 Practice Exam 51 — 20 Questions

Question 1

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?

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

Accepted Answer

Azure SQL Database with active geo-replication. Azure SQL Database with active geo-replication (Option A) is the correct choice because it enables automatic failover to a secondary region in case of a regional outage, meeting the high availability requirement. Option B (Azure SQL Managed Instance with local redundancy) only provides local redundancy within a single region, not cross-region failover. Option C (Azure Database for PostgreSQL with read replicas) does not provide automatic failover; read replicas are for read scaling, not failover. Option D (Azure SQL Database single database) by default does not have automatic regional failover; active geo-replication is needed for that.

Answer

Azure SQL Managed Instance with local redundancy

Answer

Azure Database for PostgreSQL with read replicas

Answer

Azure SQL Database (single database)

Question 2

A company stores customer data in a SQL table with fixed columns (CustomerID, Name, Email, SignupDate). They also store product images as JPEG files and application logs as JSON documents. Which of the following correctly classifies each data type?

Accepted Answer

SQL table: structured, JPEG: unstructured, JSON: semi-structured. A SQL table with fixed columns enforces a rigid schema, making it structured data. JPEG files are binary blobs with no internal schema, classifying them as unstructured. JSON documents use key-value pairs with flexible schemas, which is the definition of semi-structured data.

Answer

SQL table: structured, JPEG: semi-structured, JSON: unstructured

Answer

SQL table: semi-structured, JPEG: unstructured, JSON: structured

Answer

SQL table: unstructured, JPEG: structured, JSON: semi-structured

Question 3

Refer to the exhibit. You are reviewing an Azure Cosmos DB account configuration. Which API is this account configured to use?

Accepted Answer

Cassandra API. The 'capabilities' array includes 'EnableCassandra', which indicates the Cassandra API is enabled. Option C is correct. Option A (Table API) would have 'EnableTable', Option B (SQL/Core API) would have 'EnableSQL' or no capability, and Option D (MongoDB API) would have 'EnableMongo'.

Answer

Table API

Answer

SQL (Core) API

Answer

MongoDB API

Question 4

Which TWO of the following are characteristics of Azure Blob Storage?

Accepted Answer

Supports access tiers (Hot, Cool, Archive). Options B and C are correct. Azure Blob Storage supports access tiers (Hot, Cool, Archive) to optimize cost (B) and can store large binary objects like videos (C). Option A is incorrect because Blob Storage does not enforce a fixed schema; it stores unstructured data. Option D is incorrect because ACID transactions across multiple records are a feature of relational databases, not Blob Storage. Option E is incorrect because Blob Storage supports block blobs, append blobs, and page blobs.

Answer

Enforces a fixed schema for stored data

Answer

Provides ACID transactions across multiple records

Answer

Only supports block blobs

Question 5

A DBA runs the following KQL query in Azure Monitor for an Azure SQL Database: `AzureDiagnostics | where Category == 'QueryStoreRuntimeStatistics'`. The query returns no results. What is the most likely reason?

Accepted Answer

Query Store is not enabled on the database. That Query Store is not enabled on the database. Query Store must be enabled for each database to capture and store query runtime statistics. The KQL query likely targets the Query Store data, which requires Query Store to be active. Option B is incorrect because AzureDiagnostics can contain SQL data, but the issue is that the query is looking for Query Store data, not diagnostics. Option C is incorrect because the category name is correct in the query. Option D is incorrect because the KQL syntax is valid.

Answer

The AzureDiagnostics table does not contain SQL data

Answer

The category name is misspelled

Answer

The KQL syntax is incorrect

Question 6

Your application stores millions of small log entries in Azure Table Storage. Queries by partition key and row key are fast, but you also need to query by timestamp across partitions. The query performance is slow. What is the best way to improve query performance?

Accepted Answer

Create a new table with the timestamp as the partition key and copy data there for time-based queries.. Creating a separate table with timestamp as the partition key allows efficient range queries across all data. Option A (migrate to Azure Cosmos DB) is wrong because Cosmos DB is significantly more expensive and may be overkill for this scenario. Option B (Azure Cognitive Search) is wrong because it is designed for full-text search, not for optimizing time-based queries on Table Storage. Option C is correct as it enables partition-level queries by timestamp. Option D is wrong because Table Storage does not support secondary indexes.

Answer

Migrate the data to Azure Cosmos DB and use the SQL API.

Answer

Use Azure Cognitive Search to index the Table Storage data.

Answer

Add a secondary index on the timestamp column.

Question 7

A company stores sensor data in Azure Blob Storage. The data is appended every minute and rarely modified. The compliance team requires that blobs older than 90 days be moved to a more cost-effective storage tier, and blobs older than 365 days be deleted. Which solution should you recommend?

Accepted Answer

Configure a Blob Storage lifecycle management policy.. A lifecycle management policy can automatically transition blobs to cooler storage tiers (e.g., Cool after 90 days) and delete blobs after 365 days. This directly meets the compliance requirements. Option A is incorrect because Azure Backup is for backing up data, not for lifecycle management. Option C is incorrect because Soft Delete is for recovery from accidental deletion, not for automated tiering or deletion. Option D is incorrect because Azure Files is a file share service, not designed for automated lifecycle policies on blobs.

Answer

Use Azure Backup to set a retention policy for the storage account.

Answer

Enable Blob Soft Delete and set retention days to 365.

Answer

Move the blobs to Azure Files and set a file retention policy.

Question 8

Your application uses Azure Table storage to store user preferences. You need to retrieve all preferences for a specific user quickly. Which key should you use as the partition key?

Accepted Answer

UserID. Using UserID as the partition key ensures that all preferences for a specific user are stored in the same partition, enabling fast and efficient point queries. Region, Random GUID, and Timestamp would scatter user data across multiple partitions, slowing down retrieval.

Answer

Region

Answer

Random GUID

Answer

Timestamp

Question 9

Which THREE factors should you consider when choosing between Azure Blob Storage and Azure Cosmos DB for a new application? (Choose three.)

Accepted Answer

Global distribution and multi-region writes. (Global distribution and multi-region writes) is correct because Azure Cosmos DB supports global distribution with multi-region writes, while Azure Blob Storage does not offer multi-region writes. Option B (Data structure) is correct because Blob Storage is designed for unstructured data (blobs), whereas Cosmos DB handles semi-structured data (JSON documents) with flexible schema. Option E (Query capabilities) is correct because Cosmos DB supports complex queries (e.g., SQL, MongoDB API), while Blob Storage primarily offers key-value access by blob name. Option C (Encryption at rest) is incorrect because both services support encryption at rest. Option D (Scalability limits) is incorrect because both services are highly scalable.

Answer

Encryption at rest support

Answer

Scalability limits

Question 10

Which TWO of the following are true about Azure Cosmos DB?

Accepted Answer

It guarantees single-digit millisecond latency for reads and writes at the 99th percentile.. Azure Cosmos DB is a globally distributed, multi-model database. Options C and E are correct. C is correct because Cosmos DB guarantees single-digit millisecond latency for reads and writes at the 99th percentile. E is correct because it supports multiple data models including document, key-value, graph, and column-family. Option A is incorrect because the default consistency level is Session, not Strong. Option B is incorrect because Cosmos DB uses provisioned throughput (RU/s), not DTUs. Option D is incorrect because Cosmos DB is not a relational database; it is a non-relational (NoSQL) database that supports multiple APIs.

Answer

The default consistency level is Strong.

Answer

It uses DTUs to measure performance.

Answer

It is a relational database management system.

Question 11

Which TWO Azure services can be used to host a relational database that is compatible with SQL Server?

Accepted Answer

Azure SQL Database. Options A and D are correct. Azure SQL Database and Azure SQL Managed Instance both provide SQL Server compatibility. Option B (Azure Database for PostgreSQL) is wrong because it is PostgreSQL, not SQL Server. Option C (Azure Cosmos DB) is wrong because it is NoSQL. Option E (Azure Database for MySQL) is wrong because it is MySQL.

Answer

Azure Database for PostgreSQL

Answer

Azure Cosmos DB

Answer

Azure Database for MySQL

Question 12

You are designing a solution to store and analyze large volumes of streaming data from social media feeds. The data is semi-structured (JSON) and will be used for real-time dashboards. You need to choose a storage solution that can handle high-ingestion throughput and support querying with Azure Synapse Serverless SQL. Which storage option should you choose?

Accepted Answer

Azure Data Lake Storage Gen2. (Azure Data Lake Storage Gen2) is correct because it is built on Azure Blob Storage, supports high-throughput ingestion of streaming data, and can be directly queried using Azure Synapse Serverless SQL. Option A (Azure Table Storage) is wrong because it is designed for structured NoSQL key-value data, not for analytics or semi-structured JSON. Option C (Azure Cosmos DB) is optimized for transactional workloads and real-time applications; although it can be integrated with Synapse via Synapse Link, it is not the primary choice for direct Serverless SQL queries on streaming data. Option D (Azure Cache for Redis) is an in-memory cache, not a durable storage solution for analytics.

Answer

Azure Table Storage

Answer

Azure Cosmos DB

Answer

Azure Cache for Redis

Question 13

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 provides automatic failover and read-scale capabilities, meeting the requirements for high availability and read-scale for reporting. Option A is wrong because Hyperscale tier is designed for very large databases but does not provide read-scale for reporting workloads (it does have readable replicas but typically not for reporting scale-out). Option B is wrong because Azure Database for PostgreSQL is not a target for migrating a SQL Server database; it's a different database engine. Option C is wrong because SQL Server on Azure VMs with Always On availability groups requires significant manual configuration and management, which does not meet the automatic failover requirement as seamlessly as Azure SQL Database Business Critical tier.

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 14

A development team is designing an application that stores user session data in Azure Cosmos DB. Each session document contains a sessionId (unique), userId, timestamp, and a JSON field 'metadata' that can include various optional properties. The application frequently queries by userId to retrieve all sessions for a particular user. Which property should be chosen as the partition key to optimize query performance and ensure even data distribution?

Accepted Answer

userId. The partition key should be the property most frequently used in queries and that provides high cardinality for even distribution. Since the application frequently queries by userId to retrieve all sessions for a user, choosing userId as the partition key ensures that all session documents for a given user are stored in the same logical partition, making these queries efficient and fast. Additionally, userId typically has a large number of distinct values, which promotes even data distribution across physical partitions.

Answer

sessionId

Answer

timestamp

Answer

metadata

Question 15

Which THREE are benefits of using Azure SQL Database serverless compute tier?

Accepted Answer

Billing per second for compute usage. Azure SQL Database serverless compute tier offers billing per second for compute usage (B), auto-scaling compute based on workload (C), and auto-pause during periods of inactivity (D). These features provide cost savings and automatic scaling for intermittent workloads. However, serverless does not guarantee a 99.99% SLA (A) and is not ideal for high-throughput, latency-sensitive applications (E) due to potential cold starts.

Answer

Guaranteed high availability with 99.99% SLA

Answer

Ideal for high-throughput, latency-sensitive applications

Question 16

Your team needs to store JSON documents that require schema flexibility and global distribution. Which Azure data store should you choose?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is a globally distributed NoSQL database that natively supports JSON documents with flexible schema. Option A is wrong because Azure Table Storage is for key-value data, not document storage. Option B is wrong because Azure SQL Database is relational and enforces schema. Option C is wrong because Azure Blob Storage stores unstructured data but does not provide a query interface for JSON documents.

Answer

Azure Table Storage

Answer

Azure SQL Database

Answer

Azure Blob Storage

Question 17

Which THREE of the following are valid considerations when choosing between Azure SQL Database and Azure SQL Managed Instance?

Accepted Answer

Azure SQL Managed Instance supports SQL Server Agent for job scheduling.. Options A, C, and E are correct. Azure SQL Managed Instance supports SQL Server Agent (A) and cross-database queries (C) because it shares more features with on-premises SQL Server. Azure SQL Database supports elastic pools (E) for sharing resources across databases. Option B is incorrect because Azure SQL Managed Instance supports larger database sizes than Azure SQL Database (up to 16 TB vs. up to 4 TB for single databases in SQL Database). Option D is incorrect because Azure SQL Managed Instance always runs within a virtual network, so it supports VNet integration by default.

Answer

Azure SQL Database supports larger database sizes than Azure SQL Managed Instance.

Answer

Azure SQL Managed Instance does not support virtual network integration.

Question 18

An organization stores sensitive customer data in Azure SQL Database. They need to encrypt the data at rest and ensure that only authorized applications can decrypt it. Which combination of features should they implement?

Accepted Answer

Transparent Data Encryption (TDE) and Always Encrypted. TDE encrypts the database at rest, and Always Encrypted ensures that only authorized applications with the column encryption key can decrypt sensitive columns. Option A is wrong because row-level security controls access but does not encrypt data. Option B is wrong because auditing does not control decryption. Option D is wrong because dynamic data masking obfuscates data but does not encrypt.

Answer

Row-Level Security (RLS) and Transparent Data Encryption (TDE)

Answer

Azure SQL Database Auditing and Transparent Data Encryption (TDE)

Answer

Dynamic Data Masking and Always Encrypted

Question 19

Refer to the exhibit. This ARM template snippet is used to deploy which Azure resource?

Accepted Answer

Azure SQL Database server. The ARM template snippet includes properties such as 'serverName', 'administratorLogin', and 'administratorLoginPassword', which are specific to an Azure SQL Database server deployment. Option A is incorrect because an Azure Database for MySQL server uses different properties like 'mysqlVersion' and 'storageProfile'. Option B is incorrect because Azure SQL Managed Instance requires properties like 'vCores', 'storageSizeInGB', and 'licenseType'. Option D is incorrect because Azure Synapse Analytics workspace deployments use properties like 'sqlAdministratorLogin' and 'defaultDataLakeStorage' but with different structure.

Answer

Azure Database for MySQL server

Answer

Azure SQL Managed Instance

Answer

Azure Synapse Analytics workspace

Question 20

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 (Option A) provides more compute resources to handle peak load without requiring application code changes, which directly reduces latency. Option B (add a read replica) does not help with write latency or improve performance for the primary workload. Option C (enable auto-pause) is for serverless databases and does not address performance during active usage. Option D (change to General Purpose) would likely reduce performance compared to Business Critical.

Answer

Add a read replica

Answer

Enable auto-pause

Answer

Change the service tier to General Purpose