DP-300 Practice Test 4 — 25 Questions

Question 1

A company plans to deploy a new application on Azure VMs that requires a highly available SQL Server database with automatic failover and readable secondaries. The database size is 1 TB. Which deployment option meets these requirements with the least administrative overhead?

Accepted Answer

Azure SQL Managed Instance in the Business Critical tier.. Azure SQL Managed Instance in the Business Critical tier provides built-in high availability with automatic failover and readable secondaries via Always On Availability Groups, while requiring minimal administrative overhead. The 1 TB database size is supported in the Business Critical tier (up to 4 TB), and the managed platform handles patching, backups, and failover orchestration automatically, unlike IaaS-based solutions.

Answer

Azure SQL Database in the General Purpose tier.

Answer

SQL Server on Azure VMs with Always On Availability Groups.

Answer

Azure SQL Database in the Hyperscale tier.

Question 2

You are managing an Azure SQL Database that runs a critical business application. The database experiences a predictable surge in read-only queries every night at 2:00 AM. You need to configure automatic scaling to handle this surge without manual intervention. What should you do?

Accepted Answer

Configure autoscale settings on the Azure SQL Database using Azure Automation runbooks triggered by a metric alert.. Option D is correct because Azure SQL Database does not natively support automatic scaling based on load. To achieve this, you must use Azure Automation runbooks triggered by a metric alert (e.g., DTU or CPU percentage) to programmatically scale the database's service tier up or down. This approach allows you to handle the predictable nightly surge without manual intervention, as the runbook can be scheduled or triggered by a threshold alert.

Answer

Create a read replica and redirect read queries to it during the surge.

Answer

Manually scale up the database service tier before 2:00 AM each day.

Answer

Move the database to an Elastic Database Pool and rely on its built-in autoscaling.

Question 3

A company runs a critical SQL Server database on Azure Virtual Machines. The database experiences intermittent performance degradation during peak hours. The database administrator notices high I/O latency on the data drive. The current disk configuration uses Standard HDDs. What is the most cost-effective action to reduce I/O latency without changing the virtual machine size?

Accepted Answer

Migrate the data files to Premium SSD managed disks.. Migrating the data files to Premium SSD managed disks is the most cost-effective action to reduce I/O latency without changing the virtual machine size. Premium SSDs provide consistent low-latency performance and higher IOPS/throughput compared to Standard HDDs, directly addressing the high I/O latency during peak hours. This change does not require resizing the VM, keeping compute costs unchanged while improving storage performance.

Answer

Enable instant file initialization in SQL Server.

Answer

Increase the virtual machine size to a series with higher I/O limits.

Answer

Migrate the data files to Standard SSD managed disks.

Question 4

Which TWO configurations are required to enable automatic failover for an Azure SQL Database configured with active geo-replication?

Accepted Answer

Configure a failover group that includes the primary and secondary databases.. Option B is correct because a failover group is the Azure feature that manages automatic failover for geo-replicated databases. It coordinates the failover of the primary and all secondaries in the group, providing a single endpoint and automatic failover policy. Without a failover group, active geo-replication only supports manual failover.

Answer

Enable read-scale out on the secondary database.

Answer

Set the secondary database to have the same service tier as the primary.

Answer

Configure zone redundancy on the primary database.

Question 5

Refer to the exhibit. An Azure SQL Database is experiencing performance degradation. Based on the Extended Events and wait statistics, which is the most likely root cause?

Accepted Answer

I/O subsystem bottleneck. The exhibit shows PAGEIOLATCH_SH and WRITELOG waits dominating the wait statistics, which are classic indicators of I/O subsystem bottlenecks. PAGEIOLATCH_SH waits occur when a session is waiting for a data page to be read from disk into the buffer pool, while WRITELOG waits indicate delays in writing to the transaction log. These waits are not caused by CPU or memory pressure, but by slow disk I/O, making option C the correct root cause.

Answer

Blocking due to lock contention

Answer

CPU pressure from high-complexity queries

Answer

Insufficient memory allocation for the database

Question 6

Refer to the exhibit. The SalesDB database is experiencing log space full errors. Based on the exhibit, what is the most likely reason?

