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

Question 1

A software company is migrating an on-premises SQL Server database to Azure SQL Database. The database currently uses SQL Server Agent jobs for regular maintenance tasks. The company wants to minimize code changes during migration. Which Azure SQL Database feature should they use to replace SQL Server Agent jobs?

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

Accepted Answer

Elastic Jobs. Elastic Jobs is the Azure SQL Database feature specifically designed to replace SQL Server Agent jobs by allowing you to run T-SQL scripts across multiple databases on a schedule. It provides a job scheduler and execution engine that is compatible with existing T-SQL maintenance scripts, minimizing code changes during migration from on-premises SQL Server.

Answer

Azure Functions

Answer

Azure Automation

Answer

SQL Server Agent (available in Azure SQL Database)

Question 2

A healthcare organization uses Azure SQL Database to store patient records. To comply with HIPAA regulations, they need to encrypt sensitive columns (e.g., Social Security numbers) at rest and control access to the encryption keys. Which feature should they use?

Accepted Answer

Always Encrypted. Always Encrypted. Always Encrypted is a feature designed to protect sensitive data, such as Social Security numbers, by encrypting it at rest and in transit, with the encryption keys stored outside of Azure SQL Database, providing client-side key management. This satisfies the HIPAA requirement for encrypting sensitive columns and controlling access to keys.

Option A: Dynamic Data Masking obfuscates data from non-privileged users but does not encrypt the data; it can be reversed by privileged users.

Option B: Row-Level Security restricts access to rows based on user characteristics but does not encrypt columns.

Option D: Transparent Data Encryption (TDE) encrypts the entire database at rest but does not provide column-level encryption or client-side key control.

Answer

Dynamic Data Masking

Answer

Row-Level Security

Answer

Transparent Data Encryption (TDE)

Question 3

A multinational corporation is deploying a global application using Azure SQL Database. They need to ensure that users in different geographic regions experience low latency reads. The application can tolerate slightly stale data for reads, but writes must be strongly consistent and must occur in a single primary region. Which feature should they implement?

Accepted Answer

Active geo-replication. Active geo-replication in Azure SQL Database allows up to four readable secondary databases in different Azure regions, providing low-latency reads for geographically distributed users while maintaining a single writable primary. Because the application tolerates slightly stale reads, the asynchronous replication of active geo-replication is acceptable, and writes remain strongly consistent on the single primary. This matches the requirement exactly.

Answer

Azure Cosmos DB with multi-master

Answer

Failover groups

Answer

Read scale-out

Question 4

A company uses Azure SQL Database and needs to audit all data modifications (INSERT, UPDATE, DELETE) for compliance purposes. The audit logs must be stored for 7 years. Which feature should they enable?

Accepted Answer

SQL Database auditing. SQL Database auditing captures all data modifications (INSERT, UPDATE, DELETE) and can store logs in Azure storage, Log Analytics, or Event Hubs with retention up to 7 years. Option A is wrong because Advanced Threat Protection detects suspicious activities, not audits modifications. Option C is wrong because vulnerability assessment scans for security weaknesses, not logging changes. Option D is wrong because Transparent Data Encryption (TDE) encrypts data at rest, it does not log modifications.

Answer

Advanced Threat Protection

Answer

Vulnerability assessment

Answer

Transparent Data Encryption (TDE)

Question 5

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

Accepted Answer

Azure SQL Database. Azure SQL Database (B) is a fully managed relational database service built on the SQL Server engine, offering T-SQL, relational schemas, and ACID transactions, so it is a valid relational database service in Azure. Azure Database for MySQL (E) is a fully managed relational database service based on the MySQL Community Edition engine, supporting relational tables, SQL queries, and ACID compliance, making it a valid relational database service as well. Azure Data Lake Storage (A) is a scalable object/file storage service for big data analytics, not a relational database engine. Azure Cosmos DB (C) is a globally distributed multi-model NoSQL database (document, key-value, graph, column-family), not a relational database service. Azure Cache for Redis (D) is an in-memory key-value cache/data store, not a relational database service.

Answer

Azure Data Lake Storage

