Мониторинг AlwaysOn в MS SQL Server 2012

  Начиная с MS SQL Server 2012 появилась очень хорошая функция отказоустойчивости AlwaysOn. Функция-то функция хорошая, но вот с мониторингом плоховато в MS SQL Server. Да и в последнее время ответственность за мониторинг полностью ложится на администратора СУБД. Если раньше для зеркалирования был хоть какой-то джоб и на его можно повесить операторов СУБД, то для AlwaysOn такого задания нет.

Ниже написан запрос мониторинга AlwaysOn:

DECLARE @state VARCHAR(30) 
DECLARE @P varchar(50)     --просто переменная для формирования текста письма
DECLARE @String VARCHAR(max)  -- текст письма
DECLARE @Groups TABLE (Gr varchar(50), synchronization_health_desc VARCHAR(30))
DECLARE @databases TABLE (DB varchar(100), synchronization_health_desc VARCHAR(30), replica varchar(30))
declare @Subj varchar(100)
declare @prreplica varchar(100) =@@servername

--проверка, является ли данный сервер первичный для групп
--т.к на вторичных репликах состоянии не HEALTH
if @@SERVERNAME  in (select primary_replica from
                                                  sys.dm_hadr_availability_group_states  where primary_replica= @@SERVERNAME)

begin
  insert into @Groups
   select g.name,s.synchronization_health_desc from sys.dm_hadr_availability_group_states s
   inner join sys.availability_groups g on s.group_id=s.group_id
        where s.synchronization_health_desc<>'HEALTHY'
         and s.primary_replica= @@SERVERNAME

end

--проверка состояния БД
insert into @databases
select d.name,s.synchronization_state_desc,r.replica_server_name from sys.dm_hadr_database_replica_states s
  inner join sys.sysdatabases d on s.database_id=d.dbid
  inner join sys.availability_replicas r on s.replica_id=r.replica_id
            where s.synchronization_health_desc<>'HEALTHY'
                           and d.dbid>4

--формирование текста письма
Set @String=''
if exists(select 1 from @Groups)
  while exists(select top 1 gr from @Groups)
  begin
     select top 1 @p=Gr,@state=synchronization_health_desc from @Groups
     set @String=@String+ 'Проблема с AlwaysOn на сервере '+@@SERVERNAME+'.Группа доступности '+@p+'     находится в состоянии '+@state+'.'+CHAR(13)
     delete from @Groups where gr=@p
  end

if exists(select 1 from @databases)
  while exists(select top 1 db from @databases)
  begin
      select top 1 @p=DB,@state=synchronization_health_desc from @databases
      set @String=@String+ 'Проблема с AlwaysOn на сервере '+@@SERVERNAME+'.База данных '+@p+' находится в состоянии '+@state+'.'+CHAR(13)
      delete from @databases where DB=@p
  end


-- отсылка письма
if  len(@string)>1
begin
set @Subj='Проблема с AlwaysOn на сервере '+@@SERVERNAME
  EXEC msdb.dbo.sp_send_dbmail 'mail_profile', 'dba_admins@mail.ru;', @body = @string, @subject = @Subj
end

Запихиваем данный запрос в задание MS SQL агента, ставим расписание и запускаем.

Что он делает:
Он проверят состояние групп доступности и состоянии баз данных(сделано одновременно, т.к может быть несколько групп доступности) и если у них состоянии не HEALTH, то идет формирование письма и отсылка на указанные адреса.

Буду рад, если данный запрос Вам помог!
Удачи!

08072014 upd: Обновлен скрипт с учетом более 1 групп доступности и на серверах с различными ролями серверов.