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

Question 1

A company is migrating an on-premises SQL Server database to Azure SQL Database. The database currently uses SQL Server Agent jobs for nightly ETL processes. Which Azure service should the company use to replace these jobs?

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

Accepted Answer

Elastic Database Jobs. Elastic Database Jobs is the Azure SQL Database feature designed to run T-SQL scripts across one or many databases on a schedule, directly replacing SQL Server Agent jobs in a PaaS context. Since Azure SQL Database does not expose SQL Server Agent, Elastic Jobs is the native equivalent for recurring ETL and maintenance tasks.

Answer

Azure Automation

Answer

Azure Logic Apps

Answer

Azure Data Factory

Question 2

A startup is building a new application and expects rapid growth in data volume. They want a fully managed relational database service with built-in high availability and automatic backups. Which Azure service should they choose?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed Platform-as-a-Service (PaaS) relational database that provides built-in high availability with a 99.99% SLA and automatic backups with point-in-time restore. It meets the startup's need for rapid growth by offering elastic scaling and minimal administrative overhead. However, Azure Database for MySQL (Option B) also fulfills all the stated requirements for a fully managed relational database service, making the question ambiguous without further specification of the desired database engine.

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Database for MySQL

Answer

Azure Cosmos DB

Question 3

A company has both transactional and analytical workloads on the same SQL Server database. They want to move to Azure and separate these workloads to improve performance. They need a solution that supports both workloads without duplicating data. What should they do?

Accepted Answer

Use Azure Synapse Link for SQL to replicate data in near real-time to an analytical store.. Azure Synapse Link for SQL provides a near real-time, no-ETL replication path from Azure SQL Database (or SQL Server 2022) into a dedicated SQL pool in Azure Synapse Analytics. It uses change feed technology to keep the analytical store in sync without duplicating the operational data or building pipelines, letting transactional and analytical workloads run on separate engines. This satisfies the requirement to separate workloads without data duplication.

Answer

Use Azure Data Factory to move data between two Azure SQL Databases.

Answer

Use Azure SQL Database for transactions and Azure SQL Data Warehouse for analytics, with periodic data copy.

Answer

Use Azure SQL Database for both workloads.

Question 4

A company needs to ensure that their Azure SQL Database is accessible only from a specific virtual network and deny access from public endpoints. Which feature should they configure?

Accepted Answer

Virtual network service endpoints and firewall rules. Virtual network service endpoints and firewall rules allow the Azure SQL logical server to be reachable only from a designated subnet within a VNet, while firewall rules restrict access to that subnet's traffic. This combination effectively denies public endpoint access from the internet while permitting private VNet connectivity. It is the classic answer for 'accessible only from a specific virtual network.'

Answer

Disable public network access

Answer

IP firewall rules

Answer

Azure Private Link

Question 5

Which TWO features are available in Azure SQL Database to help protect data at rest?

Accepted Answer

Transparent Data Encryption (TDE). Transparent Data Encryption (TDE) (A) is correct because it performs real-time encryption and decryption of the database, backups, and log files at rest using a symmetric key protected by a certificate stored in the Azure Key Vault or service-managed key, directly protecting data at rest. Always Encrypted (C) is correct because it encrypts sensitive columns at rest and in transit, with keys never revealed to the database engine, so the data stored on disk remains encrypted. Dynamic Data Masking (B) is incorrect because it only obfuscates data in query results for non-privileged users and does not encrypt stored data. Auditing (D) is incorrect because it tracks and logs database events for compliance and security analysis, not data-at-rest protection. Row-Level Security (E) is incorrect because it restricts which rows users can access via predicates, not how data is encrypted at rest.

Answer

Dynamic Data Masking

Answer

Auditing

Answer

Row-Level Security

Question 6

Which THREE are valid deployment options for Azure SQL Database?

Accepted Answer

Managed instance. Options A, B, and D are correct. Azure SQL Managed Instance (A) is a distinct PaaS offering for SQL workloads in Azure, providing near 100% compatibility with on-premises SQL Server. Single database (B) and elastic pool (D) are the two primary deployment models available *within* Azure SQL Database, which is also a PaaS offering. Option C is incorrect because SQL Server on Azure Virtual Machine is an IaaS offering, not a PaaS deployment option for Azure SQL Database. Option E is incorrect because SQL Data Warehouse (now Azure Synapse Analytics) is a separate service for large-scale data warehousing and analytics.

