DP-300 Practice Exam 61 — 20 Questions

Question 1

Refer to the exhibit. After a brief outage, the availability group recovered. However, SQL2 shows NOT_HEALTHY and DISCONNECTED. What is the most likely cause?

Accepted Answer

The network connection between SQL1 and SQL2 is still down after the outage.. The error 35201 indicates a connection timeout. SQL2 is configured for automatic failover and synchronous commit. After the restart, SQL2 remains disconnected and not healthy. This is likely because the network connectivity between SQL1 and SQL2 is still interrupted or there is a persistent issue. Since SQL3 is connected and healthy, the issue is specific to SQL2.

Answer

The automatic failover policy requires a quorum that is not met.

Answer

The listener is not configured correctly for SQL2.

Answer

The secondary replica SQL2 has an incompatible database version.

Question 2

Your company is deploying a multi-tenant application using Azure SQL Database. Each tenant gets its own database. You need to manage resources efficiently while ensuring performance isolation between tenants. The number of tenants fluctuates, and you want to minimize cost. What is the best strategy?

Accepted Answer

Use an elastic pool and add databases as needed. Elastic pools are designed for multi-tenant SaaS scenarios where each tenant has its own database but usage patterns are unpredictable. They provide performance isolation through per-database resource limits (e.g., min/max DTUs or vCores) while sharing a fixed pool of resources, which minimizes cost by allowing idle databases to borrow from others. This matches the requirement of fluctuating tenant counts and cost efficiency.

Answer

Deploy each tenant's database as a single database with reserved capacity

Answer

Use a single Hyperscale database with schema per tenant

Answer

Use a single Azure SQL Managed Instance with multiple databases

Question 3

You are a database administrator for a large financial services company. The company is migrating its on-premises SQL Server databases to Azure SQL Managed Instance. One of the databases, FinanceDB, is 2.5 TB in size and runs a critical application that requires high availability with automatic failover. The application uses database mail and SQL Agent jobs. During migration, you need to minimize downtime. Which deployment option should you choose?

Accepted Answer

Deploy an Azure SQL Managed Instance in a failover group with a secondary in another region. Option D is correct because Azure SQL Managed Instance supports database mail and SQL Agent jobs natively, and deploying it in a failover group with a secondary in another region provides automatic failover and high availability while minimizing downtime during migration. The 2.5 TB size is within Managed Instance limits, and the failover group enables a controlled, continuous-sync replication to the secondary, allowing a swift cutover with minimal data loss.

Answer

Deploy an Azure SQL Database Hyperscale with geo-replication

Answer

Deploy an Azure SQL Managed Instance with zone-redundant configuration

Answer

Deploy a single Azure SQL Database with Active Geo-Replication

Question 4

You are a database administrator for a healthcare organization. You need to deploy a new Azure SQL Database that stores protected health information (PHI). The database must be encrypted at rest using a customer-managed key in Azure Key Vault. Additionally, you need to ensure that backups are also encrypted with the same key. Which configuration should you use?

Accepted Answer

Enable Transparent Data Encryption (TDE) using a customer-managed key from Azure Key Vault. Transparent Data Encryption (TDE) with a customer-managed key from Azure Key Vault is the correct choice because it encrypts the database at rest, including data files and log files, using a key that you control and manage in Azure Key Vault. This meets the requirement for encrypting protected health information (PHI) at rest with a customer-managed key, and TDE automatically ensures that backups are encrypted with the same database encryption key (DEK), which is protected by the customer-managed key.

Answer

Use column-level encryption with a certificate

Answer

Enable Transparent Data Encryption with a service-managed key

Answer

Enable Always Encrypted with keys stored in Azure Key Vault

Question 5

You are a database administrator for a hospital that uses Azure SQL Database to store patient records. The hospital's security policy requires that all database access be authenticated using Microsoft Entra ID (formerly Azure AD). You have already created a Microsoft Entra ID user for yourself and granted you the 'db_owner' role. You now need to create a new Microsoft Entra ID user for a nurse who needs read-only access to the database. What should you do first?

Accepted Answer

