I’ve recently been spending a decent amount of time helping a couple of different clients work on setting up and managing SQL Server AlwaysOn Availability Groups. In my experience, while AlwaysOn Availability Groups are a powerful solution that let DBAs tackle both high availability (HA) concerns, like fault-tolerance, and some disaster recovery (DR) concerns, like remote availability, from within a single, unified, set of technologies and tooling, AlwaysOn Availability Groups also come with a number of considerations or downsides that need to be addressed as well.
Related: Changes to SQL Server 2014 AlwaysOn Availability Groups
Chief among these considerations is that AlwaysOn Availability Groups are non-trivial to setup and manage CORRECTLY. And a core part of the issue with manageability, in my opinion, is the seeming dearth of guidance and information available from Microsoft for patterns and best practices to employ when managing SQL Server Agent jobs when AlwaysOn Availability Groups are thrown into the mix.
Best Options and Solutions
To that end, I’ve spent some time over the past two weeks outlining over 30 blog posts that I’ll be sharing over the next few weeks that will help take a look at the problem, examine some of the options for addressing those problems, and then work through some of the pain points and issues I’ve found when working with some of those options.
The goal of this series, then will be twofold: to provide some tangible guidance and best practices that organizations can use to help properly manage and address concerns when using SQL Server Agent Jobs and SQL Server AlwaysOn Availability Groups and to outline specific reasons and problems or concerns that crop up with some of the various options or approaches possible. Or, in other words, my hope is to be able to specifically show why a couple of ideas or options that might initially appear obvious aren’t such a great option in the real world and showcase why I ended up with the solutions I did in the end.
Series Overview
To help organize the content and insights I’ll be sharing, I’ll be breaking my blog posts up into three main categories of posts or sections of content:
- Introduction. In the first few posts in this series I’ll obviously be focused on setting the stage and addressing the kinds of problems that subsequent posts will address. But I’ll also be outlining some terms or terminology that I’ll end up using throughout additional posts and even spend a bit of time covering whether some considerations and pointers organizations should examine before determining whether SQL Server AlwaysOn Availability Groups are even a good fit for their needs and goals.
- SQL Server Agent Jobs. In this part of this series content will focus on ways to determine how, when, and where to execute SQL Server Agent Jobs when Availability Groups are in play. Options for detecting target replicas will be explored as well as options for determining how SQL Server hosts or instances can know which Jobs should be fired against which Availability Groups—and when. We’ll also take an in depth look at the need for job synchronization and categorization as means to help ensure that Jobs aren’t fired or executed on the wrong servers or at the wrong times.
- SQL Server Backups. While backups are typically (though not always) handled by SQL Server Agent Jobs—and share many similar traits with SQL Server Agent Jobs, there are enough differences between backups and batch jobs or ‘jobs’ to warrant an entire ‘section’ of dedicated content. As such, posts in this part of this series of posts will focus on major concerns with backups, disaster recovery, business continuity, and how all of that relates to SQL Server AlwaysOn Availability Groups and the need to tackle or manage jobs across multiple SQL Server hosts.
Part 2: Putting AlwaysOn into Context
Part 3: Defining Batch Jobs
Part 4: Synchronizing Server-Level Details
