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

Question 1

A company uses Azure SQL Database with the Business Critical service tier. They notice increased latency during peak hours. They need to improve performance without changing the application code. Which action should they take?

Accepted Answer

Increase the number of vCores. Option B is correct because increasing the number of vCores (scale up) provides more resources to handle peak load without code changes. Option A is wrong because adding read replicas helps read scalability but not write latency. Option C is wrong because changing to General Purpose might reduce performance. Option D is wrong because enabling auto-pause is for serverless, not for performance.

Answer

Add a read replica

Answer

Enable auto-pause

Answer

Change the service tier to General Purpose

Question 2

A startup wants to build a new web application with a relational database. They expect variable traffic and want to minimize costs by paying only for the compute resources they use. Which Azure SQL Database deployment option should they choose?

Accepted Answer

Serverless compute tier. Option C is correct because Azure SQL Database serverless automatically pauses during inactivity and bills per second for compute. Option A is wrong because provisioned computing charges for allocated resources regardless of usage. Option B is wrong because elastic pools are for multiple databases with pooled resources, not serverless. Option D is wrong because SQL on VMs is IaaS, costs for VMs always on.

Answer

Provisioned compute tier

Answer

Elastic pool

Answer

SQL Server on Azure Virtual Machines

Question 3

A company has an Azure SQL Database that stores sensitive financial data. They need to ensure that database administrators (DBAs) cannot view the actual data but can still perform administrative tasks. Which feature should they implement?

Accepted Answer

Dynamic data masking. Option B is correct because dynamic data masking hides sensitive data from non-privileged users, including DBAs if they are not exempted. Option A is wrong because TDE encrypts at rest but does not hide data from DBAs. Option C is wrong because Azure RBAC controls access but doesn't mask data. Option D is wrong because Azure SQL Database auditing logs actions but doesn't prevent viewing.

Answer

Azure role-based access control (RBAC)

Answer

Transparent data encryption (TDE)

Answer

Azure SQL Database auditing

Question 4

A company uses Azure SQL Database with geo-replication for disaster recovery. During a regional outage, they manually failover to the secondary region. After the primary region is restored, they need to re-establish geo-replication with minimal downtime. What should they do?

Accepted Answer

Initiate a planned failover to switch back to the original region. After a manual failover to the secondary region, the original primary becomes a secondary database. To re-establish geo-replication with minimal downtime, you should initiate a planned failover (also called a graceful failover) to switch back to the original region. This operation reverses the roles without data loss and avoids the need to reseed the database, keeping downtime to seconds.

Answer

Drop the secondary database and create a new one

Answer

Delete the geo-replication link and create a new one

Answer

Manually swap the roles of the primary and secondary

Question 5

A company needs to migrate a large on-premises SQL Server database to Azure. The migration must have minimal downtime and support ongoing replication. Which Azure service should they use?

Accepted Answer

Azure Database Migration Service. Azure Database Migration Service (DMS) is designed for online migrations with minimal downtime, supporting ongoing replication from SQL Server to Azure SQL Database. It uses the Data Migration Assistant (DMA) for assessment and the Azure DMS for continuous sync, enabling near-zero downtime during cutover.

Answer

Azure Data Box

Answer

Azure Data Factory

Answer

Azure SQL Database

Question 6

Which TWO features are supported by Azure SQL Database to provide high availability?

Accepted Answer

Active geo-replication. Option A and D are correct. Geo-replication and auto-failover groups provide high availability across regions. Option B is wrong because Always On availability groups is an on-premises feature, not directly in Azure SQL Database. Option C is wrong because log shipping is not directly supported. Option E is wrong because point-in-time restore is for backup, not HA.

Answer

Always On availability groups

Answer

Point-in-time restore

Answer

Log shipping

Question 7

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

Accepted Answer

Need for SQL Server Agent. Option A is correct because 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, while Azure SQL Database (single database) has a maximum size of 4 TB for the Hyperscale service tier, making Managed Instance necessary for databases exceeding 4 TB in non-Hyperscale tiers. 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 8

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

Accepted Answer

