SQL Scheduling Goes Enterprise: JAMS at Frontline Technologies

COMPANY BACKGROUND

Frontline Technologies is a market leader in workforce management software. The Pennsylvania company serves thousands of global clients through its three applications: Aesop, an automated substitute placement and absence management system; Veritime, a time and attendance product; and Jobulator, an automatic job-notification service for substitute teachers. One in every five U.S. school districts uses Aesop to manage its personnel. Frontline has also expanded into a variety of other industries, including health care, security staffing, live events, manufacturing, and libraries.

SQL jobs represent a majority of the company’s batch jobs, but calling Visual Basic scripts and .NET scripts also are critical to the company’s business.

THE CHALLENGES

Frontline’s SQL databases form the cornerstone of the company’s workforce management applications. The company depends on SQL to handle its customers’ high volume of time-sensitive queries on important data, such as payroll records, timesheet entries and attendance records. Although SQL’s native tools provide basic scheduling capabilities, Frontline recognized that leaning on SQL for automation was a less than ideal solution. Frontline DBA Eric Butler explained the following:

First, in order to submit jobs to the schedule, SQL Server Agent often requires granting broad-reaching database permissions to users such as developers and support staff. Butler says these users shouldn’t have the “keys to the kingdom” and adds that “when you have so many jobs running, some of which run multiple times per day, managing these permissions takes up a considerable amount of staff time.”

Second, he points out that SQL Server jobs are prone to blocking, a situation in which one process fails due to the consumption of a critical resource (e.g. CPU, memory) somewhere else on the system. SQL maintenance jobs tended to run fine, but blocking was a common problem when interacting with SQL at the application level.

Third, using native SQL tools to configure disaster recovery (DR) did not provide Frontline with an acceptable standard of synchronization. In most cases, the company required that an alternate production database be up and running within 15 to 30 minutes should a DR event occur. Butler remarked that with SQL Scheduler, “jobs are never 100% in sync.” Native tools required that jobs be copied manually. This meant that during the course of 15 minutes of job copying, very recent changes – ones that might affect payroll jobs, time tracking jobs and personnel jobs – could be lost between the two data centers.

THE SOLUTIONS

Frontline looked at a few competing products in the SQL scheduling space, and quickly discovered that JAMS Job Scheduler closely aligned with their requirements.

The company resolved SQL-related security, blocking and disaster recovery issues by leveraging JAMS for batch processing and scheduling tasks. Comparing it to the native SQL Scheduler, Butler says”, “JAMS is enterprise. It’s scalable and has the performance we need.”

Centralizing user privileges in JAMS eliminates risks associated with granting unnecessarily broad permissions just to submit jobs to the company’s schedule. JAMS security uses Active Directory roles to authenticate users at the right permission level at the right times. Granting SQL administrative rights to support and development staff is no longer necessary to maintain an efficient and secure schedule.

JAMS job scheduling and workload automation is independent of SQL processing, so “blocking” is a non-issue. The resources required by SQL Server are reserved exclusively for database tasks, while JAMS manages the parameters, triggers, and conditions necessary to execute workflows.

JAMS fault tolerance architecture makes it easy for Frontline to configure and maintain its disaster recovery plan. Leveraging JAMS, Frontline has implemented log shipping at frequent intervals. By employing log shipping, every transaction and change to the schedule is backed up every 5 minutes. The resulting log is then sent to a secondary data center. “I can do mirroring,” says Butler, “which enables me to have our Phoenix data center close to zero latency.”

THE BENEFITS

Moving scheduling and automation off SQL Server ensures that Frontline uses IT resources efficiently. “SQL Server is an expensive tool.” says Butler. “It makes no sense to put scheduling on the SQL Server. It should be managing data – tracking, managing and querying, and ensuring that data is safe.” Online Transaction Processing (OLTP) best practices for keeping application logic off databases are aptly met by using JAMS. Says Butler, “When we’re scaling SQL, we’re doing so for the database not to compensate for application processing.”

Butler compares the scheduling on SQL to piling on feathers. “They may seem light as you add them individually, but 5 pounds of feathers is still 5 pounds.” One of Frontline’s goals is to remove anything extraneous from the database. By removing scheduling, Frontline achieved the maximum useful life from its investments in SQL server licensing. If scheduling had overtaxed database resources, they may have needed to make a move sooner than every 5-6 years.

Frontline has taken an integrated approach to scheduling and has connected internally developed monitoring tools to JAMS. Through secure channels, the company accesses the JAMS database tables, searching for conditions that signal impending problems. Looking ahead, Butler sees JAMS as a valuable technology platform.