Connect to the database using your Microsoft Entra account and run 'CREATE USER [nurse@hospital.onmicrosoft.com] FROM EXTERNAL PROVIDER'. Option D is correct because the nurse must be created as a contained database user mapped to Microsoft Entra ID. Since the hospital uses Azure SQL Database and requires Microsoft Entra authentication, you must connect to the user database (not master) using your Microsoft Entra account (which has db_owner privileges) and run 'CREATE USER [nurse@hospital.onmicrosoft.com] FROM EXTERNAL PROVIDER'. This creates a database user that authenticates via Microsoft Entra ID without requiring a server-level login, aligning with the security policy.

Answer

In the Azure portal, add the nurse as a server-level Microsoft Entra admin

Answer

Create a SQL login for the nurse on the logical server and then create a user in the database mapped to that login

Answer

Connect to the master database using SQL authentication and run 'CREATE USER [nurse@hospital.onmicrosoft.com] FROM EXTERNAL PROVIDER'

Question 6

You are the database administrator for an Azure SQL Database used by a financial trading application. The database is in the Business Critical service tier with 16 vCores. The application executes thousands of small, high-frequency transactions per second. Recently, the application's response time has increased, and you observe high PAGELATCH_EX waits in sys.dm_os_waiting_tasks. The database is 500 GB with a single data file (tempdb.mdf) and a single log file (tempdb_log.ldf). TempDB is configured with the default settings. You need to reduce PAGELATCH_EX contention in TempDB. What should you do?

Accepted Answer

Add additional TempDB data files equal to the number of vCores (16).. PAGELATCH_EX contention in TempDB is typically caused by allocation page contention when many concurrent transactions compete for the same system page (e.g., PFS, GAM, SGAM). In Azure SQL Database Business Critical tier, adding multiple TempDB data files equal to the number of vCores (16) reduces contention by distributing allocations across files, each with its own allocation structures. This is a proven best practice for high-concurrency workloads like financial trading applications.

Answer

Move TempDB to Azure Premium Storage for better IO latency.

Answer

Increase the database service tier to 24 vCores.

Answer

Increase the initial size of the TempDB data file to 100 GB.

Question 7

You are the database administrator for a company that uses Azure SQL Database. The company has a strict security policy requiring that all database connections be encrypted using TLS 1.2 or higher and that the server certificate be validated to prevent man-in-the-middle attacks. Additionally, the company wants to ensure that only applications running on Azure virtual machines (VMs) in a specific virtual network (VNet) can access the database. The VMs use a subnet named 'AppSubnet'. You have configured the following: 
1. The server 'Allow Azure Services' setting is OFF. 
2. A virtual network rule is added for 'AppSubnet' with the service endpoint for Microsoft.Sql enabled. 
3. The server firewall has no other rules. 
4. The 'Minimum TLS version' is set to 1.2. 
5. All client applications are configured to use 'Encrypt=True' and 'TrustServerCertificate=False' in their connection strings.

After deployment, you discover that connections from the VMs are failing with error: 'The certificate chain was issued by an authority that is not trusted'. What is the most likely cause of this issue?

Accepted Answer

The VMs do not have the necessary root CA certificates installed to validate the Azure SQL Database server certificate.. The error 'The certificate chain was issued by an authority that is not trusted' indicates that the client cannot validate the Azure SQL Database server certificate. With 'TrustServerCertificate=False', the client must trust the server's certificate chain, which requires the root CA certificates (e.g., Baltimore CyberTrust Root) to be installed in the VM's trusted root store. Since the VMs are in Azure, they typically have these certificates, but custom images or hardened VMs may lack them, causing the validation failure.

Answer

The client applications are using an older version of .NET that does not support TLS 1.2.

Answer

The virtual network rule is missing the service endpoint for Microsoft.Sql on the subnet.

Answer

The server firewall is blocking traffic from the VNet because 'Allow Azure Services' is OFF.

Question 8

