Geekly articles weekly

RabbitMQ - SQL Server

Hace una o dos semanas, vi un mensaje en el foro de usuarios de RabbitMQ sobre cómo configurar el envío de mensajes desde SQL Server a RabbitMQ. Como trabajamos estrechamente con Derivco , dejé algunas sugerencias allí y también dije que estoy escribiendo un blog sobre cómo hacer esto. Parte de mi mensaje no era del todo cierto, al menos hasta ese momento (lo siento, hermano, estaba muy ocupado).

Increíble, este es tu servidor SQL . Usarlo es muy fácil de poner información en una base de datos. Recuperar datos de una base de datos mediante una consulta es igual de fácil. Pero obtener los datos recién actualizados o pegados ya es un poco más difícil. Piensa en eventos en tiempo real; se realiza una compra: alguien debe ser notificado sobre esto en el momento en que esto suceda. Tal vez alguien dirá que dichos datos no deberían extraerse de la base de datos, sino de otro lugar. Por supuesto, este es el caso, pero a menudo simplemente no tenemos otra opción.

Teníamos una tarea: enviar eventos desde la base de datos externa para su posterior procesamiento, y la pregunta era: ¿cómo hacer esto?

SQL Server y comunicaciones externas


Durante la existencia de SQL Server, ha habido varios intentos de organizar las comunicaciones fuera de la base de datos; SQL Server Notification Services (NS), que apareció en SQL Server 2000, y más tarde, en SQL Server 2005, apareció SQL Server Service Broker (SSB). Los describí en mi libro A First Look at SQL Server 2005 for Developers , junto con Bob Boshemen y Dan Sullivan. NS apareció en SQL Server 2000, como dije, y fue rediseñado en la versión beta de SQL Server 2005. Sin embargo, NS fue completamente excluido de la versión lista para la venta (RTM) de SQL Server 2005.
Nota: Si lees el libro, encontrarás una serie de características que no estaban en la versión RTM.
SSB sobrevivió, y Microsoft presentó Service Broker External Activator (EA) en su paquete de características de SQL Server 2008. Permite a través del SSB interactuar fuera de la base de datos local. Teóricamente, suena bien, pero en la práctica es engorroso y confuso. Hicimos algunas pruebas y rápidamente nos dimos cuenta de que no estaba haciendo lo que necesitábamos. Además, SSB no nos dio el rendimiento que se necesitaba, por lo que tuvimos que inventar algo más.

SQLCLR


Como resultado, nos basamos en la tecnología SQLCLR. SQLCLR es una plataforma .NET que está integrada en el núcleo de SQL Server y puede usarse para ejecutar código .NET dentro del núcleo. Como ejecutamos código .NET, podemos hacer casi todo como en una aplicación .NET normal.
Nota: Escribí "casi" arriba, porque en realidad hay algunas limitaciones. En este contexto, estas restricciones casi no tienen efecto sobre lo que vamos a hacer.
El principio de funcionamiento de SQLCLR es el siguiente: el código se compila en una biblioteca dll, y luego esta biblioteca se registra utilizando las herramientas de SQL Server:

Construir ensamblaje

CREATE ASSEMBLY [RabbitMQ.SqlServer] AUTHORIZATION rmq FROM 'F:\some_path\RabbitMQSqlClr4.dll' WITH PERMISSION_SET = UNSAFE; GO

Fragmento de código 1: Crear un ensamblaje a lo largo de una ruta absoluta

El código realiza las siguientes acciones:

  • CREATE ASSEMBLY : crea un ensamblaje con el nombre de pila (no importa cuál sea).
  • AUTHORIZATION : indica el propietario del ensamblaje. En este caso, rmq es un rol predefinido de SQL Server.
  • FROM : determina dónde se encuentra el ensamblaje original. En la FROM , también puede especificar la ruta en formato binario o UNC. Los archivos de instalación para este proyecto usan una representación binaria.
  • WITH PERMISSION_SET - Establece permisos. UNSAFE es el menos estricto y se requiere en este caso.

Nota: independientemente del rol o inicio de sesión utilizado en la cláusula AUTHORIZATION , la clase appdomain debe crearse con el mismo nombre que cuando se carga el ensamblado en el dominio. Se recomienda separar los ensamblados con diferentes nombres de clases de dominio de aplicación para que cuando un ensamblaje falle, el resto no caiga. Sin embargo, si los ensamblajes dependen unos de otros, no se pueden dividir en diferentes clases.
Cuando se crea el ensamblaje, hacemos envoltorios de métodos .NET en él:

 CREATE PROCEDURE rmq.pr_clr_PostRabbitMsg @EndpointID int, @Message nvarchar(max) AS EXTERNAL NAME [RabbitMQ.SqlServer].[RabbitMQSqlClr.RabbitMQSqlServer].[pr_clr_PostRabbitMsg]; GO

