DP-300 Practice Exam 55 — 20 Questions

Question 1

You manage an Azure SQL Database that runs a customer-facing application. You notice that during peak hours, the DTU consumption reaches 100% and queries experience timeouts. You need to ensure consistent performance without manual intervention. What should you implement?

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

Accepted Answer

Scale up the database to a higher service tier (e.g., from S2 to S3).. Scaling up to a higher service tier (e.g., from S2 to S3) increases DTU limits, providing more CPU, memory, and IO resources to handle peak demand, thus preventing timeouts without manual intervention. Option A is incorrect because Query Store helps monitor and troubleshoot performance but does not automatically resolve resource shortages. Option B is incorrect because read scale-out offloads read-only queries to a read-only replica, but the issue is DTU consumption reaching 100%, which affects all queries, not just reads. Option C is incorrect because automatic tuning can fix plan regression but does not add resources; it only optimizes query plans.

Answer

Enable Query Store to identify costly queries.

Answer

Implement read scale-out to offload read queries.

Answer

Configure automatic tuning to force plan regression fixes.

Question 2

You observe that the average of Maximum DTU consumption over the last hour is consistently above 90%. What should you do next?

Accepted Answer

Scale up the database to a higher service tier or increase DTU.. When average DTU consumption is consistently above 90%, it indicates high resource usage. Scaling up the database to a higher service tier or increasing DTUs directly addresses the resource pressure and improves performance. Option B is incorrect because enabling Query Store is a diagnostic tool to identify performance issues, but it does not provide immediate relief. Option C is incorrect because rebuilding indexes can help with fragmentation but may not address the underlying resource limitation. Option D is incorrect because sustained high DTU can lead to performance degradation and should be addressed.

Answer

Enable Query Store to analyze top queries.

Answer

Rebuild all indexes in the database.

Answer

Do nothing; it's normal for DTU to be high.

Question 3

You need to monitor Azure SQL Database performance over time and receive alerts when CPU usage exceeds 80%. Which Azure service should you use?

Accepted Answer

Azure Monitor Alerts. Azure Monitor Alerts is the correct service because it allows you to create metric-based alert rules that trigger when the CPU percentage of an Azure SQL Database exceeds a defined threshold (e.g., 80%). It continuously monitors performance metrics over time and sends notifications (e.g., email, SMS, or webhook) when the condition is met, fulfilling the requirement for both monitoring and alerting.

Answer

Automatic tuning

Answer

Query Performance Insight

Answer

SQL Assessment

Question 4

Your team uses GitHub Actions for CI/CD. You need to automatically deploy schema changes to an Azure SQL Database after a pull request merge. The solution must use a service principal with minimal permissions. Which authentication method should the workflow use?

Accepted Answer

Service principal with a client secret stored in GitHub Secrets.. It uses a service principal with a client secret stored in GitHub Secrets, which meets the requirement of using a service principal with minimal permissions. Using a service principal is the appropriate authentication method for an automated CI/CD deployment with Azure SQL Database. Option B is incorrect because a user-assigned managed identity is not a service principal; it is a different type of identity that does not satisfy the requirement for a service principal. Option C is incorrect because storing connection strings in environment variables is insecure and does not use a service principal. Option D is incorrect because SQL authentication does not involve a service principal and is less secure than Azure AD-based methods.

Answer

Azure CLI action using a user-assigned managed identity for the GitHub runner.

Answer

Environment variables containing the connection string.

Answer

SQL authentication using a user name and password stored in GitHub Secrets.

Question 5

Which TWO are valid methods for auditing Azure SQL Database activity? (Choose two.)

Accepted Answer

Azure Storage account. Azure SQL Database auditing can write audit logs directly to an Azure Storage account, which is a supported destination for storing audit records in blob format. Option D is correct because audit logs can also be sent to a Log Analytics workspace, enabling integration with Azure Monitor for advanced querying and alerting on audit data.

Answer

Azure Event Grid

Answer

Azure Monitor Metrics

Answer

On-premises file share

Question 6

Your company uses Azure SQL Database with active geo-replication. You need to ensure that in the event of a regional disaster, the failover to the secondary region occurs automatically with minimal downtime. The secondary region must be in a different Azure geography for compliance. What should you configure?

