DP-300 Practice Exam 51 — 20 Questions

Question 1

You have configured a failover group using the JSON template shown. The primary server is server1 in East US, and the secondary server is server2 in West US. During a test, you notice that read-only queries against the secondary endpoint are failing. What is the most likely cause?

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

Accepted Answer

The read-only endpoint failover policy is disabled.. The read-only endpoint failover policy is set to Disabled, which prevents read-only queries from being routed to the secondary endpoint. Option B is incorrect because the database is included in the failover group databases list. Option C is incorrect because the failover grace period only affects automatic failover, not read-only routing. Option D is incorrect because the read-write endpoint failover policy is Automatic, which is not related to the read-only query failure.

Answer

The database is not included in the failover group databases list.

Answer

The failover grace period is set too high.

Answer

The read-write endpoint failover policy is set to Automatic.

Question 2

You are the DBA for a financial services company. The company is migrating its on-premises SQL Server databases to Azure SQL Managed Instance. One of the databases is 2 TB in size and has a high transaction volume. The migration must have minimal downtime. The source environment is SQL Server 2019 running on Windows Server. The target is a General Purpose managed instance in the same region. The network bandwidth between on-premises and Azure is 500 Mbps. The DBA plans to use Azure Database Migration Service (DMS) for an online migration. However, during the initial sync, the migration is progressing slowly and the estimated time to completion is 48 hours. The DBA needs to reduce the migration time. What should the DBA do?

Accepted Answer

Upgrade the DMS instance to a Premium SKU to increase performance. Upgrading the DMS instance to a Premium SKU increases the underlying compute and I/O resources allocated to the migration service, which directly improves the throughput of data movement during the online migration. Since the bottleneck is the DMS processing speed rather than network bandwidth (500 Mbps is sufficient for 2 TB over 48 hours), a Premium SKU provides higher performance tiers that can process change data capture (CDC) and bulk inserts faster, reducing the overall sync time.

Answer

Switch to an offline migration using BACPAC export and import

Answer

Request a higher bandwidth connection from the network team

Answer

Change the target to a different Azure region that is closer to the source

Question 3

A healthcare company is required to encrypt all patient data at rest and in transit. They are deploying Azure SQL Database. Which combination of features should they implement to meet this requirement?

Accepted Answer

Transparent data encryption (TDE) and TLS 1.2. Transparent Data Encryption (TDE) encrypts data at rest by performing real-time I/O encryption and decryption of the database, data files, and transaction logs, while TLS 1.2 encrypts data in transit between the client and Azure SQL Database. Together, they satisfy the requirement to encrypt all patient data both at rest and in transit.

Answer

Dynamic data masking and row-level security

Answer

Azure Active Directory authentication and firewall rules

Answer

Always Encrypted and transparent data encryption (TDE)

Question 4

Refer to the exhibit. An administrator wants to restore the primary database SalesDB to a point in time 2025-03-15T09:00:00Z. What is the impact on the geo-replication?

Accepted Answer

The geo-replication will be terminated, and the secondary will become a standalone database.. When you perform a point-in-time restore (PITR) on a primary database that is configured for geo-replication, the geo-replication link is broken. The secondary database becomes a standalone read-only database, and the primary is restored as a new database (typically with a name like SalesDB_restored or a specified name). This is because PITR creates a new database from backups, which cannot maintain the continuous replication stream required for geo-replication.

Answer

The geo-replication will continue seamlessly after the restore.

Answer

The restore will fail because the database is in a geo-replication relationship.

Answer

The secondary database will automatically be restored to the same point in time.

Question 5

You manage an Azure SQL Database that uses active geo-replication with a secondary in a paired region. During a planned maintenance, you need to fail over to the secondary with zero data loss. What should you do?

Accepted Answer

Execute a planned failover (graceful failover) using Transact-SQL or PowerShell. A planned failover (graceful failover) synchronizes all transactions between the primary and secondary before switching, ensuring zero data loss. Option A uses a standard failover that may allow data loss if the secondary is not fully synchronized. Option B causes data loss by taking the primary offline abruptly. Option D explicitly allows data loss, which is not acceptable for this requirement.

Answer

Use Azure portal to 'Failover' without specifying data loss tolerance

Answer

Take the primary offline, then promote the secondary

Answer

Execute a forced failover allowing data loss

Question 6

You are designing a disaster recovery solution for a critical Azure SQL Database that runs in the West US region. The database is 2 TB in size and requires a recovery point objective (RPO) of less than 5 seconds and a recovery time objective (RTO) of less than 30 seconds. Which deployment option should you recommend?

Accepted Answer

Use Azure SQL Database Business Critical with zone-redundant configuration. Azure SQL Database Business Critical with zone-redundant configuration is the correct choice because it provides synchronous replication across three availability zones within the same region, ensuring an RPO of 0 (no data loss) and an RTO of under 30 seconds for zonal failures. This meets the strict RPO of less than 5 seconds and RTO of less than 30 seconds for a 2 TB database, as Business Critical offers high-performance local SSD storage and automatic failover without data loss.

