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

Question 1

An organization stores sensitive customer data in Azure SQL Database. They need to encrypt the data at rest and ensure that only authorized applications can decrypt it. Which combination of features should they implement?

Accepted Answer

Transparent Data Encryption (TDE) and Always Encrypted. Option C is correct because TDE encrypts the database at rest, and Always Encrypted ensures that only authorized applications with the column encryption key can decrypt sensitive columns. Option A is wrong because row-level security controls access but does not encrypt data. Option B is wrong because dynamic data masking obfuscates data but does not encrypt. Option D is wrong because auditing logs access but does not encrypt.

Answer

Row-Level Security (RLS) and Transparent Data Encryption (TDE)

Answer

Azure SQL Database Auditing and Transparent Data Encryption (TDE)

Answer

Dynamic Data Masking and Always Encrypted

Question 2

You are designing a solution to store relational data that requires support for graph relationships and JSON queries. Which Azure service should you choose?

Accepted Answer

Azure SQL Database. Azure SQL Database is the correct choice because it natively supports graph relationships through graph tables (NODE and EDGE tables) and JSON queries via built-in JSON functions like JSON_VALUE, JSON_QUERY, and OPENJSON. This makes it ideal for storing relational data that also needs to handle graph traversals and semi-structured JSON data without requiring a separate service.

Answer

Azure Cosmos DB

Answer

Azure Table Storage

Answer

Azure Database for PostgreSQL

Question 3

A company has an Azure SQL Database and needs to run a weekly data aggregation job that takes several hours. They want to minimize cost and avoid impacting production workload. Which approach should they use?

Accepted Answer

Create a read-only replica and run the aggregation on the replica. Creating a read-only replica allows the weekly aggregation job to run against a separate copy of the database without affecting the production workload. Since the replica is read-only, it incurs additional compute costs only during the aggregation window, and you can scale it down or stop it when not in use, minimizing overall cost.

Answer

Migrate the database to the Hyperscale service tier

Answer

Use Azure Elastic Jobs to run the aggregation during off-peak hours

Answer

Increase the DTU or vCore size of the database to handle the load

Question 4

You are designing a multi-tenant SaaS application using Azure SQL Database. Each tenant has its own database. You need to perform maintenance across all databases efficiently. Which feature should you use?

Accepted Answer

Elastic Jobs. Option C is correct because Elastic Jobs allows executing T-SQL scripts across multiple databases. Option A is wrong because elastic pools are for resource sharing, not for maintenance. Option B is wrong because failover groups are for high availability. Option D is wrong because SQL Agent is not available in Azure SQL Database.

Answer

Elastic pools

Answer

Failover groups

Answer

SQL Server Agent

Question 5

You need to migrate an on-premises SQL Server database to Azure SQL Managed Instance with minimal downtime. Which tool should you use?

Accepted Answer

Azure Database Migration Service (DMS). Option A is correct because Azure DMS supports online migration with minimal downtime. Option B is wrong because BACPAC is for offline export/import. Option C is wrong because SSIS is for integration. Option D is wrong because Azure Data Factory is for data movement but not optimized for online migration.

Answer

Azure Data Factory

Answer

SQL Server Integration Services (SSIS)

Answer

BACPAC export and import

Question 6

Which TWO Azure services can be used to host a relational database that is compatible with SQL Server?

Accepted Answer

Azure SQL Database. Options A and D are correct. Azure SQL Database and Azure SQL Managed Instance both provide SQL Server compatibility. Option B is wrong because Azure Cosmos DB is NoSQL. Option C is wrong because Azure Database for MySQL is MySQL. Option E is wrong because Azure Database for PostgreSQL is PostgreSQL.

Answer

Azure Database for PostgreSQL

Answer

Azure Cosmos DB

Answer

Azure Database for MySQL

Question 7

Which THREE are benefits of using Azure SQL Database serverless compute tier?

Accepted Answer

Billing per second for compute usage. Options A, B, and E are correct. Serverless auto-scales, pauses during inactivity, and charges per second. Option C is wrong because serverless does not provide a fixed SLA for compute. Option D is wrong because serverless is not designed for high throughput workloads; it may have cold start delays.

Answer

Guaranteed high availability with 99.99% SLA

Answer

Ideal for high-throughput, latency-sensitive applications

Question 8

Which TWO are valid deployment options for Azure SQL?

Accepted Answer

Azure SQL Managed Instance. Options A and D are correct. Azure SQL Database and Azure SQL Managed Instance are deployment options. Options B and C are wrong because they are other Azure services. Option E is wrong because Azure Synapse Dedicated SQL Pool is a separate service for data warehousing.

Answer

Azure Cosmos DB

