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

Question 1

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?

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

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 2

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 3

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 4

A company uses Azure SQL Database with active geo-replication for disaster recovery. During a regional outage, the secondary database is promoted. After the primary region recovers, what is the best practice to re-establish geo-replication?

Accepted Answer

Perform a planned failover to make the old primary the primary again. Performing a planned failover properly fails back to the original primary and re-establishes geo-replication. Option A is wrong because deleting the old primary loses data and is unnecessary. Option B is wrong because while you can add the old primary as a secondary, it is not the best practice; a planned failover is preferred. Option C is wrong because automatic reversal of geo-replication direction is not supported; you must manually reconfigure.

Answer

Delete the old primary database and create a new geo-replication

Answer

Add the old primary as a secondary to the new primary

Answer

Reverse the geo-replication direction automatically

Question 5

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 authenticate using Microsoft Entra ID in Azure SQL Database, you must create a contained database user that is mapped to a Microsoft Entra ID identity. This user does not have a login in the master database but is authenticated via Entra ID. Option A is incorrect because SQL logins use SQL Server authentication, not Entra ID. Option C is incorrect because there is no concept of an 'external user' in this context; the correct term is a contained database user mapped to an Entra ID identity. Option D is incorrect because a database user without login is typically used for SQL authentication or users without a login, not for Entra ID authentication.

Answer

SQL login with password

Answer

External user from Microsoft Entra ID

Answer

Database user without login

Question 6

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. SQL Managed Instance supports SQL Server Agent jobs, which SQL Database does not. Option C is correct because SQL Managed Instance supports cross-database queries, while SQL Database does not. Option D is correct because SQL Database offers automatic scaling options like serverless compute, whereas SQL Managed Instance requires manual scaling. Option A is incorrect because both services support Microsoft Entra ID authentication. Option E is incorrect because VNet integration is a feature of SQL Managed Instance, not a primary differentiator, and both can be integrated into a VNet in different ways.

Answer

Support for Microsoft Entra ID authentication

Answer

Requirement for VNet integration

Question 7

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 8

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 populates the OrderDate column with the current date and time on each insert, without requiring application-side logic. Option A is incorrect because a CHECK constraint enforces data integrity by validating conditions but does not provide default values. Option B is incorrect because a UNIQUE constraint ensures all values in a column are unique, not auto-populate dates. Option C is incorrect because a PRIMARY KEY constraint uniquely identifies each row and does not set default values.

Answer

CHECK constraint

Answer

UNIQUE constraint

Answer

PRIMARY KEY constraint

Question 9

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

Question 10

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. A failover group with an automatic failover policy and a non-readable secondary meets all requirements: it provides automatic failover to a secondary region during an outage, minimizes data loss through synchronous replication (within the same region) and asynchronous replication across regions, and ensures the secondary is not used for read traffic during normal operations by disabling the readable secondary option.

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 11

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. (Elastic Jobs) is the correct answer because Elastic Jobs in Azure SQL Database can replace SQL Server Agent jobs for scheduling maintenance tasks with minimal code changes. Option A (Azure Functions) is wrong because while it can run scripts, it is not a direct replacement and requires more development effort. Option B (Azure Automation) is wrong because it is more suited for Azure resource management rather than database job scheduling. Option D is wrong because SQL Server Agent is not available in Azure SQL Database; it is only available in SQL Server on-premises or on Azure VMs.

Answer

Azure Functions

Answer

Azure Automation

Answer

SQL Server Agent (available in Azure SQL Database)

Question 12

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. Always Encrypted. Always Encrypted is a feature designed to protect sensitive data, such as Social Security numbers, by encrypting it at rest and in transit, with the encryption keys stored outside of Azure SQL Database, providing client-side key management. This satisfies the HIPAA requirement for encrypting sensitive columns and controlling access to keys.

Option A: Dynamic Data Masking obfuscates data from non-privileged users but does not encrypt the data; it can be reversed by privileged users.

Option B: Row-Level Security restricts access to rows based on user characteristics but does not encrypt columns.

Option D: Transparent Data Encryption (TDE) encrypts the entire database at rest but does not provide column-level encryption or client-side key control.

Answer

Dynamic Data Masking

Answer

Row-Level Security

Answer

Transparent Data Encryption (TDE)

Question 13

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. Azure SQL Database serverless tier is a relational database that supports JSON data, offers automatic scaling, and pauses during idle periods to reduce costs. Option A is incorrect because Azure Database for PostgreSQL serverless, while relational and supporting JSON, does not have the same level of automatic scaling and serverless compute optimization as Azure SQL Database serverless. Option B is incorrect because Azure Cosmos DB is a NoSQL database, not relational. Option D is incorrect because Azure SQL Managed Instance does not have a serverless tier.

Answer

Azure Database for PostgreSQL serverless

Answer

Azure Cosmos DB

Answer

Azure SQL Managed Instance

Question 14

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. Active geo-replication (Option B) creates readable secondary databases in other Azure regions. Reads can be directed to the secondary for low latency, while writes always go to the primary and are strongly consistent. This matches the requirement for globally distributed users with low-latency reads and strong consistency for writes. Option A (Azure Cosmos DB with multi-master) is a NoSQL solution, not relational. Option C (Failover groups) manages geo-replication and provides automatic failover, but it still relies on active geo-replication to create the readable secondaries; it does not directly enable low-latency reads. Option D (Read scale-out) uses local read-only replicas within the same region, not globally.

Answer

Azure Cosmos DB with multi-master

Answer

Failover groups

Answer

Read scale-out

Question 15

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. SQL Database auditing captures all data modifications (INSERT, UPDATE, DELETE) and can store logs in Azure storage, Log Analytics, or Event Hubs with retention up to 7 years. Option A is wrong because Advanced Threat Protection detects suspicious activities, not audits modifications. Option C is wrong because vulnerability assessment scans for security weaknesses, not logging changes. Option D is wrong because Transparent Data Encryption (TDE) encrypts data at rest, it does not log modifications.

Answer

Advanced Threat Protection

Answer

Vulnerability assessment

Answer

Transparent Data Encryption (TDE)