You are the database administrator for a large e-commerce company. The company has a SQL Server 2019 Enterprise Edition instance running on-premises with multiple databases. One of the databases, 'OrdersDB', is 2 TB in size and has a high transaction rate. The company plans to migrate 'OrdersDB' to Azure SQL Managed Instance to reduce operational overhead. The migration must have minimal downtime, and the target service tier must be Business Critical with 16 vCores. You have set up a test environment and performed a successful test migration using the Azure Database Migration Service (DMS) with online mode. During the final migration, the network bandwidth between on-premises and Azure is limited to 100 Mbps. The migration is taking longer than expected, and you are concerned about the log backup growth. What should you do to minimize the risk of the migration failing due to log space exhaustion?

Accepted Answer

Increase the frequency of transaction log backups on the source database to keep the log small.. Option D is correct because increasing the frequency of transaction log backups on the source database reduces the size of the active log, preventing log space exhaustion during the long-running online migration. The Azure Database Migration Service (DMS) in online mode continuously captures and applies log changes, so a smaller log reduces the risk of the log file filling up before backups can be taken, especially under constrained network bandwidth.

Answer

Switch to offline migration mode to avoid log growth issues.

Answer

Pause the migration and request a higher network bandwidth from the ISP.

Answer

Reduce the Azure SQL Managed Instance storage performance tier to standard to reduce cost.

Question 9

You are the database administrator for a global e-commerce company. They run a mission-critical application on Azure SQL Database in the Business Critical tier. The database is 2 TB and experiences high write throughput. The current setup uses an auto-failover group with a secondary in the same region (West US) for high availability. The client application uses the auto-failover group listener with ReadScale=1 to route read-only queries to the secondary. Recently, during a regional outage that affected West US, the failover to the secondary succeeded, but the application experienced significant performance degradation and many timeouts for read operations. Investigation reveals that the secondary replica was overwhelmed with read traffic after failover. The business requires an RTO of 30 seconds and RPO of 5 seconds. The application must be able to handle read-heavy workloads even during a failover. You need to recommend a solution to improve read scalability and disaster recovery without changing the application code. What should you do?

Accepted Answer

Deploy an additional readable secondary replica in the Business Critical tier in a different Azure region and configure active geo-replication to that region. Update the connection string to use the geo-secondary for reads.. Option B is correct because deploying an additional readable secondary replica in a different Azure region via active geo-replication provides both disaster recovery and read scalability. After a regional failover, the geo-secondary can serve read traffic without overwhelming the primary, meeting the RTO of 30 seconds and RPO of 5 seconds. The application can use the geo-secondary for reads by updating the connection string, without changing application code, ensuring read-heavy workloads are handled during failover.

Answer

Add a second secondary replica to the auto-failover group in the same region.

Answer

Increase the service objective of the secondary replica in the auto-failover group to a higher DTU or vCore to handle the read load.

Answer

Change the database to the Hyperscale tier, which supports multiple readable replicas and automatic failover.

Question 10

You are the DBA for a financial services company. The company is migrating its on-premises SQL Server databases to Azure SQL Managed Instance. One of the databases is 2 TB in size and has a high transaction volume. The migration must have minimal downtime. The source environment is SQL Server 2019 running on Windows Server. The target is a General Purpose managed instance in the same region. The network bandwidth between on-premises and Azure is 500 Mbps. The DBA plans to use Azure Database Migration Service (DMS) for an online migration. However, during the initial sync, the migration is progressing slowly and the estimated time to completion is 48 hours. The DBA needs to reduce the migration time. What should the DBA do?

Accepted Answer

Upgrade the DMS instance to a Premium SKU to increase performance. Upgrading the DMS instance to a Premium SKU increases the underlying compute and I/O resources allocated to the migration service, which directly improves the throughput of data movement during the online migration. Since the bottleneck is the DMS processing speed rather than network bandwidth (500 Mbps is sufficient for 2 TB over 48 hours), a Premium SKU provides higher performance tiers that can process change data capture (CDC) and bulk inserts faster, reducing the overall sync time.

Answer

Switch to an offline migration using BACPAC export and import

Answer

Request a higher bandwidth connection from the network team

Answer

Change the target to a different Azure region that is closer to the source

Question 11

