Following my presentation of “How to HADR Your SQL Server Jobs” at the HA/DR PASS Virtual Group yesterday, David Klee (@kleegeek) and I continued chatting for a bit longer after the recording was concluded. During which, the MSX/TSX feature of SQL Server came up, and David suggested that I’d utilize my newly earned MVP status to push a feature request to expand these capabilities.Read More »Feature Request – Expand MSX/TSX Capabilities for HADR
In one of my previous blog posts, Automatically Enable or Disable Jobs based on HADR Role, I provided a solution for automatically enabling or disabling scheduled jobs in combination with Availability Groups and Database Mirroring. In this blog post, I’m going to build upon my previous solution and improve it even further – taking it to the next level of automation!Read More »Control SQL Jobs based on HADR Role – Taking it to the Next Level
Imagine the following: You have a couple or more SQL Servers with some sort of High Availability solution between them (AlwaysOn Availability Groups, Database Mirroring, etc.). You also have a bunch of scheduled jobs which you need to be run on the Primary server.
But wait…. How would you make these jobs run only on the Primary server? After all, if they try to run on the Secondary, they would fail (whether because the database is inaccessible or because it’s read-only). Additionally, you would need to consider the possibility of a failover where the Primary and Secondary servers would switch roles.
This means that you would have to, first, create these jobs on both servers, and implement some sort of mechanism that would detect, for each job, whether the instance it’s being executed at is currently the Primary.
There are a few ways to go about doing this.
[For the sake of this article, let’s ignore the fact that most people don’t even think about it, and leave all of their important jobs on the Primary server only, while ignoring the risks of what would happen when their Primary server crashes and fails over to the Secondary]