DP-300 Practice Test 8 — 10 Questions

Question 1

You manage an Azure SQL Database that uses a Serverless compute tier. You notice that during idle periods, the database auto-pauses and then auto-resumes when a connection is made. However, users report that the first query after a pause is slow. You need to improve the performance of the first query. What should you do?

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

Accepted Answer

Disable auto-pause for the serverless database. The slow first query after auto-resume is caused by the cold-start latency of the serverless compute tier, which includes provisioning resources and warming the buffer pool. Disabling auto-pause ensures the database remains online and the buffer pool stays populated, eliminating the cold-start delay for the first query.

Answer

Increase the maximum vCores

Answer

Create a SQL Agent job to ping the database every hour

Answer

Enable Query Store

Question 2

You are deploying an Azure SQL Database using PowerShell as shown in the exhibit. The database will be used by a development team that works intermittently. You need to ensure the database is cost-effective while being available on demand. What is the purpose of the AutoPauseDelayInMinutes parameter?

Accepted Answer

It controls the automatic pausing of the database after a period of inactivity to save costs.. The AutoPauseDelayInMinutes parameter is used with Azure SQL Database serverless compute tier. It specifies the number of minutes of inactivity (no CPU usage or active sessions) after which the database automatically pauses, stopping compute billing while storage remains billed. This makes the database cost-effective for intermittent development workloads because it eliminates compute costs during idle periods and automatically resumes on the first connection.

Answer

It configures the database to pause during a disaster recovery scenario.

Answer

It sets the maximum duration for which the database can be paused.

Answer

It determines how long the database takes to resume after a pause.

Question 3

You are analyzing query performance in an Azure SQL Database. The query in the exhibit returns a list of queries ordered by total_logical_reads. What does high total_logical_reads typically indicate?

Accepted Answer

The query is reading many pages from the buffer pool, possibly due to missing indexes. High total_logical_reads typically indicates that the query is reading many pages from the buffer pool, which often points to missing or inefficient indexes. Option D is correct. Option A is incorrect because high logical reads relate to buffer pool access, not necessarily I/O latency (which is indicated by high physical reads). Option B is incorrect because CPU time is measured by worker_time, not logical reads. Option C is incorrect because while logical reads can increase memory usage, the primary indicator of memory usage is the memory grant, not logical reads.

Answer

The query is experiencing I/O latency

Answer

The query is using a lot of CPU time

Answer

The query is using a lot of memory

Question 4

You are designing an automated backup strategy for Azure SQL Managed Instance. The solution must ensure point-in-time restore (PITR) within 2 hours for the last 7 days and long-term retention (LTR) for 5 years. Which configuration should you use?

Accepted Answer

Set PITR retention to 7 days (default) and configure LTR backup policy with yearly backups for 5 years.. Azure SQL Managed Instance supports point-in-time restore (PITR) with a configurable retention period from 1 to 35 days; the default is 7 days, which satisfies the requirement to restore within 2 hours for the last 7 days (the 2-hour target is a recovery point objective, not retention). For long-term retention (LTR) of 5 years, Managed Instance supports LTR backup policies with yearly backups that can retain backups for up to 10 years. Therefore, setting PITR retention to 7 days (default) and configuring an LTR policy with yearly backups for 5 years meets both requirements. Option A is wrong because Azure Backup for SQL Server in Azure VM is for SQL Server on Azure VMs, not for Managed Instance. Option B is wrong because "geo-redundant backup storage for LTR" is a storage redundancy option, not an LTR retention policy; LTR requires explicit backup frequency and retention configuration. Option C is wrong because PITR retention cannot be set to 2 hours (minimum is 1 day) and custom backup jobs using Elastic Database Jobs are unnecessary; Managed Instance automates backups natively.

Answer

Use Azure Backup for SQL Server in Azure VM to back up the managed instance.

Answer

Set PITR retention to 7 days and use geo-redundant backup storage for LTR.

Answer

Set PITR retention to 2 hours and configure a custom backup job using Elastic Database Jobs.

Question 5

