AlwaysOn Availability Groups and SQL Server Jobs, Part 9: Dynamic Detection Gotcha #2

Using IF/ELSE statements. As above, an IF/ELSE will always throw an error when it detects the USE ; statement.

Using GOTO statements. Trying to trick your way out of this problem won't work either—GOTO statements are a bit . . .cumbersome, but still run afoul of a USE ; statement.

Using a RETURN to short-circuit. Trying to set up simple IF NOT PRIMARY logic that would execute a RETURN prior to your USE ; statement won't work either. Again, this code is parsed/evaluated BEFORE it is actually executed. (Plus, you can't use a RETURN statement in the command/code window for a SQL Server Agent job step.)

Use fully dynamic SQL. Even mentioning this option makes me cringe. But, the idea is that you could wrap ALL of your code and then execute it using EXECUTE or sp_executesql—after you've determined if the code should be run at all. Technically, this IS possible. But because of the potential for security issues, AND because this could quickly get INSANELY hard to manage as jobs and operations became more and more complex, I won't actually consider this as a truly viable option.

Using Sprocs for Redirection. This is a bit wonky too—and is, in my mind, just a single step down or only slightly less ugly than using fully dynamic SQL. But, the idea is that you COULD create sprocs in, say, the master database (you'd then have to keep this code synchronized across all servers in your AG topology), and then your SQL Server Agent Job steps/logic could: a) detect whether the host in question is the Primary or Not and then either exit gracefully; or b) keep processing code/operations by trying to run something like EXEC master.dbo.DoJobLogic @Params, @Etc, where; c) the body of that dbo.DoJobLogic sproc would just redirect with something like: "DELETE FROM MyAGDatabase.dbo.SomeTable WHERE blahBlahBlah = @Params" and so on. Again, I wouldn't recommend this, but it is possible. Right out of the gate you'd likely run into issues even trying to INSERT/UPDATE/DELETE against tables in a different database (though you could use SYNONMYMS here—one of the few places I think they actually even come close to making sense), and you’d likely find that maintenance (especially through all of the indirection) would be a pain. Accordingly, I wouldn't recommend this option at all—and would strongly recommend AGAINST it (just as with dynamic SQL).

Job Step Injection Checks. This one is viable, but comes with its own baggage (as we'll see in the next post). The idea, though, is that if you've got a SQL Server Agent Job that's got just one Job Step (for example), that you'd go in and ADD a new Job Step BEFORE your existing Job step, and have this new/injected Job Step check to see if the target DB (for the second Job Step) is acting as the Primary replica on the current server or not, and then either continuing on to Job Step 2 (if we're on the Primary) or simply short-circuiting and exiting if we're not on the primary. Overall, the process is fairly convoluted, not something I'd want to manage, and comes with some additional ugliness above and beyond the obvious problems you'd think of in a case like this. We'll look into this further in my next blog post.

Give Up and use a Different Approach. Instead of trying to cram a square peg in a round hole (i.e., trying to get around the problem with the USE statement), we could just go ahead and look at a different approach. Or, in other words, instead of trying to dynamically detect whether or not code should be executed at RUN time, we could go with the other option that I presented a few posts back and simply synchronize jobs across all servers and then actively enable or disable jobs based upon whether or not they're being hosted on the server with the Primary replica(s), or not. That might sound like a bit of work—and it is—but it's nowhere near as ugly or problematic as any of the "dynamically detect whether to execute at run time" options available.