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

Question 1

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 are managed by the platform. If backups fail, it could be due to high write activity or resource saturation causing a backup timeout. Network issues or storage problems are less likely because the database is online. Option A is correct. Option B is incorrect because the database is online. Option C is for geo-replication. Option D is a configuration that would affect all backups, not just one.

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 2

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 B is correct. Option A is for SQL Server deployments only. Option C is a scripting language, not IaC. Option D is for database schema changes, not resource deployment.

Answer

SQL Server Data Tools (SSDT) database projects

Answer

T-SQL scripts

Answer

PowerShell scripts

Question 3

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 supports active geo-replication with manual or forced failover. To automate, you can use Azure Automation runbooks with the Start-AzSqlDatabaseFailover cmdlet. Option A is correct. Option B is for virtual networks. Option C is for load balancing. Option D is incorrect because geo-replication does not have automatic failover.

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 4

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 (no connection drops) for both DTU and vCore models. However, when scaling up or down, a brief failover may occur if the target service objective is different enough. For DTU, scaling between tiers (e.g., Standard to Premium) causes a failover. Within the same tier, scaling is typically online. Option A is correct. Option B is incorrect because PITR does not scale. Option C is not necessary. Option D is incorrect because scaling is online for same-tier changes.

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 5

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 queried via DMVs or the Azure portal. Option C is correct. Option A is for database schema. Option B is for query hints. Option D is not for Azure SQL Database.

Answer

SQL Server Profiler

Answer

Database Engine Tuning Advisor

Answer

Azure Monitor Metrics

Question 6

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.. Splitting and merging partition ranges is done with T-SQL ALTER PARTITION FUNCTION and ALTER PARTITION SCHEME. The SWITCH operation moves data to a staging table, but splitting does not require it. Option A is correct. Option B is for columnstore indexes. Option C is for data archiving. Option D is for data movement, not partition management.

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 7

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 export a database to BACPAC, you need an Azure Storage account to store the file. The export is initiated via the Azure portal, PowerShell, Azure CLI, or REST API. No SQL Server on VM is required. Option B and Option D are correct. Option A is not required as the export is done by Azure. Option C is for SQL Server on VM. Option E is a feature for automated backups, not exports.

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 8

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 (day of week, time, and duration). You can also set a notification email for upcoming maintenance. Option A, Option C, and Option E are correct. Option B is not configurable per database. Option D is for Azure SQL Database, not Managed Instance.

Answer

Database-level maintenance schedule

Answer

Maximum number of concurrent databases

Question 9

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. Azure DevOps with SQL Server database projects (DACPAC) is a standard approach for schema deployment. GitHub Actions also supports deploying DACPACs or running SQL scripts. Option A and Option C are correct. Option B is for SQL Server on-premises. Option D is for running T-SQL, not CI/CD. Option E is for data migration, not schema changes.

Answer

Azure Data Studio with SQLCMD mode

Answer

SQL Server Import and Export Wizard

Answer

SQL Server Management Studio (SSMS)

Question 10

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, PITR retention is set to 28 days (retentionDays), which is valid. The LTR properties (weeklyRetention, monthlyRetention, yearlyRetention) are part of a different resource (backupLongTermRetentionPolicies). Mixing them in the same resource will cause a deployment error. Option C is correct. Option A is wrong because 28 days is within limits. Option B is wrong because LTR is not part of this resource. Option D is wrong because the JSON is syntactically correct.

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 11

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 API operation is invalid for Azure SQL Database. Option B is correct. Option A is incorrect because the connection is properly referenced. Option C is incorrect because the recurrence is valid. Option D is incorrect because API version is not the primary issue.

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 12

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, the Edition parameter must match the pool's edition (e.g., Standard for Standard pool). The script sets Edition to 'Standard', which is correct. But the cmdlet also requires that the database is not already in a pool; if it is, you need to specify -RemoveElasticPool first. However, the most common issue is that the Edition parameter conflicts if the database is in a different service tier. Option D is correct: the script sets Edition and RequestedServiceObjectiveName simultaneously, which can cause a conflict because when moving to a pool, you should not specify RequestedServiceObjectiveName. Option A is incorrect because version is not a parameter. Option B is incorrect because the pool exists. Option C is incorrect because -Force is not required.

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 13

You are a database administrator for an Azure SQL Managed Instance. 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 Automation primarily manages infrastructure tasks, not database schema deployments. Option B is wrong because Azure Logic Apps is for workflow integration, not database schema deployment. Option D is wrong because Azure Functions is for event-driven code, not orchestrated deployments.

Answer

Azure Functions

Answer

Azure Automation

Answer

Azure Logic Apps

Question 14

You manage an Azure SQL Database that uses elastic jobs to automate index maintenance. The job fails intermittently with the error 'Cannot open server 'myserver' requested by the login.' The job agent uses a database-scoped credential. What is the most likely cause?

Accepted Answer

The credential is not mapped to a server login in the master database.. The error indicates that the login cannot access the server. For elastic jobs, the credential must be mapped to a login that has access to the target databases. Option A is wrong because the credential's user must have the necessary permissions, but the specific error is about server access. Option C is wrong because firewall rules would cause a different error. Option D is wrong because the job agent itself does not need a login on the target server.

Answer

The credential's user does not have ALTER ANY DATABASE permission.

Answer

The target database's firewall is blocking the job agent's IP address.

Answer

The job agent itself needs a server-level login.

Question 15

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. Long-term retention (LTR) policies can be configured to keep backups for up to 10 years. Option A is wrong because SQL Server Agent is not available in Azure SQL Database. Option C is wrong because Azure Backup is for IaaS VMs, not PaaS databases. Option D is wrong because elastic jobs are for scheduling tasks across multiple databases, not for backup automation.

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.