Answer

Azure Cosmos DB

Answer

Azure Cache for Redis

Question 6

A company is designing a new application that will store customer orders in a relational database on Azure. The data includes order IDs, customer IDs, product IDs, quantities, and order dates. The application needs to support complex queries that join multiple tables and enforce referential integrity. Which Azure service should the company use?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed relational database engine (SQL Server-based) that supports T-SQL, complex multi-table joins, foreign keys, and referential integrity constraints. The described workload — orders with IDs, quantities, dates, and joins across customers/products — is a classic OLTP relational pattern, which Azure SQL Database is purpose-built for.

Answer

Azure Table Storage

Answer

Azure Cosmos DB

Answer

Azure Blob Storage

Question 7

Which TWO of the following are benefits of using Azure SQL Database over SQL Server on Azure Virtual Machines?

Accepted Answer

Built-in high availability with automatic failover. Option A is correct because Azure SQL Database is a fully managed PaaS offering that includes built-in high availability with automatic failover, typically using a 99.99% SLA and replicas managed by the service, whereas SQL Server on Azure VMs requires you to configure Always On availability groups or failover cluster instances yourself. Option B is correct because Azure SQL Database handles automated patching and updates of the database engine and underlying OS transparently, while SQL Server on Azure VMs leaves OS and SQL Server patching to the customer. Option C is not a benefit because Azure SQL Database does not let you choose any number of vCores arbitrarily; vCore counts are tied to specific hardware generations and service tiers, and cost depends on the chosen tier and compute size. Option D is incorrect because installing custom software on the database server is not possible in Azure SQL Database, but it is possible on SQL Server on Azure VMs. Option E is incorrect because full control over the operating system is a characteristic of SQL Server on Azure VMs (IaaS), not Azure SQL Database, which abstracts the OS away from the customer.

Answer

Lower cost because you can choose any number of vCores

Answer

Ability to install custom software on the database server

Answer

Full control over the operating system

Question 8

Your company uses Azure SQL Database to power a global application. You need to ensure that users in Europe and Asia have low-latency read access to product data, while writes are synchronized across all regions. What should you configure?

Accepted Answer

Configure Active Geo-Replication with readable secondaries in Europe and Asia.. Active Geo-Replication for Azure SQL Database allows you to configure readable secondary replicas in different Azure regions. This provides low-latency read access for users in Europe and Asia by directing their read traffic to the nearest secondary, while writes are synchronized asynchronously to all secondaries, ensuring data consistency across regions.

Answer

Use Azure Traffic Manager to route users to the nearest Azure SQL Database instance.

Answer

Migrate to Azure Cosmos DB for multi-region writes.

Answer

Create a failover group that includes all regions.

Question 9

A startup is building a web application with a relational database backend. They expect variable traffic and want to minimize costs by scaling the database automatically based on demand. Which Azure service should they use?

Accepted Answer

Azure SQL Database serverless. Azure SQL Database serverless is the correct choice because it automatically scales compute resources based on demand and pauses the database during idle periods, charging only for storage and minimal compute. This aligns with the startup's need for variable traffic and cost minimization, as it eliminates the need to provision for peak capacity manually.

Answer

Azure SQL Managed Instance

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Database for MariaDB

Question 10

A multinational corporation runs a mission-critical relational database on Azure SQL Database. They require automatic failover to a secondary region in case of a regional outage, with no data loss. The secondary region must also be readable for reporting purposes. What should they implement?

Accepted Answer

Failover group with a readable secondary in a different region. A failover group with a readable secondary in a different region provides automatic failover (no manual intervention) and the secondary can be used for read-only queries. This meets the requirement of automatic failover, no data loss (synchronous replication when using Premium or Business Critical tiers), and read access for reporting. Option A (Active Geo-Replication) requires manual failover, not automatic. Option B (Read Scale-Out) creates a local readable secondary, not in a different region, and does not provide failover. Option C (Azure Site Recovery) is designed for VM and physical server replication, not for Azure SQL Database.

Answer

Active Geo-Replication with manual failover

Answer

