This post uses screenshots from SQL Sentry for SQL Server. If you would like to visualize your SQL Server environment events download your free 15 day trial here.
In a prior post, I wrote about Event Calendar and how it helps our customers to visualize all of the events that are occurring within your environment in an Outlook-style calendar view. I also alluded to a way of dynamically scheduling jobs that need to run in a specific order. We can do that in the Event Calendar using functionality called Event Chains.
Event Chains in the Event Calendar for SQL Server allow you to create a workflow containing SQL Agent jobs or Windows Scheduled Tasks from any of the servers that you are currently monitoring. Much like SSIS packages, when you create the workflow, you can continue to subsequent steps conditionally, based on the Success, Failure, or Completion of the previous step. In addition to the ability to add jobs from multiple servers, you also only need to schedule the first node of the workflow. After that, Event Calendar takes over and continues through the workflow. Because of that, it doesn’t matter whether nodes take 5 minutes or an hour, the next step won’t start until the previous one has completed. (A very simplified example a co-worker described to me: a backup job on one server which kicked off a restore job on a standby server once the backup job was done.)
Below is an example of a workflow. It contains jobs from four different servers, and you can see the different workflows: green for continue on success, red for continue on failure, and blue for continue on completion. You can also see that a node can start one or more nodes:
Event Chain
Figure 1 : Create New Event Chain
Figure 2 : Select Objects
Figure 3 : Create Workflow
Figure 4 : Link Properties
Figure 5 : Create New Job
Figure 6 : Enable Chain
Creating an Event Chain is very simple; we’ll walk through it here. First, find Event Chains on the Navigator pane (left side of the application), right-click, and choose New (see Figure 1, at right). You’ll want to give your Event Chain a name and, optionally, a description.
The next step is creating nodes. These will be the SQL Agent Jobs or Windows Scheduled Tasks that will make up the workflow. Right-click on the design window and choose Add Nodes. This will bring up the Select Objects dialog box (see Figure 2, at right). There are 4 ways to search for nodes:
- Event Object Name: Enter the job or task name
- Parent Object Name: Enter the server name
- Step Text: Enter any text that exists in any of the steps of the job. For example, if I were looking for the job that ran the integrity check, but couldn’t remember what I called it, I could enter DBCC in the Step Text field
- Owner/Creator: Enter the job or task owner/creator
In any of the search fields, you can use partial words or wildcard characters. With the exception of Parent Object Name, any servers that you are currently monitoring will be searched. If you wanted to find all of the backup jobs, you could enter %backup% in Step Text and find all of the jobs with backup somewhere in the step command text across all of your servers. Once you’ve entered your search terms, click the Search button. For each of the items that will be a part of the workflow, select it in the search results window and choose Add. If you have a workflow that could potentially call the same job in different parts of the chain, select that job twice – the name of the second instance will be appended with _01.
Once you’ve added the nodes, you’ll want to create the workflows. Right-click in any of the nodes, choose Workflow, and then whether the continuation should be on Completion, Success, or Failure (see Figure 3, at right). In this step, you’ll also choose the direction of the workflow. Both boxes in the Link Properties dialog box have a dropdown that will list all of the nodes that you’ve added (see Figure 4, at right).
Your workflow is almost completed, but what happens if you realize that a job you thought existed doesn’t? Well, SQL Sentry can help you there as well. Navigate to the server where the job should exist, expand the instance and the SQL Agent, and right-click on Jobs. You’ll be given the option to create a new job (see Figure 5, at right); this will open the New Job dialog box within SSMS.
Assuming that all of the nodes are present and the workflows are created, the only step left is to check the Enable Chain box (see Figure 6, at right). You’ll also want to ensure that there is a schedule for the first node in the workflow, as that is what starts the Event Chain.
In my previous post, I showed how you could manually reschedule future jobs in the Event Calendar when there is a possible conflict. In the example there, I had backup jobs that had started to take longer and were running concurrently. With an Event Chain, I could make each backup job a node – in that situation, the next backup would not start until the previous one had ended, regardless of how long it took.
There are plenty of use cases for Event Chains, such as ETL operations, replacing maintenance plans, or coordinating tasks across your enterprise. If you’re a customer, take a look and see if it’s something that you could use; if you’re not a customer I invite you to explore a custom quote or experience a live demo. Take it for a spin!