Accepted Answer

Create a failover group with automatic failover policy.. Failover groups with automatic failover policy enable automatic failover to a secondary region in the same or paired region, meeting the requirement for minimal downtime. Option B is incorrect because auto-failover groups are not a separate concept; failover groups already support automatic failover. Option C is incorrect because active geo-replication alone requires manual failover, not automatic. Option D is incorrect because 'always failover groups' is not a valid Azure SQL Database feature, and Microsoft Entra ID authentication is unrelated to failover.

Answer

Create an auto-failover group using Azure CLI and set cross-geography replication.

Answer

Configure active geo-replication and set auto-failover policy.

Answer

Configure an always failover group with Microsoft Entra ID authentication.

Question 7

You are configuring an Azure SQL Database elastic pool for a SaaS application. The pool will host 50 databases with varying workloads. You need to minimize cost while ensuring performance meets baseline requirements. Which tier and configuration should you choose?

Accepted Answer

DTU-based elastic pool with 200 DTUs. A DTU-based elastic pool offers a cost-effective solution for hosting 50 databases with varying workloads. DTU pools provide a shared resource model (eDTUs) that automatically balances capacity across databases, minimizing cost while meeting baseline performance requirements. Serverless tier (Option C) is not supported for elastic pools, making it invalid. Hyperscale (Option A) is designed for large, highly scalable databases and is overkill and costly. Provisioned vCore-based pool (Option B) with 2 vCores may be insufficient for 50 databases and is generally more expensive than DTU-based pools for variable workloads.

Answer

Hyperscale tier with 4 vCores

Answer

Provisioned tier with General Purpose and 2 vCores

Answer

Serverless tier with General Purpose and auto-pause enabled

Question 8

Which TWO configurations can help improve the performance of an Azure SQL Database experiencing high `WRITELOG` waits?

Accepted Answer

Use in-memory OLTP to reduce log writes.. High WRITELOG waits indicate that the transaction log is a bottleneck, often due to excessive log I/O. In-memory OLTP reduces log writes by logging only the delta changes for memory-optimized tables, rather than full row versions, which directly alleviates log pressure. This makes option B correct.

Answer

Enable Transparent Data Encryption (TDE).

Answer

Enable Query Store.

Answer

Increase the frequency of database backups.

Question 9

You are optimizing an Azure SQL Database that runs a heavy reporting workload. The database uses the Business Critical service tier. Which THREE configuration changes can improve query performance for reporting queries without significantly impacting OLTP operations?

Accepted Answer

Create nonclustered columnstore indexes on large reporting tables.. Creating nonclustered columnstore indexes (option B) improves aggregation and reporting query performance by using columnar storage and batch processing. Configuring a read-scale replica (option C) offloads reporting queries to a secondary replica, reducing contention on the primary for OLTP operations. Enabling result set caching (option E) caches query results in the Premium/Business Critical tiers, reducing repeated reads for static reporting data. Option A (increasing MAXDOP to 8) can lead to parallel query contention and negatively impact OLTP performance, especially on smaller instances. Option D (disabling automatic tuning) removes beneficial plan corrections and is not recommended for improving performance.

Answer

Increase MAXDOP for the database to 8.

Answer

Disable automatic tuning to prevent plan changes.

Question 10

You are a database administrator for a large e-commerce company. The company has an Azure SQL Database named 'OrdersDB' in the General Purpose service tier, with a 2 TB database. The database has a table named 'OrderDetails' that contains over 1 billion rows. The table is partitioned by date using a partition function that splits data by month. You notice that queries that filter on a specific date range are performing poorly, and the query plan shows a clustered index scan on the partition column. The table has a clustered columnstore index to support analytics. You need to improve query performance for date-range queries without affecting the analytics workload. What should you do?

Accepted Answer

Create a nonclustered index on the partition key column (OrderDate) and include other columns as needed.. Creating a nonclustered index on the partition key (OrderDate) allows SQL Server to perform an index seek for date-range queries, avoiding the full clustered columnstore index scan. This index does not interfere with the existing columnstore index, which remains optimal for analytics workloads. The nonclustered index can include additional columns as included columns to cover the query without touching the base table.

