Jérôme De Cuyper

En un artículo anterior, expliqué los pasos básicos para instalar un Service Broker. Por su naturaleza asíncrona, no siempre es fácil depurar errores de dicho Servicio. Además, varios componentes están involucrados en el buen funcionamiento del proceso: cola de mensajes, mensajes, contratos, servicios de emisión y de recepción, stored procedure de activación, etc. Para poder depurar el Service Broker, existe la herramienta “ssbdiagnose”. Esta utilidad sirve parta detectar problemas que ocurren en conversaciones o en la configuración del Service Broker. Es bastante sencillo usarla, funciona para servidor locales como remotos. Si tienes Sql Server 2005 o 2008 instalado, ssbdiagnose se encuentra en:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn

Se dispara una diagnostico con el siguiente comando:

ssbdiagnose -XML -E -S ".\SQLEXPRESS" -d service_broker_db CONFIGURATION FROM SERVICE ServicioEmisor TO SERVICE ServicioRecepcion ON CONTRACT ContratoCalculoPosiciones > C:\sbOutputXML.xml

El parámetro -XML indica que la salida será en el formato XML y se podrá consultar en el archivo C:\sbOutputXML.xml. El parámetro -E avisa que se usarán las credenciales windows del usuario actualmente logeado para conectarse a la instancia de la base de datos service_broker_db. Las demás instrucciones sirven para mandar un mensaje del servicio de emisión hacia el servicio de recepción usando el contrato ‘ContratoCalculoPosiciones’.

Cuando levanté por primera vez una instancia del Service Broker, por alguna razón los mensajes de la cola nunca se procesaban. Usando ’ssbdiagnose’ descubrí dos problemas bastante graves. Resolviéndolos pude hacer funcionar mi instancia. El primer error que me arrojó fue:

<Issue code="29997" level="Error" type="Diagnosis" server=".\SQLEXPRESS" database="service_broker_db ">Service Broker GUID is identical to that of database service_broker_db on server .\SQLEXPRESS</Issue>

El error indica que el Globally Unique Identifier del Service Broker está duplicado en la base de datos. Para visualizar los GUID de todos los Service Broker, ejecuta el siguiente query:

SELECT
name
,is_broker_enabled
,is_honor_broker_priority_on
,is_trustworthy_on
,service_broker_guid
FROM sys.databases
ORDER BY service_broker_guid

Este error sucede cuando un base de datos se importa desde otro servidor. La solución consiste en regenerar el GUID del Service Broker:

ALTER DATABASE [service_broker_db] SET NEW_BROKER;

Después de generar el GUID, me encontré el siguiente error (blilingüe!):

The EXECUTE AS for the user dbo specified for activation on queue dbo.MiStoredProcedure cannot be impersonated due to an exception No se puede ejecutar como la entidad de seguridad de base de datos porque la entidad de seguridad "dbo" no existe, este tipo de entidad de seguridad no se puede suplantar o el usuario no tiene permiso.

El problema descrito aquí tiene mucho que ver con el anterior. La base de datos que usé era una copia de una base creada en otra máquina. El comando ‘EXECUTE AS’ necesita permisos para ejecutarse y no están disponibles dado que el ‘owner’ de la base no está definido correctamente. Se puede arreglar este problema de permisos con el siguiente comando:

ALTER AUTHORIZATION ON DATABASE::[service_broker_db] TO [SA];

Ya con estos dos errores arregledos, los mensajes de la cola empezaron a ser procesados por el servicio de recepción.

Se puede depurar un Service Broker “a mano” pero es mucho más complejo y largo que utilizar la herramienta “ssbdiagnose”. Espero que también les ayude en algo!

Información de MSDN sobre “ssbdiagnose”.

Recientemente, estuve involucrado en el desarrollo de una trivia online para niños. Durante 3 meses, 12 retos se publicaron y unos 20k jugadores se registraron. Conforme avanzaban las semanas, los niños iban ganando puntos. Desde el primer día de la trivia, estaba disponible una página con la lista de jugadores ordenados por puntos y fecha de participación. Para generar ese listado, se tenía que realizar una suma de todos los puntos de cada jugador y luego ordenarlos por puntos obtenidos. Durante las primeras semanas, el listado se cargaba sin problema. Pero como lo podrán imaginar, el query se hizo poco a poco más y más lento. El cálculo ya involucraba muchos datos y aunque logramos mejorar un poco el query, no dejaba de alentarse con los días.

