Cómo automatizar el monitoreo y las alertas en cambios de estado de SQL Server Agent

Vista general de SQL Server Agent

El principal propósito de SQL Server Agent es ejecutar tareas administrativas dentro de SQL Server, principalmente en acciones en demanda de usuarios. Es el servicio de Windows que corre continuamente en el fondo, pero almacena necesariamente información dentro de SQL Server en sí mismo, en la base de datos de sistema msdb.

SQL Server Agent consiste en tres principales componentes, los cuales proveen separadamente la funcionalidad núcleo: trabajos, alertas y operadores.

  • Los Trabajos son secuencias pre configuradas de pasos. Pueden ser programados para correr en un espacio de tiempo específico o momentos particulares como una sola acción. También, pueden ser monitoreados para rastrear su éxito o falla. Cuando un trabajo está corriendo, hay un subcomponente relacionado, los programas.
  • Las Alertas proveen información en particular de eventos que ocurren en SQL Server, y toman las acciones correspondientes, como enviar correos electrónicos automáticamente, o ejecutar un trabajo.
  • Los Operadores, como roles de usuario, mantienen el ecosistema de trabajo/alerta de SQL Server. Usualmente, los operadores son notificados en alertas y estados del servicio y los trabajos.

El principal enfoque de este artículo es el estado del servicio de SQL Server Agent, ya sea que esté corriendo o esté parado, lo cual puede ser determinado usando varias técnicas.

Revisando el estado de SQL Server Agent manualmente

Hay muchas maneras de determinar el estado de SQL Server Agent, manualmente o programáticamente:

  • En Windows Services: Ejecute Services.msc y busque la instancia SQL Server deseada:

Si el servicio está parado (como el primero en la imagen de arriba), esa información es vivible haciendo doble clic en el servicio particular, en el diálogo Properties:

  • En SQL Server Configuration Manager:

  • En SQL Server Management Studio – En Object Explorer, dentro de la instancia SQL Server deseada:

Si el servicio está parado, el icono aparecerá como este (en la versión 2016 de SSMS):

  • Con T-SQL – Use el script mostrado en la instancia deseada:
IF EXISTS (SELECT 1 FROM sysprocesses WHERE LEFT(program_name, 8) = 'SQLAgent')
PRINT 'SQL Server Agent is running.'
ELSE
PRINT 'SQL Server Agent stopped.';

El resultado de la consulta será uno de los mensajes fijados en la consulta, ya sea que el servicio de SQL Server Agent esté funcionando o no.

  • Con PowerShell:
Get-Service | where-object {$_.name -like '*SQL*AGENT*'}

Eso mostrará resultados como este:

Este script PowerShell lee todos los servicios que tienen cadenas „SQL“ y „AGENT“ en sus títulos y los lista con sus estados, pero, si el nombre exacto del servicio es conocido, use este script:

Get-Service -name <name_of_the_service>

Estos métodos se cerciorarán del estado del servicio pero no alertarán proactivamente al usuario ni proveerán un registro de cambios.

Para registrar el estado, una manera es crear un trabajo dentro de SQL Server Agent, que incluirá un script PowerShell especificado y generará un archivo de Texto con información acerca del estado del servicio cada vez cuando el servicio inicie o reinicie.

Creando un trabajo de registro de Estado en SQL Server Agent

Expanda el ícono de SQL Server Agent y la carpeta Jobs también. Haga clic derecho y elija la opción New Job:

En la pestaña General, ingrese el nombre del trabajo (en este caso, SQL Agent status change log), establezca el propietario (usuario con rol de Administrador), establezca Category a „Data Collector“ y, opcionalmente, provea una descripción del trabajo, como en la imagen abajo:

En la pestaña Steps, cree un paso haciendo clic en el botón New.

El diálogo Job Step Properties aparecerá. En la pestaña General, ingrese el nombre del paso (Status Change Notification), elija PowerShell para la opción Type, porque este paso ejecutará un script particular (abajo), establezca la opción Run as como se muestra (el trabajo correrá bajo el usuario por defecto).

Invoke-Sqlcmd "IF EXISTS(
SELECT 1
FROM MASTER.dbo.sysprocesses
WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
SELECT SERVERPROPERTY('ServerName') AS 'InstanceName', 'Running' AS 'SQLServerAgent Status'
END
ELSE
BEGIN
SELECT SERVERPROPERTY('ServerName') AS 'InstanceName', 'Stopped' AS 'SQLServerAgent Status'
END" | Out-File "d:\sql_agent_status.txt"

El PowerShell incluido recuperará el nombre de la instancia y el estado del servicio relacionado de SQL Server Agent, exportando los resultados a un archivo de Texto (cuya ruta puede ser modificada de acuerdo a las necesidades).