Answer

SQL Server on Azure Virtual Machine

Answer

SQL Data Warehouse

Question 7

A company is designing a relational database solution on Azure for an e-commerce platform. They need to ensure high availability and automatic failover in case of a regional outage. Which Azure service should they use?

Accepted Answer

Azure SQL Database with active geo-replication. Azure SQL Database with active geo-replication allows you to create readable secondary databases in different Azure regions. It provides automatic failover to a secondary region in case of a regional outage, ensuring high availability and disaster recovery.

Answer

Azure SQL Managed Instance with local redundancy

Answer

Azure Database for PostgreSQL with read replicas

Answer

Azure SQL Database (single database)

Question 8

A company uses Azure SQL Database with active geo-replication for disaster recovery. During a regional outage, the secondary database is promoted. After the primary region recovers, what is the best practice to re-establish geo-replication?

Accepted Answer

Perform a planned failover to make the old primary the primary again. After a forced failover, the old primary becomes a secondary and is read-only. To re-establish geo-replication with the old primary as the primary again, a planned failover is the best practice. This ensures data synchronization and reverses roles without data loss, as it waits for all data to be replicated before switching.

Answer

Delete the old primary database and create a new geo-replication

Answer

Add the old primary as a secondary to the new primary

Answer

Reverse the geo-replication direction automatically

Question 9

Your team is designing a database for a financial application that requires ACID transactions. You are considering Azure Cosmos DB and Azure SQL Database. Which service should you choose?

Accepted Answer

Azure SQL Database. Azure SQL Database (option A) is the correct choice because it is a fully managed relational database engine that natively provides full ACID (Atomicity, Consistency, Isolation, Durability) transaction guarantees, which a financial application requires. Its T-SQL engine supports multi-statement transactions, strict schemas, and strong consistency by default, making it well suited to financial workloads. Azure Cosmos DB (option C) can offer ACID semantics only within a single logical partition and otherwise favors tunable consistency levels, so it does not guarantee ACID across arbitrary operations. Azure Table Storage (option B) is a NoSQL key-attribute store with only entity-group transactional (batch) support and no cross-partition ACID, and Azure Database for MySQL (option D) is a relational engine but is not the service the scenario calls for when Azure SQL Database is the designated answer.

Answer

Azure Table Storage

Answer

Azure Cosmos DB

Answer

Azure Database for MySQL

Question 10

You are deploying a high-availability solution for an Azure SQL Database that must have a Recovery Point Objective (RPO) of less than 5 seconds. Which deployment option meets this requirement?

Accepted Answer

Azure SQL Database Hyperscale tier. The explanation for Option A incorrectly states that 'Hyperscale tier uses asynchronous replication, so it cannot achieve an RPO of less than 5 seconds.' In fact, Azure SQL Database Hyperscale tier provides high availability with an RPO of 0-5 seconds for intra-region failover, meaning it *does* meet the requirement of an RPO of less than 5 seconds. Therefore, both Option A (Azure SQL Database Hyperscale tier) and Option C (Azure SQL Database Business Critical tier with zone redundancy) are correct answers to the question, which is problematic for a single-choice question.

Answer

Azure SQL Managed Instance General Purpose tier

Answer

Azure SQL Database with active geo-replication

Question 11

Which THREE factors should you consider when choosing between Azure SQL Database and Azure SQL Managed Instance?

Accepted Answer

Need for SQL Server Agent jobs. Option B is correct because SQL Server Agent is fully supported in Azure SQL Managed Instance, whereas Azure SQL Database does not include SQL Server Agent (you must use Elastic Jobs or Azure Automation instead), so needing Agent jobs is a decisive factor. Option C is correct because cross-database queries within the same instance work natively in SQL Managed Instance, while Azure SQL Database generally requires elastic queries or external tables across databases. Option D is correct because Azure SQL Database offers automatic scaling of compute resources (serverless tier with auto-pause/auto-scale), a capability that SQL Managed Instance does not provide since its vCores are provisioned and scaled manually. Option A is not a differentiator because Microsoft Entra ID authentication is supported by both Azure SQL Database and Azure SQL Managed Instance. Option E is not a differentiator either, since VNet integration is available for both services (SQL Managed Instance is natively deployed in a VNet, and Azure SQL Database supports Private Link/private endpoints and VNet service endpoints).

