DP-300 Practice Test 13 — 15 Questions

Question 1

You are deploying a new Azure SQL Database for an application that will store sensitive financial data. The compliance team requires that the database be configured to automatically detect and alert on anomalous access patterns, and that all queries be logged for auditing. Which services should you enable?

Accepted Answer

Azure Defender for SQL and SQL Auditing. Option C is correct because Azure Defender for SQL provides anomaly detection and alerts for suspicious access patterns (e.g., SQL injection, brute force), while SQL Auditing captures all queries and events for compliance logging. Together, they meet the requirements for automatic detection and full query auditing without additional services.

Answer

Azure Purview and vulnerability assessment

Answer

Microsoft Sentinel and SQL Auditing

Answer

SQL Server auditing and vulnerability assessment

Question 2

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.. Option D is correct because the ARM template specifies a restore point of 2025-03-15T10:00:00Z, which is within the 7-day backup retention period (the source database was created on 2025-03-01, so backups are available from 2025-03-01 to 2025-03-22). Azure SQL Database supports point-in-time restore (PITR) to any point within the configured backup retention window, and the deployment will create a new database restored to that exact timestamp. The restore point is valid and the template will succeed.

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.

Question 3

Your company is deploying a new application that uses an Azure SQL Database. The security policy requires that all connections use Microsoft Entra ID authentication and that no SQL authentication users are created. Which server-level setting should you enforce?

Accepted Answer

Enable 'Azure AD-only authentication' on the Azure SQL logical server.. Option C is correct because enabling 'Azure AD-only authentication' on the Azure SQL logical server enforces that all connections must use Microsoft Entra ID authentication and blocks any SQL authentication attempts, even if SQL logins exist. This setting directly meets the security policy requirement that no SQL authentication users are created and all connections use Entra ID.

Answer

Set the database to use contained database authentication.

Answer

Configure a server-level firewall rule to allow only Entra ID IPs.

Answer

Set the Entra ID admin for the server and disable SQL authentication.

Question 4

You are deploying an Azure SQL Managed Instance for a financial application. The compliance requirements dictate that all data at rest must be encrypted using TDE with a customer-managed key stored in Azure Key Vault. Additionally, the key must be accessible only from the managed instance's virtual network. How should you configure the Key Vault firewall and managed instance?

Accepted Answer

Enable the Key Vault firewall, select 'Allow trusted Microsoft services', and configure the managed instance with a system-assigned managed identity. Option C is correct because enabling the Key Vault firewall with 'Allow trusted Microsoft services' and configuring the managed instance with a managed identity ensures access only from trusted services. Option A is wrong because disabling the firewall would allow public access. Option B is wrong because granting the managed instance's public IP is not recommended and may not work. Option D is wrong because disabling the firewall and using a service principal is less secure.

Answer

Disable the Key Vault firewall and assign the managed instance a service principal

Answer

Enable the Key Vault firewall and add the managed instance's public IP address to the firewall rules

Answer

Disable the Key Vault firewall and use a user-assigned managed identity for the managed instance

Question 5

You have an Azure SQL Database with Query Store enabled. You notice that a critical stored procedure has regressed in performance. You need to force a previous, better-performing execution plan for that query. What should you do?

Accepted Answer

Execute sys.sp_query_store_force_plan with the query_id and plan_id.. Option D is correct because `sys.sp_query_store_force_plan` is the dedicated system stored procedure in Azure SQL Database that forces the Query Store to use a specific execution plan for a given query. When a stored procedure's performance regresses, you identify the query_id and plan_id of the better-performing plan from Query Store views and execute this procedure to pin that plan, overriding the optimizer's choice.

Answer

Disable and re-enable Query Store to reset the plans.

Answer

Execute sys.sp_query_store_set_hints to add a query hint.

Answer

Use sys.dm_db_tuning_recommendations to apply a plan.

Question 6

You have an Azure SQL Database that experiences performance degradation due to high concurrency and blocking. You need to implement a solution that offloads reporting queries to a read-only copy of the database without impacting the primary workload. What should you configure?

Accepted Answer

Add the database to an auto-failover group and use the readable secondary for reporting. Option A is correct because adding the database to an auto-failover group and using the readable secondary for reporting offloads read-only workloads to a synchronized replica without impacting the primary. The readable secondary is a built-in feature of Azure SQL Database that provides a read-only endpoint, and it is automatically kept in sync via transactional replication, ensuring near-real-time data for reporting while the primary handles write-heavy transactions.

Answer

Create a database snapshot and use it for reporting

Answer

Configure a geo-replica and use it for reporting

Answer

Enable read scale-out on the database by adding a secondary replica

Question 7

You are a database administrator for a global e-commerce company. The company runs a critical transactional workload on an Azure SQL Managed Instance (MI) in the West US region. The database is approximately 1 TB in size and uses the Business Critical tier with zone redundancy enabled. The application requires read latency for reporting queries to be under 10 ms. The company is expanding to Europe and needs to deploy a secondary copy of the database in the North Europe region to serve read-only reporting traffic for European users. The secondary must be kept in sync with the primary with a recovery point objective (RPO) of less than 5 seconds. Additionally, the solution must support automatic failover if the primary region becomes unavailable. You need to design the disaster recovery and read scaling solution. Which approach should you take?

Accepted Answer

Create an auto-failover group with the primary in West US and add a readable secondary in North Europe, using synchronous commit. Option C is correct because Azure SQL Managed Instance supports auto-failover groups with a readable secondary in a paired region. By configuring synchronous commit within the failover group, you achieve an RPO of less than 5 seconds (typically 0-2 seconds) while the secondary in North Europe can serve read-only traffic with low latency. The auto-failover group also provides automatic failover if the primary region becomes unavailable, meeting both the read scaling and disaster recovery requirements.

Answer

Create a geo-replica of the MI in North Europe and manually failover when needed

Answer

Deploy a separate managed instance in North Europe and use transactional replication to keep it in sync

Answer

Create an auto-failover group with the primary in West US and secondary in North Europe, but do not make the secondary readable

Question 8

You need to create an Azure SQL Database that automatically scales storage up to 4 TB. Which service tier should you select?

Accepted Answer

Business Critical. Business Critical is the correct service tier because it supports up to 4 TB of storage with provisioned compute, high-performance local SSD storage, and built-in high availability. Standard tier maxes out at 1 TB, Basic at 2 GB, and Hyperscale is designed for up to 100 TB but uses a different architecture with page servers and log services, not the fixed 4 TB limit.

Answer

Standard

Answer

Hyperscale

Answer

Basic

Question 9

Your company has an Azure SQL Database configured with Active Geo-Replication between two regions. The primary database is experiencing performance degradation due to a sudden increase in write-intensive workloads. You need to minimize downtime and ensure no data loss. What should you do?

Accepted Answer

Initiate a planned failover to the secondary region. A planned failover (Option D) is correct because Active Geo-Replication supports a graceful, synchronous failover that promotes the secondary database to become the new primary without data loss. This is achieved by first replicating all pending transactions to the secondary, ensuring zero data loss, and then switching roles with minimal downtime. In contrast, a forced failover (Option B) would cause data loss because it does not wait for pending transactions to be replicated.

Answer

Enable read scale-out on the primary database

Answer

Initiate a forced failover to the secondary region

Answer

Increase the service tier of the primary database

Question 10

Which THREE factors should you consider when choosing between Azure SQL Database single database and Azure SQL Managed Instance for a new application?

Accepted Answer

Need for cross-database queries and transactions.. Option B is correct because Azure SQL Database single database does not support cross-database queries and transactions, whereas Azure SQL Managed Instance provides full SQL Server instance-level features, including cross-database queries and transactions via linked servers or within the same instance. This makes Managed Instance the appropriate choice when the application requires querying or updating multiple databases in a single transaction.

