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

Question 1

A healthcare organization stores patient records in Azure SQL Database. To comply with HIPAA, they need to encrypt sensitive columns like Social Security Numbers (SSNs) at rest and ensure that only authorized users can decrypt them. Which feature should they implement?

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

Accepted Answer

Always Encrypted. Always Encrypted (A) is correct because it encrypts sensitive columns such as SSNs at rest and keeps the encryption keys outside the database, so only clients with access to the column master key can decrypt the data. This satisfies HIPAA's requirement for column-level protection and strict control over who can decrypt values. TDE (B) encrypts the entire database at rest but does not provide column-level encryption or restrict decryption to specific authorized users. Dynamic Data Masking (C) only obfuscates data in query results and does not encrypt stored values, and Row-Level Security (D) filters rows by user context rather than encrypting columns.

Answer

Transparent Data Encryption (TDE)

Answer

Dynamic Data Masking

Answer

Row-Level Security

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. Azure Synapse Analytics dedicated SQL pool is designed for large-scale analytical workloads, using a massively parallel processing (MPP) architecture that distributes data across 60 distributions and executes queries in parallel. This offloads complex analytical queries from Azure SQL Database, which uses a single-node SQL Server engine optimized for OLTP, not heavy scanning.

Answer

Azure SQL Database (Hyperscale tier)

Answer

Azure Analysis Services

Answer

Azure Data Lake Storage

Question 3

A multinational corporation has Azure SQL Databases deployed in multiple Azure regions. They need to ensure that a failover to a secondary region happens automatically and with minimal data loss if the primary region goes down. Which deployment option should they use?

Accepted Answer

Active geo-replication with auto-failover groups. The correct option is A, Active geo-replication with auto-failover groups, because auto-failover groups provide a read-write listener endpoint and allow Azure SQL Database to automatically fail over to a secondary region if the primary region becomes unavailable, with asynchronous replication that minimizes data loss. This directly matches the requirement for automatic cross-region failover with minimal data loss. Option B, zone-redundant databases, only protects against availability zone failures within a single region and does not provide cross-region failover. Option C, Azure Traffic Manager with multiple databases, can route traffic but does not itself perform database replication or automatic database failover. Option D, locally redundant storage (LRS), only replicates data within a single datacenter/region and offers no cross-region disaster recovery.

Answer

Zone-redundant database

Answer

Azure Traffic Manager with multiple databases

Answer

Locally redundant storage (LRS)

Question 4

A small business needs a cost-effective relational database for a new web application. The workload is light and predictable. They want to minimize administrative overhead. Which Azure service should they choose?

Accepted Answer

Azure SQL Database serverless. Azure SQL Database serverless is the correct choice because it automatically pauses the database during periods of inactivity, charging only for storage and compute used. This aligns perfectly with the small business's need for a cost-effective, low-administration relational database for a light, predictable workload, as it eliminates the need to manage infrastructure or pay for idle compute.

Answer

Azure Database for PostgreSQL

Answer

SQL Server on Azure Virtual Machines

Answer

Azure SQL Database (provisioned DTU)

Question 5

A company uses Azure SQL Database and needs to implement data masking for sensitive columns like email addresses and credit card numbers, so that only authorized users can see the actual data. Which feature should they configure?

Accepted Answer

Dynamic Data Masking. Dynamic Data Masking (DDM) is the correct feature because it limits exposure of sensitive data by obfuscating columns (e.g., email addresses, credit card numbers) in query results to non-privileged users, while authorized users (with EXEMPT or UNMASK permission) see the actual data. This directly meets the requirement of masking sensitive columns without altering the underlying stored data.

Answer

Row-Level Security

Answer

Auditing

Answer

Transparent Data Encryption (TDE)

Question 6

Which TWO factors should you consider when choosing between Azure SQL Database and SQL Server on Azure Virtual Machines?

Accepted Answer

Compatibility with on-premises SQL Server features. Option C is correct because SQL Server on Azure Virtual Machines runs the full SQL Server engine on an IaaS VM, so it supports the broadest compatibility with on-premises SQL Server features (for example, cross-database queries, SQL Server Agent, CLR, FILESTREAM, and specific legacy features) that Azure SQL Database may not fully support in its PaaS model. Option E is correct because the two services differ fundamentally in administrative control: SQL Server on Azure VMs gives you OS-level and instance-level control (patching, configuration, sysadmin rights, custom settings), while Azure SQL Database is a fully managed PaaS where Microsoft handles most administration and limits control. Options A, B, and D are not the deciding factors: auditing, geo-replication, and Transparent Data Encryption are capabilities available in both Azure SQL Database and SQL Server on Azure VMs, so they do not differentiate the two choices.

Answer

Need for database auditing

Answer

Support for geo-replication

Answer

Need for Transparent Data Encryption

Question 7

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). A composite index on (DeviceID, Timestamp) directly supports the two most common filter predicates in the query workload: DeviceID (for equality) and Timestamp (for range scans). In SQL Server, a composite index with DeviceID as the leading column allows the query engine to perform an index seek on DeviceID and then a range scan on Timestamp, minimizing I/O and avoiding key lookups. This strategy is optimal for time-series IoT data where queries almost always specify a device and a time window.

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 8

Your company has an Azure SQL Database that stores customer orders. You notice that long-running reports are causing blocking on the transactional tables. Which approach would minimize impact on transaction processing while still allowing reporting?

Accepted Answer

