DP-300 Configure and manage automation of tasks Practice Test 7 — 15 Questions

Question 1

You are designing an automated backup retention policy for an Azure SQL Database. The business requirement is to retain daily backups for 30 days, weekly backups for 12 weeks, monthly backups for 12 months, and yearly backups for 7 years. Which backup retention type should you configure?

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

Accepted Answer

Long-term retention (LTR) policy. Long-term retention (LTR) policy is specifically designed to retain backups beyond the point-in-time restore (PITR) window, supporting daily, weekly, monthly, and yearly retention periods. Option C is correct. Option A (PITR retention) is too short and cannot retain backups for years. Option B (Backup vault with Azure Backup) is used for Azure VM backups and not for Azure SQL Database automated backups. Option D (Automated backup policy) refers to the default backup settings but does not include long-term retention.

Answer

Point-in-time restore (PITR) retention

Answer

Backup vault with Azure Backup

Answer

Automated backup policy

Question 2

Your company uses Azure SQL Managed Instance and wants to automate the creation of new databases for each development sprint. Each database must be a copy of a template database with specific schema and data. You need to recommend an automation solution that minimizes manual steps and integrates with your CI/CD pipeline. What should you use?

Accepted Answer

Create an Elastic Database Job that runs CREATE DATABASE AS COPY OF.... For Azure SQL Managed Instance, automating database creation with CI/CD integration can be achieved using Elastic Database Jobs (now Elastic Jobs). These jobs can run T-SQL commands such as 'CREATE DATABASE AS COPY OF' to create copies of a template database. Elastic Jobs can be triggered programmatically via REST API or Azure DevOps tasks, enabling seamless integration into a CI/CD pipeline. Option A (Logic App) does not support database copy operations. Option B (portal feature) requires manual steps. Option D uses PowerShell cmdlets ('az sql db copy' or 'Restore-AzSqlDatabase') that are specific to Azure SQL Database, not Managed Instance; the correct cmdlets for Managed Instance would be 'Restore-AzSqlInstanceDatabase' or T-SQL commands. Therefore, option C is the most appropriate recommendation.

Answer

Deploy an Azure Logic App with a SQL connector that triggers on a schedule.

Answer

Use the Azure portal's 'Create database from backup' feature.

Answer

Use an Azure DevOps pipeline with a PowerShell task that runs the 'az sql db copy' command or the Restore-AzSqlDatabase cmdlet.

Question 3

You are troubleshooting a failed automated backup for an Azure SQL Database. The backup policy is configured for geo-redundant storage (RA-GRS). You notice that the last successful backup was 48 hours ago. The database is still online and accessible. What is the most likely cause of the backup failure?

Accepted Answer

The database is experiencing high transaction log generation, exceeding the backup throughput limit.. Azure SQL Database automated backups can fail if the database generates a high volume of transaction logs, causing the backup process to exceed its throughput limit or timeout. This can happen even if the database is online and accessible. Option A (storage account issues) would likely cause persistent failures for all backups, not just a single one. Option C (geo-replication link) is unrelated to backup failure. Option D (paused state) would prevent backups entirely. Therefore, B is the correct answer.

Answer

The backup storage account has been deleted or has incorrect firewall rules.

Answer

The geo-replication link is broken, causing backup failures.

Answer

The database is in a paused state due to serverless compute.

Question 4

You need to automate the deployment of an Azure SQL Database along with its firewall rules and performance tier using infrastructure as code. Which technology should you use?

Accepted Answer

Bicep templates. Bicep is a domain-specific language for deploying Azure resources declaratively. It is the recommended infrastructure as code tool for Azure. Option A is correct because Bicep templates allow you to define Azure SQL Database, firewall rules, and performance tier in a declarative manner. Option B (SSDT) is used for database schema management, not resource deployment. Option C (T-SQL scripts) are for database queries and management, not infrastructure. Option D (PowerShell scripts) can automate tasks but are procedural, not declarative IaC like Bicep.

Answer

SQL Server Data Tools (SSDT) database projects

Answer

T-SQL scripts

Answer

PowerShell scripts

Question 5

Your Azure SQL Database is configured with active geo-replication. You need to automate the failover process in case of a regional outage. The solution should ensure minimal data loss and support testing without affecting the production environment. What should you use?

Accepted Answer

Create an Azure Automation runbook that monitors health and executes a manual failover using PowerShell cmdlet Start-AzSqlDatabaseFailover.. Azure SQL Database active geo-replication supports manual failover only; automatic failover is not available. To automate the process, you can use Azure Automation runbooks that invoke the Start-AzSqlDatabaseFailover cmdlet. This allows for scheduled or monitored failover, supports testing by targeting the secondary replica, and minimizes data loss by ensuring the primary is synchronized before failover. Option A is incorrect because Azure Traffic Manager performs DNS-level routing, not database failover. Option B is incorrect because Azure Load Balancer is for network traffic distribution, not SQL database failover. Option D is incorrect because geo-replication does not provide automatic failover; failover must be initiated manually or via automation.

