AlwaysOn Availability Groups and SQL Server Jobs, Part 27: Options and Concerns for More Advanced Deployments

Backups and multiple Secondaries. This one’s the easiest – because it’s already, effectively, covered. Just set your preferences for which replica you’d prefer to execute backups on, and you’re done. (See posts 20 – 22.)

Disaster Recovery Scenarios. If you’ve created remote-availability solutions or ‘stretch’ deployments where, say, you’ve got 2 FCIs in a data center and have then ‘linked’ that cluster to a cluster in a secondary data center (via AlwaysOn ‘replication’), then you’ve done so for the purposes of creating as optimal of a fault-tolerant solution as possible. In most cases you’ll have tried to mirror settings/configuration details within one data center as closely as possible with those in the other. Yet, sadly, it’s still all too common for management to try and ‘dumb down’ one of those data centers (and the hardware capabilities) as a cost-savings device. (This usually never really works out as expected – so feel free to rail against this whenever you see it being attempted – unless there are VERY clear cut SLAs that can defend you against the backlash that will inevitably follow…). As such, if you’re in one of these unenviable positions, just realize, you’re in a scenario where your workloads should NOT be the same from one location to the next – so some jobs should potentially be turned off (or may not have access to some of the ‘infrastructure’ and endpoints that might’ve been accessible in the other datacenter – so you’ll have to plan accordingly (and take all of this into consideration for how this will impact the strategy you use to keep jobs synchronized and/or available to be activated/deactivated as needed).

Licensing. Hopefully this one goes without saying, but I’m always surprised at how commonly organizations miss this one – so, here goes: Just remember that if you’re running Software Assurance, you’re effectively getting a ‘buy one get one (of equal or lesser value) free’ kind of deal – where Microsoft will sell you a SQL Server Enterprise Edition license that you can then use on a ‘primary’ server and where you can also spin up a SECONDARY server (on equal or lesser hardware) to be used SOLELY for failover purposes. (Sneaking reports off of that server, or letting developers spin up test databases, etc. renders this ‘failover license’ moot and requires you get a production license instead.) As such, whenever organizations start talking about scale-out scenarios (i.e., setting up read-only Secondary replicas), it’s important to call out that while those options ARE exciting and awesome, they do come at the price/cost of an additional license. (Though, this license, in turn, then comes with a ‘buy one, get one free’ kind of license too – when purchased with Software Assurance (as of SQL Server 2014 and above – before that, SA wasn’t required).)

Synchronization and Job Activation. This is the real point I wanted to get at in this post. Sample code or example solutions in posts up to this point have really been focused primarily on ‘2 node’ Availability Groups – and have relied heavily upon the notion of using an abstraction in the form of having 2 servers that equally call each other ‘PARTNER’ (via linked server interfaces) as a way to make it pretty easy for them to check on each other and compare details about active jobs, job details (e.g., is a Job on ServerA defined the exact same way as on ServerB, etc.), and so on. The problem, of course, is that if/when you stand up ServerC and/or ServerD, etc. – this construct/trick no longer works. Instead, you’ll have to look into using something else to ensure that job definitions on your servers remain synchronized or the same. Handling failover, or detecting which server is ACTIVE and should handle the execution of your jobs, happily, remains the same – as the code outlined in post #7 still works as expected. That said, with read-only Secondaries (where you’ve paid for extra licenses to offload processing from a primary server), you MAY want to look into options that would let you target some (read only, of course) SQL Server Agents (for reporting or whatever) against one of your read-only Secondaries. If that’s the case, then you’re going to find that you’ll need some other way to detect a ‘preferred’ server after a failover occurs and shuffles everything around. One obvious option here might be to ‘key’ job execution off of the same logic that defines your preferred replica for backups. In many cases, if you’ve opted to off-load backup processing on to a secondary, it would stand to reason that running read-only SQL Server Agent jobs here would make sense too. Otherwise, if/when more than 2 servers are involved, you’re likely going to have to either set up something that checks all servers by name (instead of using the PARTNER) linked-server alias – and then you’ll also have to tackle the issue of determining where that code should run.