DP-300 Practice Test 40 — 15 Questions

Question 1

You are a database administrator for an e-learning platform that uses Azure SQL Database with the General Purpose tier. The database stores course content and user progress. You need to automate the process of exporting the database schema (without data) to a BACPAC file every week for version control. The BACPAC file must be stored in an Azure Blob Storage container with a retention policy of 30 days. The automation must use Azure native services and should not require manual intervention. What should you do?

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

Accepted Answer

Create an Azure Automation runbook with PowerShell that uses SqlPackage.exe with /p:ExtractAllTableData=False to export the schema-only BACPAC to a Blob Storage container. Schedule the runbook to run weekly.. Azure Automation runbooks can execute PowerShell scripts that use the SqlPackage.exe utility to export a schema-only BACPAC file. The command 'SqlPackage.exe /Action:Export /SourceServerName: /SourceDatabaseName: /TargetFile: /p:ExtractAllTableData=False' creates a BACPAC containing only the schema. The runbook can be scheduled weekly using an Azure Automation schedule. Option A is incorrect because Azure Data Factory's Copy activity is designed for data movement, not schema exports. Option B is incorrect because Elastic Database Jobs runs T-SQL scripts and cannot produce BACPAC files. Option D is incorrect because SQL Agent is not available in Azure SQL Database.

Answer

Use Azure Data Factory with a Copy activity to export the database schema to a BACPAC file in Blob Storage.

Answer

Use Elastic Database Jobs to run a T-SQL script that uses the BACKUP DATABASE command to a URL.

Answer

Create a SQL Agent job on the database to run SqlPackage.exe.

Question 2

You are reviewing a deployment template for an Azure SQL Database. The above snippet configures a security alert policy. What is a potential issue with this configuration?

Accepted Answer

The retentionDays is set to 0, which may cause logs to be deleted immediately. RetentionDays set to 0 disables retention, meaning audit logs will not be retained in storage. Option A is wrong because state Enabled is fine. Option B is wrong because emailAddresses is provided. Option D is wrong because emailAccountAdmins true is fine.

Answer

The state is not set to "Disabled"

Answer

The emailAccountAdmins property is set to true

Answer

The emailAddresses array is missing an entry

Question 3

Refer to the exhibit. A backup administrator checks the protection status of a VM and receives an error. What is the most likely cause?

Accepted Answer

The VM has been deleted from Azure.. The error code 'UserErrorVMNotFound' and message indicate that the VM 'myVM' has been deleted. The backup item exists but the underlying VM is gone, so backups can no longer be performed.

Answer

The backup vault is in a different region than the VM.

Answer

The backup policy has been deleted, causing the VM to be unprotected.

Answer

The VM has been moved to a different resource group.

Question 4

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 5

You are managing an Azure SQL Database that is used by a real-time analytics application. The database uses the Hyperscale service tier. You notice that the transaction log rate is consistently high, causing performance degradation. You need to reduce the log generation rate without compromising data durability. What should you do?

Accepted Answer

Increase the service tier to Business Critical.. Increasing the service tier to Business Critical does not reduce the transaction log generation rate; it only improves log write throughput. The log generation rate is workload‑dependent and is not changed by moving to a higher tier. None of the provided options achieve the goal of reducing the log generation rate while maintaining durability. To reduce the rate, you would need to optimize the application (e.g., batching, reducing transactions), which is not offered among the choices.

Answer

Enable compression on transaction log backups.

Answer

Create additional nonclustered indexes on frequently updated tables.

Answer

Increase the backup retention period.

Question 6

You manage an Azure SQL Database (General Purpose, S2) used by a reporting application. The database has a table `FactSales` with 500 million rows. Queries that aggregate sales by date are slow. The execution plan shows a clustered index scan on `FactSales`. The table has a clustered index on `SaleID` and a nonclustered index on `DateKey`. The queries filter by `DateKey` and `ProductKey`. You need to improve query performance without changing the service tier. Which action should you take?

Accepted Answer

Create a covering index on DateKey and ProductKey including the aggregated columns.. The query filters by DateKey and ProductKey and aggregates sales data. A covering index on DateKey and ProductKey that includes the aggregated columns (e.g., SUM(SalesAmount)) allows the query to be satisfied entirely from the index without touching the clustered index, eliminating the costly clustered index scan. This is the most direct and effective way to improve performance without changing the service tier.

