DP-300 Practice Exam 57 — 20 Questions

Question 1

You are planning to migrate several on-premises SQL Server databases to Azure SQL Managed Instance. The databases use SQL Server Agent jobs, cross-database queries, and CLR assemblies. Which migration tool should you use to assess compatibility?

Accepted Answer

Data Migration Assistant (DMA). Option C is correct because the Data Migration Assistant (DMA) is specifically designed to assess compatibility of on-premises SQL Server databases with Azure SQL Managed Instance, including features like SQL Server Agent jobs, cross-database queries, and CLR assemblies. DMA performs a detailed feature parity check and identifies blocking issues, such as unsupported CLR permissions or cross-database dependencies, before migration. It provides a comprehensive report with remediation guidance, making it the ideal tool for this pre-migration assessment phase.

Answer

Azure Database Migration Service (DMS)

Answer

Azure Data Studio with SQL Server migration extension

Answer

SQL Server Management Studio (SSMS)

Question 2

Which THREE factors should you consider when choosing between Azure SQL Database and Azure SQL Managed Instance for a new workload?

Accepted Answer

Lift-and-shift migration of on-premises databases. Option B is correct because Azure SQL Managed Instance is designed for lift-and-shift migrations of on-premises databases, offering near 100% compatibility with SQL Server features like instance-level configurations, linked servers, and SQL Server Agent. Azure SQL Database, as a Platform-as-a-Service (PaaS) offering, lacks these instance-scoped capabilities, making Managed Instance the preferred choice for migrating existing databases with minimal changes.

Answer

Need for a static IP address

Answer

Both support automated backups

Question 3

You have an Azure SQL Managed Instance that is experiencing performance degradation. You suspect a query is causing excessive blocking. You need to identify the blocking chain and the resource holding the lock. Which DMV should you query?

Accepted Answer

sys.dm_tran_locks and sys.dm_os_waiting_tasks. To identify the blocking chain and the specific resource holding the lock, you need to combine lock metadata with wait information. sys.dm_tran_locks shows current locks and their resource types (e.g., RID, KEY, PAGE, OBJECT), while sys.dm_os_waiting_tasks reveals which sessions are waiting on those locks and the blocking session ID. Together, these DMVs allow you to trace the blocking chain from the blocked session back to the blocker and pinpoint the exact resource causing contention.

Answer

sys.dm_exec_requests

Answer

sys.dm_exec_query_stats

Answer

sys.dm_tran_active_snapshot_database_transactions

Question 4

You are planning to deploy Azure SQL Database for a new application. The application requires high availability and the ability to scale out read workloads. You need to choose the appropriate deployment option and feature. Which TWO options should you select? (Choose two.)

Accepted Answer

Configure a failover group with a readable secondary.. Option C is correct because a failover group with a readable secondary provides both high availability and read-scale capabilities. The readable secondary allows read-only workloads to be offloaded, while automatic failover ensures business continuity during an outage.

Answer

Use an elastic pool to group databases for resource sharing.

Answer

Enable zone redundancy for the database.

Answer

Deploy a single Azure SQL Database with locally redundant backup storage.

Question 5

Your organization is implementing Microsoft Purview for data governance. You need to scan an Azure SQL Database to automatically classify sensitive data. The security team requires that the scan use a managed identity and that the data classification be stored in Microsoft Purview. What should you configure on the Azure SQL Database?

Accepted Answer

Assign the Microsoft Purview managed identity the db_datareader role and configure the firewall to allow Purview IP ranges. Option C is correct because Microsoft Purview uses its own managed identity to authenticate to data sources when scanning. To scan an Azure SQL Database, you must grant Purview's managed identity the `db_datareader` role in the database to allow read access for classification, and you must configure the Azure SQL firewall to allow inbound connections from the Purview IP ranges (or enable 'Allow Azure services and resources to access this server'). This satisfies the security team's requirement to use a managed identity and store classification in Purview.

Answer

