Schedule Jobs on a Clustered SQL Server

Question

Schedule Jobs on a Clustered SQL Server

Prasad Chava

SSC Veteran

Points: 228
More actions
February 6, 2023 at 12:00 am

#4142487

Comments posted to this topic are about the item Schedule Jobs on a Clustered SQL Server

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

MMartin1 One Orange Chip Points: 27795 More actions · Answer 1

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.

----------------------------------------------------

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

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.

Mark D Powell SSCarpal Tunnel Points: 4775 More actions · Answer 3

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.

Prasad Chava SSC Veteran Points: 228 More actions · Answer 4

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.

Prasad Chava SSC Veteran Points: 228 More actions · Answer 5

@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.