RabbitMQ — SQL Server

Тиждень чи два тому я побачив повідомлення на форумі RabbitMQ Users, про те, як налагодити відправлення повідомлень з SQL Server в RabbitMQ. Оскільки ми щільно з цим працюємо в Derivco, я залишив там деякі пропозиції, а також сказав, що пишу в блозі про те, як це можна зробити. Частина мого повідомлення була не зовсім вірною — принаймні, до цього моменту (соррі, Бро, був дуже зайнятий).

Приголомшлива штука, цей ваш SQL Server. З його допомогою дуже легко помістити інформацію в базу даних. Отримати дані з бази за допомогою запиту настільки ж просто. А ось отримати тільки що оновлені або вставлені дані вже трохи складніше. Подумайте про події в реальному часі; здійснена купівля — кого-то потрібно повідомити про це в той же момент, як тільки це сталося. Можливо, хтось скаже, що такі дані повинні виштовхувати не з бази даних, а звідкись ще. Безумовно, так воно і є, але досить часто у нас просто немає вибору.

У нас було завдання: відправляти події з бази даних зовні для подальшої обробки і стояло питання — як це зробити?

SQL Server і зовнішні комунікації

За час існування SQL Server було кілька спроб організувати комунікації за межі бази даних SQL Server Notification Services (NS), яка з’явилася в SQL Server 2000, а після, в SQL Server 2005, з’явився SQL Server Service Broker (SSB). Я описав їх у своїй книзі A First Look at SQL Server 2005 for Developers, разом з Бобом Бошеменом і Деном Салліваном. NS з’явився в SQL Server 2000, як я вже говорив, та був перепроектованими в бета-версії SQL Server 2005. Однак готової до продажу (RTM) версії SQL Server 2005 NS був выпилен виключений повністю.
Примітка: Якщо Ви читали книгу, то знайдете там низку особливостей, яких не було в RTM версії.
SSB вижив, і в пакеті додаткових компонентів SQL Server 2008, Microsoft представила Service Broker External Activator (ЕА). Він дає можливість через SSB здійснювати взаємодію за межами локальної бази даних. Теоретично звучить непогано, але на практиці — він громіздкий і заплутаний. Ми провели кілька тестів і швидко зрозуміли, що він не виконує того, що нам потрібно. Крім того, SSB не дав нам тієї продуктивності, яка була необхідна, тому нам довелося вигадувати щось інше.

SQLCLR

Те, до чого ми прийшли в результаті, було засновано на технології SQLCLR. SQLCLR — це платформа .NET, вбудована в ядро SQL Server і з її допомогою можна виконати .NET код всередині ядра. Оскільки ми виконуємо .NET код, маємо можливість робити майже все, що і в звичайному .NET додатку.
Примітка: вище я написав «майже», бо насправді є деякі обмеження. У даному контексті ці обмеження майже не впливають на те, що ми збираємося зробити.

Принцип роботи SQLCLR полягає в наступному: код компілюється в dll бібліотеки, а потім ця бібліотека реєструється засобами SQL Server:

Створення збірки

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

Фрагмент коду 1: Створення збірки за абсолютним шляху

Код виконує наступні дії:

  • CREATE ASSEMBLY — створює збірку з заданим ім’ям (незалежно від того, що це має бути).
  • AUTHORIZATION — вказує на власника складання. В даному випадку rmq є заздалегідь визначеною роллю SQL Server.
  • FROM — визначає, де розташована оригінальна збірка. В пропозиції FROM також можна вказувати шлях у двійковому або UNC форматах. Настановні файли для цього проекту використовують двійкове подання.
  • WITH PERMISSION_SET — встановлює дозволу. UNSAFE є найменш суворим, і необхідна в даному випадку.

Примітка: незалежно від того, роль або логін було використано в реченні AUTHORIZATION, клас appdomain повинен бути створений з тим же ім’ям, як і при завантаженні складання в домен. Рекомендується розділяти складання різними іменами appdomain класів, щоб при падінні однієї збірки не звалилися інші. Однак якщо збірки мають залежності один від одного, їх можна розділити на різні класи.

Коли зборка створена, робимо в ній обгортки методів .NET:

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

Фрагмент коду 2: обгортка методу .NET

Читайте також  Приклади розрахунку коефіцієнта готовності» для комплектів мережевого обладнання

