AlwaysOn Availability Groups and SQL Server Jobs, Part 3: Defining Batch Jobs

Among other things, the SQL Server Agent provides SQL Server with a job scheduling engine (i.e., a daemon or bot) that can be used to schedule the execution of various tasks. Practically speaking, there are a number of different kinds of jobs that can executed on a given SQL Server (via the SQL Server Agent’s ability to schedule jobs).

Maintenance Jobs

One of the more obvious types of jobs that are run on SQL Servers—or via the SQL Server Agent—are maintenance jobs. Jobs tasked with things like executing backups, checking for consistency errors, performing index maintenance, updating statistics, truncating history, checking for free disk space, and other, similar, maintenance tasks. In terms of scope, some of these types of jobs are focused on what I typically refer to as server level (though, technically, it might be more apt to describe them as being at the instance level)—either because they’re focused on evaluating or maintaining server level resources (such as disk space checkups, cycling logs, or truncating SQL Server Agent history) or because they’re being fired off against pretty much all databases on the server (or instance)—such as when doing index maintenance, backups, or checking for corruption errors.

Batch Jobs

But there’s also another type of commonly deployed SQL Server Agent Jobs as well—those that, within this series of blog posts, I’ll call batch jobs. Technically speaking, most SQL Server Agent Jobs (but not all) are batch jobs in the sense that they’re a wrapped batch of commands or operations sent to be executed at various times against the server. In this case, however, I’m using the term batch job to connote blocks of user-defined code that fired against user-databases to tackle various business needs and other operations. For example, with e-commerce applications, I commonly find that many organizations will set up regular tasks to go out and delete abandoned carts—or shopping carts that haven’t been touched in the last N hours. Situations like this can be pretty easily tackled by SQL Server Agent Jobs, and are best tackled when a SQL Server Agent Job is used to invoke a sproc or something within the target database.

At issue, though, is what happens to these batch jobs when they need to be fired off against a user database that’s part of an AlwaysOn Availability Group? If, for example, we’re talking about a bit of code that will delete or archive user shopping carts that are over N hours old, that code can’t be successfully called against an AlwaysOn Availability Replica that’s a read-only Replica or that’s a non-readable (or write-able) availability replica. Instead, it can only work if it’s correctly fired against the primary replica where read/write operations are allowed. As such, a major portion of this series of posts is focused on how we detect the correct replica and target that replica when failover (i.e., the ability of the primary replica to move to a different host) is thrown into the mix.

NOTE: While the notion of a Batch Job automatically assumes that we’re firing off batches of T-SQL code, I feel it’s a worst practice to actually put anything OTHER than calls to stored procedures in the Command text-box of a Job Step (because it’s just too easy to introduce bugs against code writing in a text box and because maintaining this code can be a total pain).

NOTE: While it’s fairly common for organizations to use batch jobs as a way to tackle regular, recurring, processing of business logic, there’s a school of thought that argues against putting any sort of business logic into the database and which would argue that setting up a daemon or bot at the application level would be a better choice. I don’t fully subscribe to this line of thinking—but I don’t discount it either. My recommendation is to try and use whatever makes the best sense long term (SQL Server Agent or chron jobs run at the application level)—though it’s worth pointing out that creating schedulers and services at an application level is non-trivial and that the SQL Server Agent is a fantastic Jobs engine because of the flexibility it provides in terms of both scheduling AND alerting.

Looking Forward

Given the difference between the two types of jobs outlined above, this series of blog posts on SQL Server Agent Jobs and AlwaysOn Availability Groups will focus, somewhat ironically, on three different types of jobs:

Part 4: Synchronizing Server-Level Details