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

Question 1

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?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Dynamic data masking. Dynamic data masking hides sensitive data from non-privileged users, including DBAs if they are not exempted. Option A is wrong because Azure RBAC controls access but doesn't mask data. Option C is wrong because TDE encrypts at rest but does not hide data from DBAs. 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 2

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 3

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 4

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

Accepted Answer

Active geo-replication. Active geo-replication (C) is correct because Azure SQL Database supports it as a built-in feature that continuously replicates a database to up to four readable secondary databases in different Azure regions, enabling fast manual failover to maintain availability during a regional outage. Auto-failover groups (D) is correct because it builds on active geo-replication to provide automatic failover of a group of databases to a secondary region, along with read-write and read-only listener endpoints, ensuring high availability without manual intervention. Always On availability groups (A) is not a native Azure SQL Database feature; it applies to SQL Server on Azure VMs and Azure SQL Managed Instance, not the PaaS single-database/multi-database model. Point-in-time restore (B) is a backup-based data recovery feature for restoring a database to an earlier state, not a high-availability mechanism. Log shipping (E) is a SQL Server disaster-recovery technique that is not offered as a built-in high-availability feature in Azure SQL Database.

Answer

Always On availability groups

Answer

Point-in-time restore

Answer

Log shipping

Question 5

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 6

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. The policy has an effect of 'deny', so any attempt to create an Azure SQL Database without zone redundancy will be denied. Option B is correct because the policy explicitly denies the creation. Option A is incorrect because the effect is deny, not audit. Option C is incorrect because the policy denies creation rather than enabling zone redundancy automatically. Option D is incorrect because the policy denies the creation entirely, it does not allow creation with zone redundancy disabled.

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 7

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 8

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. 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

Question 9

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. Transparent Data Encryption (TDE) encrypts the database, backups, and logs at rest at the storage layer, protecting against physical media theft. Always Encrypted goes further by encrypting specific columns client-side so that even DBAs and the SQL engine cannot see plaintext — only authorized client applications holding the column encryption keys can decrypt. Together they satisfy both 'encrypt at rest' and 'only authorized applications can decrypt'.

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 10

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 11

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 12

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. Elastic Jobs is the Azure SQL Database feature designed to run T-SQL scripts or maintenance tasks across many databases — including all databases in a pool or all databases on a server — from a single job definition. For a multi-tenant SaaS model where each tenant has its own database, Elastic Jobs lets you schedule and execute maintenance (index rebuilds, statistics updates, schema changes) across the entire fleet without writing custom orchestration.

Answer

Elastic pools

Answer

Failover groups

Answer

SQL Server Agent

Question 13

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). Azure Database Migration Service (DMS) is designed for minimal-downtime migrations to Azure SQL Managed Instance. It supports online migration mode, which continuously replicates changes from the source SQL Server to the target while the source remains operational, allowing a cutover with minimal downtime. DMS handles schema and data migration and provides monitoring and validation.

Answer

Azure Data Factory

Answer

SQL Server Integration Services (SSIS)

Answer

BACPAC export and import

Question 14

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

Accepted Answer

Azure SQL Database. Azure SQL Database (A) is correct because it is a fully managed PaaS relational database engine built on the same SQL Server engine, so it is directly compatible with SQL Server T-SQL, tooling, and drivers. Azure SQL Managed Instance (D) is also correct because it runs the SQL Server database engine with near-100% compatibility, including features like SQL Agent, cross-database queries, and CLR, making it a relational SQL Server-compatible service. Azure Database for PostgreSQL (B) is not correct because it is a PostgreSQL-compatible relational service, not SQL Server-compatible. Azure Cosmos DB (C) is not correct because it is a multi-model NoSQL database, not a relational SQL Server-compatible engine. Azure Database for MySQL (E) is not correct because it is a MySQL-compatible relational service, not SQL Server-compatible.

Answer

Azure Database for PostgreSQL

Answer

Azure Cosmos DB

Answer

Azure Database for MySQL

Question 15

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

Accepted Answer

Billing per second for compute usage. Option B is correct because the serverless compute tier bills per second of compute usage, so you only pay for the vCore-seconds actually consumed rather than a fixed provisioned capacity. Option C is correct because serverless automatically scales compute (vCores) up and down based on the workload's demand, within the configured min/max vCore range. Option D is correct because serverless supports auto-pause, where the database is paused during inactivity and resumes on the next connection, eliminating compute charges while paused. Option A is not a serverless-specific benefit, since the 99.99% availability SLA applies to Azure SQL Database generally, not uniquely to serverless. Option E is not correct because serverless is designed for intermittent, unpredictable, or low-to-moderate workloads, whereas high-throughput, latency-sensitive applications are better suited to the provisioned or hyperscale tiers due to cold-start delays after auto-pause.

Answer

Guaranteed high availability with 99.99% SLA

Answer

Ideal for high-throughput, latency-sensitive applications