DP-300 Practice Test 17 — 15 Questions

Question 1

You are a database administrator for a financial services company. You have deployed an Azure SQL Database and configured auditing using the JSON policy shown in the exhibit. After a security incident, you need to review all successful and failed login attempts to the database. However, you notice that login events are not being captured in the audit logs. What is the most likely reason?

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

Accepted Answer

The audit actions and groups do not include login events. The JSON policy shown in the exhibit defines audit actions and groups, but it does not include the `SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP` or `FAILED_DATABASE_AUTHENTICATION_GROUP` action groups. These groups are required to capture both successful and failed login attempts (authentication events) in Azure SQL Database. Without them, login events are not recorded in the audit logs, regardless of other settings.

Answer

The audit logs are being sent to Azure Monitor instead of blob storage

Answer

The retention days are set too high, causing logs to be truncated

Answer

Auditing is disabled at the server level

Question 2

You need to automate the deployment of an Azure SQL Database using Infrastructure as Code. The deployment should include the database, firewall rules, and threat detection settings. Which tool should you use?

Accepted Answer

Azure Resource Manager templates. Azure Resource Manager (ARM) templates are the native IaC for Azure. Azure Automation runbooks can deploy but are not declarative. Azure CLI can script deployments but is imperative. Azure Policy is for governance, not deployment.

Answer

Azure CLI scripts

Answer

Azure Automation runbooks

Answer

Azure Policy

Question 3

You are using Azure SQL Managed Instance with auto-failover groups. The primary region is East US, secondary is West US. The primary region experiences a full outage. How does the failover group behave?

Accepted Answer

The failover group automatically fails over to West US and the read-write endpoint now points to West US.. Auto-failover groups automatically fail over to the secondary region with automatic read-write endpoint switch. Option B is incorrect because automatic failover does not require manual initiation; it occurs after the grace period. Option C is incorrect because the read-only listener is also updated to point to the new secondary (formerly primary) after failover. Option D is incorrect because the failover group will fail over automatically, not remain in the primary region.

Answer

You must manually initiate failover via the portal.

Answer

The read-only listener continues to point to West US.

Answer

The failover group remains in primary region and does not fail over.

Question 4

You are designing a disaster recovery strategy for an Azure SQL Database that uses the Business Critical tier. The primary region is East US, and you need to ensure automatic failover with zero data loss in case of a regional outage. The secondary region must be West US. You also need to minimize latency for read-only workloads in the secondary region. What should you implement?

Accepted Answer

Create a failover group with the primary in East US and a readable secondary in West US.. A failover group with a readable secondary in the Business Critical tier provides automatic failover with zero data loss because it uses synchronous replication. The readable secondary in West US minimizes latency for read-only workloads by allowing direct connections to the secondary replica. This meets all requirements: automatic failover, zero data loss, and low-latency reads in the secondary region.

Answer

Create a failover group with the primary in East US and a non-readable secondary in West US using Basic tier.

Answer

Configure active geo-replication with a readable secondary in West US.

Answer

Configure active geo-replication with a readable secondary in West US using Standard tier.

Question 5

Your organization uses Azure SQL Managed Instance and needs to implement a defense-in-depth strategy. Which THREE security controls should you implement? (Choose three.)

Accepted Answer

Enable advanced threat protection using Microsoft Defender for Cloud.. Enabling advanced threat protection via Microsoft Defender for Cloud provides continuous monitoring for suspicious activities, such as SQL injection or brute-force attacks, and generates security alerts that integrate with Azure Sentinel or SIEM solutions. This is a critical layer in a defense-in-depth strategy, as it detects and responds to threats at the database level.

Answer

Create columnstore indexes on large tables to improve query performance.

Answer

Create application roles in each database to manage permissions.

Question 6

You are the DBA for a company that uses Azure SQL Database. You need to ensure that only authorized users can view sensitive columns (e.g., salary) in the Employees table. You want to obfuscate the data for certain users but allow full access to HR managers. Which feature should you use?

Accepted Answer

Dynamic Data Masking. Dynamic Data Masking (DDM) is the correct choice because it obfuscates sensitive columns (e.g., salary) in query results for unauthorized users while allowing full visibility for authorized users like HR managers. DDM applies masking rules at the database level without modifying the underlying data, making it ideal for scenarios where you need to limit exposure of sensitive data to certain roles without changing the application code.

Answer

Always Encrypted

Answer

Row-Level Security (RLS)

Answer

