How UVA Health Cured SQL Agent Ailments with JAMS Enterprise Job Scheduler

Good afternoon everyone and thank you for joining us for today’s webinar, How UVA Health Cured SQL Agent Ailments with JAMS Enterprise Job Scheduling. My name is Cody Gondyke, I’m from Fortra and we make JAMS Job Scheduler, the enterprise job scheduling solution that centralizes and automates your critical batch processes.

Today, I’m going to give you a quick summary of who we are and walk you through a start to finish overview of University of Virginia Health System’s automation journey. Then one of our SQL automation experts, Rob Newman, is going to take you through some really neat stuff including some extra use cases, the features and functionality you’ll have access to with workload automation, and take a look under the hood to see how you can automate SQL in your own environment. If you have any questions, please go ahead and post those in the chat window that’s accessible through the panel over on the right-hand side of your screen. We will be monitoring those as we go. And if we don’t get your question right away, don’t worry we will take some extra time at the end of our webinar to answer as many of those questions as we can. Let’s get started.

So, automating SQL is our focus today, but it’s always good to know that here at Fortra we really can solve any and every security and automation you have. I like to say if you have an IT need, we have an IT solution.

No matter where you are. You probably have a Fortra office close to home, as you can see here, and we do have dedicated JAMS offices in the United States, the United Kingdom and Australia, which means you can get 24/7 critical support for our JAMS workload automation software. Our team has deployed JAMS to hundreds of customers worldwide, and you can see a small sample of our customers here. I like to say it’s a general rule that you’re going to interact with a JAMS customer today, whether that’s watching a show on a Vizio TV, maybe building a bird feeder with Milwaukee power tools, checking the forecast with AccuWeather, or going for a run in Nike sneakers.

We also have an incredibly strong partner network, as you can see here. We’re a Microsoft technology partner and we have strategic partners like Oracle, IBM, and Cisco and we belong to IT organizations like PCI for security. Lastly, everything that we do at Fortra is for your success. That’s from incorporating your needs directly into our development cycle to offering professionals services, and support services that jumpstart your workload automation. But, that’s enough about us. Let’s talk about UVA Health.

So, the University of Virginia Health system is an academic healthcare organization associated with the University of Virginia. The organization includes a top ranking hospital, a level one trauma center, nationally recognized cancer and heart centers and primary and specialty clinics throughout central Virginia. So, UVA Health provides inpatient and outpatient care, patient education, and conducts medical research and education.

So, for years UVA Health had been running your traditional once-a-day data warehouse process, but that process couldn’t keep up with the needs of the organization. Users increasingly required reports for a given point in time during the day. And that meant new automation standards for the underlying batch processes and automated workloads that were making those reports possible. I think everyone here already knows that SQL Agent is a decent scheduling tool, it can handle some simple cases pretty well, but it’s not equipped for enterprise needs.

One of the enterprise features missing from SQL Agent and one of the single most important enterprise scheduling features, in general, would be dependencies. So, dependencies make sure that your job streams run in order, and make sure that they do it properly. Without dependencies we, most often, see pillow scheduling where DBAs and operators think that the first job is going to take 25 minutes, so they schedule the second job 30 minutes out, so that it finishes after the first job. And, unfortunately, that leaves a great deal of room for problems to occur and it doesn’t resolve all possible issues.

So at UVA Health, the lack of dependencies would derail their critical workloads. Scheduling was eyeballed – that pillow scheduling we just talked about – which meant that if a job failed, or if something else wasn’t ready at the right time, jobs would still fire off everywhere else. It created what Scott Carter, the systems analyst and DBA for UVA Health, called ‘train wrecks’. Since everything was decentralized with applications running on multiple platforms, dealing with those train wrecks would often involve multiple IT staff members from other areas, which meant pulling them off of higher value projects.

So, UVA Health did try some work arounds to mitigate those errors. They tried third-party file watchers and SSIS packages, but those tools were difficult to configure even for basic schedules and they proved too brittle to be useful. So, we have a lack of dependencies causing failures to pile up, and because the jobs failing might relate to patient admission or treatment, everything needs to get resolved as quickly as possible. So, we come to the issue with SQL Agent’s architecture.

Technicians would have to manually access SQL server boxes to get basic information. If a failure happened on a weekend, the techs would have to log into the VPN, log into a SQL server box that had SQL Agent on it, review the error log and then hope that they got a message that was useful. Visibility wasn’t only a problem with SQL boxes either, UVA Health’s data science team relies heavily on cross-platform workloads that touch both Unix and SQL machines. So, if a job failure involves a Unix box, a Unix administrator had to be pulled in. Even more, the team would also deal with bottlenecks where jobs were submitted by individual employees outside of a central schedule. Since they were effectively invisible to the rest of the organization, they could chew up valuable resources at peak times, or stay unexecuted for long periods of time if somebody went on vacation or was out sick. So, Scott Carter’s team tracked these jobs manually in Visio swim lanes, and that wasn’t a solution. It was just an extra set of tasks chewing up time in the day.

All right, so that’s, a lot of challenges that Scott and his team were facing. And to meet those, they looked in and tried out a number of schedulers, but JAMS was their perfect fit. I think Scott summed up the search well when he said that the functionality in schedulers was virtually the same for what they needed, but JAMS was half the cost. And, to be honest, it’s not the first time we’ve heard that. I really take pride in the fact we don’t overinflate our price like some other software in our space because it means we can help organizations like UVA.

So, once JAMS was in, everything moved quickly. Scott and his team centralized everything with JAMS, and took advantage of the native SQL converter that ships with JAMS to bring in about 30 processes very quickly. Scott called the converter extremely easy. The next piece was automating things that had been manual. The best example is probably the stopping and restarting of services in Unix Business Objects. So with a JAMS dependency, there’s a data warehouse process that’s waiting, and then a Unix process that knows when to kick off because of that dependency. Jobs downstream from the Unix process, take the hand off and load data back into the warehouse. And no manual intervention ever needs to happen.

The next piece was around file transfers. So Scott and his team replaced a number of third-party tools and SSIS scripts with the robust native SFTP and FTPS file transfer functionality in JAMS. And I know they’re also taking advantage of automated zipping and unzipping, and deleting source files after transfer, which are all just check boxes and JAMS. So with any system jobs still fail time to time. UVA is no exception – whether it be a network blip or maybe a database gets corrupted. And that’s where the notifications in JAMS came in for Scott and his team. They set up a comprehensive set of notifications for critical events within the master schedule. Those notifications go out to the admin group responsible for troubleshooting and they also go out to business users so they’re already aware of potential delays before they’re actually impacted. And, of course, having everything centralized and running through JAMS also meant that Scott got to say goodbye to that Visio diagram.

Now, with JAMS running for a while, UVA Health has scaled to hundreds of jobs and workflows every day. And, of course, every one of those jobs and workflows is centralized through JAMS, so a single monitor shows everything that’s running. Scott and his team also know that things are really well locked down. They’ve got 40 plus people on their team, so Scott is leveraging JAMS’s granular security options to make sure that administrators can create and modify jobs, and business users can see what’s running and get notified, but business users can’t edit the job in themselves. It’s all really logically locked down. They’ve also used native reporting features to show growth to management and they’ve even used those figures to justify some growth initiatives and changes. And, finally, they’re using historical reporting to identify trends in their own processes.

Now, I’ll turn things over to JAMS support engineer and SQL automation expert, Rob Newman. Rob has more than 10 years of experience supporting SQL server. He’s presented a number of SQL focused webinars including some with MSSQL tips, and he’s a coauthor of our JAMS University curriculum. He’s going to give us all a peek under the hood at the type of process UVA would have gone through to automate their jobs through JAMS. Rob, take it away.

