Activación y desactivación de alertas SQL Server

Hola a todos, hoy vamos a hablaros sobre la activación y desactivación de alertas SQL Server. Este mecanismo tan útil de SQL Server en todas las versiones que tienen dbMail habilitado y SQL Server Agent.

No vamos a entrar en detalle en cómo crear alertas, sino en cómo controlarlas para poder activar y desactivarlas según necesitemos. Como norma general, estas alertas, especialmente en entornos AlwaysOn están siempre activas y cuando hay algún cambio de nodo, una caída de WSFC o cualquier otro problema que estemos controlando, avisen y lleguen decenas de correos a varios buzones.

Es bueno que estén activas y salten cuando hay un failover no controlado, pero en caso de ser un failover controlado por alguna intervención, actualización, etc, deshabilitarlas manualmente es muy tedioso y no siempre viable, ya que no podemos seleccionar todas y desactivarlas. En esta ocasión optaremos por dos jobs, uno para activarlas, y otro para desactivarlas.

De esta forma, una vez que tengamos planificada alguna intervención podamos desactivarlas y una vez acabado todo el proceso, poder activarlas.

¿Qué necesitamos para la activación y desactivación de alertas en SQL Server?

En primer lugar, necesitaremos las propias alertas ya creadas, dbMail configurado para que envíe correo, y dos jobs de SQL Server Agent, uno para activar todas, y otro para desactivar todas. Este último paso de los jobs es el único que faltaría, ya que al tener un sistema de alertas implementado, el resto debería estar ya montando y funcionando.

Como hemos pensado en todo y cabe la posibilidad de que haya previamente alertas desactivadas por algún motivo, usaremos adicionalmente una tabla de control, para ver los jobs que había habilitados previos al failover y reactivar solamente esos.

Creación de la tabla

En este ejemplo usaremos msdb, pero lo recomendable sería usar una BBDD propia con nuestras herramientas y scripts. Utilizaremos el siguiente script para crearla:

USE msdb;
GO

