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

Question 1

Which TWO of the following statements about Azure SQL Database elastic pools are true?

Accepted Answer

They allow multiple databases to share a fixed set of resources. Option B is correct because an elastic pool provides a shared resource model in which multiple Azure SQL databases draw from a common pool of resources (such as vCores, DTUs, storage, and IOPS) rather than each database being provisioned with dedicated resources, which is the defining characteristic of elastic pools. Option C is correct because elastic pools are specifically designed for databases with unpredictable or intermittent usage patterns; by pooling resources, the peaks of some databases are offset by the idle periods of others, allowing cost savings compared to provisioning each database for its own peak demand. Option A is incorrect because an elastic pool is a management and resource-sharing construct, not a data store; the actual database data resides in the individual databases, not in the pool itself. Option D is incorrect because elastic pools can be scaled after creation — you can change the service tier, performance level (e.g., DTU or vCore limits), and storage limits dynamically. Option E is incorrect because an elastic pool is associated with a single Azure SQL logical server, but it can contain many databases on that server, so the statement that it is limited to a single database is false.

Answer

They store actual database data

Answer

They cannot be scaled after creation

Answer

They are limited to a single Azure SQL Database server

Question 2

Your organization is migrating a large on-premises SQL Server database to Azure SQL Database. The database is used by a critical line-of-business application that requires near-zero downtime during migration. The application uses a mix of read and write operations, and you need to minimize the cutover time. The database is about 2 TB in size. You plan to use Azure Database Migration Service (DMS) for the migration. The source SQL Server is version 2019 Enterprise Edition. The target is a Business Critical tier Azure SQL Database. You need to choose the appropriate migration method and configuration. Which of the following actions should you take?

Accepted Answer

Use online migration mode with DMS on Premium tier. The correct choice is C: use online migration mode with DMS on Premium tier, because online mode keeps the source and target synchronized via continuous change-data-capture replication so the application can keep reading and writing with only a brief final cutover, and the Premium tier provides the higher compute/throughput needed to replicate a 2 TB database with minimal downtime. Offline mode (A and D) requires the source to be quiesced and taken down for the entire migration, which cannot meet the near-zero-downtime requirement, and manually copying remaining changes (D) adds risk and cutover time. Basic tier (B) is unsuitable because it has very low DTU limits and cannot support the performance or scale required for a 2 TB Business Critical target. Standard tier (A) is likewise too limited for this workload and is paired with offline mode, which is disallowed here.

Answer

Use offline migration mode with DMS on Standard tier

Answer

Use online migration mode with DMS on Basic tier

Answer

Use offline migration mode with DMS on Premium tier, then manually copy remaining changes

Question 3

Your team is migrating an on-premises SQL Server database to Azure. The database is used by a critical application that requires high availability and automatic failover. You need to choose a deployment option that provides a 99.99% SLA and supports automated backups. What should you use?

Accepted Answer

Azure SQL Database (Business Critical tier with zone redundancy). Azure SQL Database in the Business Critical service tier with zone redundancy provides a 99.995% SLA and supports automated backups, meeting the high availability and automatic failover requirements. Option A (Azure Database for PostgreSQL) is a different database engine and not suitable for migrating a SQL Server database. Option C (SQL Server on Azure Virtual Machines) requires manual configuration for high availability and does not offer automated backups by default. Option D (Azure SQL Managed Instance) provides a 99.99% SLA and automated backups. While it supports zone redundancy, it is not enabled by default and would need to be configured. Option B explicitly includes zone redundancy, offering 99.995% SLA, making it a more robust choice for the specified high availability.

Answer

Azure Database for PostgreSQL

Answer

SQL Server on Azure Virtual Machines

Answer

Azure SQL Managed Instance

Question 4

An e-commerce company runs a transactional database on Azure SQL Database. During peak shopping seasons, they experience performance degradation due to high read traffic on product catalog tables. The company wants to offload read queries to a read-only copy of the database without affecting write performance. What should they implement?

Accepted Answer