You are a database administrator for a company that uses Azure SQL Database. You need to configure a diagnostic setting to send database metrics to a Log Analytics workspace for long-term analysis. The solution should be cost-effective and include metrics like CPU percentage, data IO, and log IO. What should you do?

Accepted Answer

In the Azure portal, add a diagnostic setting for the database to stream 'AllMetrics' to a Log Analytics workspace.. Azure SQL Database diagnostic settings allow streaming platform metrics (CPU percentage, data IO, log IO) directly to a Log Analytics workspace by selecting the 'AllMetrics' category. This is the native, cost-effective mechanism for long-term metric retention and analysis without custom code. It requires no T-SQL jobs or preview features and integrates directly with Azure Monitor.

Answer

Enable Azure SQL Insights (preview) for the database.

Answer

Enable Query Store and configure it to export to Log Analytics.

Answer

Create a T-SQL job that periodically inserts sys.dm_db_resource_stats into a table in Log Analytics.

Question 6

Which TWO are valid methods to connect to an Azure SQL Database without exposing a public endpoint?

Accepted Answer

Use a service endpoint. A service endpoint extends your virtual network private address space and the identity of your VNet to Azure SQL Database over a direct connection on the Azure backbone network. This allows you to secure your logical SQL server to accept traffic only from a specific subnet, eliminating the need for a public endpoint while still using the public endpoint's DNS name internally.

Answer

Use Always Encrypted

Answer

Use a public endpoint with a firewall rule

Answer

Use a site-to-site VPN gateway and connect to public endpoint

Question 7

You are troubleshooting a failover group for Azure SQL Database. The automatic failover is not triggering as expected during a regional outage. You verify that the grace period for data loss is set to 3600 seconds. The outage lasts 30 minutes. What is the most likely reason the automatic failover did not occur?

Accepted Answer

The outage duration is less than the grace period for data loss.. The grace period for data loss (also called the 'grace period' or 'data loss grace period') is the maximum time the failover group waits before forcing a failover when the primary region is unavailable. If the outage duration is shorter than this grace period, Azure SQL Database will not trigger automatic failover because it still hopes to recover the primary without data loss. Here, the grace period is 3600 seconds (60 minutes) and the outage lasted only 30 minutes, so the condition for automatic failover was not met. Thus, the most likely reason is that the outage duration is less than the grace period.

Answer

The failover policy is set to manual.

Answer

The grace period for data loss is too short.

Answer

The secondary region is also experiencing an outage.

Question 8

You are designing a secure environment for Azure SQL Managed Instance. The company requires that all database backups be encrypted using customer-managed keys stored in Azure Key Vault. Which combination of actions should you take?

Accepted Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault.. Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault allows you to encrypt the database backup files using a key that you control. When TDE is enabled and configured with a customer-managed key (CMK) stored in Azure Key Vault, Azure SQL Managed Instance automatically encrypts backups with the same TDE protector key, meeting the requirement for customer-managed backup encryption.

Answer

Configure Always Encrypted with keys stored in Key Vault.

Answer

Use Azure Storage Service Encryption to encrypt the backup files.

Answer

Enable backup encryption using a certificate stored in the managed instance.

Question 9

You have an Azure SQL Database in the General Purpose service tier. The database must be available during a planned patching event that updates the underlying infrastructure. What high availability feature is provided by default?

Accepted Answer

Built-in high availability with a standby replica. Azure SQL Database in the General Purpose service tier includes built-in high availability with a standby replica, which is provisioned automatically and used to fail over during planned maintenance or unplanned outages. This built-in HA ensures the database remains available during patching events without requiring the customer to configure geo-replication or zone redundancy. The standby replica is maintained synchronously and failover is automatic.

Answer

Active geo-replication

Answer

Zone-redundant availability

Answer

Business Critical service tier

Question 10

You are reviewing an ARM template snippet that configures a long-term retention (LTR) policy for an Azure SQL Database. Based on the exhibit, how long will weekly backups be retained?

Accepted Answer

4 weeks.. In the ARM template snippet, the weeklyRetention property is set to 'P4W' (ISO 8601 duration format), which means 4 weeks. Therefore, weekly backups are retained for exactly 4 weeks.

Answer

4 days.

Answer

4 months.

Answer

4 years.