DP-900 Practice Test 29 — 15 Questions

Question 1

A company runs a sales analytics workload on Azure Synapse Analytics. They notice that queries against the fact table are slow. The fact table is hash-distributed on the SalesDate column. Which design change would most likely improve query performance?

Accepted Answer

Replicate the fact table to all nodes. Option D is correct because hash-distributing on a high-cardinality date column can cause data skew and slow queries. Replicating the fact table avoids shuffling and improves performance for smaller tables. Option A is wrong because round-robin distribution is for staging tables, not fact tables. Option B is wrong because dropping indexes makes queries slower. Option C is wrong because the distribution key should be a frequently joined column, not necessarily the primary key.

Answer

Change distribution key to the primary key column

Answer

Change distribution to round-robin

Answer

Drop all indexes on the fact table

Question 2

Which TWO options are valid ways to secure data at rest in Azure Synapse Analytics?

Accepted Answer

Customer-managed keys (CMK) with TDE. Customer-managed keys (CMK) with TDE is a valid method to secure data at rest in Azure Synapse Analytics because it allows you to bring your own key (BYOK) to encrypt the database encryption key (DEK), which is stored in Azure Key Vault. This provides an additional layer of control and separation of duties, ensuring that only authorized users can access the encryption keys and thus the underlying data.

Answer

Always Encrypted

Answer

Column-level security

Answer

Dynamic Data Masking

Question 3

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 4

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. Option B is correct because 268435456000 bytes equals 250 GB (268435456000 / (1024^3) = 250). Option A is wrong because 268 GB would be 268435456000 bytes? Actually 268 GB is close but 250 GB is exact. Option C is wrong because 256 GB would be 274877906944 bytes. Option D is wrong because 500 GB would be 536870912000 bytes.

Answer

256 GB

Answer

500 GB

Answer

268 GB

Question 5

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. Options A and B are correct. TLS ensures encryption between client and server. Azure SQL Database always enforces encryption (TLS) for connections. Option C is wrong because transparent data encryption (TDE) encrypts data at rest, not in transit. Option D is wrong because always encrypted is for column-level encryption at rest and in transit. Option E is wrong because firewall rules control access, not encryption.

Answer

Enable Transparent Data Encryption (TDE)

Answer

Configure firewall rules to allow only specific IPs

Answer

Use Always Encrypted

Question 6

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. Option B is correct because Dynamic Data Masking obfuscates sensitive data in query results for unauthorized users. Option A is wrong because Transparent Data Encryption encrypts data at rest, not in query results. Option C is wrong because Row-Level Security restricts row access but does not mask columns. Option D is wrong because Azure SQL Database Auditing tracks database events, not masking.

Answer

Row-Level Security

Answer

Auditing

Answer

Transparent Data Encryption (TDE)

Question 7

You are designing a relational database for a multi-tenant SaaS application. Each tenant's data must be isolated for security and compliance. Which design approach best ensures data isolation while keeping cost manageable?

Accepted Answer

Use a separate database per tenant. Option A is correct because a separate database per tenant provides strong isolation. Option B is wrong because shared tables with a TenantID column require careful row-level security. Option C is wrong because a separate schema per database is not a standard Azure feature. Option D is wrong because Azure Synapse Analytics is for data warehousing, not OLTP.

Answer

Use Azure Synapse Analytics with workload isolation

Answer

Use a single database with a TenantID column and row-level security

Answer

Use a single database with separate schemas per tenant

Question 8

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. Option B is correct because Azure SQL Database's serverless compute tier automatically scales compute resources based on workload demand, pausing during inactivity to save costs. Option A is wrong because elastic pools are for managing multiple databases with shared resources, not automatic scaling of a single database. Option C is wrong because read replicas are for read scalability, not compute scaling. Option D is wrong because reserved capacity is a pricing model, not a scaling feature.

Answer

Purchase reserved capacity

Answer

Add a read replica

Answer

Configure an elastic pool

Question 9

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. Options A, B, and D are correct. Managed Instance offers near 100% compatibility with SQL Server, supports SQL Server Agent, and provides a virtual network for secure integration. Option C is wrong because both services support automated backups. Option E is wrong because both can be configured for high availability; Managed Instance does not necessarily have a simpler HA setup.

