AlwaysOn Availability Groups and SQL Server Jobs, Part 2: Putting AlwaysOn into Context

AlwaysOn is a marketing term used to describe two different high-availability (HA) and disaster recovery (DR) solutions from Microsoft: AlwaysOn Failover Cluster Instances (FCIs) or raditional clusters and AlwaysOn Availability Groups (AGs) which are a lot like Database Mirroring 3.0.

At a high level, both FCIs and AGs share the common theme or notion of using multiple SQL Server hosts to distribute a workload as a means of increasing fault tolerance (and in situations where one or more of the hosts involved in handling the load is remotely deployed, can also address concerns around disaster recovery via remote availability).

Despite similar intentions and high-level goals (and despite the fact that both AGs and FCIs are heavily reliant upon Windows Server Failover Clustering, or WSFC), the way in which FCIs and AGs tackle the exact specifics of how they enable HA and DR are quite different.

Related: AlwaysOn Availability Groups and SQL Server Jobs, Part 1: Introduction

High-Level Differences between AGs and FCIs

Simply put, there are enough architectural and fundamental differences between AGs and FCIs to fill entire books. So I won’t be able to do the topic any justice in a single post. In brief, however, Failover Cluster Instances use clustering technology to create two or more nodes or teamed hosts that coordinate with the domain controller to specify which of those physical nodes will control a virtual IP address, Virtual Network Name, and a virtual SQL Server Instance that spans or straddles all of the hosts—but can be active on only one physical host at a time.

On the other hand, AlwaysOn Availability Groups use the notion of mirroring (or server-to-server communication via endpoints) to keep distinct copies of data on multiple hosts synchronized and then, also, coordinate with the Domain Controller to, optionally, keep Availability Group Listeners pointed at a Read/Write replica for normal database interactions, as well as enable the possibility of having read-only replicas made available for scale-out scenarios.

To help put key differences into a slightly bit better perspective, FCIs and AGs each come with some pros and cons that help identify where they’re best suited:

AlwaysOn Availability Groups

AlwaysOn Failover Cluster Instances

In addition to the pros and cons of each of those AlwaysOn options, FCIs and AGs both share the same benefits or key strengths that make them worthwhile in the first place:

A Question of Context

So, why go over the differences between FCIs and AGs in a series of posts on SQL Server AlwaysOn Availability Groups and SQL Server Agent Jobs? Simple: If you use Failover Cluster Instances, then all of the concerns we’ll cover in this series of posts about how to get SQL Server Agent Jobs to work when AlwayOn Availability Groups are thrown into the mix are simply a non-issue. Or, in other words, assume that, for whatever reason, we need a job to run against the production version of our Widget database every 10 minutes.

If we use Failover Clustering to achieve High Availability, then if we set up that job to run against the Widget Database, it won’t matter which physical host our SQL Server Instance is running against—every 10 minutes the job will just run (because FCIs provide instance-level protection and synchronization as there’s really only ever one SQL Server Agent active or working and only ever one copy of the msdb database that keeps schedule meta data and state around for scheduling and executing SQL Server Agent Jobs).

On the other hand, if we use a simple 2-node AlwaysOn Availability Group and need our job to fire every 10 minutes against the Widget database, then not only do we have 2 copies (or Replicas) of the Widget Database (one in read/write or primary status and the other in a Recovery status or serving as a warm failover database), but we also have two totally separate instances of SQL Server running—meaning two distinct SQL Server Agents and two totally different and distinct copies of the msdb database with their own state info and meta data describing jobs. The problem, though, is which server’s SQL Server Agent owns or runs this job every 10 minutes? Obviously, we want to have the job fired on the server that’s hosting the Primary Replica. The trick is that getting that to work is a bit more complex and complicated than it might initially appear. Hence, this series of posts.

Using Failover Cluster Instance to Achieve HA

The point of this post, however, is to call out that if you are just looking for high-availability or fault-tolerance within your datacenter (and have a smoke-and-rubble contingency plan, i.e., backups, etc.) for what happens should your datacenter encounter an outage), then you are likely much better off using SQL Server Failover Cluster Instances to achieve HA instead of using Availability Groups.

Availability Groups may be all the rage and they do allow scale-out scenarios (though I think there are much cheaper options for achieving this than using AGs), and they may be a bit easier to set up and configure (in some ways), but they’re hands-down more expensive and troublesome to manage and own. As such, only use them if they 100 percent make sense—not simply because they’re cool or because you don’t want the hassle of setting up a real cluster.

Part 3: Defining Batch Jobs