Accepted Answer

The transaction log is not being truncated, possibly due to an active transaction or replication. The exhibit shows a transaction log with very low free space (0.01%) and a high log used percentage (99.99%), but the data file has ample free space. This indicates the transaction log is not being truncated, likely due to an active transaction preventing log reuse or a replication scenario that marks log records as needed. In Azure SQL Database, log space is managed automatically, but long-running transactions or replication can block log truncation, causing log space full errors even when storage is not full.

Answer

The database storage is almost full, preventing log growth

Answer

The log rate limit is being throttled due to high log IO percentage

Answer

The database should be scaled to BusinessCritical tier for faster log writes

Question 7

You are configuring Azure SQL Database for a multi-tenant application. Each tenant's data is stored in a separate database. You need to ensure that a tenant admin can only manage their own database and not other databases on the same logical server. What is the best approach?

Accepted Answer

Create a contained database user with db_owner role in each tenant's database and use Microsoft Entra authentication.. Option B is correct because creating a contained database user with the db_owner role in each tenant's database, using Microsoft Entra authentication, ensures that the tenant admin can only manage their own database. Contained database users are scoped to the individual database, not the logical server, so they cannot access other databases on the same server. This aligns with the principle of least privilege for multi-tenant isolation.

Answer

Use a server-level firewall rule to restrict access to the tenant's IP.

Answer

Create a server-level login and assign it as db_owner on all databases.

Answer

Create a database-level firewall rule for each tenant database.

Question 8

Match each Azure SQL Database monitoring metric to its meaning.

Accepted Answer

DTU percentage: Measures the percentage of DTU limit used by the database.. These metrics are used to monitor resource usage and performance in Azure SQL Database.

Answer

DTU percentage: Measures the percentage of CPU limit used by the database.

Answer

CPU percentage: Measures the percentage of data I/O limit used by the database.

Answer

Data IO percentage: Measures the percentage of DTU limit used by the database.

Question 9

You are a junior DBA at a startup that uses Azure SQL Database for its SaaS application. The application uses a single database and the development team frequently makes schema changes. You need to implement security measures to:

1. Ensure that all connections from the application use TLS encryption.
2. Prevent unauthorized access from the internet.
3. Allow your office IP range (203.0.113.0/24) to connect for management.
4. Enable auditing of all schema changes.
5. Encrypt the database at rest using Azure-managed keys.

You have already enabled Transparent Data Encryption (TDE) with service-managed keys. What should you do next to meet the remaining requirements?

Accepted Answer

Set the minimal TLS version to 1.2. Add a firewall rule for the office IP range and remove the default 'Allow Azure services' rule. Enable auditing with SCHEMA_OBJECT_CHANGE_GROUP.. Option C is correct because it addresses all remaining requirements: setting the minimal TLS version to 1.2 ensures encrypted connections (requirement 1), adding a firewall rule for the office IP range and removing the default 'Allow Azure services' rule prevents unauthorized internet access while allowing management traffic (requirements 2 and 3), and enabling auditing with SCHEMA_OBJECT_CHANGE_GROUP captures schema changes (requirement 4). TDE with service-managed keys already satisfies requirement 5, so no further action is needed for encryption at rest.

Answer

Set the minimal TLS version to 1.2. Enable auditing with SCHEMA_OBJECT_CHANGE_GROUP.

Answer

Add a firewall rule for the office IP range. Enable auditing with SCHEMA_OBJECT_CHANGE_GROUP.

Answer

Add a firewall rule for the office IP range. Remove the default 'Allow Azure services' rule.

Question 10

Refer to the exhibit. An administrator wants to restore the primary database SalesDB to a point in time 2025-03-15T09:00:00Z. What is the impact on the geo-replication?

Accepted Answer

The geo-replication will be terminated, and the secondary will become a standalone database.. When you perform a point-in-time restore (PITR) on a primary database that is configured for geo-replication, the geo-replication link is broken. The secondary database becomes a standalone read-only database, and the primary is restored as a new database (typically with a name like SalesDB_restored or a specified name). This is because PITR creates a new database from backups, which cannot maintain the continuous replication stream required for geo-replication.

