Notificação em caso de failover ou restart do SQL Server

Olá pessoal tudo certo?

No post de hoje vou demonstrar como criar uma notificação de quando a sua instância for reiniciada ou acontecer um faillover.

Quando administramos muitas instâncias, pode ocorrer alguns eventos como um restart de uma instância ou um failover e acabar passando despercebido, por isso, toda vez que ocorre um destes eventos eu recebo uma notificação por email para verificar o que aconteceu.

Para criar este alerta eu uso a sys.dm_server_services, para mais informações sobre (https://msdn.microsoft.com/pt-br/library/hh204542%28v=sql.120%29.aspx).

SELECT servicename, startup_type_desc, status_desc, last_startup_time
FROM sys.dm_server_services

A notificação do evento é realizada através um job que é executado toda vez que o SQL Agent for iniciado, ele vai enviar um e-mail notificando que houve um failover ou que a instância foi reiniciada.

DECLARE
    @msg NVARCHAR(MAX),
    @instancia VARCHAR(100),
    @assunto VARCHAR(100);

SELECT
    @instancia = @@SERVERNAME;
SET @assunto = 'A instancia ' + @instancia + ' Foi reiniciada';
SET @msg = '<p> ' + @instancia + ' A Instancia foi reniciada.</p>';

SET @msg = @msg + '<table border="2" cellspacing="2" cellpadding="2">';

SET @msg = @msg + '<tbody align="left" style="font-family:Arial; font-size: 11;" 
                   <TR><TH>Service Name</TH><TH>Startup Type</TH><TH>Status</TH>
                   <TH>Startup Time</TH></TR></tbody>';

SELECT
    @msg = @msg + '<tbody align="left" style="font-family:Arial;
                    font-size: 11;" <TR><TD>' + servicename + '</TD><TD>' 
	           + startup_type_desc + '</TD><TD>' + status_desc 
                   + '</TD><TD>'+ CAST(last_startup_time AS VARCHAR(30))
                   + '</TD></TR></tbody>'
FROM
    sys.dm_server_services;


EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'MSSQLSERVER',  -- 
    @recipients = 'meu@email.com',
    @subject = @assunto, 
    @body = @msg, 
    @body_format = 'HTML';

Para criar o job basta expandir o SQL Agent

Na guia steps você deve colocar o script acima.

Na guia schedules deverá colocar “Start automatically when SQL Server Agent starts”

Pronto, quando acontecer um restart você vai receber um email como este.

Para criar um notificação em caso de failover, é um procedimento um pouco diferente, para isso precisamos criar uma tabela em um banco de dados, nela vamos armazenar os nós que fazem parte do nosso cluster, essa informação obtemos na função SERVERPROPERTY, ela nos mostra todas as informações da instância.

Vamos criar as tabelas e popular com os dados que são obtidos na função SERVERPROPERTY.

Como é a configuração inicial vamos colocar os valores Nm_Servidor_Ativo e Nm_Servidor_Passivo com o mesmo valor.

CREATE TABLE Alerta_Failover
(
Nm_Servidor_Ativo VARCHAR(50),
Nm_Servidor_Passivo VARCHAR(50)
)

INSERT INTO Alerta_Failover (Nm_Servidor_Ativo,Nm_Servidor_Passivo)
 VALUES (CONVERT(VARCHAR(100),SERVERPROPERTY('ComputerNamePhysicalNetBIOS')),
 (CONVERT(VARCHAR(100),SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))))

Após as tabelas criadas vamos criar um job, para envio do alerta em caso de failover, os passos para criar o job é o mesmo da instância stand alone, porém na parte de configurar o step você vai utilizar este script, ele vai nos notificar se houve um failover ou se apenas a instância foi reiniciada.

DECLARE @Nm_Servidor_Ativo VARCHAR(100);
DECLARE @Nm_Servidor_Passivo VARCHAR(100);
DECLARE @msg NVARCHAR(MAX);
DECLARE @instancia VARCHAR(100);
DECLARE @assunto VARCHAR(100);

SET @Nm_Servidor_Ativo = CONVERT(VARCHAR(100), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'));
SET @Nm_Servidor_Passivo = ( SELECT Nm_Servidor_Passivo FROM Alerta_Failover );
SELECT
    @instancia = @@SERVERNAME;


IF @Nm_Servidor_Ativo <> @Nm_Servidor_Passivo
    
	BEGIN


        UPDATE
            Alerta_Failover
        SET
            Nm_Servidor_Ativo = @Nm_Servidor_Ativo;

        SET @assunto = 'Atenção ocorreu um failover na instância ' + @instancia + '.';
        
		SET @msg = '<p> Um failover aconteceu na instância ' + @instancia + '</p>';
  
        SET @msg = @msg + '<table border="2" cellspacing="2" cellpadding="2">';
     
        SET @msg = @msg + '<tbody align="left" style="font-family:Arial; font-size: 11;"
		                   <TR><TH>Nm_Servidor_Ativo</TH><TH>Nm_Servidor_Passivo</TH></TR></tbody>';
		
        SET @msg = @msg + '<tbody align="left" style="font-family:Arial; font-size: 11;" <TR><TD>' + @Nm_Servidor_Ativo + 
		                  '</TD><TD>' + @Nm_Servidor_Passivo + '</TD></TR></tbody>';

        EXEC msdb.dbo.sp_send_dbmail 
			@profile_name = 'MSSQLSERVER', 
			@recipients = 'meu@email.com', 
			@subject = @assunto, 
			@body = @msg,
			@body_format = 'HTML';
        
		UPDATE
            Alerta_Failover
        SET
            Nm_Servidor_Passivo = @Nm_Servidor_Ativo;
	  
    END;


IF @Nm_Servidor_Ativo = @Nm_Servidor_Passivo  -- Restart
  
    DECLARE @msg_restart NVARCHAR(MAX);
	DECLARE @assunto_restart VARCHAR(100);

BEGIN


 
    SET @msg_restart = 'Atenção a instancia: ' + @instancia + ' foi reiniciada, mas não fez failover.';
    SET @assunto_restart = 'Atenção instância: ' + @instancia + ' Foi reiniciada.';
    EXEC msdb.dbo.sp_send_dbmail
		@profile_name = 'MSSQLSERVER', 
		@recipients = 'meu@email.com', 
		@subject = @assunto_restart, 
		@body = @msg_restart;

END;

E-mail de notificação em caso de failover.

E-mail de notificação em caso de restart;

Bom pessoal, por hoje é isso, espero que o alerta possa te ajudar a monitorar os eventos de failover e restart da sua instância e até a próxima.

Abraços,

Tiago Neves