Thank you Cody. Now, as Cody stated, SQL Agent does do an okay job of running steps sequentially. It even has some basic on success and on failure actions. Yes, it can send emails, and it can even run some of your external processes like PowerShell and DOS, but it can’t run your Unix jobs nor external applications like SAP, or Business Objects jobs. And, of course, those jobs – when run within SQL – are very hard to monitor and track. And if you consider the SQL cycle cost per core model, it’s quite expensive. You really want to only use those SQL cycles for your SQL database jobs. SQL should not be running your PowerShell scripts or even sending email job notifications. In fact, SQL mail actually opens your SQL server to additional hacking opportunities.

So how do we eliminate those invisible jobs that are run by SQL Agent? Well, by incorporating them into JAMS, so that they’re no longer invisible. Natively, JAMS can connect to SQL, Unix and run any of your cross-platform jobs. Logging history and audit information retrieval in JAMS does not require access to SQL. And the UVA Health science team no longer needs to log in and access SQL to view any of that information. The JAMS Active Directory RBAC security model makes it very easy to segregate access by inquirer, submitter, developer and admin roles. Try doing that in SQL.

Now, as with any projects, the first thing that should be done is create an outline. This step is critical and it’s as easy as answering a few questions. What does my process currently look like? Where should each step in the process be executing? What accounts should each step execute as? And when should the process run? This preparation eliminates all your guesswork since you are essentially creating a checklist. This outline or checklist will identify patterns, dependencies, security, and even those invisible jobs.

So, JAMS will allow you to recycle your efforts and reuse jobs or steps and even notifications. It allows you to incorporate all your cross-platform jobs or processes into a single source. And it assures that all your processes are run securely without providing elevated, or unnecessary user access to those remote systems, or applications. And you can set schedules based on both dependencies and time requirements. This will remove the guesswork and pillow scheduling out of your workflow process.

So, now that we have the overall process defined, let’s take a look at that SQL Agent job that we converted into a JAMS job. On the left is the SQL Agent job and its steps, and the right shows the JAMS sequence created by the converter. And, as you can see, JAMS has created jobs for each of the SQL steps. Converting SQL Agent jobs is quite easy. Using the SQL Agent job converter tool, you query for your SQL jobs, right-click on the job, and choose convert. By the way, you can also do this for all your Windows Task Scheduler tasks as well.

Now, JAMS actually creates a job for each of the steps in the SQL Agent job. It then adds all of those jobs to a JAMS sequence to be run in a sequential dependent order. Each job or step in the sequence, by default, is dependent upon the previous job or step completing successfully. The sequence will even halt on an error, if desired, and notify you. This’ll allow you to fix the cause of the error and resume processing from the point or jump step that erred out. And failure notifications will be sent automatically for any job or step that fails.

You can use JAMS’ native functionality to easily replace steps or processes that you would have to otherwise script into SQL. File watches or dependencies are created using a form with drop downs, and fill-in-the blank fields. Not only can you check to see if a file is present, but you can also make sure it is available and not locked. In fact, you can even check for the absence of a file, so why not let JAMS, not SQL, do your file hunting for you?

Notifications are sent by default on all errors and you can easily add any number of additional notifications desired. These notifications will all include the log file. This allows you to easily identify where problems exist, where the error occurred without having to log into multiple systems, or your remote servers. And, as you can already guess, these are huge time savers.

So, some additional scheduling dependencies or triggers that you can set up include things like running this job based on a schedule, a file, a variable, or even an email in a particular inbox with a particular subject line. The scheduling options in JAMS are much more robust than what is available in SQL. We can schedule jobs with terms like every other Tuesday, or how about the third Friday of the month, or even just work days? And if you don’t want it to run on a holiday, then we can easily add that exception as well. You can also set up dependencies to include other jobs or even time windows. Yes, you can prevent your SQL update jobs from executing while your database backup jobs are running.