Fragmento de código 2: Contenedor de métodos .NET

El código realiza las siguientes acciones:

  • Crea un procedimiento almacenado T-SQL denominado rmq.pr_clr_PostRabbitMsg que toma dos parámetros; @EndpointID y @Message .
  • En lugar del cuerpo del procedimiento, se utiliza una fuente externa, que consiste en:
    • Un ensamblado llamado RabbitMQ.SqlServer , es decir, el agregado que creamos anteriormente en el fragmento de código 1 .
    • Tipo completo (espacio de nombres y clase): RabbitMQSqlClr.RabbitMQSqlServer
    • El método del espacio de nombres y la clase anteriores es: pr_clr_PostRabbitMsg .

Cuando rmq.pr_clr_PostRabbitMsg , se pr_clr_PostRabbitMsg método pr_clr_PostRabbitMsg .
Nota: al crear un procedimiento, el nombre del ensamblado no distingue entre mayúsculas y minúsculas, a diferencia del nombre completo del tipo y método. No es necesario que el nombre del procedimiento que se está creando coincida con el nombre del método. Sin embargo, los tipos de datos finales para los parámetros deben coincidir.
Como dije anteriormente, en Derivco necesitamos enviar datos fuera de SQL Server, por lo que usamos SQLCLR y RabbitMQ (RMQ).

Rabbitmq


RMQ es un agente de mensajes de código abierto que implementa el Protocolo avanzado de colas de mensajes (AMQP) y está escrito en Erlang.

Dado que RMQ es un intermediario de mensajes, se requieren bibliotecas de cliente AMQP para conectarse a él. La aplicación hace referencia a las bibliotecas del cliente y, con su ayuda, abre una conexión y envía mensajes, ya que, por ejemplo, hay una llamada a través de ADO.NET a SQL Server. Pero a diferencia de ADO.NET, donde, lo más probable, la conexión se abre cada vez que accede a la base de datos, aquí la conexión permanece abierta durante todo el período de la aplicación.

Por lo tanto, para poder interactuar desde la base de datos con RabbitMQ, necesitamos la aplicación y la biblioteca cliente .NET para RabbitMQ.
Nota: en la siguiente parte de este artículo, se encontrarán fragmentos de código RabbitMQ, pero sin explicaciones detalladas de lo que hacen. Si es nuevo en trabajar con RabbitMQ, le sugiero que consulte los diversos tutoriales de RabbitMQ para comprender el propósito del código. El tutorial de Hello World C # es un buen comienzo. Una de las diferencias entre los libros de texto y los ejemplos de código es que los intercambiadores no se declaran en los ejemplos. Se supone que están predefinidos.

RabbitMQ.SqlServer


RabbitMQ.SqlServer es un ensamblado que utiliza la biblioteca de cliente .NET para RabbitMQ y proporciona la capacidad de enviar mensajes desde la base de datos a uno o más puntos finales RabbitMQ (VHosts e intercambiadores). El código se puede descargar / bifurcar desde mi repositorio RabbitMQ-SqlServer en GitHub. Contiene fuentes de ensamblaje y archivos de instalación (es decir, no tiene que compilarlos usted mismo).
Nota: este es solo un ejemplo para mostrar cómo SQL Server puede interactuar con RabbitMQ. Este NO es un producto terminado o incluso parte de él. Si este código te rompe el cerebro, no me culpes, porque esto es solo un ejemplo.

Funcionalidad


Cuando se carga el ensamblaje, o cuando se llama explícitamente a su inicialización, o cuando se llama indirectamente, en el momento en que se llama al procedimiento de envoltura, el ensamblaje carga la cadena de conexión en la base de datos local en la que se instaló, así como los puntos finales RabbitMQ a los que se conecta:

Conexión

 internal bool InternalConnect() { try { connFactory = new ConnectionFactory(); connFactory.Uri = connString; connFactory.AutomaticRecoveryEnabled = true; connFactory.TopologyRecoveryEnabled = true; RabbitConn = connFactory.CreateConnection(); for (int x = 0; x < channels; x++) { var ch = RabbitConn.CreateModel(); rabbitChannels.Push(ch); } return true; } catch(Exception ex) { return false; } }

