About

Dear Eithan,
first let me thank for your post “Control SQL Jobs based on HADR Role – Taking it to the Next Level” that i hope can be for us (DHL ITS, Prague CZ, part of DHL International) very helpful hint and recommendation in next steps like “fight” with new visions of separately managed server boxes joined into HADR with un-sync system DBs – it’s bit a nightmare 🤦‍♂️. But Thank to people like you and Chrissy LeMaire (DBA Tools) we can still continue our jobs with head held high and world is much more simpler😊.
Let me kindly describe my concern about “AlwaysOn: Master Control Job” (*MJ), that looks like for some scenarios ignore jobs inside of category i want to manage in case that replicas going to be “switched”.
First discover was, why jobs are still running on secondary after switch? it is not possible, i’ve tested it, it showed me New Status: “0”…, in log of *MJ was the same information, what happened? did not know until i stayed sick after extraction of very back teeth, and in fever is was somehow still thinking about this strange behavior and had time to make my minds shape.
I detaily “dissect” your code, make some design changes to better reading and able to making notes/comments. In begin left only base INNER join of “job” tables and adding next LEFT join(s) and CApply. To me was from begin suspicious condition in the end, just for reason, that when i see WHERE depended on non mandatory part of outer joins, i always became alert 🙂 >>WHERE (Config.DBName IS NOT NULL OR ag.databasename IS NOT NULL) uff if it is not there it is returning results together with previously missing, but for all cases from XML, so every time job will run it will try to change Enable tick to other state, so condition must stay there, but something inside query causing problem – and I need to understand this.
All job steps that calling SSIS packages (Type: SQL Server IS Package) has default job step db setup internally to master this is done by MS, and it is rule, not change to that. and i found in main query, that AG query returning configuration of HADR will never returns system DBs or DBs out of HADR. once this set of data is attached by outer join to base of “job” tables, the column ag.databasename taken from AG will for some cases returns NULL value. It is true, because for that exact job step there is no “friend” in AG and also for “job” tables in ag.databasename=js.database_name and in the end is WHERE will these records omits from final select/cursor/desired_state candidates. So i found root-cause, but what now?
I found as simplest solution to extent AG subquery for one more statical row “‘master’,1,’PRIMARY'” done by UNION and SELECT. in stable state of replicas it looks good, candidates for desired_state are coming/returning as expected, but what will happen ie. in the middle of switching, there will be only my static row and nothing else, what can cause this? – enable SSIS jobs to not stable replica? – not sure here and, hm, no support from out DBA guys – i know it sounds strange, they just recommend us SQL2022. but bring hands out of problem if any, so I am lets say lonely in this, and your code and idea if perfect, ie. if Alert runs *MJ for tens of times, it provides changes only once – just in case that all is stable – do desired_state change, else be just dummy – those MAX and MIN are really nifty!
So than, let me kindly ask you for help/reply/consultation if this is ok, if there is other way how to “inject” master db into range of accepted DBs for *MJ.

Please, understand this more as email than comment, bud i did not found any better way how to write to you, pls if possible respond to my email below ([email protected]) and make public this note is not necessary 🙂

Kindly,
Ondrej Liptak, CZ

PS: I truly hope that you, your family, friends and colleagues are these hard time, regarding international situation of last few days, O.K. Truly hope that it all will be soon over and solved mostly for citizens of Israel. 🤝