Answer

Simpler high-availability configuration

Answer

Desire for automated backups

Question 10

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. Option A is correct because Azure SQL Database serverless is designed for intermittent, unpredictable workloads and automatically pauses when idle, reducing cost. Option B is wrong because Azure SQL Database provisioned is for steady-state workloads. Option C is wrong because SQL Server on Azure VMs is IaaS and requires management. Option D is wrong because Azure Database for PostgreSQL is fully managed but PostgreSQL-specific; Azure SQL Database serverless is more cost-effective for light relational workloads.

Answer

Azure Database for PostgreSQL

Answer

SQL Server on Azure Virtual Machines

Answer

Azure SQL Database (provisioned DTU)

Question 11

You need to migrate an on-premises SQL Server database to Azure. The database uses many stored procedures and CLR assemblies. Which Azure service is most compatible without requiring major application changes?

Accepted Answer

Azure SQL Managed Instance. Option B is correct because Azure SQL Managed Instance provides near 100% compatibility with SQL Server. Option A is wrong because Azure SQL Database does not support CLR assemblies. Option C is wrong because Azure Database for MySQL is a different database engine. Option D is wrong because Azure Virtual Machines with SQL Server requires patching and management overhead.

Answer

Azure Database for MySQL

Answer

Azure Virtual Machines with SQL Server

Answer

Azure SQL Database

Question 12

An e-commerce application uses Azure SQL Database and stores user session data in a table called Sessions. The table contains millions of rows and queries often filter by UserID and LastActivityTime. The development team wants to improve query performance for these filters. What should they implement?

Accepted Answer

Create a nonclustered index on UserID and LastActivityTime. Option C is correct because an index on (UserID, LastActivityTime) speeds up filters on both columns. Option A is wrong because a clustered index organizes data physically but does not specifically optimize these filters. Option B is wrong because partitioning can help manage large tables but not directly optimize filter queries. Option D is wrong because views are virtual and do not inherently improve performance without indexes.

Answer

Create a clustered index on the SessionID column

Answer

Create a view that filters the data

Answer

Partition the table by month

Question 13

A financial services company runs a critical application on Azure SQL Managed Instance. They need to ensure that in the event of a regional outage, the database can be failed over to a secondary region with minimal data loss and automatic failover. The secondary region should not be used for read traffic during normal operations. Which configuration meets these requirements?

Accepted Answer

Configure a failover group with automatic failover policy and set the secondary to non-readable. Option A is correct because failover groups with automatic failover and readable secondary set to 'No' provide disaster recovery with minimal data loss and no read traffic on secondary. Option B is wrong because active geo-replication allows read-only access on secondary. Option C is wrong because automatic failover groups require readable secondary to be enabled for automatic failover. Option D is wrong because backup restore does not provide automatic failover and has higher data loss.

Answer

Create an automatic failover group with readable secondary enabled

Answer

Use Azure SQL Database backup to blob storage and restore to another region

Answer

Implement active geo-replication and manually initiate failover

Question 14

Which TWO of the following statements about Azure SQL Database elastic pools are true?

Accepted Answer

They allow multiple databases to share a fixed set of resources. Options A and C are correct. Elastic pools allow sharing of resources among multiple databases and are cost-effective for databases with variable usage patterns. Option B is wrong because elastic pools can be scaled up or down. Option D is wrong because elastic pools are not limited to a single server. Option E is wrong because elastic pools do not store data; databases within pools do.

Answer

They store actual database data

Answer

They cannot be scaled after creation

Answer

They are limited to a single Azure SQL Database server

Question 15

A startup is building a web application with a relational database backend. They expect variable traffic and want to minimize costs by scaling the database automatically based on demand. Which Azure service should they use?

Accepted Answer

Azure SQL Database serverless. Option C is correct because Azure SQL Database serverless automatically scales compute resources and pauses during inactivity to save costs. Option A (Azure SQL Managed Instance) is not serverless. Option B (SQL Server on Azure VM) requires manual scaling. Option D (Azure Database for MariaDB) does not have serverless compute.

Answer

Azure SQL Managed Instance

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Database for MariaDB