Answer

Configure an Azure Traffic Manager profile with endpoint monitoring and failover.

Answer

Use Azure Load Balancer with a health probe to redirect traffic.

Answer

Enable automatic failover for the geo-replication group.

Question 6

You are automating the scaling of an Azure SQL Database based on workload patterns. The database uses the DTU purchasing model. You need to ensure that scaling actions do not cause connection drops. Which scaling method should you use?

Accepted Answer

Use the Azure portal or PowerShell to change the service tier within the same DTU tier (e.g., S2 to S3) to avoid downtime.. Azure SQL Database supports online scaling without connection drops when scaling within the same DTU tier (e.g., from S2 to S3). This is because the database remains online and the operation is a metadata change. Option D is correct. Option A is incorrect: creating a new database and using point-in-time restore does not scale the existing database and causes downtime during restore. Option B is incorrect: ALTER DATABASE with ONLINE option is for file operations, not for scaling service tiers. Option C is incorrect: Elastic Database Jobs are for scheduling scripts across databases, not for scaling operations.

Answer

Create a new database at the target size and use point-in-time restore to copy data.

Answer

Scale using the ALTER DATABASE T-SQL command with the ONLINE option.

Answer

Use Elastic Database Jobs to schedule scaling during maintenance windows.

Question 7

You need to automate the generation of a weekly report that shows the top 10 queries by CPU consumption in an Azure SQL Database. The report should be emailed to the DBA team. Which service should you use to gather the query performance data?

Accepted Answer

Query Performance Insight. Query Performance Insight in Azure SQL Database provides query-level performance metrics including CPU, duration, and execution count. It can be used to identify the top 10 queries by CPU consumption and automate report generation. Option D is correct. Option A (SQL Server Profiler) is a traditional tool for capturing SQL Server events, not for automated reporting in Azure. Option B (Database Engine Tuning Advisor) focuses on index recommendations, not report generation. Option C (Azure Monitor Metrics) provides platform-level metrics but lacks query-level granularity.

Answer

SQL Server Profiler

Answer

Database Engine Tuning Advisor

Answer

Azure Monitor Metrics

Question 8

You are implementing automated table partitioning maintenance for a large Azure SQL Database. The partitioning function uses a monthly range. You need to add a new partition for the next month and remove the oldest partition. What is the best way to automate this?

Accepted Answer

Schedule a T-SQL script via Elastic Database Jobs that uses ALTER PARTITION FUNCTION to SPLIT the range and MERGE the oldest boundary.. To automate monthly partition maintenance, you need to add a new partition for the next month (split the range) and remove the oldest partition (merge the oldest boundary). The T-SQL commands ALTER PARTITION FUNCTION ... SPLIT RANGE and ALTER PARTITION FUNCTION ... MERGE RANGE accomplish this. Scheduling these via Elastic Database Jobs provides automation. Option A's SELECT INTO and TRUNCATE are not partition management operations. Option B's ALTER INDEX REORGANIZE is for index maintenance, not partition boundary changes. Option D's SWITCH PARTITION moves data without altering the partition function/scheme.

Answer

Create a stored procedure that uses SELECT INTO to copy old data to a history table and then TRUNCATE the partition.

Answer

Use ALTER INDEX REORGANIZE to compress old partitions.

Answer

Use the SWITCH PARTITION operation to move the oldest partition to a staging table and then drop the staging table.

Question 9

Which TWO actions are required to automate the export of an Azure SQL Database to a BACPAC file on a monthly basis? (Choose two.)

Accepted Answer

Use Azure Automation or a scheduled Azure Function to call the Export-AzSqlDatabase cmdlet.. To automate the export of an Azure SQL Database to a BACPAC file on a monthly basis, you need a storage location for the file and an automation method. Option B is correct because Azure Automation or an Azure Function can trigger the Export-AzSqlDatabase cmdlet or REST API to perform the export on a schedule. Option E is correct because an Azure Storage account with a container is required to store the resulting BACPAC file. Option A is incorrect because LTR policy is for automated backups, not export to BACPAC. Option C is incorrect because a SQL Server on Azure VM is not needed; the export is initiated directly against the Azure SQL Database logical server. Option D is incorrect because SSIS is an ETL tool that is not necessary for a simple export operation; the export can be done via PowerShell, CLI, or REST API without SSIS.

Answer

Configure long-term retention (LTR) policy for the database.

Answer

Deploy a SQL Server on Azure VM to run the export command.

Answer

Install SQL Server Integration Services (SSIS) on a virtual machine.

Question 10

Which THREE components are part of an Azure SQL Managed Instance automated maintenance window configuration? (Choose three.)

Accepted Answer

