SQL Trigger, który wywołuje metodę w .NET – Service Broker

Mechanizm Service Broker w systemie zarządzania bazą danych Microsoft SQL Server (MS SQL) stanowi zaawansowane narzędzie umożliwiające asynchroniczną komunikację między różnymi bazami danych lub aplikacjami w ramach tej samej bazy danych. Został opracowany i wdrożony w 2005 roku w celu wspierania zadań związanych z przetwarzaniem wiadomości, zarządzaniem kolejkami komunikatów oraz zapewnianiem niezawodności dostarczania komunikatów.

Service Broker, który przybliżę w niniejszym artykule, stanowi integralną część systemu MS SQL, umożliwiając przekazywanie informacji do zewnętrznych odbiorców o zmianach zachodzących w bazie danych, takich jak dodanie, usunięcie lub modyfikacja rekordów. Jego funkcjonalność umożliwia uruchamianie asynchronicznych działań, podobnie jak w przypadku triggerów, co sprawia, że jest szczególnie użyteczny w wywoływaniu metod asynchronicznych napisanych w technologii .NET po zmianach w bazie danych MS SQL.

Potrzeba biznesowa

W środowisku, w którym pracuję wraz z zespołem, mamy do czynienia z fabryką, gdzie poszczególne maszyny uczestniczące w procesie produkcji komunikują się ze sobą poprzez bazę danych MSSQL. Urządzenia te potrafią w sposób autonomiczny zapisywać informacje do tabeli, aby oznaczyć rozpoczęcie lub zakończenie produkcji konkretnego elementu.

Naszym zadaniem było przetwarzanie tych wpisów w tabeli, wykonanie określonych obliczeń i wprowadzenie odpowiednich danych do tabel w innej bazie danych. Ten temat był kluczowy z perspektywy biznesowej, a opóźnienia w jego realizacji nie mogły zostać zaakceptowane. Wcześniej próbowaliśmy rozwiązywać podobne problemy, okresowo sprawdzając, czy pojawiły się nowe wpisy w bazie danych i uruchamiając wtedy całą procedurę przetwarzania. Jednakże taka metoda prowadziła do opóźnień, nieaprobowanych przez naszego klienta.

W tym kontekście klient wyraźnie zażądał, aby cała logika biznesowa była realizowana natychmiast po pojawieniu się nowego rekordu w bazie danych MSSQL. Wskazano, że całe przetwarzanie danych musi być realizowane wewnątrz bazy danych przy użyciu mechanizmów takich jak triggery, procedury i funkcje.

Alternatywne rozwiązanie

Z naszej perspektywy rozwiązanie zaproponowane przez klienta jawiło się jako wyjątkowo skomplikowane i trudne do zarządzania. W rezultacie podjęliśmy decyzję o poszukiwaniu alternatywnego rozwiązania, które umożliwiłoby nam spełnienie wymagań biznesowych w bardziej efektywny i prostszy w zarządzaniu sposób.

Nasze poszukiwania doprowadziły nas do Service Brokera, który okazał się idealnym narzędziem do obsługi takiej sytuacji. Dzięki niemu mogliśmy zautomatyzować przetwarzanie danych w sposób niezawodny i efektywny, a jednocześnie zachować kontrolę nad naszym kodem.

Przykładowe uruchomienie komunikacji w paru prostych krokach

Konfiguracja Service Brokera

Na funkcjonalność ServiceBrokera składa się część bazodanowa (nadawcza) oraz część programowa (odbiorcza), której zadaniem jest przetworzenie przychodzącego zdarzenia o zmianie.

W bazie danych TestDB utworzona została tabela TestTable o strukturze pokazanej poniżej:

W pierwszym korku należy uruchomić funkcjonalność Service Broker-a na danej bazie:

ALTER DATABASE TestDB SET ENABLE_BROKER;

Następnie należy utworzyć kolejkę i usługę Service Brokera:

-- Tworzenie kolejki
CREATE QUEUE MyQueue;

-- Tworzenie usługi
CREATE SERVICE MyService
ON QUEUE MyQueue ([DEFAULT]);

Ostatnim krokiem po stronie nadawcy jest wygenerowanie zdarzenia informującego o zmianie na bazie. W naszym przypadku zdarzenie ma wystąpić po dodaniu wiersza do tabeli. W tym celu należy użyć polecenia SEND, aby wysłać wiadomość do kolejki. Najlepiej zrealizować to triggerem:

CREATE OR ALTER TRIGGER insertNewData  ON dbo.TestTable
   AFTER insert  
AS   
BEGIN  
    declare @newId int  
	declare @dialog_handle UNIQUEIDENTIFIER
    SET NOCOUNT ON;  
    SELECT @newId = Id from inserted  
	
	DECLARE @message_body NVARCHAR(MAX);
	SET @message_body = 'New data id: ' + cast( @newId as varchar(10)); -- Możesz dostosować dane do przetworzenia

	set @dialog_handle = NEWID()
	BEGIN DIALOG CONVERSATION @dialog_handle
	FROM SERVICE MyService
	TO SERVICE 'MyService'
	ON CONTRACT [DEFAULT]
	WITH ENCRYPTION = OFF;

	SEND ON CONVERSATION @dialog_handle MESSAGE TYPE [DEFAULT] (@message_body);

   END CONVERSATION @dialog_handle