Answer

Create a new partition scheme with a different partition function.

Answer

Rebuild the clustered columnstore index with a different compression setting.

Answer

Change the clustered index to a rowstore B-tree index on the partition key.

Question 11

You are monitoring an Azure SQL Database and notice that the average CPU usage is 80% and the average data IO percentage is 70%. You need to identify the most likely cause of the high resource usage. What should you check first?

Accepted Answer

Use Query Store to identify top resource-consuming queries. High average CPU (80%) and data IO (70%) suggest that the database is under sustained load from inefficient or resource-intensive queries. Query Store captures query execution plans, runtime statistics, and resource consumption per query, making it the fastest way to pinpoint the top resource consumers. Checking Query Store first allows you to identify the specific queries driving CPU and IO, which is the most direct diagnostic step.

Answer

Check for long-running maintenance tasks

Answer

Check for connection pooling issues

Answer

Check for blocking and deadlocks

Question 12

Refer to the exhibit. You are reviewing an ARM template snippet for an Azure SQL Database backup long-term retention policy. The template is deployed successfully. Which statement is true about the resulting backup retention?

Accepted Answer

Weekly backups are retained for 4 weeks.. The ARM template snippet defines a backup long-term retention policy for Azure SQL Database. The `weeklyRetention` property is set to `P4W`, which in ISO 8601 duration format means a period of 4 weeks. Therefore, weekly backups are retained for exactly 4 weeks. Option D correctly reflects this configuration.

Answer

Monthly backups are retained for 24 months.

Answer

Weekly backups are retained for 8 weeks.

Answer

Yearly backups are retained for 1 year.

Question 13

You manage an Azure SQL Database that must run a maintenance task every Sunday at 2:00 AM UTC. The task must be resilient to failures and automatically retry if it fails. You need to configure this using Azure automation. What is the most appropriate solution?

Accepted Answer

Create an Azure Logic App with a recurrence trigger scheduled for Sunday at 2:00 AM UTC and configure a retry policy.. The most appropriate solution because Azure Logic Apps provides a recurrence trigger that can be scheduled to run at specific times (e.g., every Sunday at 2:00 AM UTC) and includes built-in retry policies to handle failures, ensuring resilience without custom code. Option B (Azure Automation Runbook) can be scheduled and can include custom error handling for retries, but it requires more manual implementation and is less integrated for simple retry logic. Option C (T-SQL Agent job) is not available in Azure SQL Database; Azure SQL Database does not support SQL Agent jobs natively. Option D (Azure Function with timer trigger) requires custom retry logic in code, which is more complex than using Logic Apps' built-in retry policy.

Answer

Create an Azure Automation Runbook and schedule it to run weekly. Add custom error handling for retries.

Answer

Use T-SQL Agent job in Azure SQL Database with a schedule and set up retry via Transact-SQL.

Answer

Create an Azure Function with a timer trigger and implement retry logic in code.

Question 14

You need to audit schema changes on an Azure SQL Database. Specifically, you must capture details of any DDL statements executed by any user. The audit logs must be stored in a Log Analytics workspace for analysis. What should you configure?

Accepted Answer

Configure server-level auditing with a Log Analytics workspace destination. Azure SQL Database server-level auditing with a Log Analytics workspace destination captures all DDL statements executed by any user and stores them in a Log Analytics workspace for analysis. This meets the requirement to audit schema changes and store logs in Log Analytics, as server-level auditing captures events for all databases on the server, including DDL operations, and supports Log Analytics as a destination.

Answer

Create DDL triggers that write to a table

Answer

Configure database-level auditing with blob storage destination

Answer

Create an extended events session to capture DDL events

Question 15

You are the database administrator for a SaaS company that uses Azure SQL Database. The company has a new requirement to audit all SELECT operations on a specific table containing sensitive customer data. You enable auditing on the server and configure a storage account for audit logs. However, after 24 hours, you notice that no SELECT operations are captured in the audit logs. You verify that the table is being accessed frequently. What is the most likely cause?

Accepted Answer

