Seguramente ya lo conoces, pero por si acaso, ahí va…
El resumen corto del post sería algo así como “Con Service Broker se pueden agenda la frecuencia de las interacciones del procesamiento de mensajes”
Con esta consideración, podría resultar bastante sencillo crear una infraestructura de Service Broker que dispara la conversación periódicamente cada XX segundos para ejecutar un stored procedure.Este es un escenario interesante para automatizar tareas de mantenimiento con SQL Server Express; como sabes, SQL Server Express no incluye SQL Agent, y normalmente estos procesos deben ejecutarse mediante tareas programadas de windows, otras herramientas de agendado, o incluso aplicaciones hechas para tal efecto; otro publico objetivo de este post serían clientes de MySQL que a la hora de migrar a SQL Server Express Edition se encuentran con que la funcionalidad de agendado no existe.Por otra parte, si estás trabajando con MSDE 2000, y no quieres migrar a SQL Server 2005-8 Express Edition por la ausencia de SQL Server Agent, este sería un empujón más para la migración 🙂

En este post, te mostraré cómo crear esta infraestructura para realizar copias de seguridad periódicamente.

Nota: parte del código está excesivamente simplificado porque quiero enfocarlo en cómo usar Service Broker para la tarea.

1: Creación de la tabla donde se monitorizarán los resultados.

create table monitor_backups(   bd sysname,   fecha datetime default (getdate()) );

2: Creación de la cola y dos servicios: un servicio para la primera ejecución y el otro para las iteraciones.

create queue cola_copias create service sb_inicio_copias      on queue cola_copias;  create service sb_proceso_copias      on queue cola_copias ([DEFAULT]) ;

3: Creación del stored procedure que se lanzará para cada iteración: explicaré más adelante el código:

alter procedure lanzar_backup  as      declare @conversationhandle uniqueidentifier      declare @message_type_name sysname     declare @dialog uniqueidentifier       waitfor (         receive top(1)             @message_type_name = message_type_name,               @dialog = conversation_handle                 from cola_copias     ), timeout 500          if (@message_type_name =              'http://schemas.microsoft.com/sql/servicebroker/dialogtimer')     begin             -- segundos en que se lanzará el siguiente backup         begin conversation timer (@dialog) timeout = 60;                  -- cuidado todas las copias van al mismo fichero         -- es decir se machaca en cada ejecución         -- codificalo adecuadamente para tu necesidad         begin try             backup database accountsdb to disk = 'c:tempaccountsdb.bak'             with init, format             -- registro de seguimiento             insert into monitor_backups (bd) select 'accountsdb'         end try         begin catch                 declare @s sysname = ERROR_MESSAGE()                 raiserror ( @s, 10, 1) with log;                     end catch     end go

Qué sucede en el procedimiento almacenado; aquí lo explico:

    if (@message_type_name =              'http://schemas.microsoft.com/sql/servicebroker/dialogtimer')

Si el mensaje es del tipo Dialog Timer, es el momento de ejecutar la operación de mantenimiento; en este caso, el tratamiento para otro tipo de mensajes lo he descartado porque no es el propósito de SB en este caso procesar mensajes, sino activarse para ejecutar la tarea de mantenimiento.

        -- segundos en que se lanzará el siguiente backup         begin conversation timer (@dialog) timeout = 60;

Primero se agenda la ejecución para la siguiente vez; en este caso, la próxima ejecución será dentro de 60 segundos; cámbialo a tu gusto o necesidades.

        -- cuidado todas las copias van al mismo fichero         -- es decir se machaca en cada ejecución         -- codificalo adecuadamente para tu necesidad         begin try             backup database accountsdb to disk = 'c:tempaccountsdb.bak'             with init, format             -- registro de seguimiento             insert into monitor_backups (bd) select 'accountsdb'         end try         begin catch                 declare @s sysname = ERROR_MESSAGE()                 raiserror ( @s, 10, 1) with log;                     end catch

algunas consideraciones para la ejecución de la tarea: fíjate meterlo en try/catch para poder capturar y auditar el error en algún sitio; en mi caso, el error lo envío al Error Log de SQL Server; fíjate en la siguiente imagen cómo aparece en el error log

NewBitmapImage_22_6D6A5907

 

además, recuerda cambiar el código de la sentencia backup porque en este ejemplo, el backup se “re-escribe” continuamente; además fíjate que se ejecuta cada 60 segundos… como propósito didáctico está bien, nada más 🙂

4: Modificar el comportamiento de la cola para que se active el procedimiento almacenado:

alter queue cola_copias with activation (   status = on, procedure_name = lanzar_backup,   max_queue_readers = 1, execute as self)

5: Disparar la primera ejecución:

-- primer lanzamiento declare @conversationhandle uniqueidentifier  begin dialog conversation @conversationhandle   from service sb_inicio_copias   to service 'sb_proceso_copias'; begin conversation timer (@conversationhandle)    timeout = 60;

Nota final: el siguiente código:

select conversation_handle, state_desc, far_service, dialog_timer      from sys.conversation_endpoints; select * from monitor_backups;

la columna dialog_timer, indica cuando se activará la siguiente vez; la otra tablita muestra la bitacora de copias realizadas.

Conclusión: hemos usado Service Broker para agendar operaciones de mantenimiento sin tener que usar SQL Server Agent; para escenarios de agendado complejos, apóyate en tablas de configuración que mantengan frecuencias de ejecución, operaciones a realizar, etc.. etc..

 

Eladio Rincón