July 13, 2018 at 7:57 am
Hi, I've been Googling and testing to try to find a solution that meets my goal, but I'm not having much luck. I'm hoping you all can provide some insight.
Ok a little context. I have a simple AOAG setup. One primary server, one secondary server, one database in the AG.
My goal: I want to run a snippet of T-SQL ONCE when a failover happens--and that snippet is a subset of more T-SQL code that exists in a job.
Right now, for testing purposes, I have one alert set up on the secondary. The alert's response is to execute a SQL Server job. The job's code looks similar to this:
DECLARE @HadrRole TINYINT;
DECLARE @DatabaseName VARCHAR(100) = 'mydatabase';
DECLARE @EmailSubject VARCHAR(200);
DECLARE @EmailBody VARCHAR (500);
SELECT @HadrRole = ars.role
FROM sys.dm_hadr_availability_replica_states ars
INNER JOIN sys.databases dbs
ON ars.replica_id = dbs.replica_id
WHERE dbs.name = @DatabaseName;
SELECT @EmailSubject = @@SERVERNAME + ' Just became the Primary';
SELECT @EmailBody = 'The HadrRole is ' + CAST(@HadrRole AS VARCHAR(1));
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'myprofile',
@recipients = 'my@email.com',
@subject = @EmailSubject,
@body = @EmailBody;
--If @HadrRole = 1 then run this example code
-- example code
I've tried monitoring for error numbers 1480 and 19406 (separately), hoping that when the failover to the secondary happened, it would only trigger the alert once and therefore run the job only once. However, I'm seeing that it gets called anywhere from 2 to 6 times. This wouldn't be an issue if, let's say, all of the times the job gets executed @HadrRole was 0 and the last time it was 1--then my code would run once. However, in my testing I've seen four alerts/emails with @HadrRole as 0,1,1,1. I've seen the alerts get triggered a varying number of times with differing @HadrRole values (depending on the error number I'm monitoring for and whether it's failing over to or from the secondary).
In any case, I haven't been able to find a way to trigger the alert only once or let me run my "example code" in the job only once. Any ideas?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
July 13, 2018 at 1:08 pm
Have you tried using
IF fn_hadr_group_is_primary('AG_Name') = 1
BEGIN
"YOUR SQL HERE"
END
ELSE
BEGIN
PRINT 'This is not the primary replica - exiting with success';
END
July 13, 2018 at 3:15 pm
AlexSQLForums - Friday, July 13, 2018 1:08 PMHave you tried using
IF fn_hadr_group_is_primary('AG_Name') = 1
BEGIN
"YOUR SQL HERE"
END
ELSE
BEGIN
PRINT 'This is not the primary replica - exiting with success';
END
Alex,
Thank you. I can try that. My fear is that fn_hadr_group_is_primary('AG_Name') will evaluate to 1 on more than one of the alert calls (as I mentioned, it seems to get triggered 4 times when a failover happens). But I'll try. Thanks!
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply