Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

Centralized SQL Server Job Management with JAMS Job Scheduler


By:   |   Read Comments (6)   |   Related Tips: More > SQL Server Agent

Problem

Our company's entire IT infrastructure is growing rapidly, but our SQL Server environment is growing exponentially faster. My team needs to make sure we have consistency across our SQL Server instances. One of our needs is to execute the same code across multiple servers for certain core business processes and have SQL Server processes such as backups, maintenance and daily health checks, run consistently across every instance. Unfortunately, source code for our core business processes gets out of sync all the time. Someone will make a change to one server, but not all of them. Re-syncing the code is time-consuming and can be error-prone.

We created a code deployment process to minimize the issues, but, depending on the type of change, it can be a bit cumbersome. In all honesty, we are not very process-driven, so I have been looking into technologies that can help our team. We have seen the SQL Server Agent multi-server administration option in SQL Server Management Studio, but we are not too familiar with its functionality. I am also considering SQL Server Linked Servers and things such as SSIS and Replication, but I am not sure if that makes sense for the code we run. Each one of these technologies looks promising in some respects, but challenging in others. I think it would make more sense to manage these SQL Server Jobs centrally than it would to manage them separately on each SQL Server instance.

To top it off, my team may be inheriting database platforms that run on different operating systems. This could change everything for my team in terms of our responsibilities and expected knowledge base. At this point, job management is chaotic. I need to make this simple and manageable before our responsibilities grow.

Ultimately, I need a tool that I can plug into our environment to manage our jobs and without missing a beat. Do you have any suggestions?

Solution

The technologies you mentioned could be reasonable options for your SQL Server environment. Keep in mind there may be a learning curve with some of them depending on your experience level. If you are looking for a tool to take away your centralized job management pain, consider JAMS Job Scheduler. JAMS provides a centralized job management and workflow solution with the features you need. Over the last 25 years, MVP Systems Software, the company that makes JAMS, has helped more than 800 organizations around the globe. Let's dig into what JAMS has to offer.

Execute the Same Job on Multiple SQL Servers

Based on the problem you are facing, I think this section is really where you are going to see JAMS shine. In this example, we are able to set up a single JAMS Setup Definition (i.e. Figure 1 - Top image) to have a single code base i.e. SQL Server Maintenance code and then be able to customize the server, security and schedule within each Setup step job (i.e. Figure 1 - bottom image). This simple paradigm within JAMS enables you to have a single set of source code and separately define the properties across your enterprise.

Figure 1 - JAMS Setup Definition and Job

For more information about how to use JAMS, check out these training videos.

JAMS Core Features to Support SQL Server

For large SQL Server environments, JAMS delivers a unique feature set:

  • Enterprise Job Scheduling
  • Fault Tolerant Architecture
  • Centralized Job Management
  • Additional Layer of Security
  • Support for T-SQL, SSIS, PowerShell and more
  • Alerting and Reporting
  • Complex Job Scheduling Options
  • Detailed Performance Metrics
  • Pass Input and Output Variables between Jobs
  • and more...

For examples of leveraging JAMS to automate SQL, check out these resources: Parameter Powered Batch Automation and all case studies.

Exploring the JAMS Application

JAMS has a feature rich GUI to help manage SQL Server Jobs across the enterprise. Below are examples of three productivity enhancing interfaces. First is the main JAMS interface which is the launching pad for managing, monitoring and configuring the product. Second, is a detailed monitor view, listing each job, its current status, user name and more than 100 other details. You can quickly view the status of your jobs across the enterprise. Third, is a nifty interface to build a business workflow using JAMS. It includes conditional logic, notifications, and other pre-defined processes in a tool box on the left of the screen.

This is just a quick glimpse into a few of the JAMS interfaces.  Let's dig deeper to see how we can help address your centralized job management needs.

 

Figure 2 - JAMS Main Interface, Monitor View and Flowchart

JAMS Job Monitoring

Beyond the detailed listing of your JAMS jobs as shown above in Figure 2, you can also visualize the status of your jobs in a Gantt Chart view. Being able to visualize jobs, their dependencies and status is valuable. In this interface you have the ability to zoom in and out as well as right-click on a job to cancel, restart or re-schedule it. With that type of functionality, it takes this interface from an attractive report to a productivity booster.

