October 22, 2018 at 8:51 am
I have this piece of code which I am using on the primary server so I can be notified if there is a change in a role of AG (when it becomes secondary, I would like to be notified) but what I notice when I run this query is that the role of the DB shows Primary as well as Secondary. I am using the wrong system table which is showing me that DB is primary on an existing server but also is a secondary on a diff server. Just trying to understand.select distinct adc.database_name as 'DatabaseName',@@SERVERNAME,ag.name as 'AGName', ars.role_desc from sys.availability_databases_cluster adc
join sys.availability_groups ag on adc.group_id = ag.group_id
join sys.dm_hadr_availability_replica_states ars on ag.group_id = ars.group_id
where ars.role_desc <> 'PRIMARY'
order by ag.name, database_name
Complete code:alter procedure AGStatus
as
DECLARE @AGStatus table(
ServerName varchar(50)
,AGName varchar(50)
,DatabaseName varchar(50)
,Role varchar(20))
DECLARE @DBNAME 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' and EmailAddress like 'syed%'
SELECT @ProfileName = name from msdb.dbo.sysmail_profile
SELECT @DistributionList = @EmailAddress
SET @ServerName = CONVERT(VARCHAR(255),SERVERPROPERTY('ServerName'))
INSERT INTO @AGStatus (DatabaseName, ServerName, AGName, Role)
select distinct adc.database_name as 'DatabaseName',@@SERVERNAME,ag.name as 'AGName', ars.role_desc from sys.availability_databases_cluster adc
join sys.availability_groups ag on adc.group_id = ag.group_id
join sys.dm_hadr_availability_replica_states ars on ag.group_id = ars.group_id
where ars.role_desc <> 'PRIMARY'
order by ag.name, database_name
if exists (select top 1 databaseName from @AGStatus)
BEGIN
declare report_cursor CURSOR fast_forward for
select DatabaseName from @AGStatus
OPEN report_cursor
FETCH NEXT from report_cursor into @DBName
while @@FETCH_STATUS = 0
BEGIN
FETCH NEXT from report_cursor into @DBName
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 Failed Job</th>
</tr>
<tr>
<th>ServerName</th>
<th>AGName</th>
<th>DatabaseName</th>
<th>Role</th>
</tr>'
SELECT @body1 = @body1 + '<tr>
<td>' + ServerName + '</td>
<td>' + AGName + '</td>
<td>' + DatabaseName + '</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]
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply