The SQL Server Job Management You Wish You Had
By: Jeremy Kadlec | Updated: 2019-05-28 | Comments (1) | Related: More > SQL Server Agent
My team needs to manage hundreds, if not thousands of scheduled processes that run against my environment with numerous versions and editions of SQL Server including Express. Many processes we support have been developed internally, others are from third parties and a final set are application related like SAP, PeopleSoft, JD Edwards, etc. We have a hard enough time wrapping our arms around all of the processes running, let alone understanding the dependencies, having the jobs run on specific days with numerous business calendars, getting timely notifications when a job fails with meaningful information to act upon, setting up workflows with insight into the state of the SQL Server when the processes were running and more. We need to get our shop in order and fast. Can you provide any suggestions?
SQL Server Agent is the native job management feature that runs on each instance as a separate Windows Service. With SQL Server Agent you have the ability to create Jobs, Job Steps, Schedules, Operators, Alerts, Proxies, Notifications (Success, Failure or Completion), etc. SQL Server Agent can be managed with SQL Server Management Studio, T-SQL or PowerShell scripts. SQL Server Agent can run T-SQL code, stored procedures, PowerShell, batch files, call SSIS Packages, run SSRS Reports, execute MDX queries, etc. With a little bit of programming, you can email the results, save the results to the file system and build a workflow with Integration Services. Further, with a little bit of know-how, you can call dependent jobs, setup retries, call jobs on other SQL Servers with Linked Servers, query the system tables in the MSDB database for history and more.
SQL Server Agent is very helpful when it comes to managing jobs related to a single SQL Server instance. Unfortunately, it can quickly become overwhelming managing hundreds, even thousands of jobs across a large SQL Server environment. With environments like this very common, numerous challenges are faced including:
- No global view of jobs across the enterprise
- No centralized job data for analysis and reporting
- No interface customization to meet my team's needs
- No native visualization to see dependent or overlapping jobs as well as future scheduling
- No custom calendars to prevent jobs from running based on business needs
- No historical record of job changes, by whom and when
- No support for SQL Server Express Edition
- No logical grouping of Jobs to assign permissions, setup notifications, alert on issues, etc.
- No native reporting options
- No native support for common core business applications
Beyond SQL Server Agent, Windows also includes the Task Scheduler which has much of the same functionality and also suffers many of the same shortcomings as SQL Server Agent as it pertains to enterprise job management. When managing a large enterprise how can you overcome these shortcomings and work efficiently to support the technology and business needs of the organization?
One option I would like to introduce is JAMS Enterprise Job Scheduling from MVP Systems Software, Inc. JAMS is a Windows based Job Management Solution built on .NET that addresses the needs outlined above and more including an option to deploy as in a fully redundant architecture. Let's dive into the solution to see how JAMS can address each of these needs.
The JAMS Dashboard
From a user interface perspective, JAMS is a single interface to build, manage and report on all (Dev, Test, QA, UAT, Production) of your jobs across the enterprise. The JAMS interface is built with flexibility in mind. It provides the ability to build a custom dashboard to access the data needed to best manage your environment. This dashboard consists of a number of predefined components that you can drag and drop on the interface providing key information for you Job Management needs at your fingertips. JAMS includes options for charts, graphs and gauges that can help visualize data such as job failures, concurrent processes, history and load on the job scheduler.
Figure 1 - JAMS Dashboard Customization
Now that you have seen a glimpse of JAMS, let's compare the native SQL Server and JAMS terminology as a basis for the remainder of this tip.
|Job Scheduling Engine||SQL Server Agent||Primary and/or Secondary Engine for High Availability|
|Multi-Step Process||Job||Sequence Job|
|Single Unit of Work in a Process||Job Step||Job|
|Date and Time of Process Execution||Schedule||Schedule|
|Point of Contact||Operator||Operator|
|Notification of Process Status||Alert||Alert|
|Logical Grouping of Processes||Not Applicable||Folder|
|Email Alerting||Job Level||Custom Notification + HTML Emails|
|Reporting||Not Applicable||Built-in Reports|
|Conditional Logic and Processing||SQL Server Agent Job + Integration Services||Workflow|
Job Monitoring for Current and Projected Jobs
Monitoring SQL Server Jobs is more than just knowing if a job succeeds, fails, or completes. It is important to understand how the job is performing, review in-depth logging, understand the dependencies and if a job will complete as needed based on the business Service Level Agreements (SLAs). With JAMS, all basic needs are covered to review your Jobs across the enterprise in a single interface with the ability to drill into the performance metrics, job dependencies, overlapping schedules and projected schedules. Let's see how it's done.
Based on the information gathered during each job execution in JAMS, you are able to easily understand performance metrics including Elapsed Time, CPU Time, IO and Page Faults. JAMS also provides summary statistics, such as Minimium, Average and Maximum that enables users to understand how the current execution of a job compares to all of its executions. This real time information gives you insight into the performance of a job to determine if there is an issue and if additional actions are needed.
Figure 2 - JAMS Monitoring Job Performance Metrics
Understanding what Jobs are currently running is critical. In SQL Server Management Studio, you have the ability to see if Jobs are running and when they will run next in the Job Activity Monitor. JAMS takes this to a whole new level by providing a Diagram View for Sequence Jobs. This wholistic view highlights details such as which Sequence Tasks are currently executing, which Tasks are waiting on upstream Dependencies, and which tasks will impact downstream Dependencies. This visualization can help you map out and fully understand the relationships between your jobs so you can make more efficient scheduling decisions.
Figure 3 - JAMS Sequence Diagram of a Current Sequence Job
The next JAMS monitoring feature to note is the Projected Schedule. This visualization outlines the upcoming jobs per day broken down by hour. Hovering over a job enables the you to see the job name, description, scheduled start date/time, projected start date/time, and projected end date/time. This information gives you an opportunity to proactively review your future schedule and resolve scheduling issues prior to impacting critical processes.
Figure 4 - JAMS Projected Schedule
For the most part, scheduling Jobs is simple. Right? Jobs just run at the same days and time. But what happens as your business grows and various departments have their own schedules? Maybe the Accounting department follows all the bank holidays, your manufacturing department has a different production calendar, maybe your company has a fiscal calendar, the company calendar has specific holidays, etc. Then there are mergers with the new company and you have a transition period with a whole new set of calendars. Then your company goes global. Now you can understand how simple scheduling can become a nightmare.
With various needs from numerous departments you can end up having to write complex code to determine if a job should run, or worse setting up reminders in Outlook to manually disable jobs or set the start date beyond date when a job will not run. JAMS understands these needs and gives you the ability to define multiple calendars for business holidays around the globe or in various departments. These custom calendars are easy to setup with a point and click interface as shown below, then you can access the calendars directly from your JAMS Job as shown in the next section.
Figure 5 - JAMS Custom Calendar Configuration
Custom SQL Server Job Scheduling
Based on the JAMS custom calendars we just discussed, you have the ability to define a schedule then setup an exceptions list as a comma separated list for a single Job. As your calendar changes, simply make the change as shown in the previous section and all of the corresponding Jobs are automatically updated.
Figure 6 - JAMS Custom Scheduling with Exceptions
Another common challenge with scheduling is time sensitive needs for beginning of the month, mid-month or end of month processing. What happens when these days occur on a weekend? Is that an issue? How do you handle months having 31, 30, 29 or 28 days? How can you complete tasks in advance of month end to deliver data on the first of the month? For example, you need 3 days prior to the end of the month for a process to run and your team to review the data. JAMS understands these needs and includes natural language scheduling where you can specify a parameter such as the end of the month and subtract three workdays. JAMS natural language scheduling simplifies the process, reduces errors and manual intervention.
Figure 7 - JAMS Natural Language
Fully Customizable Job Alerting
SQL Server Agent includes the ability to setup notifications (Success, Failure or Completion) at a Job Level as well as setup Alerts for errors generated by a portion of the SQL Server platform. The alerting in JAMS is focused on Jobs and is more than just a job succeeding, failing or completing. You have the ability to add intelligence based on the data collected for each job execution. This enables you to be alerted on a job completing too quickly, a runaway job, a file not being available, etc. All of these alerts are critical in eliminating common scenarios to focus on core business needs rather than being dependent on manually reviewing processes or being notified by your users.
In JAMS, Alerts are setup at a folder level, which is a logical grouping of Jobs. This is also where Alerts are assigned so that all jobs within the folder receive the alert, so you do not have to tediously setup alerts for each job.
Figure 8 - JAMS Alerts Setup at a Folder Level
The email based Alerts in JAMS are fully customizable markdown based emails. Each Alert is based on a template that you can modify with parameters, file attachments, etc. Here is a sample email template with the JAMS parameters available for customization.
Figure 9 - JAMS Alert Email Template
As a point of reference, check out this sample email based on the previous template.
Figure 10 - JAMS Sample Email
As a final note, JAMS also provides integration with an IVR system to deliver Alerts as interactive phone messages directly to your mobile phone. You have the ability to setup predefined actions (Retry, Stop, Cancel, Run Another Job, etc.) for each Job in order to support the business. Based on the status of the Job reported when you are called, you can take action with minimal interruption.
Job Management Workflow
There are a few options available when it comes to native workflow management options in SQL Server. First, SQL Server Agent has Job Steps with "on success" and "on failure" actions, with the ability to call particular Job Steps and/or end the Job as successful or failed. To take it a step further, SQL Server Integration Services has numerous work flow and data flow options with conditional logic and can be scheduled as a SQL Server Agent Job. The JAMS Workflow engine includes many of these common features in addition to FTP, file watch (validate existence, file size, etc.), built-in retries, conditional logic, Zip/Unzip files, JAMS variables, access to numerous platforms (JD Edwards, PeopleSoft, SAP, etc.) natively and a complete API.
Figure 11 - JAMS Workflow Designer
In terms of extensibility, below is an example of the JAMS variables to give you a sense of the scope natively available. JAMS recognizes the need for customization and provides the ability to write Visual Basic (VB) expressions to evaluate the variables.
Figure 12 - JAMS Workflow Parameters
There are 100+ native features in the JAMS Workflow Toolbox. Below is an example of the File Trigger interface that validates that a file exists and is a greater than a specific size. There are also numerous FTP options in JAMS to reduce the frustration of simply moving files.
Figure 13 - JAMS Workflow Toolbox Samples
Figure 14 - JAMS FTP
How do I get started with JAMS?
- Register for a Demo.
- Download JAMS to see how it can help you.
- Think about all of the challenges you face with Job Scheduling, Reporting, Notification, Workflows, etc. and how JAMS can solve these problems.
- Communicate with your team and management about how you think JAMS will
- Global view of jobs across the enterprise with centralized data for insight, decision making and historical needs
- Numerous customizable interfaces including: monitoring, troubleshooting, understanding dependencies, overlapping jobs, future scheduling and more
- Custom calendars and natural language scheduling to simplify scheduling
- Simple management of Jobs to assign permissions, setup notifications and alert on issues
- Supports all versions and editions of SQL Server in addition to application adapters for numerous core business applications
- Put JAMS through its paces in your environment, share the results with your team and determine your next steps.
- We have just scratched the surface of the functionality available with JAMS. If you are facing job management issues in your environment, consider JAMS as a solution to address your needs. Getting started is easy.
- Sound interesting? Check out these resources:
MSSQLTips.com Product Editorial sponsored by HelpSystems, makers of JAMS.
Last Updated: 2019-05-28
About the author
View all my tips