Fragmento de código 3: conectarse al punto final

Al mismo tiempo, parte de la conexión al punto final también crea IModels en la conexión, y se usan al enviar (agregar a la cola) mensajes:

Envío de mensajes

 internal bool Post(string exchange, byte[] msg, string topic) { IModel value = null; int channelTryCount = 0; try { while ((!rabbitChannels.TryPop(out value)) && channelTryCount < 100) { channelTryCount += 1; Thread.Sleep(50); } if (channelTryCount == 100) { var errMsg = $"Channel pool blocked when trying to post message to Exchange: {exchange}."; throw new ApplicationException(errMsg); } value.BasicPublish(exchange, topic, false, null, msg); rabbitChannels.Push(value); return true; } catch (Exception ex) { if (value != null) { _rabbitChannels.Push(value); } throw; } }

El método Post se llama desde el método pr_clr_PostRabbitMsg(int endPointId, string msgToPost) , que se presentó como un procedimiento utilizando la cláusula CREATE PROCEDURE en el fragmento de código 2:

Método de llamada posterior

 public static void pr_clr_PostRabbitMsg(int endPointId, string msgToPost) { try { if(endPointId == 0) { throw new ApplicationException("EndpointId cannot be 0"); } if (!isInitialised) { pr_clr_InitialiseRabbitMq(); } var msg = Encoding.UTF8.GetBytes(msgToPost); if (endPointId == -1) { foreach (var rep in remoteEndpoints) { var exch = rep.Value.Exchange; var topic = rep.Value.RoutingKey; foreach (var pub in rabbitPublishers.Values) { pub.Post(exch, msg, topic); } } } else { RabbitPublisher pub; if (rabbitPublishers.TryGetValue(endPointId, out pub)) { pub.Post(remoteEndpoints[endPointId].Exchange, msg, remoteEndpoints[endPointId].RoutingKey); } else { throw new ApplicationException($"EndpointId: {endPointId}, does not exist"); } } } catch { throw; } }

Fragmento de código 5: Representar un método como procedimiento

Cuando se ejecuta el método, se supone que la persona que llama envía el identificador del punto final al que debe transmitirse el mensaje y, de hecho, el mensaje mismo. Si el valor -1 se pasa como el identificador del punto final, iteramos sobre todos los puntos y enviamos un mensaje a cada uno de ellos. El mensaje viene en forma de una cadena de la cual obtenemos bytes usando Encoding.UTF8.GetBytes . En un entorno de producción, la llamada Encoding.UTF8.GetBytes debe reemplazarse por serialización.

Instalación


Para instalar y ejecutar el ejemplo, necesita todos los archivos en la carpeta src\SQL . Para instalar, siga estos pasos:

  • Ejecute el script 01.create_database_and_role.sql . El creará:
    • Base de datos de prueba RabbitMQTest donde se creará el ensamblado.
    • rol rmq que se asignará como propietario del ensamblado
    • esquema, que también se llamará rmq . En este diagrama, se crean varios objetos de base de datos.

  • Ejecute el archivo 02.create_database_objects.sql . El creará:

    • la tabla rmq.tb_RabbitSetting , que almacenará la cadena de conexión en la base de datos local.
    • La tabla rmq.tb_RabbitEndpoint , en la que se almacenarán uno o más puntos finales RabbitMQ .

  • En el archivo 03.create_localhost_connstring.sql cambie el valor de la variable @connString a la cadena de conexión correcta para la base de datos RabbitMQTest creada en el paso 1 y ejecute el script.

Antes de continuar, debe tener una instancia en ejecución del corredor RabbitMQ y VHost (de forma predeterminada, VHost se representa como /). Como regla, tenemos varios VHost, solo por aislamiento. Este host también necesita un intercambiador, en el ejemplo usamos amq.topic . Cuando su corredor RabbitMQ esté listo, edite los rmq.pr_UpsertRabbitEndpoint procedimiento rmq.pr_UpsertRabbitEndpoint , que se encuentra en el archivo 04.upsert_rabbit_endpoint.sql :

Endpoint RabbitMQ

 EXEC rmq.pr_UpsertRabbitEndpoint @Alias = 'rabbitEp1', @ServerName = 'RabbitServer', @Port = 5672, @VHost = 'testHost', @LoginName = 'rabbitAdmin', @LoginPassword = 'some_secret_password', @Exchange = 'amq.topic', @RoutingKey = '#', @ConnectionChannels = 5, @IsEnabled = 1

