Event Chains in SQL Sentry

When Event Manager (now Event Calendar), our first commercially available solution hit the market, we included our event chaining functionality within it. This functionality is still shipped today within our SQL Sentry solution.

But you may already be asking yourself… Self, what is this event chaining functionality that Jim is speaking of?

We have some quick 2-Minute Tuesday videos about these features:

  • Event Calendar
  • Event Chains

But I can also talk through the reasons behind these features, if you'd rather read than listen!

What are Event Chains and why are they used?

An Event Chain is a workflow that can contain SQL Server Agent Jobs and / or Windows Scheduled Tasks.

These Event Chains are used to automate the execution of jobs and / or tasks in a defined order where the next node in the chains execution is dependent upon either the completion, failure, or success of the previous node in the chain.

Now that we know what an event chain is and why they are used, let’s look at an example of when you might want to use this functionality.

Houston, we have a problem!

OUCH! After banging my head while jumping out of my bunk, and changing into a new pair of shorts, I set about figuring out why all the alarms on my ship are going off…

Last night I had a critical issue with a set of jobs responsible for calculating the oxygen being produced by my CO2 scrubber aboard my vessel. It appears that my system processes were being blocked while trying to write data to the database for my air handler. This caused my monitors to read less O2 than needed and sent off alarms all over the ship.

Luckily, this was a false alarm. After re-running the calculation, the O2 values were adjusted and everything returned to normal.

Why had there been blocking? How can I avoid this issue in the future?

Upon further investigation, it appears that 2 of my system jobs were locked on my O2 table at the same time, which caused my update job to run prior to the other critical jobs completing, and bedlam ensued.

Luckily, I know of a company who has designed some event chain functionality to keep my jobs from completing out of order again.

Setting up the Event Chain

To set up the Event Chain, we will need to follow these steps:

  1. Right click on the Event Chains folder in the Navigator pane and select New
  2. Provide a name and description for your Event Chain and check the box marked Chain Enabled
  3. Expand the Event Manager node and Tasks folder and / or SQL Server Agent and Jobs folders under the monitored servers in the Navigator pane to expose the tasks and / or jobs you want to include in the Event Chain
  4. Click and drag the jobs and / or tasks from the Navigator pane into the blank space to build the Event Chain
  5. Right click on the first node in the chain and select Workflow Completed, Success, or Failure
  6. Make sure you select the nodes in the From and To fields in the order that you want the workflow to proceed and click the OK button
  7. Continue this process until all your desired Event Chain nodes appear the way you want them
  8. Save the chain by clicking on the disk icon in the toolbar and the new chain will show up under the Event Chains folder in the Navigator pane

Please note: The event chain will kick off when the first node executes (either manually or when that node is scheduled to run). For a more thorough walk-through of setting up event chains, see Lori Edwards' post, Event Chains in the Event Calendar.

Remember: When you’re out in space, a lot can go wrong. Make sure you take SentryOne with you! An extra pair of shorts may come in handy also.

Want to take these features for a spin? Download a free trial today!

Thwack - Symbolize TM, R, and C