SQL Service Broker: herramienta para envío de mensajes en SQL Server

En escenarios donde es necesario notificar de forma inmediata a sistemas externos cuando ocurre una acción en la base de datos, SQL Service Broker se presenta como una solución robusta y escalable. Esta tecnología permite orquestar mensajes asíncronos desde SQL Server sin bloquear las operaciones internas, garantizando integridad y seguridad.

¿Qué es SQL Service Broker?

SQL Service Broker es un componente de SQL Server que habilita la mensajería y el procesamiento distribuido mediante colas y contratos de servicio para comunicación asíncrona. Es ideal para escenarios donde, por ejemplo, al registrar un nuevo cliente en una tabla, se requiere enviar un mensaje a un sistema externo, como un CRM o servicio de notificaciones.

Ejemplo Práctico: Notificación al insertar un cliente

Tabla de eventos OutboxCustomerEvents

Se utiliza una tabla para almacenar los eventos generados al insertar un nuevo cliente, registrando datos del cliente y el estado de la notificación. Por ejemplo:

de>CREATE TABLE [dbo].[OutboxCustomerEvents](
 [EventId] [int] IDENTITY(1,1) NOT NULL,
 [Membresia] varchar NULL,
 [Nombre] varchar NULL,
 [ApellidoPaterno] varchar NULL,
 [ApellidoMaterno] varchar NULL,
 [Email] varchar NULL,
 [Telefono] varchar NULL,
 [Celular] varchar NULL,
 [Sexo] varchar NULL,
 [FechaNacimiento] [date] NULL,
 [Cliente] varchar NULL,
 [Notificado] [bit] NULL,
 [CorreoVerificado] [bit] NULL,
 [UltimaVerificacion] [datetime] NULL,
 [EstatusUltimaVerificacion] varchar NULL,
 [Estado] varchar NULL,
 [AttemptCount] [int] NULL,
 [LastError] nvarchar NULL,
 [CreatedAt] [datetime] NULL,
 [SentAt] [datetime] NULL,
 [ProcessedBy] varchar NULL,
 [LockedAt] [datetime] NULL,
)

Trigger para enviar mensaje con Service Broker

Al insertarse un cliente en la tabla principal, el trigger inserta un evento en la tabla OutboxCustomerEvents y envía un mensaje a Service Broker para notificación asíncrona:

de>CREATE TRIGGER [dbo].[trg_Customer_AfterInsert]
ON [dbo].[Customer]
AFTER INSERT
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @ConversationHandle UNIQUEIDENTIFIER
 DECLARE @MessageBody NVARCHAR(MAX)
 DECLARE @EventId INT
 DECLARE @ErrorMessage NVARCHAR(4000)
 DECLARE @Cliente VARCHAR(20)

 BEGIN TRY
 SELECT @Cliente = C.Cliente 
 FROM inserted i 
 INNER JOIN Ecommerce_db.dbo.CTE C ON C.Tarjeta = i.Membresia

 INSERT INTO [dbo].[OutboxCustomerEvents] (
 Membresia, Nombre, ApellidoPaterno, ApellidoMaterno,
 Email, Telefono, Celular, Sexo, FechaNacimiento, Cliente,
 Notificado, CorreoVerificado, UltimaVerificacion, 
 EstatusUltimaVerificacion, Estado, CreatedAt
 )
 SELECT
 i.Membresia, i.Nombre, i.ApellidoPaterno, i.ApellidoMaterno,
 i.Email, i.Telefono, i.Celular, i.Sexo, i.FechaNacimiento, @Cliente,
 i.Notificado, i.CorreoVerificado, i.UltimaVerificacion,
 i.EstatusUltimaVerificacion, 'PENDING', GETDATE()
 FROM inserted i

 SET @EventId = SCOPE_IDENTITY()
 SET @MessageBody = CAST(@EventId AS NVARCHAR(10))

 BEGIN DIALOG CONVERSATION @ConversationHandle
 FROM SERVICE [PatientEventService]
 TO SERVICE 'PatientEventService'
 ON CONTRACT [PatientEventContract]
 WITH ENCRYPTION = OFF;

 SEND ON CONVERSATION @ConversationHandle
 MESSAGE TYPE PatientEventMessage;

 END TRY
 BEGIN CATCH
 SET @ErrorMessage = ERROR_MESSAGE()

 UPDATE [dbo].[OutboxCustomerEvents]
 SET LastError = 'Error en trigger: ' + @ErrorMessage,
 Estado = 'FAILED'
 WHERE EventId = @EventId
 END CATCH
END

Listener en Python para recibir los mensajes

Un ejemplo básico de un listener en Python que se conecta a SQL Server para escuchar mensajes y procesarlos:

de>import pyodbc

conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=server_name;DATABASE=Ecommerce_db;UID=user;PWD=password')
cursor = conn.cursor()

while True:
 cursor.execute('WAITFOR (RECEIVE TOP(1) CONVERT(NVARCHAR(MAX), message_body) AS message FROM dbo.QueueName), TIMEOUT 10000')
 row = cursor.fetchone()
 if row:
 message = row.message
 print(f'Recibido mensaje: {message}')
 # Aquí incluir lógica para enviar mensaje a sistema externo
 # Después de procesamiento, cerrar conversación o actualizar estado
 else:
 print('Esperando mensajes...')

Ventajas de esta implementación

• Control total de reintentos: Los mensajes quedan registrados y se pueden reintentar en caso de fallos.
• Acción inmediata sin bloqueo: El trigger sólo detona el envío, el proceso es asíncrono y no afecta la inserción principal.
• Escalabilidad: Se pueden crear múltiples canales y contratos para diversos tipos de mensajes y sistemas externos.
• Seguridad: El usuario de Service Broker no requiere permisos elevados, minimizando riesgos.
• Integridad y trazabilidad: Todo evento y su resultado quedan registrados para auditoría y análisis.