Figure 3 - JAMS Gantt Chart of Current Jobs

JAMS records a number of key metrics, well beyond if the job completed successfully or not, for each job execution. These metrics include the CPU time, reads, writes, memory usage and more. This can give you a great deal of insight into a particular job that has a performance issue or a performance difference resulting from a code change.

Figure 4 - JAMS Monitor Detail 

JAMS Security

Another feature of JAMS that could help your team is its security paradigm, which is independent from the source system where the jobs are running. JAMS implements security for users at a folder level within its interface. As you can see from the screen shot below (Figure 5) JAMS includes a high level of granularity to set up, manage and report on jobs. These types of permissions can be invaluable to your team as you grow and bring new team members into your organization.

Figure 5 - JAMS Security

Technologies Supported by JAMS

Now, when you take your job management a step further and need to manage SQL Server 2005 to 2012 as well as other platforms, JAMS can really provide value. Since JAMS is a single interface to numerous database platforms and operating systems, the learning curve should be minimal. You do not need to be an expert on every source system to schedule jobs, but rather proficient enough with JAMS to let it handle the details for you. Here are a number of the platforms JAMS supports:

  • Operating Systems - Windows, UNIX, Linux, VMWare, IBM Power Systems, OpenVMS and more
  • Databases - SQL Server, Oracle, etc.
  • Applications - SAP, JD Edwards, Microsoft Dynamics, SAS, PeopleSoft and more
  • Code - T-SQL, SSIS, PowerShell, Python, Perl, VB Script, etc.
  • Schedulers - Windows Task Scheduler, CRON, SQL Server Agent, etc.
Next Steps

MSSQLTips.com Product Editorial sponsored by MVP Systems Software makers of JAMS Job Scheduler.



Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, June 07, 2017 - 12:50:27 PM - jeff_yao Back To Top

 @Jeremy, wow, I did not know JAMS has such nice API feature. It is better than my current scheduling system.

Thanks for the info.


Wednesday, June 07, 2017 - 12:11:06 PM - Jeremy Kadlec Back To Top

Jeffrey,

There is an API with JAMS and you do have the ability to run PowerShell as Jobs.

 

Here are a few URLs to check out:

http://www.jamsscheduler.com/jams-features/windows-net/

http://www.jamsscheduler.com/restapi/

http://www.jamsscheduler.com/jams-features/windows-powershell/

 

HTH.

 

Thank you,
Jeremy Kadlec
Community Co-Leader


Tuesday, June 06, 2017 - 3:27:21 PM - jeff_yao Back To Top

This is obviously a good tool, and we are using a similar system. But all these 3rd party tools need to consider going one step further to make itself excellent. this step is called "programability", ie. in addition to configuring/managing jobs with GUI, there should be another way to configure / manage jobs with APIs, so that if I want to deploy multiple jobs, I can write a script (say, PowerShell script) to deploy them automatically.

Unluckily, this "programability" feature is absent in 99% of these 3rd party tools. All the product managers/architects, please consider "programability" as your product's distinguisher.


Thursday, March 12, 2015 - 12:42:24 PM - Ashwani Nanda Back To Top

We use a similar tool called Redwood Cronacle and found that it is very helpful if you have a separate Ops monitoring the jobs. I believe any enterprise scheduler would do that but to emphasize that this is a good way to take load off dev team who generally have to support the failures but not necessarily have resources for monitoring.


Friday, February 21, 2014 - 10:28:44 AM - Jeremy Kadlec Back To Top

Mark,

Thank you for the feedback.  We did note the following after the Next Steps section:

"MSSQLTips.com Product Editorial sponsored by MVP Systems Software makers of JAMS Job Scheduler."

This piece was authored by me, not MVP Systems Software based on my review of the product.

Thank you,
Jeremy Kadlec
MSSQLTips.com Community Co-Leader


Friday, February 21, 2014 - 9:14:18 AM - Mark Hions Back To Top

"Let's dig deeper to see how we can help address your centralized job management needs." [Emphasis mine]

The text for this column seems to have been provided by MVP Systems.  This is less a SQL Tip and more an advert for a third-party commerical product.  The author should reveal whether this was a paid review.


Learn more about SQL Server tools