DP-900 Identify considerations for relational data on Azure — Timed 100 Questions

Question 1

A company is migrating an on-premises SQL Server database to Azure. They want to ensure that database administrators (DBAs) can perform administrative tasks but cannot view sensitive customer data in query results. Which Azure SQL feature should they implement?

Accepted Answer

Always Encrypted. Always Encrypted ensures that sensitive data is encrypted at all times, including during query processing, and that the encryption keys are never revealed to the database engine. This allows DBAs to perform administrative tasks (e.g., backups, index maintenance) while being unable to view the plaintext data in query results, because the decryption occurs only on the client side.

Answer

Dynamic Data Masking

Answer

Transparent Data Encryption

Answer

Row-Level Security

Question 2

A software-as-a-service (SaaS) provider hosts a multi-tenant application with a separate database for each tenant. They anticipate scaling to thousands of tenants and want to minimize cost while allowing tenants to share resources flexibly. Which Azure SQL offering is most suitable?

Accepted Answer

Azure SQL Database elastic pool. Azure SQL Database elastic pool is the most suitable choice because it allows multiple single databases (one per tenant) to share a fixed set of resources (DTUs or vCores) within a pool, optimizing cost by averaging resource usage across tenants. This model supports scaling to thousands of tenants while providing resource elasticity and predictable pricing, as tenants with low activity can use unused capacity from busy ones without over-provisioning.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Managed Instance

Answer

SQL Server on Azure Virtual Machine

Question 3

A company runs an e-commerce application backed by an on-premises SQL Server database. They plan to migrate to Azure SQL Database and require automatic failover across two Azure regions for disaster recovery. The application must continue to connect using the same connection string after a failover, with no code changes. Which feature should they implement?

Accepted Answer

Failover groups. Failover groups (Option C) enable automatic, geo-redundant failover across two Azure regions while providing a single read-write listener endpoint that remains unchanged after failover. This ensures the application can continue using the same connection string without any code modifications, meeting the stated requirement for disaster recovery with zero application changes.

Answer

Active Geo-Replication

Answer

Elastic pools

Answer

SQL Server on Azure Virtual Machine with Always On Availability Groups

Question 4

A company is migrating a legacy on-premises database to Azure. They require the ability to run cross-database queries within the same logical server, full control over database collation settings, and want to minimize management overhead for infrastructure patching. The database size is under 1 TB and they do not need instance-level features like SQL Agent jobs or linked servers. Which Azure SQL offering should they choose?

Accepted Answer

Azure SQL Database. Azure SQL Database is the correct choice because it supports cross-database queries within the same logical server via elastic queries, allows full control over database-level collation settings, and is a fully managed Platform-as-a-Service (PaaS) offering that handles infrastructure patching automatically. With a database size under 1 TB and no need for instance-level features like SQL Agent jobs or linked servers, Azure SQL Database meets all requirements while minimizing management overhead.

Answer

Azure SQL Managed Instance

Answer

SQL Server on Azure Virtual Machine

Answer

Azure Synapse SQL pool

Question 5

A company is migrating an on-premises SQL Server database to Azure. The database uses SQL Server Integration Services (SSIS) packages for daily ETL processes. The company wants to minimize administrative overhead for patching and backup management, but needs to retain full control over instance-level configurations and support for SSIS. Which Azure SQL service should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is correct because it provides near 100% compatibility with on-premises SQL Server, including full support for SQL Server Integration Services (SSIS) via Azure-SSIS Integration Runtime, while offloading patching and backup management to the platform. It also allows full control over instance-level configurations such as collation, CLR, and SQL Agent jobs, which are not available in Azure SQL Database.

Answer

Azure SQL Database

Answer

Azure Synapse Analytics

Answer

Azure SQL Server on Azure Virtual Machines

Question 6

A startup is developing a web application that requires a relational database with PostgreSQL compatibility. They want a fully managed service that automatically handles backups, patching, and provides high availability with a 99.99% SLA. Which Azure service should they choose?

Accepted Answer

Azure Database for PostgreSQL. Azure Database for PostgreSQL is the correct choice because it is a fully managed relational database service that offers PostgreSQL compatibility, automatic backups, patching, and high availability with a 99.99% SLA. This meets the startup's requirements for a managed PostgreSQL solution without the need for manual administration.

Answer

Azure SQL Database

Answer

Azure Database for MySQL

Answer

Azure Cosmos DB for PostgreSQL

Question 7

A company has an existing on-premises SQL Server database that is 500 GB in size. The database uses SQL Server Agent jobs for scheduled maintenance and linked servers to query data from a remote SQL Server instance. The company wants to migrate to Azure with minimal application changes and needs automated backups and patching. Which Azure SQL service should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is correct because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs and linked servers, while offering automated backups and patching. This minimizes application changes during migration, unlike other Azure SQL options that lack these features.

Answer

Azure SQL Database

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Database for PostgreSQL

Question 8

A company plans to migrate a 2-TB on-premises SQL Server database to Azure. The database uses SQL Server Agent jobs for scheduled maintenance and requires automatic failover across Azure regions. The company wants a fully managed service with minimal application changes. Which Azure SQL service should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is correct because it provides near 100% compatibility with SQL Server, including support for SQL Server Agent jobs, and offers automatic failover across Azure regions via failover groups. It is a fully managed service that requires minimal application changes, unlike Azure SQL Database which lacks SQL Server Agent and has limited cross-region failover capabilities.

Answer

Azure SQL Database

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Synapse Analytics

Question 9

A company is migrating an on-premises SQL Server database to Azure. The database is 800 GB, uses SQL Server Agent jobs for scheduled tasks, and needs to link to another on-premises SQL Server instance via linked servers. The company wants a fully managed service with minimal application changes. Which Azure SQL service should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is correct because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs and linked servers, while being a fully managed platform-as-a-service (PaaS) offering. This allows the company to migrate the 800 GB database with minimal application changes, as it preserves the existing instance-level features without requiring a rearchitecture.

Answer

Azure SQL Database elastic pool

Answer

Azure SQL Database single database

Answer

Azure Synapse Analytics dedicated SQL pool

Question 10

A company is migrating a 1.5 TB on-premises SQL Server database to Azure. The database relies on SQL Server Agent jobs for daily ETL processes and uses linked servers to query data from another on-premises SQL Server database. The company wants a fully managed PaaS service that requires minimal application changes. Which Azure SQL service should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs and linked servers, while being a fully managed PaaS service. This minimizes application changes, as the migration can leverage the existing database code and features without significant rework.

Answer

Azure SQL Database

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Synapse Analytics

Question 11

A company is migrating a 3-TB on-premises SQL Server database to Azure. The database heavily uses cross-database queries with three-part names (e.g., db.schema.table) and relies on SQL Server Agent for scheduled maintenance jobs. They want a fully managed PaaS service with automatic backups and patching, while minimizing application code changes. Which Azure SQL service should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near-100% compatibility with on-premises SQL Server, including support for cross-database queries using three-part names (db.schema.table) and SQL Server Agent for scheduled maintenance jobs. As a fully managed PaaS service, it offers automatic backups, patching, and high availability while minimizing application code changes, unlike Azure SQL Database which lacks cross-database query support and SQL Agent.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database (elastic pool)

Answer

Azure Synapse Analytics dedicated SQL pool

Question 12

A company runs a customer-facing web application that uses an Azure SQL Database. The database experiences highly variable workloads: high traffic during business hours and low traffic at night and on weekends. The company wants to pay only for the compute resources consumed and automatically scale compute capacity based on demand, while maintaining the ability to pause during inactivity. Which Azure SQL Database service tier should they choose?

Accepted Answer

Serverless. The Serverless tier is designed for workloads with variable traffic and idle periods, as it automatically scales compute resources based on demand and can pause the database during inactivity, charging only for consumed compute and storage. This matches the requirement to pay only for resources used and to pause when there is no traffic, such as at night and weekends.

Answer

Hyperscale

Answer

Provisioned (General Purpose)

Answer

Business Critical

Question 13

A company is migrating a 2-TB on-premises SQL Server database to Azure. The database uses SQL Server Agent jobs for scheduled maintenance, relies on linked servers to query data from another SQL Server instance, and requires cross-database queries within the same instance. The company wants a fully managed PaaS service that minimizes application code changes and provides automatic backups and patching. Which Azure SQL service should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs, linked servers, and cross-database queries within the same instance. As a fully managed PaaS service, it offers automatic backups, patching, and high availability while minimizing application code changes, unlike Azure SQL Database which lacks instance-scoped features.

Answer

Azure SQL Database (Single Database)

Answer

Azure SQL Database (Elastic Pool)

Answer

SQL Server on Azure Virtual Machine

Question 14

A company is migrating a 500 GB on-premises SQL Server database to Azure. The database uses SQL Server Agent for scheduled maintenance jobs and requires the ability to run cross-database queries within the same logical server. The company wants a PaaS service that minimizes management overhead for patching and backups while preserving these SQL Server features. Which Azure SQL service should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including SQL Server Agent and cross-database queries within the same instance. As a PaaS service, it handles patching and backups automatically, minimizing management overhead while preserving these required features.

Answer

Azure SQL Database

Answer

SQL Server on Azure Virtual Machine

