Comments posted to this topic are about the item Schedule Jobs on a Clustered SQL Server
One Orange Chip
Thanks for sharing. If you use Ola Hallengren's scripts for Jobs related to backups and other maintenance, they do the check internally so you dont need to do this check on Ola's Jobs.
Also when viewed through the GUI, the Job will show as yellow (not green or red). This just means there is , as far as sql server is concerned, a mix of events. A error yet wanting to report success. So just know these will not show true success (green) in the history, like the Ola Jobs I alluded to will.
I think the title here has gotten me a little confused, but that may be my old school ways of looking at this.
When I see anyone mention a clustered SQL Server, that to me is a failover cluster instance, as AOAG's are technically not clustered SQL servers, they are a collection of individual instances working together, while still sharing the underlying WSFC technology, SQL in an AOAG isn't clustered.
Additionally you mention shared storage, again AOAG's don't use shared storage, that is an FCI which does.
Now you could mix an FCI with an AOAG so combining the two technologies is perfectly acceptable.
Also no downtime, there is always downtime it is just mitigated to perhaps a few seconds and what may be classified as a momentary blip, as the connections would need to be re-established after the failover happens, so unless the application in use has connection retry logic, the applications will error with no open connection and the app will need to be restarted to re-establish connectivity.
So it seems your mixing the two technologies in the post.
I think it would be good to differentiate the two in the article as both FCI and AOAG all fall under the same marketing term of "Always On" Always On Failover Cluster Instances / Always On Availability Groups, and that is all AO is a marketing term.
Additionally, instead of the T-SQL code you posted to find the primary replica, you could also use the functions that MSFT provide you (sys.fn_hadr_is_primary_replica), I have seen issues with stack dumps being generated in the past when querying the DMVs here and the response from MSFT was to use the function. May not happen for everyone but probably one to note that if things do occur.
Overall this is a great post and informative, for me personally reading this, I think it could do with a few tweaks to fully document the topic in question here.
Ant-Green, some good points.
Prasad, what is the point of the last step? If I raise an error in the first step and exist or raise an error and jump to the last step exiting success SSMS still shows the job as yellow.
Yes, the job shows with a warning, but will not fail and send notification or support ticket. This will help to avoid false alarms. If you are monitor SQL jobs in SSMS, then this process will show a warning.
@Ant-Green, you are right I meant the SQL servers which are part of all way on availability groups. I used cluster for easy identification. Thanks for pointing it out.
Viewing 6 posts - 1 through 5 (of 5 total)