Running the SQL Server Job on the desired node of Availability Group

Post configuring SQL Server Always-On a very common problem and issue I have seen happening is running SQL Server Jobs on the desired side of AG.

Like backup jobs to only run at secondary, some reporting jobs to run only on secondary, index maintenance job to run on primary and so on.

I have been using the below script might be helpful to you too:

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

GitHub link.

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