Answer

Create a nonclustered index on ProductKey only.

Answer

Partition the table by DateKey.

Answer

Create a clustered columnstore index on the table.

Question 7

You are a database architect for a multinational corporation that uses Azure SQL Database for a customer relationship management (CRM) system. The primary database is in the East US region using the Business Critical service tier. The compliance team requires that in the event of a regional disaster, the database can be failed over to a secondary region with zero data loss and an RTO of 30 seconds. Additionally, the secondary region must be able to handle read-only queries during normal operations to reduce load on the primary. You need to design a solution that meets these requirements with the lowest possible latency for write operations. What should you do?

Accepted Answer

Deploy a failover group with a readable secondary in a paired region using Business Critical, and ensure the failover is planned to achieve zero data loss.. To meet the requirements of zero data loss (RPO=0) and RTO of 30 seconds across regions, a failover group with a readable secondary in a paired region using Business Critical service tier is the best option. Business Critical provides synchronous replication within a region but asynchronous geo-replication across regions; however, a planned failover ensures zero data loss by synchronizing all changes before failover. The readable secondary reduces read load on the primary. Option A is incorrect because active geo-replication is asynchronous and cannot guarantee zero data loss. Option B is incorrect because it does not make the secondary readable, missing the read workload requirement. Option D is incorrect because zone redundancy protects within a region but not against regional disasters.

Answer

Create a secondary database in a paired region using active geo-replication and configure it as readable.

Answer

Deploy a failover group with a secondary database in a paired region using Business Critical, but do not make the secondary readable.

Answer

Configure zone redundancy on the existing Business Critical database in East US.

Question 8

You need to create a disaster recovery plan for an Azure SQL Database that has an RPO of 1 hour and an RTO of 12 hours. Which solution should you use?

Accepted Answer

Geo-restore of automated backups.. Geo-restore of automated backups can achieve an RPO of 1 hour (backups are taken every 5-10 minutes, allowing point-in-time restore within that window) and an RTO of up to 12 hours, meeting the stated requirements. Option A is incorrect because failover groups with automatic failover provide much lower RPO (seconds to minutes) and RTO (minutes), which exceeds the required thresholds and typically incurs higher cost. Option C is incorrect because active geo-replication also offers very low RPO (seconds) and RTO (minutes), exceeding requirements. Option D is incorrect because zone-redundant configuration only protects against an availability zone failure within a region, not against a regional disaster.

Answer

Failover group with automatic failover.

Answer

Active geo-replication.

Answer

Zone-redundant configuration.

Question 9

You manage a SQL Managed Instance in the East US region. The instance must be recoverable within 1 hour in the event of a regional disaster. You need to configure a secondary replica in a paired region with automatic failover. Which solution meets the requirement?

Accepted Answer

Create an auto-failover group with a secondary replica in West US.. SQL Managed Instance supports automatic failover groups with a readable secondary in a paired region. Option A is wrong because log shipping is not supported for Managed Instance. Option B is wrong because geo-restore takes up to 12 hours, which does not meet the 1-hour RTO requirement. Option D is wrong because Always On availability groups on SQL Managed Instance do not support automatic failover across regions; they require manual failover.

Answer

Configure log shipping to a secondary instance in West US.

Answer

Enable geo-redundant backup storage and use geo-restore.

Answer

Deploy an Always On availability group with a synchronous secondary in West US.

Question 10

Which THREE factors should be considered when choosing between Azure SQL Database active geo-replication and auto-failover groups for disaster recovery?

Accepted Answer

Automatic failover capability. Auto-failover groups provide automatic failover capability, which is essential for minimizing downtime during a disaster. Active geo-replication, on the other hand, requires manual or custom scripting to initiate failover, making it less suitable for scenarios where rapid, unattended recovery is needed.

Answer

Recovery Point Objective (RPO) of 1 second

Answer

Support for SQL Server Authentication

Question 11

Your company uses GitHub Actions to deploy changes to Azure SQL Database. You need to ensure that rollbacks can be performed automatically if a deployment fails. Which strategy should you implement?

Accepted Answer

Create a database copy or snapshot before each deployment. The correct strategy is to create a database copy or snapshot before each deployment. This allows you to revert the database to its pre-deployment state if the deployment fails, enabling automated rollback. Option A (Azure App Service deployment slots) is designed for app deployment, not databases. Option B (approval gates) controls when deployments proceed but does not automate rollback. Option D (point-in-time restore) can restore to a point in time but is not a deployment-specific rollback method and may not cover the exact previous state.

