DP-300 Plan and implement data platform resources Practice Test 12 — 15 Questions

Question 1

You are configuring Azure SQL Database for a new application. The security policy requires that all connections use Microsoft Entra authentication and that the database blocks IP addresses from outside your corporate network. You also need to ensure that the application can connect without storing credentials in code. Which combination of features should you implement?

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

Accepted Answer

Microsoft Entra authentication, firewall rules, and managed identity. It satisfies all three requirements: Microsoft Entra authentication enforces identity-based access, firewall rules block IP addresses outside the corporate network, and a managed identity allows the application to connect without storing credentials in code by using a system-assigned or user-assigned identity to obtain an access token from Microsoft Entra ID.

Answer

Always Encrypted, VNet service endpoints, and SQL authentication

Answer

Transparent Data Encryption, IP firewall rules, and connection strings

Answer

Azure Defender for SQL, firewall rules, and service principal

Question 2

You are deploying a new Azure SQL Database for an application that will store sensitive financial data. The compliance team requires that the database be configured to automatically detect and alert on anomalous access patterns, and that all queries be logged for auditing. Which services should you enable?

Accepted Answer

Azure Defender for SQL and SQL Auditing. Azure Defender for SQL provides anomaly detection and alerts for suspicious access patterns (e.g., SQL injection, brute force), while SQL Auditing captures all queries and events for compliance logging. Together, they meet the requirements for automatic detection and full query auditing without additional services.

Answer

Azure Purview and vulnerability assessment

Answer

Microsoft Sentinel and SQL Auditing

Answer

SQL Server auditing and vulnerability assessment

Question 3

You are managing an Azure SQL Managed Instance that hosts multiple databases for a financial application. You need to implement a security solution that meets compliance requirements by auditing all database activity and sending the audit logs to a centralized Log Analytics workspace for analysis. The solution must also support real-time alerts on suspicious activities. What should you configure?

Accepted Answer

Enable auditing to a Log Analytics workspace and integrate with Microsoft Sentinel.. Auditing to a Log Analytics workspace allows centralized collection of audit logs, which can then be integrated with Microsoft Sentinel for real-time analytics, threat detection, and automated alerting on suspicious activities. This meets both the compliance requirement for auditing and the operational need for real-time alerts.

Answer

Enable Microsoft Defender for Cloud and configure SQL vulnerability assessment.

Answer

Enable auditing to a storage account and use Microsoft Intune for monitoring.

Answer

Enable Microsoft Purview Data Map for the managed instance.

Question 4

You are configuring Azure SQL Database for a new application. The security policy requires that all connections use Microsoft Entra authentication and that the database blocks IP addresses from outside your corporate network. You also need to ensure that the application can connect without storing credentials in code. Which combination of features should you implement?

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

Accepted Answer

Azure SQL Database serverless.. Azure SQL Database serverless is the correct choice because it automatically scales compute resources based on demand and supports pausing the database when idle, effectively scaling to zero compute to save costs. This aligns perfectly with the requirements of variable traffic and cost optimization without manual intervention.

Answer

Azure SQL Database elastic pool.

Answer

Azure SQL Database Hyperscale.

Answer

Azure SQL Database Business Critical tier.

Question 5

You need to create an Azure SQL Database that will be used by a new application. The database must support JSON data storage and querying. Which data type should you use to store JSON documents?

Accepted Answer

NVARCHAR. B is correct because Azure SQL Database uses the NVARCHAR data type to store JSON documents as text. JSON is stored as a string in NVARCHAR columns, which supports Unicode characters and allows the use of built-in JSON functions like JSON_VALUE, JSON_QUERY, OPENJSON, and ISJSON for validation and querying. NVARCHAR(MAX) is recommended for large or variable-length JSON documents.

Answer

TEXT

Answer

XML

Answer

VARCHAR

Question 6

You are designing a security strategy for Azure SQL Database. You need to ensure that database access is secured using Microsoft Entra ID (formerly Azure Active Directory) authentication. Which THREE actions should you take? (Choose THREE.)

Accepted Answer