Код виконує наступні дії:

  • Створює збережену процедуру Т-SQL з ім’ям rmq.pr_clr_PostRabbitMsg, що приймає два параметри; @EndpointID і @Message.
  • Замість тіла процедури використовується зовнішнє джерело, який складається з:
    • Збірка з ім’ям RabbitMQ.SqlServer, тобто агрегат, який ми створили вище у фрагменті коду 1.
    • Повний тип (простір імен і клас): RabbitMQSqlClr.RabbitMQSqlServer
    • Метод з наведеного вище простору імен і класу: pr_clr_PostRabbitMsg.

При виконанні процедури rmq.pr_clr_PostRabbitMsg, буде викликатися метод pr_clr_PostRabbitMsg.
Примітка: при створенні процедури, ім’я збірки не чутливе до регістру, на відміну від повного імені типу та методу. Не обов’язково щоб ім’я створюваної процедури збігалося з ім’ям методу. Однак кінцеві типи даних для параметрів повинні збігатися.
Як я говорив раніше, у нас в Derivco є необхідність відправляти дані за межі SQL Server, тому ми використовуємо SQLCLR і RabbitMQ (RMQ).

RabbitMQ

RMQ — це брокер повідомлень з відкритим вихідним кодом, який реалізує протокол AMQP (Advanced Message Queuing Protocol) і написаний на мові Erlang.
Оскільки RMQ є брокером повідомлень, для підключення до нього необхідні клієнтські бібліотеки AMQP. Додаток посилається на клієнтські бібліотеки і з їх допомогою відкриває з’єднання і відправляє повідомлення — як, наприклад, йде звернення через ADO.NET до SQL Server. Але на відміну від ADO.NET, де, швидше за все, з’єднання відкривається кожного разу при зверненні до бази даних, тут з’єднання залишається відкритим протягом усього періоду роботи програми.

Таким чином, для того, щоб мати можливість взаємодіяти з бази даних з RabbitMQ нам потрібно додаток і клієнтська бібліотеки .NET для RabbitMQ.
Примітка: у наступній частині цієї статті будуть зустрічатися фрагменти коду RabbitMQ, але без детальних пояснень що вони роблять. Якщо ви новачок в роботі з RabbitMQ, то пропоную поглянути на різні уроки з RabbitMQ, щоб розуміти призначення коду. Навчальний урок Hello World по C# — гарний початок. Одне з відмінностей між підручниками і прикладами коду полягає в тому, що в прикладах не оголошуються обмінники. Передбачається, що вони зумовлені.

RabbitMQ.SqlServer

RabbitMQ.SqlServer — збірка, що використовує клієнтську .NET бібліотеку для RabbitMQ і надає можливість відправки повідомлень з бази даних в одну або кілька кінцевих точок RabbitMQ (VHosts і обмінники). Код можна скачати/ответвить з мого репозиторію RabbitMQ-SqlServer на GitHub. Він містить вихідні коди збірок та інсталяційні файли (тобто Вам не доведеться збирати їх самостійно).
Примітка: це просто приклад, щоб показати, як SQL Server може взаємодіяти з RabbitMQ. Це НЕ готовий продукт і навіть не частина його. Якщо цей код розриває вам мозок — не треба на мене нарікати, бо це просто приклад.

Функціональність

При завантаженні складання, або при явному виклик її ініціалізації, або при непрямому зверненні, в момент виклику процедури-обгортки, збірка завантажує рядок підключення в локальну базу даних, в яку вона була встановлена, як і кінцеві точки RabbitMQ, до яких вона підключається:
Підключення

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;
}
}

Фрагмент коду 3: підключення до кінцевої точки

У цей же час частина підключення до кінцевої точки також створює IModels на з’єднанні, і вони використовуються при відправленні (додавання в чергу повідомлень:
Відправлення повідомлення

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;
}
}

Метод Post викликається з методу pr_clr_PostRabbitMsg(int endPointId, string msgToPost), який був представлений в якості процедури з допомогою пропозиції CREATE PROCEDURE у фрагменті коду 2:
Спосіб виклику Post

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;
}
} 

Фрагмент коду 5: Подання методу у вигляді процедури

Читайте також  Клац, клац: історія компанії Cherry, яка прославилася перемикачами для клавіатур

При виконанні методу передбачається, що викликає об’єкт відправляє ідентифікатор кінцевої точки, в яку необхідно передати повідомлення, і, власне, саме повідомлення. Якщо в якості ідентифікатора кінцевої точки передається значення -1, то ми перебираємо всі точки і відправляємо повідомлення кожної з них. Повідомлення приходить у вигляді рядка, з якою ми отримуємо байти з допомогою Encoding.UTF8.GetBytes. У середовищі виклик Encoding.UTF8.GetBytes слід замінити на серіалізацію.

