AlwaysOn Availability Groups and SQL Server Jobs, Part 26: Using Master Servers for Job Administration

Introduction. Posts 1 through 6 – where I introduced the ‘problem domain’, defined terms, and provided some high level rationale for why synchronizing jobs and worrying about failover is even a concern.

SQL Server Agent Jobs. Posts 7 through 18 – where I covered some of the problems and limitations – as well as options for work-arounds in dealing with ‘batch’ jobs that can potentially span multiple servers.

Backups. Posts 19 through 25 – where I touched upon some high-level considerations for making sure that backups work as expected and don’t cause any surprises.

Additional Considerations. Posts 26 through 30(ish) – where I’ll provide some very high-level notes and details on some more ‘advanced’ concerns, strategies, and potential techniques for addressing SQL Server Jobs when AlwaysOn Availability Groups are involved.

SQL Server Multi Server Administration

Stairway to SQL Server Agent – Level 12: Using MSX/TSX to scale out Job Management

Set up an MSX/TSX configuration – where you’ve got your AG hosts set up as Target Servers where you can ensure each of the SQL Server Agent Jobs you want/need to run will end up running across ALL Target servers at the same time – and with the exact same logic/code defined.

Tweak or ‘modify’ your ‘multi-server’ Jobs so that they include logic to detect preferred replicas for backups (when dealing with backups) – as per post #22 – or to include logic to detect the primary replica when dealing with batch jobs – as per post #7.

Sit back, relax, and let things work themselves out – without all of the hassle, overhead, and problems associated with synchronization that I’ve covered in previous posts.

Don’t host your Master Server (MSX) on one of your Availability Group hosts – because you’d be compromising fault-tolerance for your jobs/backups. Or, in other words, since the whole purpose of Availability Groups is to account for the fact that an individual SQL Server instance (i.e., an AG host) can and WILL go down, putting the equivalent of the ‘Master Controller’ SQL Server Agent that’s coordinating execution of all of your jobs on one of your AG hosts is a guarantee that you’ll eventually lose your MSX at some point – and jobs/alerting/etc. will suffer as well.

If execution of your jobs and backups is important (and it most likely is), then you’re going to want to CLUSTER your MSX or Master Server. Or, in other words, if you stand up a new ‘controller’ (i.e., Master Server) to coordinate the execution of your jobs across multiple, fault-tolerant, SQL Server instances that are hosting your Availability Group databases, you’ve now introduced a new single-point-of-failure for a potentially mission-critical set of operations: backups and jobs execution. As such, you’re going to need to make your MSX fault-tolerant – something you can fairly easily do by having the SQL Server Agent acting as your MSX end up being hosted as part of an AlwaysOn Failover Cluster Instance – so that it’s adequately fault-tolerant as well.

Synchronization. MSX/TSX configurations may be pretty awesome once you’ve got them working correctly, but they can sometimes get out of sync.

SSIS Packages. While MSX/TSX functionality is actually set up to tackle SSIS packages (in the form of SQL Server Maintenance Plans) out of the box, I suspect you’ll potentially run into issues with those ‘over time’ – as well as any other SSIS jobs/packages you may have as well. Sadly, I can’t quantify any of this or point to any links on it – so I might be wrong here – but I have vague recollections of running into problems with these in the past (years ago) – so I figured I’d list SSIS packages as a POTENTIAL concern.