DP-300 Practice Test 37 — 15 Questions

Question 1

You are automating the creation of Azure SQL Databases using ARM templates. You need to ensure that the databases are created with the 'Hyperscale' service tier and with 'ZoneRedundant' enabled. Which ARM template property should you set?

Accepted Answer

Set 'sku' to 'HS_Gen5_2' and 'properties.zoneRedundant' to true.. The 'sku' property in ARM templates defines the service tier. For Hyperscale, the name is 'HS', and tier is 'Hyperscale'. The 'zoneRedundant' property is a separate property under 'properties'. Option B is wrong because 'edition' is not used in ARM templates for Azure SQL Database. Option C is wrong because 'requestedServiceObjectiveName' is for DTU-based tiers. Option D is wrong because 'maxSizeBytes' does not define the tier.

Answer

Set 'requestedServiceObjectiveName' to 'HS_Gen5_2' and 'zoneRedundant' to true.

Answer

Set 'edition' to 'Hyperscale' and 'zoneRedundant' to true.

Answer

Set 'maxSizeBytes' to a value and 'zoneRedundant' to true.

Question 2

You are reviewing an Azure CLI command that creates an elastic job step. The job step is intended to rebuild all indexes on the Sales.Orders table, but the job fails. What is the error in the JSON configuration?

Accepted Answer

The command is missing a semicolon at the end.. The 'ALTER INDEX ALL' command should have a semicolon at the end. While not strictly required, the more likely issue is that the command is missing the semicolon. However, the real error is that the 'command' property should be a T-SQL script, and 'ALTER INDEX ALL ON Sales.Orders REBUILD' is missing the semicolon. Option A is wrong because the syntax is mostly correct. Option C is wrong because the command is correct syntactically. Option D is wrong because the job agent does not require a specific version.

Answer

The target group should specify 'SqlServer' type instead of 'SqlDatabase'.

Answer

The command should be 'REBUILD INDEX' instead of 'ALTER INDEX'.

Answer

The job agent version is incompatible with the command.

Question 3

Which TWO services can be used to automate the backup of Azure SQL Database to a storage account? (Choose two.)

Accepted Answer

Azure Backup. Azure Automation with PowerShell runbooks can execute backup commands. Azure Data Factory can copy backups but not native backup. Azure Backup service supports Azure SQL Database backups. SQL Agent is not available. Elastic jobs are for T-SQL, not backup.

Answer

Elastic Database Jobs

Answer

SQL Agent jobs

Answer

Azure Data Factory

Question 4

You manage an Azure SQL Managed Instance that hosts several databases. You need to automate the process of patching the operating system and SQL Server engine with minimal downtime. What should you use?

Accepted Answer

Configure the maintenance window for the Managed Instance.. Option A is correct because Managed Instance automatically handles patching during the configured maintenance window. Option B is wrong because Azure Update Manager is not applicable to Managed Instance. Option C is wrong because manual patching is not automated. Option D is wrong because elastic jobs are for database-level tasks, not patching.

Answer

Use an Elastic Job agent to run a script that applies updates.

Answer

Schedule a manual patching using the Azure portal.

Answer

Use Azure Update Manager to schedule patching.

Question 5

You need to automatically scale up an Azure SQL Database to the next service tier when CPU usage exceeds 80% for 10 minutes, then scale back down when CPU drops below 30% for 30 minutes. Which Azure feature should you use?

Accepted Answer

Configure autoscale settings in the Azure portal for the SQL database server.. Option D is correct because autoscale settings in Azure SQL Database can be configured to scale based on metrics like CPU percentage. Options A and B are for different purposes. Option C is not a built-in feature.

Answer

Azure Automation runbook with PowerShell cmdlets to modify the service objective.

Answer

Elastic Database Jobs to run ALTER DATABASE MODIFY.

Answer

Azure Logic Apps with a recurrence trigger and SQL DB REST API.

Question 6

A company has multiple Azure SQL Databases and wants to automate the deployment of schema changes using a CI/CD pipeline. They need to ensure that changes are automatically rolled back if any step fails. Which Azure service should they use?

Accepted Answer

Azure DevOps with Azure SQL Database deployment task. Azure SQL Database elastic jobs are designed for automating administrative tasks, not CI/CD. Azure Automation with PowerShell runbooks can orchestrate rollbacks but requires custom scripting. Azure DevOps with Azure SQL Deployment task supports transactional deployments with built-in rollback on failure. Azure Machine Learning is irrelevant.

Answer

Azure Automation with PowerShell runbooks

Answer

Azure SQL Database elastic jobs

Answer

Azure Machine Learning

Question 7

Which THREE components are required to run Elastic Database Jobs for Azure SQL Database? (Choose three.)

Accepted Answer

A job database. Elastic Database Jobs require a job database to store metadata, a job agent to orchestrate, and target databases. SQL Agent is not required. Elastic pool is optional. Azure Automation is not required.

Answer

SQL Agent

Answer

Azure Automation account

Question 8

You need to automate the creation of an Azure SQL Database and a corresponding server-level firewall rule to allow access from a specific IP address. The deployment must be repeatable and version-controlled. What should you use?

Accepted Answer

Create an ARM template that defines both the server firewall rule and the database.. Option B is correct because ARM templates are declarative and version-controlled. Option A is wrong because the Azure portal is manual. Option C is wrong because SSMS is manual. Option D is wrong because PowerShell is procedural, not the best for version-controlled repeatable deployments.

Answer

Write a PowerShell script that uses New-AzSqlDatabase and New-AzSqlServerFirewallRule.

Answer

Use the Azure portal to create the database and firewall rule.

Answer

