Onprem server/Database to Cloud - Data Copy Automation via Azure Data Factory

Question

Onprem server/Database to Cloud - Data Copy Automation via Azure Data Factory

Nalini Bhavaraju 170

Hi Team,

If I am copying data from a On prem server/Database to Azure Data Lake storage via Azure Data factory, Can I automate the ADF Pipeline for this copy to happen once every day?

How does the self hosted integration Environment managed in this case to avoid connection error when automated?

And, what are the pros and cons of having the Self hosted Environment service not stopped for this pipeline execution? In terms of costs, CPU memory/Usage etc.,

Is there any way we can start and stop the service automatically after usage during the scheduled automation?

Also, If I automate the process and if I am not onsite will the pipeline fail to connect to the database/server as the server is On-prem?

Thanks,

Nalini

2 answers

Your answer

Answer 1

Hi Manoj,

Thanks for the detailed writeup.

Here are the answers for the follow up questions -

Do you have any specific data size or type you are working with that could impact the execution time of the pipeline? Data size is not very huge its around 3-5 GB
Have you already set up your self-hosted integration runtime? Yes, But When I trigger my ADF pipeline for copying data from SQL to ADLS - will start the runtime and stop it once after copy.
Are there specific times when you anticipate the data will be accessed more frequently or have stricter availability needs? Planning to setup a daily once Trigger to copy data No specific timeline for trigger decided yet.
Would you like guidance on setting up the Azure Function or Automation scripts? yes Is there a documentation that helps me to understand more on how to leverage Azure Functions/Azure Automation for self hosted integration Environment using Azure Data factory.

Thanks,

Nalini.

Answer 2

Manoj Kumar Boyini 8,570 Microsoft External Staff Moderator

Hi Nalini Bhavaraju

It looks like you’re trying to automate data copies from an on-prem server to Azure Data Lake Storage using Azure Data Factory (ADF). Here’s how you can manage that:

Automating the ADF Pipeline

Yes, you can definitely automate your ADF pipeline to run daily. You can set a schedule by using triggers within Azure Data Factory. This way, your data can be copied at a specific time each day without needing manual intervention.

Self-hosted Integration Runtime Management

When using a self-hosted integration runtime to connect to your on-premises database, it's essential to keep it running to avoid connection errors during automation. If it's stopped, any scheduled pipeline execution might fail to connect to the database.

Pros and Cons of Keeping the Self-hosted Environment Running

Pros:

Continuous Availability: Always ready to process data, which helps in meeting your scheduled automation requirements.
Reduced Connection Errors: Less likelihood of running into connectivity issues during scheduled jobs.

Cons:

Cost: You incur costs based on the compute resources allocated to the integration runtime.
CPU and Memory Usage: Continuous operation can lead to higher CPU and memory consumption, which may not be efficient if the integration runtime is used infrequently.

Automatic Start and Stop

Currently, Azure Data Factory doesn't support automatic start/stop of the self-hosted integration runtime directly. However, you can leverage Azure Functions or Azure Automation to script the stopping and starting of the integration runtime, allowing it to only run during specific hours when data copy operations are scheduled.

Failure to Connect When Offsite

If the ADF pipeline is automated to run while you are not onsite, it shouldn’t fail to connect to the on-prem DB as long as the integration runtime is properly configured and running. It’s crucial to ensure that the integration runtime has the necessary access permissions and that there are no firewall rules blocking the connection.

Follow-up Questions:

Do you have any specific data size or type you are working with that could impact the execution time of the pipeline?
Have you already set up your self-hosted integration runtime?
Are there specific times when you anticipate the data will be accessed more frequently or have stricter availability needs?
Would you like guidance on setting up the Azure Function or Automation scripts?

Hope this helps get your data workflow up and running! If you have further questions, feel free to ask!

References:

Manoj Kumar Boyini 8,570 Reputation points Microsoft External Staff Moderator

2026-02-10T12:54:18.4066667+00:00

Hi Nalini Bhavaraju,

I hope you had a chance to review the information shared earlier, and I hope this information has been helpful! If you still have questions, please let us know what is needed in the comments so the question can be answered.
Manoj Kumar Boyini 8,570 Reputation points Microsoft External Staff Moderator

2026-02-11T20:16:40.7066667+00:00

Hi Nalini Bhavaraju,

You can schedule the pipeline to run once per day by using an ADF Schedule Trigger. Triggers are the native mechanism in ADF for running pipelines on a recurring schedule, and the trigger blade provides options for creating, starting, and monitoring scheduled executions.

Because your source is on-prem, the Self-Hosted Integration Runtime (SHIR) must be online and reachable when the trigger fires. ADF routes the copy operation through the SHIR node; if the node is offline, ADF cannot reach your on-prem SQL Server and the pipeline will fail. For normal reliability, keep at least one SHIR node online. For production workloads, Microsoft recommends registering two or more nodes under the same SHIR to provide high availability if one node goes offline, the workload can automatically fail over to another. The SHIR service is lightweight when idle, and the installer documentation explains node registration and runtime behavior.

If you prefer not to leave a machine running continuously, you can automate starting and stopping the host or the SHIR service, but this must be done outside of ADF. A common approach is to host the SHIR on an Azure VM and use an Azure Automation runbook or a VM start/stop schedule to power the VM on approximately 10–15 minutes before the scheduled run. Once the SHIR appears as Online in ADF, the pipeline can safely execute, and the VM can be deallocated after completion. Another approach is to use Azure Automation Hybrid Runbook Worker (or a local scheduled task) to run PowerShell on the on-prem server to start and stop the SHIR Windows service. These approaches work, but they require a warm-up period and a health check before triggering the pipeline so the SHIR can register its heartbeat with ADF.

To make the pipeline more resilient, configure appropriate retry policies on the Copy Activity. For file-based copies, the Copy Activity can resume from the last successful file; for other scenarios you can configure retry counts and timeouts. You can monitor pipeline activity and SHIR node status in the ADF Monitor section. Alerts on SHIR being Offline or Limited can help prevent unexpected failures when you're not onsite.

If you run into connectivity issues, the SHIR troubleshooting documentation includes steps for diagnosing common registration, heartbeat, proxy, and runtime errors. The Integration Runtime monitoring blade will show whether the SHIR node is Online, Limited, or Offline before the pipeline runs.

Helpful References:
https://learn.microsoft.com/en-us/azure/data-factory/how-to-create-schedule-trigger?tabs=data-factory
https://learn.microsoft.com/en-us/azure/data-factory/create-self-hosted-integration-runtime?tabs=data-factory
https://learn.microsoft.com/en-us/azure/automation/automation-hybrid-runbook-worker
https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-overview
https://learn.microsoft.com/en-us/azure/data-factory/self-hosted-integration-runtime-troubleshoot-guide?tabs=data-factory
https://learn.microsoft.com/en-us/azure/data-factory/monitor-integration-runtime

Hope this helps, Please let us know if you have any questions and concerns.
Nalini Bhavaraju 170 Reputation points

2026-02-12T15:39:54.5233333+00:00

Hi Manoj,

Thanks for the detailed explanation and suggestions.

I will have to compare SHIR on a VM vs run SHIR using Azure Automation Hybrid Runbook Worker to make a better choice.

I will look into the documentation shared.

As we are testing the flow, Once I automate the pipeline in production I will get back to you in case of any other questions.

I can accept the answer once I try it and it worked for me.

Thanks,

Nalini.
Manoj Kumar Boyini 8,570 Reputation points Microsoft External Staff Moderator

2026-02-13T17:58:17.69+00:00

Hi Nalini Bhavaraju,

I hope information shared earlier helpful. Could you please confirm have you gone through it.
Manoj Kumar Boyini 8,570 Reputation points Microsoft External Staff Moderator

2026-02-14T15:36:59.5566667+00:00

Hi Nalini Bhavaraju

We haven’t received a response. Please let us know if you need any additional information or assistance from my side.

Share via

Onprem server/Database to Cloud - Data Copy Automation via Azure Data Factory

2 answers

Automating the ADF Pipeline

Self-hosted Integration Runtime Management

Pros and Cons of Keeping the Self-hosted Environment Running

Automatic Start and Stop

Failure to Connect When Offsite

Follow-up Questions:

References:

Your answer