Sql Server - Running a Job on Multiple servers

  • I am using SqlServer 2008.

    I have a Sql Server Job that checks Disk Space and emails admins if the space is less than 15%

    Instead of adding that Job to all servers is it Possible to run the Job on all servers ?

    (I have those servers added as Linked Servers)

  • SmilingLily (1/30/2015)


    I am using SqlServer 2008.

    I have a Sql Server Job that checks Disk Space and emails admins if the space is less than 15%

    Instead of adding that Job to all servers is it Possible to run the Job on all servers ?

    (I have those servers added as Linked Servers)

    If you have the Enterprise Edition you can set up a Master server to hold a set of jobs that are then deployed to Target servers. The management of the job is centralized on the Master server, giving you one point of management.

    If you don't have Enterprise Edition then there is one thing that comes to mind. You could query sys.servers for returning the Linked Server names. Then dynamically build a SQL statement to execute your disk space check using the Linked Server names to traverse your network to check disk space on the other servers.

  • Hi Imarkum - Thank you for the reply. I appreciate it. I tried setting it up and

    I had below errors

    Make sure Agent Service for 'MasteServerName' Is running

    Access Denied Exception From HRESULT

    Elist 'TargetServerName' Into 'MasterServerName'

    MSX Enlist failed for JobServer 'TargetServerName' - Enlist operation failed.

    When I searched Online to fix this issue they advised on Setting Encryption Option

    https://msdn.microsoft.com/en-us/library/ms365379.aspx

    Is there any other changes that I need to do?

    Is there a way I can do it with out opening the RegEdit?

    I mean is there a command or stored procedure that I can use to do it instead of manually editing?

  • SmilingLily (2/2/2015)


    Hi Imarkum - Thank you for the reply. I appreciate it. I tried setting it up and

    I had below errors

    Make sure Agent Service for 'MasteServerName' Is running

    Access Denied Exception From HRESULT

    Elist 'TargetServerName' Into 'MasterServerName'

    MSX Enlist failed for JobServer 'TargetServerName' - Enlist operation failed.

    When I searched Online to fix this issue they advised on Setting Encryption Option

    https://msdn.microsoft.com/en-us/library/ms365379.aspx

    Is there any other changes that I need to do?

    Is there a way I can do it with out opening the RegEdit?

    I mean is there a command or stored procedure that I can use to do it instead of manually editing?

    Looks to me like the issue is that sql agent service on the Master is not running, which would cause the failure related to the target not being able to enlist. Let me do some testing and get back to you.

  • lmarkum (2/2/2015)


    SmilingLily (2/2/2015)


    Hi Imarkum - Thank you for the reply. I appreciate it. I tried setting it up and

    I had below errors

    Make sure Agent Service for 'MasteServerName' Is running

    Access Denied Exception From HRESULT

    Elist 'TargetServerName' Into 'MasterServerName'

    MSX Enlist failed for JobServer 'TargetServerName' - Enlist operation failed.

    When I searched Online to fix this issue they advised on Setting Encryption Option

    https://msdn.microsoft.com/en-us/library/ms365379.aspx

    Is there any other changes that I need to do?

    Is there a way I can do it with out opening the RegEdit?

    I mean is there a command or stored procedure that I can use to do it instead of manually editing?

    Looks to me like the issue is that sql agent service on the Master is not running, which would cause the failure related to the target not being able to enlist. Let me do some testing and get back to you.

    So I looked into this some more and found that by default encryption is used when setting up Master/Target servers. Based on the link you found, I did manually change my encryption level on my local machine to zero (no encryption). I then re-ran the wizard for making my selected instance a Master Server and it completed fine. I understand that changing the encryption to none may not be what you want to do on a prod server. Changing the value in RegEdit was incredibly simple. Can you elaborate on why you don't want to open RegEdit?

    You can set up a certificate and import the certificate to provide SSL. I honestly am not familiar with creating certificates and importing them. I did find a link on enabling encrypted connections in SQL Server and other links on creating and importing certs in Windows.

    https://msdn.microsoft.com/en-us/library/ms191192(v=sql.105).aspx

  • Hi Imarkum - I am a Trainee DBA. DBA who is assigning me tasks is little skeptical about changing the reg keys manually. She wanted me to do more research on if I can find any Stored procedures or commands to do it.

    I only found shell scripts other than manually changing it. I am going to go ahead and change the option to 0 and 1 and I will keep you posted on the outcome.

    I read in some blog saying changing encryption option to 1 also worked fine for them.

    Thank you

  • I changed the Encryption on both master and target I also changed AllowDownloadedJobsToMatchProxyName - changed from 0 to 1 on the target

    I made progress but I am stuck with below error

    MSX Enlist failed for Job Server 'MasterServerName'

    The enlist operation Failed(Reason:SQL Server Agent Error: Unable to connect to MSX 'MasterServerName'(Microsoft Sql Server, Error : 22026)

    They both servers SQLAgents are running on the same windows service account.

    Detailed Log Info :

    Enlist TSX Progress

    Create MSXOperator (Success) Checking for an existing MSXOperator.

    Updating existing MSXOperator.

    Successfully updated MSXOperator.

    Make sure the Agent service for 'Test3' is running (Success) The service 'SQLSERVERAGENT' is running.

    Ensure the agent startup account for 'Test4' has rights to login as a target server (Success)

    Checking to see if the startup account for 'Test4' already exists.

    Login exists on server.

    Checking to see if login has rights to msdb.

    Login has rights to msdb.

    Checking to see if user is a member of the TargetServersRole.

    User is a member of the TargetServersRole.

    Enlist 'Test4' into 'Test3' (Error)

    Enlisting target server 'Test4' with master server 'Test3'.

    Using new enlistment method.

    Messages

    MSX enlist failed for JobServer 'Test4'. (Microsoft.SqlServer.Smo)

    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    The enlist operation failed (reason: SQLServerAgent Error: Unable to connect to MSX 'TEST3'.) (Microsoft SQL Server, Error: 22026)

  • SmilingLily,

    I would have to do some more research. I would encourage you to take your error message to the internet and do the same. While I have some experience with multi-server administration it is limited. I suggested it because I knew it was designed to address your initial need. I did post a question on Twitter asking for people more familiar with this technology to offer assistance here.

  • lmarkum (2/4/2015)


    SmilingLily,

    I would have to do some more research. I would encourage you to take your error message to the internet and do the same. While I have some experience with multi-server administration it is limited. I suggested it because I knew it was designed to address your initial need. I did post a question on Twitter asking for people more familiar with this technology to offer assistance here.

    Thank you ImarKum - I posted on Multiple sites. I will keep you posted on the fix (If I find any - Thank you so much for the support)

  • Finally I got it working.

    I changed the Service Account of SQL Server agent on both master and Target and it worked.

  • We use linked servers for that, when job is running on only one DBA monitoring Server. However we are still using one Stored Procs across all servers to complement space monitoring job. That stored proc (and many others) is being added to the new server at the time of its original build. Another alternative would be using a Powershell script.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply