DP-300 Configure and manage automation of tasks Practice Question
You are a database administrator for a logistics company that uses Azure SQL Database. You need to automate the process of copying data from an on-premises SQL Server to Azure SQL Database every night. The data volume is large, and you want to minimize the impact on the source server. You also need to ensure that the copy operation is resilient to transient failures. What should you use?
⚠ Common exam trap
The trap here is assuming that Elastic Jobs can directly query on-premises sources, but they run in Azure and require additional connectivity.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Azure Data Factory with a self-hosted integration runtime and a copy activity.
Azure Data Factory with a self-hosted integration runtime is the best solution. It can connect to on-premises SQL Server, perform parallel data extraction to reduce source impact, and has built-in retry and resilience features. It is designed for scheduled, large-scale data movement. The other options either lack the necessary connectivity, are not optimized for performance, or do not provide the required resilience.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Azure Elastic Jobs with a T-SQL script that uses OPENROWSET to read from the on-premises server.
Why it's wrong here
Elastic Jobs run T-SQL on Azure SQL Database, but OPENROWSET cannot directly connect to an on-premises SQL Server without a linked server or other configuration. Moreover, this approach would not be efficient for large data volumes and lacks built-in retry logic. Elastic Jobs are not designed for cross-premises data movement.
- ✓
Azure Data Factory with a self-hosted integration runtime and a copy activity.
Why this is correct
Azure Data Factory with a self-hosted integration runtime can connect to on-premises SQL Server and Azure SQL Database. The copy activity can be scheduled and supports parallel reads to minimize source impact. It also has built-in retry policies to handle transient failures, making it resilient. This is the recommended approach for large-scale data movement.
- ✗
Azure Automation runbook that uses the Invoke-Sqlcmd cmdlet to copy data.
Why it's wrong here
Azure Automation runbooks can run PowerShell, but Invoke-Sqlcmd is not suitable for large data copies and would require a hybrid runbook worker to access on-premises. It lacks parallelization and robust retry mechanisms. This approach would be slow and unreliable for large volumes, and it would put significant load on the source server.
- ✗
SQL Server Integration Services (SSIS) with an Azure-SSIS integration runtime.
Why it's wrong here
SSIS with Azure-SSIS integration runtime requires deploying an SSISDB and managing the runtime, which adds complexity. While it can perform the copy, it is not as lightweight or as easy to schedule as Azure Data Factory. The question emphasizes minimizing source impact and resilience; Data Factory's copy activity is optimized for this scenario.
Go deeper
Related to this question
Learn chapter
Securing Data at Rest and in Transit
Key term
Azure SQL Performance Tuning
Azure SQL Performance Tuning is the process of optimizing the speed and efficiency of queries and database operations in Microsoft Azure SQL Database or SQL Managed Instance to reduce latency and improve throughput.
About these practice questions
This DP-300 question is part of Courseiva's 574-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Microsoft exam blueprint
This DP-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-300 exam.