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

Question 1

Which TWO are valid deployment options for Azure SQL?

Accepted Answer

Azure SQL Managed Instance. Options A and B are correct. Azure SQL Database and Azure SQL Managed Instance are the two main deployment options for Azure SQL. Options C, D, and E are incorrect: Azure Cosmos DB is a NoSQL database, Azure Database for MariaDB is a separate managed database service, and Azure Synapse Analytics Dedicated SQL Pool is a data warehousing service.

Answer

Azure Cosmos DB

Answer

Azure Database for MariaDB

Answer

Azure Synapse Analytics Dedicated SQL Pool

Question 2

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. The ARM template specifies the 'maxSizeBytes' property as 268,435,456,000 bytes. Converting this to gigabytes (divide by 1024^3) yields exactly 250 GB. This is the maximum size of the database as defined in the template.

Answer

256 GB

Answer

500 GB

Answer

268 GB

Question 3

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 4

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

Accepted Answer

Azure SQL Database server. The ARM template snippet contains the resource type 'Microsoft.Sql/servers', which is the Azure Resource Manager type for an Azure SQL Database logical server. This server acts as a central administrative point for one or more Azure SQL databases. The presence of properties like 'administratorLogin' and 'administratorLoginPassword' further confirms it's a SQL server, not a managed instance or MySQL server.

Answer

Azure Database for MySQL server

Answer

Azure SQL Managed Instance

Answer

Azure Synapse Analytics workspace

Question 5

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. The serverless compute tier for Azure SQL Database automatically pauses and resumes the database based on compute usage, scaling compute resources on demand without manual intervention. This makes it ideal for handling unpredictable peak loads while minimizing costs during idle periods.

Answer

Purchase reserved capacity

Answer

Add a read replica

Answer

Configure an elastic pool

Question 6

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 7

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 PaaS database service that handles patching, backups, high availability, and monitoring automatically, minimizing administrative overhead. It is the natural migration target for a SQL Server database when the goal is least administrative effort.

Answer

Azure Database for PostgreSQL

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Cosmos DB

Question 8

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 (D) is correct because it is purpose-built to migrate on-premises SQL Server databases to Azure with minimal downtime, using online migration mode that continuously replicates changes until cutover. Azure SQL Database (A) is a target platform, not a migration tool, so it does not perform the migration itself. Azure Backup (B) only backs up and restores data and does not support live database migration with minimal downtime. Azure Data Factory (C) is an orchestration and data-integration service suited to data movement and ETL, not seamless SQL Server database migration with minimal downtime.

Answer

Azure SQL Database

Answer

Azure Backup

Answer

Azure Data Factory

Question 9

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 is correct because it enables cross-database queries across multiple Azure SQL Databases from a single T-SQL query, using an external data source and external table to reference the other databases. This directly matches the requirement to analyze data from multiple Azure SQL Databases with one query. Failover groups are for high availability and geo-replication, not for querying across databases. Azure Synapse Link provides near-real-time analytics replication from operational stores into Synapse, and Azure Stream Analytics is for real-time stream processing, so neither fits this cross-database query scenario.

Answer

Azure SQL Database failover groups

Answer

Azure Synapse Link

Answer

Azure Stream Analytics

Question 10

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 11

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 12

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 13

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. Option A is correct because the 'Encrypt connection' setting in a connection string (e.g., Encrypt=True;TrustServerCertificate=False) forces the client driver to negotiate an encrypted channel to Azure SQL Database, protecting data in transit. Option B is correct because requiring TLS 1.2 for client connections ensures that only modern, secure transport encryption is used between the client and the database, preventing downgrade to weaker protocols. Option C is not correct because Transparent Data Encryption protects data at rest by encrypting database files, not data moving over the network. Option D is not correct because firewall rules restrict which IP addresses can reach the server but do not encrypt the traffic itself. Option E is not correct because Always Encrypted protects sensitive columns at rest and in memory on the client side, not the transport channel.

Answer

Enable Transparent Data Encryption (TDE)

Answer

Configure firewall rules to allow only specific IPs

Answer

Use Always Encrypted

Question 14

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. Query Store runtime statistics are only emitted to Azure Monitor's AzureDiagnostics table when Query Store is actually enabled on the database. If Query Store is off, no QueryStoreRuntimeStatistics records are generated, so the query legitimately returns zero rows even though the KQL syntax and category name are correct.

Answer

The AzureDiagnostics table does not contain SQL data

Answer

The category name is misspelled

Answer

The KQL syntax is incorrect

Question 15

An administrator runs an Azure CLI command to update an Azure SQL Database's service objective. The command completes without errors, but the database remains at S2. The administrator wants to scale to S3. What is the issue?

Accepted Answer

The command did not specify the new service objective. The command did not specify the new service objective. To scale an Azure SQL Database with the Azure CLI, you must run `az sql db update` and explicitly pass the target tier/objective, for example `--service-objective S3` (or `--edition Standard --service-objective S3`); without that parameter, the command does not apply any change, so the database remains at S2. The other options do not fit: Azure CLI fully supports scaling Azure SQL Database via `az sql db update`, and there is no indication that the property names or the `maxSizeBytes` value are invalid for this scenario.

Answer

The property names are incorrect

Answer

The maxSizeBytes value is invalid

Answer

The Azure CLI does not support scaling Azure SQL Database