DP-300 Practice Test 12 — 15 Questions

Question 1

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 2

You are planning to migrate a large number of on-premises SQL Server databases to Azure SQL Managed Instance. The migration must be automated and support schema and data validation. Which TWO tools or services should you consider using? (Choose TWO.)

Accepted Answer

Azure Database Migration Service. Azure Database Migration Service (DMS) is the correct choice because it is a fully managed service designed to automate the migration of on-premises SQL Server databases to Azure SQL Managed Instance with minimal downtime. It supports both schema and data validation through built-in assessment, data integrity checks, and cutover capabilities, making it ideal for large-scale automated migrations.

Answer

Azure SQL Migration tool

Answer

SQL Server Management Studio (SSMS)

Answer

BACPAC export and import

Question 3

Which THREE of the following are required steps to configure a failover group for an Azure SQL Database with a readable secondary in a different region?

Accepted Answer

Add the primary database to the failover group.. Option B is correct because adding the primary database to the failover group is the essential step that associates the database with the group, enabling automated replication and failover to the secondary server. Without this step, the failover group has no database to replicate, and the readable secondary cannot be created or maintained.

Answer

Create a server-level firewall rule on the secondary server to allow client IPs.

Answer

Configure a grace period of at least 1 hour.

Question 4

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 5

You are designing a disaster recovery plan for an Azure SQL Database that is used by a critical application. The database is currently in the West US region. You need to ensure that if a regional outage occurs, the database can be failed over to another region with minimal data loss. The solution must also minimize costs for the secondary replica. Which deployment option should you recommend?

Accepted Answer

Configure active geo-replication to a secondary in East US using a lower service tier.. Active geo-replication allows you to create a readable secondary database in a different region (East US) with a lower service tier, which reduces costs while still providing a disaster recovery target. In the event of a regional outage, you can manually initiate a failover to the secondary, and because replication is asynchronous, data loss is limited to the replication lag (typically a few seconds). This meets the requirement of minimal data loss and cost minimization.

Answer

Deploy an auto-failover group with a secondary in East US using the same service tier.

Answer

Deploy a zone-redundant database in West US with a failover group to East US.

Answer

Use a failover group with a secondary in East US and enable zone redundancy on both.

Question 6

Your company is migrating an on-premises SQL Server 2016 database to Azure SQL Managed Instance. The database uses SQL Server Agent jobs, Database Mail, and Service Broker. The database is 500 GB. You need to minimize downtime during migration. Which migration approach should you use?

Accepted Answer

Use Azure Database Migration Service with online migration. Option B is correct because Azure Database Migration Service (DMS) with online migration mode uses continuous data synchronization via change tracking or log shipping to minimize downtime. This approach supports SQL Server Agent jobs, Database Mail, and Service Broker, which are preserved during migration to Azure SQL Managed Instance. The online mode allows the source database to remain operational until the final cutover, reducing downtime to seconds.

Answer

Set up transactional replication from on-premises to Azure SQL Managed Instance

Answer

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

Answer

Back up the database to URL and restore to Azure SQL Managed Instance

Question 7

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 8

You are deploying an Azure SQL Database for a new application that requires high availability with automatic failover within the same region. The database must be able to recover from a zonal failure without data loss. Which deployment option should you use?

Accepted Answer

Business Critical tier with zone redundancy enabled. The Business Critical tier with zone redundancy enabled provides the highest level of availability within a single Azure region by synchronously replicating data across three different availability zones. This ensures that if one zone fails, automatic failover occurs with no data loss because all transactions are committed to at least two replicas before being acknowledged. This meets the requirement for zonal failure recovery without data loss.

Answer

Hyperscale tier with zone redundancy

Answer

Standard tier with failover group

Answer

Basic tier with active geo-replication

Question 9

You are deploying Azure SQL Database for a multi-tenant SaaS application. Each tenant has its own database. You need to ensure that tenant data is isolated and that performance is predictable. Cost efficiency is important. Which deployment model should you use?

Accepted Answer

Use an elastic pool with one database per tenant. Option D is correct because an elastic pool allows you to provision a shared set of resources (eDTUs or vCores) that is distributed across multiple databases, each representing a tenant. This provides logical isolation of tenant data (each tenant has its own database) while pooling resources to handle variable workloads cost-effectively. The elastic pool model is specifically designed for SaaS multi-tenant scenarios where predictable performance is achieved through resource governance, and cost efficiency comes from sharing resources among databases that do not all peak simultaneously.

Answer

Deploy a single Azure SQL Database per tenant

Answer

Use Azure SQL Managed Instance with multiple databases

Answer

Use a single large database with row-level security

Question 10

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 11

You are deploying an Azure SQL Database for a development environment. The database will be used for testing and will be deleted after three months. You want to minimize costs while ensuring the database can be restored to any point within the retention period. Which backup retention policy should you configure?

Accepted Answer

Configure short-term backup retention to 7 days.. Option B is correct because the scenario specifies a development database that will be deleted after three months, with the requirement to restore to any point within the retention period. The default short-term backup retention for Azure SQL Database is 7 days, which provides point-in-time restore (PITR) capability within that window. Since the database is temporary and cost minimization is key, 7 days meets the requirement without incurring extra storage costs for longer retention or geo-redundancy.

Answer

Configure geo-redundant backup storage.

Answer

Configure short-term backup retention to 14 days.

Answer

Configure long-term backup retention (LTR) for 1 year.

Question 12

Which TWO of the following are valid methods to secure data in transit for Azure SQL Database?

Accepted Answer

Configure the server to enforce TLS 1.2 by setting minimal TLS version.. Enforcing TLS 1.2 and using an Azure VPN gateway are valid methods to secure data in transit. Column-level encryption (Always Encrypted) protects data in use, not in transit. Network security groups (NSGs) are for network layer filtering, not encryption. Azure Firewall is a network security service, not specifically for data in transit encryption.

Answer

Enable Always Encrypted on sensitive columns.

Answer

Deploy Azure Firewall in front of the database.

Answer

Apply a Network Security Group (NSG) to the subnet.

Question 13

You need to migrate an on-premises SQL Server database to Azure SQL Database. The database uses SQL Server Integration Services (SSIS) packages for ETL. You want to continue using SSIS in the cloud with minimal changes. Which Azure service should you use?

Accepted Answer

Use Azure Data Factory with Azure-SSIS Integration Runtime. Azure Data Factory with Azure-SSIS Integration Runtime is the correct choice because it provides a fully managed service that lifts and shifts existing SSIS packages to the cloud with minimal changes. The Azure-SSIS IR runs a dedicated cluster of Azure VMs that host the SSIS engine, allowing you to deploy, execute, and manage your existing SSIS packages directly in Azure without rewriting code.

Answer

Deploy SSIS on Azure SQL Database

Answer

Migrate SSIS packages to Azure Databricks

Answer

Use Azure SQL Managed Instance to run SSIS

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 configuring Azure SQL Database for an e-commerce application that experiences variable traffic. You need to ensure that the database can automatically scale resources based on demand without manual intervention. The solution must also support scaling to zero compute when not in use to save costs. Which Azure SQL Database offering should you use?

Accepted Answer

Azure SQL Database serverless.. Azure SQL Database serverless is the correct choice because it automatically scales compute resources based on demand and supports pausing the database when idle, effectively scaling to zero compute to save costs. This aligns perfectly with the requirements of variable traffic and cost optimization without manual intervention.

Answer

Azure SQL Database elastic pool.

Answer

Azure SQL Database Hyperscale.

Answer

Azure SQL Database Business Critical tier.