Answer

Use Azure SQL Database General Purpose with auto-failover group

Answer

Use Azure SQL Database Hyperscale with geo-replication

Answer

Use Azure SQL Managed Instance Business Critical with zone-redundant configuration

Question 7

Which TWO of the following are best practices for securing Azure SQL Database?

Accepted Answer

Enable firewall rules to restrict access to specific IP addresses.. Azure SQL Database firewall rules allow you to restrict access to specific IP address ranges, which is a fundamental network security best practice. By limiting inbound traffic to only trusted IPs, you reduce the attack surface and prevent unauthorized connections from unknown sources. This is a first line of defense in a defense-in-depth strategy for securing Azure SQL Database.

Answer

Enable Auditing to block malicious queries.

Answer

Enable TDE to prevent SQL injection attacks.

Answer

Use SQL authentication with complex passwords.

Question 8

You are designing a secure architecture for Azure SQL Managed Instance. You need to ensure that all connections to the instance are encrypted and that the instance can only be accessed from a specific virtual network. Which TWO configurations should you implement?

Accepted Answer

Configure the managed instance with the 'Force encryption' property set to Enabled.. Setting the 'Force encryption' property to Enabled on Azure SQL Managed Instance ensures that all client connections are encrypted using TLS, enforcing encryption in transit. This is a critical security control to protect data from interception or tampering during transmission.

Answer

Configure a service endpoint on the subnet of the managed instance.

Answer

Deploy the managed instance outside a virtual network.

Answer

Attach a private endpoint to the managed instance.

Question 9

Your Azure SQL Managed Instance is experiencing performance degradation. You suspect a query plan regression caused by parameter-sensitive plan issues. Which feature should you use to identify and resolve the issue?

Accepted Answer

Query Store with Query Store Hints. Query Store with Query Store Hints is the correct feature because it allows you to identify parameter-sensitive plan regression by analyzing historical execution plans stored in Query Store, and then force a specific plan using a hint without changing application code. This directly addresses the root cause of parameter-sensitive plan issues, where different parameter values lead to suboptimal cached plans.

Answer

Intelligent Insights

Answer

Automatic tuning

Answer

Database Advisor

Question 10

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 11

You need to automate the deployment of database schema changes across multiple Azure SQL Databases in a development environment. Which Azure service is designed for this purpose?

Accepted Answer

Azure DevOps using database projects and release pipelines.. Azure DevOps with database projects and release pipelines provides a CI/CD solution specifically designed for deploying schema changes to Azure SQL Databases. Option B is incorrect because SQL Agent jobs are used for scheduled administrative tasks within a single database, not for multi-database schema deployment automation. Option C is incorrect because Azure Automation Account with PowerShell runbooks is suitable for general automation tasks but lacks native CI/CD capabilities for database schema updates. Option D is incorrect because Azure Data Factory with copy activity focuses on data movement and transformation, not on executing schema changes.

Answer

SQL Agent jobs on each database.

Answer

Azure Automation Account with PowerShell runbooks.

Answer

Azure Data Factory with a copy activity.

Question 12

You are designing a security strategy for Azure SQL Managed Instance. The compliance team requires that all database backups be encrypted at rest using a customer-managed key. Which feature should you enable?

Accepted Answer

Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault.. Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault is the correct feature because it encrypts the database at rest, including all backup files, using a key that the customer controls. This satisfies the compliance requirement for customer-managed key encryption of backups, as TDE automatically encrypts backups when the database is encrypted.

Answer

Always Encrypted with a column master key in Azure Key Vault.

Answer

Transparent Data Encryption (TDE) with a service-managed key.

Answer

Row-Level Security (RLS) with a custom authorization function.

Question 13

Refer to the exhibit. You observe DTU usage reaching 100% at 11:00 AM. Which recommendation would best resolve the performance issue?

Accepted Answer

Schedule the recurring job to run at a different time, such as midnight.. The exhibit shows a pattern of increasing DTU usage. The recurring job runs from 10:00 to 10:30, but the peak at 11:00 suggests another process causing full usage. The best solution is to schedule resource-intensive operations during off-peak hours. Increasing the service tier may help but is not the most targeted approach.

Answer

Enable read scale-out to offload reporting queries.

Answer

Increase the service tier to S3 to provide more DTUs.

Answer

Create an index on the tables used by the job.

Question 14

You are reviewing an Azure CLI command that creates an elastic job step. The job step is intended to rebuild all indexes on the Sales.Orders table, but the job fails. What is the error in the JSON configuration?

Accepted Answer

The command is missing a semicolon at the end.. The T-SQL command 'ALTER INDEX ALL ON Sales.Orders REBUILD' must end with a semicolon to be valid in an Azure Elastic Job step. Without the semicolon, the job fails. Option B is incorrect because the target group type for a single database is 'SqlDatabase'. Option C is incorrect because 'ALTER INDEX ... REBUILD' is the correct syntax. Option D is incorrect because there is no version incompatibility.