IF OBJECT_ID('dbo.Alertas_Desactivadas_Failover', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.Alertas_Desactivadas_Failover
    (
        id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        execution_id UNIQUEIDENTIFIER NOT NULL,
        alert_id INT NOT NULL,
        alert_name SYSNAME NOT NULL,
        was_enabled BIT NOT NULL,
        fecha_desactivacion DATETIME NOT NULL,
        fecha_reactivacion DATETIME NULL,
        login_name SYSNAME NULL,
        host_name SYSNAME NULL
    );
END;
GO

Creación del job para desactivar todas las alertas antes del failover

USE msdb;
GO

IF EXISTS (
    SELECT 1 
    FROM msdb.dbo.sysjobs 
    WHERE name = N'DBA - Desactivar alertas failover'
)
BEGIN
    EXEC msdb.dbo.sp_delete_job 
        @job_name = N'DBA - Desactivar alertas failover';
END;
GO

EXEC msdb.dbo.sp_add_job
    @job_name = N'DBA - Desactivar alertas failover',
    @enabled = 1,
    @description = N'Desactiva temporalmente todas las alertas activas de SQL Server Agent antes de un failover controlado.';
GO

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'DBA - Desactivar alertas failover',
    @step_name = N'Guardar estado y desactivar alertas',
    @subsystem = N'TSQL',
    @database_name = N'msdb',
    @command = N'
SET NOCOUNT ON;

DECLARE @execution_id UNIQUEIDENTIFIER = NEWID();
DECLARE @alert_name SYSNAME;

IF EXISTS (
    SELECT 1
    FROM msdb.dbo.Alertas_Desactivadas_Failover
    WHERE fecha_reactivacion IS NULL
)
BEGIN
    RAISERROR(''Ya existe una ejecución pendiente de reactivación. Ejecuta primero el job de reactivación.'', 16, 1);
    RETURN;
END;

INSERT INTO msdb.dbo.Alertas_Desactivadas_Failover
(
    execution_id,
    alert_id,
    alert_name,
    was_enabled,
    fecha_desactivacion,
    login_name,
    host_name
)
SELECT
    @execution_id,
    id,
    name,
    enabled,
    GETDATE(),
    SUSER_SNAME(),
    HOST_NAME()
FROM msdb.dbo.sysalerts
WHERE enabled = 1;

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
SELECT alert_name
FROM msdb.dbo.Alertas_Desactivadas_Failover
WHERE execution_id = @execution_id
  AND was_enabled = 1;

OPEN c;
FETCH NEXT FROM c INTO @alert_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC msdb.dbo.sp_update_alert
        @name = @alert_name,
        @enabled = 0;

    PRINT ''Alerta desactivada: '' + @alert_name;

    FETCH NEXT FROM c INTO @alert_name;
END;

CLOSE c;
DEALLOCATE c;

PRINT ''Execution ID: '' + CONVERT(VARCHAR(36), @execution_id);
';
GO

EXEC msdb.dbo.sp_add_jobserver
    @job_name = N'DBA - Desactivar alertas failover';
GO

Creación del job para reactivar todas las alertas después del failover

Creamos el job responsable de reactivas todas las alertas que estaban previamente activas unicamente:

USE msdb;
GO

IF EXISTS (
    SELECT 1 
    FROM msdb.dbo.sysjobs 
    WHERE name = N'DBA - Reactivar alertas failover'
)
BEGIN
    EXEC msdb.dbo.sp_delete_job 
        @job_name = N'DBA - Reactivar alertas failover';
END;
GO

EXEC msdb.dbo.sp_add_job
    @job_name = N'DBA - Reactivar alertas failover',
    @enabled = 1,
    @description = N'Reactiva solo las alertas que estaban activas antes del failover controlado.';
GO

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'DBA - Reactivar alertas failover',
    @step_name = N'Reactivar alertas guardadas',
    @subsystem = N'TSQL',
    @database_name = N'msdb',
    @command = N'
SET NOCOUNT ON;

DECLARE @execution_id UNIQUEIDENTIFIER;
DECLARE @alert_name SYSNAME;

SELECT TOP (1)
    @execution_id = execution_id
FROM msdb.dbo.Alertas_Desactivadas_Failover
WHERE fecha_reactivacion IS NULL
ORDER BY fecha_desactivacion DESC;

IF @execution_id IS NULL
BEGIN
    PRINT ''No hay alertas pendientes de reactivar.'';
    RETURN;
END;

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
SELECT alert_name
FROM msdb.dbo.Alertas_Desactivadas_Failover
WHERE execution_id = @execution_id
  AND was_enabled = 1
  AND fecha_reactivacion IS NULL;

OPEN c;
FETCH NEXT FROM c INTO @alert_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF EXISTS (
        SELECT 1
        FROM msdb.dbo.sysalerts
        WHERE name = @alert_name
    )
    BEGIN
        EXEC msdb.dbo.sp_update_alert
            @name = @alert_name,
            @enabled = 1;

        PRINT ''Alerta reactivada: '' + @alert_name;
    END
    ELSE
    BEGIN
        PRINT ''La alerta ya no existe: '' + @alert_name;
    END;

    FETCH NEXT FROM c INTO @alert_name;
END;

CLOSE c;
DEALLOCATE c;

UPDATE msdb.dbo.Alertas_Desactivadas_Failover
SET fecha_reactivacion = GETDATE()
WHERE execution_id = @execution_id
  AND fecha_reactivacion IS NULL;

PRINT ''Execution ID reactivado: '' + CONVERT(VARCHAR(36), @execution_id);
';
GO

EXEC msdb.dbo.sp_add_jobserver
    @job_name = N'DBA - Reactivar alertas failover';
GO

Uso

Antes del failover:

EXEC msdb.dbo.sp_start_job 
    @job_name = N'DBA - Desactivar alertas failover';

Después del failover:

EXEC msdb.dbo.sp_start_job 
    @job_name = N'DBA - Reactivar alertas failover';

Comprobación

SELECT 
    name,
    enabled
FROM msdb.dbo.sysalerts
ORDER BY name;

Y para ver el histórico:

SELECT *
FROM msdb.dbo.Alertas_Desactivadas_Failover
ORDER BY fecha_desactivacion DESC;

Esto habría que replicarlo en cada nodo del AG, o si es un solo servidor, ya tendríamos suficiente. Si no quieres perderte estos trucos no dudes en suscribirte a nuestra newsletter. Con un solo email al mes estarás informado de nuestras publicaciones.

Si prefieres que lo hagamos nosotros, no dudes en contactar con nosotros sin compromiso.

¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle. Más información en su página de LinkedIn.

Sígue a GPS en LinkedIn