So, how do I add those cross-platform jobs and specifically that Unix Business Objects process that was previously mentioned? Well, since it was not part of the SQL Agent job, as it could not be run from SQL, we can easily add any cross-platform job to a JAMS sequence. You simply use the submit job activity, select the job, and it’s very easy to inject jobs or steps into your business process. The sequential dependency is set by default, however, let’s say you want a certain set of jobs to run at the same time you can also run jobs in a parallel within the sequence. So, JAMS is now handling your file and job dependencies, the job schedule, and the notifications. As well as running any cross-platform jobs that SQL can’t handle. This leaves SQL doing what SQL does best – SQL.

JAMS automatically captures the standard out and standard error streams. This will allow you to easily identify things like the SAP web service that failed, or that the jobs SQL connection timed out. The JAMS notifications will, by default, send the log file right to your email, so you will not need to VPN to try and log into SQL only to find out that, “Oh yeah, I can’t but I already knew that because the JAMS job log file already told me that.” You also shouldn’t have to read a log file, log in, and manually rerun the job because your SAP web services failed, or was temporarily down. With JAMS retries you can set your jobs to automatically retry. How often and how long to wait between retries is entirely up to you. Three times with a one minute wait in between. Or how about two times waiting five minutes in between retries?

JAMS can also automatically run recovery jobs upon failures. These could restart services, check to see if the services are down, if they are then restart them, or check system availability and then they can actually rerun the job automatically for you.

JAMS security, it’s a lot like an onion, and can best be described by a series of layers. The top layer, server, allows authentication to JAMS from all JAMS clients. Second layer, access control, is a list of access control entries providing authorization to the various areas within JAMS like folders, jobs, agents, calendars, queues, et cetera. Additional layers consist of the root folder, all the sub folders, and finally the objects themselves like your job definition, credential definition, or agent definition. And folder and job access control entries can be inherited from the folders above, which makes it real easy to set your security at the folder level and have all of your sub folders and jobs inherit those properties and that security.

Now, authentication and authorization is integrated with Active Directory users and groups or any all that provider. This RBAC, or role based access control, utilizes four roles as a starting point: admins, inquirers, submitters, and developers. Now, you’re not restricted to use only those roles. You’re free to add and create your own. And this allows you to get as granular with the security as you’d like.

Now, if you want to look at it from an audit perspective, as users are configuring their jobs JAMS will track and record all changes to JAMS objects. Not only does this provide a built-in version control system for your source code, it keeps your auditors happy, as they can see all end user activity, they can see the changes being made, when they’re being made and who is making those changes. This also makes your change managers happy, as any job changes can easily be identified and rolled back if needed.

So, to recap, JAMS allows you to quickly convert your SQL Agent jobs into JAMS jobs. Functionality that needed to be scripted in SQL can be converted to native JAMS functionality. Those invisible jobs within SQL are now visible and trackable within JAMS. You can easily add all your cross-platform jobs to JAMS sequences and workflows. And you can link your jobs together to create optimized workflows. No more static scheduling, have JAMS dynamically trigger your jobs for you.

Tracking, monitoring and troubleshooting all starts within JAMS. All job logs can be retrieved directly from JAMS. This alleviates the need to give access to those remote systems and applications. And automatic retries and recovery jobs all help alleviate failures. The JAMS Active Directory RBAC security model makes it easy to secure JAMS. Roles dictate who can create edit, submit, and monitor. Inheritance allows you to quickly set security through all your folders and sub folders. And object level ACLs allows you to override security down to the individual job or even the credential level. So, SQL is now doing what it does best, SQL. And JAMS is doing what it does best, everything else. Back to you, Cody.

Fantastic. Thanks for all that so far Rob, really informative stuff. We’ll go to the questions here now. And I’ll just remind everyone that the question section is over on the right-hand side of your screen. Feel free to submit those as we go. And, I apologize in advance for paraphrasing any of the questions that we’ve gotten in so far.

So the first one that I have here for your Rob, can JAMS run SQL stored procedures?