Установка

Щоб встановити і запустити приклад, потрібні всі файли в папці srcSQL. Для установки виконайте наступні дії:

  • Запустіть скрипт 01.create_database_and_role.sql. Він створить:
    • тестову базу даних RabbitMQTest, де буде створена збірка.
    • роль rmq, яка буде призначена в якості власника складання
    • схему, яка теж буде назватися rmq. У цій схемі створюються різні об’єкти бази даних.

  • Запустіть файл 02.create_database_objects.sql. Він створить:
    • таблицю rmq.tb_RabbitSetting, в якій буде зберігатися рядок підключення до локальної БД.
    • таблицю rmq.tb_RabbitEndpoint, в якій буде зберігатися одна або кілька кінцевих точок RabbitMQ.

  • У файлі 03.create_localhost_connstring.sql змініть значення змінної @connString на правильну рядок підключення до бази RabbitMQTest, створеної на кроці 1 і запустіть скрипт.

Перш ніж продовжити, необхідно мати запущений екземпляр брокера RabbitMQ і VHost (за замовчуванням VHost представлений як /). Як правило, у нас є кілька VHost, просто для ізоляції. Цей хост також потребує обміннику, у прикладі ми використовуємо amq.topic. Коли у вас готовий брокер RabbitMQ, змініть параметри процедури rmq.pr_UpsertRabbitEndpoint, яка знаходиться у файлі 04.upsert_rabbit_endpoint.sql:
Кінцева точка 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

Фрагмент коду 6: створення кінцевої точки в RabbitMQ

На даному етапі настав час розгорнути складання. У розгорнутих варіантах є відмінності для версій SQL Server, попередніх SQL Server 2014 (2005, 2008, 2008R2, 2012), і для 2014 і вище. Різниця полягає в підтримуваної версії CLR. До SQL Server 2014 платформа .NET виконувалася в середовищі CLR версії 2, а в SQL Server 2014 і вище використовується версія 4.

SQL Server 2005 — 2012

Давайте почнемо з версій SQL Server, які працюють на CLR 2, так як там є свої особливості. Нам потрібно розгорнути створену збірку, а разом з тим розгорнути клієнтську .NET бібліотеку RabbitMQ (RabbitMQ.Client). З нашої збірки будемо посилатися на клієнтську бібліотеку RabbitMQ. Оскільки ми планували використовувати CLR 2, то наша збірка і RabbitMQ.Client повинні бути скомпільована на основі .NET 3.5. Тут виникають проблеми.

Всі останні версії бібліотеки RabbitMQ.Client скомпільовані для середовища CLR 4, тому вони не можуть використовуватися в нашій збірці. Остання версія клієнтських бібліотек для CLR 2 зібрана на .NET 3.4.3. Але навіть якщо ми спробуємо розгорнути цю збірку, то отримаємо повідомлення про помилку:

Малюнок 1: Відсутній збірка System.ServiceModel

Ця версія RabbitMQ.Client посилається на збірку, що не є частиною МОВИ SQL Server. Це WCF збірка, і це одне з обмежень у SQLCLR, про які я говорив вище: ця конкретна збірка призначена для таких типів завдань, які не допускається виконувати SQL Server. Останні версії RabbitMQ.Client не мають цих залежностей, тому можуть використовуватися без будь-яких проблем, якщо не вважати прикрі вимоги середовища CLR 4. Що робити?

Як відомо, RabbitMQ має відкритий вихідний код, ну, а ми ж з вами розробники, вірно? 😉 Так давайте ж перекомпилим! У варіанті до останніх релізів (т. е. версії <3.5.0) RabbitMQ.Client я видалив посилання на System.ServiceModel і перекомпилил. Мені довелося змінити кілька рядків коду, які використовують функціонал System.ServiceModel, але це були незначні зміни.

У цьому прикладі я не використовував версію клієнта 3.4.3, а взяв стабільний реліз 3.6.6 і перекомпилировал з використанням .NET 3.5 (CLR 2). Це майже спрацював :), за винятком того, що більш пізні релізи RabbitMQ.Client використовують Task‘і, які спочатку не є частиною .NET 3.5.

На щастя, є версія System.Threading.dll для .NET 3.5, яка включає Task. Я скачав її, налаштував посилання і все поїхало! Тут головна фішка в тому, що System.Threading.dll повинна бути встановлена разом зі збіркою.
Примітка: ісходник RabbitMQ.Client, з якого я зібрав версію .NET 3.5, є в моєму репозиторії на GitHub RabbitMQ Client 3.6.6 .NET 3.5. Бінарники dll разом з System.Threading.dll для .NET 3.5 також лежить в каталозі libNET3.5 репозиторію (RabbitMQ-SqlServer).

