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

Question 1

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. Azure SQL Database is a fully managed platform-as-a-service (PaaS) that automates patching, backups, and high availability. Option A is wrong because Azure Database for PostgreSQL is a different database engine, not SQL Server. Option B is wrong because SQL Server on Azure Virtual Machines (IaaS) requires manual patching and backup management. 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 2

A financial services company runs a critical application on Azure SQL Managed Instance. They need to ensure that a recent transaction can be recovered within 15 minutes of a user error. Which feature should they configure?

Accepted Answer

Point-in-time restore (PITR). Point-in-time restore (PITR) is the correct feature because it allows you to restore an Azure SQL Managed Instance to a specific point in time within the retention period (default 7 days, configurable up to 35 days). This directly addresses the requirement to recover a recent transaction after a user error, such as an accidental data modification or deletion, within 15 minutes. PITR creates a new database from automated backups, enabling precise recovery to the moment just before the error occurred.

Answer

Geo-restore

Answer

Automatic failover groups

Answer

Long-term retention (LTR)

Question 3

A company needs to create a relational database in Azure that is compatible with existing SQL Server applications and provides built-in high availability without requiring configuration. Which service should they choose?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed Platform-as-a-Service (PaaS) relational database that is built on the latest stable version of the Microsoft SQL Server engine, ensuring compatibility with existing SQL Server applications. It provides built-in high availability with a 99.99% SLA through automatic failover groups and zone-redundant configurations, requiring no manual setup or configuration from the user.

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Database for MariaDB

Answer

Azure Cosmos DB

Question 4

Which THREE are valid reasons to choose Azure SQL Managed Instance over Azure SQL Database?

Accepted Answer

Need for SQL Server Agent and CLR integration. Azure SQL Managed Instance provides near 100% compatibility with on-premises SQL Server, including instance-level features like SQL Server Agent and CLR integration. These features are not available in Azure SQL Database, which is a Platform-as-a-Service offering that abstracts away the instance scope. Therefore, if your application requires SQL Server Agent for job scheduling or CLR for custom .NET code execution, Managed Instance is the correct choice.

Answer

Simpler high-availability configuration

Answer

Desire for automated backups

Question 5

A company uses the above ARM template snippet to deploy an Azure SQL Database. The deployment fails with an error about invalid SKU. What is the most likely cause?

Accepted Answer

The capacity value must be a multiple of 4. The deployment would not fail because of a capacity multiple requirement. 'GP_Gen5_2' is a valid vCore SKU for the GeneralPurpose tier with 2 vCores, and Azure SQL Database supports 2 vCores in the GeneralPurpose tier. Therefore none of the listed options is correct; the question is invalid as written.

Answer

The SKU name 'GP_Gen5_2' is not valid for the 'GeneralPurpose' tier

Answer

The 'tier' property should be 'Standard' instead of 'GeneralPurpose'

Answer

The location 'eastus' does not support GeneralPurpose tier

Question 6

A DBA runs the following KQL query in Azure Monitor for an Azure SQL Database: `AzureDiagnostics | where Category == 'QueryStoreRuntimeStatistics'`. The query returns no results. What is the most likely reason?

Accepted Answer

Query Store is not enabled on the database. That Query Store is not enabled on the database. Query Store must be enabled for each database to capture and store query runtime statistics. The KQL query likely targets the Query Store data, which requires Query Store to be active. Option B is incorrect because AzureDiagnostics can contain SQL data, but the issue is that the query is looking for Query Store data, not diagnostics. Option C is incorrect because the category name is correct in the query. Option D is incorrect because the KQL syntax is valid.

Answer

The AzureDiagnostics table does not contain SQL data

Answer

The category name is misspelled

Answer

The KQL syntax is incorrect

Question 7

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?

Accepted Answer

Elastic Database Jobs. Elastic Database Jobs in Azure SQL Database can replace SQL Server Agent jobs for scheduling T-SQL scripts across multiple databases. Option A is incorrect because Azure Automation is not designed for T-SQL job scheduling; it is used for automating Azure management tasks. Option B is incorrect because Azure Logic Apps is for workflow automation and integration, not for direct T-SQL job scheduling. Option D is incorrect because Azure Data Factory is primarily for data integration and orchestration, not for scheduling ad-hoc T-SQL jobs.

Answer

Azure Automation

Answer

Azure Logic Apps

Answer

Azure Data Factory

Question 8

A company uses Azure SQL Database and needs to run complex analytical queries that scan large amounts of data. The queries are experiencing performance issues. Which Azure service should they use to offload the analytical workload?

Accepted Answer