END  
GO

W powyższym triggerze wyróżniamy 3 kluczowe polecenia:

• BEGIN DIALOG CONVERSATION – rozpoczyna nowy dialog, który jest jednostką komunikacji między dwiema usługami lub aplikacjami. To polecenie jest używane do rozpoczęcia dialogu przed wysłaniem wiadomości.
• SEND ON CONVERSATION – służy do wysyłania wiadomości w ramach dialogu do docelowej usługi.
• END CONVERSATION – zamyka dialog, co oznacza zakończenie komunikacji między usługami.

Część programowa (odbiorcza) zaimplementowana w .NET

W pierwszej kolejności należy nawiązać połączenie z bazą danych. Najprościej zrobić to poprzez zaimportowanie biblioteki System.Data.SqlClient.

Poniżej znajduje się przykładowy kod, który będzie monitorować kolejkę i reagować na nowe wiadomości:

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = @"data source=localhost;initial catalog=TestDB;persist security info=True;Integrated Security=SSPI;";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand("WAITFOR (RECEIVE TOP(1) CAST(message_body AS NVARCHAR(MAX)) FROM MyQueue)", connection))
            {
                command.CommandTimeout = 0; // Czekaj na wiadomość bez limitu czasu
                while (true)
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read() && !reader.IsDBNull(0))
                        {
                            // Tutaj można przetwarzać otrzymaną wiadomość
                            string messageBody = reader.GetString(0);
                            Console.WriteLine("Received message: " + messageBody);

                            // Wywołaj asynchroniczne działanie lub inne operacje
                            // Przykład: CallAsyncOperation(messageBody);
                        }
                    }
                }
            }
        }
    }
}

Powyższy kod nie wymaga obszernego komentarza, jednakże należy zwrócić uwagę na dwa aspekty:

• kolejkę MyQueue odpytujemy poleceniem WAITFOR,
• Pprogram zatrzymuje swoje działanie na poleceniu command.ExecuteReader() i oczekuje, aż nowa wiadomość pojawi się w kolejce. Dzięki zastawaniu while(true) po przetworzeniu komunikatu z kolejki program będzie oczekiwać na kolejny.

ServiceBrokerListener – gotowe rozwiązania w .NET

Podczas poszukiwań gotowego rozwiązania do wykorzystania w projekcie natrafiliśmy na bibliotekę o nazwie ServiceBrokerListener, która upraszcza proces obsługi Service Brokera, zapewniając gotowe mechanizmy do tworzenia kolejek, dodawania usług i kontraktów, a także instaluje niezbędne triggery na monitorowanych tabelach w bazie danych.

Niezwykle praktycznym elementem ServiceBrokerListener jest obsługa błędów, co czyni go jeszcze bardziej wszechstronnym narzędziem. Z naszego doświadczenia wynika, że najwygodniej jest korzystać z tej biblioteki, jednakże tworząc własną solucję i projekt, a następnie generując niestandardową DLL na podstawie kodu źródłowego biblioteki. Dzięki temu mamy pełną kontrolę nad biblioteką i możemy dostosować ją do naszych potrzeb.

Jak już wspomniałem, biblioteka ServiceBrokerListener podczas inicjalizacji tworzy wszystkie komponenty potrzebne do komunikacji (triggery, kolejki, kontrakty etc.). Kontroluje również proces destrukcji tych komponentów. W trakcie prac nad zadaniem z wykorzystaniem biblioteki zaobserwowaliśmy, że w przypadku wystąpienia nieoczekiwanego wyjątku w aplikacji i jej nagłego zamknięcia komponenty utworzone na bazie nie są zniszczone. Jeśli trigger dodawany na początku działania aplikacji nie zostanie usunięty, operacja MERGE na danej tabeli staje się niemożliwa.

Biblioteka ServiceBrokerListener wraz z kodem źródłowym jest dostępna na platformie GitHub.

Podsumowanie

W artykule opisałem tylko jedno z wielu możliwych zastosowań Service Brokera. Może on być używany również do innych zadań, takich jak:

• Przetwarzanie zadań asynchronicznych – Service Broker umożliwia asynchroniczną komunikację między różnymi usługami lub aplikacjami w bazie danych. Jest często używany do przetwarzania zadań asynchronicznych, takich jak przetwarzanie zamówień, generowanie raportów lub obsługa zdarzeń systemowych.
• Synchronizacja danych – Service Broker może być wykorzystywany do synchronizacji danych między różnymi bazami danych, np., można go użyć do replikacji danych między lokalnymi i zdalnymi serwerami.
• Komunikacja między mikrousługami – w architekturze mikrousługowej (Microservices) Service Broker może być używany do komunikacji między różnymi mikrousługami, umożliwiając im współpracę i wymianę informacji.
• Monitorowanie i zarządzanie zadaniami – dzięki Service Broker można wysyłać do kolejki wiadomości, które zawierają informacje o zadaniach do wykonania, a następnie przetwarzać te zadania asynchronicznie.

Z powyższych powodów uważam, że narzędzie jest warte lepszego poznania i wykorzystania.