Answer

Use Azure App Service deployment slots for the database

Answer

Use GitHub Actions deployment environments with approval gates

Answer

Use Azure SQL Managed Instance point-in-time restore

Question 12

Your company uses Azure SQL Managed Instance with transactional replication. You need to automate the monitoring of replication latency and send an alert if latency exceeds 5 minutes. You want to use Azure Monitoring capabilities. What is the most efficient solution?

Accepted Answer

Create an Azure Monitor metric alert on the 'Log Send Queue Size' metric with a threshold of 300000 KB (approximately 5 minutes).. The most efficient solution because Azure Monitor provides built-in metrics for transactional replication on Azure SQL Managed Instance, such as 'Log Send Queue Size'. By creating a metric alert with a threshold of 300000 KB (which corresponds to approximately 5 minutes of latency at typical throughput), you can be notified automatically when latency exceeds this threshold. This approach uses native Azure Monitoring capabilities without requiring custom jobs or scripts. Options A, B, and C are less efficient: A requires Elastic Database Jobs and a logic app; B uses a performance counter that is not directly exposed in Azure Monitor for SQL Managed Instance and is more complex to set up; C requires creating a SQL Agent job to check a DMV and send email, which is manual and not as scalable as Azure Monitor alerts.

Answer

Use Elastic Database Jobs to run a query on the distributor and log results to a table, then set up a logic app to check the table.

Answer

Monitor the 'Replication: Dist Delivery Latency' performance counter.

Answer

Create a SQL Agent job on the publisher that checks sys.dm_repl_sync_status and sends an email.

Question 13

Your company uses Azure SQL Database and wants to implement row-level security (RLS) to restrict access to customer data based on the user's Microsoft Entra ID group membership. Which TWO actions are required?

Accepted Answer

Create a security policy that uses a predicate function.. Row-level security (RLS) in Azure SQL Database restricts data access at the row level by using a security policy that invokes a predicate function. The predicate function defines the logic for filtering rows, such as checking the caller's Microsoft Entra ID group membership via IS_MEMBER(). Therefore, creating both the predicate function (Option E) and the security policy that uses it (Option C) are required steps.

Answer

Create a column master key for the secured columns.

Answer

Grant the user the db_securityadmin server role.

Answer

Apply a dynamic data masking rule to hide sensitive columns.

Question 14

You are monitoring an Azure SQL Database using sys.dm_db_wait_stats. You see a high percentage of WRITELOG waits. What is the most likely cause?

Accepted Answer

The transaction log is on a slow I/O subsystem.. A high percentage of WRITELOG waits indicates that transaction log writes are slow, often due to I/O latency on the log drive. Therefore, the most likely cause is a slow I/O subsystem for the transaction log (Option B). Option A is incorrect because tempdb allocation contention manifests as PAGELATCH waits. Option C is incorrect because blocking is represented by LCK_M_* waits. Option D is incorrect because CPU pressure shows SOS_SCHEDULER_YIELD waits.

Answer

Tempdb has allocation contention.

Answer

Queries are blocked by locks.

Answer

CPU is under pressure.

Question 15

You are monitoring an Azure SQL Database using the Automatic Tuning feature. The database has a workload that is read-intensive. You enable the CREATE INDEX and DROP INDEX options. After a week, you observe that the database has created several new indexes automatically. However, you notice that one of the new indexes is causing increased write latency for an application that performs frequent updates. What should you do to resolve the issue without losing the benefits of automatic tuning for other indexes?

Accepted Answer

Manually drop the problematic index using a DROP INDEX command.. Manually dropping the problematic index allows you to resolve the specific performance issue caused by increased write latency while retaining the benefits of automatic tuning for other indexes. The Automatic Tuning feature in Azure SQL Database can create indexes to improve read performance, but these indexes may introduce overhead on write operations. By issuing a DROP INDEX command, you surgically remove only the offending index without disabling the overall tuning mechanism.

Answer

Use the Azure portal to revert all automatic tuning recommendations for the past week.

Answer

Manually create the missing indexes that were dropped by automatic tuning.

Answer

Disable automatic tuning for the entire database.