Fragmento de código 6: Crear un punto final en RabbitMQ

En este punto, es hora de implementar ensamblados. Existen diferencias en las opciones de implementación para versiones de SQL Server anteriores a SQL Server 2014 (2005, 2008, 2008R2, 2012), y para 2014 y posteriores. La diferencia radica en la versión compatible de CLR. Antes de SQL Server 2014, la plataforma .NET se estaba ejecutando en la versión 2 de CLR, y en SQL Server 2014 y versiones posteriores, se usaba la versión 4.

SQL Server 2005 - 2012


Comencemos con las versiones de SQL Server que se ejecutan en CLR 2, ya que tienen sus propias características. Necesitamos implementar el ensamblado creado y, al mismo tiempo, implementar la biblioteca .NET del cliente RabbitMQ.Client ( RabbitMQ.Client ). Desde nuestro ensamblaje nos referiremos a la biblioteca del cliente RabbitMQ. Porque Como planeamos usar CLR 2, nuestro ensamblado y RabbitMQ.Client deberían compilarse en base a .NET 3.5. Hay problemas

Todas las versiones más recientes de la biblioteca RabbitMQ.Client están compiladas para el entorno CLR 4, por lo que no pueden usarse en nuestro ensamblado. La última versión de las bibliotecas del cliente para CLR 2 se compila en .NET 3.4.3. Pero incluso si tratamos de implementar este ensamblado, recibimos un mensaje de error:


Figura 1: Sistema perdido Conjunto de modelo de servicio

Esta versión de RabbitMQ.Client refiere a un ensamblado que no forma parte del CLR de SQL Server. Este es un ensamblado WCF, y esta es una de las limitaciones en SQLCLR que mencioné anteriormente: este ensamblaje en particular es para tipos de tareas que no se pueden realizar en SQL Server. Las versiones recientes de RabbitMQ.Client no tienen estas dependencias, por lo que se pueden usar sin ningún problema, excepto por los molestos requisitos del CLR 4. ¿Qué debo hacer?

Como saben, RabbitMQ es de código abierto, pero somos desarrolladores, ¿verdad? ;) ¡Entonces recompilemos! En la versión anterior a las últimas versiones (es decir, la versión <3.5.0) de RabbitMQ.Client eliminé los enlaces a System.ServiceModel y volví a compilar. Tuve que cambiar un par de líneas de código usando la funcionalidad System.ServiceModel , pero estos fueron cambios menores.

En este ejemplo, no utilicé la versión 3.4.3 del cliente, pero tomé la versión estable 3.6.6 y la volví a compilar usando .NET 3.5 (CLR 2). Casi funcionó :), excepto que las versiones posteriores de RabbitMQ.Client usan Task 'y que originalmente no forman parte de .NET 3.5.

Afortunadamente, hay una versión de System.Threading.dll para .NET 3.5 que incluye Task . Lo descargué, configuré los enlaces y ¡todo salió! Aquí el truco principal es que System.Threading.dll debe instalarse con el ensamblado.
Nota: la fuente de RabbitMQ.Client , desde la cual compilé una versión de .NET 3.5, está en mi repositorio en GitHub RabbitMQ Client 3.6.6 .NET 3.5 . El archivo binario dll junto con System.Threading.dll para .NET 3.5 también se encuentra en el lib\NET3.5 repositorio (RabbitMQ-SqlServer) .
Para instalar los ensamblados necesarios ( System.Threading , RabbitMQ.Client y RabbitMQ.SqlServer ) ejecute los scripts de instalación desde el directorio src\sql en el siguiente orden:

  1. 05.51.System.Threading.sql2k5-12.sql - System.Threading
  2. 05.52.RabbitMQ.Client.sql2k5-12.sql - RabbitMQ.Client
  3. 05.53.RabbitMQ.SqlServer.sql2k5-12.sql - RabbitMQ.SqlServer

SQL Server 2014+


En SQL Server 2014 y versiones posteriores, el ensamblaje se compila bajo .NET 4.XX (mi ejemplo está en 4.5.2), y puede hacer referencia a cualquiera de las últimas versiones de RabbitMQ.Client , que se puede obtener usando NuGet . En mi ejemplo, estoy usando 4.1.1. RabbitMQ.Client , que también se encuentra en el lib\NET4 repositorio (RabbitMQ-SqlServer) .

