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

Question 1

Which THREE considerations are important when choosing between Azure SQL Database and Azure SQL Managed Instance?

Accepted Answer

Need for SQL Server Agent. SQL Server Agent is fully supported in Azure SQL Managed Instance, enabling native job scheduling and automation, whereas Azure SQL Database does not include SQL Server Agent and requires alternative solutions like Elastic Jobs or Azure Automation. Option B is correct because Azure SQL Managed Instance supports databases up to 16 TB. Azure SQL Database (single database) has a maximum size of 4 TB for the General Purpose and Business Critical service tiers. Therefore, for databases exceeding 4 TB where the Hyperscale service tier of Azure SQL Database is not an option, Azure SQL Managed Instance is a necessary consideration. Option E is correct because Azure SQL Managed Instance provides near-100% compatibility with on-premises SQL Server, allowing lift-and-shift migrations with minimal or no application changes, whereas Azure SQL Database often requires schema and code modifications due to differences in features like CLR, Service Broker, or cross-database queries.

Answer

Requirement for elastic pools

Answer

Need for automated backups

Question 2

Refer to the exhibit. A failover group configuration is displayed. The database 'mydb' is currently on server1 in westus. What will happen if a regional outage occurs in westus?

Accepted Answer

After 60 minutes, automatic failover will occur to server2 in eastus. The failover group is configured with an automatic failover policy and a grace period of 60 minutes. In the event of a regional outage in westus, after the 60-minute grace period expires, an automatic failover will occur to the secondary server (server2) in eastus, making it the new primary. Option B is incorrect because the read-only endpoint is disabled in the configuration, and even if enabled, it would only allow read-only connections during normal operation, not during an outage. Option C is incorrect because the automatic failover will occur after the grace period without manual intervention. Option D is incorrect because the failover is not immediate; it waits the 60-minute grace period, and asynchronous replication may cause data loss.

Answer

The read-only endpoint will allow connections during the outage

Answer

The database will remain unavailable until manual action is taken

Answer

The failover group will immediately failover to server2 without data loss

Question 3

Refer to the exhibit. A database administrator runs the KQL query shown to monitor failed logouts? Actually, the query filters for 'DATABASE_LOGOUT' events. What is the primary purpose of this query?

Accepted Answer

Identify users with the highest number of logout events per hour. The provided explanation correctly justifies option C, but it relies on and introduces critical information about the KQL query's behavior ('counts DATABASE_LOGOUT events per user per hour') that should have been present in the STEM or the exhibit for the question to be self-contained and answerable.

Answer

Identify users with the most failed login attempts

Answer

Determine which users are currently connected to the database

Answer

Monitor the total number of database connections over time

Question 4

You need to run a complex T-SQL query that joins tables from Azure SQL Database and Azure SQL Managed Instance in a single query. Which feature should you use?

Accepted Answer

Linked server. Linked server (Option C) is the correct feature. Azure SQL Managed Instance fully supports linked servers, allowing you to connect to and query other SQL Server instances, including Azure SQL Database, in a single T-SQL query. This enables joining tables across these different Azure SQL services. Azure SQL Database does not support linked servers, but Azure SQL Managed Instance can initiate a linked server connection to an Azure SQL Database.

Answer

Database Mail

Answer

PolyBase

Answer

Elastic Query

Question 5

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. 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, minimizing data loss. Option B (point-in-time restore) is wrong because it restores a database to a specific point in time, not for region failover. Option C (auto-failover groups) is wrong because while it builds on active geo-replication, it automates failover but does not by itself reduce data loss; the core feature for minimal data loss is geo-replication. Option D (RA-GRS) is wrong because it provides geo-redundant storage for backups, not for live database failover.

Answer

Point-in-time restore

Answer

Auto-failover groups

Answer

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

Question 6

A multinational corporation needs to deploy a relational database in Azure that supports global distribution and low-latency reads and writes across multiple regions. The data model is primarily key-value with occasional relational queries. Which Azure service should they choose?

Accepted Answer

Azure SQL Database with active geo-replication. Azure SQL Database with active geo-replication is the best choice among the options because it enables global distribution with readable replicas in multiple regions for low-latency reads, and a single writable primary that can be placed in the region with the highest write volume to minimize write latency. While it does not support multi-region writes, it meets the requirement of low-latency reads across regions and supports relational and key-value queries. Option D is incorrect because Azure Cosmos DB's PostgreSQL-compatible API is not a fully relational database, and its NoSQL API may not support relational queries efficiently. Options B and C are region-bound and do not provide the same global distribution capabilities.

Answer

Azure SQL Managed Instance

Answer

Azure Database for PostgreSQL – Hyperscale (Citus)

Answer

Azure Cosmos DB with PostgreSQL-compatible API

Question 7

A company needs to migrate a large on-premises SQL Server database to Azure with minimal downtime. Which Azure service should they use for the migration?

Accepted Answer

