Launch pricing — Azure certification prep for €19,95 €29,95

← All DP-900 topicsDP-900 · Relational Data on Azure

DP-900 Azure SQL and Open-Source Databases Practice Questions

The exam expects you to choose the right relational service for a scenario. The Azure SQL family has three options: SQL Server on Azure Virtual Machines (IaaS, full control), Azure SQL Managed Instance (near-complete SQL Server compatibility as PaaS) and Azure SQL Database (fully managed, single databases or elastic pools).

Azure also offers managed open-source databases: Azure Database for PostgreSQL and Azure Database for MySQL. Expect questions on purchasing models, elastic pools, serverless compute and what you still manage yourself in each option.

Below are 13 free questions on this topic. Try each one before you open the answer.

Question 1

An organization wants to provision Azure SQL Database and needs to choose a purchasing model. They want predictable performance measured in compute, memory, and I/O resources rather than a blended unit. Which purchasing model should they choose?

  • A.DTU-based purchasing model
  • B.vCore-based purchasing model
  • C.Serverless purchasing model
  • D.Elastic pool purchasing model
chevron_rightShow answer and explanation

Correct answer: B. vCore-based purchasing model

The vCore-based model lets you independently select the number of virtual cores, memory, and storage, giving transparent control over compute resources. The DTU (Database Transaction Unit) model bundles compute, memory, and I/O into a single blended unit, making it less transparent. Serverless is a compute tier within the vCore model that auto-scales and pauses during inactivity — it is not a separate purchasing model. Elastic pools are a resource-sharing feature, not a purchasing model.

Question 2

Which statement correctly describes a key difference between IaaS and PaaS database services in Azure?

  • A.PaaS services require the customer to manage the operating system and database engine patches.
  • B.IaaS services are fully managed by Microsoft, including OS updates and backups.
  • C.PaaS services let Microsoft manage infrastructure, OS, and the database engine, while IaaS requires the customer to manage the OS and SQL Server.
  • D.IaaS and PaaS provide the same level of customer control.
chevron_rightShow answer and explanation

Correct answer: C. PaaS services let Microsoft manage infrastructure, OS, and the database engine, while IaaS requires the customer to manage the OS and SQL Server.

In the PaaS model (e.g., Azure SQL Database, SQL Managed Instance), Microsoft handles patching, backups, high availability, and OS management. In the IaaS model (e.g., SQL Server on Azure VMs), the customer is responsible for OS updates, SQL Server patching, backups, and security configuration. The key trade-off is control versus management overhead.

Question 3

A database administrator wants to offload read-heavy reporting queries from the primary Azure SQL Database to a secondary replica in another region. Which feature enables this?

  • A.Active Geo-Replication
  • B.Azure SQL Elastic Pools
  • C.Long-term backup retention
  • D.Transparent Data Encryption
chevron_rightShow answer and explanation

Correct answer: A. Active Geo-Replication

Active Geo-Replication creates up to four readable secondary replicas in the same or different regions. These replicas can serve read-only queries, offloading reporting workloads from the primary. Azure SQL Elastic Pools share compute resources across multiple databases but do not create replicas. Long-term backup retention stores backups for compliance but does not serve queries. Transparent Data Encryption encrypts data at rest.

Question 4

Which two statements accurately describe Azure SQL Elastic Pools? Each correct answer presents a complete solution. *(meerdere antwoorden)*

  • A.All databases in the pool share a fixed pool of resources (eDTUs or vCores)
  • B.Each database in the pool is guaranteed its own dedicated, non-shared compute resources
  • C.Elastic pools are cost-effective when databases have unpredictable, variable usage patterns
  • D.Elastic pools require all databases to be in different Azure regions
chevron_rightShow answer and explanation

Correct answer: A. All databases in the pool share a fixed pool of resources (eDTUs or vCores)

In an elastic pool, all member databases share a defined pool of resources. Databases with unpredictable or variable workloads benefit because busy periods for different databases rarely overlap, allowing idle databases to free up resources for active ones. Each database does NOT get its own dedicated resources — the sharing is the core value proposition. All databases in a pool must be on the same logical server in the same region.

Question 5

