DP-300 Practice Test 14 — 15 Questions

Question 1

You are implementing automated table partitioning maintenance for a large Azure SQL Database. The partitioning function uses a monthly range. You need to add a new partition for the next month and remove the oldest partition. What is the best way to automate this?

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

Accepted Answer

Schedule a T-SQL script via Elastic Database Jobs that uses ALTER PARTITION FUNCTION to SPLIT the range and MERGE the oldest boundary.. The correct approach is to schedule a T-SQL script via Elastic Database Jobs that uses ALTER PARTITION FUNCTION ... SPLIT to add a new boundary for the next month and MERGE to remove the oldest boundary. This automates partition maintenance without moving data or dropping tables, and Elastic Database Jobs provide cross-database scheduling native to Azure SQL Database.

Answer

Create a stored procedure that uses SELECT INTO to copy old data to a history table and then TRUNCATE the partition.

Answer

Use ALTER INDEX REORGANIZE to compress old partitions.

Answer

Use the SWITCH PARTITION operation to move the oldest partition to a staging table and then drop the staging table.

Question 2

You are the database administrator for a large e-commerce company that uses Azure SQL Database for its transactional systems. The environment consists of 100 databases spread across 10 elastic pools in different regions. You need to implement an automated solution to perform the following tasks every night: (1) Run integrity checks (DBCC CHECKDB) on all databases, (2) Rebuild indexes with fragmentation > 30%, (3) Update statistics with full scan for databases that have had significant data changes (>20% of rows). The solution must minimize manual intervention, provide centralized logging, and be resilient to failures (e.g., if one database fails, the others should continue). Which approach should you use?

Accepted Answer

Create an Elastic Database Job with step scripts for each maintenance task, targeting all databases, and configure retry logic.. Elastic Database Jobs are purpose-built for running T-SQL across many Azure SQL databases, including those in elastic pools across regions. You can define step scripts for DBCC CHECKDB, index rebuilds, and statistics updates, target all databases, and configure retry logic so a failure on one database doesn't stop the others — meeting the automation, centralized logging, and resilience requirements.

Answer

Create a SQL Agent job on each server to run a maintenance script.

Answer

Use Azure Data Factory pipelines with a ForEach activity to execute stored procedures.

Answer

Use Azure Automation runbooks with Invoke-SqlCmd to loop through each database.

Question 3

You need to ensure that only specific Azure services can access your Azure SQL Database server. You want to allow traffic from Azure services but block all other traffic. What should you configure?

Accepted Answer

Set the firewall rule 'Allow Azure Services and resources to access this server' to ON and remove all other IP rules.. Setting the 'Allow Azure Services and resources to access this server' firewall rule to ON enables a special rule that permits traffic from all Azure datacenter IP ranges, while removing all other IP rules ensures no other external traffic can reach the server. This configuration meets the requirement to allow only Azure services and block all other traffic, as the Azure services rule is a blanket allow for Azure-originated connections without needing specific IP addresses.

Answer

Set the firewall rule 'Allow Azure Services and resources to access this server' to OFF and add a rule for 0.0.0.0.

Answer

Set firewall rules to deny all IP addresses.

Answer

Set the firewall rule 'Allow Azure Services and resources to access this server' to ON and add a rule for 0.0.0.0.

Question 4

Refer to the exhibit. You are troubleshooting an Azure SQL Database auditing configuration. The exhibit shows the blob auditing policy. The storage account access key is null, and the subscription ID is all zeros. What is the most likely issue?

Accepted Answer

Auditing will fail because the storage account access key is null.. The exhibit shows that the storage account access key is null, which means Azure SQL Database cannot authenticate to the storage account using the access key. Without a valid access key or a configured managed identity, blob auditing will fail because the database cannot write audit logs to the specified storage container. Option C is correct because a null access key directly prevents auditing from functioning when no alternative authentication method is configured.

Answer

Auditing will fall back to Log Analytics workspace.

Answer

Auditing will work because managed identity is used.

Answer

Auditing will write to the storage account using the system-assigned managed identity.

Question 5

You have an Azure SQL Database in the Business Critical tier with zone redundancy enabled. The database experiences a brief outage due to a zone failure. How does the platform automatically recover?