You are the Azure DBA for a global e-commerce company. The company uses Azure SQL Database for its product catalog. The database is in the Business Critical tier with 16 vCores and 1 TB of storage. During a major sales event, the application experiences severe performance degradation. Analysis reveals that the database is experiencing high write latency on the secondary replicas due to heavy read workload routed to them. The primary replica is not overloaded. The application uses read-only routing to direct queries to readable secondaries. The DBA needs to reduce write latency on the secondaries without affecting the read workload or incurring additional cost. What should the DBA do?

Accepted Answer

Disable read-scale out for non-critical queries and use the primary for read-write. Option B is correct because disabling read-scale out for non-critical queries forces those queries to use the primary replica, reducing the read workload on the secondaries. This directly lowers the write latency on secondaries, as fewer reads compete for resources with the log apply process. The change does not affect the read workload for critical queries that still use read-only routing, and it incurs no additional cost.

Answer

Scale up the service tier to Hyperscale to offload writes

Answer

Add more replicas to distribute the read load

Answer

Remove the secondary replicas and use only the primary

Question 12

You are the database administrator for a large e-commerce company. The production Azure SQL Database (Business Critical, 16 vCores) hosts the order processing system. Recently, users report that order submissions are slow during peak hours. You examine the wait statistics and find that `LCK_M_IX` waits are the top wait type. You also notice that the stored procedure `usp_PlaceOrder` performs an `UPDATE` on the `Orders` table, and there is a high volume of concurrent transactions. The table has a clustered index on `OrderID` and a nonclustered index on `CustomerID`. The procedure uses serializable isolation level. Which action will most effectively reduce the blocking?

Accepted Answer

Change the database to use read committed snapshot isolation (RCSI) and modify the procedure to use read committed.. The primary issue is blocking caused by `LCK_M_IX` waits under the serializable isolation level, which holds range locks and prevents concurrent updates. Changing to read committed snapshot isolation (RCSI) and using read committed eliminates these locks by providing statement-level row versioning, allowing concurrent transactions to read without blocking writers. This directly addresses the high volume of concurrent `UPDATE` operations without requiring schema or hardware changes.

Answer

Increase the vCore count to 24.

Answer

Convert the Orders table to a memory-optimized table.

Answer

Add a nonclustered index on the Status column of the Orders table.

Question 13

You manage an Azure SQL Database (General Purpose, S2) used by a reporting application. The database has a table `FactSales` with 500 million rows. Queries that aggregate sales by date are slow. The execution plan shows a clustered index scan on `FactSales`. The table has a clustered index on `SaleID` and a nonclustered index on `DateKey`. The queries filter by `DateKey` and `ProductKey`. You need to improve query performance without changing the service tier. Which action should you take?

Accepted Answer

Create a covering index on DateKey and ProductKey including the aggregated columns.. The query filters by DateKey and ProductKey and aggregates sales data. A covering index on DateKey and ProductKey that includes the aggregated columns (e.g., SUM(SalesAmount)) allows the query to be satisfied entirely from the index without touching the clustered index, eliminating the costly clustered index scan. This is the most direct and effective way to improve performance without changing the service tier.

Answer

Create a nonclustered index on ProductKey only.

Answer

Partition the table by DateKey.

Answer

Create a clustered columnstore index on the table.

Question 14

You are the database administrator for a large e-commerce company. The company uses Azure SQL Database for its product catalog, which is updated frequently during business hours. To maintain query performance, you have automated index maintenance using Elastic Database Jobs. The job runs every night at 2:00 AM and rebuilds indexes with a fill factor of 80. However, you notice that the job is taking longer than expected, often running until 6:00 AM, and sometimes it fails due to timeouts. You also observe that index fragmentation is still high after the job completes. The database is in the General Purpose service tier with 100 DTUs. Which action should you take to improve the situation?

Accepted Answer

Modify the job to reorganize indexes instead of rebuilding them, and use a lower degree of parallelism.. Option C is correct because reorganizing indexes is a less resource-intensive operation than rebuilding, which reduces the job duration and avoids timeouts. Additionally, using a lower degree of parallelism (MAXDOP) prevents excessive resource contention, which is critical for a General Purpose database with only 100 DTUs. Reorganizing also addresses fragmentation without the overhead of a full rebuild, making it more suitable for the limited resources and nightly maintenance window.

