UVA Health Cures SQL Agent Ailments with JAMS

COMPANY BACKGROUND

The University of Virginia Health System (UVA Health) is an academic health care organization associated with the University of Virginia. The organization includes a top-ranking hospital, level-1 trauma center, nationally recognized cancer and heart centers, and primary and specialty clinics throughout Central Virginia. UVA Health provides inpatient and outpatient care, patient education, and conducts medical research and education.

THE CHALLENGES

For years, UVA Health had a “traditional” once-a-day data warehouse process. “But,” explained Scott Carter, Systems Analyst and DBA, “we moved into a world where users want clinical reports updated on a more frequent basis.” Users increasingly required reports for “a given point in time during the day.” Those demands added new automation standards to underlying batch processes and automated workloads.

Hosting critical applications and business processes on multiple platforms had created a variety of pressure points for UVA Health’s workload automation tools. To resolve failures, UVA Health often had to involve multiple IT staff members, pulling them off higher value projects.

SQL Agent’s lack of support for dependencies often derailed critical workloads. Carter pointed out, “With SQL Agent, we would eyeball our scheduling, everything was timed. If a dependency wasn’t fulfilled or a job failed, jobs would still fire off everywhere else. It was like a train. Once one car went off the track, all the rest behind piled up on it.” A given SQL job might relate to patient admission or treatment and therefore have a direct impact on patient care, so delays of a day or more were unacceptable. UVA Health tried leveraging 3rd party file watchers and SSIS packages to mitigate errors, but these tools were often “brittle” and difficult to configure for even very basic schedules.

SQL Agent’s architecture created technical support headaches as well. When outages caused process failures, technicians had to manually access SQL Server boxes to get basic information. Troubleshooting failures on weekends was especially difficult. “They usually had 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.”

UNIX visibility posed another roadblock to UVA Health’s workloads. The Data Science team relies heavily on cross-platform workloads that include jobs that run on both UNIX and SQL Agent machines. Job failures that involved UNIX boxes could often only be resolved with the help a UNIX administrator.

Jobs run by individual employees outside of the central schedule created another bottleneck. These jobs were effectively invisible to the enterprise, and could remain unexecuted for long periods of time. Carter’s team tracked these jobs manually using Visio swim lanes, but this added an extra set of tasks to their day.

THE SOLUTIONS

UVA Health evaluated multiple schedulers in their search for a workload automation solution before selecting JAMS. “We’re something of a nonprofit,” said Carter, “so cost was definitely a huge factor. The functionality [the other schedulers] offered was virtually equivalent to JAMS, but JAMS was half the cost.”

UVA Health replaced each of the tools it had used to keep critical processes running with a centralized solution. First, Carter and his team combined disparate jobs into managed workflows. “JAMS has enabled us to use multi-platform setups,” said Carter. “We’re doing one thing in Windows. Then we have another job that runs on UNIX. Then a job waits on that in the same setup to run additional tasks on the Windows side.” Next, UVA Health pulled in close to 30 SQL Agent jobs with the integrated SQL conversion utility. “Migration of those over to JAMS was extremely easy and went very quickly,” Carter noted.

In addition, UVA Health automated stopping and restarting of services in UNIX for Business Objects. “We don’t manually run UNIX jobs anymore,” said Carter. “A dependency waits on the data warehouse update, so the UNIX process knows when to kick off. Jobs further downstream take the handoff and load the data back into the warehouse. It’s all done at the proper time, and we don’t have to manually intervene when there are issues.”

The system’s third-party file transfer tools and several SSIS scripts have been replaced by robust, native file transfer features in JAMS. JAMS easily handles UVA Health’s SFTP requirements, and automates zipping, unzipping, and deletion.

UVA Health leveraged JAMS to create a comprehensive set of notifications for critical events within its master schedule. Notifications are distributed not only to the admin group responsible for troubleshooting, but also to business users, so they’re aware of any potential delays.

UVA Health no longer uses a Visio diagram to represent its master schedule. The JAMS Projected Schedule view provides server administrators with a graphic representation of their schedule, and helps them easily identify peak processing times.

THE BENEFITS

Now a year into implementing JAMS, the team at UVA Health runs hundreds of jobs and setups every day. Scheduled hourly workflows enable UVA Health to meet business demands for near real-time information.

JAMS has centralized management of even the most complex of UVA Health’s cross-platform workloads. It has also eliminated the need for personnel to manually run jobs. “We see when jobs run and we know when they don’t. We have one monitor we can go to that shows us everything that’s running at a given time. It’s nice to have everything centrally managed. That’s been a huge benefit, and a huge time saver.”

UVA Health has established an even greater security standard by centralizing its jobs. “All 40 people on the team can open JAMS and see what’s running, but,” Carter explained, “only administrators can actually go in and create and modify setups or jobs. Business users can see what’s running, and get notified of the processes that impact them – all while keeping the actual jobs secure.”

Native reporting and dashboards in JAMS give Carter’s team the ability to easily share information about the automation environment with management. “It’s nice to have a concise view of metrics that you can give to [upper management]. We’ve shown [them] the kind of volume we’re seeing and the amount of jobs that we’re running, the ones that are successful and the ones that failed. We’ve actually used the number of jobs we’ve added in the last year as a metric to show how much we’re growing.”

UVA Health no longer uses estimated times to trigger jobs. Event-based scheduling gives them the ability to schedule jobs with great accuracy. “With JAMS, we’ve been able to make the schedule more robust. Now, things are running when they’re supposed to run – when events transpire – as opposed to a time which may or may not be correct,” said Carter.

Scheduling trends are easy to identify and analyze with JAMS history data. “We have some jobs that only run quarterly or monthly. When you go to check what happened last month on SQL Agent, you wouldn’t be able to tell because history would have rolled off. You only keep a certain amount of history on SQL Agent. With JAMS, we can go back and look at previous runs from the beginning of JAMS to now. It’s been fantastic.”

With JAMS, there are no more train crashes. “If there’s ever an issue with the source system, everything waits,” Carter explained, “all we have to do is restart one setup, and then all the setups and dependencies downstream kick off appropriately. It’s been a lifesaver. It’s a lot nicer to just re-run one job and then have the natural schedule take place as opposed to manually restarting 15 or so SQL agent jobs. We’ve gone from maybe taking a day to recover to 4 hours.”

UVA Health has benefited from the 24×7 Support of the JAMS Team. “Your online tech support is top notch.” Carter stated, “We join a chat window with one of your technicians and they solve a problem or get us pointed in the right direction very quickly. That’s unusual from most of the software vendors we work with.”