Accepted Answer

A replica in another availability zone is automatically promoted to primary.. When zone redundancy is enabled in Business Critical, Azure SQL Database maintains replicas across multiple availability zones. If a zone fails, the platform automatically detects the failure and promotes a replica from a surviving zone to primary, typically within seconds to a minute, with no manual intervention. This is built-in HA, not disaster recovery.

Answer

You must perform a manual failover to a secondary replica.

Answer

The database is restored from the latest backup.

Answer

The database becomes read-only until the zone is restored.

Question 6

Refer to the exhibit. You run the Azure CLI command to check the configuration of an Azure SQL Database named db1. The output shows zoneRedundant is true and replicationRole is Primary. Which statement is true about this database?

Accepted Answer

The database is zone-redundant and is the primary database in its replication relationship.. The output shows zoneRedundant is true, meaning the database is deployed across multiple availability zones within its region for high availability, and replicationRole is Primary, meaning it is the primary in a replication relationship (such as a failover group or active geo-replication). Option B correctly combines both facts. The zoneRedundant flag has nothing to do with cross-region replication, so options A and D are incorrect, and Hyperscale is a separate service tier not indicated by these properties.

Answer

The database is configured with active geo-replication to a secondary region.

Answer

The database is a Hyperscale database with zone redundancy enabled.

Answer

The database has a readable secondary replica in a different Azure availability zone.

Question 7

Your Azure SQL Database is experiencing high DTU consumption. You need to identify the top resource-consuming queries. What should you do?

Accepted Answer

Use the Query Store reports in the Azure portal. Query Store in Azure SQL Database provides built-in reports to identify top resource-consuming queries by CPU, IO, and duration, making it the easiest and most direct method. Option B (SQL Server Profiler) is not supported in Azure SQL Database. Option C (Extended Events) is more complex and not the simplest approach for this task. Option D (sys.dm_exec_query_stats) can be used but lacks persistent historical data and is more effort than using Query Store reports.

Answer

Use SQL Server Profiler

Answer

Create an Extended Events session to capture query events

Answer

Query sys.dm_exec_query_stats

Question 8

You are troubleshooting a performance issue on Azure SQL Database. The database uses the General Purpose tier with 100 DTUs. Users report intermittent slowdowns during peak hours. Query Store shows frequent waits for RESOURCE_SEMAPHORE. What is the most likely cause?

Accepted Answer

The database is experiencing memory pressure due to concurrent queries exceeding available memory.. RESOURCE_SEMAPHORE waits indicate that queries are waiting for memory grants to execute. In Azure SQL Database General Purpose tier with 100 DTUs, memory is shared between the buffer pool and query execution. During peak hours, concurrent queries can exhaust the available memory, forcing queries to wait for memory grants. This is a classic sign of memory pressure, not CPU or IO throttling.

Answer

There is a blocking chain due to unoptimized queries.

Answer

The disk IOPS limit is being reached, causing queuing.

Answer

The DTU limit is being reached, causing CPU throttling.

Question 9

You need to automate the deployment of Azure SQL Database logical servers and databases using Bicep. What is the best practice for storing the administrative password securely?

Accepted Answer

Reference the password from Azure Key Vault using the getSecret function. Azure Key Vault is the recommended secure storage for secrets like administrative passwords in Azure deployments. Using the `getSecret` function in Bicep allows you to reference a secret from Key Vault at deployment time without exposing the password in the Bicep file or deployment logs, aligning with Azure security best practices and the principle of least privilege.

Answer

Use the adminPassword property with a generated password

Answer

Use an environment variable in the deployment script

Answer

Store the password as a plain text parameter in the Bicep file

Question 10

Refer to the exhibit. You run the above PowerShell command to set the Transparent Data Encryption (TDE) protector for an Azure SQL Database server. What is the result?

Accepted Answer

The server’s TDE protector is changed to a customer-managed key from Azure Key Vault.. The PowerShell command Set-AzSqlServerTransparentDataEncryptionProtector with -Type AzureKeyVault changes the server-level TDE protector to a customer-managed key stored in Azure Key Vault. This is the documented cmdlet for configuring Bring Your Own Key (BYOK) TDE at the Azure SQL logical server level, and the result is that the server's TDE protector is now the specified Key Vault key.

