Azure Elastic Jobs para bases de datos SQL

¿Qué es un Elastic Job?

Aunque la definición de un Elastic Job es bastante sencilla en la documentación de Microsoft, siempre me gusta leer primero un ejemplo de uso de una tecnología o herramienta – suele hacer que el momento «a-ha» llegue antes. Conocí Elastic Jobs inicialmente cuando en mi proyecto diario, muy centrado en Azure, tuve que programar la ejecución de un procedimiento almacenado en una base de datos SQL, que se ejecutaba en un Azure SQL Server, a diario por motivos de mantenimiento.

Sin embargo, ese día en particular, descubrí el límite de tiempo de espera del conector SQL, utilizado por Logic Apps: sólo dos minutos. Si el tiempo de ejecución del procedimiento almacenado supera los 120 segundos, es una respuesta 504 Timeout. Mi procedimiento almacenado en particular estaba funcionando muy por encima del límite, tardando una media de cuatro horas en ejecutarse. Leyendo sobre posibles soluciones, se sugirieron las siguientes recomendaciones:

• Utiliza sólo los triggers de la base de datos – En general, soy de los que desaconsejan el uso de triggers, incluso para la integridad de los datos. En mi experiencia, al final todo el mundo se olvida de los triggers y redescubrirlos suele ser un camino largo y doloroso, lleno de dolores de cabeza. Además, los desencadenantes no iban a funcionar con mi problema de ejecutar consultas de mantenimiento a una hora determinada, diariamente – no podía vincular la ejecución a eventos de la base de datos, como inserciones, actualizaciones o eliminaciones (desencadenantes DML), ni a sentencias de creación/alteración/eliminación (desencadenantes DDL), ni a eventos de inicio de sesión (desencadenantes LOGON).
• Consultas nativas de paso de SQL
• Tablas de estado
• Trabajos server-side

Después de señalar las soluciones, la documentación entra en detalle sobre el propósito de los Elastic Jobs – proporcionan una posibilidad de ejecutar ‘asincrónicamente’ un procedimiento almacenado a través de un agente de trabajo. Los trabajos son consultas ejecutadas periódicamente, o tareas de mantenimiento que pueden ejecutarse sobre una colección de bases de datos. Las entradas y salidas pueden ser almacenadas en una tabla de estado, lo cual es una característica notable, sin embargo, innecesaria para mi caso de uso, ya que mi tarea en cuestión era sólo ejecutar un simple «exec stored_proc», sin parámetros, sin necesidad de rastrear las salidas (aparte de vigilar las ejecuciones fallidas).

Al final, decidí dar una oportunidad a este enfoque, aunque sólo sea como prueba de concepto, ya que el recurso Elastic Job todavía está disponible sólo como una función de vista previa en Azure.

Configuración del trabajo elástico

El primer paso en la creación del Elastic Job, es especificar una base de datos en la que el recurso generará un esquema de trabajos y jobs_internal, así como unas cuantas tablas (ej. jobs, job_steps, job_executions, job_cancellations, etc), vistas y procedimientos almacenados (ej. sp_add_job, sp_add_jobstep, sp_start_job, etc). Para mantener las cosas bien separadas de mi proyecto, decidí crear una nueva base de datos, puramente para el Elastic Job – como nota al margen, tuvo que ser configurado en al menos el nivel S0, por lo tanto mi costo mensual fue ~ $ 12. Aparte del costo de la base de datos, no se aplicaron cargos adicionales a mi cuenta:

El segundo paso es ya la revisión y finalización del resumen. Después de asegurarse de que todo es correcto, la base de datos se rellena, sin embargo, no hemos terminado con nuestra configuración – algunas declaraciones tendrán que ser ejecutadas para terminar nuestra configuración.

Una de las partes más confusas de la documentación era entender qué sentencia debe ejecutarse en cada base de datos. A continuación, intentaré simplificar mi experiencia con esto, mediante las siguientes indicaciones:

• Servidor original, base de datos: El SQL Server «de destino», la base de datos en él, donde se encuentra nuestro procedimiento almacenado original.

• Servidor original, master: El SQL Server ‘objetivo’, nuestro servidor original en el que reside una base de datos, en cuya base de datos tenemos el procedimiento almacenado que queremos ejecutar – sin embargo, en lugar de conectarnos a esa base de datos dentro del servidor, esta es la base de datos master.

• Servidor Elastic Jobs, base de datos: El servidor SQL Server y la base de datos recién creados, donde acabamos de configurar el agente de Elastic Job a través del Azure Portal.

• Servidor Elastic Jobs, master: El recién creado SQL Server, donde acabamos de configurar el agente de Elastic Job a través del Portal de Azure, sin embargo, se conecta de nuevo a la base de datos maestra, en lugar de la base de datos real.

Necesitaremos crear credenciales, un login y un usuario para la ejecución del job. Conectando a la base de datos recién creada para el Elastic Job, hay que ejecutar las siguientes sentencias, en un orden establecido:

Servidor Elastic Jobs, base de datos

create master key encryption by password = 'StrongPassword123!'
go

create database scoped credential JobExecution with identity = 'UserForJob',
secret = 'StrongPassword123!'
go

Se observa que el mismo conjunto de credenciales debe ser utilizado en la base de datos de destino, para un create-login y create-user-from-login, asegurando que realmente podemos conectarnos a nuestra base de datos original donde tendremos que ejecutar el procedimiento almacenado – dentro de master de la base de datos original, el siguiente comando tiene que ser ejecutado:

Servidor original, master

create login UserForJobLogin with password = 'StrongPassword123!'

Crearemos un usuario a partir de este login tanto para el master como para la base de datos real:

Servidor original, master

create user UserForJob for login UserForJobLogin with default_schema = dbo

Servidor original, base de datos

exec sp_addrolemember 'db_owner', 'jobuser'

Volviendo a la base de datos recién creada para el agente de trabajo, conectándonos a la base de datos real (en lugar de a la maestra), nuestro siguiente paso será crear un grupo en el que almacenaremos las bases de datos en las que deberá ejecutarse nuestro procedimiento almacenado original.

Se puede crear un grupo simplemente ejecutando el siguiente procedimiento almacenado:

Servidor Elastic Jobs, base de datos

exec jobs.sp_add_target_group 'MyTargetGroup'

Ahora, necesitamos añadir miembros a este grupo – en nuestro caso será un miembro, el servidor SQL, donde necesitamos ejecutar nuestro procedimiento almacenado original en una de sus bases de datos:

Servidor Elastic Jobs, base de datos

exec jobs.sp_add_target_group_member
'MyTargetGroup',
@target_type = N'SqlServer',
@refresh_credential_name = 'JobExecution',
@server_name ='tcp:my-original-database.windows.net,1433'

Si efectivamente lo logramos, al consultar las tablas jobs.target_groups y jobs.target_group_members, deberíamos ver los siguientes datos (por supuesto, los GUIDs serán diferentes):

Ahora que tenemos un grupo en el que colocar nuestro trabajo, podemos seguir adelante y crear primero un trabajo, y un simple paso (que crea una tabla si no existe en la base de datos de destino, y luego inserta una sola fila en ella):

Servidor Elastic Jobs, base de datos

exec jobs.sp_add_job
@job_name ='MyFirstJob',
@description ='For demo purposes'
go

exec jobs.sp_add_jobstep
@job_name = 'MyFirstJob',
@credential_name = 'JobExecution',
@target_group_name = 'MyTargetGroup',
@command = 'IF NOT EXISTS (SELECT name FROM sys.tables WHERE name =''SampleTable'')
BEGIN
CREATE TABLE ElasticJob
(
    [Id] INT IDENTITY,
    [Timestamp] DateTime
)
END
INSERT INTO ElasticJob ([Timestamp]) values (GETUTCDATE())'

De nuevo, para asegurarnos de que todo funciona como se espera, podemos consultar desde jobs.jobs y jobs.jobsteps. Si ambas tablas están llenas, podemos intentar activar el trabajo manualmente a través de:

Servidor Elastic Jobs, base de datos

exec jobs.sp_start_job 'MyFirstJob'

Si en la base de datos de destino no vemos la nueva tabla, o la fila insertada, podemos depurar el problema ejecutando la siguiente consulta (prestando mucha atención a la columna ‘last_message’):

Servidor Elastic Jobs, base de datos

select * from jobs.job_executions order by create_time desc

Con esto, hemos creado el Elastic Job y estamos listos para configurar nuestra Logic App, que activará el trabajo en un horario.

Crear una Logic App

Aprovisionar una Logic App es un proceso bastante sencillo, y no requiere ninguna necesidad adicional o específica de Elastic Jobs. Una vez creado el recurso, podemos configurar el trigger – de nuevo, nada único en este paso, aunque para mi caso, configuré un trigger de Recurrencia, configurándolo para una programación nocturna, todos los días.

El siguiente paso, sin embargo, es donde comienza la diversión: empezaremos con una consulta SQL de ejecución V2, que ejecuta el procedimiento almacenado anteriormente anotado de jobs.sp_start_job:

Servidor Elastic Jobs, base de datos

Si quisiéramos capturar la salida/resultado de la ejecución del paso anterior, creando una tabla de estado en el Servidor original, base de datos y luego anexando un paso For Each con Insert row V2 en la Logic App podríamos almacenar los detalles de la ejecución:

Sumario

Por supuesto, este era un caso de uso muy simple, que sólo tocaba la punta del iceberg de las capacidades de Elastic Job, pero para mi caso de uso, era una solución sencilla y elegante que tardó medio día en configurarse.

Naturalmente, a la hora de aplicar la solución en un escenario real, hay que tener en cuenta algunos aspectos adicionales:

• En la demo, el usuario que creamos en el SQL Server es un db_owner, tanto en la base de datos maestra, como en la nuestra, lo que está otorgando mucha más autorización al usuario de la necesaria.
• No configuramos ninguna alerta – si la Logic App falla su ejecución algún día, es una buena idea configurar una alerta por correo electrónico o mensaje de texto. Para esto, una simple Regla de Alerta, vigilando el fallo de la Logic App es una forma fácil y eficiente de hacerlo, ya que hasta ahora, la ejecución de los trabajos del Elastic Job son visibles bajo el propio recurso, pero ninguna alerta puede estar ligada a una métrica de «Estado» (al menos en este momento, en marzo de 2022):

Referencias

• https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-overview
• https://docs.microsoft.com/en-us/azure/logic-apps/handle-long-running-stored-procedures-sql-connector
• https://docs.microsoft.com/en-us/azure/azure-sql/database/job-automation-overview
• https://www.sqlshack.com/elastic-jobs-in-azure-sql-database/
• https://techcommunity.microsoft.com/t5/integrations-on-azure-blog/long-running-sql-stored-procedures-in-logicapps/ba-p/1800712