Enable system-assigned managed identity on the database and grant it permissions to access Purview

Answer

Use SQL authentication with a dedicated login and firewall rule

Answer

Create a user-assigned managed identity and assign it to the database

Question 6

You have an Azure SQL Database with a table that stores historical data. To improve query performance, you create a nonclustered columnstore index on the table. However, after a large data load, you notice that query performance is worse than before. What is the most likely reason?

Accepted Answer

The data was loaded in small batches, causing rowgroups to be smaller than optimal.. Option B is correct because when data is loaded in small batches into a columnstore index, each batch may form its own rowgroup. Rowgroups with fewer than 102,400 rows are considered 'delta rowgroups' and are not compressed into the columnstore format, leading to poor compression and suboptimal query performance. The large data load likely consisted of many small batches, resulting in numerous small rowgroups that degrade scan performance.

Answer

The columnstore index is fragmented and needs to be rebuilt.

Answer

The table has too many columns included in the index.

Answer

The index was created with compression disabled.

Question 7

You are the database administrator for a company that uses Azure SQL Database. You need to implement a security solution that automatically detects and alerts on suspicious activities, such as SQL injection attempts. Which feature should you enable?

Accepted Answer

Microsoft Defender for SQL. Microsoft Defender for SQL (formerly Azure Security Center's Advanced Threat Protection) is the correct feature because it continuously monitors database traffic for anomalous activities, including SQL injection, brute-force attacks, and privilege escalation. When suspicious behavior is detected, it generates a security alert that can be viewed in the Azure portal or integrated with Azure Sentinel for automated response. This is the only option among the choices that provides proactive threat detection and alerting for suspicious activities.

Answer

Azure SQL Auditing

Answer

SQL Vulnerability Assessment

Answer

Transparent Data Encryption (TDE)

Question 8

You have an Azure SQL Database with sensitive customer data. You need to mask the credit card numbers so that only users with the 'Unmask' permission can see the full number. Non-privileged users should see only the last four digits. Which feature should you implement?

Accepted Answer

Dynamic Data Masking. Dynamic Data Masking (DDM) is the correct feature because it allows you to obfuscate sensitive data in query results without changing the underlying database. You can define a mask on the credit card column that shows only the last four digits by default, and grant the UNMASK permission to privileged users so they see the full value. This directly meets the requirement of role-based partial masking without altering the stored data.

Answer

Column-level security

Answer

Row-level security

Answer

Always Encrypted

Question 9

You are deploying an Azure SQL Database for a new application that requires consistent performance and low latency. The database will be accessed by users across multiple Azure regions. Which deployment option should you use?

Accepted Answer

Single database with Active Geo-Replication. Active Geo-Replication for a single database enables continuous data synchronization to readable secondary replicas in different Azure regions, providing low-latency read access for globally distributed users and supporting manual failover for write availability. This meets the requirement for consistent performance and low latency across multiple regions without the orchestration overhead of failover groups.

Answer

Managed Instance with failover groups

Answer

Single database in one region

Answer

Elastic pool in one region

Question 10

You are implementing row-level security (RLS) in Azure SQL Database to enforce data isolation for multiple tenants. Each tenant has a TenantID. The RLS predicate function uses the SESSION_CONTEXT variable set by the application. You need to ensure that users cannot bypass the RLS by setting arbitrary SESSION_CONTEXT values. What should you do?

Accepted Answer

Create a stored procedure that sets the SESSION_CONTEXT and grant the application only execute permission on that procedure; revoke SET permission from users. Option A is correct because by creating a stored procedure that sets the SESSION_CONTEXT and granting the application only EXECUTE permission on that procedure while revoking SET permission from users, you prevent users from arbitrarily setting SESSION_CONTEXT values. This enforces that only the trusted application code can set the context used by the RLS predicate, ensuring tenants cannot impersonate other tenants.

Answer

Use dynamic data masking to hide the TenantID column

Answer

Encrypt the SESSION_CONTEXT values using Always Encrypted

Answer

Use a firewall rule to restrict access to the database to only the application IP addresses

Question 11

Which THREE of the following are required to configure Microsoft Entra authentication for an Azure SQL Managed Instance?

Accepted Answer

Enable the managed instance system-assigned managed identity.. Enabling the system-assigned managed identity for Azure SQL Managed Instance is required because Microsoft Entra authentication relies on this identity to authenticate the instance itself against Microsoft Entra ID. Without a managed identity, the instance cannot securely obtain tokens or perform directory lookups needed for authentication and authorization.

Answer

Ensure the managed instance is deployed in a different virtual network from the clients.

Answer

Configure the managed instance to allow public network access.

Question 12

Refer to the exhibit. You are troubleshooting an Azure SQL Database auditing configuration. The exhibit shows the blob auditing policy. The storage account access key is null, and the subscription ID is all zeros. What is the most likely issue?

Accepted Answer

Auditing will fail because the storage account access key is null.. The exhibit shows that the storage account access key is null, which means Azure SQL Database cannot authenticate to the storage account using the access key. Without a valid access key or a configured managed identity, blob auditing will fail because the database cannot write audit logs to the specified storage container. Option C is correct because a null access key directly prevents auditing from functioning when no alternative authentication method is configured.

Answer

Auditing will fall back to Log Analytics workspace.

Answer

Auditing will work because managed identity is used.

Answer

Auditing will write to the storage account using the system-assigned managed identity.

Question 13

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database. The template configures backup retention. What is the effect of this configuration?

Accepted Answer

Point-in-time restore (PITR) backups are retained for 7 days, and differential backups occur every 12 hours.. The ARM template configures the backup retention settings for Azure SQL Database. By default, Azure SQL Database automatically performs full backups every week, differential backups every 12 hours, and transaction log backups every 5–10 minutes. The configuration shown sets the point-in-time restore (PITR) retention period to 7 days, meaning you can restore the database to any point within the last 7 days. Differential backups occur every 12 hours to support efficient PITR, but the retention setting directly controls how far back you can perform a point-in-time restore.

Answer

Full backups are taken every 12 hours and retained for 7 days.

Answer

Long-term retention (LTR) is set to 7 days.

Answer

Transaction log backups are taken every 12 hours.

Question 14

You are reviewing an Azure SQL Database audit policy configuration. The policy is set to audit successful and failed database authentication events. You notice that audit logs are being written to both Azure Blob Storage and Azure Monitor. However, you are concerned about security of the storage account access key in the policy. What is the recommended approach to securely reference the storage account?

Accepted Answer

Configure the server's system-assigned managed identity and grant it access to the storage account.. Option D is correct because using a system-assigned managed identity for the Azure SQL Database logical server eliminates the need to store a storage account access key in the audit policy configuration. Managed identities provide a secure, Azure AD-backed identity that can be granted specific permissions (e.g., Storage Blob Data Contributor) on the storage account, ensuring that access is controlled via RBAC rather than a static key. This approach aligns with security best practices by removing the risk of key exposure or rotation failures.

Answer

Remove the storageAccountAccessKey property and rely on the storage endpoint.

Answer

Use a different storage account with a key that expires daily.

Answer

Disable the Azure Monitor destination and only use Blob Storage.

Question 15

You are the database administrator for a company that uses Azure SQL Managed Instance. The security team has detected unusual query activity from a specific client IP address. You need to immediately block all connections from that IP address while maintaining connectivity for all other users. Which solution should you implement?

Accepted Answer

Add a firewall rule at the managed instance level that denies the specific IP address.. Option C is correct because Azure SQL Managed Instance supports instance-level firewall rules that can explicitly deny a specific client IP address while allowing all other traffic. Unlike Azure SQL Database, which uses server-level firewall rules, Managed Instance firewall rules are configured directly on the instance and can include both allow and deny entries, enabling granular access control without affecting other users.

Answer

Create a private endpoint for the managed instance and disable public network access.

Answer

Add a server-level firewall rule at the logical server level to deny the IP address.

Answer

Modify the network security group (NSG) associated with the managed instance's subnet to deny the IP address.

Question 16

You are configuring security for an Azure SQL Database that will be used by a web application. The application uses a connection string with SQL authentication. You need to protect the database from SQL injection attacks. Which two measures should you implement? (Choose two.)

Accepted Answer

Use parameterized queries in the application.. Parameterized queries ensure that user input is treated as data, not executable code, by separating SQL logic from input values. This prevents attackers from injecting malicious SQL statements into the query string, which is the primary defense against SQL injection attacks.

Answer

Enable Transparent Data Encryption (TDE).

Answer

Configure Dynamic Data Masking (DDM).

Answer

Enable Always Encrypted on sensitive columns.

Question 17

Your company uses Azure SQL Database with Azure Active Directory (now Microsoft Entra ID) authentication. A new security policy requires that all database users must be authenticated via Microsoft Entra ID only. You need to disable SQL authentication for an Azure SQL Database logical server. What should you do?

Accepted Answer

Set the 'Azure AD-only authentication' property to Enabled for the logical server.. Option C is correct because enabling the 'Azure AD-only authentication' property at the logical server level explicitly blocks all SQL authentication attempts, forcing every database user to authenticate via Microsoft Entra ID. This property is a server-level toggle that overrides any existing SQL logins or contained database users, ensuring compliance with the policy without needing to manually remove logins.

Answer

Remove the Azure AD administrator for the server.

Answer

Remove all SQL logins from the master database.

Answer

Set the 'Azure AD-only authentication' property to Enabled for each database.

Question 18

Which TWO actions are required to enable Microsoft Entra ID authentication for Azure SQL Database?

Accepted Answer

Set an Microsoft Entra ID administrator for the logical server. Setting a Microsoft Entra ID administrator for the logical server (Option A) is required because it establishes the Entra ID tenant as an identity provider for the Azure SQL Database logical server, enabling token-based authentication. Creating a contained database user mapped to an Entra ID identity (Option D) is required because Azure SQL Database uses contained database users for authentication, where the user is authenticated directly against the database without requiring a login in the master database. These two actions together allow Entra ID users to authenticate to the database using their cloud identities.

Answer

Create a login in the master database for each Entra ID user

Answer

Assign a system-assigned managed identity to the logical server

Answer

Enable 'contained database authentication' on the server

Question 19

You are the DBA for a company that uses Azure SQL Database. You need to ensure that only authorized users can view sensitive columns (e.g., salary) in the Employees table. You want to obfuscate the data for certain users but allow full access to HR managers. Which feature should you use?

Accepted Answer

Dynamic Data Masking. Dynamic Data Masking (DDM) is the correct choice because it obfuscates sensitive columns (e.g., salary) in query results for unauthorized users while allowing full visibility for authorized users like HR managers. DDM applies masking rules at the database level without modifying the underlying data, making it ideal for scenarios where you need to limit exposure of sensitive data to certain roles without changing the application code.

Answer

Always Encrypted

Answer

Row-Level Security (RLS)

Answer

Transparent Data Encryption (TDE)

Question 20

Your Azure SQL Database is protected by a failover group. You need to ensure that during a failover to the secondary region, only authenticated applications can connect. What should you configure?

Accepted Answer

Use the failover group listener endpoint with Microsoft Entra ID authentication.. The failover group listener endpoint provides a DNS name that automatically points to the current primary region. By configuring Microsoft Entra ID authentication on the logical server, you ensure that only authenticated applications (using Microsoft Entra tokens) can connect, even after a failover. This eliminates reliance on SQL authentication passwords, which could be compromised or misconfigured across regions.

Answer

Set up a private endpoint for each region and update connection strings manually.

Answer

Deploy an Always On availability group listener.

Answer

Configure geo-replication and use the secondary server's public endpoint.