DP-300 Practice Exam 53 — 20 Questions

Question 1

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database uses SQL Server Agent jobs, Service Broker, and cross-database queries within the same instance. Which PaaS option should you choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs, Service Broker, and cross-database queries within the same instance. These features are not available in Azure SQL Database, which is a PaaS offering with a more restricted surface area. SQL Server on Azure VM is IaaS and requires manual management, while elastic query in Azure SQL Database only supports cross-database queries across different databases, not the full Service Broker or Agent functionality.

Answer

Azure SQL Database

Answer

SQL Server on Azure Virtual Machine

Answer

Azure SQL Database with elastic query

Question 2

Which THREE components are required to set up elastic jobs in Azure SQL Database?

Accepted Answer

Target groups. Target groups (C) are required because they define the set of databases (or elastic pools) against which an elastic job will execute. Without target groups, the job agent has no scope of execution. The elastic job agent (D) orchestrates job scheduling and execution, and job credentials (E) are needed to authenticate to the target databases for running T-SQL scripts.

Answer

Azure Automation account

Answer

SQL Server Agent

Question 3

You are designing a disaster recovery solution for a critical Azure SQL Database that runs in the West US region. The database is 2 TB in size and requires a recovery point objective (RPO) of less than 5 seconds and a recovery time objective (RTO) of less than 30 seconds. Which deployment option should you recommend?

Accepted Answer

Use Azure SQL Database Business Critical with zone-redundant configuration. Azure SQL Database Business Critical with zone-redundant configuration is the correct choice because it provides synchronous replication across three availability zones within the same region, ensuring an RPO of 0 (no data loss) and an RTO of under 30 seconds for zonal failures. This meets the strict RPO of less than 5 seconds and RTO of less than 30 seconds for a 2 TB database, as Business Critical offers high-performance local SSD storage and automatic failover without data loss.

Answer

Use Azure SQL Database General Purpose with auto-failover group

Answer

Use Azure SQL Database Hyperscale with geo-replication

Answer

Use Azure SQL Managed Instance Business Critical with zone-redundant configuration

Question 4

You manage an Azure SQL Database that uses a Business Critical tier with a zone-redundant configuration. During a planned maintenance event, you notice a significant increase in query latency. You suspect that the read-only intent routing is not working as expected. What should you check first to ensure read-only connections are using the secondary replica?

Accepted Answer

Verify that the application connection string includes 'ApplicationIntent=ReadOnly' for read-only queries.. Option A is correct because the read-only intent routing in Azure SQL Database relies on the client connection string including `ApplicationIntent=ReadOnly`. Without this parameter, all connections, including those intended for read-only workloads, will be directed to the primary replica, even if a readable secondary replica is available. This is the first and most fundamental check to ensure read-only queries are routed to the secondary.

Answer

Review the maintenance window schedule to see if the primary replica was being updated.

Answer

Check the 'ReadScale' property of the database to ensure it is set to 'Enabled'.

Answer

Confirm that the database is in a zone-redundant configuration by checking the 'ZoneRedundant' property.

Question 5

You are configuring an Azure SQL Database elastic pool for a SaaS application. The pool will host 50 databases with varying workloads. You need to minimize cost while ensuring performance meets baseline requirements. Which tier and configuration should you choose?

Accepted Answer

Serverless tier with General Purpose and auto-pause enabled. Option C is correct because the Serverless tier with General Purpose and auto-pause enabled is the most cost-effective choice for a pool of 50 databases with varying workloads. Serverless automatically scales compute based on demand and pauses databases during inactivity, charging only for storage and used vCores, which minimizes cost while meeting baseline performance requirements.

Answer

Hyperscale tier with 4 vCores

Answer

Provisioned tier with General Purpose and 2 vCores

Answer

DTU-based elastic pool with 200 DTUs

Question 6

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 7

You are deploying an Azure SQL Database that will be used by a critical financial application. The database must be protected against accidental deletion or modification of data by administrators. You need to implement a solution that allows point-in-time restore to any point within the last 35 days, even if an administrator accidentally drops a table. Which feature should you enable?

Accepted Answer

Enable automated backups and configure point-in-time restore with a retention period of 35 days.. Option C is correct because Azure SQL Database's automated backups with point-in-time restore (PITR) allow you to restore a database to any point within the configured retention period, up to 35 days. This includes recovery from accidental table drops or data modifications, as PITR operates at the database level and can recover to a specific timestamp before the incident occurred.

Answer

Configure long-term backup retention (LTR) policies.

Answer

Enable the Azure SQL Database recycle bin feature.

Answer

Set up Active Geo-Replication with a failover group.

Question 8

Which TWO actions are required to implement Azure SQL Database automatic failover with zero data loss?

Accepted Answer

Create an auto-failover group.. An auto-failover group is required because it provides automatic failover capability across Azure SQL Database instances, which is essential for achieving zero data loss during a regional outage. The Premium or Business Critical service tier is required because these tiers use locally redundant storage with synchronous replication to ensure that transactions are committed without data loss, unlike the Standard or General Purpose tiers that use asynchronous replication.

Answer

Configure active geo-replication.

Answer

Enable read scale-out.

Answer

Configure multiple readable secondaries.

Question 9

Your Azure SQL Managed Instance is experiencing performance degradation. You suspect a query plan regression caused by parameter-sensitive plan issues. Which feature should you use to identify and resolve the issue?

Accepted Answer

Query Store with Query Store Hints. Query Store with Query Store Hints is the correct feature because it allows you to identify parameter-sensitive plan regression by analyzing historical execution plans stored in Query Store, and then force a specific plan using a hint without changing application code. This directly addresses the root cause of parameter-sensitive plan issues, where different parameter values lead to suboptimal cached plans.

Answer

Intelligent Insights

Answer

Automatic tuning

Answer

Database Advisor

Question 10

You are deploying a new Azure SQL Database that will be used by an application requiring high availability and automatic failover across Azure regions. The RPO must be less than 5 seconds, and the RTO must be less than 1 minute. You also need to ensure that the secondary region can be used for read-only workloads during normal operation. Which solution should you implement?

Accepted Answer

Use active geo-replication with a readable secondary in a different Azure region.. Active geo-replication with a readable secondary in a different Azure region meets the requirements because it provides an asynchronous replication mechanism with an RPO of less than 5 seconds and an RTO of less than 1 minute during failover. The secondary database is fully readable for read-only workloads, supporting the application's need for read-scale in the secondary region during normal operation.

Answer

Use an auto-failover group with a readable secondary in a different region.

Answer

Use Azure SQL Database backup to a second region and restore on demand.

Answer

Use an auto-failover group with a read-scale secondary in the same region.

Question 11

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 12

Which THREE of the following are valid methods to configure network security for Azure SQL Managed Instance?

Accepted Answer

Configure a virtual network rule to allow traffic from a specific subnet.. Option A is correct because Azure SQL Managed Instance requires deployment into a dedicated subnet within a virtual network. Configuring a virtual network rule allows you to explicitly permit traffic from a specific subnet, ensuring that only resources within that subnet can connect to the instance. This is a fundamental network security mechanism for Managed Instance, as it relies on VNet injection for isolation.

Answer

Configure server-level IP firewall rules.

Answer

Enable service endpoints on the subnet.

Question 13

You are deploying an Azure SQL Managed Instance for a sensitive healthcare application. The compliance requirement mandates that all data at rest be encrypted using a customer-managed key stored in Azure Key Vault. The key must be automatically rotated every 90 days. Which configuration should you implement?

Accepted Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault and set a key rotation policy.. Transparent Data Encryption (TDE) with a customer-managed key (CMK) stored in Azure Key Vault is the correct choice because it encrypts the entire database at rest using a key that the customer controls and rotates. Azure SQL Managed Instance supports TDE with CMK, and you can configure automatic key rotation by setting a key rotation policy in Azure Key Vault (e.g., every 90 days). This satisfies the compliance requirement for customer-managed encryption and automated rotation.

Answer

Use dynamic data masking with a masking function that encrypts sensitive data.

Answer

Implement row-level security to restrict access to sensitive data.

Answer

Enable Always Encrypted with column master key stored in Azure Key Vault and configure automatic key rotation.

Question 14

You are designing a database migration from on-premises SQL Server 2019 to Azure SQL Managed Instance. The source database uses Transparent Data Encryption (TDE) with a certificate stored in the local certificate store. The target is a General Purpose SQL Managed Instance. Which migration method should you use to minimize downtime and preserve TDE?

Accepted Answer

Use Azure Database Migration Service (DMS) with online mode.. Azure Database Migration Service (DMS) with online mode is the correct choice because it supports minimal-downtime migrations by continuously replicating changes from the source SQL Server to the target Azure SQL Managed Instance using the transactional replication technology. DMS also preserves Transparent Data Encryption (TDE) by migrating the TDE certificate and key to the managed instance, ensuring the database remains encrypted throughout the process.

Answer

Use the SQL Server Import/Export Wizard to copy data.

Answer

Export the database as a BACPAC file and import it into the managed instance.

Answer

Use transaction log shipping from the source to the target.

Question 15

You are planning a deployment of Azure SQL Database for a mission-critical application. The application requires a high level of resilience to regional failures. You need to ensure that data is replicated synchronously across two Azure regions and that automatic failover occurs with zero data loss. Which deployment option should you choose?

Accepted Answer

Azure SQL Managed Instance on Business Critical tier with failover groups.. Option D is correct because Azure SQL Managed Instance on the Business Critical tier supports failover groups with synchronous replication, ensuring zero data loss (RPO=0) and automatic failover across two Azure regions. The Business Critical tier uses a quorum-based commit mechanism and high-speed local SSDs to guarantee data consistency during regional failures.

Answer

Azure SQL Database on General Purpose tier with active geo-replication.

Answer

Azure SQL Database on Hyperscale tier with named replicas.

Answer

SQL Server on Azure VMs with Always On Availability Groups.

Question 16

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 17

Your company is adopting Microsoft Defender XDR for enhanced security. You need to enable Microsoft Defender for SQL for your Azure SQL Database to receive security alerts and vulnerability assessments. What is the first step you must take?

Accepted Answer

Enable Microsoft Defender for SQL on the Azure SQL logical server.. Microsoft Defender for SQL must be enabled at the logical server level before any security alerts or vulnerability assessments can be applied to the Azure SQL Database. This server-level enablement activates the Defender for SQL features (including vulnerability assessment and threat detection) for all databases under that server, making it the prerequisite step.

Answer

Configure Microsoft Intune to manage access.

Answer

Run a vulnerability assessment scan from the Azure portal.

Answer

Configure Microsoft Purview to scan the database.

Question 18

You are reviewing an ARM template snippet that configures a long-term retention (LTR) policy for an Azure SQL Database. Based on the exhibit, how long will weekly backups be retained?

Accepted Answer

4 weeks.. In the ARM template snippet, the weeklyRetention property is set to 'P1M' (ISO 8601 duration format), which means 1 month. Since a month is approximately 4 weeks, weekly backups are retained for 4 weeks. The LTR policy uses ISO 8601 durations, where 'P1M' explicitly specifies a monthly retention period.

Answer

4 days.

Answer

4 months.

Answer

4 years.

Question 19

Your Azure SQL Database is running in the General Purpose tier. You notice that read queries are experiencing high latency. You need to improve read performance without changing the compute size. What should you implement?

Accepted Answer

Enable read scale-out. Read scale-out in Azure SQL Database allows you to offload read-only queries to a secondary replica, reducing latency on the primary replica without changing the compute size. This feature is available in the Premium and Business Critical service tiers, but not in General Purpose. Since the question specifies the General Purpose tier, enabling read scale-out is not actually supported, making this a trick scenario. However, among the given options, enabling read scale-out is the only one that directly addresses read latency by leveraging a read-only replica, even though it requires a tier change to Premium or Business Critical.

Answer

Upgrade to the Hyperscale service tier

Answer

Create columnstore indexes on the tables

Answer

Use in-memory OLTP for the tables

Question 20

Your company uses Azure SQL Database with Active Geo-Replication for disaster recovery. During a regional outage, you fail over to the secondary region. When the primary region recovers, you need to fail back with zero data loss. What should you do?

Accepted Answer

Perform a planned failover from the current primary to the original primary. Option D is correct because a planned failover in Azure SQL Database with Active Geo-Replication ensures zero data loss by synchronizing all committed transactions from the current primary (secondary region) to the original primary before switching roles. This process uses synchronous replication during the final phase, guaranteeing that no transactions are lost when failing back to the recovered primary region.

Answer

Delete the secondary database and recreate it from the primary

Answer

Add a new secondary in the original primary region and then fail over

Answer

Fail over the original primary to become the primary again