Answer

The geo-replication will continue seamlessly after the restore.

Answer

The restore will fail because the database is in a geo-replication relationship.

Answer

The secondary database will automatically be restored to the same point in time.

Question 11

You have an Azure SQL Database that uses a firewall rule allowing access from a specific range of IP addresses. A developer reports that they cannot connect from a new IP address that falls outside the allowed range. You need to temporarily allow the developer's IP address for 24 hours without affecting existing rules. What should you do?

Accepted Answer

Add a new firewall rule at the server level that allows the developer's IP address.. Option B is correct because Azure SQL Database firewall rules are configured at the server level (the logical server) to control inbound access. Adding a new server-level firewall rule for the developer's specific IP address allows temporary access without modifying or removing the existing range-based rule. This approach is the standard method for granting time-limited access to a single IP while preserving all other firewall configurations.

Answer

Configure a point-to-site VPN connection for the developer.

Answer

Update the existing firewall rule to include the developer's IP address.

Answer

Modify the database-level firewall rule to include the developer's IP.

Question 12

Refer to the exhibit. An Azure SQL Database in the Standard tier (S2: 50 DTU) is consistently showing high DTU consumption. Which action would most effectively reduce DTU usage?

Accepted Answer

Create an index on the tables accessed by Query 1234. Query 1234 is likely the primary contributor to high DTU consumption, as indicated by the exhibit (not shown here but implied). Creating an index on the tables it accesses can reduce the number of logical reads and improve query performance, directly lowering DTU usage without additional cost. This is the most effective action because it addresses the root cause—poor query performance—rather than masking the symptom with more resources.

Answer

Increase the log_write_percent by adjusting transaction log settings

Answer

Scale up to a higher service tier (e.g., S3)

Answer

Rebuild all indexes in the database

Question 13

You are configuring a new Azure SQL Database. The company policy requires that all connections use Microsoft Entra authentication and that no SQL authentication accounts exist. What should you do to prevent creation of SQL authenticated logins?

Accepted Answer

Set the 'DisallowSqlAuthentication' property to 'True' on the logical server.. Setting the 'DisallowSqlAuthentication' property to 'True' on the logical server enforces that only Microsoft Entra authentication can be used to connect to the Azure SQL Database. This property prevents the creation of any SQL authenticated logins, including the SQL admin account, and ensures compliance with the policy that no SQL authentication accounts exist.

Answer

Set the 'public_network_access' to 'Disabled'.

Answer

Remove the SQL admin login after creating the database.

Answer

Create an Azure Policy to audit SQL authentication usage.

Question 14

Which TWO of the following are valid methods to secure data in transit for Azure SQL Database?

Accepted Answer

Configure the server to enforce TLS 1.2 by setting minimal TLS version.. Option C is correct because Azure SQL Database allows you to enforce a minimum TLS version, such as TLS 1.2, which ensures that all client connections use at least that protocol version. This directly secures data in transit by preventing downgrade attacks and blocking older, less secure TLS versions like TLS 1.0 or 1.1.

Answer

Enable Always Encrypted on sensitive columns.

Answer

Deploy Azure Firewall in front of the database.

Answer

Apply a Network Security Group (NSG) to the subnet.

Question 15

You need to ensure that an Azure SQL Database uses Microsoft Entra-only authentication. You have configured the server to disallow SQL authentication. What additional step is required to prevent users from creating SQL logins?

Accepted Answer

Revoke the 'CREATE LOGIN' permission from all users except the Entra admin.. Disallowing SQL authentication at the server level prevents users from authenticating with SQL logins, but it does not revoke the ability to create new SQL logins. Users with the 'loginmanager' fixed server role or the 'CREATE LOGIN' permission can still create SQL logins, which would exist in the master database even though they cannot be used for authentication. To fully enforce Microsoft Entra-only authentication, you must explicitly revoke the 'CREATE LOGIN' permission from all users except the Entra admin, ensuring no new SQL logins can be created.

Answer

Remove the 'loginmanager' fixed server role from all users.

Answer

Enable auditing to detect login creation attempts and alert the security team.

Answer

No further action needed; disallowing SQL authentication prevents any SQL login creation.