Для встановлення необхідних збірок (System.Threading, RabbitMQ.Client і RabbitMQ.SqlServer) запустіть установчі скрипти з каталогу srcsql в такому порядку:

  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+

У SQL Server 2014 і пізніших версіях збірка компілюється під .NET 4.ХХ (мій приклад на 4.5.2), і ви можете посилатися на будь-яку з останніх версій RabbitMQ.Client, яку можна отримати з допомогою NuGet. У своєму прикладі я використовую 4.1.1. RabbitMQ.Client, яка так само є в каталозі libNET4 репозиторію (RabbitMQ-SqlServer).

Для установки запустіть скрипти з каталогу srcsql в такому порядку:

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

Обгортки методів SQL

Щоб створити процедури, які будуть використовуватися з нашої збірки (3.5 або 4), запустіть скрипт 06.create_sqlclr_procedures.sql. Він створить Т-SQL процедури для трьох .NET-методів:

  • rmq.pr_clr_InitialiseRabbitMq викликає pr_clr_InitialiseRabbitMq. Використовується для завантаження і ініціалізації складання RabbitMQ.SqlServer.
  • rmq.pr_clr_ReloadRabbitEndpoints викликає pr_clr_ReloadRabbitEndpoints. Завантажує різні кінцеві точки RabbitMQ.
  • rmq.pr_clr_PostRabbitMsg викликає pr_clr_PostRabbitMsg. Використовується для відправки повідомлення у RabbitMQ.

Скрипт також створює просту T-SQL процедуру — rmq.pr_PostRabbitMsg, яка застосовується до rmq.pr_clr_PostRabbitMsg. Це процедура-обгортка, яка знає, що робити з даними, обробляє виключення і т. д. В робочому середовищі у нас є кілька подібних процедур, що обробляють різні типи повідомлень. Детальніше про це читайте нижче.

Використання

З усього перерахованого видно, що для відправки повідомлень в RabbitMQ ми викликаємо rmq.pr_PostRabbitMsg або rmq.pr_clr_PostRabbitMsg, передавши в параметрах ідентифікатор кінцевої точки і саме повідомлення у вигляді рядка. Все це, звичайно, круто, але хотілося б бачити як це буде працювати в реальності.

Що ми робимо в робочих середовищах — у збережених процедурах, що обробляють дані, які повинні бути відправлені в RabbitMQ, ми збираємо дані для відправки і в блоці підключення викликаємо процедуру, подібну rmq.pr_PostRabbitMsg. Нижче наведено дуже спрощений приклад такої процедури:
Процедура обробки даних

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

У фрагменті коду 7 ми бачимо, як у процедурі захоплюються і обробляються потрібні дані і після обробки відправляються. Щоб використовувати цю процедуру, виконайте скрипт 07.create_processing_procedure.sql з каталогу srcSQL.

Давайте все це запустимо

На цьому етапі ви повинні бути готові відправити кілька повідомлень. Перед тестуванням переконайтеся, що у вас в RabbitMQ є черги, прив’язані до обмінника кінцевої точки в rmq.tb_RabbitEndpoint.

Отже, для запуску потрібно виконати наступне:
Відкрийте файл 99.test_send_message.sql.
Виконайте

EXEC rmq.pr_clr_InitialiseRabbitMq;

щоб ініціалізувати складання і завантажити кінцеві точки RabbitMQ. Це не обов’язкова дія, але рекомендується попередньо завантажити збірку, після її створення або зміни.
Виконайте

EXEC dbo.pr_SomeProcessingStuff @id = 101

(можна використовувати будь-який інший ідентифікатор, який подобається).
Якщо все відпрацювало без помилок, то в черзі RabbitMQ повинно з’явитися повідомлення! Ось Ви і скористалися SQLCLR для відправки повідомлення у RabbitMQ.
Конгратулейшенс!

Читайте також  FreeMarker шаблони

Степан Лютий

Обожнюю технології в сучасному світі. Хоча частенько і замислююся над тим, як далеко вони нас заведуть. Не те, щоб я прям і знаюся на ядрах, пікселях, коллайдерах і інших парсеках. Просто приходжу в захват від того, що може в творчому пориві вигадати людський розум.

You may also like...

Залишити відповідь

Ваша e-mail адреса не оприлюднюватиметься. Обов’язкові поля позначені *