DP-300 Practice Exam 54 — 20 Questions

Question 1

Refer to the exhibit. You notice the database db1 is currently 80% full on storage, and the service objective is S3. Which action would best prevent storage full errors while minimizing cost?

Accepted Answer

Increase the maximum database size to 250 GB.. The exhibit shows the database is at 80% of its current maximum size. The S3 service objective allows a maximum database size of up to 250 GB. By increasing the maximum size to 250 GB, you provide sufficient storage headroom without changing the service tier, which would incur higher costs. Option A (automatic storage growth) is not a user-configurable feature in Azure SQL Database. Options B and D involve scaling to a higher service tier (S4 or higher), which is unnecessary and more expensive.

Answer

Enable automatic storage growth on the database.

Answer

Change the service objective to S4.

Answer

Scale the database to a higher service tier like Standard S4.

Question 2

Which THREE features can help protect Azure SQL Database from data exfiltration?

Accepted Answer

Microsoft Defender for SQL. Microsoft Defender for SQL detects anomalous activities that may indicate data exfiltration attempts. Azure Policy with deny effect for public network access blocks external connectivity, preventing unauthorized data transfer. Always Encrypted ensures that sensitive data remains encrypted even when accessed by unauthorized users, so if data is exfiltrated, it is unusable without the encryption keys. Together, these three features protect Azure SQL Database from data exfiltration.

Answer

Azure SQL Database Ledger

Answer

Dynamic Data Masking

Question 3

You need to automate the deployment of schema changes to multiple Azure SQL Databases in different regions. The solution must support rollback and version control. Which technology should you use?

Accepted Answer

Use Azure DevOps with a database project and release pipelines.. Azure DevOps with a database project and release pipelines is the correct choice because it provides source control for schema changes, automated deployment across multiple environments, and built-in rollback capabilities through pipeline versioning and deployment history. This approach aligns with infrastructure-as-code principles, enabling consistent, repeatable, and auditable schema deployments to Azure SQL Databases in different regions.

Answer

Use Azure Data Factory to run stored procedures for schema changes.

Answer

Use SQL Server Agent jobs to run deployment scripts on schedule.

Answer

Use Azure Automation with PowerShell scripts to execute T-SQL scripts.

Question 4

Your company is deploying Azure SQL Database for a new application. The security policy requires that all queries be encrypted in transit and that the database be protected against accidental deletion. Which THREE configurations should you implement? (Select THREE.)

Accepted Answer

Set the minimum TLS version to 1.2. Setting the minimum TLS version to 1.2 ensures all connections are encrypted in transit. Enabling Microsoft Entra ID authentication provides secure identity-based access, a fundamental security practice even if not explicitly stated. A resource lock on the logical server prevents accidental deletion of the database and server.

Answer

Configure firewall rules to allow only specific IP addresses

Answer

Enable Transparent Data Encryption (TDE)

Question 5

You manage an Azure SQL Managed Instance with a large database. You notice that the automatic tuning recommendations are not being applied. You need to ensure that automatic tuning is enabled for the instance. Which PowerShell cmdlet should you run?

Accepted Answer

Set-AzSqlInstanceDatabase with -AutoTuningProperties. The correct cmdlet is Set-AzSqlInstanceDatabase because Azure SQL Managed Instance uses instance-level databases, and automatic tuning properties are configured at the database level within the instance. The -AutoTuningProperties parameter directly enables or configures automatic tuning recommendations for the specified database in the managed instance.

Answer

Set-AzSqlDatabase with -AutoTuningProperties

Answer

Set-AzSqlInstance with -DtcEnabled

Answer

Set-AzSqlServer with -AdministratorLogin

Question 6

Which TWO actions are required to implement transparent data encryption (TDE) with customer-managed keys for an Azure SQL Database?

Accepted Answer

Create an Azure Key Vault and a key.. TDE with customer-managed keys requires an Azure Key Vault to store the key, which provides centralized key management and control. The key in the vault is used to protect the database encryption key (DEK), ensuring that the customer retains ownership and control over the encryption material.

Answer

Set the backup encryption level to 'Encrypted'.

Answer

Create a server certificate in the database.

Answer

Configure column encryption keys in the database.

Question 7

Your company uses Azure SQL Database with active geo-replication configured between two regions. The primary database in East US experiences a critical failure, and you initiate a manual failover to the secondary in West US. After the failover, you need to re-establish geo-replication to a new secondary database in a third region (Central US) to restore the DR capability. What is the correct sequence of actions?

Accepted Answer

Remove the geo-replication link to the old primary, then add a new secondary in Central US.. After a manual failover, the former secondary becomes the new primary. The old primary, if still available, becomes a secondary. To restore DR capability with a new secondary in Central US, you must first remove the existing geo-replication link between the current primary and the old primary (the orphaned secondary). Without removing this link, you cannot add another secondary. Once the link is removed, you can create a new geo-secondary in Central US from the current primary. Therefore, option D is correct. Option A is incorrect because deleting the old primary is unnecessary and could be destructive. Option B is incorrect because a new secondary cannot be added while a geo-replication link already exists to the old primary. Option C is incorrect because performing a planned failover back to the old primary would cause unnecessary downtime and does not facilitate adding a secondary in Central US.

Answer

Delete the old primary and create a new geo-secondary in Central US.

Answer

Add a new secondary in Central US on the current primary.

Answer

Perform a planned failover to the old primary, then configure geo-replication to Central US.

Question 8

You are designing an automated backup retention policy for an Azure SQL Database. The business requirement is to retain daily backups for 30 days, weekly backups for 12 weeks, monthly backups for 12 months, and yearly backups for 7 years. Which backup retention type should you configure?

Accepted Answer

Long-term retention (LTR) policy. Long-term retention (LTR) policy is specifically designed to retain backups beyond the point-in-time restore (PITR) window, supporting daily, weekly, monthly, and yearly retention periods. Option C is correct. Option A (PITR retention) is too short and cannot retain backups for years. Option B (Backup vault with Azure Backup) is used for Azure VM backups and not for Azure SQL Database automated backups. Option D (Automated backup policy) refers to the default backup settings but does not include long-term retention.

Answer

Point-in-time restore (PITR) retention

Answer

Backup vault with Azure Backup

Answer

Automated backup policy

Question 9

You are designing an automation strategy for an Azure SQL Database that requires the following: 1) Automatically scale up the service tier when CPU usage exceeds 90% for 5 minutes. 2) Automatically scale down when CPU usage drops below 10% for 15 minutes. 3) The solution must be cost-effective and use built-in Azure features. Which TWO options should you combine? (Choose two.)

Accepted Answer

Azure Logic Apps with a metric trigger. Azure Logic Apps can trigger scaling actions based on metric thresholds using a metric trigger, and Azure Automation runbooks can execute the scaling commands (such as changing the service tier) via PowerShell or Azure CLI. Together, they provide a cost-effective, built-in solution. Option A (Elastic Database Jobs) is used for scheduled database tasks like index maintenance, not scaling. Option B (Azure Monitor autoscale) is not directly available for Azure SQL Database; autoscale is only for Azure Virtual Machine scale sets, App Service, etc. Option C (Azure Functions) could also be used, but the recommended combination for this scenario is Logic Apps and Automation runbooks because they are serverless and integrate natively with Azure SQL Database.

Answer

Elastic Database Jobs

Answer

Azure Monitor autoscale

Answer

Azure Functions

Question 10

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?

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 11

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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.