DP-300 Practice Test 38 — 15 Questions

Question 1

You are a database administrator for an e-learning platform that uses Azure SQL Database with the General Purpose tier. The database stores course content and user progress. You need to automate the process of exporting the database schema (without data) to a BACPAC file every week for version control. The BACPAC file must be stored in an Azure Blob Storage container with a retention policy of 30 days. The automation must use Azure native services and should not require manual intervention. What should you do?

Accepted Answer

Create an Azure Automation runbook with PowerShell that uses the New-AzSqlDatabaseExport cmdlet to export the schema-only BACPAC to a Blob Storage container. Schedule the runbook to run weekly.. Option A is correct. Azure Automation runbooks can use the SqlPackage.exe utility (or the New-AzSqlDatabaseExport cmdlet) to export a BACPAC and save it to Blob Storage. You can schedule the runbook weekly. Option B is incorrect because Data Factory is more complex than needed. Option C is incorrect because Elastic Database Jobs cannot export BACPAC. Option D is incorrect because SQL Agent is not available in Azure SQL Database.

Answer

Use Azure Data Factory with a Copy activity to export the database schema to a BACPAC file in Blob Storage.

Answer

Use Elastic Database Jobs to run a T-SQL script that uses the BACKUP DATABASE command to a URL.

Answer

Create a SQL Agent job on the database to run SqlPackage.exe.

Question 2

You need to automate the execution of a T-SQL script against all user databases in an Azure SQL Database elastic pool. The script should run on a schedule and results should be logged to a table. Which feature should you use?

Accepted Answer

Elastic Database Jobs. Elastic Database Jobs are designed for executing T-SQL across many databases in a pool. SQL Agent is not available in Azure SQL Database. Azure Automation runbooks can run T-SQL but require more setup. Azure Data Factory is for data movement, not T-SQL execution.

Answer

SQL Agent jobs

Answer

Azure Data Factory

Answer

Azure Automation with PowerShell runbooks

Question 3

You have an Azure SQL Database that uses a SQL Agent job to run a critical ETL process every night. The job recently started failing intermittently. You need to automate the monitoring and alerting of job failures, and automatically retry the job twice with a 10-minute interval between retries. What should you configure?

Accepted Answer

Configure the job step's 'Retry attempts' and 'Retry interval (minutes)' settings in the SQL Agent job step properties.. Option C is correct because SQL Agent jobs have built-in retry settings in the job step properties. Option A is wrong because Azure Monitor alerts can notify but do not automatically retry the job. Option B is wrong because it would require custom logic. Option D is wrong because Power Automate can trigger retries but requires additional setup and is not native to SQL Agent.

Answer

Modify the job to use a T-SQL loop that checks job history and re-runs the job step.

Answer

Use Microsoft Power Automate to poll the job history and re-run the job if failed.

Answer

Create an Azure Monitor alert on the job failure event and use a webhook to trigger a PowerShell script that retries the job.

Question 4

You need to automate the process of refreshing a development copy of an Azure SQL Database from a production backup every night. The production database is in the West US region, and the dev database is in East US. What is the most cost-effective method?

Accepted Answer

Use the Restore-AzSqlDatabase PowerShell cmdlet to restore the production database to the dev server using a point-in-time backup from the same day.. Option B is correct because Azure SQL Database supports point-in-time restore to a different server and region, which is cost-effective and automated via PowerShell or CLI. Options A and D require manual steps. Option C is for cross-region replication, which is not designed for nightly refresh.

Answer

Export the production database to a BACPAC file, store it in Azure Blob Storage, and import it to dev using a Data Factory pipeline.

Answer

Configure active geo-replication between production and dev, then initiate a planned failover each night.

Answer

Use Azure Data Studio to export the database as a script and run it on the dev server.

Question 5

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.. Option A is correct because SQL Agent can run T-SQL jobs on Managed Instance. Option C is correct because creating a stored procedure to check fragmentation and rebuild indexes is a standard approach. Option D is correct because logging to a table is necessary for tracking. Option B is incorrect because Azure Automation runbooks cannot directly connect to SQL Managed Instance easily without hybrid workers. Option E is incorrect because Elastic Jobs is for Azure SQL Database, not 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 6

You are managing an Azure SQL Managed Instance that hosts a critical database. You need to automate the export of daily backups to a storage account for long-term retention. The solution must minimize administrative overhead and support point-in-time restore within the retention period. What should you use?