Use SQL Server Management Studio to script the creation.

Question 9

You have an Azure SQL Database that uses a serverless compute tier. You want to automate the process of pausing the database during non-business hours (8 PM to 6 AM) to save costs. The database should automatically resume when the first connection is attempted. What should you configure?

Accepted Answer

Set the auto-pause delay to 10 hours in the serverless configuration.. Option A is correct because serverless databases automatically pause after a period of inactivity and resume on first connection; you can set the auto-pause delay to 10 hours (from 8 PM to 6 AM). Options B and C are not applicable. Option D is for elastic pools.

Answer

Create a SQL Agent job to run ALTER DATABASE PAUSE and ALTER DATABASE RESUME.

Answer

Configure an elastic job to scale down to 0 vCores during off-hours.

Answer

Use Azure Automation to run a script that calls the REST API to pause/resume the database.

Question 10

You manage an Azure SQL Database that supports a critical financial application. The database is in the General Purpose tier and uses active geo-replication for disaster recovery. You need to automate the process of failing over to the secondary region in case of a regional outage, but only after confirming that the primary is unreachable for more than 5 minutes. Additionally, you need to send an alert to the operations team when the failover occurs. The solution should use Azure services and minimize manual steps. What should you implement?

Accepted Answer

Configure an Azure Monitor metric alert on the 'Deadlock count' metric (or custom metric) with a threshold of 0 for 5 minutes, then use an action group to trigger an Azure Automation runbook that runs the failover PowerShell cmdlet.. Option A is correct because Azure Monitor can detect the primary database unavailability (e.g., through heartbeat metrics) and trigger an alert that invokes an Azure Automation runbook to perform the failover. Options B and C require manual intervention. Option D (Azure Site Recovery) is not designed for SQL Database geo-replication failover.

Answer

Use Azure Logic Apps with a timer trigger to check the database status every minute and initiate failover if unreachable.

Answer

Schedule a SQL Agent job on the secondary to run a script that checks connectivity and fails over.

Answer

Configure Azure Site Recovery for the SQL database.

Question 11

A company uses Azure SQL Database and wants to automate the process of refreshing a development database from production backups weekly. Which Azure service should be used to orchestrate this process including restore and post-restore scripts?

Accepted Answer

Azure Automation with PowerShell runbooks. Azure Data Factory supports copy activity but not native restore. Azure Automation with PowerShell runbooks can orchestrate the entire workflow. Azure SQL Database elastic jobs are for T-SQL tasks across databases but not for restore operations. Azure Logic Apps can also orchestrate but is less suited for complex scripting.

Answer

Elastic Database Jobs

Answer

Azure Logic Apps

Answer

Azure Data Factory

Question 12

You need to automate the deployment of a new Azure SQL Database with a specific performance tier, collation, and firewall rules using infrastructure as code. Which technology should you use?

Accepted Answer

ARM template with Microsoft.Sql/servers/databases resource definition.. Option B is correct because Azure Resource Manager (ARM) templates are the standard infrastructure-as-code tool for Azure resources. Option A is wrong because PowerShell can be used but is procedural rather than declarative. Option C is wrong because Azure CLI is also procedural. Option D is wrong because SQL Server Management Studio is a GUI tool, not for automation.

Answer

Azure CLI script with az sql db create command.

Answer

SQL Server Management Studio (SSMS) with a deployment script.

Answer

PowerShell script with New-AzSqlDatabase cmdlet.

Question 13

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?

Accepted Answer

Because SQL injection detection is already handled by Microsoft Defender for SQL.. Option C is correct. SQL Injection alerts may be disabled if an alternative mechanism, such as Microsoft Defender for SQL, is used to detect SQL injection attacks. Option A is wrong because disabling alerts does not reduce cost; it reduces security. Option B is wrong because there is no conflicting policy. Option D is wrong because there is no known compatibility issue.

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 14

You are configuring automated performance tuning for an Azure SQL Database. You want to automatically create missing indexes and drop unused indexes, but you need to prevent automatic index creation during peak business hours (9 AM - 5 PM). What is the best approach?

Accepted Answer

Enable automatic tuning but set the 'INDEX_CREATE' option to 'OFF' and use a schedule to run a script that enables it during off-peak hours and disables during peak hours.. Option D is correct because Azure SQL Database's automatic tuning can be enabled but with a schedule that restricts index creation to off-peak hours using the 'SET AUTOMATIC_TUNING' command with a schedule. Options A and B do not provide scheduling. Option C is not available.

Answer

Enable automatic indexing in Azure SQL Database and rely on the system to decide when to create indexes.

Answer

Use SQL Server Agent to schedule index creation scripts that check for missing indexes.

Answer

Use Azure Automation to run index analysis scripts only during off-peak hours.

Question 15

You are designing an automation solution to deploy Azure SQL Database schema changes using CI/CD pipelines. The solution must support rollback if a deployment fails and must integrate with Azure DevOps. Which two components should you include in your pipeline?

Accepted Answer

Azure SQL Database project with DACPAC deployment.. Option B is correct because Azure SQL Database deployment task in Azure DevOps can execute schema scripts. Option E is correct because using Azure SQL Database projects with DACPAC allows version-controlled schema deployment. Option A is incorrect because Azure Automation runbooks are more for operational tasks, not CI/CD. Option C is incorrect because Azure SQL Managed Instance is a different service. Option D is incorrect because Data Factory is for ETL, not schema deployment.

Answer

Azure Automation runbook to execute T-SQL scripts.

Answer

Azure Data Factory pipeline to copy schema changes.

Answer

A PowerShell script that uses Invoke-SqlCmd to deploy to Azure SQL Managed Instance.