Answer

Azure Database for MariaDB

Answer

Azure Synapse Analytics Dedicated SQL Pool

Question 9

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database deployment. What is the maximum size (in GB) of the database?

Accepted Answer

250 GB. Option B is correct because 268435456000 bytes equals 250 GB (268435456000 / (1024^3) = 250). Option A is wrong because 268 GB would be 268435456000 bytes? Actually 268 GB is close but 250 GB is exact. Option C is wrong because 256 GB would be 274877906944 bytes. Option D is wrong because 500 GB would be 536870912000 bytes.

Answer

256 GB

Answer

500 GB

Answer

268 GB

Question 10

Refer to the exhibit. You create an external table in Azure SQL Database. Which data source is being used?

Accepted Answer

Azure Blob Storage. The exhibit shows an external table referencing a data source with the LOCATION set to 'https://mystorage.blob.core.windows.net/...', which is the endpoint for Azure Blob Storage. In Azure SQL Database, external tables are created over external data sources that point to Azure Blob Storage or Azure Data Lake Storage, but the URL format 'blob.core.windows.net' specifically indicates Azure Blob Storage. The CREATE EXTERNAL TABLE statement uses this data source to read data stored as files (e.g., CSV, Parquet) in the blob container.

Answer

Azure Files

Answer

Azure SQL Database

Answer

Azure Data Lake Storage Gen2

Question 11

Refer to the exhibit. This ARM template snippet is used to deploy which Azure resource?

Accepted Answer

Azure SQL Database server. Option A is correct because the properties include serverName, administratorLogin, etc., which are for an Azure SQL Database server. Option B is wrong because Azure SQL Managed Instance deployment uses different properties. Option C is wrong because Azure Database for MySQL has different properties. Option D is wrong because Azure Synapse Analytics workspace has different properties.

Answer

Azure Database for MySQL server

Answer

Azure SQL Managed Instance

Answer

Azure Synapse Analytics workspace

Question 12

A company stores customer orders in an Azure SQL Database. They need to ensure that the database can automatically scale to handle peak loads without manual intervention. Which Azure feature should they use?

Accepted Answer

Enable the serverless compute tier. Option B is correct because Azure SQL Database's serverless compute tier automatically scales compute resources based on workload demand, pausing during inactivity to save costs. Option A is wrong because elastic pools are for managing multiple databases with shared resources, not automatic scaling of a single database. Option C is wrong because read replicas are for read scalability, not compute scaling. Option D is wrong because reserved capacity is a pricing model, not a scaling feature.

Answer

Purchase reserved capacity

Answer

Add a read replica

Answer

Configure an elastic pool

Question 13

A retail company uses Azure SQL Database to store inventory data. They notice excessive blocking and deadlocks during peak hours. Which design change would best reduce these issues?

Accepted Answer

Add appropriate indexes to reduce lock duration. Adding appropriate indexes reduces the number of rows scanned during queries, which shortens lock duration and lowers the chance of blocking and deadlocks. In Azure SQL Database, indexes help queries become more efficient by using seeks instead of scans, minimizing the time locks are held on resources.

Answer

Implement read replicas for reporting queries

Answer

Use the READ UNCOMMITTED isolation level

Answer

Scale up to a higher service objective

Question 14

A startup is migrating its on-premises SQL Server database to Azure. They want the least administrative overhead for patching and backups. Which Azure data service should they choose?

Accepted Answer

Azure SQL Database. Option B is correct because Azure SQL Database is a fully managed platform-as-a-service (PaaS) that automates patching, backups, and high availability. Option A is wrong because SQL Server on Azure Virtual Machines (IaaS) requires manual patching and backup management. Option C is wrong because Azure Database for PostgreSQL is a different database engine, not SQL Server. Option D is wrong because Azure Cosmos DB is a NoSQL database, not relational.

Answer

Azure Database for PostgreSQL

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Cosmos DB

Question 15

A company uses Azure SQL Database for an e-commerce application. They need to ensure that if the primary region fails, the database can be failed over to a secondary region with minimal data loss. Which feature should they enable?

Accepted Answer

Active geo-replication. Option A is correct because active geo-replication creates readable secondary replicas in a different region and supports failover with a configurable recovery point objective (RPO) of up to 5 seconds. Option B is wrong because auto-failover groups build on active geo-replication and enable automatic failover, but the core feature for minimal data loss is geo-replication. Option C is wrong because backup storage redundancy (RA-GRS) is for backups, not live failover. Option D is wrong because point-in-time restore restores to a specific time, not for region failover.

Answer

Point-in-time restore

Answer

Auto-failover groups

Answer

Read-access geo-redundant storage (RA-GRS)