DP-300 Timed Practice Test 3 — 10 Questions

Question 1

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. Option A is correct because 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 2

Which TWO of the following are valid methods to migrate an on-premises SQL Server database to Azure SQL Database?

Accepted Answer

Export the database as a BACPAC file and import into Azure SQL Database. A BACPAC file is a logical backup that contains the database schema and data in a compressed format. Azure SQL Database supports importing BACPAC files directly via the Azure portal, PowerShell, or the SqlPackage utility, making this a straightforward and fully supported migration method.

Answer

Configure transactional replication to replicate changes to Azure SQL Database

Answer

Use log shipping to continuously copy transaction logs

Answer

Restore a native SQL Server backup to Azure SQL Database

Question 3

You need to monitor Azure SQL Database performance over time and receive alerts when CPU usage exceeds 80%. Which Azure service should you use?

Accepted Answer

Azure Monitor Alerts. Azure Monitor Alerts is the correct service because it allows you to create metric-based alert rules that trigger when the CPU percentage of an Azure SQL Database exceeds a defined threshold (e.g., 80%). It continuously monitors performance metrics over time and sends notifications (e.g., email, SMS, or webhook) when the condition is met, fulfilling the requirement for both monitoring and alerting.

Answer

Automatic tuning

Answer

Query Performance Insight

Answer

SQL Assessment

Question 4

You are designing an automated backup strategy for Azure SQL Database. Which TWO actions satisfy a requirement to maintain daily backups for 30 days and ensure recoverability if the Azure region becomes unavailable?

Accepted Answer

Enable geo-redundant backup storage for the server.. Option A is correct because enabling geo-redundant backup storage (RA-GRS) for Azure SQL Database ensures that automated backups are replicated to a paired Azure region. This satisfies the requirement for recoverability if the primary region becomes unavailable, as backups can be restored from the secondary region. Combined with setting point-in-time restore retention to 30 days (Option C), you maintain daily backups for the full 30-day retention period, meeting both requirements.

Answer

Deploy a read-scale replica in a different Azure region.

Answer

Set up a long-term retention policy with a retention of 30 days.

Answer

Configure active geo-replication to a secondary region.

Question 5

Which TWO actions are required to enable Microsoft Entra ID authentication for an Azure SQL Database?

Accepted Answer

Set an Microsoft Entra ID admin for the Azure SQL Server.. To enable Entra ID authentication, you must set an Entra ID admin for the SQL server and create contained database users mapped to Entra ID identities. Enabling Azure AD (Entra ID) integration is automatic. Assigning Azure RBAC roles is for management plane, not database authentication. The SQL Server authentication mode is always enabled.

Answer

Enable SQL Server authentication only.

Answer

Assign the SQL Server Contributor role to the Entra ID users.

Answer

Enable Azure AD integration on the SQL server.

Question 6

You are monitoring an Azure SQL Database using Query Performance Insight and notice that a specific query has a high average duration and high CPU usage. The query plan shows a clustered index scan on a large table. Which two actions should you take to optimize performance? (Choose two.)

Accepted Answer

Update statistics on the table to ensure the optimizer has current information.. Option B is correct because updating statistics provides the query optimizer with current data distribution information, which can lead to a more efficient query plan, potentially avoiding the clustered index scan. In Azure SQL Database, stale statistics are a common cause of suboptimal plans, and updating them is a low-cost, non-disruptive first step before considering index changes.

Answer

Rebuild the clustered index to reduce fragmentation.

Answer

Force a different query plan using Query Store hints.

Answer

Increase the DTU service tier of the database.

Question 7

Which TWO of the following are valid methods to connect to Azure SQL Database securely?

Accepted Answer

Connect using Azure AD authentication with multi-factor authentication.. Option A is correct because Azure AD authentication with multi-factor authentication (MFA) provides strong identity-based security, eliminating password vulnerabilities and supporting conditional access policies. This method integrates with Azure SQL Database's built-in support for Azure AD tokens, ensuring that only authenticated users with MFA can connect, which meets the exam's focus on secure environment implementation.

Answer

Connect using a shared access key from Azure Storage.

Answer

Connect directly using the server's public IP address without encryption.

Answer

Connect using SQL authentication with a simple password.

Question 8

You are designing a database solution for an e-commerce application that requires high read throughput with sub-5 ms latency. The application runs on Azure VMs. You need to choose between Azure SQL Database and Azure SQL Managed Instance. Which factor most strongly supports choosing Azure SQL Database over SQL Managed Instance?

Accepted Answer

Azure SQL Database supports Hyperscale tier with sub-5 ms read latency on local SSDs.. Option B is correct because the Hyperscale tier of Azure SQL Database uses a distributed architecture with local SSD-based buffer pool extensions on compute nodes, enabling sub-5 ms read latency for data cached locally. This directly meets the e-commerce application's requirement for high read throughput with low latency, whereas Azure SQL Managed Instance relies on remote premium page blobs for storage, which introduces higher latency for reads that are not cache-resident.

Answer

Azure SQL Database offers a wider range of built-in instance-level features.

Answer

Azure SQL Database allows databases up to 100 TB in size.

Answer

Azure SQL Database supports active geo-replication with read-scale secondaries.

Question 9

You are automating index maintenance for an Azure SQL Database. Which THREE of the following should you consider to minimize performance impact?

Accepted Answer

Use the ONLINE = ON option when rebuilding indexes.. Option A is correct because using ONLINE = ON when rebuilding indexes in Azure SQL Database allows concurrent user access to the underlying table during the index rebuild operation. This minimizes blocking and performance impact on production workloads, as the index rebuild is performed as a series of short-term locks rather than a single long-term schema modification lock.

Answer

Always use SORT_IN_TEMPDB = ON to reduce tempdb contention.

Answer

Set MAXDOP to 0 to use all CPUs for faster execution.

Question 10

You are responsible for automating backups of on-premises SQL Server databases to Azure Blob Storage. The solution must use the least administrative effort and provide point-in-time restore capability. What should you implement?

Accepted Answer

Configure SQL Server Managed Backup to Microsoft Azure.. SQL Server Managed Backup to Microsoft Azure (also known as Managed Backup) is the correct choice because it provides automated, policy-based backup management with minimal administrative effort. It natively supports point-in-time restore by automatically scheduling full, differential, and transaction log backups to Azure Blob Storage, and it handles backup retention and recovery point management without requiring custom scripts or additional infrastructure.

Answer

Install Azure Backup Server on-premises and configure backup of SQL Server databases.

Answer

Use SQL Server Agent jobs to perform full, differential, and log backups to an Azure Blob Storage URL.

Answer

Use Azure Data Factory to copy database backups to Blob Storage.