DP-300 Practice Test 49 — 15 Questions

Question 1

You are configuring automated backup retention for Azure SQL Managed Instance. The compliance policy requires that you be able to restore a database to any point within the last 90 days, and that you keep backups for a minimum of 7 years for auditing purposes. Which backup retention policy should you configure?

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

Accepted Answer

Set PITR retention to 35 days and configure a long-term retention (LTR) policy to keep weekly backups for 7 years.. Azure SQL Managed Instance's point-in-time restore (PITR) retention is capped at 35 days, so to meet the 90-day restore requirement, you must combine a 35-day PITR policy with a long-term retention (LTR) policy that keeps weekly full backups for 7 years. LTR allows you to restore to any point within the LTR window by using full, differential, and log backups, satisfying both the 90-day point-in-time restore and the 7-year auditing compliance.

Answer

Set point-in-time restore (PITR) retention to 90 days.

Answer

Set PITR retention to 90 days and configure geo-replication to achieve the 7-year retention.

Answer

Configure geo-redundant backup storage with a retention of 90 days.

Question 2

You are reviewing a PowerShell script that configures auditing for an Azure SQL Database. The script sets an audit rule with the specified parameters. After running the script, you notice that SELECT operations are not being audited. What is the most likely cause?

Accepted Answer

The AuditActionGroup specified does not capture SELECT operations.. The script likely specifies an AuditActionGroup that does not include the group responsible for capturing SELECT operations. In Azure SQL Database auditing, SELECT operations are captured by the SUCCESSFUL_SCHEMA_OBJECT_ACCESS_GROUP or similar action groups. If the configured AuditActionGroup omits this group, SELECT statements will not be logged, even though other operations may be audited correctly.

Answer

The retention days are set too low, causing logs to be overwritten.

Answer

The storage endpoint is incorrectly formatted.

Answer

The storage account access key is invalid.

Question 3

You are the database administrator for a company that uses Azure SQL Database. The company has a strict security policy requiring that all database connections be encrypted using TLS 1.2 or higher and that the server certificate be validated to prevent man-in-the-middle attacks. Additionally, the company wants to ensure that only applications running on Azure virtual machines (VMs) in a specific virtual network (VNet) can access the database. The VMs use a subnet named 'AppSubnet'. You have configured the following: 
1. The server 'Allow Azure Services' setting is OFF. 
2. A virtual network rule is added for 'AppSubnet' with the service endpoint for Microsoft.Sql enabled. 
3. The server firewall has no other rules. 
4. The 'Minimum TLS version' is set to 1.2. 
5. All client applications are configured to use 'Encrypt=True' and 'TrustServerCertificate=False' in their connection strings.

After deployment, you discover that connections from the VMs are failing with error: 'The certificate chain was issued by an authority that is not trusted'. What is the most likely cause of this issue?

Accepted Answer

The VMs do not have the necessary root CA certificates installed to validate the Azure SQL Database server certificate.. The error 'The certificate chain was issued by an authority that is not trusted' indicates that the client cannot validate the Azure SQL Database server certificate. With 'TrustServerCertificate=False', the client must trust the server's certificate chain, which requires the root CA certificates (e.g., Baltimore CyberTrust Root) to be installed in the VM's trusted root store. Since the VMs are in Azure, they typically have these certificates, but custom images or hardened VMs may lack them, causing the validation failure.

Answer

The client applications are using an older version of .NET that does not support TLS 1.2.

Answer

The virtual network rule is missing the service endpoint for Microsoft.Sql on the subnet.

Answer

The server firewall is blocking traffic from the VNet because 'Allow Azure Services' is OFF.

Question 4

A DBA is migrating a large on-premises database to Azure SQL Database using the Data Migration Assistant (DMA). The migration fails with an error indicating that the source database contains cross-database queries. What is the best remediation?

Accepted Answer

Refactor the application to eliminate cross-database queries or use elastic query. Azure SQL Database does not support cross-database queries natively. The Data Migration Assistant (DMA) blocks migrations that rely on such queries because the PaaS service lacks the necessary server-level context. The correct remediation is to refactor the application to eliminate cross-database dependencies or use elastic query, which provides a read-only, schema-bound mechanism to query remote databases via external data sources and external tables.

Answer

Use elastic database query to reference external tables

Answer

Create a linked server in Azure SQL Database

Answer

Migrate to Azure SQL Managed Instance instead

Question 5

You manage an Azure SQL Database that is experiencing performance degradation during peak hours. You suspect that the current pricing tier is insufficient. You need to increase performance with minimal downtime. Which action should you take?

Accepted Answer