Answer

Azure Database for MySQL

Question 15

A financial company is migrating a 2-TB on-premises SQL Server database to Azure. The database uses SQL Server Agent jobs for data validation and cleanup, and it performs cross-database queries using three-part names (e.g., DB1.schema.table). The company requires a fully managed PaaS service that supports these features with minimal application changes. Which Azure SQL service should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near-100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs and cross-database queries using three-part names (e.g., DB1.schema.table). As a fully managed PaaS service, it minimizes application changes while offloading infrastructure management, making it ideal for migrating a 2-TB database with these specific requirements.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database elastic pool

Answer

SQL Server on Azure Virtual Machine

Question 16

A company plans to migrate an on-premises SQL Server database to Azure. The database uses SQL Server Agent jobs for scheduled maintenance and relies on linked servers to query data from another SQL Server instance. It also performs cross-database queries within the same instance. The company wants a fully managed PaaS service that requires minimal application changes and provides automated backups and patching. Which Azure SQL service should they choose?

Accepted Answer

B. Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs, linked servers, and cross-database queries within the same instance. It is a fully managed PaaS service that offers automated backups and patching, minimizing application changes during migration.

Answer

A. Azure SQL Database (single database)

Answer

C. SQL Server on Azure Virtual Machines

Answer

D. Azure SQL Database elastic pool

Question 17

A company has a suite of 20 e-commerce applications, each with its own SQL Server database. The databases vary in size from 5 GB to 100 GB and have unpredictable usage patterns with bursty peaks. The company wants to migrate to Azure SQL Database to benefit from built-in high availability and automatic backups. They need to minimize costs by only paying for the resources each database actually uses, and they want to avoid over-provisioning for peak loads. Which Azure SQL Database deployment option should they choose?

Accepted Answer

Azure SQL Database Elastic Pool. Azure SQL Database Elastic Pool is the correct choice because it allows multiple databases to share a fixed pool of resources (DTUs or vCores), enabling cost efficiency by pooling and reallocating resources across databases with unpredictable, bursty usage patterns. This avoids over-provisioning for peak loads while still providing built-in high availability and automatic backups, as each database in the pool benefits from these features without needing individual resource reservations.

Answer

Azure SQL Database (single database) with Serverless compute tier

Answer

Azure SQL Managed Instance

Answer

Azure SQL Database Hyperscale

Question 18

A company has 15 SQL Server databases, ranging from 50 GB to 200 GB each. The databases experience unpredictable load spikes during the day. They want to migrate to Azure SQL Database to minimize management overhead and reduce costs by allowing databases to share resources, while ensuring each database can burst to higher performance when needed. Which deployment option should they choose?

Accepted Answer

B) Elastic pool. Elastic pools allow multiple databases to share a fixed pool of resources (DTUs or vCores), which reduces costs by pooling unused capacity and enables each database to automatically burst to higher performance when needed. This matches the company's need to minimize management overhead while handling unpredictable load spikes across 15 databases ranging from 50 GB to 200 GB.

Answer

A) Single database with Provisioned throughput tier

Answer

C) SQL Managed Instance

Answer

D) SQL Server on Azure Virtual Machine

Question 19

A company is migrating a 500 GB financial database to Azure. The database requires low read/write latency, supports a high number of concurrent transactions, and must have a Recovery Point Objective (RPO) of less than 5 seconds and a Recovery Time Objective (RTO) of less than 30 minutes. The company is willing to pay more for these guarantees. Which Azure SQL Database service tier should they choose?

Accepted Answer

Business Critical. Business Critical is the correct choice because it provides the lowest read/write latency through always-on secondary replicas and uses local SSD storage, which is essential for high-concurrency transactional workloads. It also guarantees an RPO of less than 5 seconds via synchronous data replication and an RTO of under 30 minutes, meeting the strict recovery requirements.

Answer

General Purpose

Answer

Hyperscale

Answer

Serverless (General Purpose)

Question 20

A company needs to migrate 10 on-premises SQL Server databases (each 50–200 GB) to Azure. The databases frequently run cross-database queries using three-part names (e.g., DB1.dbo.table) and rely on SQL Server Agent for maintenance tasks. They want to minimize management overhead and share resources across databases to reduce costs. Which Azure SQL deployment option should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is correct because it provides native support for cross-database queries using three-part names (e.g., DB1.dbo.table) and SQL Server Agent for maintenance tasks, which are critical requirements. It also offers a fully managed platform that minimizes management overhead while allowing resource sharing across databases within the instance, reducing costs compared to single databases or VMs.

Answer

Azure SQL Database elastic pool

Answer

SQL Server on Azure Virtual Machine

Answer

Azure SQL Database single database