En un entorno complejo con múltiples instancias de SQL Server gestionadas por un equipo de DBAs, puede resultar desafiante realizar un seguimiento de los cambios realizados en los trabajos de SQL Server Agent durante las implementaciones. Esto se vuelve aún más complicado cuando los trabajos son gestionados por diferentes equipos. La falta de visibilidad en los cambios de los trabajos puede generar frustración para los DBAs de guardia cuando surgen problemas y necesitan determinar qué sucedió y por qué.

Una solución a este problema es implementar una auditoría de cambios en los trabajos de SQL Server Agent. Si bien existen métodos existentes que utilizan tablas/vistas del sistema en la base de datos msdb, este artículo presenta un enfoque diferente que implica generar scripts de los trabajos en archivos SQL y comparar sus valores hash para detectar cambios.

La ventaja de este método es que permite recrear fácilmente el trabajo antes de la modificación y compararlo con el trabajo después de la modificación para identificar los cambios específicos realizados. Para generar un script de un trabajo de SQL, se puede utilizar la clase de objeto de administración de SQL Server y se puede aprovechar PowerShell para automatizar el proceso.

Aquí tienes un ejemplo de cómo generar un script de un trabajo de SQL y obtener su valor hash utilizando el módulo de PowerShell dbatools:

# Generar un script de un trabajo de SQL
import-module dbatools
$jobName = "Trabajo de prueba"
$filePath = "C:\Temp\Trabajo_de_prueba.sql"

if (Test-Path -Path $filePath) {
    Remove-Item -Path $filePath
}

Get-DbaAgentJob -SqlInstance localhost\sql2019 -Job $jobName | Export-DbaScript -FilePath $filePath -NoPrefix -Encoding ASCII

# Obtener el valor hash del archivo
Get-FileHash -Path $filePath

Una vez que se ejecuta el script, se mostrará el valor hash del archivo SQL. Al comparar este valor hash con el valor anterior, se puede determinar si el trabajo ha sido modificado.

Para implementar una solución funcional para monitorear múltiples trabajos en un servidor, se pueden considerar los siguientes puntos clave de diseño:

1. Crear tres tablas para almacenar la información recopilada: JobList_Current, JobList_History y JobList_Staging.
2. Crear tres carpetas en una unidad local: Staging, Curr y Hist.
3. Implementar una lógica de flujo de trabajo que implique eliminar archivos en la carpeta Staging, generar un script de cada trabajo en un archivo en la carpeta Staging, obtener el valor hash de cada archivo y comparar los valores hash para determinar si un trabajo es nuevo, actualizado o eliminado.
4. Mover los registros de JobList_Current a JobList_History para los trabajos actualizados o eliminados, y mover el trabajo de JobList_Staging a JobList_Current. Por último, enviar un informe al equipo de DBA sobre cualquier cambio en los trabajos.

El script completo de PowerShell para monitorear cambios en los trabajos y enviar notificaciones por correo electrónico se puede encontrar en el artículo original.

En resumen, implementar un marco de auditoría para los cambios en los trabajos de SQL Server Agent proporciona una forma centralizada de realizar un seguimiento e informar sobre cualquier modificación realizada en los trabajos. También permite restaurar fácilmente los trabajos en caso de errores o cambios no deseados. Este marco se puede ampliar para auditar otros objetos de SQL Server, como vistas, procedimientos almacenados y permisos de usuario.

Al implementar este marco de auditoría, los DBAs y los equipos pueden tener una mejor visibilidad de los cambios en los trabajos y asegurarse de que cualquier modificación se realice correctamente y esté documentada.

Última actualización del artículo: 2021-03-08