DP-300 Practice Test 28 — 15 Questions

Question 1

Refer to the exhibit. An administrator is configuring geo-replication for an Azure SQL Database. The admin runs the commands and sees the output shown. What is the current status of the geo-replication link?

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

Accepted Answer

The secondary database is fully synchronized and ready for failover.. The output shows replicationState as CATCH_UP and percentComplete as 100, indicating that all data changes on the primary have been synchronized to the secondary. This means the geo-replication link is healthy and data is fully synchronized.

Answer

The secondary database is lagging behind the primary by a significant amount.

Answer

The secondary database is in the process of initial seeding and is not yet fully synchronized.

Answer

The replication link is broken and needs to be re-created.

Question 2

Your organization requires that all Azure SQL Database backups be retained for 10 years to meet compliance requirements. Which backup retention policy should you configure?

Accepted Answer

Configure long-term retention (LTR) policy.. Long-term retention (LTR) in Azure SQL Database allows you to retain full database backups for up to 10 years, which meets the compliance requirement for 10-year backup retention. LTR policies are configured separately from point-in-time restore (PITR) and store backups in isolated containers for extended periods, ensuring regulatory compliance.

Answer

Configure point-in-time restore (PITR) retention to 10 years.

Answer

Enable automatic tuning to optimize backups.

Answer

Enable geo-redundant backup storage (GRS).

Question 3

You are configuring Azure SQL Database firewall rules for a new application. The application runs on Azure VMs in the same region. To minimize latency and security risk, which approach should you use?

Accepted Answer

Configure a virtual network service endpoint and a virtual network firewall rule.. Using a virtual network service endpoint and a virtual network firewall rule allows Azure SQL Database to accept traffic only from the specific subnet hosting the application VMs, without exposing the database to the public internet. This minimizes latency by keeping traffic within the Azure backbone network and reduces the security risk by eliminating broad IP-based rules.

Answer

Add a firewall rule allowing all Azure IP addresses.

Answer

Add a firewall rule for each VM's public IP address.

Answer

Add a firewall rule allowing all Azure services to access the database.

Question 4

You are a database administrator for a retail company that uses Azure SQL Database. The security team wants to prevent SQL injection attacks by ensuring that all application queries use parameterized statements. Which built-in Azure feature should you enable to help detect and alert on potential SQL injection attempts?

Accepted Answer

Enable Microsoft Defender for SQL. Microsoft Defender for SQL includes advanced threat detection capabilities that continuously monitor database activity for anomalous patterns, including SQL injection attempts. When enabled, it analyzes query execution patterns and can alert on suspicious queries that deviate from parameterized statement usage, directly addressing the security team's requirement to detect and alert on potential SQL injection attacks.

Answer

Enable auditing on the database

Answer

Enable data discovery and classification

Answer

Enable SQL vulnerability assessment

Question 5

Which THREE are valid methods to authenticate to Azure SQL Database using Microsoft Entra ID?

Accepted Answer

Microsoft Entra ID password authentication. Microsoft Entra ID password authentication allows users to authenticate to Azure SQL Database using their Entra ID credentials directly, without requiring Windows domain join or integrated authentication. This method uses the Entra ID access token flow, where the user provides their username and password to obtain a token from the Microsoft Entra ID endpoint, which is then used to connect to the database. It is a straightforward, cloud-native authentication method supported for contained database users mapped to Entra ID identities.

Answer

User-assigned managed identity without a contained database user

Answer

Azure CLI authentication

Question 6

You are designing a secure environment for Azure SQL Database. Which authentication method provides the strongest security and supports multi-factor authentication?

Accepted Answer

Azure Active Directory authentication. Azure Active Directory (Azure AD) authentication is the recommended method for Azure SQL Database because it supports multi-factor authentication (MFA), conditional access policies, and identity-driven security. It eliminates the need for password management and leverages Azure AD's built-in security features, providing the strongest security posture for cloud-native environments.

Answer

Certificate-based authentication

Answer

SQL authentication with strong passwords

Answer

Windows authentication

Question 7

Which TWO actions can be performed using Azure Automation runbooks for Azure SQL Database? (Choose two.)

Accepted Answer

Execute T-SQL scripts against Azure SQL Database. Azure Automation runbooks can execute T-SQL scripts and scale databases. They cannot manage on-premises SQL Server directly (requires hybrid worker). SQL Agent jobs cannot be created in Azure SQL Database.

Answer

Deploy Azure Resource Manager templates

Answer

Create SQL Agent jobs on Azure SQL Database

Answer

Manage on-premises SQL Server instances directly

Question 8

You are designing a secure Azure SQL Database environment for a financial services application. You need to meet the following requirements: - All network traffic to the database must be private and not traverse the public internet. - Only specific Azure resources in a virtual network should be able to connect. - The solution must minimize administrative overhead. Which TWO configurations should you implement? (Choose two.)

Accepted Answer

