Posted Fri, 28 Aug 2015 14:19:39 GMT by Portal Admin

Using tsql script with sqlcmd to start and monitor SQL agent jobs when not using the Skybot BIF

 

Installing the Skybot Agent Service into a Microsoft Failover Cluster group

  1. Install the Skybot Agent on each node system of the Microsoft Cluster.
  2. Install to the same relative location on each node (default install is probably best).
  3. No need to install into any shared cluster storage.
  4. See that each node’s Skybot Agent can connect to the Skybot Server.
  5. On each node, open the Windows Services manager.
  6. Find the Skybot Scheduler Agent Server. Right click it and select Properties.
  7. Stop the service.
  8. Set Startup Type to Manual. Click OK.
  9. Start the Microsoft Failover Cluster Manager.
  10. Add a Generic Service to the cluster group (under “Services and applications”) where a Skybot Agent is desired.
  11. Right click the appropriate group and select “Add a resource > 4 – Generic Service”
  12. Select the Skybot Scheduler Agent Server from the list. Click Next.
  13. On the Confirmation screen click Next.
  14. If the operation was successful you will receive a Summary screen. Click Finish.
  15. The new cluster managed Service will appear under Other Resources in the group.
  16. Right click the new managed service and select Properties.
  17. Click the Policies tab.
  18. In “Response to resource failure” enable the radio button next to “If resource fails, do not restart”.
  19. This prevents an Agent failure from causing the cluster node to fail over to a different node.
  20. Click the Dependencies tab.
  21. Click in the grid to add a dependency.
  22. In the Resource column select the Name: {Server Name}  property.  {Server Name} will be the DNS name of the clustered application. 
  23. Click OK at the bottom to save these changes.
  24. Right click the same managed service again and select Properties.
  25. Click the General tab.
  26. Enable the check box “Use Network Name for Computer name”
  27. Click OK at the bottom to save the change.
  28. This step needs to be done separately from adding the dependency or you will receive an error message about needing a dependency.
  29. Right click the same managed service again and select “Bring this resource online”.
  30. The status should go to Online and stay Online.
  31. Go to the Skybot Scheduler server web page and log in.
  32. In the Skybot Scheduler Agents list the new Agent should appear that begins with the Name of the cluster application.
  33. Right click the new agent and select “Edit Agent”.
  34. Click the [EDIT] link in the General section.
  35. Enable the check box next to “Allow agent name clustering”. This will allow the Agent to come online as the same agent no matter which cluster node it is running on.
  36. Click Save at the bottom of the page.
  37. The old Agent entries from before the Skybot Agent was added to the cluster group can be deleted.
  38. Configuration is now complete. The Skybot Agent should go online/offline with that cluster group and move along with the other cluster group resources.

 

Notes:

-          Setting the option to “Use Network Name for Computer name” makes it unnecessary to otherwise configure the Windows environment or agent xml file for a fixed Agent name.  If a cluster group does not have a DNS name assigned then follow the older Agent Name Clustering documentation to set a consistent name for the Agent as it moves between nodes.

-          Updating an agent installed into a Microsoft Failover cluster in the manner described above will require running the Agent installer to update each node separately.

-          The Skybot SQL Server Command will not currently work with a SQL Server instance residing in a Microsoft Failover Cluster.

 

Using a tsql script with sqlcmd to start and monitor a SQL Server Agent Job from a Skybot Job

The monitor.tsql script starts the SQL Server Agent Job and then monitors the execution status every 3 seconds to see if it is still running.  Once the Job is no longer executing it checks the last run outcome and outputs it to stdout.  Example:

last_run_outcome=1

The possible values of last_run_outcome are:

  • 0 = Failed
  • 1 = Succeeded
  • 3 = Canceled
  • 5 = Unknown

 

The SQL Server must be configured to allow ad hoc distributed q              ueries in order for the monitor.tsql script to work.  See http://msdn.microsoft.com/en-us/library/ms187569.aspx for instructions on how to allow ad hoc distributed queries.

 

The example here assumes the SQL Server is allowing Windows Authentication and that the user account in the Skybot Job Agent Environment is a Windows user with sufficient authority to issue the tsql statements and run the SQL Server Agent Job.

 

This script does not allow for canceling or ending the SQL Server Agent Job by ending the Skybot Job running the script.

 

-          Copy the monitor.tsql script to the same location on each node where a SQL Server instance could run.  For this example we’ll just use c:\monitor.tsql.

-          Create a new Skybot Return Code.

o   In Skybot UI go to Scheduling Objects > Return Codes

o   Click Create Return Code

o   Enter Name, Description, and Tags as needed.  For this example the Name is “sqljobmonitor”

o   In the Values section Action should be Pass

o   Click Add

o   In the new line in the grid enter “0”

o   Enable check box “Search log for text”

o   Click Add in this section

o   Type = Contains, Text to match = ‘last_run_outcome=1’,  Regular Expressioin = False

o   Click Save

-          Create a new Skybot Job

o   In Skybot UI go to Jobs > Individual Jobs

o   Click Create Job

o   General section items as needed.  Set the Agent/Group to be the Skybot Agent running in the Microsoft Failover Cluster application.

o   In Agent Environment select Environment Type Username and Password

o   Enter a fully qualified Windows user with sufficient SQL Server authority in the Username field

o   Enter the Password and Confirm Password

o   Set Working Path to the directory where the monitor.tsql script is.  In this example “c:\”.

o   In Commands click Add to add a new command

o   Enter this command:

  • sqlcmd –S myserver\myinstance –i c:\monitor.tsql –v AGENTJOB=’mysqljobname’

o   Edit the Return Code for this command.  Select Shared Return Code “sqljobmonitor”

o   In Logging enable the check box for Save Job Log

o   Click Save to save the Job.

-          When this Job is run the monitor.tsql script will pick up the server name and SQL Server Job name entered the sqlcmd  parameters.  The SQL Server Agent Job ‘mysqljobname’ will run and when it ends the last run outcome will be checked.  If the last run outcome is 1 Success then the command will finish and the Skybot Job will end with Completed Status.  If the SQL Server Agent Job failed or was ended some other status then the command will finish and the Skybot Job will end with Failed Status.

-          This Job should work the same no matter which is the Microsoft Failover Cluster active node for the clustered application.

Credit for basics of the tsql script to this blog post: 

http://www.interworks.com/blogs/bbickell/2010/01/15/how-execute-and-monitor-agent-job-using-t-sql-sql-server-20052008

 

Info about execution status and last run outcome:

 http://technet.microsoft.com/en-us/library/ms186722.aspx

 

Info about ad hoc distributed queries Server Configuration Option:

http://msdn.microsoft.com/en-us/library/ms187569.aspx

 

You must be signed in to post in this forum.