Accepted Answer

Configure a long-term retention (LTR) backup policy for the Managed Instance.. Option B is correct because Managed Instance automated backups can be configured with long-term retention (LTR) policies that store backups in a geo-redundant storage account. Option A is wrong because Azure Backup requires additional configuration and is not the native backup solution for Managed Instance. Option C is wrong because SQL Agent jobs require manual setup and maintenance. Option D is wrong because BACPAC export is not a backup solution and does not support point-in-time restore.

Answer

Configure an Azure Backup policy for the Managed Instance.

Answer

Schedule a BACPAC export of the database to the storage account.

Answer

Create a SQL Agent job to perform backups using BACKUP DATABASE TO URL.

Question 7

You need to automate the backup strategy for an Azure SQL Database to meet a recovery point objective (RPO) of 5 minutes and a recovery time objective (RTO) of 1 hour. Which THREE features should you combine? (Choose three.)

Accepted Answer

Long-term retention (LTR) backup policy.. Options A, C, and D are correct. Active geo-replication provides automatic failover with low RPO, long-term retention for archival, and auto-failover groups for automated failover. Option B is wrong because point-in-time restore does not provide automated failover. Option E is wrong because Azure Backup service is not used for Azure SQL Database.

Answer

Azure Backup service.

Answer

Point-in-time restore (PITR).

Question 8

You need to automate the backup of an on-premises SQL Server database to Azure Blob Storage using the latest Microsoft recommended method. Which approach should you use?

Accepted Answer

Use SQL Server Management Studio to create a maintenance plan that uses the 'Back Up Database' task with destination = URL.. Option A is correct because SQL Server Backup to URL using the BACKUP TO URL command with a shared access signature is the recommended method for backing up to Azure Blob Storage. Options B and C are outdated or less secure. Option D requires additional software.

Answer

Use PowerShell with the Backup-SqlDatabase cmdlet and the -BackupContainer parameter.

Answer

Use Azure Backup for SQL Server in Azure VMs.

Answer

Use the 'Backup to Windows Azure' feature in SQL Server 2012.

Question 9

You are a database administrator for a company that uses Azure SQL Managed Instance. You need to automate the process of patching the operating system and SQL Server engine for all managed instances in a specific region. The automation must minimize downtime and ensure high availability. Which two actions should you include in your automation strategy?

Accepted Answer

Configure a maintenance window using Azure Portal or PowerShell.. Option A is correct because Azure SQL Managed Instance automatically applies updates, but you can configure a maintenance window to control the timing. Option C is correct because using a failover group with read-scale replicas ensures that during patching, traffic can be redirected to a secondary replica, minimizing downtime. Option B is incorrect because manual patching is not supported for Azure SQL Managed Instance; Microsoft manages patching. Option D is incorrect because scaling up is not related to patching automation. Option E is incorrect because enabling Azure Update Manager is not applicable to Azure SQL Managed Instance; it's for IaaS VMs.

Answer

Use Azure Policy to automatically scale up the instance before patching.

Answer

Create an Azure Automation runbook to manually apply OS and SQL patches.

Answer

Enable Azure Update Manager for the managed instances.

Question 10

You have an Azure SQL Database configured with active geo-replication. You need to automate the failover process in the event of a regional outage, ensuring minimal data loss and automatic failback when the primary region recovers. What should you implement?

Accepted Answer

Configure an auto-failover group with a grace period of 1 hour.. Option A is correct because Azure SQL Database auto-failover groups provide automated failover and failback with a grace period for data loss tolerance. Option B is wrong because it does not automate failover. Option C is wrong because manual failover does not provide automatic failback. Option D is wrong because ScheduledExecutorService is a manual workaround that does not integrate with Azure failover groups.

Answer

Set up a PowerShell script that checks primary database health and initiates failover.

Answer

Use a ScheduledExecutorService in a Java application to monitor and failover.

Answer

Enable geo-replication and manually trigger failover when needed.

Question 11

You are responsible for automating the deployment of an Azure SQL Database with associated firewall rules, a Microsoft Entra ID admin, and a vulnerability assessment baseline. The deployment must be idempotent and repeatable. Which THREE tools or approaches should you use? (Choose three.)

Accepted Answer

Bicep template.. Options A, B, and D are correct. ARM templates define infrastructure, Azure DevOps pipelines automate deployment, and Bicep is a declarative language for ARM. Option C is wrong because the Azure portal is manual. Option E is wrong because SQL Server Management Studio is not for infrastructure deployment.

Answer

SQL Server Management Studio.

Answer

Azure portal.

Question 12

You have an Azure SQL Database that uses a managed identity to access Azure Key Vault for storing column master keys. You need to automate the rotation of the column master key using Azure Automation. Which steps should you include in the runbook?

Accepted Answer

Authenticate using Connect-AzAccount -Identity, then use Set-AzKeyVaultKey to create a new key version, and update the column encryption key using Invoke-SqlCmd.. Option C is correct because the runbook must authenticate to Key Vault using the managed identity, create a new key, update the column encryption key, and then clean up. Options A and B miss authentication steps. Option D incorrectly uses connection strings.

Answer

Use PowerShell cmdlets without authentication; the runbook runs under the Automation account's identity.

Answer

Use the SQL Server Management Objects (SMO) to rotate the key directly from the runbook without Key Vault.

Answer

Use the Key Vault REST API with a client secret stored in the runbook.

Question 13

You are responsible for automating index maintenance and statistics updates across multiple Azure SQL Databases in an elastic pool. Each database has varying workloads and sizes. The automation must run weekly during low-traffic periods and should not degrade performance for critical applications. Which three actions should you take to implement a reliable and efficient automation solution?

Accepted Answer

Create an Azure Automation account and schedule a PowerShell runbook that connects to each database and runs index maintenance scripts.. Option A is correct because Azure Automation with PowerShell runbooks allows scheduling and executing T-SQL scripts across databases. Option C is correct because Elastic Database Jobs can target a group of databases in an elastic pool. Option E is correct because Azure SQL Database's automatic tuning can handle index and statistics maintenance, reducing manual effort. Option B is incorrect because enabling auto-failover groups does not automate maintenance tasks. Option D is incorrect because SQL Agent jobs are not available in Azure SQL Database (only in Managed Instance).

Answer

Configure SQL Agent jobs in each database to run the maintenance scripts.

Answer

Enable auto-failover groups for all databases to automate failover during maintenance.

Question 14

You manage an Azure SQL Database that uses Azure SQL Database elastic jobs to execute maintenance scripts across multiple databases. You need to ensure that job execution is logged in a central database for auditing and troubleshooting. What should you configure?

Accepted Answer

Ensure the Elastic Job agent uses a dedicated job database to store job execution logs.. Option A is correct because elastic jobs automatically log job execution history in the job database (which is a user-defined database). Option B is wrong because the elastic job agent uses a job database, not Azure Table Storage. Option C is wrong because Azure Monitor logs require separate configuration. Option D is wrong because SQL Server Audit is for database auditing, not job logging.

Answer

Configure the job to write logs to Azure Table Storage using a custom output.

Answer

Enable SQL Server Audit on the target databases to capture job execution.

Answer

Set up Azure Monitor diagnostic settings for the elastic job agent.

Question 15

You are a senior database administrator for a financial services company that uses Azure SQL Managed Instance to host multiple customer databases. The company has a requirement to automatically execute a series of compliance scripts every Sunday at 2:00 AM against all databases in the instance. The scripts include checking for orphaned users, verifying data encryption, and auditing login attempts. The solution must log the execution results in a central table and send an email summary to the compliance team. You have been asked to implement this automation using built-in Azure features without relying on external tools like Power Automate or custom schedulers. What should you use?

Accepted Answer

Create a SQL Agent job that runs a T-SQL script using sp_foreachdb to execute the compliance scripts on each database, store results in a central database, and send an email using Database Mail.. Option D is correct because SQL Agent jobs in Managed Instance can execute T-SQL scripts across databases using sp_send_dbmail for email, and cross-database queries can log results to a central database. Option A is wrong because elastic jobs target Azure SQL Database, not Managed Instance. Option B is wrong because Azure Automation Runbooks require external connectivity and cannot directly execute T-SQL across databases. Option C is wrong because Logic Apps are external and require additional configuration.

Answer

Schedule a Logic App that connects to each database via the SQL connector and runs the scripts.

Answer

Use Azure Automation Runbooks with the PowerShell module for SQL Server to execute scripts on each database.

Answer

Create an Elastic Job agent with a job that runs the scripts against each database and logs results to a central database.