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,701 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,116 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,222 reads
