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

Question 1

Which TWO factors should you consider when choosing between Azure SQL Database and SQL Server on Azure Virtual Machines?

Accepted Answer

Compatibility with on-premises SQL Server features. When choosing between Azure SQL Database (PaaS) and SQL Server on Azure Virtual Machines (IaaS), key factors include compatibility with on-premises SQL Server features (C) because SQL Database may not support all features (e.g., SQL Agent, CLR, etc.) while SQL Server on VM provides full feature compatibility. The level of administrative control required (E) is also critical because SQL Database is a managed service with less administrative overhead, whereas SQL Server on VM gives full control over the OS and SQL Server instance. Options A (auditing), B (geo-replication), and D (Transparent Data Encryption) are all supported by both services, so they are not differentiating factors.

Answer

Need for database auditing

Answer

Support for geo-replication

Answer

Need for Transparent Data Encryption

Question 2

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) is correct because it is a fully relational database that supports full ACID transactions, making it ideal for financial applications. Option B (Azure Table Storage) is a NoSQL key-value store that does not support ACID transactions. Option C (Azure Cosmos DB) is a NoSQL database that provides limited transactional support (e.g., through stored procedures) but does not guarantee full ACID across multiple documents. Option D (Azure Database for MySQL) is a relational database that supports ACID transactions, but the question specifically asks to choose between Azure Cosmos DB and Azure SQL Database, so Azure SQL Database is the best answer.

Answer

Azure Table Storage

Answer

Azure Cosmos DB

Answer

Azure Database for MySQL

Question 3

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 4

Which TWO Azure services can be used to store relational data?

Accepted Answer

Azure SQL Database. (Azure SQL Database) and Option C (Azure Database for PostgreSQL) are fully managed relational database services in Azure. Option B (Azure Cosmos DB) is a NoSQL database. Option D (Azure Blob Storage) is object storage for unstructured data. Option E (Azure Table Storage) is a NoSQL key-value store.

Answer

Azure Cosmos DB

Answer

Azure Blob Storage

Answer

Azure Table Storage

Question 5

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. Azure Data Factory with PolyBase enables high-speed parallel loading into Azure Synapse, minimizing data transfer costs and latency. Option A is wrong because the Azure portal upload is impractical for 500 GB and lacks automation. Option B is wrong because SSIS with Azure Blob is slower, requires additional components, and does not leverage the optimized PolyBase connector. Option D is wrong because BACPAC export/import is not designed for large-volume data transfers and would be slower and more costly.

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 6

Which TWO of the following are valid relational database services in Azure?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed relational database service in Azure, supporting SQL Server-based relational data. Azure Database for MySQL is also a fully managed relational database service, specifically for MySQL. Both provide relational database capabilities, including ACID transactions and support for their respective SQL dialects, making them valid relational database services.

Answer

Azure Data Lake Storage

Answer

Azure Cosmos DB

Answer

Azure Cache for Redis

Question 7

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

Accepted Answer

Zone-redundant databases. Options B, C, and E are correct. Zone-redundant databases provide resilience within a region by replicating across availability zones. Active geo-replication provides disaster recovery to a secondary region. Automatic failover groups enable automatic failover to a secondary. Option A is wrong because Query Store is for performance tuning, not high availability or disaster recovery. Option D is wrong because Read scale-out is for performance, not HA/DR.

Answer

Query Store

Answer

Read scale-out

Question 8

Which TWO of the following statements about Azure SQL Database elastic pools are true?

Accepted Answer

They allow multiple databases to share a fixed set of resources. Elastic pools allow multiple databases to share a fixed set of resources (eDTUs or vCores), which provides predictable performance and cost savings. Databases within a pool automatically consume resources as needed, up to the pool's configured limits, making this a shared-resource model.

Answer

They store actual database data

Answer

They cannot be scaled after creation

Answer

They are limited to a single Azure SQL Database server

Question 9

Your organization is migrating a large on-premises SQL Server database to Azure SQL Database. The database is used by a critical line-of-business application that requires near-zero downtime during migration. The application uses a mix of read and write operations, and you need to minimize the cutover time. The database is about 2 TB in size. You plan to use Azure Database Migration Service (DMS) for the migration. The source SQL Server is version 2019 Enterprise Edition. The target is a Business Critical tier Azure SQL Database. You need to choose the appropriate migration method and configuration. Which of the following actions should you take?

Accepted Answer

Use online migration mode with DMS on Premium tier. Using online migration mode with continuous sync allows near-zero downtime by replicating changes from source to target, and using the Premium tier for DMS provides better performance for large databases. Option A is wrong because offline migration causes downtime. Option B is wrong because using Basic tier DMS is too slow for 2 TB. Option D is wrong because offline migration mode requires downtime, and manually copying remaining changes still results in significant cutover time, not meeting the near-zero downtime requirement.

Answer

Use offline migration mode with DMS on Standard tier

Answer

Use online migration mode with DMS on Basic tier

Answer

Use offline migration mode with DMS on Premium tier, then manually copy remaining changes