Start time (e.g., 2:00 AM local time). Azure SQL Managed Instance allows you to configure a maintenance window including the day of week (e.g., Sunday), start time (e.g., 2:00 AM local time), and a notification email for upcoming maintenance. Option C is incorrect because maintenance windows are instance-level, not per database. Option D is incorrect because it applies to Azure SQL Database, not Managed Instance.

Answer

Database-level maintenance schedule

Answer

Maximum number of concurrent databases

Question 11

Which TWO tools can be used to automate the deployment of database schema changes to Azure SQL Database as part of a CI/CD pipeline? (Choose two.)

Accepted Answer

GitHub Actions with the Azure SQL Database deployment action. GitHub Actions with the Azure SQL Database deployment action (Option B) and Azure DevOps release pipeline with the SQL Server database project (DACPAC) deployment task (Option C) are both valid tools for automating schema deployments to Azure SQL Database in a CI/CD pipeline. Option A (Azure Data Studio SQLCMD mode) is manual and not designed for CI/CD integration. Option D (SQL Server Import and Export Wizard) is a data migration tool, not for schema changes. Option E (SSMS) is a management tool without native CI/CD automation.

Answer

Azure Data Studio with SQLCMD mode

Answer

SQL Server Import and Export Wizard

Answer

SQL Server Management Studio (SSMS)

Question 12

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database backup retention policy. The database is in a business-critical environment that requires point-in-time restore (PITR) for the last 28 days. What is the issue with this configuration?

Accepted Answer

Long-term retention properties (weeklyRetention, monthlyRetention, yearlyRetention) are not valid in a backupShortTermRetentionPolicies resource.. The ARM template shows both short-term retention (PITR) and long-term retention (LTR) properties. However, the resource type is 'backupShortTermRetentionPolicies', which only supports short-term retention properties like retentionDays. Long-term retention properties (weeklyRetention, monthlyRetention, yearlyRetention) belong to a different resource type ('backupLongTermRetentionPolicies'). Including them in the same resource will cause a deployment error. Option B correctly identifies this. Option A is incorrect because the JSON syntax is valid (camelCase is used in ARM templates). Option C is incorrect because diffBackupIntervalInHours is not relevant to this issue. Option D is incorrect because 28 days is within the allowed range for PITR (up to 35 days).

Answer

The JSON syntax is invalid because property names use camelCase instead of PascalCase.

Answer

The diffBackupIntervalInHours should be 12 for a business-critical database.

Answer

The retentionDays value of 28 exceeds the maximum allowed for PITR.

Question 13

Refer to the exhibit. A Logic App is configured to pause an Azure SQL Database named AdventureWorks every Friday at 8 PM. However, the database is not pausing. What is the most likely reason?

Accepted Answer

The pause action is not supported for Azure SQL Database.. Azure SQL Database does not support pause/resume; only Azure SQL Data Warehouse (now Azure Synapse Analytics dedicated SQL pool) supports it. Therefore, the pause action is not supported for Azure SQL Database, making option D correct. Option A is incorrect because the connection authentication is not the issue here. Option B is incorrect because the recurrence trigger schedule is valid. Option C is incorrect because the API version is not the primary reason; the operation itself is unsupported.

Answer

The SQL connection is not authenticated.

Answer

The recurrence trigger uses a schedule that is not valid.

Answer

The API version specified is outdated.

Question 14

Refer to the exhibit. A PowerShell script is used to update an Azure SQL Database. The script runs without errors, but the database is not moved to the specified elastic pool. What is the most likely cause?

Accepted Answer

The script sets both Edition and RequestedServiceObjectiveName, which is invalid when moving to an elastic pool.. The Set-AzSqlDatabase cmdlet can move a database into an elastic pool by specifying the ElasticPoolName parameter. However, when moving a database to an elastic pool, you must not specify the RequestedServiceObjectiveName parameter because the service objective is determined by the pool. The script incorrectly sets both Edition and RequestedServiceObjectiveName, causing a conflict. Option C correctly identifies this issue. Option A is incorrect because the pool not existing would cause an error, not a silent failure. Option B is incorrect because the ServerName parameter does not require a fully qualified domain name; the resource group and server name suffice. Option D is incorrect because the -Force parameter is not required for pool moves.

Answer

The elastic pool 'pool-prod' does not exist.

Answer

The ServerName parameter is missing the fully qualified domain name.

Answer

The cmdlet requires the -Force parameter to move a database.

Question 15

You are a database administrator for an Azure SQL Database. You need to automate the deployment of schema changes across multiple databases in an elastic pool. Which Azure service should you use to orchestrate these deployments?

Accepted Answer

Azure DevOps. Azure DevOps with its release pipelines can manage the deployment of schema changes across multiple databases. Option A is wrong because Azure Functions is for event-driven code, not orchestrated deployments. Option B is wrong because Azure Automation primarily manages infrastructure tasks, not database schema deployments. Option D is wrong because Azure Logic Apps is for workflow integration, not database schema deployment.

Answer

Azure Functions

Answer

Azure Automation

Answer

Azure Logic Apps