Absolutely. In fact, JAMS has an execution method that’s specifically for running SQL stored procedures. It is one of our form-based execution methods where you choose the SQL connection you want to connect to, you choose the set of credentials that you want to use, and it’ll provide you a dropdown list of all of the stored procedures on that SQL box that you want to run. You can then take this job that you just created and incorporate it in any one of your sequence or workflows to your business workflows and processes.

All right. The next question that I have for you here, can I use JAMS for Azure SQL automation?

Sure. JAMS doesn’t really care where the database lives. It could be on-premise, it could be in the cloud, whether that cloud be a virtual VM environment that you have available, or in Amazon Web Services, or Azure Services as well, JAMS does not care.

Great. The next one I have here, again, I apologize for paraphrasing. How easy is the SQL converter? Do we have to manually set things up after they’re imported? Actually, Rob, if I give you a minute here, would you be able to open up JAMS and show us the converter in action? I’ve seen a couple of questions here, I think people would like seeing that.

Sure. Let me switch my screens here, see if that came through.

Yeah.

All right, so clicking on the SQL Agent jobs shortcut here, over in the left-hand frame, will bring up a list of all the SQL Agent jobs that your JAMS server is connected to. Additionally, you can search any SQL server in your environment to get a list of its SQL Agent jobs. You can select one or more of these SQL Agent jobs, right-click, choose convert, and in the dialogue box you can set a name. You can add a description if you want. You can see the connection string is already pre-configured here, so let’s just pick a folder that we want to save this to and click convert. That’s how simple it really is.

But let’s just over to the definitions folder, I saved it in this one. Let’s do a refresh here and you can see that JAMS has actually created four jobs, one for each of the steps that was in that SQL Agent job. And then, it created the sequence which contains those four jobs in that sequential dependent order.

Great.

You also asked … Hey, I’m sorry. You also asked if everything comes over. Yes it does. The schedule that was on the SQL job came over automatically as well. Any schedules there will come over and within JAMS you can also add as many schedules as you’d like.

Great. Back in that editor, going back to a previous question here, one person asked if they can run jobs in parallel instead of in sequence?

Sure. So, remember how I mentioned we pulled in the submit job activity at a job? Well, if we pull in the parallel activity into JAMS, we can then add additional jobs, or even move some of the jobs we have here in to this parallel sequence to run them in a parallel manner. And I missed – there it goes.

Great.

Oh and, by the way, these processes that we didn’t want, I’m guessing the next question here, we can remove these very easily by highlighting and clicking. So, we have now cleaned up our JAMS sequence.

Great. All right, the next question that I have here is, and again I apologize for paraphrasing, about running SQL Agent jobs and SSIS packages without converting them. Can JAMS run those without converting them?

Yes. So, JAMS also has a form based execution method specifically for running SSIS packages. So, simply choose the SSIS execution method, fill out the form to choose your SQL connection, your credentials, where you want the package or where the package lives, what package you want to run. And if that is executing SQL jobs, then that will execute the SSIS package, will run whatever SSIS wants to run. So, absolutely.

Great. I have another question here, I’m going to take this one myself. Somebody was asking if the script that was in a slide earlier was for Informatica, they saw pmcmd in the script. So yes, that example was an Informatica job, and JAMS connects to dozens of different applications out-of-the-box. But if you’re interested in something else we can do inside of Informatica, or SAP, or JDE feel free to let me know in the chat and I can get you some more information on what we integrate with.

All right, so I’m seeing a couple of additional questions here, but those are outside of the normal scope of this webinar. So, if you have a question that we did not get to, we will respond to you right after this webinar is over. Thank you again, Rob, this has been really informative. And thank you all for joining us today.

If you’re looking for more information on how to bring enterprise features to your job scheduling, I encourage you to get in touch with our team. And if you’re new to JAMS, remember you can start a trial at anytime by downloading the latest version of JAMS right for our main site. And that download is going to give you everything that you need to get up and running, including ways to contact our support team members, like Rob, and links to training materials. So, thank you all, again, for joining us and have a great day. Thank you, Rob.

Thanks to everybody.