Obviamente, fue necesario encontrar una solución. Como el query estaba alentando mucho la generación de la lista de ganadores, se me ocurrió que era necesario mandar su ejecución en un proceso asíncrono al thread principal. Cada vez que un jugador grabe algún punto, se tendrá que calcular nuevamente los resultados pero sin afectar a la consulta de la tabla de ganadores.

Existen varias maneras de crear un proceso asíncrono con SQL Server 2008 pero una de éstas consiste en utilizar el no muy famoso Service Broker:

El Service Broker permite implementar un servicio de mensajería bastante robusto sin salirse de la base de datos. En computación, una mensajería (o cola de mensajes) es un componente lógico que se ocupa para permitir la comunicación entre procesos. Además provee un protocolo de comunicación asíncrona en el cuál el emisor y el receptor no deben interactuar con el mensaje al mismo tiempo. El emisor agrega un mensaje a la cola y el receptor lo consulta en otro momento.  En mi caso, la ejecución del query es mi “mensaje”. El emisor es la trivia y el receptor es el Service Broker. Cada vez que un niño marca puntos, la aplicación mete en la cola un mensaje para ejecutar el cálculo de posiciones. Luego, el Service Broker se encarga de ejecutar el cálculo sin interumpir al thread principal. Y no importa si varios jugadores están grabando puntos al mismo tiempo, porque por cada nuevo punto, el Service Broker se encargará de recalcular la tabla de posiciones.

Originalmente, el Service Broker ha sido diseñado para permitir intercambiar datos entre bases de datos distribuidas y así repartir la carga de ejecución entre varias máquinas. Sin embargo, también se puede utilizar, como en mi caso, adentro de la misma base de datos y en el mismo servidor. Ahora, ¿cómo implementar este servicio? Desafortunadamente, no existen tantos artículos sobre este componente como me lo esperaba. Tuve que buscar y debugear un buen antes de poder levantar mi propio servicio. La buena noticia es que valió la pena el esfuerzo. Aquí les explico cómo armar un servicio de mensajería sencillo.

Antes que todo, se tiene que confirmar que la base de datos tiene el Service Broker habilitado. Esto se puede visualizar en las propiedades de la base de datos:

Se puede habilitar mediante la siguiente instrucción SQL:

ALTER DATABASE miBD SET ENABLE_BROKER

En los dos casos, la instrucción puede tardar un poco en ejecutarse. Cuando ya está lista nuestra base de datos, podemos empezar a configurar los componentes del servicio. Para proteger los accesos y las lecturas de los mensajes, se tiene que definir tanto el tipo de mensaje que se va a manejar, así como el contrato que va a existir entre el emisor y el receptor. Se puede requerir, por ejemplo, que los mensajes sean en formato XML y obligar el servicio a validar ese formato durante la transmisión de los mensajes. En nuestro caso, vamos a ocupar el formato más sencillo y sin requerir validación ninguna:

CREATE MESSAGE TYPE CalcularPosiciones VALIDATION = NONE;

Los intercambios de mensajes entre emisor y receptor se llaman conversaciones. Cada conversación está asociada con un contrato.  El anterior define el sentido de la conversación (de emisor a receptor o vice-versa) así como el tipo de mensaje que se puede intercambiar. Nuestro contrato define que solamente se pueden intercambiar mensajes de tipo ‘CalcularPosiciones’ y que sólo el iniciador de la conversación puede mandar mensajes de este tipo :

CREATE CONTRACT ContratoCalculoPosiciones
(
CalcularPosiciones SENT BY INITIATOR
)

Luego, se crea una cola que se va a encargar de procesar cada nuevo mensaje. La configuramos con un estatus activo. La propiedad MAX_QUEUE_READERS indica si se pueden o no procesar varios mensajes a la vez. Para evitar colisiones, dejamos esta propiedad en un 1. El stored procedure ’spCalcularPosiciones’ será disparado por cada activación de la cola de recepción. Como lo veremos más adelante, este stored procedure se encargará del cálculo de la tabla de posiciones.

