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

Question 1

You are the database administrator for a large financial institution migrating their core banking system to Azure. The system uses SQL Server with many stored procedures, triggers, and CLR assemblies. The database is 2 TB and growing. The migration must minimize application changes and support high availability with automatic failover. You need to select an Azure relational database service. What should you choose?

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

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance provides near 100% compatibility with SQL Server, supports CLR and cross-database queries, and offers high availability with automatic failover. Option A (Azure SQL Database) lacks support for CLR and cross-database queries. Option B (SQL Server on Azure Virtual Machines) requires manual high availability setup. Option C (Azure Database for PostgreSQL) would require significant application changes.

Answer

Azure SQL Database

Answer

Azure Database for PostgreSQL

Answer

SQL Server on Azure Virtual Machines

Question 2

Your company runs a global e-commerce platform on Azure SQL Database. The platform experiences heavy read traffic on product catalog and inventory tables. You need to reduce read latency for users in different geographic regions while keeping write latency low. The solution must be cost-effective and require minimal application changes. Current architecture: a single Azure SQL Database in West US. You have budget for additional Azure resources. What should you implement?

Accepted Answer

Configure Active Geo-Replication to create readable secondaries in regions where users are located.. Active Geo-Replication, creates readable secondaries in other regions, allowing local read access for users in different geographic regions while maintaining single-region writes, which keeps write latency low. It requires minimal application changes (only updating connection strings) and is cost-effective compared to full database copies. Option B (Azure Cosmos DB with multi-region writes) would require significant application rewrite and is not a relational database, so it's not suitable. Option C (Azure Traffic Manager) only routes requests to the primary database, which does not reduce read latency since all reads still hit the single West US region. Option D (Read Scale-Out) provides a read-only endpoint but only within the same region, so it does not address cross-region read latency.

Answer

Migrate the database to Azure Cosmos DB with multi-region writes.

Answer

Use Azure Traffic Manager to route read requests to the primary database in West US.

Answer

Enable Read Scale-Out on the existing database and configure application to use the read-only endpoint.

Question 3

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 4

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. Creating a readable secondary replica offloads the reporting query from the primary database, thus minimizing impact on the transactional workload. The replica can have its own indexes optimized for the report query without affecting primary writes. Option A is incorrect: while partitioning the Orders table by OrderDate can improve query performance through partition elimination, it does not isolate the reporting workload from the primary database; the query still runs against the primary, causing performance degradation. Option B is incorrect: adding a nonclustered columnstore index on the primary database would greatly improve the aggregation performance, but it still runs the report query on the primary, impacting transactional operations. Option C is incorrect: a nonclustered index covering report columns would improve query performance, but it does not offload the workload from the primary; the report query still competes with transactional queries on the same database.

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

Question 5

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?

Accepted Answer

Azure SQL Managed Instance with failover group. Azure SQL Managed Instance is the correct choice because it supports SQL Server Agent, stored procedures, and functions, providing near 100% compatibility with on-premises SQL Server. It offers failover groups that enable automatic failover to a secondary region, meeting the high availability requirement. The 2 TB database size and expected growth are within Managed Instance limits, and migration effort is minimized due to high compatibility. Option B is incorrect because Azure Database for SQL Server is not a recognized service; the Hyperscale tier is part of Azure SQL Database, which does not support SQL Server Agent. Options C and D are incorrect because Azure SQL Database (single or elastic pool) does not support SQL Server Agent, which is required for the application's SQL Server Agent jobs.

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 6

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. Active geo-replication provides readable secondary replicas in other regions (West Europe and Southeast Asia) that can serve read traffic locally, reducing latency for global users, while all write traffic goes to the primary in East US, maintaining strong consistency. Option A is wrong because Azure Redis Cache is a caching layer that does not provide strong consistency for writes and adds complexity; it is not a database-level solution for global read scale. Option C is wrong because geo-redundant storage only provides storage redundancy, not read scaling; read-scale on Azure SQL Database uses in-region replicas, which do not help users in other regions. Option D is wrong because read replicas in the same region (East US) do not reduce latency for users in Europe and Asia; Azure Traffic Manager would still route to East US replicas, resulting in high latency.

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 7

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 8

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 9

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 10

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 11

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 12

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 13

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 14

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 15

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