December 17, 2018 at 11:24 am
I have created an alert using the script below to see if I get an email if there is a failover but it's not working. I mean I failed over to the secondary server but no email was sent. I am not sure what am I missing.
Alert script:
USE [msdb]
GO
/****** Object: Alert [test alert for failover] Script Date: 12/17/2018 12:16:24 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N'test alert for failover',
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
Operator script:
USE [msdb]
GO
/****** Object: Operator [DBA Team] Script Date: 12/17/2018 12:18:12 PM ******/
EXEC msdb.dbo.sp_add_operator @name=N'DBA',
@enabled=1,
@weekday_pager_start_time=80000,
@weekday_pager_end_time=75900,
@saturday_pager_start_time=80000,
@saturday_pager_end_time=75900,
@sunday_pager_start_time=80000,
@sunday_pager_end_time=75900,
@pager_days=0,
@email_address=N'myemailaddress',
@category_name=N'[Uncategorized]'
GO
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
December 17, 2018 at 11:58 am
@Message_id = 0, that is nothing. Try making that 1480.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 17, 2018 at 12:37 pm
still no luck.
EXEC msdb.dbo.sp_add_alert @name=N'test alert for failover',
@message_id=1480,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
December 17, 2018 at 12:53 pm
You need to add the notifications to the alerts.
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Whatever name you gave it',
@operator_name = N'DBA',
@notification_method = 1;
GO
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 17, 2018 at 1:07 pm
still no email after I failed it over to the secondary. I did receive an email when I sent myself a test email so its not the SMTP server. There is something I am missing with this alert.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
December 17, 2018 at 1:20 pm
check database mail log and see if emails are being sent.
sometimes the profile security is not setup properly.
December 18, 2018 at 5:29 am
Emails are being sent fine. I checked the DB mail log and I don't see any issue.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
December 18, 2018 at 10:24 am
Just curious, does the test e-mail option located in the Sql Server Agent properties work? This is found under Agent>Properties>Alert System (tab)>Mail session
December 18, 2018 at 12:32 pm
is email setup correctly..
use msdb
go
SELECT items.subject,
items.last_mod_date
,l.description FROM dbo.sysmail_faileditems as items
INNER JOIN dbo.sysmail_event_log AS l
ON items.mailitem_id = l.mailitem_id
December 18, 2018 at 12:39 pm
RVSC48 - Tuesday, December 18, 2018 10:24 AMJust curious, does the test e-mail option located in the Sql Server Agent properties work? This is found under Agent>Properties>Alert System (tab)>Mail session
The reason why the email is setup correctly is because I get emails often when there is a job failure. So I don't think there is an issue with the email. I am just using this script I wrote. I have scheduled it to run every 2 minutes. I tested it and it is working as expected.USE [DBMAINT]
GO
/****** Object: StoredProcedure [dbo].[AGStatus] Script Date: 12/18/2018 1:36:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[AGStatus]
as
DECLARE @AGStatus table(
[AG Primary Server] varchar(50)
,ServerName varchar(50)
,AGName varchar(50)
,Role varchar(20))
DECLARE @Role VARCHAR(100)
DECLARE @body1 VARCHAR(MAX)
DECLARE @subject1 VARCHAR(64)
DECLARE @css VARCHAR(MAX)
DECLARE @ProfileName varchar(200)
DECLARE @DistributionList varchar(500)
DECLARE @EmailAddress varchar(500)
--DECLARE @ServerName VARCHAR(255)
SELECT @EmailAddress = COALESCE(@EmailAddress+'; ','') + EmailAddress from DBMAINT.dbo.EmailDistributionList where Role = 'D'
SELECT @ProfileName = name from msdb.dbo.sysmail_profile
SELECT @DistributionList = @EmailAddress
--SET @ServerName = CONVERT(VARCHAR(255),SERVERPROPERTY('ServerName'))
INSERT INTO @AGStatus (AGName, [AG Primary Server],ServerName, Role)
SELECT
AG.name AS [AGName],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
@@SERVERNAME as ServerName,
case
when arstates.role = 1 then 'Primary'
when arstates.role = 2 then 'Secondary'
end Role
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
where arstates.role = 2
if exists (select top 1 Role from @AGStatus)
BEGIN
declare report_cursor CURSOR fast_forward for
select Role from @AGStatus
OPEN report_cursor
FETCH NEXT from report_cursor into @Role
while @@FETCH_STATUS = 0
BEGIN
FETCH NEXT from report_cursor into @Role
END
close report_cursor
deallocate report_cursor
SET @css = '
<style type="text/css">
#body {
font-family: verdana,arial,sans-serif;
font-size: 12px;
background: #FFF;
width: auto;
height: 525px;
margin: auto;
position: relative;
overflow: auto;
}
p {
font-family: verdana,arial,sans-serif;
padding: 5px 0px 0px;
}
.gray {
font-weight: 600;
color: #9A8B7D;
}
.DimGray {
font-weight: 600;
color: #696969;
}
.results {
font-family: verdana,arial,sans-serif;
border-collapse: collapse;
width: 100%;
margin: auto;
}
.resultsTitle {
font-family: Verdana,Arial,sans-serif;
background: #696969;
font-size: 12px;
font-weight: 600;
color: #FFF;
padding: 5px;
border-color: #FFF;
border-width: 2px;
border-style: solid;
}
th {
font-family: verdana,arial,sans-serif;
background: #9A8B7D;
font-size: 13px;
font-weight: 500;
color: #FFF;
padding: 5px;
border-color: #FFF;
border-width: 2px;
border-style: solid;
}
td {
font-family: verdana,arial,sans-serif;
background: #DDD;
font-size:12px;
padding: 5px;
border-color: #FFF;
border-width: 2px;
border-style: solid;
}
</style>'
SET @body1 = '<html><head><title>DB Mail Alert</title>' + @css + '</head>
<body>
<div id ="body">
<table class = "results">
<tr>
<th class="resultsTitle" colspan="5">List of AG Servers</th>
</tr>
<tr>
<th>AG Primary Server</th>
<th>Current ServerName</th>
<th>AGName</th>
<th>Role of the current Server</th>
</tr>'
SELECT @body1 = @body1 + '<tr>
<td>' + [AG Primary Server] + '</td>
<td>' + ServerName + '</td>
<td>' + AGName + '</td>
<td>' + Role + '</td>
</tr>'
FROM @AGStatus
SET @body1 = @body1 +
'</table>
</div>
</body>
</html>'
SET @subject1 = 'AG Status has changed on ' + @@SERVERNAME
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@recipients = @DistributionList,
@body = @body1,
@subject = @subject1,
@body_format = 'HTML'
END
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
December 18, 2018 at 2:53 pm
Assuming SQL mail is working, it has to be the configuration of the alert.
I have this in a script that is part of the default set of scripts used to build or re-build a server.
EXEC msdb.dbo.sp_add_alert
@name=N'Alert - Error 1480: AG - Role Change',
@message_id=1480,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
GO
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Alert - Error 1480: AG - Role Change',
@operator_name = N'YOUR OPERATOR GOES HERE',
@notification_method = 1;
GO
Drop the existing alert, run this, failover.
In the history of the alert, you should see the date of the last occurrence. Check that to see if in fact the alert is being raised.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 18, 2018 at 3:13 pm
Could also be that the message id is not being logged if it's setup that way in sys.messages. It needs to be logged for alerts.
Sue
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply