DP-300 Practice Test 28 — 15 Questions

Question 1

Which TWO actions are required to automate the export of an Azure SQL Database to a BACPAC file on a monthly basis? (Choose two.)

Accepted Answer

Use Azure Automation or a scheduled Azure Function to call the Export-AzSqlDatabase cmdlet.. Option B is correct because automating a monthly BACPAC export requires a scheduling/orchestration mechanism, and Azure Automation runbooks or a timer-triggered Azure Function can invoke the Az PowerShell cmdlet Export-AzSqlDatabase (or the equivalent az sql db export CLI command) on a recurring schedule. Option E is correct because a BACPAC export must be written to a destination, and the Export-AzSqlDatabase cmdlet requires a target Azure Storage account and container (specified via -StorageAccountName/-StorageContainerName or a storage key/URI) to hold the resulting .bacpac file. Option A is not required because long-term retention (LTR) applies to automated full/differential/log backup copies for point-in-time restore, not to BACPAC logical exports. Option C is not required because the export is a platform service performed by Azure SQL Database; no SQL Server on an Azure VM is needed. Option D is not required because SSIS is an ETL tool and plays no role in generating a BACPAC export.

Answer

Configure long-term retention (LTR) policy for the database.

Answer

Deploy a SQL Server on Azure VM to run the export command.

Answer

Install SQL Server Integration Services (SSIS) on a virtual machine.

Question 2

You are optimizing an Azure SQL Database that runs a heavy reporting workload. The database uses the General Purpose tier. You notice that many queries are scanning large tables. What is the best first action to improve performance?

Accepted Answer

Analyze the missing index recommendations from Query Store.. Query Store's missing index recommendations directly identify indexes that, if created, would likely improve the performance of the observed scanning queries. This is the most targeted, low-risk first action because it is based on actual workload data and addresses the root cause (missing indexes causing scans) without changing the service tier or making structural changes. It is also the cheapest and fastest to implement.

Answer

Partition the large tables by date.

Answer

Scale up to Business Critical tier.

Answer

Implement columnstore indexes on all large tables.

Question 3

You have an Azure SQL Managed Instance that is experiencing performance degradation. You suspect a query is causing excessive blocking. You need to identify the blocking chain and the resource holding the lock. Which DMV should you query?

Accepted Answer

sys.dm_tran_locks and sys.dm_os_waiting_tasks. To identify the blocking chain and the specific resource holding the lock, you need to combine lock metadata with wait information. sys.dm_tran_locks shows current locks and their resource types (e.g., RID, KEY, PAGE, OBJECT), while sys.dm_os_waiting_tasks reveals which sessions are waiting on those locks and the blocking session ID. Together, these DMVs allow you to trace the blocking chain from the blocked session back to the blocker and pinpoint the exact resource causing contention.

Answer

sys.dm_exec_requests

Answer

sys.dm_exec_query_stats

Answer

sys.dm_tran_active_snapshot_database_transactions

Question 4

You need to audit all schema changes in an Azure SQL Database and store the audit logs in a storage account for long-term retention. What should you enable?

Accepted Answer

Azure SQL Auditing with storage account destination.. Azure SQL Auditing with a storage account destination is the correct choice because it tracks database events, including schema changes (DDL operations), and writes audit logs to Azure Blob Storage for long-term retention. This meets the requirement to audit all schema changes and store logs durably, as storage accounts provide configurable retention policies.

Answer

Advanced Threat Protection with email alerts.

Answer

Query Store with 'Data Flush Interval' set to 1 minute.

Answer

SQL Vulnerability Assessment with recurring scans.

Question 5

You are configuring authentication for Azure SQL Database. Which TWO of the following are supported authentication methods?

Accepted Answer

Microsoft Entra ID authentication with a service principal.. Option B is correct because Azure SQL Database natively integrates with Microsoft Entra ID (formerly Azure AD), and service principals (app registrations) can be granted access and authenticate via Entra ID tokens, which is a fully supported authentication method. Option D is correct because SQL authentication using a login name and password is a core, supported authentication method for Azure SQL Database (created via CREATE LOGIN or the portal). Option A is not supported because Azure SQL Database does not use Windows/Kerberos authentication; Kerberos-based Windows authentication applies to on-premises SQL Server or Azure SQL Managed Instance with AD integration, not Azure SQL Database. Option C is not a distinct supported method because OAuth 2.0 tokens are the underlying mechanism used by Entra ID authentication, not a separately configurable authentication method for SQL logins. Option E is not supported because Azure SQL Database does not support certificate-based authentication for SQL logins; certificate authentication applies to SQL Server on-premises or Azure SQL Managed Instance.

Answer

Windows authentication using Kerberos.

Answer

OAuth 2.0 token authentication.

Answer

Certificate-based authentication for SQL logins.

Question 6

You are monitoring an Azure SQL Database using the sys.dm_db_resource_stats DMV. The avg_log_write_percent column shows 95% for the last hour. What does this indicate, and what should you do?

Accepted Answer

The database is nearing its log write IOPS limit; consider scaling up or optimizing log writes.. The avg_log_write_percent metric in sys.dm_db_resource_stats measures the percentage of the log write IOPS limit used. At 95%, the database is nearing its log write IOPS limit, which can cause transaction delays and throttling. The appropriate response is to scale up the service tier (e.g., to a higher DTU or vCore level) or optimize log writes to reduce IOPS consumption. Option A is incorrect because the metric does not indicate running out of log space; log space is separate. Option B is incorrect because storage scale (size) does not directly affect log write IOPS. Option D is incorrect because this metric is specifically about log I/O, not CPU.

Answer

The database is out of transaction log space; increase the max log size.

Answer

The database storage is running out; scale up storage.

Answer

The CPU is overloaded; scale up CPU.

Question 7

You are configuring Azure SQL Database firewall rules. You need to allow a range of IP addresses (192.168.1.0 to 192.168.1.255) to connect to the database. Which firewall rule should you create?

Accepted Answer

Start IP: 192.168.1.0, End IP: 192.168.1.255. Azure SQL Database firewall rules require a contiguous range of IP addresses defined by a start and end IP. The range 192.168.1.0 to 192.168.1.255 exactly covers the specified /24 subnet, allowing all hosts in that block to connect. This is the standard method for permitting a subnet in Azure SQL firewall configuration.

Answer

Start IP: 192.168.0.0, End IP: 192.168.2.255

Answer

Start IP: 192.168.1.255, End IP: 192.168.1.0

Answer

Start IP: 192.168.1.0, End IP: 192.168.1.0

Question 8

You manage an Azure SQL Managed Instance that hosts a business-critical database. The compliance team requires that the instance be recoverable in a different Azure region if the primary region becomes unavailable, and that the failover be initiated manually only by authorized administrators. You need to configure a disaster recovery solution. Which two actions should you perform? (Choose two.)

Accepted Answer

Configure the failover policy of the failover group to Manual.. Auto-failover groups are the supported cross-region disaster recovery feature for Azure SQL Managed Instance, and they provide a listener endpoint. To meet the compliance requirement that failover be manually initiated, the failover policy must be set to Manual. Zone redundancy is intra-region, active geo-replication is not available for managed instances, and Automatic policy would remove the required human control.

Answer

Configure active geo-replication between the two managed instances.

Answer

Enable zone redundancy on the primary managed instance.

Answer

Set the failover group policy to Automatic.

Question 9

You administer an Azure SQL Database named SalesDB that uses the Business Critical service tier. The database is deployed in the West Europe region, which supports availability zones. The application requires that the database remains available even if an entire availability zone fails. You need to configure the database to meet this requirement with minimal administrative effort. What should you do?

Accepted Answer

Enable zone redundancy on the database.. Zone redundancy in the Business Critical service tier spreads replicas across availability zones, ensuring automatic failover if a zone becomes unavailable. It is a simple configuration toggle that requires no application changes, providing high availability at the zone level. Other options address regional disasters or involve migration, which are not minimal-effort solutions for zone-level resilience.

Answer

Configure active geo-replication to a secondary region.

Answer

Create a failover group with an auto-failover policy.

Answer

Deploy the database to a SQL Managed Instance with zone redundancy.

Question 10

Which TWO are benefits of using a failover group for Azure SQL Database? (Select two.)

Accepted Answer

Allows the secondary database to be readable for reporting. Option A is correct because a failover group allows you to configure the secondary database as readable, enabling read-only workloads such as reporting to be offloaded to the secondary replica. Option C is correct because a failover group provides a read/write listener endpoint (e.g., <failover-group>.database.windows.net) that automatically redirects connections to the current primary after a failover, giving applications a stable connection string. Option B is not a benefit specific to failover groups; TDE is a database-level encryption feature that can be enabled independently and is not provided or extended across regions by a failover group. Option D is incorrect because failover groups do not automatically load-balance read queries between primary and secondary; read-only routing must be configured and is not automatic balancing. Option E is incorrect because Azure SQL Database failover groups use asynchronous replication, not synchronous replication, between the primary and secondary servers.

Answer

Enables transparent data encryption (TDE) across regions

Answer

Automatically balances read queries between primary and secondary

Answer

Supports synchronous replication between primary and secondary

Question 11

You have an Azure SQL Managed Instance named MI1 in the West Europe region. The instance hosts a mission-critical database that must be recoverable within 30 minutes in the event of a regional outage. You need to implement a disaster recovery solution that minimizes data loss and administrative effort. The solution must support read-only access to the secondary during normal operations. What should you configure?

Accepted Answer

Create an auto-failover group with MI1 as the primary and a secondary managed instance in North Europe.. Auto-failover groups for Azure SQL Managed Instance are the correct choice because they provide automatic failover, a read-only listener for the secondary, and low RPO/RTO. They are specifically designed for cross-region disaster recovery for managed instances. Geo-replication is not available for managed instances, log shipping is not native, and omitting the read-only listener fails the read-only access requirement.

Answer

Configure geo-replication for the database to a secondary managed instance in North Europe.

Answer

Set up a failover group with a secondary managed instance, but do not enable the read-only listener.

Answer

Use log shipping to a secondary managed instance in North Europe.

Question 12

You have an Azure SQL Database named DB1 that uses the Hyperscale service tier. The database has a primary replica and multiple named replicas. You need to ensure that read-only workloads are offloaded to a named replica and that the named replica remains available if the primary replica fails. What should you do?

Accepted Answer

Configure the application to connect to the named replica using its connection string and set ApplicationIntent=ReadOnly.. Named replicas in Azure SQL Database Hyperscale are independent read-only replicas that must be accessed via their own connection string. They continue to serve read workloads even if the primary fails, providing high availability for read operations. Using the primary connection string with ApplicationIntent=ReadOnly does not target named replicas; it targets the built-in read-only replicas. Geo-secondaries are for disaster recovery and not the same as named replicas.

Answer

Create a geo-secondary in another region and redirect read-only connections to it.

Answer

Enable read-scale out on the primary database and use the read-only listener.

Answer

Use the primary replica's connection string with ApplicationIntent=ReadOnly to route to a named replica.

Question 13

You have an Azure SQL Database that uses the General Purpose service tier. The database is 200 GB and is used by a web application. The application experiences occasional unplanned failovers due to hardware failures in the primary region. You need to ensure that the database remains available during a single datacenter failure within the region without any application changes. What should you do?

Accepted Answer

Enable zone redundancy for the database.. Zone redundancy is the feature that provides high availability within a single region by spreading replicas across availability zones. It is available for both General Purpose and Business Critical service tiers. Enabling zone redundancy on the existing General Purpose database meets the requirement of surviving a datacenter failure without application changes and is cost-effective. Auto-failover groups and active geo-replication are for cross-region disaster recovery, and Business Critical is an unnecessary upgrade.

Answer

Change the service tier to Business Critical.

Answer

Configure active geo-replication with a secondary database in the same region.

Answer

Configure an auto-failover group with a secondary database in a different region.

Question 14

You are a database administrator for a company that runs a SaaS application on Azure SQL Database. The application's workload is unpredictable, with rapid bursts of activity that last only a few minutes. You need to ensure that the database can handle these bursts without manual intervention, while minimizing cost during idle periods. What should you do?

Accepted Answer

Enable the serverless compute tier for the database.. The serverless compute tier is specifically designed for single databases with unpredictable or intermittent usage. It automatically scales compute based on demand and pauses during inactivity, which directly addresses the need to handle bursts without manual intervention while minimizing cost. Other options either require manual scaling or are optimized for different scenarios such as large databases or read offloading.

Answer

Configure the database to use the Hyperscale service tier with a fixed number of vCores.

Answer

Create a read scale-out replica and route all write operations to the primary replica.

Answer

Implement elastic database pool with multiple databases sharing resources.

Question 15

You are responsible for performance tuning of an Azure SQL Database that hosts a customer relationship management (CRM) application. The database has several tables with millions of rows. Users report that a report query that joins four tables is slow. You examine the query execution plan and notice that the database engine is using an Index Spool (Lazy Spool) operator. Which TWO actions should you take to improve query performance? (Choose two.)

Accepted Answer

Create appropriate indexes on the columns used in joins and filters.. Option C is correct because an Index Spool (Lazy Spool) operator typically appears when the optimizer lacks a suitable index and must build a temporary spool to satisfy repeated lookups or joins; creating appropriate indexes on the join and filter columns gives the optimizer a permanent access path, eliminating the need for the spool. Option E is correct because stale or missing statistics cause the optimizer to misestimate cardinality, which frequently leads to spool operators; updating statistics on all involved tables provides accurate row-count estimates so the optimizer can choose a more efficient plan. Option A is not appropriate because MAXDOP 1 disables parallelism and does not address the root cause of a spool operator, and it can actually hurt performance on large reporting queries. Option B is not the right fix because adding DTUs or vCores increases resources but does not change the plan shape or remove the spool, so the underlying inefficiency remains. Option D is not recommended because forcing join order with table hints is fragile, overrides the optimizer's cost-based decisions, and does not resolve the missing index or statistics problem that caused the spool.

Answer

Disable parallelism for the query using the MAXDOP 1 hint.

Answer

Increase the DTU or vCore count of the database.

Answer

Rewrite the query using table hints to force a specific join order.