Create a Microsoft Entra ID administrator for the Azure SQL logical server.. Option A is correct because every Azure SQL logical server must have a Microsoft Entra ID administrator provisioned (via the server's Microsoft Entra admin setting) before Entra authentication can be used to manage and connect to the database. Option C is correct because applications should authenticate using Microsoft Entra ID with MFA, leveraging token-based authentication (for example, Microsoft.Data.SqlClient with Authentication=Active Directory Interactive/Password or managed identity) to enforce strong, centralized identity controls. Option E is correct because contained database users mapped to Microsoft Entra ID principals (CREATE USER ... FROM EXTERNAL PROVIDER) allow Entra logins to access the database without requiring server-level logins, which is the recommended pattern for Entra-authenticated database access. Option B is not required: disabling SQL authentication is optional and not mandatory for enabling Entra ID authentication, and it can break existing tooling or break-glass scenarios. Option D is not relevant here because TDE provides encryption at rest for data files and does not implement Entra ID authentication for database access.

Answer

Disable SQL Server authentication after migrating to Entra ID.

Answer

Enable Transparent Data Encryption (TDE).

Question 7

You are reviewing an ARM template for creating a new Azure SQL Database. The template uses the above JSON to create a database named 'db2' from 'db1'. The source database 'db1' is currently in a failed state due to a storage issue. What will be the result of deploying this template?

Accepted Answer

The deployment will fail because db1 is not in a recoverable state.. The ARM template creates a new database by copying from a source database. Azure SQL Database requires the source database to be in an online and healthy state to perform a copy operation. Since db1 is in a failed state due to a storage issue, it is not accessible for copying, so the deployment will fail.

Answer

It will create an empty database because the source is not accessible.

Answer

It will delete db1 and create db2 as a replacement.

Answer

It will create a new database by recovering db1 to its last known good state.

Question 8

Your team is migrating an on-premises SQL Server 2019 database to Azure SQL Managed Instance. The database uses Service Broker for cross-database messaging. The compliance requirement mandates that the migration must be performed with minimal downtime and that the target must support the Service Broker feature. What migration strategy should you recommend?

Accepted Answer

Use Azure Database Migration Service (DMS) with online migration to Azure SQL Managed Instance. Azure SQL Managed Instance fully supports Service Broker, and the Azure Database Migration Service (DMS) with online migration mode enables minimal downtime by continuously replicating changes from the source SQL Server to the target Managed Instance until a cutover. This satisfies both the Service Broker feature requirement and the compliance mandate for minimal downtime.

Answer

Perform a native backup and restore to Azure SQL Managed Instance

Answer

Use the Data Migration Assistant (DMA) to migrate to Azure SQL Database

Answer

Use Azure Database Migration Service (DMS) with offline migration to Azure SQL Database

Question 9

You are designing a disaster recovery plan for an Azure SQL Database that uses the Business Critical tier. The database is deployed in the West US region. You need to ensure that if the entire West US region becomes unavailable, the database can be failed over to a secondary region with minimal data loss. What should you implement?

Accepted Answer

Configure an auto-failover group with a readable secondary in East US. Auto-failover groups with a readable secondary in East US provide automatic and manual failover capabilities. For Business Critical tier, the secondary is readable and uses asynchronous replication, typically achieving an RPO of 5 seconds or less. This meets the requirement for minimal data loss. Active geo-replication also uses asynchronous replication with similar RPO but requires manual failover and does not support automatic failover. Zone redundancy protects against within-region failures, not regional outages. Geo-restore has a higher RPO and requires manual recovery.

Answer

Configure active geo-replication to East US

Answer

Enable zone redundancy for the database

Answer

Configure geo-restore from the West US database

Question 10

Your company requires that all Azure SQL Databases use Transparent Data Encryption (TDE) with customer-managed keys (CMK) stored in Azure Key Vault. The security policy mandates that the key must be rotated every 90 days. You need to implement this requirement with minimal administrative overhead. What should you do?

Accepted Answer

Use Azure Key Vault key rotation policy to automatically rotate the key every 90 days. Azure Key Vault supports automatic key rotation policies that can be configured to rotate a customer-managed key (CMK) every 90 days without any manual intervention. When this key is used for TDE in Azure SQL Database, the database automatically uses the latest version of the key from Key Vault, so no additional steps are needed to update the database after rotation. This satisfies the security policy with minimal administrative overhead.

Answer

Manually rotate the key in Key Vault every 90 days and update the database

Answer

Use Azure SQL Database server-side TDE with a service-managed key

Answer

Store the key in the database and use a custom rotation script

Question 11

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 12

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 13

You have an Azure SQL Database with Query Store enabled. You notice that a critical stored procedure has regressed in performance. You need to force a previous, better-performing execution plan for that query. What should you do?

Accepted Answer

Execute sys.sp_query_store_force_plan with the query_id and plan_id.. `sys.sp_query_store_force_plan` is the dedicated system stored procedure in Azure SQL Database that forces the Query Store to use a specific execution plan for a given query. When a stored procedure's performance regresses, you identify the query_id and plan_id of the better-performing plan from Query Store views and execute this procedure to pin that plan, overriding the optimizer's choice.

Answer

Disable and re-enable Query Store to reset the plans.

Answer

Execute sys.sp_query_store_set_hints to add a query hint.

Answer

Use sys.dm_db_tuning_recommendations to apply a plan.

Question 14

Your company is deploying a new application that uses an Azure SQL Database. The security policy requires that all connections use Microsoft Entra ID authentication and that no SQL authentication users are created. Which server-level setting should you enforce?

Accepted Answer

Enable 'Azure AD-only authentication' on the Azure SQL logical server.. Enabling 'Microsoft Entra ID-only authentication' on the Azure SQL logical server enforces that all connections must use Microsoft Entra ID authentication and blocks any SQL authentication attempts, even if SQL logins exist. This setting directly meets the security policy requirement that no SQL authentication users are created and all connections use Entra ID.

Answer

Set the database to use contained database authentication.

Answer

Configure a server-level firewall rule to allow only Entra ID IPs.

Answer

Set the Entra ID admin for the server and disable SQL authentication.

Question 15

You are monitoring an Azure SQL Database and notice a pattern of high CPU usage during business hours. You need to identify the queries consuming the most CPU over the last 24 hours. Which dynamic management view should you query?

Accepted Answer

sys.dm_exec_query_stats. sys.dm_exec_query_stats (Option C) is the correct DMV because it returns aggregate performance statistics for cached query plans, including total CPU time (total_worker_time), execution count, and last execution time. By querying this view and ordering by total_worker_time descending, you can identify the queries that have consumed the most CPU over the last 24 hours, directly addressing the pattern of high CPU usage during business hours.

Answer

sys.dm_exec_requests

Answer

sys.dm_exec_sessions

Answer

sys.dm_os_performance_counters