Answer

The target group should specify 'SqlServer' type instead of 'SqlDatabase'.

Answer

The command should be 'REBUILD INDEX' instead of 'ALTER INDEX'.

Answer

The job agent version is incompatible with the command.

Question 15

You need to automate the backup of an Azure SQL Managed Instance to a storage account every 4 hours. Which feature should you use?

Accepted Answer

Azure Automation with PowerShell runbooks. Azure SQL Managed Instance has built-in automated backups, but they run automatically and cannot be customized to run every 4 hours or target a specific storage account. Long-term retention (LTR) policies are for retaining backups beyond the automated backup period, not for scheduling. SQL Server Agent is available in Azure SQL Managed Instance and can be used to schedule jobs, but it does not provide a direct way to back up to an Azure storage account every 4 hours. Azure Automation with PowerShell runbooks can invoke backups using REST APIs or T-SQL, and schedule them precisely, making it the best choice for this requirement.

Answer

Built-in automated backups

Answer

SQL Agent jobs

Answer

Long-term retention (LTR) policies

Question 16

You manage an Azure SQL Managed Instance that hosts a critical OLTP database. You notice that the average CPU usage is consistently above 90% during business hours. You have enabled Intelligent Insights, which recommends creating a missing index. What should you do first to validate the recommendation before implementing it?

Accepted Answer

Use Query Store to review query performance and missing index details.. Use Query Store to review query performance and missing index details. Intelligent Insights provides recommendations, but you should validate them using Query Store, which shows actual query performance and missing index details. This helps confirm the index will reduce CPU usage without negative side effects. B is wrong because scaling up increases resources but doesn't address the root cause; it may be unnecessary. C is wrong because automatic index tuning would implement changes without validation, which could be risky. D is wrong because creating the index immediately without validation might cause performance issues or be unnecessary.

Answer

Scale up the managed instance to a higher tier.

Answer

Enable automatic index tuning.

Answer

Create the recommended index immediately.

Question 17

You need to configure Azure SQL Database to automatically adjust indexing based on workload patterns. Which feature should you enable?

Accepted Answer

Automatic tuning. Automatic tuning in Azure SQL Database continuously analyzes query execution plans and workload patterns, then automatically creates, drops, or rebuilds indexes to improve performance. It uses built-in intelligence to recommend and apply index changes without manual intervention, making it the correct feature for automatically adjusting indexing based on workload patterns.

Answer

Azure Advisor

Answer

Intelligent Insights

Answer

Query Store

Question 18

You are reviewing the firewall and virtual network rules for an Azure SQL Database server as shown in the exhibit. The server has public network access disabled. A client application in a different virtual network (vnet2) needs to connect to the database. What must you do to allow the connection?

Accepted Answer

Add a virtual network rule that references subnet2 in vnet2.. Since public network access is disabled, the server rejects all public IP-based connections. To allow a client in a different virtual network (vnet2) to connect, you must create a virtual network rule that references subnet2 in vnet2. This rule establishes a private endpoint-like connection through the Azure backbone, bypassing the public internet and honoring the disabled public network access setting.

Answer

Add the IP address of the client to the ipRules array.

Answer

Set publicNetworkAccess to 'Enabled' and add an IP firewall rule.

Answer

Create a server-level firewall rule to allow all Azure services.

Question 19

You are planning a migration of on-premises SQL Server databases to Azure SQL Managed Instance. Which TWO tools or services can you use to assess compatibility and identify potential migration blockers? (Select TWO.)

Accepted Answer

Data Migration Assistant (DMA). Data Migration Assistant (DMA) is the correct tool because it performs a detailed assessment of on-premises SQL Server databases, identifying compatibility issues and migration blockers specifically for Azure SQL Managed Instance. It generates a report of feature parity, breaking changes, and behavior differences. Azure Migrate is also correct because it provides a unified migration assessment and discovery platform, including database assessment capabilities for Azure SQL targets. Both tools can assess compatibility and identify blockers before migration.

Answer

SQL Server Management Studio (SSMS) – Upgrade Data Tier Application wizard

Answer

Azure Database Migration Service (DMS)

Answer

Azure Data Studio – Migration Wizard

Question 20

Your organization requires that all Azure SQL Database deployments use the most recent version of SQL Server engine to ensure security and performance. Which deployment option automatically provides the latest engine version without requiring manual upgrades?

Accepted Answer

Azure SQL Database (single database).. Azure SQL Database (single database) is a Platform-as-a-Service (PaaS) offering where Microsoft manages the underlying SQL Server engine. Microsoft automatically applies the latest engine version updates during the service's maintenance windows, ensuring you always run a supported and current version without any manual upgrade effort. This aligns directly with the requirement for automatic, hands-off version management.

Answer

SQL Server on Azure Virtual Machines.

Answer

Azure SQL Database elastic pool.

Answer

Azure SQL Managed Instance.