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

Question 1

Your team is designing a database for a financial application that requires ACID transactions. You are considering Azure Cosmos DB and Azure SQL Database. Which service should you choose?

Accepted Answer

Azure SQL Database. Option B is correct because Azure SQL Database provides full ACID transactions. Option A is wrong because Azure Cosmos DB provides limited transactional support. Option C is wrong because Azure Database for MySQL provides ACID but the question asks between Cosmos DB and SQL Database. Option D is wrong because Azure Table Storage does not support ACID.

Answer

Azure Table Storage

Answer

Azure Cosmos DB

Answer

Azure Database for MySQL

Question 2

You are deploying a high-availability solution for an Azure SQL Database that must have a Recovery Point Objective (RPO) of less than 5 seconds. Which deployment option meets this requirement?

Accepted Answer

Azure SQL Database Business Critical tier with zone redundancy. Option C is correct because Azure SQL Database Business Critical tier with zone redundancy provides synchronous replication achieving RPO of 0-5 seconds. Option A is wrong because active geo-replication has asynchronous replication. Option B is wrong because Azure SQL Database Hyperscale has asynchronous replication. Option D is wrong because Azure SQL Managed Instance General Purpose has asynchronous replication.

Answer

Azure SQL Database Hyperscale tier

Answer

Azure SQL Managed Instance General Purpose tier

Answer

Azure SQL Database with active geo-replication

Question 3

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

Accepted Answer

Azure SQL Database. Option A (Azure SQL Database) and Option D (Azure Database for PostgreSQL) are relational database services. Option B is wrong because Azure Cosmos DB is NoSQL. Option C is wrong because Azure Blob Storage is object storage. Option E is wrong because Azure Table Storage is NoSQL.

Answer

Azure Cosmos DB

Answer

Azure Blob Storage

Answer

Azure Table Storage

Question 4

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 5

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 6

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(). Option B is correct because the DEFAULT constraint with GETDATE() automatically populates the OrderDate with the current date and time on insertion. Option A is wrong because CHECK enforces data integrity, not default values. Option C is wrong because UNIQUE ensures uniqueness. Option D is wrong because PRIMARY KEY identifies rows uniquely.

Answer

CHECK constraint

Answer

UNIQUE constraint

Answer

PRIMARY KEY constraint

Question 7

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. Option C is correct because an index on (UserID, LastActivityTime) speeds up filters on both columns. Option A is wrong because a clustered index organizes data physically but does not specifically optimize these filters. Option B is wrong because partitioning can help manage large tables but not directly optimize filter queries. Option D is wrong because views are virtual and do not inherently improve performance without indexes.

Answer

Create a clustered index on the SessionID column

Answer

Create a view that filters the data

Answer

Partition the table by month

Question 8

A financial services company runs a critical application on Azure SQL Managed Instance. 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 and automatic failover. The secondary region should not be used for read traffic during normal operations. Which configuration meets these requirements?

Accepted Answer

Configure a failover group with automatic failover policy and set the secondary to non-readable. Option A is correct because failover groups with automatic failover and readable secondary set to 'No' provide disaster recovery with minimal data loss and no read traffic on secondary. Option B is wrong because active geo-replication allows read-only access on secondary. Option C is wrong because automatic failover groups require readable secondary to be enabled for automatic failover. Option D is wrong because backup restore does not provide automatic failover and has higher data loss.

Answer

Create an automatic failover group with readable secondary enabled

Answer

Use Azure SQL Database backup to blob storage and restore to another region

Answer

Implement active geo-replication and manually initiate failover

Question 9

A software company is migrating an on-premises SQL Server database to Azure SQL Database. The database currently uses SQL Server Agent jobs for regular maintenance tasks. The company wants to minimize code changes during migration. Which Azure SQL Database feature should they use to replace SQL Server Agent jobs?

Accepted Answer

Elastic Jobs. Option B is correct because Elastic Jobs (preview) in Azure SQL Database can replace SQL Server Agent jobs for scheduling tasks. Option A is wrong because Azure Automation can run scripts but is not a direct replacement. Option C is wrong because SQL Server Agent is not available in Azure SQL Database. Option D is wrong because Azure Functions can run code but requires more development effort.

Answer

Azure Functions

Answer

Azure Automation

Answer

SQL Server Agent (available in Azure SQL Database)

Question 10

A healthcare organization uses Azure SQL Database to store patient records. To comply with HIPAA regulations, they need to encrypt sensitive columns (e.g., Social Security numbers) at rest and control access to the encryption keys. Which feature should they use?

Accepted Answer

Always Encrypted. Option A is correct because Always Encrypted with secure enclaves protects sensitive columns at rest and allows client-side key management. Option B is wrong because Transparent Data Encryption encrypts the entire database but does not allow column-level key control. Option C is wrong because Dynamic Data Masking obfuscates data but does not encrypt it. Option D is wrong because Row-Level Security restricts row access but does not encrypt columns.

Answer

Dynamic Data Masking

Answer

Row-Level Security

Answer

Transparent Data Encryption (TDE)

Question 11

A startup is building a new application and needs a relational database that supports JSON data, automatic scaling, and a serverless compute tier to minimize costs during low usage periods. Which Azure data service should they choose?

Accepted Answer

Azure SQL Database serverless tier. Option C is correct because Azure SQL Database serverless tier supports JSON, automatic scaling, and pauses during inactivity. Option A is wrong because Azure Cosmos DB is NoSQL, not relational. Option B is wrong because Azure SQL Managed Instance does not have a serverless tier. Option D is wrong because Azure Database for PostgreSQL serverless does not support JSON as naturally as SQL Server.

Answer

Azure Database for PostgreSQL serverless

Answer

Azure Cosmos DB

Answer

Azure SQL Managed Instance

Question 12

A multinational corporation is deploying a global application using Azure SQL Database. They need to ensure that users in different geographic regions experience low latency reads. The application can tolerate slightly stale data for reads, but writes must be strongly consistent and must occur in a single primary region. Which feature should they implement?

Accepted Answer

Active geo-replication. Option C is correct because active geo-replication allows creating readable secondaries in other regions for low-latency reads, while writes go to the primary. Option A is wrong because failover groups provide automatic failover but not necessarily low-latency reads from multiple regions. Option B is wrong because Cosmos DB with multi-master is NoSQL and not relational. Option D is wrong because read scale-out uses local replicas, not global.

Answer

Azure Cosmos DB with multi-master

Answer

Failover groups

Answer

Read scale-out

Question 13

A data engineering team is designing a data warehouse in Azure Synapse Analytics. They need to load data from an on-premises SQL Server database daily. The data volume is about 500 GB per load. They want to minimize data transfer costs and use the fastest possible transfer method. Which approach should they use?

Accepted Answer

Use Azure Data Factory with PolyBase. Option B is correct because Azure Data Factory with PolyBase allows high-speed parallel loading into Azure Synapse. Option A is wrong because using the Azure portal upload is not practical for 500 GB. Option C is wrong because BACPAC export/import is not optimized for large volumes. Option D is wrong because SSIS with Azure Blob is slower and more complex.

Answer

Use the Azure portal to upload a CSV file

Answer

Use SQL Server Integration Services (SSIS) to load to Azure Blob and then into Synapse

Answer

Export to BACPAC and import to Azure SQL Database

Question 14

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 15

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