Para instalar, ejecute los scripts desde el directorio src\sql en el siguiente orden:

  1. 05.141.RabbitMQ.Client.sql2k14+.sql - RabbitMQ.Client
  2. 05.142.RabbitMQ.SqlServer.sql2k14+.sql - RabbitMQ.SqlServer

Contenedores de métodos SQL


Para crear procedimientos que se utilizarán desde nuestro ensamblado (3.5 o 4), ejecute el script 06.create_sqlclr_procedures.sql . Creará procedimientos T-SQL para tres métodos .NET:

  • rmq.pr_clr_InitialiseRabbitMq llama a pr_clr_InitialiseRabbitMq . Se usa para cargar e inicializar el ensamblado RabbitMQ.SqlServer.
  • rmq.pr_clr_ReloadRabbitEndpoints llama a pr_clr_ReloadRabbitEndpoints . Carga varios puntos finales RabbitMQ.
  • rmq.pr_clr_PostRabbitMsg llama a pr_clr_PostRabbitMsg . Se usa para enviar mensajes a RabbitMQ.

El script también crea un procedimiento T-SQL simple: rmq.pr_PostRabbitMsg , que se aplica a rmq.pr_clr_PostRabbitMsg . Este es un procedimiento de envoltura que sabe qué hacer con los datos, maneja excepciones, etc. En un entorno de producción, tenemos varios procedimientos similares que procesan varios tipos de mensajes. Lea más sobre esto a continuación.

Uso


De todo lo anterior, está claro que para enviar mensajes a RabbitMQ llamamos rmq.pr_PostRabbitMsg o rmq.pr_clr_PostRabbitMsg , pasando los parámetros del identificador del punto final y el mensaje en sí como una cadena. Todo esto, por supuesto, es genial, pero me gustaría ver cómo funcionará en realidad.

Lo que hacemos en entornos de producción es que en los procedimientos almacenados que procesan los datos que se deben enviar a RabbitMQ, recopilamos los datos que se enviarán y en el bloque de conexión llamamos a un procedimiento como rmq.pr_PostRabbitMsg . El siguiente es un ejemplo muy simplificado de dicho procedimiento:

Procedimiento de procesamiento de datos

 ALTER PROCEDURE dbo.pr_SomeProcessingStuff @id int AS BEGIN SET NOCOUNT ON; BEGIN TRY --     DECLARE @endPointId int; --    DECLARE @msg nvarchar(max) = '{' --        SET @msg = @msg + '"Id":' + CAST(@id AS varchar(10)) + ',' --  -  SET @msg = @msg + '"FName":"Hello",'; SET @msg = @msg + '"LName":"World"'; SET @msg = @msg + '}'; -- -  --     -,  -  SELECT @endPointId = 1; --    --     EXEC rmq.pr_PostRabbitMsg @Message = @msg, @EndpointID = @endPointId; END TRY BEGIN CATCH DECLARE @errMsg nvarchar(max); DECLARE @errLine int; SELECT @errMsg = ERROR_MESSAGE(), @errLine = ERROR_LINE(); RAISERROR('Error: %s at line: %d', 16, -1, @errMsg, @errLine); END CATCH END

En el fragmento de código 7, vemos cómo se capturan y procesan los datos necesarios en el procedimiento y se envían después del procesamiento. Para utilizar este procedimiento, ejecute el script 07.create_processing_procedure.sql desde el directorio src\SQL .

Vamos a correrlo todo


En este punto, debe estar preparado para enviar algunos mensajes. Antes de realizar la prueba, asegúrese de tener colas en RabbitMQ conectadas al intercambiador de punto final en rmq.tb_RabbitEndpoint .

Entonces, para comenzar, debe hacer lo siguiente:
Abra el archivo 99.test_send_message.sql .
Correr

 EXEC rmq.pr_clr_InitialiseRabbitMq;

para inicializar el ensamblaje y cargar los puntos finales RabbitMQ. Este no es un paso obligatorio, pero se recomienda que precargue el ensamblaje después de crearlo o modificarlo.

Correr

 EXEC dbo.pr_SomeProcessingStuff @id = 101

(puede usar cualquier otro identificador que desee).

Si todo funcionó sin errores, ¡debería aparecer un mensaje en la cola RabbitMQ! Entonces usaste SQLCLR para enviar un mensaje a RabbitMQ.

Felicidades

Source: https://habr.com/ru/post/es419457/

More articles:


All Articles