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:\temp\accountsdb.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:\temp\accountsdb.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

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..