Configure Active Geo-Replication to create a readable secondary replica.. Active Geo-Replication (option A) is correct because it creates a readable secondary replica of the Azure SQL Database in another region, allowing read-only workloads such as product catalog queries to be offloaded without impacting write performance on the primary. It supports up to four readable secondaries and is designed for read offloading and disaster recovery. Option B (Read Scale-Out) only applies to the Premium/Business Critical tiers and provides a read-only replica in the same region, but it is not the general-purpose solution described and doesn't match the scenario's requirement as directly. Option C (Azure Cache for Redis) is a caching layer, not a read-only database copy, and does not offload queries at the database level. Option D (failover group) is primarily for disaster recovery and automatic failover, not for read offloading, though it can include readable secondaries, it is not the best fit for the stated goal.

Answer

Enable Read Scale-Out on the database.

Answer

Implement Azure Cache for Redis to cache product catalog data.

Answer

Create a failover group with a secondary region.

Question 5

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.. Option A is correct because Azure SQL Managed Instance includes SQL Server Agent, allowing on-premises-style job scheduling, whereas Azure SQL Database does not provide SQL Server Agent (it relies on Elastic Jobs or other automation). Option C is correct because Azure SQL Managed Instance supports three-part name cross-database queries within the same instance, a key compatibility feature for migrating multi-database SQL Server workloads, while Azure SQL Database generally requires elastic queries or external tables for cross-database access. Option E is correct because Azure SQL Database offers elastic pools, which let multiple databases share a pool of resources (eDTUs or vCores) for cost efficiency, a capability not available in the same form on Azure SQL Managed Instance. Option B is incorrect because Azure SQL Managed Instance supports larger maximum storage sizes (up to 16 TB) than the single-database limits of Azure SQL Database, so it does not support larger sizes. Option D is incorrect because Azure SQL Managed Instance is deployed inside an Azure virtual network subnet with native VNet integration, which is one of its defining features.

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 6

Which TWO of the following are true about Azure Database for PostgreSQL? (Select TWO.)

Accepted Answer

It supports read replicas to offload read traffic.. Option C is correct because Azure Database for PostgreSQL supports read replicas, which are asynchronously replicated copies of the primary server that can be used to offload read-only workloads and improve performance. Option E is correct because the service automatically performs backups and retains them, enabling point-in-time restore (PITR) within the configured retention period (up to 35 days), which is a core managed-service capability. Option A is incorrect because Azure Database for PostgreSQL also offers the Flexible Server deployment mode and supports both the community edition and, historically, the Single Server option, but the statement that it 'only supports' the open-source community edition is too restrictive and not accurate as a defining truth. Option B is incorrect because Azure Database for PostgreSQL is a relational SQL database service, not a NoSQL service. Option D is incorrect because cross-database queries across multiple servers are not supported; each server is isolated, and queries are scoped to a single database on a single server.

Answer

It only supports the open-source community edition of PostgreSQL.

Answer

It is a NoSQL database service.

Answer

It allows cross-database queries across multiple servers.

Question 7

A financial services company needs to store transaction data in a relational database that must comply with strict regulatory requirements. The data must be encrypted at rest and in transit, and the database must support automatic failover to a secondary region. Which Azure solution meets these requirements?

Answer

Azure Database for MySQL

Answer

Azure Cosmos DB

Answer

Azure SQL Database with Active Geo-Replication

Answer

SQL Server on Azure Virtual Machines

Question 8

A company needs to migrate a large on-premises SQL Server database to Azure. The database uses features like SQL Server Agent jobs, cross-database queries, and common language runtime (CLR) assemblies. Which Azure service supports these features?

Accepted Answer

Azure SQL Managed Instance. The current explanation correctly states that Azure SQL Managed Instance supports the features. However, it also acknowledges that Option A (SQL Server on Azure Virtual Machines) supports all these features but attempts to disqualify it by stating it is an IaaS solution, not a fully managed PaaS service. Since the question does not specify a requirement for a PaaS or fully managed service, this distinction does not make D the *sole* correct answer based on the stem's wording. To make D the unique correct answer, the stem should be modified to explicitly ask for a PaaS or fully managed service.

Answer

SQL Server on Azure Virtual Machines

Answer

Azure SQL Database

Answer

Azure Database for PostgreSQL

Question 9

Your organization uses Azure SQL Database and needs to audit all database operations for compliance. The audit logs must be stored for at least five years and be easily searchable. What should you configure?

Accepted Answer

Enable auditing and store logs in Azure Blob Storage with a retention policy of five years.. Azure SQL Database auditing can write audit logs directly to an Azure Storage account (Blob Storage), which is the recommended destination for long-term retention because blob storage supports configurable retention policies and is cost-effective for multi-year archival. Setting a retention policy of five years satisfies the compliance requirement, and the logs remain searchable via storage tools or by querying them with services like Azure Monitor or Log Analytics if needed. This is the standard pattern for regulatory audit retention beyond the default 90-day Azure Monitor retention.

Answer

Enable auditing and store logs in Azure Monitor Logs.

Answer

Use Azure Sentinel to collect and store audit logs.

Answer

Enable Transparent Data Encryption (TDE) to track changes.

Question 10

A company runs a critical application on Azure SQL Database. They need to ensure that in the event of a regional disaster, the database can be failed over to a secondary region with minimal data loss. The solution should also allow the secondary region to be used for read-only queries. What should they configure?

Accepted Answer

Create a failover group with a readable secondary in the secondary region.. A failover group with a readable secondary in the secondary region is correct because Azure SQL Database auto-failover groups provide automatic or manual geo-failover to a secondary region with a defined RPO/RTO, and the secondary replica can be configured as readable to serve read-only workloads. This directly satisfies both requirements: minimal data loss during regional disaster failover and read-only query offloading. Active Geo-Replication (A) also supports readable secondaries and manual failover, but it lacks the failover group's listener endpoint and automatic failover capability, making it less aligned with the stated need for a managed failover solution. Azure Front Door (B) is only a traffic-routing service and does not replicate or fail over the database. Azure Cosmos DB (D) is a different database platform and would require migration, which is not warranted here.

Answer

Configure Active Geo-Replication and manually failover when needed.

Answer

Use Azure Front Door to route traffic to the secondary region.

Answer

Migrate to Azure Cosmos DB for multi-region writes.

Question 11

A startup is building a new mobile app that will track user fitness activities. They need a relational database to store user profiles, activity logs, and goals. The database must be easy to set up, require minimal administration, and automatically scale during peak usage. The startup has a limited budget and prefers a consumption-based pricing model. Which Azure service should they choose?

Accepted Answer

Azure SQL Database serverless. Azure SQL Database serverless is the correct choice because it provides a consumption-based pricing model that automatically pauses during inactivity and scales compute resources based on demand, requiring minimal administration. This aligns perfectly with the startup's need for easy setup, minimal administration, automatic scaling during peak usage, and a limited budget.

Answer

Azure Database for MySQL serverless

Answer

SQL Server on Azure Virtual Machines

Answer

Azure SQL Managed Instance

Question 12

Which TWO Azure services can be used to host a relational database that requires native support for JSON data and high availability with automatic failover?

Accepted Answer

Azure SQL Database. Azure SQL Database (A) is correct because it is a fully managed relational database engine that natively supports JSON data via the JSON functions (JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON) and offers high availability with automatic failover through built-in geo-replication, auto-failover groups, and zone-redundant configurations. Azure SQL Managed Instance (C) is also correct because it runs the same SQL Server engine, providing identical native JSON support and built-in high availability with automatic failover (via Always On availability groups managed by the service). Azure Database for MariaDB (B) and Azure Database for PostgreSQL (E) are relational services with some JSON capabilities, but the question targets the Azure SQL family's native JSON support and automatic failover guarantees; MariaDB's JSON is an alias for LONGTEXT and PostgreSQL's JSONB differs from SQL Server's native JSON functions. Azure Cosmos DB (D) is a NoSQL database with JSON documents and automatic failover, but it is not a relational database, so it does not satisfy the 'relational database' requirement.