En la pestaña Advanced (diálogo Job Step Properties), establezca estas opciones como en la image de abajo:

El paso creado aparecerá en el diálogo luego, y declarado como un inicio:

En el diálogo Job Properties, pestaña Schedules, cree el nuevo programa:

En el siguiente diálogo, establezca la opción Schedule type como en la imagen abajo:

Esto particularmente significa que el trabajo se ejecutará cada vez que el servicio de SQL Server Agent se inicie (al inicio, si se reinicia, etc.). También, el programa se mostrará en el diálogo.

Después de la creación, el nuevo trabajo aparecerá en la lista de Trabajos:

Para probar el trabajo SQL Agent status change log, simplemente ejecútelo con un clic derecho y eligiendo la opción Start Job at Step. Después de un intento exitoso, el diálogo de confirmación aparecerá:

En este caso, el archivo de Texto generado debería aparecer en la ruta especificada, conteniendo la información acerca del estado del servicio. De otra manera, revise todos los pasos de nuevo, para identificar errores en la creación del trabajo.

La salida se ve así:

Como puede notarse, hasta ahora no hubo menciones de un monitoreo activo del estado de SQL Server Agent, sólo recuperación manual de información acerca del estado del servicio y ejecutar un trabajo en el evento específico.

Monitoreo continuo de cambios

ApexSQL Monitor es una herramienta de monitoreo de SQL Server, pero también tiene una característica para el monitoreo activo del estado del servicio de SQL Server Agent.

ApexSQL Monitor indica el estado actual del servicio de SQL Server Agent, rastrea los momentos exactos cuando este servicio cambió su estado y muestra esa información en múltiples vistas.

El monitoreo básico del estado de SQL Server Agent es resuelto usando un contador especial de desempeño, el cual es colocado en la vista Metrics, debajo del grupo SQL Server metrics:

Note que este contador no tiene ajustes para el umbral base, los niveles de umbral particulares y el periodo de alerta personalizado, porque ApexSQL Monitor sólo revisa su estado en un periodo definido (por defecto, es 15 segundos).

Un enfoque pasivo en determinar los cambios de estados es primero revisar y luego resolver las alertas relacionadas con los eventos de SQL Server Agent.

Vaya a la vista de Alertas, y busque el hilo de estado de SQL Server Agent:

La diferencia entre una métrica “ordinaria” y esta es que la escala de valores para el gráfico de estado de SQL Server Agent son descripciones de cambios de estados, en lugar de valores numéricos: Stopped, Other start pending, Other stop pending, Running y Other continue pending. En este gráfico particular, los eventos del servicio parado y corriendo en una manera continua son presentados.

Usted puede revisar todas las alertas relacionadas a esta métrica en la pestaña Details. El tiempo exacto de ocurrencia de cada evento puede ser visto en la columna Time raised, o posando el cursor sobre una parte específica del gráfico en sí mismo (marcado en la imagen de abajo).

Estas alertas pueden ser revisadas periódicamente, para determinar la frecuencia de cambios de estado.

Alertando proactivamente acerca de cambios de estado de SQL Server Agent

Junto con estos pasos básicos, los usuarios pueden configurar y aplicar algunas acciones de alerta específicas, usando notificaciones de correo electrónico, scripts SQL personalizados y acciones de alerta de comandos. Una vez configuradas, estas acciones puede notificar al usuario (y emular el envío de información al operador), revelar la posible razón de la parada del servicio, e iniciar/reiniciar el servicio programáticamente.

Configurando la notificación de correo electrónico

Para obtener notificaciones de correo electrónico acerca del cambio de estados, configure el perfil deseado de correo electrónico y aplíquelo a la métrica:

Las notificaciones de correo electrónico puede ser configuradas para enviar un correo electrónico de resumen en un rango de tiempo deseado, o cuando alguna alerta es gatillada. Para lograr esto, elija la segunda opción cuando esté configurando el perfil de correo electrónico, y enviará un correo electrónico cada vez que el estado del servicio cambie:

Iniciar/Reiniciar el Servicio de SQL Server Agent con PowerShell

Use este script PowerShell e incrústelo en la acción de alerta de comando personalizada, y revisará el estado del servicio en cada cambio, ya sea que esté parado o con un cambio pendiente (como se dijo antes):

powershell.exe "if ((Get-Service -name SQLSERVERAGENT).Status -eq 'Stopped'){Start-Service SQLSERVERAGENT}
if ((Get-Service -name SQLSERVERAGENT).Status -eq 'Running'){Restart-Service SQLSERVERAGENT}"

En lugar de SQLSERVERAGENT, otro nombre de servicio puede ser ingresado.

 

julio 6, 2017