DP-300 Practice Test 21 — 15 Questions

Question 1

Your company has an Azure SQL Managed Instance in the General Purpose tier. You need to configure a failover group for disaster recovery. The secondary managed instance must be in a different region and must also be used for read-only workloads. During a failover, you want to minimize data loss. Which configuration should you use?

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

Accepted Answer

Create a failover group and configure the secondary instance to allow read-only connections.. Failover groups for Azure SQL Managed Instance allow you to configure the secondary instance to allow read-only connections, enabling it to serve read-only workloads. This minimizes data loss by using automatic replication with a Recovery Point Objective (RPO) of less than 1 second. Option A (zone redundancy) provides high availability within a region, not disaster recovery across regions. Option C (log shipping) is not a built-in feature for managed instances and does not support automatic failover. Option D (active geo-replication) is not supported for managed instances; failover groups are the recommended solution.

Answer

Enable zone redundancy on the primary and secondary instances.

Answer

Configure log shipping between the primary and secondary instances.

Answer

Use active geo-replication instead of a failover group.

Question 2

Your organization has Azure SQL Database with several databases. You need to implement a solution that allows a junior DBA to view the security logs for failed logins but not modify any security settings. What is the minimum role assignment needed on the logical server?

Accepted Answer

Assign the SQL Security Manager role.. The SQL Security Manager role is the minimum built-in Azure RBAC role that grants read access to SQL security-related logs, including failed login audit logs, at the logical server scope. While it can also manage security policies, it is the least-privileged built-in role that satisfies the requirement to view the failed login security logs. The Reader role only provides read access to Azure resource metadata and does not expose SQL security logs.

Answer

Assign the Reader role.

Answer

Assign the Contributor role.

Answer

Assign the SQL DB Contributor role.

Question 3

Your company is migrating on-premises SQL Server databases to Azure SQL Managed Instance. You need to ensure that database backups are encrypted at rest using customer-managed keys stored in Azure Key Vault. You also need to allow the backup service to access the keys. What should you configure?

Accepted Answer

Enable Transparent Data Encryption (TDE) with customer-managed keys and grant the managed instance's system-assigned managed identity 'get', 'wrapKey', and 'unwrapKey' permissions on the key vault.. Transparent Data Encryption (TDE) with customer-managed keys (CMK) in Azure SQL Managed Instance encrypts database backups at rest. To allow the Azure backup service to access the key for backup encryption, the managed instance's system-assigned managed identity must be granted 'get', 'wrapKey', and 'unwrapKey' permissions on the Azure Key Vault where the CMK is stored. This ensures that backups are encrypted using the customer-controlled key, meeting the requirement for encryption at rest with customer-managed keys.

Answer

Use Always Encrypted with column master key stored in Azure Key Vault.

Answer

Configure Azure Backup for SQL Server in Azure VM and use Backup Center to manage encryption.

Answer

Configure server-level firewall rules to allow Azure services to access the server.

Question 4

Refer to the exhibit. An automatic tuning recommendation to force the last good plan is active. What should the database administrator do next?

Accepted Answer

Monitor the query performance to confirm the forced plan resolves the regression. When an automatic tuning recommendation to force the last good plan is active, the correct next step is to monitor the query performance to confirm that the forced plan resolves the regression. This is because plan forcing is a corrective action that may or may not improve performance; validation through monitoring ensures the change is beneficial before taking further steps like creating or dropping indexes.

Answer

Immediately implement the DROP_INDEX recommendation to reduce overhead

Answer

Create the recommended index to improve performance

Answer

Revert the plan force because it is causing regression

Question 5

You are a DBA for a company that uses Azure SQL Database for its customer relationship management (CRM) system. The database is currently in the Standard tier (DTU S2) and is experiencing performance degradation during end-of-month reporting. Reports that aggregate large amounts of data take over 30 minutes to run. You notice that the database's DTU usage averages 80% during these reports, with high IO. You need to improve report performance without significantly increasing cost. The reports are read-only and can tolerate some staleness. What should you do?

Accepted Answer

Increase the service tier to S3 during the end-of-month period. Increasing the service tier to S3 during the end-of-month period provides more DTUs and IO resources, which directly addresses the performance degradation without a permanent cost increase. The cost is only incurred during the reporting period, making it a cost-effective short-term solution. Option D is incorrect because read-only replicas are not supported in the Standard (DTU) S2 tier; they are only available in Premium, Business Critical, and Hyperscale tiers. Therefore, this option is not feasible. Options B and C may help but are not as effective or risk affecting write performance.

Answer

Add nonclustered indexes to the tables used in reports

Answer

Convert the tables to clustered columnstore indexes

Answer

Create a read-only replica and direct reports to it

Question 6

You are optimizing a data warehouse workload on Azure SQL Database. The workload involves large batch inserts and nightly aggregations. You notice that the transaction log is growing excessively during the batch inserts, causing performance degradation. You need to reduce log growth without affecting data consistency. What should you do?

Accepted Answer

Use bulk insert operations with TABLOCK hint to enable minimal logging.. Using minimally logged operations (e.g., bulk insert with TABLOCK) reduces log space for large imports under the full recovery model, but requires specific conditions. Option A is wrong because simple recovery model is not supported in Azure SQL Database (only FULL, BULK_LOGGED is not available). Option C is wrong because partitioning does not reduce log growth. Option D is wrong because increasing log size only accommodates growth, does not prevent it.

Answer

Change the database recovery model to Simple.

Answer

Create a partition function and scheme to spread the inserts.

Answer

Increase the maximum log size of the database.

Question 7

Your Azure SQL Database is configured with the Hyperscale service tier. You observe that log write latency is consistently high, affecting transaction throughput. What is the most likely cause and the recommended mitigation?

Accepted Answer

High log generation rate is causing log rate governance throttling; reduce the log generation rate by batching transactions.. In Hyperscale, log rate is governed to protect secondary replicas; high latency indicates throttling. Option A is wrong because Hyperscale uses local SSD for log, not PIOPS. Option B is wrong because log rate governance affects all log writes. Option D is wrong because scaling up compute doesn't increase log throughput limits.

Answer

The log IOPS is limited by the disk performance; increase the provisioned IOPS.

Answer

The compute replica is undersized; scale up the compute to increase log throughput.

Answer

The log write latency is due to network congestion; move the database to a different region.

Question 8

Which THREE of the following are required steps to configure a failover group for an Azure SQL Database with a readable secondary in a different region?

Accepted Answer

Add the primary database to the failover group.. Option B is correct because a failover group is defined over databases, so the primary database must be added to the failover group (via New-AzSqlDatabaseFailoverGroup or the portal) to be replicated and made failover-eligible. Option D is correct because you must first provision a secondary Azure SQL logical server in the target region, and it must use the same administrator login and password as the primary server so the contained database users and logins resolve after failover. Option E is correct because the failover group requires a read/write failover policy to be set to either Automatic or Manual, which determines whether Azure initiates failover automatically or only on manual trigger. Option A is not required because failover group configuration does not mandate a server-level firewall rule on the secondary server; firewall rules are managed separately and the secondary is typically accessed through the failover group listener. Option C is not required because the grace period is a configurable value (default 1 hour) that can be set to other durations, and it is not a mandatory step to create the failover group.

Answer

Create a server-level firewall rule on the secondary server to allow client IPs.

Answer

Configure a grace period of at least 1 hour.

Question 9

You are monitoring an Azure SQL Database using Query Performance Insight. You see a query with high duration and high CPU usage. The query plan shows a clustered index scan. What is the most likely cause and recommendation?

Accepted Answer

Missing nonclustered index; create an index on the predicates.. Query Performance Insight shows a query with high duration and CPU usage, and the query plan reveals a clustered index scan. A clustered index scan reads all rows in the table, which is inefficient when only a subset of rows is needed. The most likely cause is a missing nonclustered index on the columns used in the WHERE clause (predicates), which would allow a seek operation instead of a full scan, reducing both CPU and duration.

Answer

Fragmented clustered index; rebuild the clustered index.

Answer

Insufficient memory; increase the service tier.

Answer

Parameter sniffing; add OPTION (RECOMPILE).

Question 10

You manage an Azure SQL Database that runs a reporting workload. Users report that month-end reports are slow. You query sys.dm_db_resource_stats and observe that the average log write percentage is consistently high, but CPU and data IO are low. You need to reduce the impact of log write throughput on the workload. What should you do first?

Accepted Answer

Scale up the database to a higher service tier or compute size.. The sys.dm_db_resource_stats DMV shows resource usage as a percentage of the limit for the current service tier and compute size. A consistently high log write percentage with low CPU and data IO indicates the workload is constrained by the log write throughput limit. Scaling up to a higher service tier or compute size increases that limit, directly alleviating the bottleneck. Other actions like increasing max size or read scale-out do not affect log write throughput.

Answer

Enable read scale-out and redirect reporting queries to the secondary replica.

Answer

Change the database to the Hyperscale service tier.

Answer

Increase the database's max size.

Question 11

Which THREE actions can you take to monitor and optimize database resources in Azure SQL Database? (Choose three.)

Accepted Answer

Use Query Store to track query performance over time.. Options B, C, and D are correct. Query Store tracks query performance over time, dynamic management views help identify blocking and resource waits, and automatic tuning automatically implements performance improvements. Option A (Microsoft Defender for SQL) is a security feature for vulnerability detection, not directly for monitoring and optimizing database resources. Option E (Microsoft Sentinel) is a security information and event management (SIEM) tool, not specifically for database monitoring and optimization.

Answer

Enable Microsoft Defender for Azure SQL to detect vulnerabilities.

Answer

Configure Microsoft Sentinel to monitor database activity.

Question 12

Your Azure SQL Database is configured with the Hyperscale service tier. You observe increased redo log latency. Which resource is most likely the bottleneck?

Accepted Answer

Log service throughput. In Hyperscale, the log service is a separate component that handles redo; high latency there directly affects redo speed. Option A is wrong because compute node CPU affects query processing, not redo latency specifically. Option C is wrong because page servers handle data reads, not redo. Option D is wrong because storage IO is distributed and rarely the bottleneck for redo in Hyperscale.

Answer

Compute node CPU

Answer

Page server IO

Answer

Remote storage IOPS

Question 13

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.. Option A is correct because implementing TDE with customer-managed keys (BYOK) requires an Azure Key Vault (or Managed HSM) to hold the asymmetric RSA key that protects the database encryption key; the vault must be created and the key generated before it can be referenced. Option B is correct because the key must be assigned to the Azure SQL logical server (via the server's TDE protector, e.g., Set-AzSqlServerTransparentDataEncryptionProtector or the portal's Transparent data encryption blade) and TDE then enabled so the server uses that customer-managed key as the protector for its databases. Option C is not required because backup encryption in Azure SQL is automatic and tied to TDE; there is no separate 'Encrypted' backup encryption level setting to configure. Option D is not required because server certificates are a SQL Server on-premises/IaaS mechanism, not how Azure SQL Database BYOK is implemented. Option E is not required because column encryption keys belong to Always Encrypted, a different feature from TDE.

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 14

You execute the following query:

SELECT c.client_ip, c.application_name
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
WHERE s.action_id = 'LGIF' AND s.state = 'ABORT';

What does this query return?

Accepted Answer

Successful login events with client IP and application name.. The query as written is invalid. sys.dm_exec_sessions does not contain action_id or state columns; it has a status column with values such as sleeping, running, dormant, and preconnect. Therefore the query would raise an error or return no rows, not successful login events. None of the listed options accurately describes the result. sys.dm_exec_sessions and sys.dm_exec_connections only contain authenticated sessions, so they cannot return failed login data either.

Answer

All security audit events for the database.

Answer

Failed login events at the server level.

Answer

Failed login attempts to the database, including client IP and application name.

Question 15

You manage an Azure SQL Database that uses the General Purpose tier. You need to monitor the performance of the database and identify the top resource-consuming queries. You want to use a built-in feature that requires no additional cost. What should you use?

Accepted Answer

Query Store.. Query Store is a built-in, no-cost feature that automatically captures query execution history and helps identify top resource-consuming queries. Option B is wrong because Azure SQL Analytics (preview) is a monitoring solution that requires configuring diagnostic logs and Log Analytics, adding setup and potential cost, rather than being a simple built-in database feature. Option C is wrong because SQL Server Profiler is deprecated and not supported for Azure SQL Database. Option D is wrong because sys.dm_exec_query_stats and sys.dm_exec_sql_text are DMVs that require manual querying and do not provide the built-in, user-friendly query performance tracking of Query Store.

Answer

Azure SQL Analytics (preview) in Azure Monitor.

Answer

SQL Server Profiler.

Answer

sys.dm_exec_query_stats and sys.dm_exec_sql_text DMVs.