SQL Sentry Event Chains

Introduction

An event chain is a series of dependencies between events running on the same server or different servers. There is no limit to the length of a chain or the number of events that can be defined for a chain. To navigate to event chains, select the Event Chains node from the Navigator pane.

Event Chain Examples

Example A

When Job1 (SQL Server job) completes, execute Job2 (SQL Server job). If Job2 succeeds, execute Task A (Windows Task Scheduler Task). If Job2 fails, execute Job3.

To accommodate a path that encompasses the same event more than once, and to avoid a circular reference, the concept of nodes are used. A node is simply a unique representation of an event. Even though Job1 is listed twice, the chain can treat the event as two separate instances and apply varying conditions to each instance that provides a greater degree of flexibility.

Circular References

An event chain is a logical workflow through a series of one or more event objects, with each occurrence of an event object represented by a unique event node. A circular reference occurs if the workflow was built to reference an event node a second time. Such a workflow is not allowed in an event chain.

For example, when Event A completes, execute Event C. When Event C completes, execute Event A.

To allow an event to occur a second time in an event chain, add the object Event A a second time to the object list. Change the To Node in the second link to reference the newly created node. This method can be repeated to allow the event to occur a discrete number of times.  

Adding an Event Chain

1. Double-click the Event Chains node in the Navigator pane, or select New in the context menu. 
2. Assign the event chain a name and optional description.
3. Right-click to add objects that are used in the event chain, or drag-and-drop jobs from the Navigator pane onto the Event Chain diagram. Node names are given to each object.
   
4. Add links to the chain by right-clicking the appropriate node, and then select Workflow. 
5. Select the status condition for execution and the Target node. Nodes can be dragged and dropped to reposition for easier viewing of the chain. The Auto Layout toolbar button may also be used once links are established.
6. Select Save in the File menu or select the Save iconto save the chain.

Editing an Event Chain

1. Select the Event Chain node from the tree view in the Navigator pane, and then select Edit.
2. Select the necessary properties of the event chain.
3. Select Save in the File menu to save the chain.

Remapping Nodes in an Event Chain

Event Chain nodes can be remapped to other event objects. This can save you time over deleting and recreating nodes in your event chain. When you remap a node in an event chain, the next step(s) in the workflow remain intact. To remap a node, select Remap Node available through the node context menu.

Chain History

To view a historical run of a chain, select the runtime from the Select Chain Instance box. Runtime and status information is displayed for each node. If the object is completed successfully, it has a green background. If it fails, it has a red background. This is also shown for currently executing chains. Currently executing nodes have an orange background.

Context Menus

Navigator Pane

View Mode

Workspace

Node - <Current> Chain Instance

Node - Historical Chain Instance

Edit Mode

Workspace

Node

Event Chain Notifications

General Actions

When more than one workflow path exists in a chain, the Chain Success and Chain Failure conditions become less valuable since they are based exclusively on whether or not all nodes completed. At that point the Node Success and Node Failure conditions should be used for more accurate status information about the respective nodes and/or paths.

Settings

Event chains and Event Chain nodes support Runtime Threshold settings. These settings are specified as absolute runtimes for both the minimum and maximum runtime thresholds.

Audit Actions

Event View

All event chains contain an Event View sub-node. Double-clicking or selecting Open from the context menu produces a calendar of all the objects that are a part of the selected chain. As with any Custom Event View, you have the ability to add Performance Counters to any Event Chain View.

Event Chain Calendar Info

All event source objects that appear on a calendar because of an event chain display a small chain glyph in the top left corner.

Additionally, the event source object tooltip displays information about any chain(s) that this object is associated with. An * is placed next to the actual chain that created this instance.

Reporting Services Jobs in Event Chains

It is a known limitation that Reporting Services jobs don't work within chains in versions prior to 2.7. This limitation occurs because Reporting Services jobs are filtered out of SQL Sentry as they have no duration and don't reflect the accurate information about the report.

A workaround is available in version 2.7 and later. Normally, SQL Sentry filters out a Reporting Services job. You can force them to stay in the history and alert chaining that they have completed by placing an additional space after the AddEvent call.

For example, if you currently have:

AddEvent @EventType='Test', @EventData= '5B0537EA-7A87-4C8B-B002-03A51DD5100C'

You could change it to:

AddEvent  @EventType='Test', @EventData= '5B0537EA-7A87-4C8B-B002-03A51DD5100C'

After the change is made, reset the chain.

Copy Chain

You may run into a situation where you want to create a second chain that is similar to an existing chain, or you want to use an existing chain as a template for a new chain. You can effectively copy a chain using the Save Chain As option on the File menu.

You will then be asked to enter a new name and optional description for the chain. 

Organizing Event Chains

Event chains can be organized using custom folder names. Any event chain can be moved into another folder by dragging and dropping it into another folder.