DP-300 Plan and implement data platform resources Practice Test 6 — 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?

Accepted Answer

Enable Active Geo-Replication with a readable secondary in West US.. Active Geo-Replication with a readable secondary in West US provides automatic, asynchronous replication of transactions to a secondary database in a paired region. In the event of a regional outage, you can initiate a manual or automated failover to the secondary with minimal data loss, as replication lag is typically seconds. This option meets the requirement for automatic failover and minimal data loss, whereas other options either lack automatic failover or introduce significant data loss.

Answer

Configure auto-failover groups between East US and 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 are configuring Azure SQL Database for a new e-commerce application that must support high read throughput for product catalog queries. The application uses Entity Framework Core and requires that read-only queries be offloaded to a secondary replica to reduce load on the primary. Which feature should you enable?

Accepted Answer

Enable read scale-out and configure the application to use read-only intent.. Read scale-out in Azure SQL Database allows you to offload read-only workloads to a readable secondary replica by setting the application connection string's `ApplicationIntent=ReadOnly`. This reduces load on the primary replica, which is essential for high read throughput in an e-commerce catalog scenario. Entity Framework Core can use this by specifying `ReadOnly` in the connection string or via a custom interceptor.

Answer

Enable Query Performance Insights and create indexes for frequent queries.

Answer

Configure Active Geo-Replication with a readable secondary in a different region.

Answer

Enable automatic tuning to force parameterization of queries.

Question 5

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 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 6

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.. Option D is correct because 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 7

You are deploying Azure SQL Database for a multi-tenant SaaS application. Each tenant has its own database, and you need to ensure that resource usage is isolated and predictable. You also need to manage performance at the tenant level. Which Azure SQL Database offering should you choose?

Accepted Answer

Azure SQL Database elastic pools with per-database min/max DTU or vCore settings.. Azure SQL Database elastic pools with per-database min/max DTU or vCore settings are the correct choice because they provide resource isolation and predictable performance at the tenant level. Elastic pools allow you to allocate a shared pool of resources across multiple databases while setting per-database minimum and maximum limits, ensuring that no single tenant can consume excessive resources and that each tenant gets a guaranteed baseline. This directly addresses the multi-tenant SaaS requirement for isolated and predictable resource usage.

Answer

Azure SQL Database serverless compute tier.

Answer

Azure SQL Database single databases with DTU-based tier.

Answer

Azure SQL Managed Instance with multiple databases.

Question 8

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 9

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 10

You are configuring security for an Azure SQL Database that will be accessed by multiple applications. You need to implement a solution that allows applications to connect using their own managed identities without storing credentials in connection strings. What should you configure?

Accepted Answer

Enable Microsoft Entra ID authentication and assign managed identities to the applications.. Option A is correct because Microsoft Entra ID authentication allows Azure SQL Database to trust tokens issued by Entra ID for managed identities. By assigning a managed identity to each application, the application can acquire an access token from Azure Managed Identity endpoints and present it to the database without ever storing credentials in connection strings. This eliminates the need for passwords or connection string secrets.

Answer

Use Always Encrypted with column master key in Azure Key Vault.

Answer

Configure firewall rules to allow application IP addresses.

Answer

Enable SQL Server authentication and create a login for each application.

Question 11

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 12

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 13

You are deploying an Azure SQL Database using PowerShell as shown in the exhibit. The database will be used by a development team that works intermittently. You need to ensure the database is cost-effective while being available on demand. What is the purpose of the AutoPauseDelayInMinutes parameter?

Accepted Answer

It controls the automatic pausing of the database after a period of inactivity to save costs.. The AutoPauseDelayInMinutes parameter is used with Azure SQL Database serverless compute tier. It specifies the number of minutes of inactivity (no CPU usage or active sessions) after which the database automatically pauses, stopping compute billing while storage remains billed. This makes the database cost-effective for intermittent development workloads because it eliminates compute costs during idle periods and automatically resumes on the first connection.

Answer

It configures the database to pause during a disaster recovery scenario.

Answer

It sets the maximum duration for which the database can be paused.

Answer

It determines how long the database takes to resume after a pause.

Question 14

You are configuring managed backup for an Azure SQL Managed Instance as shown in the exhibit. What is the purpose of this configuration?

Accepted Answer

To configure automated backups of the managed instance to Azure Blob Storage.. The configuration shown in the exhibit is for managed backup, which automatically schedules and stores full, differential, and transaction log backups of the Azure SQL Managed Instance to Azure Blob Storage. This ensures that backups are retained and available for restore operations without manual intervention, fulfilling the core purpose of automated backups to Azure Blob Storage.

Answer

To enable geo-replication for the managed instance.

Answer

To set up disaster recovery across Azure regions.

Answer

To configure point-in-time restore for the managed instance.

Question 15

You are analyzing the SQL script in the exhibit. This script is used to query data stored in Azure Blob Storage from Azure SQL Database. What is the primary purpose of the database scoped credential?

Accepted Answer

To store the shared access signature token for accessing the blob storage.. The database scoped credential in this context securely stores the Shared Access Signature (SAS) token required to authenticate and authorize access to Azure Blob Storage. When creating an external data source with the CREDENTIAL option, SQL Database uses this credential to pass the SAS token to the storage endpoint, enabling read operations for PolyBase or external table queries.

Answer

To define the file format of the external data.

Answer

To specify the schema of the external table.

Answer

To provide the location of the external data source.