By: Jeremy Kadlec | Last Updated: 2019-05-21 | Comments (6) | SQL Server Agent
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?
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, HelpSystems, the company that makes JAMS, has helped more than 15,000 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 Sequence 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 Sequence Job Step (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 Sequence Definition Source Editor
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 Layers 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 case studies for:
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 launch 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 toolbox 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 Monitor View and Workflow Editor
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 Monitor view. Being able to visualize jobs and current status is valuable. In this interface you have the ability to drill into multi-step Workflow and Sequence Jobs as well as right-click on a job to view log details, 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 Monitor View 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
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 2012 to 2017 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, zOS/Mainframe, IBM Power Systems, OpenVMS and more
- Databases - SQL Server, Oracle, etc.
- Applications - SAP, JD Edwards, Microsoft Dynamics, SAS, Ellucian Banner, PeopleSoft and more
- Code - T-SQL, SSIS, PowerShell, Python, Perl, VB Script, Shell, etc.
- Schedulers - Windows Task Scheduler, CRON, SQL Server Agent, etc.
- If you are at a point where you need to manage business processes across the enterprise, consider JAMS Job Scheduler to see if this solution can solve your business issues.
- Interested in learning more about SQL Server Agent and JAMS? Check out this web cast - Making the most out of SQL Server Agent.
- If JAMS Job Scheduler sounds interesting, check out the following resources:
MSSQLTips.com Product Editorial sponsored by HelpSystems makers of JAMS Job Scheduler.
Last Updated: 2019-05-21
About the author
View all my tips