Now we have our AlwaysOn solution and the same SQL Agent jobs running on each availability replica and they reference the availability group listener. So we're all set up and running with our Availability solution but our SQL Agent jobs are unaware of all of this. The solution is to script copies of each SQL Agent job on each Availability replica.
Then the job is only to run if the current server is the primary. We can use an extra starting T-SQL script in our job to determine if we are the primary replica.
DECLARE @IsPrimary int
SET @IsPrimary = 0
SELECT @IsPrimary = 1
FROM sys.availability_groups sag
INNER JOIN sys.dm_hadr_availability_group_states hags ON sag.group_id = hags.group_id
WHERE hags.primary_replica = @@SERVERNAME AND sag.name = [Your Availability Name]
IF @IsPrimary = 1
BEGIN
RETURN
END
ELSE
BEGIN
RAISERROR ('Database is running as secondary', 16, 1)
END
If the task fails we can go to a final step that succeeds so as to report the actual job as successful completion and not alert us accordingly as a failed job. If the task succeeds it should use the next step to run these rest of the job as we running on the primary replica.Labels: AlwaysOn, SQL2012, sqlagent