DP-300 Practice Test 45 — 15 Questions

Question 1

You are reviewing an ARM template snippet that configures a Security Alert Policy for an Azure SQL Database. The policy is enabled, and email notifications are sent to the account admin and admin@contoso.com. However, you notice that SQL Injection alerts are disabled. What is the most likely reason for disabling SQL Injection alerts?

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

Accepted Answer

Because SQL injection detection is already handled by Microsoft Defender for SQL.. Microsoft Defender for SQL provides built-in SQL injection detection, which can supersede the need for separate Security Alert Policy rules. When Defender for SQL is enabled, it automatically monitors and alerts on SQL injection attempts, so duplicating with a custom alert policy would be redundant and could cause confusion. Option A is incorrect because disabling alerts does not save costs; it weakens security. Option B is incorrect because vulnerability assessment policies do not override alert rules. Option C is incorrect because there is no known incompatibility between SQL injection alerts and storage account endpoints.

Answer

To reduce the number of false positives and save costs on alert processing.

Answer

Because the database is configured with a conflicting vulnerability assessment policy that overrides SQL injection detection.

Answer

Because SQL injection alerts are incompatible with the chosen storage account endpoint.

Question 2

You are a database administrator for a global e-commerce company. The company runs a critical transactional workload on an Azure SQL Managed Instance (MI) in the West US region. The database is approximately 1 TB in size and uses the Business Critical tier with zone redundancy enabled. The application requires read latency for reporting queries to be under 10 ms. The company is expanding to Europe and needs to deploy a secondary copy of the database in the North Europe region to serve read-only reporting traffic for European users. The secondary must be kept in sync with the primary with a recovery point objective (RPO) of less than 5 seconds. Additionally, the solution must support automatic failover if the primary region becomes unavailable. You need to design the disaster recovery and read scaling solution. Which approach should you take?

Accepted Answer

Create an auto-failover group with the primary in West US and add a readable secondary in North Europe, using synchronous commit. Azure SQL Managed Instance supports auto-failover groups with a readable secondary in a paired region. By configuring synchronous commit within the failover group, you achieve an RPO of less than 5 seconds (typically 0-2 seconds) while the secondary in North Europe can serve read-only traffic with low latency. The auto-failover group also provides automatic failover if the primary region becomes unavailable, meeting both the read scaling and disaster recovery requirements.

Answer

Create a geo-replica of the MI in North Europe and manually failover when needed

Answer

Deploy a separate managed instance in North Europe and use transactional replication to keep it in sync

Answer

Create an auto-failover group with the primary in West US and secondary in North Europe, but do not make the secondary readable

Question 3

You are responsible for managing a fleet of 20 Azure SQL Databases used by different departments. Each database has its own schema and data. You need to automate the creation of weekly exports of each database to a BACPAC file stored in a specific Azure Storage container. The exports should be done outside business hours (Sundays at 2 AM). You also need to ensure that the export process does not impact production performance. What is the most straightforward and cost-effective solution?

Accepted Answer

Create an Azure Automation runbook that uses the Export-AzSqlDatabase cmdlet for each database, and schedule the runbook to run weekly on Sundays at 2 AM.. Azure Automation runbooks can use the Export-AzSqlDatabase cmdlet to export each database to BACPAC, and scheduling the runbook for Sundays at 2 AM meets the automation and off-hours requirement, minimizing performance impact. This is straightforward and cost-effective as it uses built-in Azure Automation without extra services. Option A (Elastic Database Jobs) is designed for running T-SQL scripts across databases, not for exporting BACPAC files. Option B (Azure Data Factory) adds unnecessary complexity and cost for this simple export task. Option D is manual and not automated.

Answer

Use Elastic Database Jobs to run a SQL script that exports the database using the BACPAC export command.

Answer

Use Azure Data Factory with a copy activity to export the database to BACPAC via the SQL Server Import/Export service.

Answer

Manually export each database using Azure Portal once a week.

Question 4

You are reviewing an Azure Policy definition that audits SQL Servers. Based on the exhibit, which condition triggers the audit effect?

Accepted Answer

The SQL Server uses service-managed TDE or has no encryption protector configured. The policy 'if' condition requires allOf: type is 'Microsoft.Sql/servers', and anyOf: encryption protector does not exist OR encryption protector is 'ServiceManaged'. This audits SQL servers that either use service-managed TDE or have no encryption protector configured. Option B is incorrect because the policy does not trigger when a customer-managed key is used; it specifically targets those without a customer-managed key. Option C is incorrect because the condition checks for absence of encryption protector or service-managed, not the absence of customer-managed key. Option D is incorrect because version 12.0 is checked but it is not the trigger; the trigger is based on encryption protector type.

Answer

The SQL Server uses a customer-managed key from Azure Key Vault

Answer

The SQL Server does not have an encryption protector set to a customer-managed key

Answer

The SQL Server is version 12.0 and uses a customer-managed key

Question 5

Your company wants to implement transparent data encryption (TDE) for an Azure SQL Database using a customer-managed key stored in Azure Key Vault. Which TWO prerequisites must be met? (Choose two.)

Accepted Answer

The Azure SQL Server must have a system-assigned managed identity.. Azure SQL Database uses the server's system-assigned managed identity to authenticate to Azure Key Vault when accessing the customer-managed key for TDE. Without this identity, the SQL Server cannot prove its identity to Key Vault to retrieve the key. Option B is correct because the Key Vault must have an access policy that grants the SQL Server's managed identity the 'get', 'wrapKey', and 'unwrapKey' permissions, which are required for TDE operations.

Answer

The database must contain a column master key.

Answer

The Key Vault must be in a different region than the SQL Server.

Answer

The database must be taken offline during key configuration.

Question 6

Your company uses Azure SQL Managed Instance for a critical application. You need to automate the process of rebuilding all indexes that have fragmentation above 30% across all databases on the instance. The automation must run every Sunday at 2:00 AM and must log the results to a table. Which three steps should you take to implement this automation?

Accepted Answer

Create a logging table in a database to record start time, end time, and status.. Creating a logging table is necessary to record the results of the index maintenance operations. Option C is correct because SQL Agent jobs can be scheduled on a Managed Instance to run tasks at specific times, such as every Sunday at 2:00 AM. Option E is correct because a T-SQL script using sys.dm_db_index_physical_stats to identify fragmented indexes and ALTER INDEX REBUILD to rebuild them is the standard approach. Option B is incorrect because Azure Automation runbooks require additional setup (like Hybrid Runbook Worker) to connect to SQL Managed Instance, and it is not the simplest way to schedule a recurring task on the instance. Option D is incorrect because Elastic Database Jobs is designed for Azure SQL Database and is not supported on Azure SQL Managed Instance.

Answer

Use an Azure Automation runbook with the PowerShell module SqlServer to connect and run the maintenance.

Answer

Use Elastic Database Jobs to run the script across all databases.

Question 7

You have Azure SQL Database elastic pools in the primary region. You need to configure disaster recovery using a failover group. The secondary pool must be in a different region. Which PowerShell cmdlet should you use to create the secondary pool?

Accepted Answer

New-AzSqlDatabaseSecondary. The correct cmdlet is New-AzSqlDatabaseSecondary (Option C). When the primary database is in an elastic pool, using the -SecondaryElasticPoolName parameter creates a new elastic pool on the secondary server in a different region if it does not already exist. This makes it suitable for creating the secondary pool as part of failover group setup. Option A (Set-AzSqlDatabaseSecondary) modifies an existing geo-replication relationship, not create a new one. Option B (Add-AzSqlDatabaseToFailoverGroup) adds an existing database to a failover group, but the secondary pool must already be provisioned. Option D (New-AzSqlDatabaseFailoverGroup) creates the failover group itself, not the secondary elastic pool.

