Conclusiones sobre el Agente SQL Server

Este mes concluiremos nuestra miniserie sobre el agente SQL Server mostrando cómo crear secuencias de comandos que esperen a la terminación de una tarea (o un  paso intermedio) y ejecute después el informe sobre el estado del servicio del Agente SQL Server. Pero primero, vamos a revisar rápidamente los informes de trabajo del Agente para discutir la manera más eficiente de filtrar un gran número de registros históricos.

Filtrado de un gran número de registros

En el artículo del mes pasado "Informes con el Agente de Tareas" le mostramos cómo crear sencillos informes sobre el estado de ejecución de los trabajos con Windows PowerShell y los objetos de administración de SQL Server (SMO). Para filtrar los registros históricos, utilice el método EnumJobHistory() para rellenar completamente una colección y transmitirla a través de la canalización de PowerShell al cmdlet Where-Object, que filtra los registros de la colección.
Aunque se trata de una forma muy directa de filtrado de PowerShell, puede no ser lo suficientemente eficiente en casos en que la tabla Historial contiene un gran número de registros. Las empresas podrían tener numerosos registros porque nunca hayan ajustado la configuración del histórico o necesiten mantener un montón de registros para fines de auditoría interna. Sea cual sea la razón, en estos casos, es mucho más eficiente declarar por adelantado un filtro que se puede pasar al método EnumJobHistory(). De esta forma, SMO aplica las condiciones de consulta entre bastidores en lugar de crear una colección que contenga todos los registros.
Por ejemplo, supongamos que desea un informe detallando sólo  de aquellos trabajos que no se pudieron ejecutar en las últimas 24 horas. El Listado 1 muestra cómo puede crear el filtro por adelantado y luego pasarlo al método EnumJobHistory().

Esperando la terminación de un trabajo

Listado 1

Cuando se trata del de agente SQL Server, puede tener que esperar a que un trabajo finalice la ejecución. En T-SQL, esto puede hacerse mediante una llamada al procedimiento almacenado del sistema sp_help_job dentro de  un bucle y esperar entre cada llamada. En PowerShell, SMO puede utilizarse de forma similar. Antes de ver el código de PowerShell, sin embargo, vamos a crear un simple trabajo en un solo paso con un comando T-SQL que espera 1 minuto, como sigue:

Este será el trabajo por el que hay que esperar. El código completo de T-SQL para crear este trabajo está disponible en  el script  Script 1 - WaitFor.sql.

Para esperar a que el trabajo finalice su ejecución, puede utilizar la propiedad CurrentRunStatus del objeto Job en un bucle While y comprobar si el valor de la propiedad es "Idle", como en el código del Listado 2.

Dentro del bucle, llame al cmdlet Start-Sleep, para detener la secuencia de comandos durante  un período especificado de tiempo entre llamadas, como se muestra en el siguiente código:

El cmdlet Start-Sleep impide el alto consumo de CPU causado por un bucle "estrecho" que recupera el valor de la propiedad tan rápido como pueda. Por otra parte, dentro del bucle, debe llamar al método de Refresh() del objeto Job para actualizar la propiedad Current-RunStatus, como se muestra en el siguiente fragmento de código:

Listado 2

En muchos casos de la vida real, también necesita una forma de terminar la secuencia de comandos después de cierto tiempo, incluso si aún se está ejecutando el trabajo. Se puede traducir fácilmente el período de tiempo en un número de iteraciones que espere durante el mismo  período.

Listado 3

El Listado 3 muestra la solución completa. Esta solución utiliza la función Get-SQLInstance para inicializar y conectarse a una instancia de SQL Server. Introdujimos esta función en nuestro artículo "Automatización del proceso de migración de bases de datos" (febrero de 2011).La función Get-SQLInstance es compatible con métodos de autenticación de Windows y SQL Server. Requiere sólo un parámetro: el nombre de instancia. Puede encontrar el código completo de PowerShell en la secuencia de comandos Script 2 - Waiting For Job Step.ps1.

Tenga en cuenta que si no se está ejecutando el trabajo "WaitFor" cuando esta secuencia de comandos se ejecuta (es decir, el estado del trabajo es "Idle"), la secuencia de comandos se completa inmediatamente. Esto puede no ser el objetivo deseado. Deberá cambiar la secuencia de comandos para satisfacer sus necesidades.

Si usted ejecuta primero el trabajo y el script de PowerShell inmediatamente después, el script terminará después de un período de 20 segundos, informando de que no se terminó el trabajo en el tiempo especificado. Si se aumenta el tiempo de espera total a más de 60 segundos aumentando el número o la longitud de las iteraciones, la secuencia de comandos terminará después del tiempo asignado, informando de que ha terminado el trabajo.

Si no desea iniciar manualmente el trabajo utilizando SQL Server Management Studio (SSMS), puede agregar el código en el listado 4 a la secuencia de comandos de PowerShell justo antes del bucle While. Después de que este código inicie el trabajo, debe esperar hasta que el estado de ejecución cambie de "Idle" a "Executing" antes de continuar. En condiciones normales, tarda mucho menos de un segundo, y por eso el código duerme durante sólo 10 milisegundos entre cada solicitud.

Listado 4

Esperando la terminación de un paso individual

En algunos casos, quizá deba esperar a que finalice la ejecución de un único paso de la tarea. A partir de SQL Server 2008, el Agente SQL Server admite pasos de trabajo de tipo PowerShell Script, por lo que puede aprovechar el código anterior con algunos cambios menores.

Como ya comentamos en "Logging con el Agente de SQL Server" (abril de 2011), éste admite tokens de agente. Dentro del código de cada paso del trabajo, se traducen en marcadores de posición de valores del entorno que son sustituidos dinámicamente antes de la ejecución. Por lo que, en lugar de utilizar la función Get-SQLInstance, simplemente puede conectarse utilizando la autenticación de Windows, y pasando el nombre del servidor actual a través de un token de agente, como el siguiente fragmento de código:

Otra cosa que necesita cambiar es cómo termina la secuencia de comandos. En la mayoría de los casos, probablemente desee  un informe de estado del tipo  "Éxito/Fracaso" para la ejecución del paso si el trabajo al que está esperando no termina en el período de tiempo especificado. Esto puede hacerse mediante una excepción, como se muestra en el listado 5. Puede encontrar el código completo de T-SQL para crear el trabajo en el Script - 3 - Waiting For Job.sql.

Obtener el estado del servicio del Agente de SQL Server

Otra tarea administrativa común es comprobar el estado del servicio del Agente de SQL Server. Esto puede hacerse mediante el cmdlet Get-Service y especificando un filtro, como el siguiente fragmento de código se muestra:

La figura 1 muestra un ejemplo de la salida cuando se ejecuta en una máquina con cuatro instancias de SQL Server.
Listado 5

Listado 6

 Listado 7

 
Sin embargo, esto pasa sólo el nombre del archivo; todavía tenemos que leer del archivo los nombres de los servidores. Esto puede hacerse mediante el cmdlet Get-Content para crear una colección de nombres, como en el ejemplo siguiente:

Hay algo que no hemos cubierto aún. ¿Qué sucede si se pasa un servidor inexistente al cmdlet Get-Service o uno de los servidores no está accesible? El cmdlet produce una excepción, que se debe controlar. Aún mejor, puede utilizar una función genérica llamada Get-AgentState que contiene la lógica de filtrado y el control de excepciones, como se muestra en el Listado 8. Una vez que tenga la función genérica, simplemente puede transmitir la colección de nombres de servidor leída del archivo de texto, de la siguiente forma:

Listado 8 

 

Figura 4: Resultados de utilizar el script Get-AgentState.ps1

Listado 9

Listado 10

También puede utilizar la secuencia de comandos Get-AgentState.ps1 directamente, pasando el nombre del archivo de texto que contiene los nombres de servidor. La figura 4 muestra los resultados.

Tenga en cuenta que P839 y P840 son nodos físicos en un clúster de SQL Server bidireccional, activo-activo. La figura 4 muestra ambas instancias virtuales de SQL Server; así, ambos servicios del Agente de SQL Server se están ejecutando en el mismo nodo físico (P839). Puede encontrar el código completo de PowerShell en el Script 4: Get-AgentState.ps1.

Si prefiere que la colección resultante incluya los servidores con los que no se pudo conectar, puede sustituir la llamada al cmdlet Write-Warning en la Directiva de capturas con el código del listado 9.

De lo contrario, puede cambiar la función Get-AgentState para construir la colección resultante, como se ve en el listado 10.

Conclusión

En este artículo, se cubren algunas de las técnicas más útiles cuando se trabaja con el Agente de SQL Server. Debido a su papel central en la automatización de tareas administrativas, probablemente ofreceremos más ejemplos en el futuro. Hasta entonces, nos encantaría saber de usted, especialmente sobre los temas que le gustaría que tratásemos a continuación. Envíenos un correo electrónico a TheJournal@solidq.com.