Answer

Azure Database for MariaDB

Answer

Azure Cosmos DB

Answer

Azure Database for PostgreSQL

Question 13

Which THREE factors should be considered when designing a relational database in Azure to minimize latency for globally distributed users?

Accepted Answer

Configure read replicas. Option C (Configure read replicas) is correct because read replicas let read-heavy workloads be served from copies of the database located closer to users, reducing round-trip latency and offloading the primary. Option D (Use active geo-replication) is correct because it continuously replicates the database to secondary regions, enabling globally distributed users to read from a nearby region and supporting fast failover. Option E (Choose a service tier that provides higher IOPS) is correct because higher IOPS and throughput tiers reduce storage I/O wait times, directly lowering query latency under load. Option A (horizontal partitioning/sharding) is not marked correct because sharding primarily improves scalability and manageability rather than minimizing network latency for globally distributed users. Option B (columnstore indexes) is not marked correct because columnstore indexes mainly accelerate analytical/aggregation queries and are not a general latency-reduction factor for globally distributed transactional access.

Answer

Implement horizontal partitioning (sharding)

Answer

Use columnstore indexes

Question 14

The exhibit shows a firewall rule configuration for an Azure SQL Server. A developer reports that a client app running on IP 10.0.0.5 cannot connect to the database. What is the most likely cause?

Accepted Answer

The first rule only allows a single IP (10.0.0.0), not the entire subnet. The first rule allows only IP 10.0.0.0 (single IP), not the entire subnet (10.0.0.0/24). Therefore, a client at 10.0.0.5 would be blocked. Option A is incorrect. Azure SQL firewall rules can be configured via various methods, including ARM templates which use JSON. If the underlying configuration was malformed JSON, the rules would likely fail to deploy or apply, which would prevent connectivity. However, a simple misconfiguration of an IP range (as described in option B) is a more common and direct cause of a connection failure for an already deployed rule set, making it the most likely cause. Option C is incorrect because Azure SQL firewall rules are evaluated such that if any rule allows the connection, it is permitted; there is no overriding in the sense of a later rule explicitly blocking an earlier allowed one. Both rules apply, but the first rule's narrow scope is the problem. Option D is incorrect because the client uses IPv4 (10.0.0.5) and IPv6 is not relevant here.

Answer

The firewall rules are not in valid JSON format

Answer

The second rule overrides the first rule

Answer

The firewall rule uses IPv4 addresses, but the client is using IPv6

Question 15

You are the database administrator for a global e-commerce company that uses Azure SQL Database. The company has a single database in the West US region. The database hosts a table named Orders with 500 million rows. The table is clustered on OrderId (uniqueidentifier). The most critical query is a daily report that aggregates total sales by product category for the previous day. This query currently takes over 30 minutes to run and causes performance degradation on the primary database. The report must be available by 6:00 AM local time each day, and the data must be no more than 24 hours old. You need to design a solution that minimizes impact on the transactional workload and improves report query performance. What should you do?

Accepted Answer

Create a readable secondary replica in the same region and run the report query against the replica. Option D is correct because creating a readable secondary replica (via active geo-replication or an auto-failover group) in the same region lets the daily aggregation report run against a read-only copy, completely offloading the reporting workload from the primary transactional database while keeping data replication lag well within the 24-hour freshness requirement. This directly minimizes impact on the transactional workload and improves report performance by isolating the resource-intensive query. Option A is wrong because partitioning by OrderDate with a partitioned view does not by itself reduce the primary database's workload for the report and adds complexity without offloading reads. Option B is wrong because a nonclustered columnstore index on the primary still consumes primary database resources during the report and index maintenance, impacting transactions. Option C is wrong because a covering nonclustered index on the primary likewise runs the report on the primary and adds write overhead to the transactional workload.

Answer

Partition the Orders table by OrderDate and create a partitioned view for the report

Answer

Add a nonclustered columnstore index on the primary database for the Orders table

Answer

Create a nonclustered index on the primary database covering the columns used in the report query