A development database is only used during business hours and sits idle overnight and on weekends. A team wants the database compute to automatically pause when idle and resume automatically when a connection is made, to minimize costs. Which Azure SQL Database option provides this?

  • A.Active Geo-Replication
  • B.Azure SQL Elastic Pool
  • C.Serverless compute tier
  • D.Hyperscale tier
chevron_rightShow answer and explanation

Correct answer: C. Serverless compute tier

The Serverless compute tier for Azure SQL Database automatically pauses the database after a configurable idle period and resumes it when the next connection arrives. You are billed only for storage during the paused period. This makes it ideal for intermittently used databases such as dev/test environments. Elastic pools share resources across multiple databases but do not pause individual databases. Geo-Replication and Hyperscale serve different purposes.

Question 6

A company wants to migrate an on-premises PostgreSQL database to Azure without managing the underlying server infrastructure. Which Azure service provides a fully managed PostgreSQL database as a PaaS offering?

  • A.SQL Server on Azure Virtual Machines
  • B.Azure Database for PostgreSQL
  • C.Azure SQL Managed Instance
  • D.Azure Cosmos DB for PostgreSQL
chevron_rightShow answer and explanation

Correct answer: B. Azure Database for PostgreSQL

Azure Database for PostgreSQL is a fully managed PaaS relational database service based on the open-source PostgreSQL engine. Microsoft handles patching, backups, high availability, and scaling, so the customer does not manage the underlying OS or server infrastructure. Azure SQL Managed Instance is a PaaS service, but it is based on SQL Server, not PostgreSQL. SQL Server on Azure VMs is an IaaS offering requiring OS and engine management. Azure Cosmos DB for PostgreSQL is a separate distributed database offering (formerly Citus) suited for multi-node, horizontally scaled workloads.

Question 7

A business has a reporting application that runs heavy read queries against a MySQL database. To avoid impacting the transactional workload on the primary server, they want to offload reads to a separate server. Which Azure Database for MySQL feature enables this?

  • A.Geo-redundant backup
  • B.Read replica
  • C.Zone-redundant standby
  • D.Elastic pool
chevron_rightShow answer and explanation

Correct answer: B. Read replica

Azure Database for MySQL – Flexible Server supports read replicas — read-only copies of the primary server that are kept in sync via MySQL's binary log replication. Applications can direct read-heavy queries to replicas, reducing load on the primary and improving overall throughput. Read replicas can be created in the same region or different regions. A zone-redundant standby is a failover target only — it does not accept read connections. Geo-redundant backup enables restoring to another region after a failure, but does not serve live traffic. Elastic pools are an Azure SQL Database concept, not available for Azure Database for MySQL.

Question 8

A developer needs to scale an Azure Database for PostgreSQL – Flexible Server to handle a spike in traffic. They want to increase compute (vCores) without any data loss. Which statement is correct about scaling compute in Flexible Server?

  • A.Scaling compute requires creating a new server and migrating data manually
  • B.Compute can be scaled up or down; the server restarts briefly during the operation
  • C.Compute can only be scaled up, not down
  • D.Scaling compute is not supported — a new server must be provisioned at the required size
chevron_rightShow answer and explanation

Correct answer: B. Compute can be scaled up or down; the server restarts briefly during the operation

Azure Database for PostgreSQL – Flexible Server supports dynamic scaling of compute (vCores) and memory up or down. A brief server restart is required to apply the new compute configuration, but no data is lost during this process. Storage can be scaled up but not down (storage auto-grow is also available). The ability to scale both up and down provides cost flexibility — you can reduce compute after a traffic spike. This is a key advantage of managed PaaS services over IaaS, where resizing a VM may require more manual steps.

Question 9

A company runs a PostgreSQL database on-premises and wants to migrate to Azure with minimal downtime, keeping the on-premises database operational while the migration runs. Which Azure service is designed for online database migrations with minimal downtime?

  • A.Azure Data Factory
  • B.Azure Database Migration Service (DMS)
  • C.Azure Backup
  • D.Azure Site Recovery
chevron_rightShow answer and explanation

Correct answer: B. Azure Database Migration Service (DMS)

Azure Database Migration Service (DMS) is a fully managed service designed to migrate databases to Azure with minimal downtime. It supports both offline (one-time migration with downtime) and online (continuous sync, minimal downtime) migration modes. For PostgreSQL migrations to Azure Database for PostgreSQL, DMS can replicate ongoing changes while the migration runs, cutting over with near-zero downtime. Azure Data Factory is an orchestration and ETL tool, not a database migration service. Azure Backup is for backup and restore, not live migration. Azure Site Recovery is for virtual machine disaster recovery, not database migrations.

Question 10

A company must comply with a regulation requiring all database backups to be stored in encrypted form. Which statement about Azure Database for PostgreSQL backup encryption is correct?

  • A.Backups are stored unencrypted by default; the customer must enable backup encryption manually
  • B.Backups are encrypted at rest using AES-256 by default, using Microsoft-managed keys
  • C.Backup encryption is only available in the Memory Optimized compute tier
  • D.Customers must provide their own encryption keys; Microsoft-managed keys are not supported
chevron_rightShow answer and explanation

Correct answer: B. Backups are encrypted at rest using AES-256 by default, using Microsoft-managed keys

Azure Database for PostgreSQL – Flexible Server encrypts all backups at rest using AES-256 encryption by default. Microsoft manages the encryption keys unless the customer configures customer-managed keys (CMK) in Azure Key Vault for additional control. This means the compliance requirement is met out of the box without any additional configuration. Backup encryption is not tier-specific and applies across all compute tiers. This is part of Azure's broader security baseline for managed database services.

Question 11

An Azure SQL Database table contains 10 million product records. Queries that filter by ProductCategoryID are very slow because every query scans all 10 million rows. The ProductCategoryID column is frequently used in WHERE clauses but is not the primary key. What should be added to improve query performance?

  • A.A foreign key constraint on ProductCategoryID
  • B.A non-clustered index on ProductCategoryID
  • C.A CHECK constraint on ProductCategoryID
  • D.A stored procedure that wraps the SELECT query
chevron_rightShow answer and explanation

Correct answer: B. A non-clustered index on ProductCategoryID

An index is a database structure that allows the query engine to find rows matching a filter condition without scanning the entire table. A non-clustered index on ProductCategoryID creates a sorted reference structure pointing to the physical rows, allowing the database to jump directly to matching rows. This can reduce query time from a full table scan across 10 million rows to a targeted lookup of only the relevant rows. A foreign key constraint enforces referential integrity but does not speed up queries. A CHECK constraint validates column values against a rule — it does not affect query performance. A stored procedure wraps SQL code but does not change how the database retrieves data internally.

Question 12

Determine whether each statement about Azure Database for PostgreSQL is correct.

  1. 1.Azure Database for PostgreSQL stores query information in a database called azure_sys.
  2. 2.All on-premises PostgreSQL extensions are fully supported in Azure Database for PostgreSQL.
  3. 3.The pgAdmin tool can be used to connect to and monitor Azure Database for PostgreSQL.
chevron_rightShow answer and explanation
  1. Statement 1: Yes
  2. Statement 2: No
  3. Statement 3: Yes

Statement 1 is correct: Azure Database for PostgreSQL records query information in the azure_sys database, queryable via the query_store.qs_view view. Statement 2 is incorrect: only a core set of frequently used extensions is supported; not all on-premises extensions are available, particularly those that interact directly with the OS or require unsupported languages. Statement 3 is correct: pgAdmin can still be used to connect and monitor, although server-focused operations like backup and restore are handled by Microsoft.

Question 13

In Azure SQL Database, an ___ pool allows multiple databases to share a common set of resources such as memory, CPU, and storage, helping to reduce costs for databases with variable workloads.

  • A.elastic
  • B.instance
  • C.serverless
  • D.availability
chevron_rightShow answer and explanation

Correct answer: A. elastic

An Elastic Pool in Azure SQL Database allows multiple databases to share a pool of resources, which is cost-effective when different databases have peak usage at different times. An instance pool is a feature of Azure SQL Managed Instance for sharing resources across smaller instances. Serverless is a single-database configuration option where resources scale automatically. An availability pool is not a real Azure SQL concept.

These are 13 of 58 Azure SQL and Open-Source Databases questions in the full bank

Practice all 400 DP-900 questions in exam format, including drag-and-drop and multi-select questions, with a mock exam and progress tracking.