Answer

Create a second Elastic Database Job to run at 4:00 AM to rebuild indexes that failed.

Answer

Change the fill factor to 70 to reduce future fragmentation.

Answer

Increase the DTU level of the database to 200 DTUs to speed up the index rebuild.

Question 15

Your company has a critical Azure SQL Database named SalesDB in the West US region. To meet disaster recovery requirements, you configured active geo-replication with a readable secondary replica in East US. You need to automate failover in the event of a regional outage. The solution must ensure that the failover is initiated automatically based on health metrics and that the application connection string is updated to point to the new primary. Currently, the application uses a connection string with the primary server name. You have the following requirements: minimize manual intervention, use built-in Azure features, and ensure that failover is tested regularly. You consider the following options: A) Use Azure Traffic Manager with endpoint monitoring to route traffic to the primary and automatically failover. B) Configure auto-failover group with read-write failover policy and grace period. C) Create an Azure Logic App that queries the database health and runs a PowerShell script to perform failover and update DNS. D) Use Azure Automation with a runbook that checks the database status and initiates failover via REST API. Which option best meets the requirements?

Accepted Answer

Configure auto-failover group with read-write failover policy and grace period.. Option C is correct because auto-failover groups are the built-in Azure feature designed specifically for automating failover of Azure SQL Database geo-replication. They support a read-write failover policy with a configurable grace period that automatically triggers failover based on health metrics, and they automatically update the connection string endpoint for applications using the failover group listener name, not the individual server name. This minimizes manual intervention and allows regular testing via manual failover without affecting the production environment.

Answer

Use Azure Traffic Manager with endpoint monitoring to route traffic to the primary and automatically failover.

Answer

Create an Azure Logic App that queries the database health and runs a PowerShell script to perform failover and update DNS.

Answer

Use Azure Automation with a runbook that checks the database status and initiates failover via REST API.

Question 16

You are the database administrator for a global e-commerce company. The company runs its production SQL Server on an Azure Virtual Machine (IaaS) in the West US region. The database is mission-critical and requires a Recovery Point Objective (RPO) of 5 minutes and a Recovery Time Objective (RTO) of 30 minutes in the event of a regional disaster. The VM uses premium SSDs and is backed up daily to a Recovery Services vault with geo-redundant storage. The current backup policy takes full backups weekly, differential backups daily, and transaction log backups every 15 minutes. The VM is in an availability set for high availability within the region. During a recent regional outage simulation, the database was unavailable for 4 hours because the backups needed to be restored to a different region, and the restore process took longer than expected. You need to recommend a solution to meet the RPO and RTO requirements. What should you do?

Accepted Answer

Configure a SQL Server Always On availability group with a synchronous-commit replica in a secondary Azure region.. Option C is correct because a SQL Server Always On availability group with a synchronous-commit replica in a secondary Azure region provides automatic failover with zero data loss (RPO of 0 seconds) and can meet the 30-minute RTO by enabling fast, automated failover to the secondary region. This solution eliminates the need for manual restore processes and ensures continuous data synchronization, directly addressing the 4-hour outage caused by slow geo-restore.

Answer

Implement Azure Site Recovery to replicate the VM to a secondary region.

Answer

Set up log shipping to a secondary SQL Server in a different region and perform manual failover.

Answer

Increase the frequency of transaction log backups to every 5 minutes and use geo-restore.

Question 17

You are designing a data platform for a global SaaS company. The application requires a relational database that can handle up to 50 TB of data and supports high-frequency inserts. The database must be able to scale compute independently from storage and provide fast restores (within minutes) for large databases. Which Azure SQL offering should you choose?

Accepted Answer

Azure SQL Database Hyperscale tier. The Hyperscale tier of Azure SQL Database is designed for workloads up to 100 TB, decouples compute from storage (allowing independent scaling), and uses a log-based architecture with page servers to enable fast restores (typically within minutes, regardless of database size). This directly matches the requirements for 50 TB data, high-frequency inserts, independent compute/storage scaling, and rapid restore times.