Use the Azure portal to scale up the DTU or vCore service tier.. Scaling up the DTU or vCore service tier in the Azure portal is a dynamic scaling operation that typically completes within minutes and does not require application downtime. Azure SQL Database supports online scaling, meaning the database remains available during the transition, with only a brief connection failover at the end. This directly addresses the need to increase performance during peak hours with minimal disruption.

Answer

Create a new database on a higher tier and copy data manually.

Answer

Enable read scale-out to offload read workloads.

Answer

Implement horizontal sharding across multiple databases.

Question 6

You need to ensure that all users accessing Azure SQL Database from outside the corporate network are required to use multi-factor authentication (MFA). What should you configure?

Accepted Answer

Configure a Conditional Access policy in Microsoft Entra ID.. Conditional Access policies in Microsoft Entra ID (formerly Azure AD) allow you to enforce MFA based on network location, device state, or risk level. By configuring a policy that targets the Azure SQL Database application and requires MFA for all access from outside the corporate network, you meet the requirement without altering the database or server configuration.

Answer

Enable Azure RBAC for the SQL server.

Answer

Create an Azure Policy to require MFA.

Answer

Turn on Transparent Data Encryption (TDE).

Question 7

You are configuring security for an Azure SQL Database. You need to ensure that only traffic from a specific virtual network and a specific set of public IP addresses can connect to the database. Which two features should you enable?

Accepted Answer

VNet service endpoints and firewall rules. To restrict access to an Azure SQL Database to traffic from a specific virtual network and a specific set of public IP addresses, you need to combine VNet service endpoints with firewall rules. VNet service endpoints allow you to restrict inbound traffic from a specific subnet in a virtual network, while firewall rules (IP-based) allow you to specify allowed public IP address ranges. Together, they provide a layered network security approach that meets the requirement.

Answer

Microsoft Entra ID authentication and firewall rules

Answer

Advanced Threat Protection and VNet service endpoints

Answer

Private endpoint and VNet service endpoints

Question 8

You need to audit all successful and failed login attempts on an Azure SQL Database. Which feature should you enable?

Accepted Answer

SQL Server Audit with a server-level audit specification that includes SUCCESSFUL_LOGIN_GROUP and FAILED_LOGIN_GROUP.. SQL Server Audit is the correct feature because it allows you to capture both successful and failed login attempts at the server level by defining a server audit specification that includes the SUCCESSFUL_LOGIN_GROUP and FAILED_LOGIN_GROUP audit action groups. These groups specifically log authentication events, which is exactly what is needed to audit all login attempts. Microsoft Defender for SQL, Vulnerability Assessment, and regulatory compliance dashboards do not provide granular login event auditing.

Answer

Microsoft Defender for SQL threat detection.

Answer

SQL Vulnerability Assessment.

Answer

Enable Microsoft Defender for Cloud's regulatory compliance dashboard.

Question 9

You are reviewing a JSON representation of an Azure SQL Database firewall rule. What is the effect of this rule?

Accepted Answer

Allows all IP addresses from 10.0.0.0 to 10.0.0.255.. The JSON representation of the Azure SQL Database firewall rule with startIpAddress '10.0.0.0' and endIpAddress '10.0.0.255' defines a range that allows all IP addresses from 10.0.0.0 to 10.0.0.255 inclusive. Azure SQL Database firewall rules use inclusive IP range matching, so any client with an IP in that range is permitted to connect, provided the rule is enabled.

Answer

Blocks all IP addresses from 10.0.0.0 to 10.0.0.255.

Answer

Allows all IP addresses except 10.0.0.0 to 10.0.0.255.

Answer

Allows only the IP address 10.0.0.0.

Question 10

You need to audit all successful and failed login attempts to an Azure SQL Database. Which feature should you enable?

Accepted Answer

Azure SQL Auditing. Azure SQL Auditing is the correct feature because it tracks database events, including both successful and failed login attempts, and writes them to an audit log in your Azure Storage account, Log Analytics workspace, or Event Hubs. This meets the requirement to audit all authentication events for security and compliance purposes.

Answer

Transparent Data Encryption (TDE)

Answer

SQL Vulnerability Assessment

Answer

Diagnostic settings

Question 11

You need to audit all successful and failed login attempts to an Azure SQL Database server for compliance. The audit logs must be stored in a Log Analytics workspace for analysis and retention. Which two actions should you perform? (Choose two.)

Accepted Answer

Enable auditing on the Azure SQL Database server and configure the audit destination as Log Analytics.. Azure SQL Database server-level auditing can be configured to send audit logs directly to a Log Analytics workspace, which is required for compliance analysis and retention. This captures both successful and failed login attempts at the server level, meeting the audit requirement without needing per-database configuration.