Transparent data encryption (TDE). Transparent Data Encryption (TDE) is a feature in Azure SQL Database that encrypts data at rest, including backups and transaction log files, using an AES-256 encryption algorithm. It performs real-time I/O encryption and decryption of the data without requiring changes to the application, ensuring that the physical storage media is protected against unauthorized access. Always Encrypted also protects data at rest by encrypting sensitive columns within the database, but it additionally protects data in transit and during query processing by keeping the encryption keys on the client side.

Answer

Firewall rules

Answer

Dynamic data masking

Answer

Azure AD authentication

Question 9

Refer to the exhibit. An Azure Policy is defined as shown. A database administrator attempts to create an Azure SQL Database without enabling zone redundancy. What will happen?

Accepted Answer

The database creation will be denied. Option C is correct because the policy denies creation if zone redundancy is not enabled. Option A is wrong because the policy will deny, not allow. Option B is wrong because the effect is deny, not audit. Option D is wrong because the policy applies at creation time, not later.

Answer

The database will be created and an audit event will be logged

Answer

The database will be created and zone redundancy will be automatically enabled

Answer

The database will be created with zone redundancy disabled

Question 10

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. Option B is correct because the failover policy is Automatic with a grace period of 60 minutes. After the grace period, automatic failover will occur, making server2 (eastus) the primary. Option A is wrong because automatic failover is enabled. Option C is wrong because the grace period allows 60 minutes before data loss failover. Option D is wrong because the read-only endpoint is disabled, but that does not affect failover.

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 11

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. Option D is correct because the query counts logout events per user per hour. Option A is wrong because it counts logouts, not failed logins. Option B is wrong because it counts events, not active logins. Option C is wrong because it's not about total connections but logout events.

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 12

A company is migrating a large on-premises SQL Server database to Azure. They require high availability with automatic failover and read-scale for reporting workloads. Which Azure service should they use?

Accepted Answer

Azure SQL Database Business Critical tier with failover groups. Option B is correct because Azure SQL Database with Business Critical tier and failover group provides automatic failover and read-scale. Option A is wrong because Hyperscale is for very large databases but not primarily for high availability with read-scale. Option C is wrong because SQL Server on Azure VMs requires manual configuration for high availability. Option D is wrong because Azure Database for PostgreSQL is not a SQL Server migration target.

Answer

Azure SQL Database Hyperscale tier

Answer

Azure Database for PostgreSQL with geo-redundant backup

Answer

SQL Server on Azure Virtual Machines with Always On availability groups

Question 13

A data warehouse team in Azure Synapse Analytics notices query performance degradation on a large fact table. The table is partitioned by date and has a clustered columnstore index. Which action is most likely to improve performance?

Accepted Answer

Reorganize the clustered columnstore index. Reorganizing the clustered columnstore index (option C) is the most likely action to improve performance because, over time, columnstore indexes can become fragmented due to data modifications (inserts, updates, deletes). Reorganizing the index physically recompresses the data into optimal rowgroups, removing deleted rows and merging small rowgroups, which directly improves query scan efficiency and reduces I/O.

Answer

Update statistics on the fact table

Answer

Drop and recreate the partition boundaries

Answer

Change the distribution to ROUND_ROBIN

Question 14

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

Elastic Query. Elastic Query (Option D) is the correct feature because it enables running complex T-SQL queries that span multiple databases in Azure SQL Database and Azure SQL Managed Instance, allowing you to join tables across these services in a single query. It uses a logical database as a shard map manager to distribute queries across remote databases, supporting cross-instance and cross-database queries without data movement.

Answer

Database Mail

Answer

PolyBase

Answer

Linked server

Question 15

A company uses Azure SQL Database and wants to ensure that a specific query always uses a particular index. What should they do?

Accepted Answer

Use a query hint to force the index. Option B is correct because query hints, specifically the INDEX hint, allow you to force the query optimizer to use a particular index for a specific query. In Azure SQL Database, this is done by adding `OPTION (TABLE HINT (table_name, INDEX (index_name)))` to the query, overriding the optimizer's default index selection.

Answer

Update statistics on the table

Answer

Rebuild the index

Answer

Enable Query Store