2

I'm trying to set up multiserver administration on SQL Server 2016. I have two servers, one master and one target. I created a test job that does a simple select statement and then sends an email. I want that job to run on both master and target servers but it seems that I can't.

I can't believe that's not possible (tried to google for that but didn't find anything).

2
  • 1
    You have to create a local job if you want to run this in master also. Best approach is not to use a server where you need to run same jobs. See this answer which explains in detail. Jul 5, 2017 at 14:34
  • 1
    Actually, if you use SQL Server PowerShell, this should be possible but you have to create a credential that has appropriate access on each instance of SQL Server and a few other things that may make it seem complex but once setup and as long as the security on each instance is correct, you execute the correct PS SQL command, you could likely do this. I created a job on one instance that backed up all SQL Agent jobs on multiple instances in the past so I know I was able to get this completed and I kept the document of my setup as well apparently as I just cracked it open. Scary PowerShell!! Jul 6, 2017 at 4:07

1 Answer 1

2

You cannot do this unless you create another copy of the job under Local in the agent.

Details about doing this is given in this post.

1
  • 2
    agreed. I think once I got around this by creating a local procedure and a remote procedure. The local procedure would fire the local code, and also fire the remote procedure (via EXECUTE AT). The job would then run the local procedure which would, in turn, run the remote procedure.
    – SQLDevDBA
    Jul 5, 2017 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.