Answer

Requirement for elastic pool scalability.

Answer

Need for automated backups with point-in-time restore.

Question 11

Which TWO features can help you monitor and troubleshoot a query performance issue in Azure SQL Database without making changes to the application code?

Accepted Answer

Enabling Automatic Tuning to force plan corrections.. Option B is correct because Automatic Tuning in Azure SQL Database can automatically identify and force plan corrections for regressed query execution plans without requiring any application code changes. It uses the Query Store to detect plan regressions and applies the last known good plan, improving query performance transparently. Option E is correct because Query Store captures query execution statistics and plan history, allowing you to review regressed queries and manually force a better plan without modifying the application.

Answer

Using sp_setapprole to set application roles.

Answer

Implementing Always Encrypted for sensitive columns.

Answer

Using Azure Data Migration Assistant for performance assessment.

Question 12

Which TWO are valid ways to secure access to an Azure SQL Database?

Accepted Answer

Configure a Virtual Network service endpoint. Option B is correct because a Virtual Network service endpoint extends your virtual network private address space and the identity of your VNet to Azure SQL Database over a direct connection. This allows you to secure access by limiting connectivity to only traffic originating from a specific subnet in your VNet, effectively blocking public internet access while using the Azure backbone network.

Answer

Use IPsec VPN from on-premises

Answer

Join the database to an on-premises Active Directory domain

Answer

Disable all firewall rules and rely only on authentication

Question 13

You are migrating a 10-TB SQL Server database to Azure SQL Database Hyperscale. The source database has multiple filegroups and uses full-text indexing. The migration must be completed with minimal downtime. Which migration method should you use?

Accepted Answer

Use the Azure SQL Migration extension in Azure Data Studio with online migration mode. The Azure SQL Migration extension in Azure Data Studio with online migration mode is the correct choice because it supports minimal-downtime migrations for large databases (10 TB) with complex features like multiple filegroups and full-text indexing. It uses the Data Migration Service (DMS) to continuously replicate changes from the source to the target Azure SQL Database Hyperscale, allowing you to cut over with only a brief pause.

Answer

Set up transactional replication from on-premises to Azure

Answer

Use Azure Data Factory to copy data

Answer

Export a BACPAC file and import it to Azure

Question 14

Your company uses Azure SQL Database with a server-level Microsoft Entra ID admin. You need to implement a solution where database-level roles are automatically assigned based on the user's group membership in Microsoft Entra ID. What should you use?

Accepted Answer

Create database users from Microsoft Entra ID groups and grant roles to those users.. Option C is correct because Azure SQL Database supports creating database users mapped to Microsoft Entra ID (formerly Azure AD) groups. By creating a user for the Entra ID group and then granting database roles to that group user, all members of the group automatically inherit the assigned permissions. This directly satisfies the requirement for role assignment based on group membership without custom scripting or triggers.

Answer

Use Azure RBAC to assign roles to the Entra ID groups.

Answer

Configure a SQL Server Agent job to update database roles based on group membership.

Answer

Create a DDL trigger that assigns roles when users log in.

Question 15

Your Azure SQL Managed Instance is configured to allow connections only from a specific virtual network. You need to ensure that clients from on-premises can connect using a point-to-site VPN. What additional configuration is required?

Accepted Answer

Configure a point-to-site VPN connection on the virtual network gateway and ensure the gateway subnet is in the same VNet as the managed instance.. A point-to-site VPN connection on the virtual network gateway allows individual on-premises clients to securely connect to the Azure VNet over SSTP or IKEv2. Since the managed instance is deployed inside a subnet of that VNet and its endpoint is restricted to the VNet, the VPN tunnel provides the necessary network path for on-premises clients to reach the instance without exposing it to the public internet.

Answer

Configure a private endpoint for the managed instance.

Answer

Deploy Azure Bastion in the same VNet.

Answer

Add the on-premises public IP address to the managed instance firewall rules.