DP-300 Practice Test 10 — 10 Questions

Question 1

Refer to the exhibit. An Azure SQL Database is receiving Intelligent Insights degradation alerts. Which action should be taken first?

Accepted Answer

Implement automatic tuning recommendations. The database is hitting DTU limits (100% CPU and data IO). The alert recommends scaling up or optimizing queries. Since CPU and data IO are both high, query optimization is the better first step to avoid cost increase.

Answer

Increase the maximum storage size

Answer

Change the service tier to BusinessCritical

Answer

Scale up to Standard S3 (100 DTU)

Question 2

Refer to the exhibit. The ARM template snippet configures auditing for an Azure SQL Database. Based on the configuration, which events are audited?

Accepted Answer

Successful and failed database authentication attempts.. The auditActionsAndGroups include SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP, which audit successful and failed logins respectively. Option A is wrong because data modification statements (DML) are not included. Option C is wrong because data modifications are not included. Option D is wrong because only authentication events are listed.

Answer

All data modification statements (DML).

Answer

Only failed authentication attempts.

Answer

All database schema changes (DDL).

Question 3

You are configuring workload management for an Azure SQL Database using the JSON exhibit above for a classifier named 'MyWorkloadClassifier'. The classifier is intended to assign high importance to queries from user 'User1' in the 'SalesDB' database. However, after deployment, you notice that queries from 'User1' are not getting the expected resource guarantees. What is the most likely reason?

Accepted Answer

The 'memberName' in the context is not correctly formatted; it should be a single user or group name without a backslash.. The most likely reason is that the classifier's context is incorrectly formatted. In Azure SQL Database workload management, the 'memberName' property within the classifier context must be specified as a single user or group name without a backslash. If the JSON exhibit contains a backslash (e.g., 'SalesDB\User1' instead of 'User1'), the classifier fails to match the user. Option A is incorrect because 'importance' is a string ('high'), not an integer. Option C is incorrect because 'min_percentile_resource' is not the issue; resource guarantees depend on the workload group's allocation. Option D is incorrect because a classifier does not need to be associated with a workload group directly; it is defined at the database level.

Answer

The 'importance' property is set to 'high' but the classifier requires 'importance' to be an integer.

Answer

The 'min_percentile_resource' value is too low to guarantee resources.

Answer

The classifier is not associated with a workload group.

Question 4

Your team uses Azure SQL Database and wants to use Microsoft Entra ID authentication. You need to create a contained database user mapped to a Microsoft Entra ID application (service principal). Which T-SQL command should you use?

Accepted Answer

CREATE USER [app-name] FROM EXTERNAL PROVIDER;. `CREATE USER [app-name] FROM EXTERNAL PROVIDER` creates a contained database user mapped to a Microsoft Entra ID identity (user, group, or application) directly in the database, without requiring a server-level login. This is the required syntax for authenticating a service principal (application) in Azure SQL Database using Microsoft Entra ID authentication.

Answer

CREATE USER [app-name] FROM LOGIN [app-name];

Answer

CREATE LOGIN [app-name] FROM EXTERNAL PROVIDER;

Answer

CREATE USER [app-name] WITH PASSWORD = 'password';

Question 5

You have an Azure SQL Managed Instance configured with a failover group between the primary region (East US) and secondary region (West US). The secondary instance is used for read-only query workloads. During a planned failover test, you notice that after failover, the read-only queries on the secondary instance fail with error 4060 (cannot open database). What is the most likely cause?

Accepted Answer

The failover changed the secondary instance to primary, and read-only routing is not configured for the new secondary.. After failover, the secondary role changes to primary, and the database becomes writable. Read-only routing must be reconfigured or the connection string must specify ApplicationIntent=ReadOnly to reach the new secondary. Option A (secondary is not readable) is false because failover groups support readable secondary. Option B (firewall rules) would cause a different error. Option D (license type) does not affect read access.

Answer

The secondary instance is not in the same license type as the primary.

Answer

The firewall rules on the secondary instance do not allow the client IP.

Answer

The secondary instance is not configured to allow read-only access.

Question 6

You are managing an Azure SQL Database that has Automatic Tuning enabled. You receive an alert that a query plan regression was detected and a plan correction was automatically applied. You want to verify the performance improvement. What should you use?