Question 10

Your team is migrating an on-premises SQL Server database to Azure. The database is used by a critical application that requires high availability and automatic failover. You need to choose a deployment option that provides a 99.99% SLA and supports automated backups. What should you use?

Accepted Answer

Azure SQL Database (Business Critical tier with zone redundancy). Azure SQL Database in the Business Critical service tier with zone redundancy provides a 99.995% SLA and supports automated backups, meeting the high availability and automatic failover requirements. Option A (Azure Database for PostgreSQL) is a different database engine and not suitable for migrating a SQL Server database. Option C (SQL Server on Azure Virtual Machines) requires manual configuration for high availability and does not offer automated backups by default. Option D (Azure SQL Managed Instance) provides a 99.99% SLA and automated backups. While it supports zone redundancy, it is not enabled by default and would need to be configured. Option B explicitly includes zone redundancy, offering 99.995% SLA, making it a more robust choice for the specified high availability.

Answer

Azure Database for PostgreSQL

Answer

SQL Server on Azure Virtual Machines

Answer

Azure SQL Managed Instance

Question 11

An e-commerce company runs a transactional database on Azure SQL Database. During peak shopping seasons, they experience performance degradation due to high read traffic on product catalog tables. The company wants to offload read queries to a read-only copy of the database without affecting write performance. What should they implement?

Accepted Answer

Configure Active Geo-Replication to create a readable secondary replica.. Active Geo-Replication allows creating readable secondary replicas in the same or different region, which can offload read queries without affecting write performance on the primary. Option B (Read Scale-Out) is not available in all service tiers and is not the primary feature for creating a read-only copy; Active Geo-Replication is the correct feature. Option C (Azure Cache for Redis) adds caching but does not provide a read-only copy of the database. Option D (Failover groups) is used for disaster recovery and does not provide a readable secondary for read offloading.

Answer

Enable Read Scale-Out on the database.

Answer

Implement Azure Cache for Redis to cache product catalog data.

Answer

Create a failover group with a secondary region.

Question 12

A financial services company needs to store transaction data in a relational database that must comply with strict regulatory requirements. The data must be encrypted at rest and in transit, and the database must support automatic failover to a secondary region. Which Azure solution meets these requirements?

Answer

Azure Database for MySQL

Answer

Azure Cosmos DB

Answer

Azure SQL Database with Active Geo-Replication

Answer

SQL Server on Azure Virtual Machines

Question 13

A company needs to migrate a large on-premises SQL Server database to Azure. The database uses features like SQL Server Agent jobs, cross-database queries, and common language runtime (CLR) assemblies. Which Azure service supports these features?

Accepted Answer

Azure SQL Managed Instance. The current explanation correctly states that Azure SQL Managed Instance supports the features. However, it also acknowledges that Option A (SQL Server on Azure Virtual Machines) supports all these features but attempts to disqualify it by stating it is an IaaS solution, not a fully managed PaaS service. Since the question does not specify a requirement for a PaaS or fully managed service, this distinction does not make D the *sole* correct answer based on the stem's wording. To make D the unique correct answer, the stem should be modified to explicitly ask for a PaaS or fully managed service.

Answer

SQL Server on Azure Virtual Machines

Answer

Azure SQL Database

Answer

Azure Database for PostgreSQL

Question 14

A retail company uses Azure SQL Database for its inventory system. The database stores sensitive customer information that must be encrypted at rest to comply with data protection regulations. Which feature should they enable?

Accepted Answer

Transparent Data Encryption (TDE). Transparent Data Encryption (TDE) performs real-time encryption and decryption of the database, backups, and transaction log files at rest without requiring changes to the application. Option A (Dynamic Data Masking) limits exposure of sensitive data by obfuscating it in query results but does not encrypt at rest. Option B (Always Encrypted) protects sensitive data in transit and at rest, but it is a column-level encryption feature and requires client-side changes. Option C (Azure Information Protection) is a cloud-based solution for classifying and protecting documents and emails, not database encryption. Therefore, TDE is the correct feature for encrypting data at rest in Azure SQL Database.

Answer

Dynamic Data Masking

Answer

Always Encrypted

Answer

Azure Information Protection

Question 15

A company runs a critical application on Azure SQL Database. They need to ensure that in the event of a regional disaster, the database can be failed over to a secondary region with minimal data loss. The solution should also allow the secondary region to be used for read-only queries. What should they configure?

Accepted Answer

Create a failover group with a readable secondary in the secondary region.. A failover group with a readable secondary provides automatic failover in case of regional disaster and allows read-only queries on the secondary. Option A (Active Geo-Replication) requires manual failover, which could lead to data loss if not performed immediately. Option B (Azure Front Door) is used for global web traffic routing and does not manage database failover. Option D (Cosmos DB) is a NoSQL database and does not support the same relational capabilities as Azure SQL Database.

Answer

Configure Active Geo-Replication and manually failover when needed.

Answer

Use Azure Front Door to route traffic to the secondary region.

Answer

Migrate to Azure Cosmos DB for multi-region writes.