Answer

Set-AzSqlDatabaseSecondary

Answer

Add-AzSqlDatabaseToFailoverGroup

Answer

New-AzSqlDatabaseFailoverGroup

Question 8

Which TWO disaster recovery options are available for Azure SQL Database? (Choose two.)

Accepted Answer

Auto-failover groups.. Active geo-replication and auto-failover groups are the two primary disaster recovery options for Azure SQL Database. Active geo-replication provides asynchronous replication of a database to a secondary region, while auto-failover groups extend this by allowing failover of multiple databases and providing read-only endpoints. Options A (database mirroring) and D (log shipping) are not supported for Azure SQL Database. Option C (Always On availability groups) is a feature for SQL Server on Azure VMs, not for Azure SQL Database.

Answer

Database mirroring.

Answer

Always On availability groups.

Answer

Log shipping.

Question 9

You are planning a migration of on-premises SQL Server databases to Azure SQL Managed Instance. The source databases use SQL Server Agent jobs, cross-database queries, and SQL Server Integration Services (SSIS) packages. Which migration approach minimizes the need to modify existing code or processes?

Accepted Answer

Migrate to Azure SQL Managed Instance and use Azure Data Factory to run SSIS packages with an Azure-SSIS integration runtime.. Azure SQL Managed Instance provides near-100% compatibility with on-premises SQL Server, including SQL Server Agent and cross-database queries. For SSIS packages, the Azure-SSIS integration runtime in Azure Data Factory allows you to lift-and-shift existing SSIS packages without rewriting them, minimizing code changes.

Answer

Migrate to SQL Server on Azure Virtual Machines and keep all existing functionality unchanged.

Answer

Migrate to Azure Database for PostgreSQL and rewrite all SQL Agent jobs as pg_cron tasks.

Answer

Migrate to Azure SQL Database and use elastic jobs to replace SQL Server Agent.

Question 10

Your company uses Azure SQL Database with active geo-replication for disaster recovery. During a drill, you need to verify that the secondary database is ready for failover and that data synchronization is current. Which query should you run on the secondary database?

Accepted Answer

SELECT * FROM sys.dm_geo_replication_link_status;. Sys.dm_geo_replication_link_status provides replication lag and the last replicated transaction for geo-replicated databases. Option A is wrong because sys.dm_continuous_copy_status is deprecated and no longer used for geo-replication monitoring. Option C is wrong because sys.dm_database_copies is for copy-only operations, not ongoing replication. Option D is wrong because sys.dm_replication_status is for transactional replication, not geo-replication.

Answer

SELECT * FROM sys.dm_continuous_copy_status;

Answer

SELECT * FROM sys.dm_database_copies;

Answer

SELECT * FROM sys.dm_replication_status;

Question 11

You need to automate the backup of an Azure SQL Database to a storage account. Which built-in feature should you configure?

Accepted Answer

Configure a long-term retention policy for the database.. Azure SQL Database has automated backups enabled by default, which are managed by Microsoft. To retain backups beyond the default retention period (7-35 days), you can configure a long-term retention (LTR) policy to store backups for up to 10 years. Option A is incorrect because elastic jobs are used for performing administrative tasks across multiple databases, not for scheduling backups (backups are automatic and cannot be scheduled via elastic jobs). Option B is incorrect because Azure Backup is designed for IaaS VMs and Azure Files, not for Azure SQL Database, which has its own built-in backup mechanism. Option C is incorrect because SQL Server Agent is not available in Azure SQL Database (it is a feature of SQL Server on-premises or on IaaS VMs); therefore, you cannot use SQL Server Agent jobs for backup automation. Option D is correct because a long-term retention policy is the built-in feature that allows you to keep automated backups for extended periods.

Answer

Use elastic jobs to schedule backups.

Answer

Use Azure Backup to create a backup policy.

Answer

Configure a SQL Server Agent job to perform backups.

Question 12