Answer

Azure SQL Database Business Critical tier

Answer

Azure SQL Database Standard tier

Answer

Azure SQL Managed Instance Business Critical

Question 18

Your company is migrating a mission-critical application from SQL Server 2019 on-premises to Azure SQL Managed Instance. The database is 1.5 TB and has 50 databases that need to be migrated. The application uses SQL Server Agent jobs, Service Broker, and cross-database queries. The security requirements mandate that the migration must use Microsoft Entra ID authentication, and all data must be encrypted at rest using customer-managed keys in Azure Key Vault. The migration window is limited to 4 hours of downtime. You need to choose the migration method and configure the target environment. What should you do?

Accepted Answer

Use Azure Database Migration Service online migration, configure Managed Instance with customer-managed TDE, and enable Entra ID authentication.. Option C is correct because Azure Database Migration Service (DMS) online migration supports minimal downtime for large databases (1.5 TB) and 50 databases, while maintaining transactional consistency for cross-database queries and Service Broker. Configuring the Managed Instance with customer-managed TDE (Transparent Data Encryption) using Azure Key Vault meets the encryption-at-rest requirement, and enabling Microsoft Entra ID authentication satisfies the security mandate. The online migration mode allows continuous synchronization during the migration window, with a final cutover within the 4-hour downtime limit.

Answer

Use Azure Database Migration Service offline migration with automatic cutover.

Answer

Deploy a new Managed Instance, configure TDE with customer-managed keys, and use transactional replication to synchronize data.

Answer

Use Azure backup to URL and restore to Managed Instance, then configure TDE with customer-managed keys.

Question 19

You are a database administrator for a large e-commerce company. The company has an Azure SQL Database named 'OrdersDB' in the General Purpose service tier, with a 2 TB database. The database has a table named 'OrderDetails' that contains over 1 billion rows. The table is partitioned by date using a partition function that splits data by month. You notice that queries that filter on a specific date range are performing poorly, and the query plan shows a clustered index scan on the partition column. The table has a clustered columnstore index to support analytics. You need to improve query performance for date-range queries without affecting the analytics workload. What should you do?

Accepted Answer

Create a nonclustered index on the partition key column (OrderDate) and include other columns as needed.. Option B is correct because creating a nonclustered index on the partition key (OrderDate) allows SQL Server to perform an index seek for date-range queries, avoiding the full clustered columnstore index scan. This index does not interfere with the existing columnstore index, which remains optimal for analytics workloads. The nonclustered index can include additional columns as included columns to cover the query without touching the base table.

Answer

Create a new partition scheme with a different partition function.

Answer

Rebuild the clustered columnstore index with a different compression setting.

Answer

Change the clustered index to a rowstore B-tree index on the partition key.

Question 20

You are managing an Azure SQL Database that hosts a customer relationship management (CRM) application. The database has a table named 'Contacts' with columns: ContactID (int, primary key), Name (nvarchar(100)), Email (nvarchar(200)), Phone (nvarchar(20)), and CreditLimit (decimal(18,2)). The compliance team requires that the CreditLimit column be encrypted so that only authorized users can view it. The application must be able to search for exact matches on CreditLimit values. You need to implement encryption without changing the application code significantly. Which encryption method should you use?

Accepted Answer

Always Encrypted with deterministic encryption. Always Encrypted with deterministic encryption is correct because it encrypts the CreditLimit column at the client driver level, ensuring data remains encrypted at rest and in transit, while still allowing exact-match searches (e.g., WHERE CreditLimit = 5000) since deterministic encryption always produces the same ciphertext for a given plaintext. This meets the compliance requirement without requiring significant application code changes, as the Azure SQL Database driver handles encryption and decryption transparently for authorized users.

Answer

Always Encrypted with randomized encryption

Answer

Transparent Data Encryption

Answer

Dynamic Data Masking

DP-300 Practice Exam 61 — 20 Questions

Exhibit

DP-300 Practice Exam 61 — 20 Questions

Exhibit