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

Question 1

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. Option D is correct because active geo-replication with auto-failover groups provides automatic failover across regions with RPO of 5 seconds. Option A is wrong because Azure SQL Database local redundancy protects within a region. Option B is wrong because Azure SQL Database zone redundancy protects within a region across zones. Option C is wrong because Azure Traffic Manager is for traffic routing, not database failover.

Answer

Zone-redundant database

Answer

Azure Traffic Manager with multiple databases

Answer

Locally redundant storage (LRS)

Question 2

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. Option A is correct because Azure SQL Database serverless is designed for intermittent, unpredictable workloads and automatically pauses when idle, reducing cost. Option B is wrong because Azure SQL Database provisioned is for steady-state workloads. Option C is wrong because SQL Server on Azure VMs is IaaS and requires management. Option D is wrong because Azure Database for PostgreSQL is fully managed but PostgreSQL-specific; Azure SQL Database serverless is more cost-effective for light relational workloads.

Answer

Azure Database for PostgreSQL

Answer

SQL Server on Azure Virtual Machines

Answer

Azure SQL Database (provisioned DTU)

Question 3

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. Option B is correct because Dynamic Data Masking obfuscates sensitive data in query results for unauthorized users. Option A is wrong because Transparent Data Encryption encrypts data at rest, not in query results. Option C is wrong because Row-Level Security restricts row access but does not mask columns. Option D is wrong because Azure SQL Database Auditing tracks database events, not masking.

Answer

Row-Level Security

Answer

Auditing

Answer

Transparent Data Encryption (TDE)

Question 4

A company has both transactional and analytical workloads on the same SQL Server database. They want to move to Azure and separate these workloads to improve performance. They need a solution that supports both workloads without duplicating data. What should they do?

Accepted Answer

Use Azure Synapse Link for SQL to replicate data in near real-time to an analytical store.. Option D is correct because Azure Synapse Link for SQL enables real-time replication of transactional data from Azure SQL Database to Synapse Analytics for analytical queries without ETL. Option A is wrong because moving to Azure SQL Database alone doesn't separate workloads. Option B is wrong because using Azure SQL Database and SQL Data Warehouse separately would require data duplication. Option C is wrong because Azure Data Factory is for ETL, not real-time replication.

Answer

Use Azure Data Factory to move data between two Azure SQL Databases.

Answer

Use Azure SQL Database for transactions and Azure SQL Data Warehouse for analytics, with periodic data copy.

Answer

Use Azure SQL Database for both workloads.

Question 5

A company needs to ensure that their Azure SQL Database is accessible only from a specific virtual network and deny access from public endpoints. Which feature should they configure?

Accepted Answer

Virtual network service endpoints and firewall rules. Option C is correct because virtual network service endpoints and firewall rules allow restricting access to a specific VNet. Option A is wrong because Private Link provides private connectivity but is more complex. Option B is wrong because Azure SQL Database always has a public endpoint by default; disabling it without Private Link would block all access. Option D is wrong because IP firewall rules allow public IPs, not VNet-specific.

Answer

Disable public network access

Answer

IP firewall rules

Answer

Azure Private Link

Question 6

Which TWO features are available in Azure SQL Database to help protect data at rest?

Accepted Answer

Transparent Data Encryption (TDE). Option A and Option C are correct. Transparent Data Encryption encrypts the database files at rest. Always Encrypted encrypts sensitive columns at rest and in use. Option B is wrong because Dynamic Data Masking does not encrypt data; it masks it in query results. Option D is wrong because Auditing is for tracking, not encryption. Option E is wrong because Row-Level Security controls access, not encryption.

Answer

Dynamic Data Masking

Answer

Auditing

Answer

Row-Level Security

Question 7

Which THREE are valid deployment options for Azure SQL Database?

Accepted Answer

Managed instance. Options B, C, and E are correct. Single database, elastic pool, and managed instance are all deployment options for Azure SQL Database. Option A is wrong because virtual machine is not a deployment option for Azure SQL Database; it's SQL Server on VM. Option D is wrong because SQL Data Warehouse is a separate service (now Synapse).

Answer

SQL Server on Azure Virtual Machine

Answer

SQL Data Warehouse

Question 8

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. Options A and C are correct. Managed service vs. IaaS is a key differentiator. Feature compatibility is also important because managed instance offers high compatibility but not 100%. Option B is wrong because both services support TDE. Option D is wrong because both support geo-replication (Azure SQL Database has active geo-replication; SQL Server on VM can use Always On AG). Option E is wrong because both support auditing.

Answer

Need for database auditing

Answer

Support for geo-replication

Answer

Need for Transparent Data Encryption

Question 9

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 10

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 11

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. Option C is correct because creating a read-only replica offloads reporting queries. Option A is wrong because increasing database throughput does not eliminate blocking. Option B is wrong because READ UNCOMMITTED might cause dirty reads. Option D is wrong because distributing data across databases (sharding) adds complexity and may not solve blocking.

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 12

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. Option B is correct because Azure SQL Managed Instance provides near 100% compatibility with SQL Server. Option A is wrong because Azure SQL Database does not support CLR assemblies. Option C is wrong because Azure Database for MySQL is a different database engine. Option D is wrong because Azure Virtual Machines with SQL Server requires patching and management overhead.

Answer

Azure Database for MySQL

Answer

Azure Virtual Machines with SQL Server

Answer

Azure SQL Database

Question 13

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. Option A is correct because a separate database per tenant provides strong isolation. Option B is wrong because shared tables with a TenantID column require careful row-level security. Option C is wrong because a separate schema per database is not a standard Azure feature. Option D is wrong because Azure Synapse Analytics is for data warehousing, not OLTP.

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 14

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. Option D is correct because you should fail back to the original primary using planned failover, then reconfigure geo-replication. Option A is wrong because reversing geo-replication automatically is not supported. Option B is wrong because deleting the old primary loses data. Option C is wrong because you cannot simply add the old primary as a secondary without resolution.

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 15

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. Option C is correct because a contained database user mapped to a Microsoft Entra ID identity is required. Option A is wrong because SQL authentication uses a SQL login. Option B is wrong because a database user without login is for SQL authentication. Option D is wrong because an external user is not a valid concept.

Answer

SQL login with password

Answer

External user from Microsoft Entra ID

Answer

Database user without login