Configure the server firewall to deny all public network access.. Configuring the server firewall to deny all public network access ensures that no traffic can reach the database over the public internet, which is required for private connectivity. Option E is correct because creating a private endpoint for Azure SQL Database in the virtual network assigns a private IP address from the VNet, allowing traffic to stay entirely within the Microsoft backbone network and never traverse the public internet. Together, these two configurations enforce private-only access and meet the requirement for specific Azure resources in the VNet to connect.

Answer

Deploy Azure Bastion in the virtual network for secure connectivity.

Answer

Add a firewall rule to allow all Azure services to access the server.

Answer

Enable service endpoints for Azure SQL Database on the virtual network subnets.

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

You are responsible for an Azure SQL Database that stores customer personally identifiable information (PII). You need to ensure that users with the 'CustomerService' role can see only the last four digits of Social Security numbers. Which feature should you use?

Accepted Answer

Dynamic Data Masking with a partial mask function. Dynamic Data Masking (DDM) with a partial mask function is the correct choice because it allows you to obfuscate sensitive data at query time without altering the underlying storage. For a Social Security number, you can apply a mask like 'partial(0, "XXX-XX-", 4)' to expose only the last four digits to users in the 'CustomerService' role, while the full value remains intact in the database.

Answer

Row-Level Security

Answer

Column-level security with DENY permission on the column

Answer

Always Encrypted with deterministic encryption

Question 11

Drag and drop the steps to troubleshoot a high CPU usage issue in Azure SQL Database in the correct order.

Accepted Answer

Identify high CPU queries, then Analyze execution plans, then Check for missing indexes, then Implement fixes, then Monitor performance.. Start by identifying high CPU queries, analyze plans, check for missing indexes, implement fixes, then monitor.

Answer

Identify high CPU queries, then Check for missing indexes, then Analyze execution plans, then Implement fixes, then Monitor performance.

Answer

Analyze execution plans, then Identify high CPU queries, then Check for missing indexes, then Implement fixes, then Monitor performance.

Answer

Identify high CPU queries, then Analyze execution plans, then Implement fixes, then Check for missing indexes, then Monitor performance.

Question 12

Refer to the exhibit. An administrator configures an auto-failover group for Azure SQL Database. The primary server is in East US and the secondary in West US. What will happen if the primary region becomes unavailable?

Accepted Answer

The failover will occur automatically after 60 minutes.. With automatic failover policy and grace period of 60 minutes, the system will wait 60 minutes before automatically failing over to the secondary server. During that time, read-write endpoints will not be available.

Answer

The administrator must manually trigger the failover.

Answer

The databases will become read-only until the primary recovers.

Answer

The secondary will immediately take over as the primary.

Question 13

You are designing a data archiving solution for an Azure SQL Database. The database contains a table with over 100 million rows of historical sales data that is rarely accessed. You need to reduce storage costs while keeping the data available for occasional queries. What should you do?

Accepted Answer

Use Stretch Database to migrate cold data to Azure Blob Storage. Stretch Database (now deprecated but still tested in DP-300) transparently migrates cold data from an Azure SQL Database table to Azure Blob Storage while keeping the data online and queryable via T-SQL. This reduces storage costs for rarely accessed historical data without requiring application changes, as the database engine automatically routes queries to the appropriate storage tier.

Answer

Partition the table by date and compress each partition

Answer

Use page compression on the table

Answer

Delete old data and store it in Azure Blob Storage as CSV files

Question 14

Refer to the exhibit. You are reviewing the firewall rule JSON for an Azure SQL Server. What does this rule allow?

Accepted Answer

No traffic; this rule blocks all incoming connections.. This rule is a firewall rule with start IP address '0.0.0.0' and end IP address '0.0.0.0', which in Azure SQL Server firewall configuration explicitly blocks all incoming traffic. The IP range 0.0.0.0 to 0.0.0.0 is a special reserved range that denies any connection attempts, regardless of source IP. This is the default behavior when no other allow rules exist, effectively creating a deny-all rule.

Answer

All traffic from any IP address.

Answer

Traffic from IP address 0.0.0.0.

Answer

Traffic from Azure services.

Question 15

You are managing an Azure SQL Managed Instance that hosts multiple databases for a financial application. You need to implement a security solution that meets compliance requirements by auditing all database activity and sending the audit logs to a centralized Log Analytics workspace for analysis. The solution must also support real-time alerts on suspicious activities. What should you configure?

Accepted Answer

Enable auditing to a Log Analytics workspace and integrate with Microsoft Sentinel.. Auditing to a Log Analytics workspace allows centralized collection of audit logs, which can then be integrated with Microsoft Sentinel for real-time analytics, threat detection, and automated alerting on suspicious activities. This meets both the compliance requirement for auditing and the operational need for real-time alerts.

Answer

Enable Microsoft Defender for Cloud and configure SQL vulnerability assessment.

Answer

Enable auditing to a storage account and use Microsoft Intune for monitoring.

Answer

Enable Microsoft Purview Data Map for the managed instance.