Read Scale-Out with a local secondary replica

Answer

Azure Site Recovery for the database server

Question 11

A company is designing a multi-tenant SaaS application. Each tenant has its own relational database, but the total number of tenants is expected to grow rapidly. The company wants to manage all databases efficiently and optimize costs by sharing resources among tenants with low usage. What should they use?

Accepted Answer

Azure SQL Database elastic pools. Elastic pools in Azure SQL Database allow sharing resources among multiple databases, optimizing cost for low-usage tenants in a multi-tenant SaaS application. Option A (SQL Server on Azure Virtual Machines) requires manual resource management and does not offer elastic pools. Option B (Azure SQL Managed Instance) is designed for single large databases, not for sharing resources across many databases. Option C (Azure Database for MySQL) does not have elastic pools; elastic pools are a feature of Azure SQL Database.

Answer

SQL Server on Azure Virtual Machines

Answer

Azure SQL Managed Instance

Answer

Azure Database for MySQL with elastic pools

Question 12

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) is the correct choice because it encrypts Azure SQL Database data at rest at the page level, protecting the database files, backups, and log files without requiring application changes. This directly satisfies the regulatory requirement for encryption at rest of sensitive customer information. Dynamic Data Masking only obfuscates data in query results and does not encrypt stored data, so it does not meet the at-rest encryption requirement. Always Encrypted protects data in use and at rest but is designed for client-side encryption of specific columns and requires application changes, making it unnecessary here. Azure Information Protection is a data classification and labeling service for documents and emails, not a database at-rest encryption feature.

Answer

Dynamic Data Masking

Answer

Always Encrypted

Answer

Azure Information Protection

Question 13

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?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance (option D) is correct because it provides near-100% compatibility with SQL Server, including stored procedures, triggers, and CLR assemblies, while also offering built-in high availability with automatic failover, so the 2 TB core banking database can migrate with minimal application changes. It supports cross-database queries, SQL Agent, and other SQL Server features that Azure SQL Database does not fully support. Azure SQL Database (option A) has limitations such as no CLR support and restricted cross-database access, which would require application changes. Azure Database for PostgreSQL (option B) is a different engine and would require rewriting stored procedures and triggers. SQL Server on Azure Virtual Machines (option C) offers full compatibility but requires you to configure and manage your own high-availability and failover solution, such as Always On availability groups, rather than providing automatic failover as a managed service.

Answer

Azure SQL Database

Answer

Azure Database for PostgreSQL

Answer

SQL Server on Azure Virtual Machines

Question 14

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 (option A) is correct because it creates readable secondary databases in multiple Azure regions, allowing read queries to be served locally in each user's region while writes continue to the primary, reducing read latency without changing the application's write path. It is cost-effective relative to a full re-platforming and requires minimal application changes—just directing read-only connections to the regional secondary endpoints. Option B does not fit because migrating to Azure Cosmos DB with multi-region writes is a major architectural change and typically more expensive, and it changes the data model and consistency semantics. Option C is wrong because Azure Traffic Manager only routes traffic to endpoints and cannot make a single West US database serve reads faster for users in other regions. Option D is incorrect because Read Scale-Out uses a read-only replica in the same region as the primary, so it does not reduce cross-region read latency for geographically distributed users.

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 15

A company uses Azure SQL Database for its e-commerce platform. During a traffic spike, queries against the Orders table become slow. The table has 10 million rows and is clustered on OrderId. The most common query filters by CustomerId and OrderDate range. Which index change would most improve performance?

Accepted Answer

Create a nonclustered index on (CustomerId, OrderDate). The most common query filters by CustomerId and OrderDate, so a nonclustered index on (CustomerId, OrderDate) provides a covering index that allows the database engine to quickly locate rows without scanning the entire clustered index. This index order supports equality on CustomerId and range scans on OrderDate, which is optimal for the query pattern. In Azure SQL Database, this reduces I/O and improves response time during traffic spikes.

Answer

Create a clustered index on CustomerId

Answer

Partition the table by OrderId

Answer

Create a nonclustered index on (OrderDate, CustomerId)