SQLTeam.com | Weblogs | Forums

Jobs disable in secondary allwayson replica


#1

Hi,

Allwayson availabillity group is configured on some dbs. Once the failover happens the primary becomes secondary and secondary becomes primary. we configured some same migration jobs in both replicas. Now once the failover happens these jobs should be enable in primary and disable in secondary.

In seconadry replica job i wrote beow in respective Job step.

DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME
DECLARE @RoleDesc NVARCHAR(60)

SELECT @RoleDesc = a.role_desc
FROM sys.dm_hadr_availability_replica_states AS a
JOIN sys.availability_replicas AS b
ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @ServerName

IF @RoleDesc = 'primary'
BEGIN
exec database].[dbo].[abc] 5
END

i am executing stored procedure in secondary server job. when i am parsing the code i am getting error like the db is part of avalability group so not accessible. because dbs are in read access. also failure allerts are coming.
My requirement is i should not get failure alert.

How to resolve it?


#2

I got this to work. So, the question I would ask is are the AGs setup so the secondary is readable?