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 @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
	EXEC xp_logevent 60000, @Message, informational

GitHub link.

Leave a Reply

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

You are commenting using your 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