SQL Agent Job for databases in availability group

When we have SQL Server job running for databases in Always-On there could be need of running a specific job on Primary database only.

In such cases we are required to create the job on every server and add the logic that it runs only on the Primary server. The below template can be used for that. GitHub link.

-- SQL Agent Job Template for Always-On environment
-------------------------
-- Remove Line 9 and Replace 10-16  with your code
-- I prefer adding this information into the log that this job is skipped as the server is not PRIMARY

DECLARE @AORole VARCHAR(20) = 'PRIMARY';
DECLARE @Message NVARCHAR(100) = 'Executing is running on Primary'
IF @AORole = (SELECT role_desc FROM sys.dm_hadr_availability_replica_states WHERE is_local=1 AND role=1)
	PRINT 'Primary Server'
	/*
	Your Job code here
	.
	.
	.
	.
	*/
ELSE 
	EXEC xp_logevent 60000, @Message, informational

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s