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

Question 1

Your company is migrating a legacy on-premises SQL Server database to Azure. The database is used by a mission-critical application that requires high availability with automatic failover to a secondary region. The database size is 2 TB and is expected to grow 20% annually. The application uses stored procedures, functions, and SQL Server Agent jobs. You need to select an Azure relational database service that meets the high availability requirements, supports the existing SQL Server features, and minimizes migration effort. What should you choose?

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

Accepted Answer

Azure SQL Managed Instance with failover group. Azure SQL Managed Instance with failover group (option A) is correct because Managed Instance is the only Azure SQL PaaS offering that provides near-100% compatibility with on-premises SQL Server, including SQL Server Agent jobs, stored procedures, and functions, while auto-failover groups provide automatic geo-failover to a secondary region for high availability. It also supports large databases well beyond 2 TB, so the 2 TB size and 20% annual growth are not a constraint. Option B is not a real Azure service name (Azure SQL Database Hyperscale is a tier, not 'Azure Database for SQL Server'), and Hyperscale alone does not provide automatic cross-region failover. Option C, Azure SQL Database single database, lacks SQL Server Agent job support and requires manual geo-replication failover configuration rather than automatic failover. Option D, elastic pool with geo-replication, is designed for multiple databases with varying usage and also does not support SQL Server Agent jobs or automatic failover groups.

Answer

Azure Database for SQL Server (Hyperscale tier)

Answer

Azure SQL Database (single database) with active geo-replication

Answer

Azure SQL Database elastic pool with geo-replication

Question 2

A company uses Azure SQL Database for a customer relationship management (CRM) system. The database is deployed in the East US region. The company has customers worldwide who access the CRM via a web application. Currently, all read and write traffic goes to the primary database in East US, causing high latency for users in Europe and Asia. The company needs to improve read performance for global users while maintaining strong consistency for writes. They also want to minimize cost. Which solution should you recommend?

Accepted Answer

Configure active geo-replication with readable secondary replicas in West Europe and Southeast Asia. Option B is correct because active geo-replication lets you create readable secondary replicas in West Europe and Southeast Asia, so global users can read locally while all writes still go to the single primary in East US, preserving strong consistency for writes. This directly reduces read latency for European and Asian users and is a lower-cost fit than a full auto-failover group or premium read-scale configuration. Option A does not address database read latency for globally distributed users and adds cache-invalidation complexity. Option C is incorrect because geo-redundant storage is for backup/DR, not for serving readable replicas, and read-scale requires the Business Critical tier, which increases cost. Option D is wrong because read replicas in the same East US region would not reduce latency for users in Europe and Asia.

Answer

Implement Azure Redis Cache to cache frequently accessed data

Answer

Use Azure SQL Database with geo-redundant storage and enable read-scale

Answer

Create read replicas in the same region and use Azure Traffic Manager to route read traffic

Question 3

A financial services company is migrating an on-premises SQL Server database to Azure. The database contains sensitive data and must comply with strict regulatory requirements. The company needs to ensure that data is encrypted at rest and in transit, and they want to use their own encryption keys managed in a hardware security module (HSM). Which Azure SQL feature should they implement?

Accepted Answer

Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault Managed HSM. Transparent Data Encryption with customer-managed keys in Azure Key Vault Managed HSM provides encryption at rest with keys controlled by the customer in a hardware security module. This satisfies the regulatory compliance for key control and encryption. Always Encrypted and Dynamic Data Masking serve different purposes, and service-managed TDE keys do not provide customer control.

Answer

Dynamic Data Masking

Answer

Transparent Data Encryption (TDE) with service-managed keys

Answer

Always Encrypted with secure enclaves

Question 4

A company is evaluating Azure SQL Database for a new application. They need to understand which features are natively supported by Azure SQL Database without additional configuration. Which two features are natively supported? (Choose two.)

Accepted Answer

Automatic backups with point-in-time restore. Azure SQL Database natively supports automatic backups with point-in-time restore and built-in high availability with automatic failover. These features are enabled by default and require no configuration. SQL Server Agent, cross-database queries, and CLR integration are not natively supported in Azure SQL Database; they require Azure SQL Managed Instance or SQL Server on VMs.

Answer

SQL Server Agent for job scheduling

Answer

Cross-database queries within the same logical server

Answer

CLR integration for custom assemblies

Question 5

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 6

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 7

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 8

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 9

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 10

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 11

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 12

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 13

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 14

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 15

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