Answer

The command fails because the service principal does not have permissions to the key vault.

Answer

Transparent Data Encryption is disabled.

Answer

The TDE protector for the database "mydb" is updated.

Question 11

You are responsible for a set of Azure SQL Databases that are used by different departments in your organization. The databases are deployed in an elastic pool with Standard tier (eDTU 200). Usage patterns show that the marketing database uses high CPU during the day, while the sales database uses high IO at night. You want to optimize costs while ensuring each database gets the resources it needs. What should you do?

Accepted Answer

Configure minimum and maximum eDTU per database in the pool. In an elastic pool, you can set per-database minimum and maximum eDTU limits to guarantee a floor of resources for each database while capping how much any single database can consume. This lets the marketing database get the CPU it needs during the day and the sales database get the IO it needs at night, without one starving the other, and it optimizes cost by keeping the shared pool model.

Answer

Migrate the pool to a vCore-based elastic pool

Answer

Add more databases to the pool to spread the load

Answer

Move each database to a standalone DTU tier

Question 12

You have an Azure SQL Database that uses the General Purpose service tier. The database is critical and you need to ensure that it remains available during a planned patching event that updates the underlying hardware. What does Azure SQL Database provide to maintain availability during such events?

Accepted Answer

Automated failover to a built-in standby replica with minimal downtime.. Azure SQL Database provides automated failover to a built-in standby replica with minimal downtime during planned patching events. This is part of the built-in high availability architecture, where the database runs on a primary replica and maintains a secondary replica. During patching, a failover occurs to the secondary, ensuring continuity.

Answer

The database is taken offline during patching and restored afterward.

Answer

You must manually fail over to a secondary database to avoid downtime.

Answer

Zone-redundant replicas that ensure zero downtime.

Question 13

Refer to the exhibit. You are deploying an Azure SQL Database using this ARM template. After deployment, you need to automate the scaling of the database to a higher service tier when DTU consumption exceeds 80% for 5 minutes. Which Azure service should you use to trigger the scaling?

Accepted Answer

Azure Monitor metric alert. Azure Monitor metric alerts can be configured to trigger an Azure Automation runbook that scales the Azure SQL Database when DTU consumption exceeds 80% for 5 minutes. This is a common pattern for autoscaling based on metrics. Option A (Azure SQL Analytics) is a monitoring solution that does not trigger actions. Option B (Azure Automation Update Management) is for managing updates on virtual machines, not for scaling databases. Option C (SQL Server Agent) is not available in Azure SQL Database; it is used for on-premises SQL Server instances.

Answer

Azure SQL Analytics

Answer

Azure Automation Update Management

Answer

SQL Server Agent

Question 14

Which THREE of the following are required to configure Microsoft Entra authentication for an Azure SQL Managed Instance?

Accepted Answer

Enable the managed instance system-assigned managed identity.. Enabling the system-assigned managed identity for Azure SQL Managed Instance is required because Microsoft Entra authentication relies on this identity to authenticate the instance itself against Microsoft Entra ID. Without a managed identity, the instance cannot securely obtain tokens or perform directory lookups needed for authentication and authorization.

Answer

Ensure the managed instance is deployed in a different virtual network from the clients.

Answer

Configure the managed instance to allow public network access.

Question 15

You have an Azure SQL Database that uses a failover group for high availability. You need to automate the failover to the secondary region during a planned maintenance window. What is the best approach?

Accepted Answer

Use Azure CLI or PowerShell to invoke planned failover. Using Azure CLI or PowerShell to invoke a planned failover is the best approach because it provides a direct, scriptable, and auditable method to trigger failover during a planned maintenance window. Planned failover ensures no data loss by synchronizing all pending changes before switching, and it can be automated via scripts in Azure DevOps or other orchestration tools. This meets the requirement for automation during a planned window with minimal complexity.

Answer

Create an Azure Automation runbook that uses REST API

Answer

Schedule an Elastic Database Job to execute a failover script

Answer

Configure Azure Traffic Manager to route traffic to secondary