The audit policy is not configured to capture SELECT operations; only UPDATE, INSERT, and DELETE are captured. The default server-level audit policy in Azure SQL Database captures only data manipulation language (DML) operations like UPDATE, INSERT, and DELETE, not SELECT queries. To audit SELECT operations, you must explicitly configure a database-level audit action group such as DATABASE_OBJECT_ACCESS_GROUP or SCHEMA_OBJECT_ACCESS_GROUP, or use a custom audit action like SELECT on the specific table. Without this configuration, SELECT operations are not recorded in the audit logs, even if server auditing is enabled and the storage account is properly configured.

Answer

The storage account key was rotated and the audit configuration is using an expired key

Answer

The client application is using a connection string that bypasses the server firewall

Answer

The storage account is in a different region than the SQL Database server

Question 16

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. 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

Question 17

You have an Azure SQL Database that uses SQL Server Agent for automation tasks. You migrated the database to Azure SQL Managed Instance. After migration, some SQL Server Agent jobs fail because they reference a linked server that no longer exists. You need to automate the removal of all linked server references from the jobs. What should you do?

Accepted Answer

Create a T-SQL script that updates the job steps to remove linked server references, and execute it via Azure Automation runbook.. You can create a T-SQL script that updates the job steps to remove linked server references and execute it via Azure Automation runbook. This allows automated execution against the SQL Managed Instance. Option A is incorrect because elastic jobs are used for scheduling tasks across multiple databases, not for modifying SQL Agent jobs directly. Option C is incorrect because Azure CLI does not have native commands to manage SQL Agent jobs. Option D is incorrect because while Azure Automation can run PowerShell scripts, the T-SQL script approach is more direct and reliable for modifying job steps.

Answer

Use elastic jobs to remove the linked server references.

Answer

Use Azure CLI to remove the linked server references.

Answer

Use Azure Automation to run a PowerShell script that removes the linked server references.

Question 18

You have an Azure SQL Database with Intelligent Insights enabled. You receive an alert that 'SQLInsights: Resource utilization is consistently high'. You need to determine whether the issue is caused by an increase in user workload or a degradation in query performance. Which Intelligent Insights dimension should you review?

Accepted Answer

Impact. (Impact) is correct because the Impact dimension in Intelligent Insights indicates whether the high resource utilization is caused by an increase in user workload or a degradation in query performance. Option A (Metric) is incorrect because Metric shows the metric name (e.g., 'Resource utilization') but not the root cause. Option B (Severity) is incorrect because Severity indicates the alert level (e.g., high, medium) and does not provide insight into the cause. Option D (Resource type) is incorrect because Resource type shows the Azure resource type (e.g., SQL Database) and is not relevant to the cause of the alert.

Answer

Metric

Answer

Severity

Answer

Resource type

Question 19

You need to configure authentication for Azure SQL Database. Which TWO options are supported?

Accepted Answer

Azure Active Directory authentication. Azure SQL Database supports Azure Active Directory (Azure AD) authentication, which allows you to centrally manage identities and access. This includes support for modern authentication protocols like OAuth 2.0 and integration with Azure AD, enabling features such as conditional access and managed identities.

Answer

Multi-factor authentication

Answer

Certificate-based authentication

Answer

Windows authentication

Question 20

Refer to the exhibit. You are deploying an Azure SQL Database using this ARM template. After deployment, you need to automate the scaling of the database to a higher service tier when DTU consumption exceeds 80% for 5 minutes. Which Azure service should you use to trigger the scaling?

Accepted Answer

Azure Monitor metric alert. Azure Monitor metric alerts can be configured to trigger an Azure Automation runbook that scales the Azure SQL Database when DTU consumption exceeds 80% for 5 minutes. This is a common pattern for autoscaling based on metrics. Option A (Azure SQL Analytics) is a monitoring solution that does not trigger actions. Option B (Azure Automation Update Management) is for managing updates on virtual machines, not for scaling databases. Option C (SQL Server Agent) is not available in Azure SQL Database; it is used for on-premises SQL Server instances.

Answer

Azure SQL Analytics

Answer

Azure Automation Update Management

Answer

SQL Server Agent