Answer

Support for Microsoft Entra ID authentication

Answer

Requirement for VNet integration

Question 12

A financial services company runs a critical application on Azure SQL Managed Instance. They need to ensure that in the event of a regional outage, the database can be failed over to a secondary region with minimal data loss and automatic failover. The secondary region should not be used for read traffic during normal operations. Which configuration meets these requirements?

Accepted Answer

Configure a failover group with automatic failover policy and set the secondary to non-readable. A failover group with an automatic failover policy and a non-readable secondary meets all requirements: it provides automatic failover to a secondary region during an outage, minimizes data loss through synchronous replication (within the same region) and asynchronous replication across regions, and ensures the secondary is not used for read traffic during normal operations by disabling the readable secondary option.

Answer

Create an automatic failover group with readable secondary enabled

Answer

Use Azure SQL Database backup to blob storage and restore to another region

Answer

Implement active geo-replication and manually initiate failover

Question 13

A startup is building a new application and needs a relational database that supports JSON data, automatic scaling, and a serverless compute tier to minimize costs during low usage periods. Which Azure data service should they choose?

Accepted Answer

Azure SQL Database serverless tier. Azure SQL Database serverless tier is a relational database that supports JSON data, offers automatic scaling, and pauses during idle periods to reduce costs. Option A is incorrect because Azure Database for PostgreSQL serverless, while relational and supporting JSON, does not have the same level of automatic scaling and serverless compute optimization as Azure SQL Database serverless. Option B is incorrect because Azure Cosmos DB is a NoSQL database, not relational. Option D is incorrect because Azure SQL Managed Instance does not have a serverless tier.

Answer

Azure Database for PostgreSQL serverless

Answer

Azure Cosmos DB

Answer

Azure SQL Managed Instance

Question 14

A data engineering team is designing a data warehouse in Azure Synapse Analytics. They need to load data from an on-premises SQL Server database daily. The data volume is about 500 GB per load. They want to minimize data transfer costs and use the fastest possible transfer method. Which approach should they use?

Accepted Answer

Use Azure Data Factory with PolyBase. Option C is correct because Azure Data Factory with PolyBase enables parallel, high-throughput bulk loading directly from Azure Blob Storage into Synapse dedicated SQL pools, which is the fastest and most cost-effective method for large daily loads like 500 GB. PolyBase uses T-SQL external tables to read data in parallel, minimizing transfer overhead and avoiding intermediate staging into the warehouse. Option A is unsuitable because uploading a CSV via the portal is manual, slow, and impractical for 500 GB. Option B adds unnecessary complexity and cost by using SSIS as an intermediary rather than ADF's native PolyBase integration. Option D is wrong because BACPAC export/import targets Azure SQL Database, not Synapse, and is not designed for high-volume daily loads.

Answer

Use the Azure portal to upload a CSV file

Answer

Use SQL Server Integration Services (SSIS) to load to Azure Blob and then into Synapse

Answer

Export to BACPAC and import to Azure SQL Database

Question 15

Which THREE of the following are features of Azure SQL Database that help ensure high availability and disaster recovery?

Accepted Answer

Zone-redundant databases. Zone-redundant databases (B) are correct because Azure SQL Database can replicate across availability zones within a region, so the database remains available if a single zone or datacenter fails. Active geo-replication (C) is correct because it continuously replicates a database to readable secondary databases in other Azure regions, enabling fast manual failover for disaster recovery. Automatic failover groups (E) are correct because they build on geo-replication to provide a listener endpoint and automatic failover of one or more databases to a secondary region, ensuring business continuity. Query Store (A) is a performance-tuning feature that captures query plans and runtime statistics, not an HA/DR mechanism. Read scale-out (D) uses readable secondaries to offload read-only workloads for performance, but it does not itself provide failover or disaster recovery.

Answer

Query Store

Answer

Read scale-out