DP-300 Plan and implement data platform resources Practice Test 4 — 15 Questions

Question 1

You are designing a disaster recovery strategy for an Azure SQL Database that supports a critical OLTP application. The database is currently deployed in the East US region. You need to ensure that failover occurs automatically with minimal data loss in the event of a regional outage. Which deployment option should you recommend?

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

Accepted Answer

Configure auto-failover groups between East US and West US.. Auto-failover groups in Azure SQL Database provide automatic, synchronous replication to a secondary region and enable automatic failover with minimal data loss (RPO of a few seconds). Active Geo-Replication alone requires manual failover, which does not meet the automatic failover requirement. Geo-restore has an RPO of up to 1 hour, and manual failover groups still require manual initiation.

Answer

Enable Active Geo-Replication with a readable secondary in West US.

Answer

Deploy a failover group with manual failover.

Answer

Use geo-restore to recover the database in West US.

Question 2

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 3

Your company plans to migrate several on-premises SQL Server databases to Azure SQL Database. The databases are used by different applications, each with varying performance requirements. You need to choose a deployment option that provides the most cost-effective solution while allowing for independent scaling of each database. Which option should you choose?

Accepted Answer

Create an Azure SQL Database elastic pool and add databases to the pool.. Azure SQL Database elastic pools allow multiple databases to share a fixed set of resources (eDTUs or vCores), enabling cost savings by pooling underutilized databases while providing the ability to independently scale each database's resource consumption within the pool. This meets the requirement for independent scaling and cost-effectiveness, as opposed to single databases that each require their own dedicated resources.

Answer

Install SQL Server on Azure Virtual Machines and host all databases on a single instance.

Answer

Migrate all databases to a single Azure SQL Managed Instance.

Answer

Deploy each database as a single Azure SQL Database with its own DTU-based tier.

Question 4

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.. Option B is correct because Azure SQL Database auditing is enabled at the server level, and the audit destination can be configured to send audit records directly to a Log Analytics workspace, which captures successful and failed login events for compliance retention and analysis. Option C is correct because diagnostic settings on the Azure SQL server (or database) are what route the SQL security audit telemetry to a Log Analytics workspace, enabling Kusto queries and long-term retention. Option A is not appropriate because SQL Server Audit is an on-premises/SQL Server feature and does not natively target Azure Log Analytics for Azure SQL Database. Option D is unnecessary and less efficient because server-level auditing already covers the databases on the server, so per-database auditing is not required. Option E is incorrect because ALTER SERVER CONFIGURATION SET AUDIT_LOGIN_EVENTS is not a valid Azure SQL Database command for this purpose.

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 5

You are planning the migration of a large on-premises SQL Server database to Azure SQL Managed Instance. The database is 500 GB in size and has a high transaction rate. You need to minimize downtime during the migration. Which migration approach should you recommend?

Accepted Answer

Use Azure Database Migration Service with online migration mode.. Azure Database Migration Service (DMS) with online migration mode uses transactional log shipping to continuously synchronize changes from the source SQL Server to Azure SQL Managed Instance, enabling near-zero downtime. This is essential for a 500 GB database with a high transaction rate, as it allows the source to remain operational during the migration and only requires a final cutover with minimal interruption.

Answer

Set up transactional replication to replicate data to SQL Managed Instance.

Answer

Perform an offline migration using Azure Database Migration Service.

Answer

Export the database as a BACPAC file and import to SQL Managed Instance.

Question 6

You need to design a business continuity plan for an Azure SQL Database that must meet an RPO of 5 seconds and an RTO of 1 hour. The database is used by a global application with users in North America and Europe. Which configuration should you implement?

Accepted Answer

Configure Active Geo-Replication with a readable secondary in a different Azure region and add to a failover group.. Active Geo-Replication with a readable secondary in a different Azure region, added to a failover group, meets the RPO of 5 seconds and RTO of 1 hour. Active Geo-Replication provides asynchronous replication with an RPO of up to 5 seconds, and failover groups enable automatic or manual failover with an RTO typically under 1 hour. The readable secondary supports read-only workloads in Europe, optimizing global application performance.

Answer

Configure auto-failover groups between two Azure regions.

Answer

Deploy the database as zone-redundant within a single region.

Answer

Use geo-restore of automated backups to recover in another region.

Question 7

Your organization requires that all Azure SQL Database deployments use the most recent version of SQL Server engine to ensure security and performance. Which deployment option automatically provides the latest engine version without requiring manual upgrades?

Accepted Answer

Azure SQL Database (single database).. Azure SQL Database (single database) is a Platform-as-a-Service (PaaS) offering where Microsoft manages the underlying SQL Server engine. Microsoft automatically applies the latest engine version updates during the service's maintenance windows, ensuring you always run a supported and current version without any manual upgrade effort. This aligns directly with the requirement for automatic, hands-off version management.

Answer

SQL Server on Azure Virtual Machines.

Answer

Azure SQL Database elastic pool.

Answer

Azure SQL Managed Instance.

Question 8

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.. 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 9

You are reviewing the firewall and virtual network rules for an Azure SQL Database server as shown in the exhibit. The server has public network access disabled. A client application in a different virtual network (vnet2) needs to connect to the database. What must you do to allow the connection?

Accepted Answer

Add a virtual network rule that references subnet2 in vnet2.. Since public network access is disabled, the server rejects all public IP-based connections. To allow a client in a different virtual network (vnet2) to connect, you must create a virtual network rule that references subnet2 in vnet2. This rule establishes a private endpoint-like connection through the Azure backbone, bypassing the public internet and honoring the disabled public network access setting.

Answer

Add the IP address of the client to the ipRules array.

Answer

Set publicNetworkAccess to 'Enabled' and add an IP firewall rule.

Answer

Create a server-level firewall rule to allow all Azure services.

Question 10

You are a database administrator for a company that runs a large e-commerce platform on Azure SQL Database. The platform experiences unpredictable traffic spikes during flash sales, causing performance degradation. You need to implement a solution that automatically scales the database resources based on demand without manual intervention. Which feature should you use?

Accepted Answer

Hyperscale service tier. The Hyperscale service tier (Option D) is correct because it provides near-instantaneous, automatic scaling of compute and storage resources in response to workload demand, without requiring manual intervention. For unpredictable traffic spikes during flash sales, Hyperscale's architecture decouples compute from storage, allowing compute nodes to scale up or down independently and transparently, while its buffer pool and page server design handle high concurrency and large data volumes efficiently.

Answer

Elastic pool

Answer

Manual scaling using Azure CLI

Answer

Serverless compute tier

Question 11

You need to deploy an Azure SQL Database that complies with a regulatory requirement to encrypt data at rest using a customer-managed key stored in Azure Key Vault. The database will be used by a line-of-business application. Which feature should you enable?

Accepted Answer

Transparent Data Encryption (TDE) with customer-managed key. Transparent Data Encryption (TDE) with a customer-managed key (CMK) in Azure Key Vault encrypts the database at rest, meeting the regulatory requirement for customer-controlled encryption keys. TDE performs real-time I/O encryption and decryption of data files, logs, and backups, and when configured with a CMK (also known as Bring Your Own Key or BYOK), the encryption key is stored and managed in Azure Key Vault, giving the customer full control over key rotation and revocation.

Answer

Azure Defender for SQL

Answer

Dynamic Data Masking

Answer

Always Encrypted

Question 12

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database is 2 TB in size and has multiple logins, jobs, and SQL Server Agent alerts. You need to choose a migration method that minimizes downtime and preserves all server-level objects. Which approach should you recommend?

Accepted Answer

Use Azure Database Migration Service with online migration. Azure Database Migration Service (DMS) with online migration is the correct choice because it supports minimal downtime by continuously synchronizing changes from the source SQL Server to the target Azure SQL Managed Instance until cutover. It also automatically migrates server-level objects such as logins, jobs, and SQL Server Agent alerts, which are critical for preserving the full environment. This method is designed for large databases (2 TB) and provides a managed, resilient migration pipeline.

Answer

Export to BACPAC and import to Managed Instance

Answer

Use transactional replication

Answer

Back up to URL and restore to Managed Instance

Question 13

You need to provision an Azure SQL Database that supports a high-availability SLA of 99.995% and provides automatic failover to a secondary region in case of a regional outage. Which deployment option should you choose?

Accepted Answer

Azure SQL Database with active geo-replication and failover group. Active geo-replication with a failover group provides an SLA of 99.995% by replicating data asynchronously to a secondary region and enabling automatic failover during a regional outage. This is the only option that meets both the high-availability SLA and cross-region disaster recovery requirement.

Answer

Azure SQL Database in Hyperscale tier

Answer

Azure SQL Database with zone-redundant configuration

Answer

Azure SQL Managed Instance with failover group

Question 14

Your organization has multiple Azure SQL Databases in an elastic pool. You need to monitor the performance of the pool and identify which databases are consuming the most resources. You want to view historical resource usage for the past month. Which tool should you use?

Accepted Answer

Azure SQL Analytics (Azure Monitor). Azure SQL Analytics (Azure Monitor) is the correct tool because it provides built-in monitoring and alerting for elastic pools, including historical resource usage (CPU, DTU, storage) for up to 30 days. It aggregates data across all databases in the pool and allows you to drill down into individual database consumption, making it ideal for identifying top resource consumers over the past month.

Answer

Azure portal metrics for the elastic pool

Answer

SQL Server Profiler

Answer

Query Performance Insight

Question 15

You are tasked with designing a disaster recovery plan for an Azure SQL Database. The database is mission-critical and must have a Recovery Point Objective (RPO) of 5 seconds and a Recovery Time Objective (RTO) of 30 minutes in case of a regional failure. Which configuration should you implement?

Accepted Answer

Active geo-replication with failover group. Active geo-replication with failover groups is the correct choice because it provides a continuous, synchronous replication mechanism that can achieve an RPO of 5 seconds and an RTO of 30 minutes during a regional failure. The failover group automates the process of failing over to a readable secondary database in a paired region, meeting both the stringent RPO and RTO requirements without manual intervention.

Answer

Long-term backup retention

Answer

Automated backups with geo-restore

Answer

Zone-redundant configuration