Azure Synapse Analytics dedicated SQL pool. Azure Synapse Analytics dedicated SQL pool is designed for large-scale analytical workloads, using a massively parallel processing (MPP) architecture that distributes data across 60 distributions and executes queries in parallel. This offloads complex analytical queries from Azure SQL Database, which uses a single-node SQL Server engine optimized for OLTP, not heavy scanning.

Answer

Azure SQL Database (Hyperscale tier)

Answer

Azure Analysis Services

Answer

Azure Data Lake Storage

Question 9

A small business needs a cost-effective relational database for a new web application. The workload is light and predictable. They want to minimize administrative overhead. Which Azure service should they choose?

Accepted Answer

Azure SQL Database serverless. Azure SQL Database serverless is the correct choice because it automatically pauses the database during periods of inactivity, charging only for storage and compute used. This aligns perfectly with the small business's need for a cost-effective, low-administration relational database for a light, predictable workload, as it eliminates the need to manage infrastructure or pay for idle compute.

Answer

Azure Database for PostgreSQL

Answer

SQL Server on Azure Virtual Machines

Answer

Azure SQL Database (provisioned DTU)

Question 10

A company uses Azure SQL Database and needs to implement data masking for sensitive columns like email addresses and credit card numbers, so that only authorized users can see the actual data. Which feature should they configure?

Accepted Answer

Dynamic Data Masking. Dynamic Data Masking (DDM) is the correct feature because it limits exposure of sensitive data by obfuscating columns (e.g., email addresses, credit card numbers) in query results to non-privileged users, while authorized users (with EXEMPT or UNMASK permission) see the actual data. This directly meets the requirement of masking sensitive columns without altering the underlying stored data.

Answer

Row-Level Security

Answer

Auditing

Answer

Transparent Data Encryption (TDE)

Question 11

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 enables near real-time replication of transactional data from Azure SQL Database to Synapse Analytics for analytical queries without ETL and without duplicating data. Option A is wrong because Azure Data Factory does not provide real-time replication and would require data duplication. Option C is wrong because using Azure SQL Database and SQL Data Warehouse separately with periodic data copy duplicates data. Option D is wrong because using Azure SQL Database for both workloads does not separate workloads.

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 12

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 restricting access to a specific VNet while denying public access. Option A is incorrect because disabling public network access alone does not tie to a specific VNet; it would block all access unless combined with Private Link. Option C is incorrect because IP firewall rules allow access from specific public IP addresses, not from a VNet. Option D is incorrect because Azure Private Link provides private connectivity but does not deny public access by itself; it requires additional configuration to block public endpoints.

Answer

Disable public network access

Answer

IP firewall rules

Answer

Azure Private Link

Question 13

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

Accepted Answer

Transparent Data Encryption (TDE). And Option C are correct. Transparent Data Encryption encrypts the database files at rest. Always Encrypted encrypts sensitive columns at rest and in use. Option B is wrong because Dynamic Data Masking does not encrypt data; it masks it in query results. Option D is wrong because Auditing is for tracking, not encryption. Option E is wrong because Row-Level Security controls access, not encryption.

Answer

Dynamic Data Masking

Answer

Auditing

Answer

Row-Level Security

Question 14

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 (Option A) is the correct choice because it enables automatic failover to a secondary region in case of a regional outage, meeting the high availability requirement. Option B (Azure SQL Managed Instance with local redundancy) only provides local redundancy within a single region, not cross-region failover. Option C (Azure Database for PostgreSQL with read replicas) does not provide automatic failover; read replicas are for read scaling, not failover. Option D (Azure SQL Database single database) by default does not have automatic regional failover; active geo-replication is needed for that.

Answer

Azure SQL Managed Instance with local redundancy

Answer

Azure Database for PostgreSQL with read replicas

Answer

Azure SQL Database (single database)

Question 15

You are designing a relational database for an IoT application that ingests high volumes of time-stamped sensor data. The queries frequently filter by device ID and time range. Which index strategy would optimize query performance?

Accepted Answer

Create a composite index on (DeviceID, Timestamp). A composite index on (DeviceID, Timestamp) directly supports the two most common filter predicates in the query workload: DeviceID (for equality) and Timestamp (for range scans). In SQL Server, a composite index with DeviceID as the leading column allows the query engine to perform an index seek on DeviceID and then a range scan on Timestamp, minimizing I/O and avoiding key lookups. This strategy is optimal for time-series IoT data where queries almost always specify a device and a time window.

Answer

Create a non-clustered index on Timestamp only

Answer

Create a clustered index on DeviceID only

Answer

Create a non-clustered index on SensorType