Create a read-only replica in Azure SQL Database Hyperscale. Creating a read-only replica offloads reporting queries to a separate copy, avoiding blocking on the primary transactional tables. Option A is wrong because READ UNCOMMITTED reduces blocking but risks dirty reads and inconsistent data. Option B is wrong because increasing the service tier to Business Critical provides more resources but does not separate reporting load. Option C is wrong because sharding distributes data across databases but does not prevent blocking; reporting queries still run on the same shards as transactional queries.

Answer

Use the READ UNCOMMITTED isolation level for reporting queries

Answer

Increase the service tier to Business Critical

Answer

Shard the database by customer region

Question 9

You need to migrate an on-premises SQL Server database to Azure. The database uses many stored procedures and CLR assemblies. Which Azure service is most compatible without requiring major application changes?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance (D) is the most compatible choice because it provides near 100% compatibility with the on-premises SQL Server engine, including full support for CLR assemblies and stored procedures. Unlike Azure SQL Database, Managed Instance does not require rewriting or refactoring code that relies on SQL Server Agent, cross-database queries, or CLR integration, making it ideal for lift-and-shift migrations without major application changes.

Answer

Azure Database for MySQL

Answer

Azure Virtual Machines with SQL Server

Answer

Azure SQL Database

Question 10

You are designing a relational database for a multi-tenant SaaS application. Each tenant's data must be isolated for security and compliance. Which design approach best ensures data isolation while keeping cost manageable?

Accepted Answer

Use a separate database per tenant. A separate database per tenant provides strong isolation, ensuring each tenant's data is completely separate for security and compliance, while keeping costs manageable through elastic pools or other cost-effective deployment models. Option A is incorrect because Azure Synapse Analytics is a data warehousing and analytics service, not designed for OLTP multi-tenant isolation. Option C is incorrect because row-level security can help but does not provide the same level of isolation as separate databases. Option D is incorrect because separate schemas within a single database do not provide full data isolation; they share the same database and resources.

Answer

Use Azure Synapse Analytics with workload isolation

Answer

Use a single database with a TenantID column and row-level security

Answer

Use a single database with separate schemas per tenant

Question 11

You are creating an Azure SQL Database and need to connect using Microsoft Entra ID authentication. Which user type must you create in the database to represent the authenticated Microsoft Entra ID identity?

Accepted Answer

Contained database user mapped to a Microsoft Entra ID identity. To connect to Azure SQL Database using Microsoft Entra ID authentication, you must create a contained database user that is mapped to a Microsoft Entra ID identity (user, group, or service principal). This contained user resides in the database and is authenticated via Entra ID, allowing token-based or integrated authentication without a SQL login. The syntax is CREATE USER [name] FROM EXTERNAL PROVIDER.

Answer

SQL login with password

Answer

External user from Microsoft Entra ID

Answer

Database user without login

Question 12

Which TWO Azure services can be used to store relational data?

Accepted Answer

Azure SQL Database. Azure SQL Database (A) is correct because it is a fully managed relational database service built on the Microsoft SQL Server engine, storing data in tables with schemas, primary/foreign keys, and T-SQL query support. Azure Database for PostgreSQL (C) is also correct because it is a managed relational database service based on the open-source PostgreSQL engine, supporting relational tables, ACID transactions, and SQL queries. Azure Cosmos DB (B) is a globally distributed NoSQL database offering multiple non-relational APIs (SQL, MongoDB, Cassandra, Gremlin, Table), so it is not a relational data store. Azure Blob Storage (D) is object storage for unstructured data such as files, images, and backups, not relational tables. Azure Table Storage (E) is a NoSQL key-attribute store for structured non-relational data, lacking relational schema and join capabilities.

Answer

Azure Cosmos DB

Answer

Azure Blob Storage

Answer

Azure Table Storage

Question 13

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

Accepted Answer

Predictable pricing for a group of databases. Azure SQL Database elastic pools provide predictable pricing because you pay for a fixed set of resources (eDTUs or vCores) allocated to the pool, regardless of how many databases use them. This allows you to budget for a group of databases with variable usage patterns without incurring per-database costs, making it cost-effective for workloads with intermittent or unpredictable demand.

Answer

Unlimited storage per database

Answer

Isolated performance for each database

Answer

Support for databases over 1 TB each

Question 14

A company uses Azure SQL Database for a customer relationship management (CRM) application. The database has a table named Orders that stores order details. The company needs to ensure that the OrderDate column is automatically set to the current date and time when a new row is inserted, without any application-side logic. Which T-SQL construct should be used?

Accepted Answer

DEFAULT constraint with GETDATE(). A DEFAULT constraint with GETDATE() automatically inserts the current date and time into the OrderDate column when a new row is inserted and no value is specified. This is a server-side solution that requires no application logic, ensuring consistency and reducing code complexity.

Answer

CHECK constraint

Answer

UNIQUE constraint

Answer

PRIMARY KEY constraint

Question 15

An e-commerce application uses Azure SQL Database and stores user session data in a table called Sessions. The table contains millions of rows and queries often filter by UserID and LastActivityTime. The development team wants to improve query performance for these filters. What should they implement?

Accepted Answer

Create a nonclustered index on UserID and LastActivityTime. A nonclustered index on UserID and LastActivityTime allows the database engine to quickly locate rows matching the filter criteria without scanning the entire table. This index covers the two columns most frequently used in WHERE clauses, significantly reducing I/O and improving query performance for the e-commerce application's session data.

Answer

Create a clustered index on the SessionID column

Answer

Create a view that filters the data

Answer

Partition the table by month