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).

  • 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. – SqlWorldWide Jul 5 '17 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!! – Pimp Juice IT Jul 6 '17 at 4:07
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.

| improve this answer | |
  • 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. – MguerraTorres Jul 5 '17 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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