Answer

Use SQL Server Audit to capture login events.

Answer

Enable auditing on each database individually.

Answer

Enable login auditing by running ALTER SERVER CONFIGURATION SET AUDIT_LOGIN_EVENTS.

Question 12

You have an Azure SQL Database that is used by a reporting application. Reports run slowly during the day. You notice that the database is at the S1 tier. You need to improve report performance without changing application code. What should you do?

Accepted Answer

Scale up the database to a higher service tier.. Scaling up to a higher service tier (e.g., S2 or S3) provides more DTUs, which directly addresses the resource bottleneck and improves report performance without changing application code. Option A is incorrect because while rebuilding fragmented indexes can help query performance, it may not be sufficient if the underlying issue is insufficient resources. Option C is incorrect because table partitioning can improve manageability and partition elimination but does not guarantee performance improvement for reports and often requires query changes. Option D is incorrect because read scale-out is only available on the Premium and Business Critical service tiers, not on the S1 tier.

Answer

Rebuild fragmented indexes.

Answer

Implement table partitioning.

Answer

Enable read scale-out to offload reports.

Question 13

Your company has a critical Azure SQL Database named SalesDB in the West US region. To meet disaster recovery requirements, you configured active geo-replication with a readable secondary replica in East US. You need to automate failover in the event of a regional outage. The solution must ensure that the failover is initiated automatically based on health metrics and that the application connection string is updated to point to the new primary. Currently, the application uses a connection string with the primary server name. You have the following requirements: minimize manual intervention, use built-in Azure features, and ensure that failover is tested regularly. You consider the following options: A) Use Azure Traffic Manager with endpoint monitoring to route traffic to the primary and automatically failover. B) Configure auto-failover group with read-write failover policy and grace period. C) Create an Azure Logic App that queries the database health and runs a PowerShell script to perform failover and update DNS. D) Use Azure Automation with a runbook that checks the database status and initiates failover via REST API. Which option best meets the requirements?

Accepted Answer

Configure auto-failover group with read-write failover policy and grace period.. Auto-failover groups are the built-in Azure feature designed specifically for automating failover of Azure SQL Database geo-replication. They support a read-write failover policy with a configurable grace period that automatically triggers failover based on health metrics, and they automatically update the connection string endpoint for applications using the failover group listener name, not the individual server name. This minimizes manual intervention and allows regular testing via manual failover without affecting the production environment.

Answer

Use Azure Traffic Manager with endpoint monitoring to route traffic to the primary and automatically failover.

Answer

Create an Azure Logic App that queries the database health and runs a PowerShell script to perform failover and update DNS.

Answer

Use Azure Automation with a runbook that checks the database status and initiates failover via REST API.

Question 14

You are a database administrator for an e-commerce company that uses Azure SQL Database to store order data. The company is implementing a new policy that requires all database access to be audited, including both successful and failed attempts. Additionally, the audit logs must be retained for at least one year for compliance purposes. You need to configure auditing for the database. What should you do?

Accepted Answer

Enable auditing on the database, set the audit log destination to a storage account, and configure retention to 365 days. Azure SQL Database auditing can be configured at the database level to send audit logs to a storage account, which allows you to set a retention policy of 365 days directly on the audit configuration. This meets the requirement to audit both successful and failed attempts and retain logs for at least one year for compliance.

Answer

Use the 'Audit Logs' feature in the Azure portal to enable auditing and set retention to 365 days

Answer

Enable auditing on the database and configure it to send logs to a Log Analytics workspace, then set the retention in Log Analytics to 365 days

Answer

Enable auditing on the server and configure it to send logs to an event hub

Question 15

You are reviewing an ARM template for creating a new Azure SQL Database. The template uses the above JSON. The source database 'db1' was created on 2025-03-01 and has a current backup retention of 7 days. The restore point is 2025-03-15T10:00:00Z. What will be the result of deploying this template?

Accepted Answer

The deployment will succeed and create a new database restored to the specified point in time.. The ARM template specifies a restore point of 2025-03-15T10:00:00Z. Azure SQL Database point-in-time restore (PITR) uses a sliding window of the configured backup retention period (7 days) backward from the current date. Assuming the current date is 2025-03-22, the retention window covers from 2025-03-15 to 2025-03-22. The restore point falls exactly at the start of this window, so it is valid. The deployment will succeed and create a new database restored to that exact timestamp.

Answer

The deployment will fail because the restore point is older than 7 days.

Answer

The deployment will succeed but the restore point will be ignored and the latest backup will be used.

Answer

The deployment will fail because the source database is not geo-replicated.