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