You have an Azure SQL Managed Instance configured with a failover group across two regions. The primary region experiences a complete outage. You perform a manual failover to the secondary region. After the primary region is restored, you need to bring the original primary back into the failover group. What is the correct order of actions?

Accepted Answer

Remove the original primary from the failover group and then add it back as a secondary.. After a manual failover in an Azure SQL Managed Instance failover group, the original primary becomes a secondary. To restore it to the failover group, you must first remove the original primary from the group if it is still listed, then add it back as a secondary. This allows the original primary to rejoin as a readable secondary and resync data. Option B is incorrect because a planned failover can only be initiated when the primary is healthy, which it may not be after an outage. Option C is incorrect because creating a new instance is unnecessary and wasteful. Option D is incorrect because a forced failover is not appropriate for restoring a previously failed instance; forced failover is used for urgent failover scenarios and may cause data loss.

Answer

Perform a planned failover back to the original primary region.

Answer

Create a new SQL Managed Instance in the original region and add it to the failover group.

Answer

Perform a forced failover to the original primary region.

Question 13

You are a database administrator for a large financial services company. You manage an Azure SQL Database in the Business Critical tier with a failover group configured for disaster recovery. The database has a heavy OLTP workload. You notice that the secondary replica is experiencing high log write latency, impacting the primary's performance due to synchronous commit. You need to minimize the performance impact on the primary while maintaining disaster recovery capabilities. What should you do?

Accepted Answer

Change the failover group to use asynchronous commit mode.. Changing the failover group to asynchronous commit mode decouples the primary's transaction commit from the secondary's log write. In synchronous mode, the primary waits for the secondary to confirm log hardening, which causes performance impact when the secondary has high log write latency. Asynchronous commit allows the primary to commit without waiting, thus minimizing performance impact while still maintaining disaster recovery capabilities (the secondary will apply changes eventually, though with potential data loss if a failover occurs before sync). Option A is incorrect because backup storage redundancy (LRS vs GRS) only affects backup storage, not live log write latency for replication. Option B is incorrect because adding another secondary does not reduce latency on the existing secondary; it could even increase overhead. Option D is incorrect because decreasing the secondary's service tier to General Purpose would reduce its I/O capacity and likely worsen log write latency.

Answer

Change the backup storage redundancy of the secondary replica to locally-redundant storage (LRS).

Answer

Add an additional secondary replica to distribute the log write load.

Answer

Decrease the service tier of the secondary replica to General Purpose.

Question 14

You need to ensure that all connections to an Azure SQL Database are encrypted. Which setting should you enforce?

Accepted Answer

Set 'Force encryption' to 'Yes' on the server.. Setting 'Force encryption' to 'Yes' on the Azure SQL Database server enforces TLS encryption for all client connections. This setting ensures that any client connecting to the database must use an encrypted connection, rejecting any unencrypted attempts. It is the direct mechanism to mandate encryption in transit, as opposed to encryption at rest or auditing.

Answer

Configure firewall rules to allow only specific IP addresses.

Answer

Enable Transparent Data Encryption (TDE).

Answer

Enable SQL Auditing.

Question 15

You are designing a high availability solution for a mission-critical Azure SQL Database. The database must have an uptime SLA of 99.995% and automatically recover from zone-level failures without data loss. Which service tier and redundancy option should you choose?

Accepted Answer

Business Critical tier with zone redundancy. The Business Critical tier with zone redundancy provides an SLA of 99.995% and automatic recovery from zone-level failures without data loss. The Basic tier does not support zone redundancy. The General Purpose tier with zone redundancy offers a 99.99% SLA. The Hyperscale tier with zone redundancy offers a 99.95% SLA for zone-redundant configurations. Therefore, only the Business Critical tier meets the 99.995% uptime requirement with zone redundancy.

Answer

Basic tier with zone redundancy

Answer

General Purpose tier with zone redundancy

Answer

Hyperscale tier with zone redundancy