Accepted Answer

Query the Query Store to compare query performance before and after the plan change.. Query Store provides detailed query performance data, including plan regressions and improvements. Option A is wrong because sys.dm_exec_query_stats gives current performance metrics but does not provide historical comparison. Option B is wrong because Azure Monitor alert details only notify that a regression occurred, not the performance improvement. Option D is wrong because the automatic tuning log shows actions taken but not detailed performance metrics for comparison.

Answer

Use sys.dm_exec_query_stats to view current performance.

Answer

Review the Azure Monitor alert details.

Answer

Check the automatic tuning log in the Azure portal.

Question 7

Your organization uses Azure SQL Database and needs to automate email notifications when a database reaches 80% storage usage. Which native Azure feature can you use?

Accepted Answer

Create an Azure Monitor alert rule on the 'storage_percent' metric with an email action group.. Azure Alert Rules can monitor metrics like storage percent and trigger email actions. Option B is wrong because SQL Agent is not available in Azure SQL Database. Option C is wrong because Elastic Jobs execute T-SQL, not send email directly. Option D is wrong because Database Mail is not available in Azure SQL Database.

Answer

Create a SQL Agent alert that fires when the storage is above 80% and sends an email.

Answer

Configure Database Mail to send alerts automatically.

Answer

Create an Elastic Database Job that checks storage and sends email via sp_send_dbmail.

Question 8

You are responsible for security compliance of Azure SQL databases. You need to audit all successful and failed login attempts and store the audit logs in a Log Analytics workspace for analysis. You also want to detect potential brute-force attacks. What should you implement?

Accepted Answer

Enable Azure SQL Auditing for the server, configure the audit log destination to Log Analytics, and enable Microsoft Sentinel for threat detection.. Azure SQL Auditing captures both successful and failed login attempts (audit logs) and can be configured to send them directly to a Log Analytics workspace for centralized analysis. Microsoft Sentinel, when enabled, provides built-in analytics rules to detect brute-force attacks by correlating failed login patterns across time and IP addresses, fulfilling the threat detection requirement.

Answer

Configure Azure Policy to enforce auditing on all SQL databases in the subscription.

Answer

Enable SQL Vulnerability Assessment and schedule recurring scans.

Answer

Enable Advanced Threat Protection (ATP) for Azure SQL Database.

Question 9

You are the database administrator for an Azure SQL Managed Instance hosting a data warehouse workload. You notice that the storage space consumed by the database is significantly larger than expected. The database has multiple large tables with clustered columnstore indexes. You suspect that the columnstore indexes have become fragmented and that deleted rows are consuming space. You need to reclaim storage space with minimal impact on query performance during business hours. What should you do?

Accepted Answer

Perform an ALTER INDEX REORGANIZE with the COMPRESS_ALL_ROW_GROUPS option on the affected columnstore indexes.. REORGANIZE with COMPRESS_ALL_ROW_GROUPS compresses all row groups, including those in the delta store, and removes deleted rows from columnstore indexes. This operation is online and can be performed during business hours with minimal impact on query performance, unlike a rebuild which is offline and resource-intensive. It directly addresses the fragmentation and deleted row space consumption in columnstore indexes.

Answer

Perform an ALTER INDEX REBUILD on the affected columnstore indexes after business hours.

Answer

Perform an ALTER INDEX REORGANIZE on the affected columnstore indexes.

Answer

Rebuild the entire database by creating a new database and copying data.

Question 10

You are migrating a 10-TB SQL Server database to Azure SQL Database Hyperscale. The source database has multiple filegroups and uses full-text indexing. The migration must be completed with minimal downtime. Which migration method should you use?

Accepted Answer

Use the Azure SQL Migration extension in Azure Data Studio with online migration mode. The Azure SQL Migration extension in Azure Data Studio with online migration mode is the correct choice because it supports minimal-downtime migrations for large databases (10 TB) with complex features like multiple filegroups and full-text indexing. It uses the Data Migration Service (DMS) to continuously replicate changes from the source to the target Azure SQL Database Hyperscale, allowing you to cut over with only a brief pause.

Answer

Set up transactional replication from on-premises to Azure

Answer

Use Azure Data Factory to copy data

Answer

Export a BACPAC file and import it to Azure