CREATE QUEUE [ColaRecepcion];
ALTER QUEUE [ColaRecepcion] WITH ACTIVATION
(
STATUS = ON,
MAX_QUEUE_READERS = 1,
PROCEDURE_NAME = spCalcularPosiciones,
EXECUTE AS SELF
);

Para que la cola de recepción funcione, se le tiene que asociar a un servicio de recepción. El servicio tiene como tarea entregar los mensajes a la cola. Se asocian de la siguiente forma:

CREATE SERVICE [ServicioRecepcion] ON QUEUE [ColaRecepcion]([ContratoCalculoPosiciones]);

Para poder mandar los mensajes, se necesita crear una cola de envío y asociarla con un servicio emisor:

CREATE QUEUE [ColaEmisor];
CREATE SERVICE [ServicioEmisor] ON QUEUE [ColaEmisor];

Ya tenemos listas nuestras dos colas de mensajes así como nuestros servicios. Ahora nos falta crear el stored procedure que se va a disparar por cada mensaje recibido:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spCalcularPosiciones]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Handle UNIQUEIDENTIFIER;
DECLARE @MessageType SYSNAME;
DECLARE @fecha DATETIME;

RECEIVE TOP (1)
@Handle = conversation_handle,
@MessageType = message_type_name
FROM [ColaRecepcion];

SELECT @Handle
IF(@Handle IS NOT NULL)
BEGIN
-- Calcular puntos de todos los jugadores e insertarlos en la tabla de resultados
-- Para efectos de prueba, también se puede inserta un registro en alguna tabla con efecto de crear un log de los movimientos de cada mensaje
END
END

El stored procedure se encarga de buscar, con la instrucción RECEIVE, el último mensaje disponible en la cola. Se recupera el ‘handle’ de la conversación. Cada intercambio
de mensaje se asocia con un handle, un identificador único. Después de recuperar el último mensaje, se realiza el cálculo de las posiciones de cada jugador o cualquier otro tipo de cálculo que necesiten.

Ya están listos todos los componentes. Ahora, nada más nos falta probar que todo esté funcionando correctamente. Para mandar un mensaje a la cola, se ocupan las siguientes instrucciones:

DECLARE @MessageBody XML
SET @MessageBody = '';

DECLARE @Handle UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @Handle
FROM SERVICE [ServicioEmisor]
TO SERVICE 'ServicioRecepcion'
ON CONTRACT [ContratoCalculoPosiciones]
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @Handle
MESSAGE TYPE [CalcularPosiciones](@MessageBody);

Una vez que se manda el mensaje, el servicio de recepción se activa y agrega el mensaje a la cola de recepción que a su vez dispara nuestro stored procedure. Si todo sale bien, se debe ejecutar correctamente nuestro cálculo de posiciones. El envío del mensaje se puede incluir, por ejemplo, en un trigger asociado con la inserción de registros en una tabla. En mi caso, agregue un trigger a la tabla de puntos. Cada vez que un jugador inserta puntos en esa tabla, se dispara un mensaje nuevo hacia nuestro Service Broker. La gran ventaja de este servicio de mensajería es que la inserción de los puntos ya no está afectada por el cálculo de posiciones. Mientras se insertan nuevos puntos, el cálculo se ejecuta pero de forma asíncrona sin alentar el thread principal. Gracias a este servicio, la consulta de la tabla de posiciones ya no está alentada por ningún cálculo y tiene un tiempo de respuesta inmediata.

Esta implementación del Service Broker es bastante sencilla. Si requieren configurarla para un desarrollo más complejo, les aconsejo consultar el blog de Remus Rusanu. Este blog contiene los artículos más completos que he visto sobre el Service Broker. También, pueden consultar una pregunta que dejé en el sitio de Stackoverflow.

FUENTES:
Crear Base de Datos (.sql)
Crear Service Broker (.sql)
Activar Service Broker (.sql)
Borrar Service Broker (.sql)