Transparent Data Encryption (TDE)

Question 7

You have an Azure SQL Database that uses the General Purpose service tier. The database is critical and you need to protect against a regional outage with an RPO of 1 hour and an RTO of 12 hours. What is the most cost-effective solution?

Accepted Answer

Use geo-restore with the default backup retention.. Geo-restore uses automated backups stored in geo-redundant storage (RA-GRS) at no additional cost beyond backup storage. With default backup retention (7 days), geo-restore typically achieves an RPO of 1 hour and an RTO of 12 hours, meeting the requirements cost-effectively. Option B (active geo-replication) and Option C (auto-failover group) both incur extra compute and storage costs for the secondary replica, making them more expensive. Option D (zone-redundant) protects against zonal failures within a region, not regional outages.

Answer

Enable active geo-replication.

Answer

Configure auto-failover group with a secondary in a different region.

Answer

Deploy a zone-redundant configuration.

Question 8

Refer to the exhibit. The exhibit shows an Azure role assignment with a condition. When user@contoso.com tries to read data from the database 'proddb', what will be the effect of this condition?

Accepted Answer

The user will be denied the ability to read data from the database.. The condition in the Azure role assignment explicitly denies read access to the database 'proddb' for user@contoso.com. In Azure RBAC, a deny assignment overrides any allow assignments, so even if the user has other roles granting read permissions, this condition will block all data read operations on that specific database. Therefore, the user will be denied the ability to read data from the database.

Answer

The user will be allowed to read all data from the database.

Answer

The user will be able to read the database metadata but not the data.

Answer

The user will be able to assign the role to others.

Question 9

You have an Azure SQL Database that must be automatically stopped during weekends to save costs. Which combination of Azure services can achieve this automation?

Accepted Answer

Azure Automation Runbook with PowerShell script using Suspend-AzSqlDatabase cmdlet, scheduled via Automation schedule.. Azure Automation Runbooks with the Suspend-AzSqlDatabase cmdlet provide a native, straightforward way to stop an Azure SQL Database on a schedule. Option B is incorrect because Azure Logic Apps with the SQL connector cannot execute ALTER DATABASE SET SUSPEND; suspension is not a T-SQL operation for Azure SQL Database. Option C is incorrect because Azure Functions can achieve this but require more custom code and setup compared to the simpler Automation Runbook approach. Option D is incorrect because Elastic Database Jobs are designed for executing T-SQL scripts across multiple databases, not for stopping or starting databases.

Answer

Azure Logic Apps with a recurrence trigger and the SQL connector to execute ALTER DATABASE SET SUSPEND.

Answer

Azure Functions with a timer trigger and the Azure SDK to stop the database.

Answer

Elastic Database Jobs with a T-SQL script to ALTER DATABASE SET SUSPEND.

Question 10

You are a database administrator for an Azure SQL Database. You need to ensure that only specific client IP addresses can connect to the database, while all other traffic is blocked. You also need to allow Azure services to access the database. What should you configure?

Accepted Answer

Configure server-level firewall rules to allow the specific client IP addresses and enable the 'Allow Azure services and resources to access this server' setting.. Azure SQL Database uses server-level firewall rules to control inbound access. By adding rules for specific client IP addresses and enabling the 'Allow Azure services and resources to access this server' setting, you restrict connections to only those IPs while permitting Azure internal services (e.g., Azure Logic Apps, Azure Functions) to connect. This setting leverages the Azure SQL firewall, which evaluates source IP addresses against the configured rules before allowing a connection.

Answer

Disable public network access and configure a service endpoint.

Answer

Configure a private endpoint and disable public network access.

Answer

Configure network security group (NSG) rules on the subnet where the Azure SQL Database is deployed.

Question 11

Refer to the exhibit. You are reviewing the configuration of an Azure SQL Database hyperscale service tier. The database 'SalesDB' has read scale enabled with 2 read replicas. You need to ensure that if the primary region fails, the database can be failed over to a secondary region with minimal data loss and that the read replicas in the secondary region are also available. What should you configure?

Accepted Answer

Create a named replica in a secondary region and configure it as a geo-secondary.. For Azure SQL Database Hyperscale, named replicas can be created in a secondary region. These replicas provide both read scale-out and can be promoted to primary during a regional failover, ensuring minimal data loss. Option A is incorrect because zone redundancy provides high availability within a single region, not disaster recovery across regions. Option C is incorrect because active geo-replication is not supported for Hyperscale; named replicas serve the geo-replication purpose. Option D is incorrect because failover groups are not supported for Hyperscale; named replicas are the mechanism for cross-region failover and read scaling.