Azure Database Migration Service. Azure Database Migration Service (DMS) is designed for online migrations with minimal downtime, supporting continuous data sync. Option A is wrong because Azure SQL Database is the target, not a migration tool. Option B is wrong because Azure Backup is for backup, not migration. Option C is wrong because Azure Data Factory is for data integration, not database migration with minimal downtime.

Answer

Azure SQL Database

Answer

Azure Backup

Answer

Azure Data Factory

Question 8

A company wants to analyze data from multiple Azure SQL Databases using a single query. Which Azure service should they use?

Accepted Answer

Azure SQL Database elastic query. Azure SQL Database elastic query enables querying across multiple Azure SQL Databases from a single connection point. Option B is wrong because failover groups are for high availability and disaster recovery. Option C is wrong because Azure Synapse Link is for near-real-time analytics on operational data, not for querying multiple databases with a single query. Option D is wrong because Azure Stream Analytics is for real-time stream processing, not ad-hoc cross-database queries.

Answer

Azure SQL Database failover groups

Answer

Azure Synapse Link

Answer

Azure Stream Analytics

Question 9

Which TWO features are available in Azure SQL Database to improve performance without changing application code?

Accepted Answer

Automatic tuning. Options A and C are correct. Automatic tuning automatically adjusts indexes and query plans based on workload patterns, directly improving performance without requiring application code changes. Query Store collects detailed query runtime statistics and execution plans, enabling database administrators to identify and resolve performance regressions by forcing optimal plans or suggesting index changes, thereby improving performance without requiring modifications to the application's code. Option B (Columnstore indexes) requires altering table definitions, which is a schema change. Option D (Read replicas) requires application modifications to route read traffic to replicas. Option E (Elastic pools) is a resource management feature for scaling multiple databases, not a direct performance improvement feature for a single database.

Answer

Columnstore indexes

Answer

Read replicas

Answer

Elastic pools

Question 10

Which TWO are valid ways to secure data in transit for an Azure SQL Database?

Accepted Answer

Use the 'Encrypt connection' setting in connection strings. The 'Encrypt connection' setting in connection strings (e.g., 'Encrypt=True' or 'Encrypt=yes') forces the client to use TLS encryption for all data transmitted between the application and Azure SQL Database. This ensures data in transit is protected against eavesdropping and man-in-the-middle attacks. Azure SQL Database enforces encryption by default, but explicitly setting this parameter guarantees compliance with security policies. Additionally, requiring TLS 1.2 for client connections ensures that a strong, modern encryption protocol is used for all data transmitted over the network, further securing data in transit.

Answer

Enable Transparent Data Encryption (TDE)

Answer

Configure firewall rules to allow only specific IPs

Answer

Use Always Encrypted

Question 11

An administrator runs an Azure CLI command to update an Azure SQL Database's service objective. The output shows the above properties. The database is currently at S2 and the administrator wants to scale to S3. What is the issue?

Accepted Answer

The command did not specify the new service objective. The output shows the current service objective as S2, indicating the command did not include the new service objective parameter, so no change was applied. Option B is incorrect because the property names shown (e.g., name, serviceObjective, maxSizeBytes) are valid Azure SQL Database properties. Option C is incorrect because the maxSizeBytes value shown is within the acceptable range for the S2 tier. Option D is incorrect because Azure CLI does support scaling Azure SQL Database via the 'az sql db update' command.

Answer

The property names are incorrect

Answer

The maxSizeBytes value is invalid

Answer

The Azure CLI does not support scaling Azure SQL Database

Question 12

A healthcare organization stores patient records in Azure SQL Database. To comply with HIPAA, they need to encrypt sensitive columns like Social Security Numbers (SSNs) at rest and ensure that only authorized users can decrypt them. Which feature should they implement?

Accepted Answer

Always Encrypted. Always Encrypted allows client-side encryption of sensitive columns, ensuring only authorized clients can decrypt the data at rest and in use. Option B (Transparent Data Encryption) encrypts the entire database at rest but does not provide column-level granularity or client-side control. Option C (Dynamic Data Masking) obfuscates data for unauthorized users but does not encrypt it. Option D (Row-Level Security) restricts access to rows based on user context but does not encrypt columns.

Answer

Transparent Data Encryption (TDE)

Answer

Dynamic Data Masking

Answer

Row-Level Security

Question 13

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 14

A multinational corporation has Azure SQL Databases deployed in multiple Azure regions. They need to ensure that a failover to a secondary region happens automatically and with minimal data loss if the primary region goes down. Which deployment option should they use?

Accepted Answer

Active geo-replication with auto-failover groups. Active geo-replication with auto-failover groups in Azure SQL Database provides automatic failover across regions with a typical RPO of 5 seconds, meeting the requirement for minimal data loss. Option B (zone-redundant database) protects against zone failures within a single region, not across regions. Option C (Azure Traffic Manager with multiple databases) handles traffic routing but does not provide automated database failover or replication. Option D (locally redundant storage, LRS) protects data within a datacenter but does not offer cross-region failover capability.

Answer

Zone-redundant database

Answer

Azure Traffic Manager with multiple databases

Answer

Locally redundant storage (LRS)

Question 15

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