This AAG Database State monitoring Script could be added as a step in a SQL job that runs frequently to check the status of AAG Database Synchronization.
declare @subject varchar(100)
declare @xml nvarchar(max)
declare @body nvarchar(max)
set nocount on
Create table #output(InstName varchar(50),
AGName varchar(50),
DBName varchar(50),
[State] varchar(20)
)
insert into #output(InstName, AGName, DBName, [State])
select @@servername, ag.[name], sd.[name], hdrs.synchronization_state_desc
from sys.availability_groups ag join sys.dm_hadr_availability_group_states hags
on ag.group_id = hags.group_id join sys.dm_hadr_database_replica_states hdrs
on hags.group_id = hdrs.group_id join sys.sysdatabases sd
on hdrs.database_id = sd.dbid
where hdrs.synchronization_state_desc not in('SYNCHRONIZING', 'SYNCHRONIZED')
if @@rowcount > 0
begin
set @xml = cast((select InstName as 'td','', AGName as 'td','', DBName as 'td','', [State] as 'td'
from #output
for xml path('tr'), elements ) as nvarchar(max))
set @body = '<html><body><H3>AAG Status Report</H3>
<table border =1>
<tr>
<th> ServerName </th> <th> Availability Group </th> <th> Database </th> <th> Status </th></tr>'
set @subject = 'Availability Group not Synchronizing: '
set @body = @body + @xml + '</table><body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default',
@recipients='DBA@SomeDomain.com',
@subject = @subject,
@body = @body,
@body_format = 'HTML' ;
end
drop table #output 
In level 6 of this stairway, we will look deeper into Availability Groups and how to set them up along with a Listener.
2024-07-23 (first published: 2015-03-25)
11,416 reads
We have a SQL Failover Cluster Instance in our primary data center, and want to implement a second two node Failover Cluster Instance in our secondary data center. A new two node cluster located in our secondary data center was setup, but I could not create a new High Availability Group between the two failover cluster instances. What went wrong?
2014-03-07
3,721 reads
Learn how to get a Failover Cluster Instance set up and configured with Windows and SQL Server. This is part of a series showing how to combine a Failover Cluster Instance of SQL Server with an Always On availability group.
2024-07-23 (first published: 2014-02-10)
10,061 reads
SQL Server 2012 AlwaysOn provides flexible design choices for selecting an appropriate high availability and disaster recovery solution for your application. This article looks at the common design patterns.
2013-12-11
5,345 reads
This article discusses failover cluster instances and AlwaysOn groups
2012-09-04
12,094 reads