AlwaysOn Availability Group Aware SQL Agent Job Step

The SQL Agent job step that I present here is intended to make a job execute only if the node currently owns a specified replica. The T-SQL for this job step leverages a function that was published in SQL Server Magazine and can be found here. The function, fn_hadr_group_is_primary, takes the name of the availability group as a single parameter and returns true if the replica is currently housed on the node and false if the replica is not currently housed on the node. I strongly recommend reading the SQLMag article as well as the articles in the larger series.

My job step calls the function to determine the status of the node. If the function returns true, the job continues to the next step. If the function returns false, the step calls msdb..sp_stop_job and the job terminates. The advantage to this approach is simplicity. You can simply insert this as the first job step into an existing SQL Agent job. No further modifications are necessary. Here is my code:

1
2
3
4
5
6
7
8
9
IF master.dbo.fn_hadr_group_is_primary('ophelia') = 1
          PRINT 'This server houses the primary replica, so continue to next step';
ELSE 
BEGIN 
	-- we're not in the Primary - exit gracefully:
	PRINT 'This server does not house  the primary replica, so terminate job'
	set @jobid = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))
	exec msdb..sp_stop_job @job_id = @jobid	
END
This entry was posted in SQL Server, T-SQL, Uncategorized and tagged . Bookmark the permalink.