Need Help Executing a Remote Task from SQL Server Agent

Question

There is a scheduled task running on a batch server via the Windows Task Scheduler that sometimes overlaps with other scheduled jobs in the SQL Server Agent causing CPU issues. We can solve this if we can have SQL Server Agent execute the .exe on the remote server to ensure the schedules never conflict.

Is there any way to do this with SQL Server Agent, perhaps with a Powershell command? If so, can you please give me the steps?

The .exe on the remote server is a data importer for SQL. It is a stand alone .exe. We want it to run on the batch server so it doesn't cause resource contention on the DB server. We are running SQL 2008 R2 Enterprise.

Please let me know if any additional information is required to answer this question.

Answer 1

I have only done this a couple of times and quite a while ago. At that time I used PSEXEC.EXE from the Windows Internals. You can read the instructions here:

http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx

For Powershell remote execution there was a discussion in StackOverflow that satisfied the questioner. Powershell is probably the way to go these days.

https://stackoverflow.com/questions/9535515/powershell-execute-remote-exe-with-command-line-arguments-on-remote-computer

However you run the remote job, you can schedule it in the SQL Server Agent process that runs the other jobs. If you think it would work for you, you could also set the job to run on a defined period of 'idleness'. (I don't think that is what you want, but you can look into it under the SQL Server Agent discussion on Agent schedules at http://technet.microsoft.com/en-us/library/ms189065.aspx.)