Answer

Configure zone redundancy on the Hyperscale database.

Answer

Enable active geo-replication to a secondary server in a different region.

Answer

Create a failover group with a secondary Hyperscale database in another region.

Question 12

Which TWO actions should you take to achieve high availability for an Azure SQL Managed Instance with a 99.99% SLA? (Choose two.)

Accepted Answer

Use Business Critical service tier.. To achieve the 99.99% SLA for Azure SQL Managed Instance, you must use the Business Critical service tier (which provides local redundancy) and deploy the instance as zone-redundant (which protects against zone failures). Option A (automated backups with long-term retention) is for data durability and recovery, not availability. Option D (geo-replication) is for disaster recovery, not high availability within a region. Option E (General Purpose) offers a lower SLA (99.99% only with zone redundancy, but still not the highest available; typically 99.99% is achieved with Business Critical and zone redundancy). Therefore, B and C are the correct choices.

Answer

Enable automated backups with long-term retention.

Answer

Configure geo-replication to a secondary region.

Answer

Use General Purpose service tier.

Question 13

Drag and drop the steps to restore an Azure SQL Database to a point in time in the correct order.

Accepted Answer

Select the source database, then choose Restore type as Point-in-time, then specify the point in time, then name the new database, then click Create.. The restore process starts by selecting the database, then choosing the restore type, specifying the point in time, naming the new database, and finally creating it.

Answer

Select the source database, then name the new database, then choose Restore type as Point-in-time, then specify the point in time, then click Create.

Answer

Select the source database, then choose Restore type as Point-in-time, then click Create, then name the new database, then specify the point in time.

Answer

Select the source database, then specify the point in time, then choose Restore type as Point-in-time, then name the new database, then click Create.

Question 14

You have an Azure SQL Database that uses active geo-replication with a readable secondary in another region. You need to ensure that if a failover occurs, the application can automatically reconnect to the new primary with minimal code changes. What should you configure?

Accepted Answer

Implement retry logic in the application to reconnect to the secondary server after failover.. With active geo-replication, there is no automatic listener endpoint. To achieve automatic reconnection with minimal code changes, the application should implement retry logic in the connection string that includes the failover partner (secondary) and set ApplicationIntent=ReadWrite. When the primary is unavailable, the driver will automatically attempt to connect to the secondary. Option A is incorrect because manually updating DNS is not automatic and requires manual intervention, which violates the 'minimal code changes' requirement. Option B is incorrect because a failover group listener endpoint is specific to failover groups, not active geo-replication; configuring a failover group would be a different setup. Option C is incorrect because using Azure Traffic Manager introduces additional complexity and is not considered minimal code change; it requires managing a separate DNS service.

Answer

Manually update the DNS record to point to the new primary after failover.

Answer

Configure a failover group listener endpoint and use that in the connection string.

Answer

Use Azure Traffic Manager with priority routing to the secondary server.

Question 15

You are designing a disaster recovery plan for an Azure SQL Managed Instance that hosts a financial application. The application requires an RPO of 0 seconds and an RTO of 30 seconds. Which TWO configurations meet these requirements? (Choose two.)

Accepted Answer

Deploy a Business Critical instance with zone redundancy and a failover group to a secondary instance in a paired region, using the Business Critical service tier.. Options C and D are correct. To achieve an RPO of 0 seconds and an RTO of 30 seconds for Azure SQL Managed Instance, you need configurations that provide synchronous replication and rapid failover. Option C combines zone redundancy (for local high availability with zero data loss within the region) and a failover group to a secondary instance in a paired region. The failover group, when used with the Business Critical service tier and a planned failover, can synchronize all data before failing over, meeting the zero RPO requirement. Option D directly uses a failover group with the 'planned' failover option, which ensures all committed transactions are replicated before failover, achieving zero data loss and a fast RTO. Option A is incorrect because active geo-replication is not supported for SQL Managed Instance and is asynchronous. Option B is incorrect because zone redundancy alone provides high availability within a single region but does not address cross-region disaster recovery. Option E is incorrect because manual restore from backup cannot meet the 30-second RTO.

Answer

Configure active geo-replication between two Business Critical instances in different regions.

Answer

Deploy a zone-redundant Business Critical instance in a single region and rely on automatic failover.

Answer

Set up a manual failover process using a PowerShell script that restores from the latest backup.