Question 16

You are a database administrator for a manufacturing company that uses Azure SQL Database. The company has a requirement to encrypt sensitive data in transit between the application and the database. Additionally, the company wants to ensure that database administrators (DBAs) cannot view the sensitive data. Which TWO features should you implement?

Accepted Answer

Configure the server to enforce TLS 1.2 by setting the 'Minimal TLS Version' property. Option C is correct because configuring the server to enforce TLS 1.2 by setting the 'Minimal TLS Version' property ensures that all connections to Azure SQL Database use at least TLS 1.2, which encrypts data in transit. This directly addresses the requirement to encrypt sensitive data between the application and the database. Option E is correct because Always Encrypted with a column master key stored in Azure Key Vault ensures that sensitive data is encrypted at the client side and the encryption keys are never exposed to the database engine, preventing DBAs from viewing the sensitive data.

Answer

Implement row-level security (RLS) to filter rows

Answer

Enable transparent data encryption (TDE) on the database

Answer

Implement dynamic data masking on sensitive columns

Question 17

Your company has an Azure SQL Database that stores financial data. You need to implement auditing to track all data modifications (INSERT, UPDATE, DELETE) and schema changes (DDL). Which audit action group should you configure?

Accepted Answer

SCHEMA_OBJECT_CHANGE_GROUP and DATABASE_OBJECT_CHANGE_GROUP. To track all data modifications (INSERT, UPDATE, DELETE) and schema changes (DDL) in Azure SQL Database, you need to audit both data changes and schema changes. The SCHEMA_OBJECT_CHANGE_GROUP captures DDL changes like CREATE, ALTER, DROP on schema objects, while DATABASE_OBJECT_CHANGE_GROUP captures DML changes on database objects (tables, views). Together, they cover the required tracking. Option A is correct because these two action groups specifically target the stated requirements.

Answer

DATABASE_OBJECT_PERMISSION_CHANGE_GROUP and SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP

Answer

DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP and SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP

Answer

SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP

Question 18

You are a database administrator for a retail company that uses Azure SQL Database. The security team wants to prevent SQL injection attacks by ensuring that all application queries use parameterized statements. Which built-in Azure feature should you enable to help detect and alert on potential SQL injection attempts?

Accepted Answer

Enable Microsoft Defender for SQL. Microsoft Defender for SQL includes advanced threat detection capabilities that continuously monitor database activity for anomalous patterns, including SQL injection attempts. When enabled, it analyzes query execution patterns and can alert on suspicious queries that deviate from parameterized statement usage, directly addressing the security team's requirement to detect and alert on potential SQL injection attacks.

Answer

Enable auditing on the database

Answer

Enable data discovery and classification

Answer

Enable SQL vulnerability assessment

Question 19

You are configuring authentication for Azure SQL Database. Which TWO of the following are supported authentication methods?

Accepted Answer

Microsoft Entra ID authentication with a service principal.. Microsoft Entra ID authentication with a service principal is supported in Azure SQL Database. A service principal is an identity created for use with applications, hosted services, and automated tools to access Azure resources, and it can authenticate to Azure SQL Database using Microsoft Entra ID (formerly Azure AD) tokens. This method enables secure, non-interactive authentication for applications and automation scenarios.

Answer

Windows authentication using Kerberos.

Answer

OAuth 2.0 token authentication.

Answer

Certificate-based authentication for SQL logins.

Question 20

You are a database administrator for a bank that uses Azure SQL Database to store transaction data. The compliance team requires that you implement a solution to encrypt data at rest and in transit. Additionally, the solution must allow the database to be restored to any point in time within the last 35 days. Which TWO actions should you take?

Accepted Answer

Enable transparent data encryption (TDE) on the database. Option D is correct because Transparent Data Encryption (TDE) encrypts the database at rest, including backups and transaction log files, which satisfies the compliance requirement for data at rest. TDE is a built-in feature of Azure SQL Database that requires no application changes and is enabled at the database level.

Answer

Enable Microsoft Defender for SQL

Answer

Implement dynamic data masking on sensitive columns

Answer

Enable Always Encrypted for all columns containing sensitive data